CREATE INDEX trong MySQL
Xem thửTình huống mở đầu
Trang Đơn của tôi của cửa hàng shop chỉ làm một việc: lấy 5 đơn đã thanh toán gần nhất của một khách. Trên bảng thử 20000 đơn, mỗi lần khách mở trang MySQL đọc 20001 dòng để trả về đúng 5 dòng. Một đồng nghiệp nhanh tay thêm chỉ mục vào cột status vì cột này nằm trong WHERE. Số lần đọc giảm, nhưng vẫn còn hơn mười nghìn.
Hai chuyện cùng nguồn gốc: có chỉ mục chưa chắc là có chỉ mục đúng. Chỉ mục là một danh sách đã sắp xếp, và nó chỉ giúp khi thứ tự sắp xếp của nó khớp với cách câu truy vấn tìm dữ liệu.
Hãy đoán trước: với chỉ mục đúng, trang Đơn của tôi cần đọc khoảng bao nhiêu mục, gần 5, gần 500 hay gần 5000? Cuối bài bạn sẽ có con số đo thật.
Ôn nhanh bài trước: ở chương 14, khóa chính (order_id, product_id) có giúp tìm nhanh theo riêng product_id không?
Đáp án: không. Khóa sắp theo order_id trước nên tìm riêng product_id không có điểm bắt đầu. Bài này đi tiếp ý đó cho chỉ mục thường.
Mục tiêu bài học
Sau bài này bạn có thể:
- Tạo chỉ mục bằng
CREATE INDEXhoặcALTER TABLE ... ADD INDEX, rồi đọc cột, thứ tự và độ phân biệt (cardinality) của nó tronginformation_schema.statistics. - Đo hiệu quả chỉ mục bằng
EXPLAINvà bộ đếmHandler_read_*thay vì đoán. - Chọn thứ tự cột cho chỉ mục ghép: cột so sánh bằng đứng trước, cột sắp xếp đứng sau, và giải thích vì sao chỉ mục riêng cho cột ít giá trị khác nhau gần như vô dụng.
- Dự đoán truy vấn nào dùng được chỉ mục ghép theo quy tắc tiền tố trái và đọc
key_lenđể biết nó dùng bao nhiêu cột. - Nhận ra ba bẫy làm chỉ mục bị bỏ qua (
LIKE '%x', so sánh chuỗi với số, hàm bọc quanh cột) và chi phí ghi của mỗi chỉ mục.
Bức tranh lớn
Xuyên suốt bài, chỉ mục là mục lục tra cứu ở cuối một cuốn sổ đơn hàng dày: các từ khóa được xếp theo thứ tự, mỗi từ khóa kèm số trang. Chỉ mục ghép là mục lục xếp theo từ khóa thứ nhất, trong cùng từ khóa thứ nhất mới xếp theo từ khóa thứ hai. Dự án xuyên suốt là trang Đơn của tôi của cửa hàng shop, database tên shop.
Hình dưới trả lời một câu hỏi: từ câu truy vấn có điều kiện bằng và ORDER BY ... LIMIT, MySQL đi qua những ngã rẽ nào để tới chỗ đọc ít nhất?
Đang tải sơ đồ…
Cách đọc hình: đọc từ ô xanh dương xuống. Ô cam đầu tiên hỏi chỉ mục có bắt đầu bằng các cột so sánh bằng hay không. Không có thì đi nhánh đỏ bên trái: đọc hết rồi sắp xếp. Có thì MySQL tìm một lần tới điểm bắt đầu (ô tím), rồi ô cam thứ hai hỏi cột kế tiếp của chỉ mục có phải cột sắp xếp không. Đúng thì đọc theo thứ tự có sẵn và dừng khi đủ LIMIT (ô xanh lá), sai thì phải đọc hết các mục khớp rồi sắp xếp (ô đỏ bên phải).
Chỗ ẩn dụ không còn đúng: mục lục sách in một lần, còn chỉ mục MySQL được cập nhật theo từng lệnh ghi, và đó chính là cái giá của nó. Ngoài ra bộ tối ưu có thể quyết định không dùng mục lục nếu thấy đọc thẳng cả bảng rẻ hơn.
Khái niệm cốt lõi
Bài này có bốn khái niệm.
1. Chỉ mục phụ là một danh sách đã sắp xếp, mỗi mục kèm khóa chính
Bảng InnoDB tự nó là một cây B-tree sắp theo khóa chính. Chỉ mục phụ là một cây B-tree thứ hai: mỗi mục gồm giá trị các cột được lập chỉ mục cộng với khóa chính của dòng tương ứng. Nhờ sắp xếp, MySQL tìm một giá trị bằng vài bước nhảy trong cây thay vì đọc tuần tự. Hai hệ quả: chỉ mục nào cũng ngầm chứa khóa chính, và chỉ mục chiếm dung lượng riêng.
2. Chỉ mục ghép sắp xếp theo thứ tự cột và dùng được theo tiền tố trái
(user_id, status, created_at) xếp theo user_id trước, trong cùng user_id xếp theo status, trong cùng cả hai mới xếp theo created_at. Điều kiện dùng được chỉ mục này là tiền tố trái: chỉ user_id, hoặc user_id cùng status, hoặc cả ba. Bỏ qua cột đầu thì không có điểm bắt đầu.
3. Thứ tự cột: so sánh bằng trước, sắp xếp sau, và đừng dựa vào cột ít giá trị khác nhau
Cột so sánh bằng (user_id = 10) thu hẹp phạm vi, nên đứng đầu. Cột sắp xếp (created_at) đứng sau các cột so sánh bằng để các mục khớp đã nằm sẵn theo thứ tự. Độ phân biệt (cardinality) là số giá trị khác nhau ước tính của cột: status chỉ có 4 giá trị nên đứng một mình, nó không thu hẹp được gì đáng kể.
4. Đo bằng EXPLAIN và Handler_read_*
Trong EXPLAIN, type cho biết cách truy cập (ALL là quét cả bảng, ref và range là dùng chỉ mục), key là chỉ mục được chọn, key_len cho biết đã dùng bao nhiêu byte của chỉ mục tức là bao nhiêu cột, rows là số dòng ước tính, Extra có Using filesort khi phải sắp xếp thêm. Bộ đếm Handler_read_* cho biết số lần đọc thật sự đã xảy ra.
Ví dụ đầu tiên
Dựng bảng orders 20000 đơn [Đã chạy: MySQL 8.0.44]:
SET SESSION cte_max_recursion_depth = 30000;
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
phone VARCHAR(12) NOT NULL,
created_at DATETIME NOT NULL,
total DECIMAL(10, 2) NOT NULL
);
INSERT INTO orders (user_id, status, phone, created_at, total)
WITH RECURSIVE n AS (
SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 20000
)
SELECT (i * 7919 + i DIV 7) % 500 + 1, -- 500 khách, mỗi khách khoảng 40 đơn
ELT((i * 31 + i DIV 3) % 10 + 1,
'paid', 'paid', 'paid', 'paid', 'paid', 'paid', -- 60 phần trăm đơn là paid
'shipped', 'shipped', 'pending', 'cancelled'),
CONCAT('09', LPAD((i * 48271) % 100000000, 8, '0')),
TIMESTAMP('2026-01-01 00:00:00') + INTERVAL i * 21 MINUTE, -- cứ 21 phút một đơn
i % 90 + 10
FROM n;
SELECT COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS users, MIN(created_at) AS first_order
FROM orders;
SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status ORDER BY order_count DESC;
+-------------+-------+---------------------+
| order_count | users | first_order |
+-------------+-------+---------------------+
| 20000 | 500 | 2026-01-01 00:21:00 |
+-------------+-------+---------------------+
+-----------+-------------+
| status | order_count |
+-----------+-------------+
| paid | 12001 |
| shipped | 4000 |
| pending | 2666 |
| cancelled | 1333 |
+-----------+-------------+
Câu truy vấn của trang Đơn của tôi, kèm EXPLAIN và bộ đếm. FLUSH STATUS xóa bộ đếm của phiên để con số chỉ thuộc về câu truy vấn sắp chạy. Chưa có chỉ mục phụ nào:
EXPLAIN SELECT id, total, created_at
FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 5;
FLUSH STATUS;
SELECT id, total, created_at
FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 5;
SHOW SESSION STATUS WHERE Variable_name LIKE 'Handler_read%' AND Value > 0;
Dự đoán trước khi xem kết quả: MySQL đọc bao nhiêu dòng để trả về 5 dòng?
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 20000 | 1.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
+-------+-------+---------------------+
| id | total | created_at |
+-------+-------+---------------------+
| 19052 | 72.00 | 2026-10-05 20:12:00 |
| 18446 | 96.00 | 2026-09-27 00:06:00 |
| 16999 | 89.00 | 2026-09-05 21:39:00 |
| 16696 | 56.00 | 2026-09-01 11:36:00 |
| 15249 | 49.00 | 2026-08-11 09:09:00 |
+-------+-------+---------------------+
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_rnd_next | 20001 |
+-----------------------+-------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
type: ALL | Quét tuần tự cả bảng | Không có chỉ mục nào để nhảy tới khách 10 |
Using filesort | Sắp xếp các dòng khớp rồi mới cắt 5 dòng | ORDER BY created_at DESC cần thứ tự mà bảng không có sẵn |
FLUSH STATUS | Đặt bộ đếm của phiên về 0 | Để con số đo sau đó chỉ do câu truy vấn gây ra |
Handler_read_rnd_next bằng 20001 | Số lần engine đọc dòng kế tiếp khi quét bảng | Bảng có 20000 dòng, cộng một lần đọc để biết đã hết |
Thử cách nghĩ quen thuộc: đặt chỉ mục lên status, cột nằm trong WHERE [Đã chạy: MySQL 8.0.44]:
CREATE INDEX idx_status ON orders (status);
EXPLAIN SELECT id, total, created_at
FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 5;
FLUSH STATUS;
SELECT id, total, created_at
FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 5;
SHOW SESSION STATUS WHERE Variable_name LIKE 'Handler_read%' AND Value > 0;
+----+-------------+--------+------------+------+---------------+------------+---------+-------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+-------+----------+-----------------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_status | idx_status | 82 | const | 10000 | 10.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+-------+----------+-----------------------------+
+-------+-------+---------------------+
| id | total | created_at |
+-------+-------+---------------------+
| 19052 | 72.00 | 2026-10-05 20:12:00 |
| 18446 | 96.00 | 2026-09-27 00:06:00 |
| 16999 | 89.00 | 2026-09-05 21:39:00 |
| 16696 | 56.00 | 2026-09-01 11:36:00 |
| 15249 | 49.00 | 2026-08-11 09:09:00 |
+-------+-------+---------------------+
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_key | 1 |
| Handler_read_next | 12001 |
+-------------------+-------+
rows và filtered là ước lượng [Có thể khác nhau], còn Handler_read_next là số lần đọc thật: 12001 bằng đúng số đơn paid. Chỉ mục có dùng, nhưng chỉ đổi 20001 lần đọc bảng thành 12001 lần đọc chỉ mục, rồi vẫn lọc khách và sắp xếp.
Chỉ mục trên user_id:
CREATE INDEX idx_user ON orders (user_id);
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_user | idx_user | 4 | const | 33 | 10.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------------+
+-------+-------+---------------------+
| id | total | created_at |
+-------+-------+---------------------+
| 19052 | 72.00 | 2026-10-05 20:12:00 |
| 18446 | 96.00 | 2026-09-27 00:06:00 |
| 16999 | 89.00 | 2026-09-05 21:39:00 |
| 16696 | 56.00 | 2026-09-01 11:36:00 |
| 15249 | 49.00 | 2026-08-11 09:09:00 |
+-------+-------+---------------------+
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_key | 1 |
| Handler_read_next | 33 |
+-------------------+-------+
Chỉ còn 33 lần đọc, đúng bằng số đơn của khách 10. Nhưng Using filesort vẫn còn: 33 đơn phải được lọc status và sắp xếp trước khi lấy 5.
Chỉ mục ghép đúng hình dạng truy vấn, cột so sánh bằng trước và cột sắp xếp sau:
CREATE INDEX idx_inbox ON orders (user_id, status, created_at);
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+---------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_inbox | idx_inbox | 86 | const,const | 19 | 100.00 | Backward index scan |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+---------------------+
+-------+-------+---------------------+
| id | total | created_at |
+-------+-------+---------------------+
| 19052 | 72.00 | 2026-10-05 20:12:00 |
| 18446 | 96.00 | 2026-09-27 00:06:00 |
| 16999 | 89.00 | 2026-09-05 21:39:00 |
| 16696 | 56.00 | 2026-09-01 11:36:00 |
| 15249 | 49.00 | 2026-08-11 09:09:00 |
+-------+-------+---------------------+
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_key | 1 |
| Handler_read_prev | 4 |
+-------------------+-------+
| Dòng code | Máy làm gì | Vì sao viết như vậy |
|---|---|---|
(user_id, status, created_at) | Dựng cây B-tree xếp theo ba cột này theo thứ tự đó | Hai điều kiện bằng đứng trước, cột sắp xếp đứng cuối |
key_len: 86 | Dùng 86 byte đầu của mỗi mục chỉ mục | 4 byte của user_id cộng 82 byte của status (20 ký tự utf8mb4 nhân 4, cộng 2) |
Backward index scan | Đọc chỉ mục từ cuối ngược lên | DESC khớp với thứ tự đảo của chỉ mục, không cần sắp xếp |
Handler_read_key 1, Handler_read_prev 4 | Một lần tìm tới khách 10 và paid, rồi lùi 4 mục | LIMIT 5 nên dừng ngay khi đủ 5 mục |
Từ 20001 xuống 5 lần đọc, bằng một chỉ mục. Ba cách đã thử xếp cạnh nhau:
| Chỉ mục | Kiểu đọc | Lần đọc chính | Có filesort |
|---|---|---|---|
| Không có | Quét cả bảng | 20001 | Có |
(status) | Một dải 12001 mục | 12001 | Có |
(user_id) | Một dải 33 mục | 33 | Có |
(user_id, status, created_at) | Tìm một lần, đọc ngược | 4 | Không |
Xem lại bằng sổ đăng ký của MySQL. Ở hàng thứ k của một chỉ mục ghép, cardinality là số tổ hợp khác nhau ước tính của k cột đầu [Có thể khác nhau]: 500 khách, rồi 1800 cặp khách và trạng thái, rồi gần 20000 bộ ba. Mỗi cột thêm vào làm dải đọc hẹp thêm, còn status đứng một mình chỉ có bốn giá trị (bảng đếm ở trên).
SELECT index_name, seq_in_index AS pos, column_name, cardinality
FROM information_schema.statistics
WHERE table_schema = DATABASE() AND table_name = 'orders' AND index_name = 'idx_inbox'
ORDER BY seq_in_index;
+------------+-----+-------------+-------------+
| INDEX_NAME | pos | COLUMN_NAME | CARDINALITY |
+------------+-----+-------------+-------------+
| idx_inbox | 1 | user_id | 500 |
| idx_inbox | 2 | status | 1800 |
| idx_inbox | 3 | created_at | 20000 |
+------------+-----+-------------+-------------+
Xem từng bước
Animation dưới đây chạy lại bốn lần đo. Hãy trả lời câu dự đoán trước khi bấm bước đầu tiên.
Đang tải Animation…
Quy tắc tiền tố trái trên chính chỉ mục vừa tạo [Đã chạy: MySQL 8.0.44]. Bốn truy vấn lần lượt: chỉ cột đầu, chỉ cột thứ hai, cột đầu và cột thứ ba (bỏ qua cột giữa), và cột đầu kèm sắp xếp:
EXPLAIN SELECT id, total FROM orders WHERE user_id = 10;
EXPLAIN SELECT id, total FROM orders WHERE status = 'paid';
EXPLAIN SELECT id, total FROM orders
WHERE user_id = 10 AND created_at > '2026-06-01';
EXPLAIN SELECT id, total FROM orders WHERE user_id = 10 ORDER BY created_at DESC LIMIT 5;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | NULL | ref | idx_inbox | idx_inbox | 4 | const | 33 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 20000 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_inbox | idx_inbox | 4 | const | 33 | 33.33 | Using index condition |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | orders | NULL | ref | idx_inbox | idx_inbox | 4 | const | 33 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
| Truy vấn | key và key_len | Điều gì xảy ra |
|---|---|---|
WHERE user_id = 10 | idx_inbox, 4 | Dùng tiền tố một cột |
WHERE status = 'paid' | không có chỉ mục, ALL | Thiếu cột đầu nên không có điểm bắt đầu |
WHERE user_id = 10 AND created_at > ... | idx_inbox, 4 | Chỉ user_id thu hẹp phạm vi, created_at bị lọc sau vì cột status ở giữa bị bỏ qua |
WHERE user_id = 10 ORDER BY created_at | idx_inbox, 4, có filesort | Cột status nằm giữa nên các mục của khách 10 không xếp theo created_at |
Bạn vừa làm được: nhìn key_len và Extra để nói chính xác chỉ mục ghép đang được dùng bao nhiêu cột và còn bước sắp xếp nào.
Concept Check
Bảng orders có chỉ mục duy nhất (user_id, status, created_at).
Câu 1. WHERE status = 'paid' AND created_at > '2026-09-01' với SELECT id, total có dùng chỉ mục này để thu hẹp phạm vi không?
- A. Có, vì cả hai cột đều nằm trong chỉ mục và đứng cạnh nhau nên đủ điều kiện.
- B. Không, vì thiếu cột đầu
user_idnên chỉ mục không có điểm bắt đầu. - C. Có, vì MySQL tự sắp lại thứ tự cột của chỉ mục theo điều kiện truy vấn.
- D. Không, vì chỉ mục ghép chỉ dùng được khi truy vấn chứa đủ cả ba cột.
Câu 2. Cột is_active chỉ có hai giá trị, với 95 phần trăm dòng là 1. Thêm chỉ mục riêng cho cột này có giúp WHERE is_active = 1 không?
- A. Giúp nhiều, vì mọi cột nằm trong
WHEREđều nên có chỉ mục riêng. - B. Giúp ít, vì chỉ mục vẫn trỏ tới gần cả bảng nên phải đọc gần hết.
- C. Giúp nhiều, vì chỉ mục luôn đọc nhanh hơn bảng dù khớp bao nhiêu dòng.
- D. Làm câu chậm đi, vì MySQL bắt buộc phải dùng chỉ mục khi nó tồn tại.
Câu 3. Cần WHERE user_id = 7 ORDER BY created_at DESC LIMIT 5 không có bước sắp xếp. Chỉ mục nào phù hợp nhất?
- A.
(created_at, user_id), vì cột sắp xếp phải đứng đầu để đọc theo thứ tự. - B.
(user_id, created_at), vì điều kiện bằng đứng trước rồi đến cột sắp xếp. - C.
(user_id), vì cột trongWHERElà đủ để bỏ hẳn bước sắp xếp. - D.
(status, user_id), vì cột có ít giá trị đứng đầu sẽ gom nhóm tốt hơn.
Đáp án và giải thích:
- Câu 1: B. Chỉ mục xếp theo
user_idtrước, nên không cóuser_idthì không có điểm bắt đầu. A sai vì cột đứng cạnh nhau không đủ, phải là tiền tố trái. C sai vì MySQL không đổi thứ tự cột đã tạo. D sai vì dùng một phần tiền tố là được, vấn đề ở đây là thiếu cột đầu. - Câu 2: B. Chỉ mục chỉ trỏ tới 95 phần trăm bảng nên gần như không thu hẹp được gì. A sai vì không phải cột nào trong
WHEREcũng đáng lập chỉ mục. C sai vì đọc 95 phần trăm bảng qua chỉ mục có thể đắt hơn quét tuần tự. D sai vì bộ tối ưu tự chọn kế hoạch rẻ hơn chứ không bị ép dùng chỉ mục. - Câu 3: B. Điều kiện bằng thu hẹp về khách 7, rồi cột
created_atđã sắp sẵn trong từng khách. A sai vì thiếu cột đầu để thu hẹp. C sai vì còn phải sắp xếp 33 mục. D sai vì cộtuser_idmới là cột thu hẹp.
Làm cùng mình
Nhóm hỗ trợ khách hàng cần màn hình: đơn của khách 25 từ ngày 1 tháng 9 trở đi, mới nhất trước, lấy id và total. Bạn sẽ chọn chỉ mục và kiểm chứng bằng key_len.
Bước 1. Vẽ ra giấy
Trên giấy, viết hai điều kiện của truy vấn và ghi cái nào là so sánh bằng, cái nào là khoảng. Ghi dự đoán: chỉ mục hai cột nên đặt cột nào trước, và key_len sẽ là bao nhiêu (user_id là INT NOT NULL nên 4 byte, created_at là DATETIME nên 5 byte).
Bước 2. Điền chỗ trống
Điền hai cột đúng thứ tự vào chỗ trống:
-- Màn hình hỗ trợ: đơn của khách 25 từ ngày 1 tháng 9, mới nhất trước
-- TODO 1: điền hai cột theo đúng thứ tự vào dấu ba chấm
CREATE INDEX idx_support ON orders (...);
EXPLAIN SELECT id, total
FROM orders
WHERE user_id = 25 AND created_at >= '2026-09-01'
ORDER BY created_at DESC;
Gợi ý: cột so sánh bằng đứng trước, cột có điều kiện khoảng và sắp xếp đứng sau. Đáp án nằm ở mục Lời giải và đáp án.
Bước 3. Đảo thứ tự rồi chạy lại
Tạo lại bảng, lần này đặt (created_at, user_id). Dự đoán trước: key_len và Extra thay đổi thế nào, và số dòng ước tính có tăng không? Đối chiếu với kết quả ở mục Lời giải và đáp án.
Bước 4. Vẽ lại ra giấy
Ghi một câu: vì sao cùng hai cột nhưng thứ tự (created_at, user_id) thu hẹp phạm vi kém hơn nhiều.
Thử phá nó đi
Năm bẫy khiến chỉ mục có mặt nhưng không giúp gì [Đã chạy: MySQL 8.0.44]:
| Code | Hiện tượng | Nguyên nhân | Cách sửa |
|---|---|---|---|
WHERE phone LIKE '%6567' | type: ALL | Chỉ mục xếp theo ký tự đầu, dấu % ở đầu làm mất điểm bắt đầu | Dùng tiền tố LIKE '0937%', hoặc cột đảo chuỗi nếu cần tìm theo đuôi |
WHERE phone = 937506567 | Đọc cả chỉ mục, trả thêm dòng không mong muốn | So chuỗi với số thì MySQL đổi từng chuỗi sang số | Luôn so với chuỗi: phone = '0937506567' |
WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 1 | Đọc cả bảng | Hàm bọc quanh cột nên chỉ mục không còn khớp giá trị | Viết khoảng nửa mở trên cột trần |
WHERE status = 'paid' trên chỉ mục (user_id, status, created_at) | type: ALL | Thiếu cột đầu nên không có điểm bắt đầu | Thêm điều kiện cột đầu, hoặc lập chỉ mục khác |
| Thêm chỉ mục cho mọi cột | Mỗi lần ghi chậm hơn, dung lượng tăng | Mỗi chỉ mục là một cây phải cập nhật | Chỉ lập chỉ mục cho truy vấn đã đo là chậm |
Bẫy thứ nhất, với chỉ mục trên phone. Tiền tố cho range, đuôi cho ALL:
EXPLAIN SELECT id, total FROM orders WHERE phone LIKE '0937%';
EXPLAIN SELECT id, total FROM orders WHERE phone LIKE '%6567';
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | orders | NULL | range | idx_phone | idx_phone | 50 | NULL | 207 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 20000 | 11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
Bẫy thứ hai. Chỉ mục idx_phone có sẵn, nhưng so với số thì không dùng được. Thêm một đơn có số điện thoại 937506567 (thiếu số 0 đầu) để xem hậu quả:
EXPLAIN SELECT id, phone FROM orders WHERE phone = 937506567;
EXPLAIN SELECT id, phone FROM orders WHERE phone = '0937506567';
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | orders | NULL | index | idx_phone | idx_phone | 50 | NULL | 20000 | 10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | idx_phone | idx_phone | 50 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
INSERT INTO orders VALUES (20001, 1, 'paid', '937506567', '2026-01-01 00:00:00', 1);
FLUSH STATUS;
SELECT id, phone FROM orders WHERE phone = 937506567;
SHOW SESSION STATUS WHERE Variable_name LIKE 'Handler_read%' AND Value > 0;
FLUSH STATUS;
SELECT id, phone FROM orders WHERE phone = '0937506567';
SHOW SESSION STATUS WHERE Variable_name LIKE 'Handler_read%' AND Value > 0;
+-------+------------+
| id | phone |
+-------+------------+
| 777 | 0937506567 |
| 20001 | 937506567 |
+-------+------------+
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_next | 20001 |
+--------------------+-------+
+-----+------------+
| id | phone |
+-----+------------+
| 777 | 0937506567 |
+-----+------------+
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_key | 1 |
| Handler_read_next | 1 |
+-------------------+-------+
Hai điều xấu cùng lúc. Một: so với số thì phải đọc 20001 mục chỉ mục, so với chuỗi chỉ cần 1 lần tìm. Hai: kết quả cũng khác, vì '0937506567' và '937506567' đều bằng số 937506567 sau khi đổi, nên truy vấn trả về cả hai số điện thoại khác nhau.
Bẫy thứ ba, hàm bọc quanh cột (đã gặp ở bài DATE ở chương 16, ở đây với YEAR và MONTH):
FLUSH STATUS;
SELECT COUNT(*) AS jan_orders FROM orders WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 1;
SHOW SESSION STATUS WHERE Variable_name LIKE 'Handler_read%' AND Value > 0;
FLUSH STATUS;
SELECT COUNT(*) AS jan_orders FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';
SHOW SESSION STATUS WHERE Variable_name LIKE 'Handler_read%' AND Value > 0;
+------------+
| jan_orders |
+------------+
| 2125 |
+------------+
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_next | 20000 |
+--------------------+-------+
+------------+
| jan_orders |
+------------+
| 2125 |
+------------+
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_key | 1 |
| Handler_read_next | 2125 |
+-------------------+-------+
Cả hai trả cùng số đơn, nhưng bọc hàm đọc 20000 mục còn khoảng nửa mở chỉ đọc đúng số đơn của tháng.
Hiểu lầm quan trọng nhất của bài là coi chỉ mục như một công tắc bật nhanh cho từng cột. Hình dưới đối chiếu mô hình sai với mô hình đúng.
Đang tải sơ đồ…
Cách đọc hình: hai chuỗi độc lập. Chuỗi đỏ là niềm tin sai rằng mỗi cột trong WHERE cần chỉ mục riêng, dẫn tới nhiều chỉ mục đơn mà bộ tối ưu chỉ dùng được một. Chuỗi xanh là thực tế: một chỉ mục ghép có hình dạng của truy vấn, cột so sánh bằng trước rồi cột sắp xếp.
Tự tay làm
Dùng chung bảng orders của bài. Mỗi bài nêu đầu vào, kết quả mong đợi và tiêu chí đạt.
Cơ bản
Bài 1. Đọc key_len. Đầu vào: chỉ mục (user_id, status, created_at), ba truy vấn: chỉ user_id = 10; user_id = 10 AND status = 'paid'; thêm created_at > '2026-09-01'. Dự đoán key_len của từng truy vấn rồi chạy EXPLAIN để kiểm. Tiêu chí đạt: đúng cả ba giá trị. Gợi ý: INT là 4 byte, VARCHAR(20) kiểu utf8mb4 là 20 nhân 4 cộng 2, DATETIME là 5 byte.
Vận dụng
Bài 2. Mọi trạng thái. Đầu vào: khách muốn xem 5 đơn mới nhất của khách 10, không lọc trạng thái. Chỉ mục (user_id, status, created_at) còn tránh được filesort không? Hãy tạo chỉ mục khác để tránh. Tiêu chí đạt: EXPLAIN không còn Using filesort. Gợi ý: cột status đang đứng giữa.
Thử thách (tùy chọn)
Bài 3. Một chỉ mục cho hai truy vấn. Đầu vào: cần phục vụ cả truy vấn lọc status = 'paid' lẫn truy vấn không lọc, đều sắp xếp theo created_at DESC của một khách. Thiết kế một chỉ mục duy nhất, chạy EXPLAIN cả hai và so với chỉ mục riêng. Tiêu chí đạt: cả hai truy vấn không có filesort. Gợi ý: đặt cột sắp xếp ngay sau user_id, cột status ở cuối.
Tự kiểm tra
Trả lời bằng lời của bạn, không nhìn lại bài. Mỗi câu ghi mục tiêu được kiểm tra.
- (Mục tiêu 1, nhớ lại) Mỗi mục của một chỉ mục phụ InnoDB chứa những gì ngoài giá trị cột được lập chỉ mục?
- (Mục tiêu 3, giải thích) Vì sao chỉ mục riêng trên
statuschỉ đổi 20001 lần đọc thành 12001 lần? - (Mục tiêu 4, dự đoán) Chỉ mục
(a, b, c), truy vấnWHERE a = 1 AND c = 3. Chỉ mục dùng được mấy cột để thu hẹp phạm vi? - (Mục tiêu 2, tìm lỗi)
EXPLAINhiệntype: ALLdùpossible_keyscó chỉ mục. Hai nguyên nhân hay gặp là gì? - (Mục tiêu 5, tìm lỗi) Cột
phonekiểuVARCHARcó chỉ mục, câuWHERE phone = 937506567chạy chậm. Sửa thế nào và vì sao còn có thể trả sai dòng?
Điều cần nhớ
- Chỉ mục phụ là cây B-tree sắp xếp theo các cột đã chọn, mỗi mục kèm khóa chính.
- Chỉ mục ghép dùng theo tiền tố trái: cột so sánh bằng đứng trước, cột sắp xếp đứng sau.
- Cột ít giá trị khác nhau đứng một mình gần như không thu hẹp được phạm vi.
- Đo bằng
EXPLAIN(type,key,key_len,Extra) vàHandler_read_*, không đoán. LIKE '%x', so chuỗi với số và hàm bọc quanh cột làm chỉ mục mất tác dụng.
Cheat sheet:
| Muốn | Viết | Ghi chú |
|---|---|---|
| Tạo chỉ mục | CREATE INDEX idx_a ON t (a, b) | Cột đứng trước là cột thu hẹp |
Tạo bằng ALTER | ALTER TABLE t ADD INDEX idx_a (a, b) | Tương đương CREATE INDEX |
| Xem chỉ mục | SHOW INDEX FROM t | Có Cardinality và Visible |
| Kiểm chứng | EXPLAIN SELECT ... | Nhìn key, key_len, Extra |
| Đo thật | FLUSH STATUS rồi SHOW SESSION STATUS LIKE 'Handler_read%' | Con số không phụ thuộc máy |
| Tìm theo tiền tố | LIKE 'abc%' | Dùng được chỉ mục |
Hình tóm tắt của bài là sơ đồ ở mục Bức tranh lớn.
Câu hỏi để dành cho bài sau: bảng orders bây giờ có thể đã có ba, bốn chỉ mục mà không ai nhớ chỉ mục nào còn cần. Làm sao biết một chỉ mục thừa, và gỡ nó an toàn thế nào?
Đào sâu
Phần này dành cho bạn tò mò.
- Cơ chế bên trong: chỉ mục phủ. Khi truy vấn chỉ cần các cột nằm trong chỉ mục (khóa chính luôn nằm sẵn trong đó), MySQL không phải quay lại bảng và
ExtrahiệnUsing index. Truy vấn trang Đơn của tôi lấy thêmtotalnên không phải chỉ mục phủ. Bỏtotalđi [Đã chạy: MySQL 8.0.44]:
EXPLAIN SELECT id, created_at FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC LIMIT 5;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+----------------------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_inbox | idx_inbox | 86 | const,const | 19 | 100.00 | Backward index scan; Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+----------------------------------+
- Ngoại lệ của tiền tố trái. Từ MySQL 8.0.13 có skip scan: nếu truy vấn chỉ cần cột trong chỉ mục (chỉ mục phủ) và cột đầu có ít giá trị khác nhau, MySQL có thể nhảy qua từng giá trị của cột đầu. Cùng chỉ mục
(user_id, status, created_at), truy vấnSELECT id WHERE status = 'paid'không còn làALL[Đã chạy: MySQL 8.0.44]:
EXPLAIN SELECT id FROM orders WHERE status = 'paid';
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------+
| 1 | SIMPLE | orders | NULL | range | idx_inbox | idx_inbox | 86 | NULL | 6666 | 100.00 | Using where; Using index for skip scan |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+----------------------------------------+
Using index for skip scan xuất hiện, nhưng rows vẫn lớn, và chỉ xảy ra khi truy vấn là chỉ mục phủ. Đừng lấy đó làm lý do bỏ qua quy tắc tiền tố trái.
- Chi phí ghi: dung lượng. Mỗi chỉ mục là một cây riêng phải được cập nhật ở mọi
INSERT,DELETEvàUPDATEđổi cột được lập chỉ mục. Dung lượng bảng 20000 dòng khi chưa có chỉ mục phụ, rồi sau khi tạo thêm ba chỉ mụcidx_inbox,idx_phonevàidx_created[Có thể khác nhau: tùy cấu hình và phiên bản]:
ANALYZE TABLE orders;
SELECT ROUND(data_length / 1024) AS data_kb, ROUND(index_length / 1024) AS index_kb
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'orders';
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| shop.orders | analyze | status | OK |
+---------------+---------+----------+----------+
+---------+----------+
| data_kb | index_kb |
+---------+----------+
| 1552 | 0 |
+---------+----------+
ANALYZE TABLE orders;
SELECT ROUND(data_length / 1024) AS data_kb, ROUND(index_length / 1024) AS index_kb
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'orders';
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| shop.orders | analyze | status | OK |
+---------------+---------+----------+----------+
+---------+----------+
| data_kb | index_kb |
+---------+----------+
| 1552 | 1248 |
+---------+----------+
Phần chỉ mục đã bằng khoảng 80 phần trăm phần dữ liệu, và mỗi lệnh ghi bây giờ phải chạm vào bốn cấu trúc thay vì một [Suy luận: đây là suy ra từ cấu trúc, bài chưa đo thời gian ghi].
- Hiểu lầm nâng cao. Có người tin rằng cột có độ phân biệt thấp không bao giờ nên nằm trong chỉ mục. Thực tế
statusnằm giữa chỉ mục ghép rất ổn khi đứng sauuser_id, vì lúc đó nó chia nhỏ thêm dải đã hẹp. - Giới hạn của bài. Bài này chưa nói đến chỉ mục tiền tố cho cột chuỗi dài, chỉ mục hàm, chỉ mục giảm dần (
DESC) vàEXPLAIN ANALYZE. - Đọc thêm. MySQL 8.0 Reference Manual, mục "How MySQL Uses Indexes": https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html và "Multiple-Column Indexes": https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html
Bài tiếp theo
Bài sau là DROP INDEX: gỡ chỉ mục thừa mà không làm hỏng khóa ngoại hay làm chậm truy vấn nóng. Mini-dự án nối tiếp: dọn các chỉ mục của bảng orders bằng cách đánh dấu vô hình trước khi xóa thật.
Lời giải và đáp án
Làm cùng mình, Bước 2. Hai cột là user_id, created_at:
CREATE INDEX idx_support ON orders (user_id, created_at);
EXPLAIN SELECT id, total
FROM orders
WHERE user_id = 25 AND created_at >= '2026-09-01'
ORDER BY created_at DESC;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | orders | NULL | range | idx_support | idx_support | 9 | NULL | 8 | 100.00 | Using index condition; Backward index scan |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------------------------+
key_len là 9 (4 byte của user_id cộng 5 byte của created_at), type là range, và không có Using filesort vì sau khi thu hẹp về khách 25, các mục đã nằm sẵn theo created_at.
Làm cùng mình, Bước 3. Đảo thành (created_at, user_id):
CREATE INDEX idx_support ON orders (created_at, user_id);
EXPLAIN SELECT id, total
FROM orders
WHERE user_id = 25 AND created_at >= '2026-09-01'
ORDER BY created_at DESC;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | orders | NULL | range | idx_support | idx_support | 9 | NULL | 3338 | 10.00 | Using index condition; Backward index scan |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------------------------+
Cột đầu là khoảng nên dải đọc là mọi đơn từ ngày 1 tháng 9 của tất cả khách, và user_id chỉ lọc thêm. Chỉ mục này không còn thu hẹp theo khách nữa, rows lớn hơn nhiều.
Bài 1.
EXPLAIN SELECT id FROM orders WHERE user_id = 10;
EXPLAIN SELECT id FROM orders WHERE user_id = 10 AND status = 'paid';
EXPLAIN SELECT id FROM orders
WHERE user_id = 10 AND status = 'paid' AND created_at > '2026-09-01';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | idx_inbox | idx_inbox | 4 | const | 33 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | idx_inbox | idx_inbox | 86 | const,const | 19 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | orders | NULL | range | idx_inbox | idx_inbox | 91 | NULL | 4 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
key_len lần lượt là 4, 86 và 91 (4, 4 cộng 82, 86 cộng 5). Điều kiện cuối là khoảng nên type thành range.
Bài 2. Chỉ mục (user_id, created_at) cho truy vấn không lọc trạng thái:
CREATE INDEX idx_uc ON orders (user_id, created_at);
EXPLAIN SELECT id, total, created_at FROM orders
WHERE user_id = 10
ORDER BY created_at DESC
LIMIT 5;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_uc | idx_uc | 4 | const | 33 | 100.00 | Backward index scan |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------+
So với chỉ mục (user_id, status, created_at) trên cùng truy vấn:
EXPLAIN SELECT id, total, created_at FROM orders
WHERE user_id = 10
ORDER BY created_at DESC
LIMIT 5;
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
| 1 | SIMPLE | orders | NULL | ref | idx_inbox | idx_inbox | 4 | const | 33 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+----------------+
Chỉ mục ba cột phải Using filesort vì giữa user_id và created_at còn cột status: các mục của khách 10 nằm theo status trước, nên chưa theo thời gian.
Bài 3. Chỉ mục (user_id, created_at, status):
CREATE INDEX idx_alt ON orders (user_id, created_at, status);
EXPLAIN SELECT id, total, created_at FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC LIMIT 5;
EXPLAIN SELECT id, total, created_at FROM orders
WHERE user_id = 10
ORDER BY created_at DESC LIMIT 5;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+----------------------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_alt | idx_alt | 4 | const | 33 | 10.00 | Using where; Backward index scan |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+----------------------------------+
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+
| 1 | SIMPLE | orders | NULL | ref | idx_alt | idx_alt | 4 | const | 33 | 100.00 | Backward index scan |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+---------------------+
Cả hai truy vấn đều Backward index scan và không có filesort. Truy vấn có lọc status chỉ cần lọc thêm theo cột nằm sẵn trong chỉ mục. Số lần đọc thật của truy vấn có lọc:
FLUSH STATUS;
SELECT id, total, created_at FROM orders
WHERE user_id = 10 AND status = 'paid'
ORDER BY created_at DESC LIMIT 5;
SHOW SESSION STATUS WHERE Variable_name LIKE 'Handler_read%' AND Value > 0;
+-------+-------+---------------------+
| id | total | created_at |
+-------+-------+---------------------+
| 19052 | 72.00 | 2026-10-05 20:12:00 |
| 18446 | 96.00 | 2026-09-27 00:06:00 |
| 16999 | 89.00 | 2026-09-05 21:39:00 |
| 16696 | 56.00 | 2026-09-01 11:36:00 |
| 15249 | 49.00 | 2026-08-11 09:09:00 |
+-------+-------+---------------------+
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_key | 1 |
| Handler_read_prev | 7 |
+-------------------+-------+
Hơn chỉ mục ba cột ở trên một chút (7 lần đọc ngược thay vì 4) vì phải đọc cả những mục status khác paid nằm xen kẽ, nhưng đổi lại một chỉ mục phục vụ cả hai truy vấn.
Tự kiểm tra.
- Giá trị khóa chính của dòng tương ứng, để quay lại bảng khi cần cột khác.
- Vì 12001 trong 20000 đơn là
paid: chỉ mục trỏ tới 60 phần trăm bảng, nên còn phải đọc 12001 mục rồi lọcuser_idvà sắp xếp. - Chỉ một cột (
a). Cộtbbị bỏ qua nêncchỉ được lọc sau, không thu hẹp phạm vi. - Điều kiện không khớp tiền tố trái của chỉ mục, hoặc điều kiện bọc hàm, so sánh sai kiểu hay dùng
LIKE '%x'làm mất điểm bắt đầu. Ngoài ra bộ tối ưu có thể thấy chỉ mục trỏ tới quá nhiều dòng nên chọn quét bảng. - So với chuỗi:
phone = '0937506567'. So với số làm MySQL đổi từng chuỗi sang số nên không dùng được chỉ mục, và hai số điện thoại khác nhau (có và không có số 0 đầu) cùng bằng một số nên trả sai dòng.