Chỉ mục — Composite, Partial, Expression và UNIQUE

Bộ dữ liệu cho bài này — bảng perf_sales

Một chỉ mục (index) hoạt động như mục lục ở cuối sách: nó cho phép bạn tìm các dòng có giá trị cột cụ thể một cách nhanh chóng.

Bài này đi qua việc tạo chỉ mục với CREATE INDEX, xây chỉ mục composite, partial, expression và UNIQUE, và dùng EXPLAIN QUERY PLAN để xác nhận chiến lược tìm kiếm thay đổi như thế nào.

Trước khi bắt đầu, hãy kiểm tra định nghĩa cột, mẫu dữ liệu, và số lượng dòng của perf_sales. (Chạy đúng các truy vấn sẽ làm xuất hiện phần giải thích.)

① Dùng PRAGMA table_info(perf_sales); để xem tên cột, kiểu, và khóa chính.

② Dùng SELECT COUNT(*) FROM perf_sales; để xác nhận tổng số dòng.

③ Xem trước 5 dòng đầu với SELECT * FROM perf_sales LIMIT 5;. Vì perf_sales có 50.000 dòng, luôn thêm LIMIT.

CREATE INDEX và EXPLAIN QUERY PLAN — chuyển quét toàn bảng sang tìm kiếm chỉ mục

Tạo chỉ mục với CREATE INDEX tên_chỉ_mục ON bảng(cột);.

Khi bạn dùng một cột có chỉ mục trong điều kiện bằng hoặc phạm vi của WHERE, bộ tối ưu có thể chọn tìm kiếm chỉ mục thay vì quét toàn bảng và nhảy thẳng tới các dòng khớp.

Kiểm tra chiến lược tìm kiếm với EXPLAIN QUERY PLAN.

Thêm tiền tố EXPLAIN QUERY PLAN cho truy vấn của bạn và bạn sẽ thấy kế hoạch về cách các dòng sẽ được tìm thay vì kết quả thật.

Không có chỉ mục bạn sẽ thấy SCAN perf_sales (còn gọi là FULL SCAN — duyệt từng dòng của bảng), và khi chỉ mục có hiệu lực bạn sẽ thấy thứ gì đó như SEARCH perf_sales USING INDEX ... (còn gọi là INDEX SCAN — thu hẹp tìm kiếm với chỉ mục).

Câu chữ chính xác của kế hoạch khác nhau tùy môi trường, nên bài này coi chạy thành công là đáp án đúng và đọc ý nghĩa của kế hoạch trong phần văn bản và giải thích.

-- Kế hoạch khi chưa có chỉ mục (ví dụ chỉ đọc riêng)
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales (duyệt từng dòng)

-- Tạo chỉ mục, rồi xem cùng kế hoạch
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)
-- Lưu ý: chạy DROP -> CREATE -> EXPLAIN liền mạch trong cùng một lần thực thi

Hãy tưởng tượng yêu cầu: "Chúng ta tìm các lần bán theo emp_id = 7 thường xuyên, nên muốn chuyển từ quét toàn bảng sang tìm kiếm qua chỉ mục." Bài tập 1 chia thành ba bước nối tiếp: ① đo thời gian chạy khi chưa có chỉ mục, ② tạo chỉ mục, ③ đo lại sau khi có chỉ mục, rồi so sánh thời gian chạy của ① và ③.

Bước ① — Ở console bên dưới, chạy SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; và EXPLAIN QUERY PLAN tương ứng, rồi xác nhận SCAN perf_sales cùng tổng thời gian chạy t1 ở cuối console. Bạn sẽ so sánh t1 với t3 của bước ③ sau.

Bước ② — Ở console bên dưới, chạy DROP INDEX IF EXISTS ix; rồi CREATE INDEX ix ON perf_sales(emp_id); để xây chỉ mục trên cột emp_id.

Lưu ý: thời gian chạy của console này là chi phí xây chỉ mục một lần, không phải chi phí tìm kiếm, nên không tính vào so sánh tốc độ thuần giữa ① và ③.

Bước ③ — Ở console bên dưới, chạy cùng SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; và EXPLAIN QUERY PLAN như bước ①, và xác nhận bạn giờ thấy SEARCH perf_sales USING INDEX ix cùng tổng thời gian chạy t3 ngắn hơn t1 của bước ①.

Chỉ mục composite — chỉ mục nhiều cột cùng nhau

