Chỉ mục kết hợp trong SQLite: thứ tự cột và quy tắc tiền tố trái

Một chỉ mục, nhiều cột

Chỉ mục kết hợp (composite index), đôi khi gọi là chỉ mục nhiều cột, là một chỉ mục duy nhất được xây trên hai cột trở lên. Bạn tạo nó bằng cách liệt kê các cột theo thứ tự:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    total REAL NOT NULL
);

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

Chỉ mục idx_orders_customer_status lưu các mục được sắp xếp trước tiên theo customer_id, rồi theo status trong từng khách hàng. Thứ tự đó là toàn bộ câu chuyện: mọi điều khác về chỉ mục kết hợp đều bắt nguồn từ đây.

Mô hình dễ hình dung: một cuốn danh bạ đã sắp xếp

Hãy hình dung một cuốn danh bạ điện thoại kiểu cũ. Các mục được sắp theo họ, và trong mỗi họ thì sắp theo tên. Đó chính xác là hình dạng của một chỉ mục trên (last_name, first_name).

Có những lần tra cứu nhanh, có những lần thì không:

• "Tìm mọi người họ Patel": dễ, tất cả người họ Patel nằm cạnh nhau.
• "Tìm Priya Patel": dễ, nhảy tới Patel rồi dò tới Priya.
• "Tìm mọi người tên Priya": chậm, bạn phải lật qua từng trang. Những người tên Priya nằm rải rác ở mọi họ.

Chỉ mục kết hợp trong SQLite hoạt động y như vậy. Cột đầu tiên là khóa sắp xếp chính; cột thứ hai chỉ sắp xếp các mục có cùng giá trị ở cột đầu.

Quy tắc tiền tố bên trái

SQLite chỉ dùng được chỉ mục kết hợp cho một truy vấn khi mệnh đề WHERE ràng buộc một tiền tố bên trái (leftmost prefix) của các cột. Với chỉ mục trên (a, b, c):

• Lọc theo a: có dùng chỉ mục.
• Lọc theo a và b: có dùng chỉ mục.
• Lọc theo a, b và c: có dùng chỉ mục.
• Chỉ lọc theo b, hoặc chỉ theo c, hoặc theo b và c: chỉ mục không được dùng.

Bạn có thể kiểm chứng trực tiếp bằng EXPLAIN QUERY PLAN:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

Kế hoạch đầu tiên báo SEARCH events USING INDEX idx_events_user_kind_time. Kế hoạch thứ hai quay về SCAN events: chỉ lọc theo kind là bỏ qua cột dẫn đầu user_id, nên chỉ mục vô dụng với truy vấn đó.

Thứ tự cột là một quyết định thiết kế

Vì tiền tố bên trái quan trọng, thứ tự bạn liệt kê cột trong CREATE INDEX là một lựa chọn thật sự, không phải chuyện phong cách. Hai quy tắc kinh nghiệm:

1. Đặt cột bạn lọc thường xuyên nhất lên đầu. Cột đó mở khóa chỉ mục cho nhiều loại truy vấn nhất.
2. Đặt cột so sánh bằng trước cột so sánh theo khoảng. SQLite có thể đi thẳng vào chỉ mục bằng =, rồi quét một đoạn liên tục bằng <, > hoặc BETWEEN, nhưng chỉ trên cột được dùng cuối cùng.

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- Good: equality on region first, range on sold_at second.
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

Kế hoạch hiển thị SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?). SQLite nhảy thẳng tới region = 'EU', rồi đi tiếp qua khoảng ngày. Đảo thứ tự cột thành (sold_at, region) thì cùng truy vấn đó phải quét mọi hàng trong khoảng ngày và kiểm tra lại region cho từng hàng.

Chỉ mục kết hợp hay nhiều chỉ mục một cột

Một câu hỏi hay gặp: nên tạo một chỉ mục trên (a, b), hay hai chỉ mục riêng trên a và b?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- Option A: one composite index.
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- Option B (alternative): two single-column indexes.
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

Với bộ lọc kết hợp, chỉ mục kết hợp nhanh hơn: SQLite đi thẳng tới các mục (project_id, state) khớp. Với hai chỉ mục một cột, SQLite thường chọn một chỉ mục, dùng nó để thu hẹp số hàng, rồi kiểm tra lại cột còn lại trên từng hàng khớp. Đôi khi nó có thể giao hai chỉ mục với nhau, nhưng chỉ mục kết hợp là lời giải gọn hơn khi các cột được truy vấn cùng nhau.

