[Electron] 2.Sqlite3 DB Fts5 설계 - 레시피 일렉트론 앱

Fts5 테이블 설계

 검색 조건은 전체 컬럼 검색과 각 컬럼 검색을 지원할 생각이다. Sqlite는 fts 라는 가상테이블을 통해 Full text search를 지원한다. Sqlite의 fts5 테이블은 일반적으로 WHERE 절에 붙이는 LIKE 절 보다 훨씬 간편한 조건 문법과 좋은 검색 성능을 제공한다. 전체 검색 또한 모든 컬럼을 일일히 탐색할 필요 없이 간단하게 가능하다는 이점이 있다.

-- full text search table
CREATE VIRTUAL TABLE tb_fts_recipe
USING FTS5(
title,
ingredients,
instructions,
id,
tokenize = 'porter'
);

 

 먼저 CREATE 문으로 fts5 테이블을 생성한다. 각 컬럼은 원본 테이블인 tb_recipe 테이블 중 검색 범위에 포함 시킬 컬럼으로 정한다. 컬럼 순서를 정할 때는 후에 bm25 문법을 사용할 때, 검색 점수에 가중치를 주기 위해 가장 중요한 컬럼 2개를 선정해야한다. 레시피 테이블에서는 title(제목)과 ingredients(재료)를 선정했다. 가상 테이블임으로 데이터 타입, 키 등은 설정할 필요가 없다. 대신 데이터의 토큰화를 위한 tokenize 값을 설정해줘야 한다.

 

Sqlite3의 fts5 테이블에서는 4개의 tokenizer가 있다.

• unicode61
• ascii
• porter
• trigram

이중에서 porter 토크나이저를 선택했다. porter stemming 알고리즘을 이용한 토크나이저로서 "correction" 와 같은 검색어가 "corrected" 또는 "correcting" 와 같은 유사한 단어와 일치하여 취급한다. 레시피 데이터는 모두 영문으로서 porter 알고리즘에 적합하다. 여기서 사용할 레시피 데이터에서 예를 들면 ‘bowls’ 를 SELECT 할 때 ‘bowl’ 이라는 단어가 있는 레코드 또한 함께 반환한다.

 

💡 Sqlite3 fts5 참고 docs

EN: https://www.sqlite.org/fts5.html#tokenizers

SQLite FTS5 Extension

KR: https://runebook.dev/ko/docs/sqlite/fts5

SQLite FTS5 확장

-- insert title
INSERT INTO tb_fts_recipe(
	title,
	ingredients,
	instructions,
	id
)
SELECT 
	title,
	ingredients,
	instructions,
	id
FROM tb_recipe;

 

fts5 테이블을 생성한 후 원본 테이블 tb_recipe의 모든 데이터를 INSERT 한다. fst5 테이블에 INSERT 됨 과 동시에 토크나이저를 이용하여 토큰화되어 저장된다. 또한 사진2와 같이 자동 생성된 부속 테이블에도 데이터가 저장된다.

사진2. fts5 테이블과 부속 테이블들

 

Fts5 테이블 테스트

MATCH 검색

 이제 fts5 테이블의 검색을 테스트해보겠다. fts5 테이블에서는 일반적으로 일치하는 텍스트를 찾기위해 쓰는 구문인 LIKE 절 대신에 MATCH 절을 사용한다. LIKE 절과 달리 %, _ 등의 예약어를 사용하지 않아도 최적의 검색결과를 도출한다. 또한 WHERE 절 뒤에 컬럼 이름 대신 테이블 이름을 입력하면 모든 필드에서의 전체 검색이 이뤄진다.

-- title 컬럼에서만 검색
SELECT *
FROM tb_fts_recipe
WHERE title MATCH 'scallops';

-- 모든 컬럼에서 검색
SELECT *
FROM tb_fts_recipe
WHERE tb_fts_recipe MATCH 'scallops';

 

사진3. title 컬럼에서만 검색

 

사진4. 모든 컬럼에서만 검색

 

사진3, 사진4와 같이 컬럼을 지정하여 검색은 물론 전체 컬럼에서도 동일한 문법으로 검색이 가능하다.

MATCH vs LIKE

일반 테이블의 LIKE 절과 검색을 비교하면, fts5 테이블 쪽이 토큰화를 통해 더 정확한 검색 결과를 도출한다. 아래 쿼리문으로 ‘and’ 검색 결과를 비교해보면 일반 테이블의 LIKE 절 검색 결과 레코드 수가 더 적은 것을 확인할 수 있다.

-- LIKE vs MATCH(fts) 비교 
-- 4555 행
SELECT title
FROM tb_recipe
WHERE title LIKE '%and%';

-- 4293행
SELECT *
FROM tb_fts_recipe
WHERE title MATCH 'and';

-- 차집합으로 비교
SELECT a.title
FROM (
	SELECT title
	FROM tb_recipe
	WHERE title LIKE '%and%'
	) a
LEFT JOIN
	(SELECT *
	FROM tb_fts_recipe
	WHERE title MATCH 'and';
	) b
ON a.title = b.title
WHERE b.title IS NULL;

검색의 목적은 ‘and’ 가 포함된 title 컬럼 값을 찾는 것이었다. 하지만 사진5와 같이 LIKE 절의 검색 결과는 Sandwiches, Island 등 단어 속에 ‘and’가 포함된 레코드도 함께 검색 결과에 포함 시키고 있다. 이는 원하지 않는 결과를 포함시킨다.

사진5. LIKE 절로 and를 검색한 결과