태그 자동 완성 검색 기능에 인덱스를 걸어보자

🟩 태그 검색

코드 리뷰는 바톤 🏃🏃‍♀️

진행 중인 프로젝트에서 태그를 검색하는 기능이 있다.

검색 바에 'ja'를 치면 'java', 'javascript', ... 이 나오면서 사용자가 태그 자동 완성을 사용하여 편리함을 제공하고 있다.

 

하지만 이런 검색 기능은 생각보다 많은 비용이 발생할 수 있다.

먼저 자동 완성 기능에 나오는 태그 목록은 모두 사용자에 의해서 만들어진 태그들이다.

즉, 언제든지 새로운 태그는 만들어질 수 있고 현재는 태그가 100개, 1000개 정도의 규모라고 해도 이후에 수십만, 수백만이 생길 가능성이 있다.

 

사실 그정도면 검색 엔진을 도입해야하는 수준이지 않을까 싶지만 인덱스를 학습하게 되면서 인덱스를 이용해서 쿼리 비용을 절감할 수 있을 거라 생각하고 실험해보려 한다.

 

Î

 

 

 

 

tag 테이블은 id, name, reduced_name 를 컬럼으로 가지고 있다.

인덱스는 PK인 id에 자동으로 만들어진 상태이다.

 

 

미리 태그를 476,496개 넣어 놓았다.

SELECT count(1) AS 태그_수 FROM tag;

태그 수

 

태그 이름은 고르지 못하다는 점이 아쉽지만 이름 마지막만 다르도록 해놓았다.

SELECT * FROM tag;

 

 

 

 

이제 실험을 진행해보자!

먼저 실제 사용하고 있는 쿼리와 비슷하게 SQL을 작성했다.

SELECT *
FROM tag
WHERE tag.reduced_name like "테%"
ORDER BY tag.name DESC
LIMIT 10;

쿼리를 실행하면 아래와 같은 결과가 나온다.

 

 

🟧 실행 계획 분석

EXPLAIN
SELECT *
FROM tag
WHERE tag.reduced_name like "테%"
ORDER BY tag.name DESC
LIMIT 10;

select_type	SIMPLE	단순한 SELECT 쿼리
type	ALL	인덱스를 사용하지 않은 Full Table Scan 방식
row	455508	실행 계획의 효율성 판단을 위해 예측된 인덱스 조건에 일치하는 레코드 건수 ( 정확도 낮음 )
filtered	11.11	필터링되고 남은 레코드의 비율 (455508 * 0.1111 = 약 50,606건 )
Extra	Using filesort	ORDER BY 처리가 인덱스를 사용하지 못한 것을 의미. 조회된 레코드를 정렬용 메모리 버퍼에 복사해 Quick Sort 또는 Heap Sort를 이용하여 정렬을 수행하게 된다.

 

 

🟧 쿼리 실행 시간 확인

EXPLAIN ANALYZE
SELECT *
FROM tag
WHERE tag.reduced_name like "테%"
ORDER BY tag.name DESC
LIMIT 10;

Limit	쿼리의 결과로 반환될 행 수를 제한하는 부분
Sort	결과 행을 정렬하는 작업
Filter	'테%'와 일치하는 값 필터링
Table scan on tag	tag 테이블의 스캔을 수행

Limit
actual time	284...284	테이블에서 읽은 값을 기준으로 테이블에서 일치하는 레코드를 검색하는데 걸린 시간(ms) 첫 번째 값의 의미 : 첫 번째 레코드를 가져오는데 걸린 평균 시간(ms) 두 번째 값의 의미 : 마지막 레코드를 가져오는데 걸린 평균 시간(ms)
rows	10	테이블에서 읽은 값이 일치하는 평균 레코드 건수
loops	1	테이블에서 읽은 값을 이용해 레코드를 찾는 작업이 반복된 횟수
Sort
actual time	284...284	``
rows	10	``
loops	1	``
Filter
actual time	0.599..174	``
rows	476496	``
loops	1	``
Table scan on tag
actual time	0.539..142	``
rows	476496	``
loops	1	``

 

 

 

 

 

 

 

🟩 인덱스 적용

이제 인덱스를 적용하고 실행 계획과 쿼리 실행 시간을 비교해보자!

먼저 이전 태그 검색 쿼리를 다시 보면 'tag.reduced_name'을 조건에 이용하고 있는 것을 알 수 있다.

SELECT *
FROM tag
WHERE tag.reduced_name like '테%'
ORDER BY tag.name DESC
LIMIT 10;

 

 

