[SQLite] 텍스트 검색과 인덱스

#
[원본 링크]

SQLite는 PostgreSQL 같은 주요 DB들에 비하면 인덱스 활용 수준이 제한적인 편이다.
텍스트 인덱싱이 어디까지 되고 뭐가 안되는지 그 한계를 대략 정리해본다.

테스트 데이터셋은 1000만개 정도만 넣어봤다.

sqlite3 test.db
-- 1. 테스트 테이블 생성
CREATE TABLE IF NOT EXISTS test_text (
  id INTEGER PRIMARY KEY,
  text TEXT NOT NULL
);

-- 2. generate_series를 사용하여 1000만 개 행 삽입
INSERT INTO test_text (id, text)
SELECT 
  value as id,
  'test_text_' || value as text
FROM generate_series(1, 10000000);

넣으면, 이런 식으로 조회가 될 것이다.

근데 저 text 필드에 대해서 인덱스를 태우고 싶다면 어떻게 해야할까?
일단 인덱스를 추가해보자

create index idx_text on test_text(text);

그러면 일반적인 동등 인덱스(=)에 대해서는 인덱스를 잘 탄다.

대소비교도 인덱스를 탄다.

문제는 패턴 검색이 들어가는 경우다.




문제: LIKE 검색

문제는 LIKE 기반의 와일드카드 검색을 수행할 경우다.
PostgreSQL의 경우에는 LIKE를 써도 되는 경우에는 인덱스를 잘 태워주는데, SQLIte에는 그런 유도리가 없다.

무조건 인덱스를 무시하고 풀스캔을 타게 된다.
구현 수준에서 문제가 없는 startWith 패턴이라도 풀스캔이고, 와일드카드 문자가 없어도 풀스캔이다.

그래서 인덱스를 활용한 필터링이 필요하다면, LIKE는 아예 쓰지 않고 봉인하는 것이 옳다.




prefix 검색: GLOB 연산자

LIKE 대신에 GLOB 연산자를 이용해 패턴 검색을 적용한다면 인덱스를 태울 수 있다.
와일드카드 문자는 %이 아닌 *이다.

대신에 제약사항이 몇개 추가되긴 한다.
정렬을 기반으로 거르기 때문에 일단 정렬 기준으로 필터링되어서 나오고, LIKE와 다르게 대소문자를 엄격하게 구분한다는 것이다.

단, GLOB의 경우에도 인덱스를 제대로 태우는건 와일드카드가 중간에 있거나 맨 끝에 있는 경우다.
머리에 붙어있는 와일드카드는 인덱스를 태우지 못한다.

그래서 SQLite의 경우에는 완전 contains 검색을 구현하기에는 기능이 충분하지 못하다.
PostgreSQL의 경우에는 pg_trgrm 같은 플러그인으로 유연한 인덱싱을 제공하는데, SQLite에는 그런것까진 없다.



참조
https://www.devkuma.com/docs/sqlite/glob/
https://www.sqlitetutorial.net/sqlite-glob/