본문 바로가기
백엔드 개발

#043. 쿼리개선: like 쿼리는 왜 인덱스를 타지않았을까? 🤔 5000ms -> 0.05 ms 개선하기

by iamjoy 2023. 7. 1.

문제상황:

슬로우 쿼리가 발생한다는 알림을 받고 모니터링 로그를 열어보니 아래와 같은 쿼리였다.

select * from student where email ilike 'abc@xyz.com%';

 
이렇게 간단한(?) 쿼리가 인덱스를 타지 않아서 슬로우 쿼리를 발생시키고 있었다.

[참고]
실행환경은 Postgres 14v 이고, 아래와 같은 쿼리를 사용하려고 한다.

select emp_no from employee where first_name ilike 'Geo%'


사용하는 데이터는 mysql 의 샘플 데이터이다. employees 테이블에는 유저 정보 약 30만개의 데이터가 들어있어 index를 타는 지 확인하기에 적합하다고 생각했다. 실험을 위해 sample data를 준비하고 string 값인 first_name 컬럼을 선택했다. first_name에 일반 b-tree 인덱스를 생성했다.
각설하고 위의 쿼리의 실행계획을 확인해보면 다음과 같다.
https://explain.dalibo.com/plan/cg9ad43d6989d899

- Parallel Seq Scan: 풀스캔을 사용해 30만개의 데이터에 접근
- where 조건을 데이터 필터링 조건으로 사용 (의도한 건 인덱스 조건으로 사용하는 것)
- 전체 30만 개 중 유효한 데이터는 1657개(전체 대비 0.006%)여서 읽은 데이터 중 99%이상이 필터링 조건에 의해 버려진다.
테이블 풀 스캔은 가장 효율이 낮은 데이터 스캔 방식으로 성능 개선이 필요하다. 여기에서는 string을 포함하고 있는 지 확인하는 쿼리이기 때문에 인덱스를 태우기 위해서는
1) 대소문자를 구분하지 않고 조회하는 ilike가 아닌 like를 사용해야하고
2) 찾고자 하는 stirng의 앞은 고정되어있고(anchor) 뒤로만 %일치를 확인해야한다.
즉 위의 쿼리에서는 아래와 같이 ilike를 like로 설정하면 인덱스를 타야한다.

select emp_no from employee where first_name like 'Geo%'

... 고 생각했는데 이게 웬걸 인덱스를 타지 않는다. 

 

개념 및 해결: 

위의 문제를 해결하기 위한 세 가지 접근 방법을 생각했다.
(1) locale 설정 변경
(2) 쿼리 변경
(3) 인덱스 변경

(1) locale 설정 변경

Postgres의 index types 문서를 보면 아래와 같은 내용이 나온다. Postgres는 C-Locale을 사용하지 않는 경우, 패턴 일치(like, ilike)쿼리에 인덱스를 사용하기 위해서는 특수 연산자 클래스를 사용한 인덱스를 제공해야 한다. 특수 연산자 클래스는 11.10에서 확인할 수 있다.

패턴이 상수이고 문자열의 시작 부분에 고정되어 있는 경우(예: col LIKE 'foo%' 또는 col ~ '^foo') 패턴 일치 연산자 LIKE 및 ~가 포함된 쿼리에 대해 옵티마이저는 B-트리 인덱스를 사용할 수 있지만 col LIKE '%bar'는 사용하지 않을 수 있습니다. 그러나 데이터베이스에서 C locale을 사용하지 않는 경우 패턴 일치 쿼리의 인덱싱을 지원하기 위해 특수 연산자 클래스를 사용하여 인덱스를 생성해야 합니다(아래 섹션 11.10 참조). ILIKE 및 ~*에 대해 B-트리 인덱스를 사용할 수도 있지만, 패턴이 알파벳이 아닌 문자, 즉 대/소문자 변환의 영향을 받지 않는 문자로 시작하는 경우에만 사용할 수 있습니다.