Viết nhiều cột trong ngoặc — CREATE INDEX tên ON bảng(col1, col2); — tạo ra một chỉ mục composite (còn gọi là chỉ mục đa cột).

Nó tăng tốc các truy vấn dùng các điều kiện từ cột dẫn đầu trở đi, như WHERE col1 = ... AND col2 >= ....

Thứ tự cột quan trọng: chỉ mục composite không giúp các truy vấn không dùng cột dẫn đầu của nó trong mệnh đề WHERE.

Nếu mẫu truy cập của bạn cố định — chẳng hạn "bằng trên emp_id và phạm vi trên sale_date" — xây chỉ mục composite theo thứ tự đó cho phép một chỉ mục bao trùm cả hai điều kiện.

-- Chỉ mục composite (ví dụ chỉ đọc riêng)
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, sale_date);

EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE region = 'East' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING INDEX ix_demo (region=? AND sale_date>?)
-- Thu hẹp theo region (bằng), rồi thu hẹp tiếp sale_date theo phạm vi

Hãy tưởng tượng yêu cầu: "Chúng ta thường chọn một nhân viên bán theo emp_id và một thời kỳ theo sale_date, nên muốn một chỉ mục bao trùm cả hai điều kiện."

① Bắt đầu với DROP INDEX IF EXISTS ix;.

② Tạo chỉ mục composite tên ix trên perf_sales liệt kê emp_id và sale_date theo thứ tự đó.

③ Sau đó chạy EXPLAIN QUERY PLAN trên một truy vấn lọc theo emp_id = 7 và sale_date >= '2024-01-01' và chọn sale_id và amount, và xác nhận chỉ mục composite được dùng.

Chỉ mục partial và chỉ mục expression

Chỉ mục partial được xây với CREATE INDEX tên ON bảng(cột) WHERE điều_kiện; và chỉ mục một tập con của các dòng trong bảng.

Nếu bạn thường tìm các dòng có giá trị cụ thể như status = 'pending', chỉ mục cho riêng các dòng đó giữ chỉ mục nhỏ và tập trung vào các truy vấn bạn thực sự chạy.

Trong perf_sales, chỉ 5.000 dòng (10% tổng) có status = 'pending', nên chỉ mục partial với WHERE status = 'pending' rất phù hợp.

Chỉ mục expression được xây với CREATE INDEX tên ON bảng(biểu_thức); và chỉ mục kết quả của một biểu thức tính toán thay vì một cột thô.

Dùng nó cho các truy vấn áp dụng cùng biểu thức ở phía tìm kiếm, như WHERE amount / 100 = ....

Một chỉ mục thường trên một cột thường không giúp khi bạn áp dụng hàm hoặc số học lên cột đó, nhưng nếu bạn lập chỉ mục bản thân biểu thức, các tìm kiếm dùng biểu thức khớp có thể dùng nó.

-- Chỉ mục partial (ví dụ chỉ đọc riêng)
DROP INDEX IF EXISTS ix_part;
CREATE INDEX ix_part ON perf_sales(sale_date) WHERE status = 'refunded';
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE status = 'refunded' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING ... INDEX ix_part (sale_date>?)

-- Chỉ mục expression
DROP INDEX IF EXISTS ix_expr;
CREATE INDEX ix_expr ON perf_sales(qty * amount);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE qty * amount = 100000;
--> SEARCH perf_sales USING ... INDEX ix_expr (<expr>=?)

Hãy tưởng tượng yêu cầu: "Chúng ta thường tìm các lần bán pending (status = 'pending') theo phạm vi ngày, nên muốn một chỉ mục nhỏ chỉ bao các dòng đó."

① Bắt đầu với DROP INDEX IF EXISTS ix;.

② Tạo chỉ mục partial tên ix trên perf_sales(sale_date) chỉ nhắm các dòng có status = 'pending'.

③ Chạy EXPLAIN QUERY PLAN trên truy vấn lọc theo status = 'pending' AND sale_date >= '2024-01-01' và xác nhận chỉ mục partial được dùng (SEARCH ... USING INDEX ix).

④ Sau đó chạy EXPLAIN QUERY PLAN trên truy vấn dùng cùng cột nhưng với status = 'paid' (ví dụ, status = 'paid' AND sale_date >= '2024-01-01') và xác nhận chỉ mục partial không được dùng (SCAN perf_sales) vì điều kiện không khớp với vị từ của chỉ mục.

