Khi chỉ mục không hoạt động — Các mẫu và cách sửa

Các mẫu mà chỉ mục tồn tại nhưng không hoạt động

Ngay cả sau khi bạn tạo chỉ mục, một số cách viết điều kiện ngăn nó được dùng và truy vấn vẫn chạy như quét toàn bảng (SCAN).

Ba thủ phạm cổ điển ở dưới.

• Điều kiện bọc cột trong hàm hoặc phép tính (substr(col) / upper(col) / col + 0, vân vân)
• LIKE khớp giữa LIKE '%x%'
• Điều kiện phủ định (<> / NOT IN)

Trong mọi trường hợp nguyên nhân giống nhau: phép so sánh không còn phù hợp với thứ tự lưu trong chỉ mục.

Sau trong bài chúng ta xem chỉ mục biểu thức cho các trường hợp bạn không thể tránh bọc cột trong hàm, và chỉ mục một phần để thu hẹp chỉ mục xuống một tập con bản ghi cụ thể.

Xuyên suốt bài này bạn sẽ so sánh "cách không dùng chỉ mục" với "cách có dùng" cạnh nhau trong kế hoạch truy vấn.

-- Ngay cả với chỉ mục trên amount,
-- bọc cột trong hàm rơi về quét toàn bảng
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- So sánh cột như nó vốn có → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Bọc cột trong hàm → quay lại SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE abs(amount) >= 800000;

Xem xét yêu cầu: "Tôi muốn đếm chỉ doanh số năm 2024." Tạo chỉ mục trên sale_date, rồi so sánh hai kế hoạch truy vấn cạnh nhau — một bọc cột trong hàm, và một dùng điều kiện phạm vi với cột như nó vốn có — và xem SCAN so với SEARCH thay đổi thế nào. Xây lại chỉ mục trong một lần thực thi console để kế hoạch tự đứng vững. (Chạy đúng sẽ làm xuất hiện phần giải thích.)

① Xóa chỉ mục cũ với DROP INDEX IF EXISTS và tạo chỉ mục một cột trên sale_date.

② Dùng EXPLAIN QUERY PLAN trên truy vấn rút năm từ sale_date với substr và so sánh nó với '2024' (dạng bọc cột trong hàm).

③ Sau đó hiển thị kế hoạch cho truy vấn diễn đạt cùng ý nghĩa như điều kiện phạm vi trên sale_date (lớn hơn hoặc bằng đầu 2024, nhỏ hơn đầu năm tiếp theo) và so sánh nó với ②.

LIKE khớp giữa

Một LIKE khớp tiền tố (một dạng nơi phần đầu cố định, như 'P00%') về nguyên tắc có thể thu hẹp một phạm vi qua thứ tự chỉ mục.

Ngược lại, một khớp giữa ('%5%') hoặc khớp hậu tố ('%50') không có phần đầu cố định, nên không cơ sở dữ liệu nào có thể đi qua chỉ mục và truy vấn trở thành quét toàn bảng.

Tuy nhiên, trong SQLite, `LIKE` mặc định không phân biệt hoa-thường, nên nó không phù hợp với chỉ mục thứ tự BINARY thông thường — ngay cả LIKE khớp tiền tố cũng không có SEARCH và rơi về SCAN (xem callout dưới).

Nên khi bạn muốn khớp tiền tố trong SQLite dùng tra cứu chỉ mục, cách sửa thẳng thắn là viết lại như điều kiện phạm vi, như product >= 'P00' AND product < 'P01'.

Với LIKE khớp giữa, nếu bạn thực sự cần nó như một yêu cầu, hãy xem xét cơ chế khác như tìm kiếm toàn văn.

LIKE của SQLite mặc định không phân biệt hoa-thường

Trong SQLite, PRAGMA case_sensitive_like mặc định là OFF, nên LIKE là phép so sánh không phân biệt hoa-thường.

Chỉ mục được xây dùng thứ tự BINARY chuẩn (thứ tự từ điển phân biệt hoa-thường), nên ngay cả khớp tiền tố như LIKE 'P00%' cũng không phù hợp với thứ tự chỉ mục và quy tắc so sánh — nó không được dùng như SEARCH.

Nếu bạn chạy LIKE 'P00%' trong console này, kế hoạch trở thành SCAN perf_sales USING COVERING INDEX.

Cách giải quyết thẳng thắn là viết lại như điều kiện phạm vi (product >= 'P00' AND product < 'P01'), hoặc liệt kê các biến thể hoa-thường ở phía giá trị (product IN ('P050', 'p050')).

Trong bài này chúng ta đi với lựa chọn trực tiếp nhất — viết lại như điều kiện phạm vi.

