MySQL LIKE % 위치에 따른 인덱스

리브리버·2023년 6월 3일
0
post-thumbnail

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에 대해서는 추후에 업로드할 예정입니다


참고

0개의 댓글