'tag.reduced_name'에 인덱스를 걸고 실험을 진행하자.

CREATE INDEX idx_tag_reduced_name ON tag (reduced_name);

인덱스가 추가됨!

 

 

 

🟧 실행 계획 분석

select_type	SIMPLE	단순한 SELECT 쿼리
type	ALL	인덱스를 사용하지 않은 Full Table Scan 방식
possible_keys	idx_tag_reduced_name	검색 조건으로 사용할 수 있는 후보 키 ( 말 그대로 후보 )
row	455508	실행 계획의 효율성 판단을 위해 예측된 인덱스 조건에 일치하는 레코드 건수 ( 정확도 낮음 )
filtered	50	필터링되고 남은 레코드의 비율 (455508 * 0.5 = 약 227,754건 )
Extra	Using filesort	ORDER BY 처리가 인덱스를 사용하지 못한 것을 의미. 조회된 레코드를 정렬용 메모리 버퍼에 복사해 Quick Sort 또는 Heap Sort를 이용하여 정렬을 수행하게 된다.

인덱스 적용 후에는 possible_keys에 idx_tag_reduced_name이 나왔다.

possible_keys 내에 있는 것들은 검색할 때 이러한 것들을 사용할 수 있다는 것을 의미한다.

 

실행 계획을 봤을 때는 인덱스 적용과 큰 차이가 있는거 같지는 않다.

	인덱스 적용 후	인덱스 적용 전
select_type	SIMPLE	SIMPLE
type	ALL	ALL
possible_keys	idx_tag_reduced_name	x
row	455508	455508
filtered	50	50
Extra	Using filesort	Using filesort

 

 

 

 

🟧 쿼리 실행 시간 분석

Limit
actual time	216...216	테이블에서 읽은 값을 기준으로 테이블에서 일치하는 레코드를 검색하는데 걸린 시간(ms) 첫 번째 값의 의미 : 첫 번째 레코드를 가져오는데 걸린 평균 시간(ms) 두 번째 값의 의미 : 마지막 레코드를 가져오는데 걸린 평균 시간(ms)
rows	10	테이블에서 읽은 값이 일치하는 평균 레코드 건수
loops	1	테이블에서 읽은 값을 이용해 레코드를 찾는 작업이 반복된 횟수
Sort
actual time	216...216	``
rows	10	``
loops	1	``
Filter
actual time	0.0578..108	``
rows	476496	``
loops	1	``
Table scan on tag
actual time	0.0543..77	``
rows	476496	``
loops	1	``

 

 

쿼리 실행 시간을 분석 했을 때 전체적으로 인덱스를 적용했을 때 시간 비용이 적어졌다.

특히 Filter와 Table scan on tag에서 실행 시간이 1/10 정도로 줄었다.

 

하지만 전체적인 실행 시간이 크게 차이가 나지는 않는다.

Limit
	인덱스 적용 후 (ms)	인덱스 적용 전 (ms)
actual time	216...216	284...284
rows	10	10
loops	1	1
Sort
actual time	216...216	284...284
rows	10	10
loops	1	1
Filter
actual time	0.0578..108	0.599..174
rows	476496	476496
loops	1	1
Table scan on tag
actual time	0.0543..77	0.539..142
rows	476496	476496
loops	1	1

 

 

 

 

 

 

 

 

🟩 비용이 많이 발생 부분 Sort

이전에 인덱스 후, 인덱스 전 테이블을 비교해봤다.

tag 테이블에 `reduced_name` 컬럼에 인덱스를 걸어서 비용을 절감할 수 있었지만 엄청나게 큰 효과를 보지는 못했다.

 

쿼리 실행 시간 분석을 통해서 봤을 때 Sort에서 가장 큰 비용이 나오고 있다.

Extra : Using filesort

실행 계획에 Extra 부분에서도 Filesort를 이용하면서 느려진다고 힌트를 주고 있다.

Filter, Table scan on tag가 약 90% 비용이 절감되었지만 Sort가 1% 비용 절감되는 것보다 실제 실행 시간이 줄지 않는다.

Sort
	인덱스 적용 전	인덱스 적용 후
actual time	216...216	284...284
rows	10	10
loops	1	1

Filter
	인덱스 적용 후	인덱스 적용 전
actual time	0.0578..108	0.599..174
rows	476496	476496
loops	1	1

 

 

 

 

 

 

 

 

🟩 ORDER BY, LIMIT, 정렬 처리 방법