Trong MySQL / PostgreSQL, nếu LIKE được cấu hình phân biệt hoa-thường, khớp tiền tố thường trở thành SEARCH.

-- So sánh kế hoạch điều kiện phạm vi và LIKE đối với chỉ mục trên product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Điều kiện phạm vi → đầu cố định, nên SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE product >= 'P15' AND product < 'P16';

-- LIKE khớp tiền tố → SCAN trong SQLite (không phân biệt hoa-thường)
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE 'P15%';

-- LIKE khớp giữa → SCAN trong bất kỳ DB nào
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE '%9%';

Lấy yêu cầu "Tôi muốn đếm doanh số có mã sản phẩm bắt đầu với một tiền tố cụ thể" và so sánh kế hoạch cho ba dạng: (A) điều kiện phạm vi, (B) LIKE khớp tiền tố, và (C) LIKE khớp giữa. Tạo chỉ mục trên product và xem dạng nào trở thành SEARCH trong SQLite.

① Xóa chỉ mục cũ với DROP INDEX IF EXISTS và tạo chỉ mục một cột trên product.

② Dùng EXPLAIN QUERY PLAN trên truy vấn đếm bản ghi với điều kiện phạm vi WHERE product >= 'P00' AND product < 'P01'.

③ Hiển thị kế hoạch cho cùng ý nghĩa được viết như LIKE khớp tiền tố 'P00%' và so sánh nó với ②.

④ Sau đó cũng hiển thị kế hoạch cho LIKE khớp giữa '%5%'.

Bọc cột với upper() hoặc phép tính dừng chỉ mục được dùng

Khi bạn áp dụng hàm hoặc phép tính lên cột để lọc nó, chỉ mục của cột không thể được dùng ngay cả khi nó tồn tại.

Lý do là chỉ mục được sắp xếp theo giá trị thô của cột, nên phép so sánh với giá trị đã biến đổi như upper(product) hoặc amount + 10000 không còn phù hợp với thứ tự chỉ mục.

Ví dụ, WHERE upper(product) = 'P050' phải đẩy product của mọi bản ghi qua upper() trước khi so sánh, nên chỉ mục trên product không được dùng và truy vấn rơi về quét toàn bộ.

Tương tự, WHERE amount + 10000 = 900000 phải tính amount + 10000 cho mọi bản ghi trước khi so sánh, nên chỉ mục trên amount không được dùng.

Cách sửa là để cột yên và điều chỉnh ở phía giá trị.

upper(product) = 'P050', nếu các giá trị duy nhất mong đợi là 'P050' và 'p050', trở thành WHERE product IN ('P050', 'p050') — và IN cho phép chỉ mục thu hẹp nhiều giá trị cùng lúc.

amount + 10000 = 900000 có thể được viết lại trực tiếp như amount = 800000 bằng cách trừ 10000 từ cả hai bên, và chỉ mục trên amount thu hẹp nó trong một lần.

Viết lại substr(sale_date, 1, 4) như điều kiện phạm vi trong phần trước là cùng ý tưởng "đừng biến đổi cột" áp dụng cho ngày tháng.

-- Ví dụ bọc cột với lower() đối với chỉ mục trên product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Bọc cột với lower() → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE lower(product) = 'p001';

-- Liệt kê các biến thể hoa-thường ở phía giá trị → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product IN ('P001', 'p001');

-- Ví dụ bọc phép tính (đối với chỉ mục trên qty)
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_qty ON perf_sales(qty);

-- Bọc với qty * 2 → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty * 2 = 20;

-- Đơn giản hóa biểu thức ở phía giá trị (chia cả hai bên cho 2) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty = 10;

Xem xét yêu cầu: "Tôi muốn đếm doanh số cho mã sản phẩm 'P050' (hoặc dạng chữ thường 'p050')."

Tạo chỉ mục trên product và so sánh kế hoạch cho hai dạng — bọc cột với upper() so với liệt kê các giá trị ứng viên với IN (...) ở phía giá trị.

Cả hai dạng nhắm cùng phép so sánh bao trùm 'P050' và 'p050', nên kết quả giống nhau.

① Xóa chỉ mục cũ với DROP INDEX IF EXISTS và tạo chỉ mục một cột trên product.

② Dùng EXPLAIN QUERY PLAN trên truy vấn bọc cột với upper() như trong WHERE upper(product) = 'P050'.

③ Hiển thị kế hoạch cho cùng ý nghĩa được viết như WHERE product IN ('P050', 'p050') và so sánh nó với ②.

Với yêu cầu "Tôi muốn đếm doanh số có số tiền chính xác 800.000", tạo chỉ mục trên amount và so sánh kế hoạch cho hai dạng — áp dụng phép tính lên cột so với điều chỉnh ở phía giá trị.

