Tìm kiếm toàn văn trong SQLite: bảng ảo FTS5 và MATCH

LIKE không theo kịp khi dữ liệu lớn

Nếu từng tìm kiếm văn bản trong SQLite, có lẽ bạn đã dùng LIKE '%word%'. Cách này chạy được trên bảng nhỏ và sụp đổ trên bảng lớn. Không có index nào giúp được: SQLite phải quét từng hàng, chuyển về chữ thường rồi tìm chuỗi con. Ranh giới từ, xếp hạng, truy vấn nhiều từ và tìm theo tiền tố đều do bạn tự xử lý.

FTS5 là câu trả lời có sẵn. Đây là một loại bảng ảo (virtual table) duy trì chỉ mục đảo ngược trên các cột văn bản, hiểu một ngôn ngữ truy vấn nhỏ và xếp hạng kết quả bằng BM25. Nó đi kèm SQLite theo mặc định, không cần cài thêm extension nào.

Tạo bảng FTS5

Bạn tạo bảng FTS5 bằng CREATE VIRTUAL TABLE ... USING fts5(...), liệt kê các cột văn bản cần đánh index:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLite full-text search', 'FTS5 is fast and built in.'),
  ('Indexes in SQLite', 'B-trees power most lookups in SQLite.'),
  ('Why use SQLite', 'It runs in-process and stores data in one file.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Có ba điều đáng chú ý. Các cột không có kiểu: FTS5 coi mọi thứ là văn bản. Toán tử MATCH đặt trên tên bảng (posts MATCH ...), không phải trên một cột. Và truy vấn không phân biệt hoa thường và được tách token, nên 'sqlite' tìm thấy SQLite trong bất kỳ hàng nào.

Ngôn ngữ truy vấn của MATCH

MATCH nhận nhiều hơn một từ. Chuỗi truy vấn có ngữ pháp nhỏ của riêng nó:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Full-text search', 'FTS5 supports phrase and prefix queries.'),
  ('Indexes', 'A B-tree index speeds up equality lookups.'),
  ('Triggers', 'Use triggers to keep FTS in sync with the source table.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

Ý nghĩa của từng truy vấn:

• 'fts5 AND prefix': cả hai token đều phải xuất hiện (theo thứ tự bất kỳ, ở bất kỳ đâu trong hàng).
• '"keep fts"': đúng cụm từ, đúng thứ tự đó.
• 'trig*': tìm theo tiền tố, khớp trigger, triggers, trigonometry...
• 'index NOT trigger': có index, không có trigger.

Bạn cũng có thể nhắm vào một cột duy nhất bằng column:term, ví dụ 'title:sqlite'. Ngữ pháp đầy đủ còn có dấu ngoặc để nhóm và OR cho các lựa chọn thay thế, giống những gì bạn mong đợi ở một công cụ tìm kiếm.

Xếp hạng với BM25

Mặc định, FTS5 gắn một cột ẩn rank vào mỗi hàng. Đó là điểm liên quan BM25: số càng thấp thì càng khớp. Sắp xếp theo cột này để lấy kết quả liên quan nhất lên đầu:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite tutorial', 'Learn SQLite from scratch.'),
  ('SQLite vs Postgres', 'Comparing SQLite to Postgres for small apps.'),
  ('SQLite SQLite SQLite', 'A post that mentions SQLite many times.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Muốn một số cột có trọng số cao hơn các cột khác? Gọi bm25() với trọng số, mỗi cột một giá trị theo thứ tự khai báo:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite indexes', 'A short note about B-trees.'),
  ('Postgres notes', 'Includes a paragraph about sqlite for comparison.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

Bài viết đầu tiên thắng vì sqlite xuất hiện trong title (trọng số 10×) chứ không chỉ trong body (trọng số 1×). Hãy chọn trọng số phù hợp với cách ứng dụng của bạn thực sự muốn xếp hạng.

Giữ index đồng bộ

Bảng FTS5 đơn giản nhất lưu bản sao văn bản của riêng nó. Điều đó ổn với dữ liệu kiểu log mà bạn chỉ thêm vào, nhưng hầu hết ứng dụng đã có một bảng thật và muốn FTS theo dõi bảng đó. Cách làm gọn gàng là một bảng FTS external content cộng với ba trigger.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('SQLite triggers', 'How to use AFTER INSERT triggers.'),
  ('FTS5 patterns', 'External content keeps the data in one place.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' bảo FTS5 không tự lưu văn bản mà lấy từ bảng articles khi cần. Các trigger sao chép thao tác ghi sang index FTS. Giờ articles là nguồn dữ liệu gốc, còn articles_fts chỉ là cấu trúc tìm kiếm nằm bên cạnh.

Câu lệnh trông lạ mắt INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) là cú pháp lệnh của FTS5 để bảo index xóa một hàng.

Đoạn trích và tô sáng

Kết quả tìm kiếm thường cần một đoạn xem trước với các từ khớp được làm nổi bật. FTS5 có hai hàm cho việc này:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Getting started', 'SQLite is a small, fast, embedded database engine that stores data in a single file on disk.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(table, column_index, open, close) trả về toàn bộ văn bản của cột, với các token khớp được bọc lại.
• snippet(table, column_index, open, close, ellipsis, token_count) trả về một đoạn trích ngắn xoay quanh chỗ khớp.

Chỉ số cột bắt đầu từ 0, theo thứ tự khai báo. Đây là những viên gạch để dựng hiệu ứng "tô vàng từ khóa khớp" mà giao diện tìm kiếm nào cũng cần.

Những cạm bẫy nên biết

Vài điều hay khiến người dùng vấp:

• MATCH chỉ dùng được trên bảng FTS. Bạn không thể MATCH một cột thường. Nếu cần tìm kiếm trên một bảng có sẵn, hãy dùng cách external content ở trên.
• Đừng quên sắp xếp theo rank. Không có nó, FTS5 trả về các hàng theo thứ tự lưu trữ, chẳng liên quan gì đến mức độ phù hợp.
• Tokenizer rất quan trọng. Tokenizer mặc định (unicode61) tách theo ranh giới từ Unicode và chuyển về chữ thường. Để tìm theo gốc từ (run khớp running), dùng tokenizer porter: USING fts5(body, tokenize='porter').
• FTS5 không chịu được lỗi chính tả. Nó khớp theo tiền tố chứ không khớp gần đúng. Nếu cần tính năng "có phải bạn muốn tìm...", đó là một lớp nằm trên FTS5.
• Bảng contentless (content='') nhỏ hơn nhưng mất dữ liệu gốc. Bạn có thể tìm kiếm trên đó nhưng không lấy lại được văn bản ban đầu, chỉ lấy được rowid. Hữu ích khi bạn lưu văn bản ở nơi khác.

Tiếp theo: window function

FTS5 lo phần tìm kiếm văn bản. Trang tiếp theo nói về một dạng truy vấn nâng cao khác: window function, cho phép bạn tính tổng lũy kế, xếp hạng và phân tích theo nhóm mà không gộp các hàng thành giá trị tổng hợp.