Hãy tưởng tượng yêu cầu: "Đôi khi chúng ta tìm theo giá trị bucket của amount chia cho 100 (amount / 100), nên muốn chỉ mục kết quả tính toán đó." Các tìm kiếm áp dụng số học lên cột không được lợi từ chỉ mục cột thường, nên dùng chỉ mục expression.

① Bắt đầu với DROP INDEX IF EXISTS ix;.

② Tạo chỉ mục tên ix trên biểu thức amount / 100 trên perf_sales.

③ Chạy EXPLAIN QUERY PLAN trên truy vấn lọc theo amount / 100 = 5000 và xác nhận chỉ mục expression được dùng (SEARCH ... USING INDEX ix).

④ Sau đó chạy EXPLAIN QUERY PLAN trên truy vấn lọc theo amount = 500000 mà không đi qua biểu thức (ví dụ dùng cùng cột amount nhưng với hình dạng khác chỉ mục) và xác nhận chỉ mục expression không được dùng (SCAN perf_sales).

Chỉ mục UNIQUE — chỉ mục không cho trùng lặp

Chỉ mục UNIQUE — xây với CREATE UNIQUE INDEX tên ON bảng(cột); — tăng tốc tìm kiếm và ngăn các giá trị trùng lặp chèn vào cột.

Nếu bạn thử tạo chỉ mục UNIQUE trên cột đã có dữ liệu trùng lặp, bạn sẽ nhận lỗi UNIQUE constraint failed và chỉ mục không được tạo.

Mặt khác: nếu bạn tạo nó trên một cột có giá trị duy nhất (một khóa chính hoặc khóa kinh doanh duy nhất), bạn được cả ngăn trùng lặp và tìm kiếm bằng nhanh.

Trong perf_sales, product (200 giá trị khác biệt) có nhiều lặp, nên chỉ mục UNIQUE không khả thi ở đó. sale_id, ngược lại, là khóa chính và duy nhất, nên bạn có thể xây một cái.

Bài tập kế tiếp tạo chỉ mục UNIQUE trên sale_id, xác nhận một cột duy nhất chấp nhận chỉ mục và các tìm kiếm bằng trên cột đó dùng nó.

-- Chỉ mục UNIQUE (ví dụ chỉ đọc riêng)
DROP INDEX IF EXISTS ix_u_demo;
CREATE UNIQUE INDEX ix_u_demo ON perf_sales(sale_id);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE sale_id = 12345;

-- Thử thêm UNIQUE trên cột có trùng lặp bị từ chối
-- CREATE UNIQUE INDEX ix_bad ON perf_sales(product);
--> UNIQUE constraint failed: perf_sales.product
-- product có 200 giá trị khác biệt lặp lại qua 50.000 dòng, nên không thể UNIQUE

Hãy tưởng tượng yêu cầu: "sale_id là duy nhất cho mỗi lần bán, nên chúng ta muốn ngăn trùng lặp và tăng tốc tìm kiếm một dòng." Đây là bài tập cuối cùng của bài viết.

① Bắt đầu với DROP INDEX IF EXISTS ix;.

② Tạo chỉ mục UNIQUE tên ix trên cột sale_id của perf_sales (nó hoạt động vì sale_id là duy nhất).

③ Sau đó chạy EXPLAIN QUERY PLAN trên truy vấn lọc theo sale_id = 12345 và chọn sale_id và amount, và xác nhận tìm kiếm một dòng đi qua chỉ mục.

Tips — khi nào thêm chỉ mục

Nguyên tắc chung: chỉ mục thường thắng SCAN khi bạn có thể thu hẹp kết quả về khoảng 15-20% hoặc ít hơn của bảng. Trên ngưỡng đó, bộ tối ưu thường chọn SCAN tự động.

- Thêm: tìm kiếm bằng hoặc phạm vi trên cột của bảng lớn, khóa chính / khóa ngoại / cột UNIQUE, khóa JOIN, cột được tham chiếu bởi ORDER BY / GROUP BY

- Bỏ qua: bảng nhỏ vài nghìn dòng hoặc ít hơn, cột chọn lọc thấp như gender, bảng ghi nhiều, LIKE '%foo%' (wildcard ở đầu không dùng được chỉ mục)

Quy trình cơ bản: kiểm tra kế hoạch SCAN / SEARCH với EXPLAIN QUERY PLAN và thêm chỉ mục chỉ cho các truy vấn chậm.