amount + 10000 = 900000 trở thành amount = 800000 một khi bạn trừ 10000 từ cả hai bên — hoàn toàn tương đương về mặt toán học.

① Xóa chỉ mục cũ với DROP INDEX IF EXISTS và tạo chỉ mục một cột trên amount.

② Dùng EXPLAIN QUERY PLAN trên truy vấn áp dụng phép tính lên cột như trong WHERE amount + 10000 = 900000.

③ Hiển thị kế hoạch cho cùng ý nghĩa được viết như WHERE amount = 800000 và so sánh nó với ②.

Điều kiện phủ định (<>, NOT IN)

Điều kiện phủ định như <> (không bằng) hoặc NOT IN thường kết thúc nhắm "phần lớn bảng", nên không có ý nghĩa trong việc thu hẹp với chỉ mục.

Ví dụ, khi status là 'paid' (khoảng 40.000 trên 50.000 bản ghi) / 'pending' (khoảng 5.000) / 'refunded' (khoảng 5.000), WHERE status <> 'paid' nhắm 10.000 bản ghi — khoảng 20% bảng.

Độ chọn lọc không đủ thấp, nên bộ tối ưu quyết định quét toàn bảng rẻ hơn.

Cách sửa là viết lại như điều kiện bằng.

WHERE status <> 'paid' có thể được viết lại như WHERE status IN ('pending', 'refunded'), và dạng đó có thể thu hẹp các bản ghi mục tiêu qua chỉ mục.

-- So sánh viết lại NOT IN so với IN đối với chỉ mục trên status
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status);

-- Điều kiện phủ định (NOT IN) → mục tiêu rộng, SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status NOT IN ('refunded');

-- Viết lại như điều kiện bằng (IN) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status IN ('paid', 'pending');

Xem xét yêu cầu: "Tôi muốn đếm doanh số khác paid." Tạo chỉ mục trên status và so sánh kế hoạch giữa viết nó với <> và viết lại cùng ý nghĩa như điều kiện bằng với IN.

① Xóa chỉ mục cũ với DROP INDEX IF EXISTS và tạo chỉ mục một cột trên status.

② Dùng EXPLAIN QUERY PLAN trên truy vấn đếm bản ghi khớp WHERE status <> 'paid'.

③ Hiển thị kế hoạch cho cùng ý nghĩa được viết lại như WHERE status IN ('pending', 'refunded') và so sánh nó với ②.

Sửa với chỉ mục biểu thức và chỉ mục một phần

Khi bọc cột trong hàm là không thể tránh, chỉ mục biểu thức (chỉ mục xây trên kết quả của một biểu thức, không phải cột thô) cứu nguy.

Với CREATE INDEX ix ON perf_sales(substr(sale_date,1,4)), tạo chỉ mục trên cùng biểu thức mà điều kiện dùng biến phép so sánh trên biểu thức đó thành tra cứu chỉ mục.

Yêu cầu là biểu thức trong điều kiện khớp với biểu thức trong chỉ mục.

Công cụ khác là chỉ mục một phần (chỉ mục có mệnh đề WHERE, được xây chỉ cho một tập con cụ thể của bản ghi).

Nếu các bản ghi status='pending' chỉ là một phần của bảng (5.000 trên 50.000), bạn có thể xây chỉ mục thu hẹp như CREATE INDEX ix ON perf_sales(emp_id) WHERE status='pending'.

Chỉ mục tự nó vẫn nhỏ và hiệu quả thu hẹp các truy vấn bao gồm điều kiện đó.

-- Chỉ mục biểu thức: xây nó trên cùng biểu thức mà điều kiện dùng
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(substr(sale_date, 1, 4));

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE substr(sale_date, 1, 4) = '2024';

Xem xét yêu cầu: "Tôi muốn tra cứu doanh số đang xử lý (pending) thu hẹp theo nhân viên bán hàng." Pending là một tập con nhỏ (5.000 trên 50.000), nên xây chỉ mục một phần nhắm chỉ những bản ghi đó và xác nhận một kế hoạch truy vấn bao gồm status='pending' trở thành tra cứu chỉ mục.

① Xóa chỉ mục cũ với DROP INDEX IF EXISTS.

② Trên emp_id, tạo chỉ mục một phần (dùng dạng CREATE INDEX ... WHERE ...) nhắm chỉ các bản ghi nơi status bằng 'pending'.

③ Dùng EXPLAIN QUERY PLAN trên truy vấn đếm bản ghi nơi status là 'pending' và một emp_id cụ thể, và xác nhận chỉ mục một phần đang được dùng.