Table 내에 특정 컬럼에 INDEX를 설정하여 LIKE 문을 통해 검색하는 경우
INDEX를 생성한다 하더라도 %의 위치에 따라 INDEX가 정상 작동을 하지 않아 Table SCAN(Full SCAN) 이 발생하는 경우도 있습니다
이번 글에서는 LIKE 검색시 % 위치에 따라 INDEX 사용 여부를 확인해보겠습니다
Table 구조
INDEX 컬럼
100만개의 데이터에 대해 INDEX 생성시 16.14 초가 걸렸습니다
INDEX를 생성하게 되면 해당 INDEX는 (한글 기준) 내의 데이터들이 ㄱ, ㄴ, ㄷ 순으로 정렬되어 추후 해당 column을 탐색시 사용되게 됩니다
Explain의 실행 계획 항목 설명
id
- 쿼리의 실행 순서대로 1부터 순차적으로 값을 부여
select_type
- select 구문의 실행 타입
table
- 테이블 명
type
- 단일 테이블
- 테이블 access 형태 - 두 개 이상의 조인된 SQL
- 테이블 간의 조인 형태
all : 전체 데이터 스캔(full table scan)
range : 주어진 범위 내의 row 스캔
possible_keys
- 옵티마이저가 쿼리 처리를 위해 고려한 인덱스 후보. 사용 가능한 인덱스들의 리스트
key
- 옵티마이저가 실제로 사용한 인덱스 key
key_len
- 옵티마이저가 사용한 인덱스 key의 길이값.
- 얼마나 많은 인덱스 key를 사용할 것인지 알 수 있음
ref
- 행을 추출하는데 키와 함께 사용된 컬림이나 상수값
rows
- 쿼리를 수행하기 위해 검색해야할 row의 개수
- 인덱스와 조건을 최적화 하여 row의 개수를 줄일수록 성능이 향상됨
extra
- 옵티마이저가 쿼리에 대한 추가 정보를 출력
Select를 통해 탐색하는 쿼리를 날렸을때 INDEX가 어떻게 적용되는지 보고자 합니다
1. '광명읍구로동%' 과 같이 데이터 시작 형식을 검색하는 경우
subject like '광명읍%' 검색인 경우 광명읍으로 시작하는 데이터들을 조회하고자 하는 것입니다.
2. '%광명읍구로동' 끝나는 데이터 형식을 검색하는 경우
subject like '%광명읍' 검색인 경우 광명읍으로 끝나는 데이터들을 조회하고자 하는 것입니다.
3. '%광명읍구로동% 데이터에 포함하는지 검색하는 경우
subject like '%광명읍' 검색인 경우 광명읍이 포함된 데이터들을 조회하고자 것입니다.
왜? '광명읍% 을 제외한 나머지 방식은 인덱스가 적용되지 않았을까요 ?
데이터베이스의 인덱스의 자료구조는 대부분 B Tree 구조로 이루어져있습니다.
이는 한글 기준 ㄱ,ㄴ,ㄷ 순으로 정렬이 되어있으므로 왜? % 위치에 따라 인덱스 조건이 적용되지 않았을까? 를 다시 생각해보면 인덱스가 사용될때
-
광명읍%
광명읍으로 시작하는 자료를 찾아주고 -
%광명읍
광명읍으로 끝나는 자료를 찾아주며 -
%광명읍%
광명읍을 포함하는 자료를 모두 찾아줍니다
이러한 특징을 생각해보면 인덱스를 사용하기 위해서는 INDEX의 이 정렬된 특징을 사용해야 합니다
데이터를 찾을때 광명읍% 의 경우 데이터 가장 앞단에 "광명읍" 이라는 키워드를 기준으로 데이터들을 탐색할 수 있습니다 이때 filtered가 100으로 INDEX가 100% 적용된 것을 확인할 수 있습니다
반면 %광명읍, %광명읍% 의 경우 가장 앞단을 기준으로 "광명읍" 키워드를 탐색하는 것이 아닌 포함되어있는지를 판단하는 것이기 때문에 filtered 가 11.11 으로 INDEX 11.11%만 적용된 것을 볼 수 있습니다
그럼 어떻게 검색하면 될까 ?
Full Text Search 을 사용하면 인덱스를 사용하지 못한 부분을 사용하면 빠른 검색이 가능하다고 합니다.
Full Text Search에 대해서는 추후에 업로드할 예정입니다
참고
