index를 사용해 검색 쿼리를 개선시켜보기

토이프로젝트를 진행하면서 검색 기능을 개선시킬 필요가 생겼습니다.

 

천만건의 데이터 중 검색창에서 특정 상품을 검색하는 기능이 있는데, 그냥 like문을 통해서 쿼리를 날리니 너무 많은 시간이 소요되었습니다. (심지어 지금 DB는 rds 프리티어를 사용 중 ㅠ)

그래서 인덱싱이라는 방법을 통해 검색 기능을 개선시키고자 하였습니다.

일단 500만건의 데이터를 db에 넣어두고, 인덱싱 테스트를 진행했습니다.

(인덱싱하는데 like문에서 와일드카드를 양쪽에? 라고 하실 수도 있지만, 관련내용을 저도 작성하다가 알게 되었네요. 따로 정리해 보겠습니다.)

 

explain
select COUNT(*)  from products p WHERE name LIKE "%Sleek%"

먼저 EXPLAIN 키워드를 통해 사용하고자 하는 쿼리의 실행계획을 봤습니다.
product 테이블에서, name 컬럼에 Sleek이라는 성이 들어가는 것의 갯수를 세는 쿼리입니다.  

 

type도 all로 되어있고, extra에도 별 내용이 없습니다.
(type에서 all은 풀 테이블 스캔입니다. 테이블에 있는 전체 데이터를 읽어가면서 비교하는 방식입니다.)

(extra는 mysql이 어떻게 쿼리를 풀었는지에 대한 부가 정보가 표시됩니다.)

select COUNT(*)  from products p WHERE name LIKE "%Sleek%"

이제 이 쿼리를 실행시켜 얼마나 걸리는지를 체크해 보겠습니다.

약 10초 정도가 걸리네요.
이번엔 인덱스를 생성해 봅시다.

 

CREATE INDEX location_name on test.products (name DESC)

위 코드에서 location_name이라는 이름으로 인덱스를 생성하고(정신없이 하니 네이밍이 이상하네요..)
products라는 테이블에서 name컬럼을 내림차순으로 정렬하겠다는 내용입니다.

 

show index from test.products

인덱스를 생성하고, 위 쿼리를 통해 잘 생성되었는지를 확인합니다.

 

잘 생성이 되었네요.

여기에서 카디널리티는 해당 열에 대한 테이블에 나타나는 고유한 값의 수를 나타냅니다. 

따라서 카디널리티의 수가 클수록 중복도는 낮아지는 것이죠.

 

만약 1000개의 row가 있는 pk 컬럼 하나로 이루어진 테이블이 있다면, 이 테이블에 대한 카디널리티는 1000이 되겠죠?

 

그 다음에는 explain을 통해 다시 쿼리 실행 계획을 봅니다.

type을 보니 인덱스가 잘 연결되어 있는 것을 볼 수 있습니다.

 

실제로 쿼리를 날려보겠습니다.

처음 날린 쿼리 이후에는 3초대가 나오네요. 30% 정도가 개선되었습니다.

(처음 쿼리가 늦는 이유는 인덱스를 사용하여 데이터를 조회할때 처음에는 인덱스를 메모리에 저장하지 않았기 때문에 시간이 더 걸리게 됩니다. 이후에는 메모리에 로드된 인덱스를 통해 더 나아진 결과를 가질 수 있는 것이라고 합니다.
출처: https://dba.stackexchange.com/questions/40149/query-runs-slow-in-test-site-on-first-execution-why)

여기서 조금 더 생각해 봤습니다.

지금 like 문에서 사용한 Sleek은 성입니다.
그런데 와일드카드로 %Sleek%으로 작성되어 있죠.

 

어짜피 성은 제일 앞에 오는 단어입니다.

그리고 인덱스는 검색어의 맨 왼쪽 문자열부터 일치하는 데이터를 찾기 때문에 와일드카드를 'Sleek%'으로 설정하면 어떻게 될지 궁금해 졌습니다.

이제는 0.3초대로 내려왔습니다.
무려 97 % 감소 (두 값의 차이 9.7)!
이렇게 작은 변화인데도 큰 차이가 있네요.

 

물론 지금은 단순 테스트이기 때문에 이렇게 쑥쑥 바뀌긴 하지만
일단 500만이라는 작은 데이터에서 97%정도 개선이니, 이후 1000만건의 데이터를 사용할 때에도 인덱싱을 사용할 때 얼마만큼의 효과가 있을 지 궁금해졌습니다.