🪴ttuan's garden

3 min read

JSON Indexing và GIN Index - Đánh index trong thế giới phi cấu trúc

Index trên cả cột JSON chỉ giúp tìm toàn bộ JSON giống hệt - gần như vô dụng. Cần các kỹ thuật chuyên biệt.

Cách 1: Virtual Column (MySQL)

Tách giá trị JSON ra thành cột ảo, rồi đánh index bình thường:

CREATE TABLE contacts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    attributes JSON NOT NULL,
    email VARCHAR(255) AS (attributes->>"$.email") VIRTUAL NOT NULL,
    INDEX contacts_email (email)
);

Nhược: mỗi trường JSON cần index → thêm một cột ảo → bảng rối khi nhiều trường.

Cách 2: Index trên biểu thức JSON (PostgreSQL)

-- Gọn gàng hơn: không cần tạo cột ảo
CREATE INDEX contacts_email ON contacts ((attributes->>'email'));
SELECT * FROM contacts WHERE attributes->>'email' = '[email protected]';

MySQL cũng làm được nhưng phức tạp hơn (phải CAST + chỉ định COLLATE utf8mb4_bin, và query phải dùng đúng toán tử ->>").

Cách 3: GIN Index - “Index mọi thứ” trong JSON (chỉ PostgreSQL)

GIN (Generalized Inverted Index): đánh index toàn bộ nội dung JSON trong một lệnh:

CREATE INDEX contacts_attrs ON contacts USING GIN (attributes);
JSON gốc: {"email": "[email protected]", "role": "admin", "age": 30}
GIN tạo các entry NGƯỢC (giá trị → xuất hiện ở dòng nào):
│ key: "email"        │ dòng 1, 5, 12 │
│ val: "[email protected]" │ dòng 1        │
│ val: "admin"        │ dòng 1, 3     │
→ Tìm bất kỳ key hoặc value nào đều nhanh!

Nhưng GIN yêu cầu toán tử đặc biệt thay vì =:

WHERE attributes @> '{"email": "[email protected]"}';  -- "chứa"
WHERE attributes ? 'email';                            -- key tồn tại?
WHERE attributes ?| array['email', 'phone'];           -- BẤT KỲ key nào
WHERE attributes ?& array['email', 'phone'];           -- TẤT CẢ key

Index cho mảng JSON

-- PostgreSQL: dùng GIN
CREATE INDEX products_cats ON products USING GIN (categories);
WHERE categories @> '["ebook", "printed"]';
-- MySQL: multi-valued index (chỉ hỗ trợ số nguyên không dấu)
CREATE INDEX products_cats ON products ((CAST(categories AS UNSIGNED ARRAY)));
WHERE JSON_CONTAINS(categories, CAST('[17, 23]' AS JSON));

Quy tắc nhớ

Chỉ cần index 1-2 trường JSON → dùng cột ảo hoặc index biểu thức. Cần tìm kiếm linh hoạt trên nhiều trường → dùng GIN (PostgreSQL). MySQL hiện tại hạn chế hơn nhiều với JSON indexing.

Liên quan

Graph View

Backlinks