Thiết kế chỉ mục — Thứ tự cột composite và điều kiện dùng chỉ mục

Bộ dữ liệu cho bài này — perf_sales (50.000 dòng)

Từ đây trở đi, các bài tuning dùng bảng nhiều dòng để quan sát chỉ mục thực sự cư xử thế nào.

Bài này nói về thứ tự cột của chỉ mục composite (chỉ mục xây qua nhiều cột theo thứ tự cụ thể), quy tắc leftmost prefix, và cách viết điều kiện có thể dùng chỉ mục (còn gọi là điều kiện SARGable).

Trước khi thực hành, xem nhanh định nghĩa cột và kích thước cùng mẫu dữ liệu của perf_sales. (Chạy các truy vấn đúng và phần giải thích sẽ xuất hiện.)

① 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 số dòng.

③ Xem trước 5 dòng đầu với SELECT emp_id, region, product, amount, sale_date, status FROM perf_sales LIMIT 5; (vì bảng có 50.000 dòng, đừng bao giờ lấy mọi dòng với SELECT * — dùng tổng hợp hoặc LIMIT để xem nhanh).

Thứ tự cột chỉ mục composite — quy tắc leftmost prefix

Một chỉ mục composite (như CREATE INDEX ix ON table(col_A, col_B), liệt kê nhiều cột theo thứ tự cố định) lưu các mục theo đúng thứ tự đó.

Chỉ mục được sắp xếp như từ điển: theo col_A tăng dần, và trong cùng col_A theo col_B tăng dần.

Vì điều này, liệu chỉ mục có giúp hay không phụ thuộc vào điều kiện của bạn có bao phủ các cột liên tục từ cột dẫn đầu hay không.

Đây gọi là quy tắc leftmost prefix (chỉ mục composite chỉ giúp điều kiện chỉ định liên tục từ cột dẫn đầu).

Chỉ mục trên (emp_id, sale_date) hoạt động cho điều kiện trên emp_id, hoặc cả emp_id và sale_date cùng nhau, nhưng không cho điều kiện chỉ trên sale_date — emp_id dẫn đầu thiếu, nên chỉ mục không được dùng.

-- Xây chỉ mục composite trên region và product, rồi so sánh
-- các kế hoạch cho điều kiện bao gồm region dẫn đầu
-- với điều kiện chỉ dùng product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, product);

-- Dùng cột dẫn đầu region -> ix_demo kích hoạt
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';

-- Bỏ region dẫn đầu và chỉ lọc theo product -> ix_demo không kích hoạt
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product = 'P050';

Xây chỉ mục composite trên emp_id và sale_date trên perf_sales và xác nhận quy tắc leftmost prefix bằng cách đọc kế hoạch thực thi. Tạo lại chỉ mục và hiển thị các kế hoạch trong một lần chạy console để các bước tự đứng vững. (Chạy đúng các truy vấn và phần giải thích sẽ xuất hiện.)

① Bắt đầu với DROP INDEX IF EXISTS để chỉ mục có thể xây lại ngay cả khi một cái cùng tên còn sót.

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

③ Dùng EXPLAIN QUERY PLAN để hiển thị kế hoạch cho truy vấn tổng hợp lọc chỉ theo emp_id.

④ Sau đó hiển thị kế hoạch cho truy vấn tổng hợp lọc chỉ theo sale_date và so sánh kế hoạch thay đổi thế nào so với ③.

Liệt kê cột theo thứ tự bằng-rồi-phạm vi

Có một hướng dẫn cho thứ tự cột trong chỉ mục composite.

Đặt điều kiện bằng (một = hay IN ghim cột vào một giá trị) ở trước, và điều kiện phạm vi (>, >=, <, BETWEEN, vv., bao phủ một đoạn) ở sau.

Ghim cột dẫn đầu với bằng giữ các mục khớp trong một khối liên tục trong chỉ mục, nên bạn có thể carve khối đó xuống tất cả cùng lúc.

Điều kiện như WHERE emp_id = 7 AND sale_date >= '2024-01-01' là phù hợp tự nhiên cho thứ tự (emp_id, sale_date).

-- Lập chỉ mục region (bằng) và amount (phạm vi) cùng nhau,
-- với cột bằng trước và cột phạm vi sau
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE region = 'West' AND amount >= 500000;

Hãy tưởng tượng yêu cầu: "Chúng ta muốn tổng hợp doanh số cho một nhân viên cụ thể từ ngày cho trước trở đi." Các điều kiện là bằng trên emp_id và phạm vi trên sale_date. Xây chỉ mục composite qua hai cột này theo thứ tự bằng-rồi-phạm vi và xác nhận với kế hoạch thực thi rằng nó chuyển thành tra cứu chỉ mục.

① Chạy DROP INDEX IF EXISTS để xóa chỉ mục cũ.

② Tạo chỉ mục composite với cột bằng trước và cột phạm vi sau.

③ Dùng EXPLAIN QUERY PLAN trên truy vấn tổng hợp kết hợp bằng trên emp_id với phạm vi trên sale_date qua AND, và xác nhận nó thành tra cứu chỉ mục.

Điều kiện có thể dùng chỉ mục — so sánh cột nguyên trạng, không bao

Ngay cả với chỉ mục đặt sẵn, cách bạn viết điều kiện quyết định liệu nó có được dùng hay không.

Điều kiện có hình dạng để chỉ mục có thể thu hẹp gọi là điều kiện có thể dùng chỉ mục (tiếng Anh còn gọi là SARGable).

Quy tắc cơ bản: so sánh cột được lập chỉ mục nguyên trạng, không bao trong bất kỳ tính toán nào.

Điều như WHERE emp_id = 7, nơi cột được so sánh trực tiếp bằng bằng hay phạm vi, cho phép chỉ mục kích hoạt.

Mặt khác, WHERE emp_id + 0 = 7 (cột nằm trong biểu thức) hoặc WHERE substr(sale_date,1,4) = '2024' (cột được bao trong hàm) phá tương ứng với thứ tự của chỉ mục và rơi về quét dòng toàn bộ.

Giữ cột thô ở bên trái của so sánh, và nếu cần điều chỉnh, áp dụng nó cho giá trị ở bên phải.

-- Cùng ý nghĩa, hình dạng khác nhau -> kế hoạch khác nhau
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- Cột so sánh nguyên trạng -> chỉ mục kích hoạt, SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Cột trong biểu thức -> chỉ mục không kích hoạt, rơi về SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount + 0 >= 800000;

Quan sát cách hai điều kiện cùng ý nghĩa — một so sánh cột nguyên trạng và một bao cột trong biểu thức — tạo ra kế hoạch thực thi khác nhau. Xây chỉ mục trên emp_id và so sánh kế hoạch cho hai hình dạng.

① Chạy DROP INDEX IF EXISTS để xóa chỉ mục cũ.

② Tạo chỉ mục một cột trên emp_id.

③ Hiển thị kế hoạch cho truy vấn tổng hợp so sánh emp_id nguyên trạng bằng bằng.

④ Sau đó hiển thị kế hoạch cho truy vấn tổng hợp cùng ý nghĩa nhưng với emp_id trong biểu thức, và so sánh kế hoạch thay đổi thế nào so với ③.