Cách tận dụng JSON trong SQLite với tốc độ đầy đủ của chỉ mục: truy vấn hiệu năng cao bằng cột được sinh

(dbpro.app)

11 điểm bởi GN⁺ 2025-12-13 | 2 bình luận | Chia sẻ qua WhatsApp

Giới thiệu cách tận dụng tính năng JSON của SQLite để lưu nguyên tài liệu JSON và trích xuất các trường cần thiết thành cột được sinh ảo (virtual generated columns) để lập chỉ mục
Dùng hàm json_extract để xử lý dữ liệu bên trong JSON như cột thông thường, cho phép truy vấn với tốc độ chỉ mục B-tree
Mỗi khi cần một kiểu truy vấn mới, có thể mở rộng bằng cách thêm cột và chỉ mục mà không cần di trú dữ liệu
Cách làm này đồng thời đạt được sự linh hoạt của dữ liệu không lược đồ và hiệu năng của cơ sở dữ liệu quan hệ
Được nhấn mạnh như một mẫu thực tiễn mang lại cấu trúc gọn gàng và hiệu năng cao cho các lập trình viên dùng SQLite

Kết hợp SQLite với tính năng JSON

SQLite hỗ trợ các hàm và toán tử JSON, nhờ đó có thể lưu trữ và thao tác trực tiếp trên dữ liệu JSON
- Lưu nguyên tài liệu JSON trong một cột và chỉ trích xuất các thông tin cần thiết thành cột ảo
- Cách này cho phép xử lý dữ liệu linh hoạt mà không cần định nghĩa lược đồ trước
Trong vài tháng gần đây, đội ngũ DB Pro đã sử dụng SQLite một cách tập trung và kiểm chứng các tính năng này trong thực tế
- Nếu được cấu hình phù hợp, SQLite có thể vận hành ổn định ngay cả trong môi trường production

Cột được sinh ảo (Generated Columns)

Dùng json_extract để định nghĩa các giá trị cụ thể bên trong JSON thành cột được sinh ảo
- Các cột này không lưu dữ liệu thực mà được tính toán tại thời điểm truy vấn để dùng ngay
- Không cần quy trình backfill hay sao chép dữ liệu riêng
Ví dụ, có thể xây dựng cấu trúc trích xuất một trường cụ thể từ dữ liệu JSON và xử lý nó như một cột

Thêm chỉ mục và cải thiện hiệu năng

Khi thêm chỉ mục vào cột ảo, dữ liệu JSON cũng có thể được tìm kiếm với tốc độ chỉ mục B-tree như cột thông thường
- Cột ảo đã được lập chỉ mục cho hiệu năng tương đương cột trong cơ sở dữ liệu quan hệ
Cách tiếp cận này vẫn cho phép tìm kiếm nhanh ngay cả khi kích thước dữ liệu JSON tăng lên

Bổ sung các kiểu truy vấn mới

Nếu sau này cần tìm kiếm theo một trường mới, chỉ cần thêm cột ảo và chỉ mục mới
- Ví dụ: trích xuất trường user_id và tạo chỉ mục
- Không cần sửa các hàng dữ liệu hiện có hay thực hiện di trú
Nhờ đó có thể mở rộng khả năng truy vấn ngay lập tức mà không phải thay đổi cấu trúc dữ liệu

Ưu điểm và ý nghĩa của mẫu này

Mẫu này kết hợp sự linh hoạt của việc lưu JSON không lược đồ với hiệu năng chỉ mục của DB quan hệ
- Không cần quyết định trước chiến lược lập chỉ mục ngay ở giai đoạn thiết kế ban đầu
- Có thể thêm cột và chỉ mục để tối ưu đúng vào thời điểm cần thiết
Đây được xem là một cách xử lý dữ liệu vừa đơn giản vừa mạnh mẽ cho các lập trình viên sử dụng SQLite
DB Pro cũng cho biết sẽ tiếp tục giới thiệu thêm các bài viết về nhiều tính năng khác nhau của SQLite

2 bình luận

GN⁺ 2025-12-13

Ý kiến trên Hacker News

Có thể mã hóa trực tiếp tài liệu JSON thành B-tree đã được tuần tự hóa
Làm vậy sẽ cho phép truy vấn trực tiếp các trường bên trong với tốc độ đã được lập chỉ mục, đồng thời không cần parse vì bản thân tài liệu đã được lập chỉ mục sẵn
Định dạng này được gọi là Lite³. Đây là dự án mà chính tôi đang thực hiện
Liên kết GitHub
- Thật sự rất tuyệt! Tôi thích Rkyv nhưng nó đòi hỏi Rust nên hơi nặng nề với các dự án nhỏ
  Tôi đặc biệt thích việc Lite³ hỗ trợ dữ liệu nhị phân qua lite3_val_bytes
