1. 선택도에 따른 단일 칼럼 인덱스 최적화
1.1 선택도가 높은 컬럼(domain)
인덱스의 성능에 칼럼의 선택도가 중요하게 작용한다. 선택도는 데이터의 "고유성"을 의미하는데 domain 컬럼의 selectivity_percentage를 보면 100이다. 이 말은 43,420개의 rows 중 데이터 중복이 없다는 뜻이다. 인덱스를 걸어보면 아래와 같은 성능 차이를 확인할 수 있다.
1.2 선택도가 낮은 칼럼(businessStatus)
이제 반대로 선택도가 낮은 칼럼에 인덱스를 걸어보자. businessStatus 칼럼은 domain에 비해 선택도가 0.01152로 굉장히 낮은 편이다.
실험을 통해 선택도가 낮은 컬럼은 단일 인덱스로 적합하지 않다는 걸 확인하였다.
2. 복합 인덱스 쿼리 최적화
그렇다면 선택도가 낮은 두 칼럼을 복합 인덱스로 걸면 어떻게 될까? 이 실험에서는 선택도가 낮은 두 컬럼을 복합 인덱스로 걸었을 때 성능 변화를 보고자 한다.
2.1 모의 가정
쇼핑몰 검색 사이트를 이용하는 사용자는 현재 영업 중인 쇼핑몰의 검색 경험에 불만이 있고 쿼리 개선이 필요한 상황이다. 테스트를 위해 13만 개의 더미 데이터를 집어넣었고, 2가지 조건(businessStatus, overallRating)이 자주 검색된다는 전제 하에 복합 인덱스를 구성했다.
CREATE INDEX idx_status_rating ON `shopping-mall` (business_status, overall_rating);여기서 business_status 칼럼을 overall_rating보다 앞에 둔 것은 쿼리 패턴을 분석했을 때 사용자가 business_status의 상태를 먼저 필터링한 후, overall_rating을 통해 쇼핑몰을 검색하는 것이 일반적인 검색 패턴이라는 합리적인 판단에 의해 이렇게 순서를 배치했다.
2.2 EXPLAIN ANALYZE
인덱스를 걸기 전과 후를 비교해 보면 rows와 time이 확실히 개선된 것을 볼 수 있다.
2.3 포스트맨
선택도가 매우 낮은데도 불구하고 속도가 6배 넘게 향상되었다. 왜일까? 복합 인덱스는 두 조건을 동시에 처리한다. 즉 두 조건을 동시에 만족하는 레코드를 한 번의 트리 탐색으로 찾기 때문에 탐색 비용을 절감할 수 있다.
3. 인덱스 칼럼 순서 최적화
복합 인덱스에서 컬럼 순서에 따른 성능 차이를 비교해 보자. 날짜 조건 칼럼의 배치에 따른 성능을 체감해 보고자 monitoringDate을 추가하였다.
3.1 domain, monitoringDate, businessStatus
3.2 businessStatus, monitoringDate, domain
3.3 domain, businessStatus, monitoringDate
3.4 businessStatus, domain, monitoringDate
다른 건 어느 정도 예상을 했지만 3.3과 3.4의 속도 차이가 안 난다는 점은 예상하지 못했다. 알아본 결과 MySQL 옵티마이저라는 녀석이 선행 칼럼의 선택도가 낮을 경우 내부적으로 실행 계획을 최적화하여 선택도가 높은 후행 칼럼을 우선적으로 활용한다고 한다.
4. 부분 일치 검색 컬럼 쿼리 최적화
위 가정에 이어 추가로 mainProducts 컬럼을 자주 검색하는 쿼리 패턴이 관찰되었다. mainProduct의 경우 아래와 같은 형태를 가지고 있으므로 부분 키워드로 검색 시 조회가 되도록 구현해야만 한다. 현재 QueryDSL을 사용 중이고 가능하면 이 환경을 유지한 상태에서 최적화를 진행할 생각이다.
QueryDSL에 WHERE 조건으로 LIKE '% 검색어%'를 적용 시 B-TREE 인덱스 구조는 왼쪽에서 오른쪽으로 순차적으로 값을 비교하기 때문에 후방일치 검색에서는 인덱스 활용이 불가능하고, 시도하면 전체 테이블 스캔(Full Table Scan)이 발생한다.
이 문제를 해결하기위한 대안으로 FULL-TEXT 기반의 INDEX, Native Query 등이 있다. Full-Text-Search는 FULL-TEXT INDEX를 활용하여 텍스트 데이터를 검색하는 기법인데, 현재 칼럼에 사용하기엔 데이터의 길이가 너무 짧아 적절한 선택이라 보긴 어렵지만 경험 차원에서 사용해 보기로 했다.
4.1 FULL-TEXT SEARCH로 해결할 수 있을까?
MySQL의 풀텍스트 기능을 QueryDSL에서 사용하고자 DDL 문을 통해 풀텍스트 인덱스를 생성하고, QueryDSL에 위 코드를 추가했다. 문제는 파싱 에러가 뜬다. 알아보니 QueryDSL에서는 MATCH... AGAINST 구문을 기본적으로 지원하지 않는다고 한다.
하지만 낙심은 금물... Custom Function을 등록하면 해결이 가능하다고 한다.
4.2 Custom Function 등록 방법
별도의 CustomFunctionContributor 클래스를 만들었다. 이게 뭐냐면 Hibernate Query Language (HQL)에서 MATCH .. AGAINST와 같은 MySQL의 풀 텍스트 기능을 사용할 수 있게 해 준다.
단 아래의 파일을 META-INF/services 추가하여 경로를 등록해 줘야 사용 가능함. (이거 잘못 추가하면 엄한 데서 개고생 한다.)
이제 커스텀 함수에서 정의한 match_against를 사용해서 아래와 같이 코드를 수정해 주면 QueryDSL에서 부분 키워드 검색이 가능해진다. 이제 끝난 걸까?
4.3 문제 발생: 두 글자 검색 시 빈 깡통을 반환한다.
MySQL의 검색은 3자 이상의 단어만 인덱싱하여 검색할 수 있다고 한다. 즉, 1자 또는 2자짜리 단어는 풀텍스트 인덱스를 생성할 수 없고, 검색에도 포함되지 않는다. my.cnf 파일에서 최소 글자 수를 조정할 수 있지만, 너무 낮게 설정하면 많은 단어가 인덱싱 되기 때문에 쿼리 성능에 문제가 생길 수 있다.
4.4 해결책: NGRAM
NGRAM은 짧은 단어나 부분 문자열 검색이 가능하다. 사용 방법은 기존 FULLTEXT SEARCH 구문 뒤에다 WITH PARSER ngram만 추가해 주면 끝이다.
4.5 성능 비교
속도가 약 7배 개선되었다.