[sql] Like 연산자 알아보기

작성일 | In |

저는 사내 재고 분류를 위한 데이터 마트 구성을 담당하고 있습니다. 시기별로 이 상품이 어떤 재고인지(양성 / 악성 / …) 분류되는 기준을 걸러내는데, 이 때 많이 사용하는 연산자가 Like입니다.

Like는 검색 필터인만큼 스캔량도 많고 잘못 사용할 경우 효율 저하를 일으킬 수 있습니다. 이에 Like 연산자가 어떻게 작동하고 한계는 무엇인지, 검색이 어떤 식으로 변화해왔는지 알아보겠습니다.


Index 처리

DB의 성능이 낮아지는 원인은 여러가지가 있으나 I/O, 그 중에서도 랜덤 I/O는 성능에 치명적입니다. OLAP도 그렇겠지만 소량의 데이터를 수시로 스캔해야 하는 OLTP 시스템에서 스캔 범위를 통제하기 어려운 랜덤 I/O는 더욱 좋지 않습니다.

인덱스는 랜덤 I/O를 줄이기 위한 가장 흔한 방법 중 하나입니다. 인덱스에 일반적으로 사용하는 B Tree 인덱스 알고리즘은 root node를 기준으로 인덱스에 해당하는 컬럼들이 leaf node까지 연결되어 있습니다.

image

검색하고자 하는 문자열을 인덱스로 생성해놓는다면 어느정도 효율을 기대할 수 있습니다.


Like가 비효율적이게 되는 이유?

like를 주로 사용하는 컬럼에 인덱스를 태우면 성능향상을 기대할 수 있으나, 인덱스를 사용하더라도 효율적이지 않은 경우도 있습니다. 원인은 여러가지입니다.

  • 인덱스 컬럼으로 설정한 문자열이 인덱스로 설정하기에 부적합하거나 (ex. 너무 많은 중복)
  • 결합 인덱스를 잘못 구성했거나 (ex. 선두 컬럼 미사용)
  • 인덱스를 태운 컬럼을 가공해 사용하거나

하는 경우가 있겠습니다. 결과적으로 이런 현상들은 range scan의 범위를 제대로 지정하지 못하게 하기 때문에 인덱스의 효율을 떨어트립니다.

검색의 관점에서 살펴보겠습니다. like는 동일한 값을 선택하는 equal 연산이 아닙니다. Index를 사용하면 일정 범위만을 검색할 수 있고, Like를 사용하는 컬럼에 인덱스가 걸려있다면 이 역시 일정 범위만을 스캔하는 range scan의 관점에서 사용할 수 있습니다. 물론 선두 컬럼이 인덱스 range scan을 탔다고 해서 무조건 효율이 좋은건 아니지만, like를 주로 사용하는 컬럼을 인덱스로 태우면 효율이 증가한다는건 쉽게 이해할 수 있습니다.

그렇다면 like가 비효율적이게 되는 이유도 비슷하게 생각해볼 수 있습니다. 스캔의 시작이나 끝을 잡을 수 없을 때입니다. 이 비효율은 like의 조건이 중간이나 앞으로 가면 기하급수적으로 증가합니다.

image

B tree 인덱스는 정렬되어 있기 때문에 WINA% 의 경우 WINA만 스캔하면 되므로 그 범위를 바로 잡을 수 있지만, WIN%D, WI%ND는 그 스캔 범위가 매우 넓어집니다. 더 나아가 %WIND가 된다면 이는 index를 태우더라도 range scan이 아닌 full scan을 해야 할 것입니다.

인덱스를 태운 컬럼이 아니더라도 WINA%보다는 %WINA가 더 비효율적이고 %WINA% / %WI%ND같이 될수록 효율은 낮아질 것입니다. 문자열의 어디까지 읽어야 조건에 부합하는지 판단할 수 있는 범위가 점점 길어지기 때문입니다.

BWINAD 문자열을 예로 들면,

  • WINA% : 맨 앞 1글자만 읽어도 판단이 가능
  • %WINA: BWINA까지 읽어야 판단이 가능
  • %WIN%D : BWINAD까지 읽어야 판단이 가능

같은 경우가 생길 수 있겠습니다.



그러면 어떻게?

위 문단의 중점내용은 TEXT%보다 %TEXT / %TE%XT와 같은 경우가 훨씬 많은 스캔량을 요구한다는 것입니다.

image image

인덱스는 앞단 문자열부터 정렬된채로 있을테니, 중간이나 뒷단 문자열을 검색하려면 결국 full scan에 가까워지기 때문입니다.


전문 인덱스(full text index)

대부분의 RDBMS에서는 full text index를 지원합니다. 세부 방식이나 명칭은 조금씩 다르겠지만, 기본 개념은 유사합니다.

토큰 단위로 쪼개진 단어를 인덱스의 KEY로 삼고, 해당하는 row의 id를 매핑한 딕셔너리를 가지고 있다면, 해당 문자가 포함된 row를 빠르게 찾아가는 원리입니다. 이는 RDBMS의 index 뿐만 아니라 ES와 같은 검색 엔진에도 동일하게 적용되는 개념입니다. 전문 인덱스의 성능은 Tokenizer의 성능이나 row id의 사용여부 등 각각 RDBMS의 특성마다 조금씩 다릅니다.

mysql의 FULL TEXT INDEX나 postgresql의 GIN INDEX를 테스트해본 포스팅은 이미 많으니 생략하도록 하겠습니다.


참고

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

친절한 SQL 튜닝 - 조시형 저

https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html