- Tôi từng thắc mắc Lite³ khác gì với JSONB của PostgreSQL
  JSONB mã hóa cả độ dài mảng và offset để cân bằng giữa hiệu quả nén và tốc độ
  Lite³ cho phép cập nhật in-place, nhưng cần “vacuum” định kỳ để tránh lưu lại dữ liệu nhạy cảm
  JSONB thì khó cập nhật nếu không mã hóa lại, còn Lite³ có thể dọn dẹp khá đơn giản chỉ bằng cách duyệt cấu trúc
  Về khả năng nén thì có lẽ JSONB tốt hơn, nhưng tôi nghĩ thiết kế của Lite³ là một cách tiếp cận rất thông minh
  Tôi cũng đang bảo trì một trình biên dịch ASN.1 nên rất quan tâm đến các định dạng tuần tự hóa kiểu này. Lite³ đã cho tôi thêm vài ý tưởng mới
- Sẽ thật tuyệt nếu có một bản triển khai bằng Rust
Tôi rất thích SQLite, nhưng khi làm phân tích thì tôi dùng DuckDB thường xuyên hơn
DuckDB dùng một tệp đơn như SQLite nhưng xử lý các tập dữ liệu lớn cực kỳ nhanh
Trên MacBook M2, ngay cả khi làm việc với 20 triệu bản ghi nó vẫn rất nhanh
Ví dụ, có thể đọc trực tiếp tệp JSON bằng truy vấn sau
```
SELECT avg(sale_price), count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';
```
Ngoài ra còn có thể nạp cột kiểu JSON và dùng cú pháp col->>'$.key' theo kiểu Postgres
- Tôi tò mò liệu truy vấn đầu tiên có đang lập chỉ mục tức thời các tệp JSON trong hệ thống tệp hay không
- Nếu dùng DuckDB cùng công cụ trực quan hóa pygwalker thì có thể phân tích hàng triệu bản ghi chỉ trong vài giây
  Tuy vậy, so với SQLite thì hơi thiếu công bằng. SQLite phù hợp để xây dựng hệ thống, còn DuckDB đúng hơn là dành cho phân tích
  Khi triển khai lên nhiều nền tảng, DuckDB có phần hơi rắc rối
- Tuyệt đối nên tránh lưu tập dữ liệu mà không nén. DuckDB hỗ trợ nhiều định dạng nén khác nhau
Tôi từng nghĩ dùng Generated Column để tối ưu hiệu năng JSON là cách làm khá phổ biến
Tôi cũng từng làm vậy trên Postgres để giữ các khóa trong cột JSON như khóa ngoại. Hơi “tà đạo” một chút nhưng hoạt động tốt
- Nếu là Postgres thì chẳng phải có thể lập chỉ mục trực tiếp trên các trường bên trong JSONB sao?
  Ví dụ
```
CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
```
  Blog tham khảo
- Nhưng kiểu này rốt cuộc nhiều khi vẫn nên tách ra thành bảng key/value thì hơn
  Nếu schema JSON thay đổi, việc parse hoặc migration có thể thất bại