C-Locale은 Postgres Locale 설정 중 하나로 C-Locale로 설정하면 locale 설정을 끈다는 의미이다. 다시 말해 지역설정이 되지 않는 경우에만 b-tree index로 패턴 일치 쿼리를 사용할 수 있다. (현재 DB의 로케일 설정을 아래 쿼리로 확인 할 수 있다.) 확인보니 유니코드를 지원하는 utf8로 설정되어 있다. 현재 운영 중인 서비스 DB를 이 쿼리만을 위해서 로케일 설정을 바꾸는 것은 적절한 해결이 아니라고 판단했다.

SHOW LC_COLLATE; -- Postgres 로케일 확인하기

 

(2) 쿼리 변경

패턴 일치가 아닌 equal 조건을 사용하도록 쿼리를 변경하는 것이다. 문제 상황은 이메일을 검색하는 상황이었다. 대부분 이메일은 unique한 값인 경우가 많다. 예를 들어 abc@gmail.com 과 abc@naver.com 은 서로 다른 메일이며  abc@gmail.com% 로 검색하는 경우 1개 이상의 데이터가 조회되는 경우가 많지 않다고 생각했다. 따라서 아래 쿼리로 수정하면 어떨까 하는 생각이 들었다.

select emp_no from employee where first_name = 'Georgi'

실행결과는 다음과 같이 인덱스를 사용한다
https://explain.dalibo.com/plan/15df0g25hga4b7a9

다만, 운영상의 편의를 위해 equal 쿼리를 사용하지 못했기 때문에 이 방법 또한 선택할 수 없었다. 😅

(3) 인덱스 변경

pattern matching index를 소개합니다 (으아ㅏㅏㅏ...)

결과적으로 인덱스 설정을 수정해 인덱스 설정을 하는 방법을 찾아봤다. Operator Class에 GIN 과 varchar_pattern_ops 의 선택지가 있었는데, 이전에 회사 내부적으로 GIN index 관련해 이슈를 공유 받은 적이 있어 Operator Class를 적용하는 방법을 선택했다. 해당 인덱스는 패턴 매칭을 위한 인덱스이므로 일반 <, <=, > 또는 >= 비교를 포함하는 쿼리에서 인덱스를 사용하려면 기본 연산자 클래스를 사용하여 인덱스를 생성해야 한다. 이러한 쿼리에는 같음 비교 (=)를 제외하고는 xxx_pattern_ops 연산자 클래스를 사용할 수 없다. 서로 다른 연산자 클래스를 사용하여 동일한 열에 여러 인덱스를 생성할 수 있다. ( 11.10. Operator Classes and Operator Families )

CREATE INDEX index_employee_2 ON employee (first_name varchar_pattern_ops);

결과적으로 의도하는 index_employee_2 인덱스를 이용해 where을 인덱스 조회 조건으로 사용한다..
https://explain.dalibo.com/plan/cfd350f12fc97aag

인덱스의 설명 중 "~>=~" 이 있는데 기본 연산자(<, <=, >=, >) 클래스와 다른 점은 Locale별 데이터 정렬 규칙에 따라 값을 비교하는 것(즉, 언어 환경 설정을 고려하지 않고)이 아니라 character("A~Z")로 엄격하게 비교한다는 것이다. 결국 국소적으로 locale을 사용하도록 설정한 것과 같다.

참고: Postgres에서 GIN을 설정하기 위해서는 아래와 같은 GIN에 사용할 extension을 설치해야한다.

-- 인덱스 추가 pg_trgm
SELECT * FROM pg_extension;

CREATE EXTENSION pg_trgm;
-- 삭제 DROP EXTENSION pg_trgm;

CREATE INDEX index_student_1 ON users USING gin (email gin_trgm_ops);

 

번외

MySQL을 이용해 같은 테스트를 해보면 다음과 같다.
환경:
MySQL Server 8.0.33
employee 샘플 데이터 사용, first_name으로 b-tree index 설정
1)  like + ~* 패턴 매칭 (MySQL은 like 로 case-insensitive 한 검색을 지원한다)

2) equal 조건 검색