Nếu project_id và state cũng được truy vấn riêng lẻ, có thể bạn cần cả hai: chỉ mục kết hợp cho bộ lọc kết hợp, cộng thêm một chỉ mục một cột trên state cho các truy vấn chỉ lọc theo nó.

Covering index

Khi một chỉ mục chứa mọi cột mà truy vấn cần, cả cột dùng để lọc lẫn cột được chọn, SQLite trả lời được truy vấn mà không cần đụng tới bảng. Đó là covering index, và đó là mức nhanh nhất mà một truy vấn có thể đạt.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, 'first'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, 'rush');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

Kế hoạch hiển thị USING COVERING INDEX idx_invoices_cover. Truy vấn đọc issued_at và total thẳng từ chỉ mục; không cần notes và id, nên bảng không bao giờ được mở. Thêm một cột vào chỉ mục kết hợp chỉ để bao trọn một truy vấn nóng là một sự đánh đổi đáng giá khi truy vấn đó chạy liên tục.

Ràng buộc UNIQUE trên nhiều cột

Chỉ mục kết hợp còn đảm bảo tính duy nhất trên tổ hợp cột. Rất hữu ích khi không cột nào duy nhất một mình, nhưng tổ hợp của chúng thì phải duy nhất:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- ok, different course
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- fails: pair repeats

Lệnh insert thứ ba gây lỗi UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. Cặp giá trị đó đã có trong chỉ mục, nên SQLite từ chối bản trùng.

Những cái bẫy nên biết

• OR giữa các cột không dẫn đầu chặn chỉ mục. WHERE a = 1 OR b = 2 trên chỉ mục (a, b) thường hoàn toàn không dùng được chỉ mục, vì SQLite phải xét riêng từng nhánh.
• Hàm áp lên cột có chỉ mục làm vô hiệu chỉ mục. WHERE lower(email) = 'x' sẽ không dùng chỉ mục trên email. Thay vào đó hãy tạo chỉ mục trên biểu thức, hoặc chuẩn hóa dữ liệu khi insert.
• Chỉ mục không miễn phí. Mỗi chỉ mục được cập nhật ở mọi lệnh INSERT, UPDATE (trên các cột có chỉ mục) và DELETE. Ba chỉ mục kết hợp trên một bảng ghi nhiều có thể chiếm phần lớn chi phí ghi.
• Chạy ANALYZE sau khi tạo chỉ mục. Bộ lập kế hoạch của SQLite dùng số liệu thống kê do ANALYZE thu thập để chọn giữa các chỉ mục ứng viên. Không có số liệu đó, nó quay về các quy tắc ước lượng không phải lúc nào cũng tối ưu.

Quy trình thực tế

Khi tối ưu một truy vấn chậm, vòng lặp thường như sau:

1. Chạy EXPLAIN QUERY PLAN trên truy vấn để xem SQLite hiện đang làm gì.
2. Nếu nó đang quét toàn bảng, xem mệnh đề WHERE: cột so sánh bằng là gì? Cột so sánh khoảng là gì? Những cột nào được chọn?
3. Tạo chỉ mục kết hợp theo thứ tự cột so sánh bằng trước, cột khoảng sau, thêm các cột được chọn vào cuối nếu covering giúp ích.
4. Chạy ANALYZE.
5. Chạy lại EXPLAIN QUERY PLAN. Xác nhận kế hoạch đã thay đổi và chỉ mục đang được dùng.
6. Đo thời gian truy vấn trước và sau trên dữ liệu tiêu biểu.

Bỏ qua bước 6 là tự chuốc rủi ro. Một chỉ mục trông đúng trong kế hoạch vẫn có thể chậm hơn trong thực tế nếu bảng nhỏ hoặc bộ lập kế hoạch chọn đường khác.

Tiếp theo: chỉ mục một phần

Chỉ mục kết hợp bao trùm mọi hàng trong bảng. Nhưng thường chỉ một tập nhỏ các hàng là quan trọng: ticket đang mở, job chưa xử lý, bản ghi chưa bị xóa. Chỉ mục một phần (partial index) cho phép bạn chỉ đánh chỉ mục những hàng đó, với một mệnh đề WHERE nằm ngay trong chỉ mục. Đó là nội dung của trang tiếp theo.