SELECT *
FROM tag
WHERE tag.reduced_name like '테%'
ORDER BY tag.name DESC
LIMIT 10;

현재 ORDER BY를 사용하고 있는데 이 때 정렬 방법으로 인덱스를 이용하는 것과 Filesort를 이용하는 방식이 있다.

	장점	단점
인덱스 이용	INSERT, UPDATE, DELETE 쿼리가 실행될 때 인덱스가 정렬되어 있어서 빠르다.	INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요해서 느리다. 인덱스 정보를 위한 디스크 공간이 더 필요하다. 인덱스 개수가 늘어날수록 InnoDB의 버퍼 풀을 위한 메모리가 많이 필요하다.
Filesort 이용	인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다. 정렬해야 할 레코드가 많지 않을 때 충분히 빠르다.	정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다.

 

 

태그 검색 쿼리는 현재 filesort 방식을 이용하고 있다.

	인덱스 적용 후	인덱스 적용 전
Extra	Using filesort	Using filesort

인덱스를 이용해서 정렬한다면 실제 인덱스 값이 렬되어 있기 때문에 인덱스를 순서대로 읽기만 하면된다.

인덱스를 이용하기 위해서 ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.

 

tag 테이블에 name 컬럼에는 인덱스를 주지 않았기 때문에 인덱스를 이용한 정렬이 아닌 Filesort를 이용하고 있었다.

이제 두 가지 방법으로 현재 비용 문제를 해결할 수 있다.

• tag.name에 인덱스 걸기
• 도메인을 생각해서 기존에 인덱스가 걸린 tag.reduced_name을 기준으로 정렬하기

적용할 도메인을 정렬 기준을 생각하면 tag.name에 인덱스를 걸 필요가 없다.

그래서 후자의 방법으로 선택하기로 했다.

 

 

🟧 도메인을 생각해서 정렬 기준 컬럼 변경하기

WHERE 조건으로 tag.reduced_name 을 이용하기 때문에 tag.reduced_name에는 인덱스를 걸은 상태이다.

tag.reduced_name 은 tag.name 데이터의 공백을 모두 제거한 상태라고 보면 된다.

tag.name	tag.reduced_name
java	java
j   ava	java
ja v a	java
j                   a                                                        va	java

 

정렬할 때 tag.name을 기준으로 두지않고 tag.reduced_name을 기준으로 두어도 같은 정렬을 할 수 있다.

쿼리를 아래와 같이 수정하고 쿼리 실행 시간을 분석해보자.

SELECT *
FROM tag
WHERE tag.reduced_name like '테%'
ORDER BY tag.reduced_name DESC
LIMIT 10;

길이가 길어서 결과를 아래에 코드 블럭에 다시 적어두었다.

"-> Limit: 10 row(s)  (cost=46200 rows=10) (actual time=0.265..0.29 rows=10 loops=1)
    -> Index range scan on tag using idx_tag_reduced_name over ('테' <= reduced_name <= '테􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿􏿿') 
    (reverse), with index condition: (tag.reduced_name like '테%')  (cost=46200 rows=227754) (actual time=0.264..0.268 rows=10 loops=1)
"

Limit
	정렬 기준 tag.reduced_name, 인덱스 적용 후 (ms)	정렬 기준 tag.name, 인덱스 적용 후 (ms)
actual time	0.265..0.29	216...216
rows	10	10
loops	1	1

결과를 봤을 때 놀랍게도 쿼리 실행 시간이 굉장히 줄은 것을 알 수 있다.

 

그렇다면 왜 그럴까?

tag.reduced_name은 이미 인덱스가 걸려서 순서가 보장되어 있는 상태다.

그래서 따로 tag.reduced_name에 대해서 정렬을 다시 할 필요없이 결과를 그대로 반환해주면 된다.

쿼리 실행 결과를 보면 Index range scan을 tag.reduced_name 인덱스를 이용해서 검색했고 내림차순으로 정렬을 요구했기 때문에 reverse한다고 나와있다.

 

 

 

처음에 tag.name에 인덱스를 걸었다면 검색할 때의 조건을 tag.reduced_name 컬럼을 이용하기 때문에 두 컬럼 모두 인덱스를 걸었을 수도 있다.

하지만 도메인을 생각해봤을 때 tag.reduced_name 컬럼 하나만 인덱스를 걸어도 WHERE 조건, ORDER BY 기준 모두 하나의 컬럼으로 해결할 수 있어 좀 더 효율적으로 해결할 수 있었다 👍