- Thật ra cũng không hẳn là cách làm “tà đạo”. Chỗ nào cần thì dùng cấu trúc quan hệ đã chuẩn hóa, phần còn lại để jsonb xử lý là được
- Tôi từng thắc mắc liệu có thể dùng cột VIRTUAL thay vì STORED không, và ví dụ này đúng là dùng VIRTUAL
Gần đây tôi biết đến kỹ thuật này qua một ví dụ tối ưu hóa do Claude Code đề xuất
Tôi đã bỏ lỡ nó vì không biết các tính năng mới của SQLite, và mức cải thiện hiệu năng là khá lớn
Bài học rút ra là, ngay cả với công cụ quen thuộc, vẫn nên đọc lại tài liệu định kỳ
- Đọc lại manual đôi khi thật sự có thể mang đến ngộ ra bất ngờ
Tôi viết bài blog này sau khi thấy bình luận của bambax trên HN vào năm 2023
Liên kết bình luận gốc
Có thể tạo chỉ mục mà không cần chiếu trực tiếp JSON, nhưng computed column giúp truy vấn đơn giản hơn
Trước MS-SQL 2025(v17), hỗ trợ JSON còn hạn chế nên cách này gần như bắt buộc
- Nếu chỉ dùng computed column thay vì truy vấn trực tiếp JSON, sẽ không vô tình viết phải truy vấn không dùng chỉ mục
- Tôi từng nghe về tính năng này ở một hội nghị DBA địa phương, nhưng lúc đó không cảm thấy nó là thay đổi lớn gì
Tôi mở bài viết trên HN và thấy bình luận của mình được trích dẫn, mà lại chính là chủ đề của bài viết, đúng là một trải nghiệm lạ
Khi thấy dòng “Cảm ơn, bambax!” tôi đã mỉm cười. SQLite thật sự là một công cụ tuyệt vời
- Thực ra bình luận đã truyền cảm hứng nằm ở liên kết này
Khá thú vị, nhưng chẳng phải có thể dùng "Index On Expression" của SQLite sao?
Ví dụ CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
Tuy nhiên chỉ cần cú pháp JSON path khác đi một chút là có thể không dùng được chỉ mục. Ngược lại, Virtual Generated Column luôn đảm bảo dùng chỉ mục
- Có thể dùng chỉ mục biểu thức cùng với view để bảo đảm biểu thức khớp nhau
  Ví dụ: dự án recordlite
- Chỉ cần cú pháp JSON path khác đi một chút là chỉ mục có thể hỏng
  Ví dụ json_extract(data, "$.type") và data -> '$.type' được nhận diện khác nhau
  Nói cách khác, nếu biểu thức trong mệnh đề WHERE thay đổi thì chỉ mục sẽ trở nên vô dụng
- Đây là một cách giải quyết đơn giản và nhanh. Việc truy vấn và chỉ mục phải khớp nhau vốn luôn là sự thật
- Tính năng chỉ mục biểu thức là một tính năng tương đối mới của SQLite, được thêm từ SQLite 3.9.0 (2015-10-14)
Tôi mong các lập trình viên hạn chế việc đưa toàn bộ dữ liệu vào cột JSON(B) dù schema đã nhất quán
Việc thiết lập chỉ mục, quản lý ràng buộc sẽ khó hơn, và khi dùng thực tế còn có thêm overhead đáng kể
- Cột JSON phát huy tác dụng khi xử lý dữ liệu khó biểu diễn thành bảng như cấu trúc cây
  Ví dụ trong môi trường Haskell+TypeScript, việc tuần tự hóa cấu trúc lồng nhau phức tạp thành JSON thuận tiện hơn nhiều
- JSON(B) hữu ích khi cần chứa dữ liệu khác kiểu trong cùng một collection
  Ví dụ: lưu kết quả từ nhiều payment processor vào một bảng, hoặc xử lý các thuộc tính khác nhau theo từng mặt hàng trên trang rao vặt
  Trong C# hay JS/TS, nếu dùng thêm các công cụ kiểm tra kiểu như Zod, OpenAPI thì việc quản lý cũng dễ hơn
- Với JSON đơn giản thì chuẩn hóa vẫn tốt hơn, nhưng các phản hồi API phức tạp mà bung ra thành bảng sẽ thành địa ngục JOIN
  Cuối cùng vẫn là bài toán cân bằng giữa khả năng bảo trì và hiệu năng. Điểm chính của bài này là ngay cả với JSON cũng có thể lập chỉ mục dễ dàng
- Trong các trường hợp như dữ liệu cảm biến, nơi cần xử lý toàn bộ cấu trúc cây cùng một lúc, cột JSON đơn giản hơn nhiều
  Hiệu năng đọc có thể bù đủ bằng chỉ mục
- Chuẩn hóa hoàn toàn nhiều khi không hiệu quả
  Ví dụ trong hệ thống cấu hình giá sản phẩm, việc biểu diễn các quy tắc giảm giá đặc thù theo từng khách hàng bằng JSON sẽ linh hoạt hơn nhiều
Nếu dùng XML thay vì JSON thì đây thực chất là cùng một mô hình với cơ sở dữ liệu tài liệu (document store) của thập niên 90~00
Dữ liệu được parse khi chèn/cập nhật, còn khi truy vấn thì chỉ truy cập chỉ mục
Việc SQLite cung cấp sẵn kiểu chức năng này thật sự rất thú vị

iolothebard 2025-12-14

Vào cuối thế kỷ 20… từng có thứ gọi là cơ sở dữ liệu vạn năng… (bây giờ thì đúng, còn khi đó thì sai.)