Chỉ mục cho mảng và các toán tử (`@>`, `<@`, `&&`) để tìm kiếm nhanh

Mảng trong PostgreSQL cho phép lưu nhiều giá trị trong một ô của bảng. Cực kỳ tiện khi cần nhóm dữ liệu liên quan, ví dụ như danh sách tag cho một bài viết hoặc các loại sản phẩm. Nhưng mà, khi bắt đầu tìm kiếm, lọc hoặc kiểm tra giao nhau giữa các mảng, tốc độ có thể tụt dốc không phanh. Đó là lý do chỉ mục cho mảng là cứu tinh. Chỉ mục giúp tăng tốc các thao tác như:

• kiểm tra xem mảng có chứa phần tử cụ thể không,
• tìm các mảng chứa các phần tử chỉ định,
• kiểm tra giao nhau giữa các mảng.

Các toán tử làm việc với mảng

Trước khi đi sâu vào tạo chỉ mục, cùng làm rõ các toán tử cơ bản để thao tác với mảng nhé:

@> (contains) — kiểm tra xem mảng có chứa tất cả phần tử của mảng khác không.

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Ở đây mình tìm các khóa học có tag "SQL".

<@ (is contained by) — kiểm tra xem một mảng có nằm trong mảng khác không.

SELECT *
FROM courses
WHERE ARRAY['PostgreSQL', 'SQL'] <@ tags;

Ở đây mình tìm các khóa học mà tag của nó bao gồm tất cả phần tử trong ARRAY['PostgreSQL', 'SQL'].

&& (overlap) — kiểm tra xem hai mảng có giao nhau không.

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Query này sẽ tìm các khóa học có ít nhất một tag là "NoSQL" hoặc "Big Data".

Chỉ mục giúp gì?

Giả sử bạn có bảng courses với hàng triệu dòng, và bạn chạy query dùng một trong các toán tử trên. Không có chỉ mục, PostgreSQL sẽ phải duyệt từng dòng một — thao tác này lâu cực kỳ (nhất là nếu bạn kiên nhẫn như dev chờ code compile).

Nhờ chỉ mục, bạn sẽ không phải chịu cảnh đó. PostgreSQL có hai loại chỉ mục phù hợp cho mảng:

1. GIN (Generalized Inverted Index) — lựa chọn tốt nhất cho mảng.
2. BTREE — dùng để so sánh toàn bộ mảng.

Ví dụ: Tạo chỉ mục cho mảng

Tạo một bảng nhỏ với mảng để test thực tế nhé.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

Thêm vài dòng dữ liệu:

INSERT INTO courses (name, tags)
VALUES
    ('Nhập môn SQL', ARRAY['SQL', 'PostgreSQL', 'Cơ sở dữ liệu']),
    ('Làm việc với Big Data', ARRAY['Hadoop', 'Big Data', 'NoSQL']),
    ('Lập trình Python', ARRAY['Python', 'Web', 'Dữ liệu']),
    ('Khóa học PostgreSQL', ARRAY['PostgreSQL', 'Advanced', 'SQL']);

Bảng sẽ trông như này:

Không có chỉ mục: tìm kiếm chậm

Giờ thử tìm tất cả khóa học có tag SQL nhé.

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Query này chạy được, nhưng nếu dữ liệu nhiều thì sẽ cực kỳ chậm. PostgreSQL sẽ chạy kiểu Sequential Scan, tức là duyệt từng dòng một.

Kết quả ví dụ:

Tạo chỉ mục GIN

Để tăng tốc tìm kiếm, tạo chỉ mục kiểu GIN nhé:

CREATE INDEX idx_courses_tags
ON courses USING GIN (tags);

Thử lại query trên:

EXPLAIN ANALYZE
SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Bây giờ PostgreSQL sẽ dùng chỉ mục GIN vừa tạo, tốc độ sẽ nhanh hơn rất nhiều.

Nếu trước đó là Sequential Scan thì giờ trong execution plan sẽ thấy Bitmap Index Scan:

Các toán tử hoạt động với chỉ mục như thế nào?

Ví dụ 1: Toán tử @>

Query:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Chỉ mục GIN cực kỳ hợp với toán tử này. Postgres kiểm tra nhanh dòng nào chứa phần tử chỉ định và trả về kết quả.

Kết quả:

Ví dụ 2: Toán tử &&

Query:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Toán tử này kiểm tra giao nhau giữa các mảng: nó trả về dòng mà mảng tags giao nhau với ít nhất một phần tử trong mảng truyền vào.

Chỉ mục GIN lại phát huy tác dụng — tìm kiếm nhanh kể cả khi dữ liệu lớn.

Kết quả:

Chỉ mục và tối ưu hóa

Khi làm việc với mảng, nhớ các khuyến nghị sau:

1. Dùng chỉ mục GIN để tìm kiếm trong mảng. Nhanh hơn nhiều so với duyệt từng dòng.
2. Chỉ tạo chỉ mục cho cột thực sự hay dùng trong query. Chỉ mục chiếm dung lượng và làm chậm insert, nên đừng tạo bừa bãi.
3. Phân tích query bằng EXPLAIN và EXPLAIN ANALYZE để kiểm tra chỉ mục có được dùng không.

Ví dụ: tạo chỉ mục cho mảng

Xem cách tạo chỉ mục cho từng loại thao tác với mảng và lý do thực tế nhé.

Chỉ mục cho toán tử @>

Giả sử đã có bảng courses như sau:

Để tăng tốc query với toán tử @> (mảng chứa phần tử), tạo chỉ mục GIN:

CREATE INDEX idx_courses_tags_gin
ON courses USING GIN (tags);

Chạy query:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

Kết quả:

Chỉ mục cho các toán tử @>, <@, và &&

Bảng vẫn như ví dụ trước.

Vì các toán tử @>, <@ và && đều chạy tốt với chỉ mục GIN, bạn chỉ cần tạo một chỉ mục chung để tăng tốc mọi query dùng các toán tử này:

CREATE INDEX idx_tags
ON courses USING GIN (tags);

Ví dụ query và kết quả:

• @> — kiểm tra mảng có chứa phần tử chỉ định không:

SELECT *
FROM courses
WHERE tags @> ARRAY['SQL'];

---

• <@ — kiểm tra mảng có nằm trong mảng khác không:

SELECT *
FROM courses
WHERE tags <@ ARRAY['SQL', 'PostgreSQL', 'Advanced', 'Big Data', 'NoSQL', 'Python'];

---

• && — kiểm tra giao nhau giữa các mảng:

SELECT *
FROM courses
WHERE tags && ARRAY['NoSQL', 'Big Data'];

Thử cái gì khó hơn chút

Tạo query trả về các khóa học mà tag có ít nhất một phần tử giao với ['Python', 'SQL', 'NoSQL']:

SELECT *
FROM courses
WHERE tags && ARRAY['Python', 'SQL', 'NoSQL'];

Kết quả:

Với chỉ mục GIN, query này chạy cực nhanh kể cả khi bảng có hàng triệu dòng.

Lỗi thường gặp khi làm việc với mảng

Không dùng chỉ mục: nếu trong EXPLAIN bạn thấy Seq Scan, kiểm tra xem đã tạo chỉ mục chưa và toán tử bạn dùng có hỗ trợ chỉ mục không.

Ít dùng mảng: nếu cột mảng hiếm khi dùng trong query hoặc hay update, chỉ mục có thể chiếm chỗ mà không giúp ích gì nhiều.

Chỉ mục dư thừa: chỉ mục tốn dung lượng và làm chậm ghi dữ liệu, nên chỉ tạo cái thực sự cần và sẽ dùng trong query thôi.

Giờ bạn đã có đủ công cụ để làm việc hiệu quả với mảng trong PostgreSQL — tăng tốc query bằng các toán tử @>, <@, && và chỉ mục GIN. Đừng ngại thử nghiệm trên dữ liệu của bạn nhé!