4. Index

Index là công cụ mạnh nhất để tăng tốc truy vấn, nhưng dùng sai thì tự làm chậm hệ thống. Cần hiểu B+Tree để biết khi nào MySQL dùng được index và khi nào không.

Index là gì

Hình dung một cuốn sách 2000 trang. Muốn tìm một từ khoá mà phải lật từng trang thì rất lâu. Mục lục cho phép nhảy thẳng tới trang cần. Bảng vài chục triệu dòng cũng vậy: không có index, MySQL phải quét từng dòng.

Index là một cấu trúc dữ liệu đã sắp xếp theo một hoặc vài cột. Đổi lại tốc độ đọc, database trả giá bằng dung lượng lưu trữ, chi phí cập nhật index mỗi lần ghi, và việc phải thiết kế đúng cho các query thật.

B+Tree: cấu trúc index của InnoDB

• Mỗi node là một page 16KB (mặc định của innodb_page_size). MySQL luôn đọc ghi cả page.
• Node trong chỉ chứa khoá và số hiệu page con; mỗi page chứa được khoảng 900–1000 entry với khoá BIGINT. Vì vậy cây chỉ cần 3–4 tầng cho hàng trăm triệu tới hàng tỷ dòng (thử máy tính ở chương 1).
• Node lá chứa các entry đã sắp xếp theo khoá và được nối với nhau bằng danh sách liên kết hai chiều, nên đọc theo khoảng (range) chỉ cần đi ngang qua tầng lá.
• Các tầng trên gần như luôn nằm sẵn trong buffer pool, nên một lần tra thường tốn tối đa một lần đọc disk.

Index càng nhỏ thì càng ít page, càng nhiều phần nằm vừa trong RAM. Đây là lý do kiểu dữ liệu ở chương 2 quan trọng.

Clustered index và secondary index

Trong InnoDB, bảng chính là một B+Tree sắp theo khoá chính, gọi là clustered index: lá của nó chứa nguyên dòng dữ liệu. Không khai báo PRIMARY KEY thì InnoDB dùng index UNIQUE NOT NULL đầu tiên; không có nữa thì tự tạo cột ẩn 6 byte.

Mọi index khác là secondary index, lưu riêng, và lá của nó chứa (các cột được index + giá trị khoá chính). Tra qua secondary index nghĩa là đi hai cây: tìm PK trong secondary index, rồi dùng PK tra clustered index để lấy cả dòng. Bấm từng bước để xem.

Bước 0: chưa đọc page nào.

0page 16KB đã đọc (cây minh hoạ 2 tầng; thực tế 3–4 tầng)

• Khoá chính to thì mọi index to. Mỗi entry của secondary index đều chứa PK. PK CHAR(36) (36 byte) thay cho BIGINT (8 byte) làm phình tất cả index, không chỉ bảng.
• Covering index. Nếu index đã đủ mọi cột câu query cần, MySQL dừng ở secondary index, bỏ hẳn bước tra clustered index (EXPLAIN: Using index).
• Khoá chính nên tăng dần. Dòng được xếp vật lý theo PK. PK ngẫu nhiên chèn vào giữa cây gây page split và phân mảnh. Dùng AUTO_INCREMENT, Snowflake hoặc UUIDv7.

Khi nào MySQL dùng được index

Đánh index xong không có nghĩa query nào cũng nhanh. MySQL chỉ seek (nhảy thẳng tới vị trí) được khi điều kiện khớp với thứ tự sắp xếp của index.

So sánh phải bắt đầu từ đầu giá trị

• WHERE name LIKE 'abc%': dùng được (range).
• WHERE name LIKE '%abc%' hoặc '%abc': không seek được. Cần tìm kiếm trong chuỗi thì dùng FULLTEXT index (có parser ngram cho tiếng Việt, Trung, Nhật) hoặc search engine.
• Bọc cột trong hàm (YEAR(created_at) = 2026) hoặc để MySQL phải ép kiểu cột cũng làm mất khả năng seek (chương 5).

Leftmost prefix với index nhiều cột

Index (A, B, C) sắp theo A, rồi B trong cùng A, rồi C. MySQL seek được theo tiền tố bên trái:

• WHERE A = ?, WHERE A = ? AND B = ?, WHERE A = ? AND B = ? AND C > ?: dùng được.
• WHERE B = ?, WHERE C = ?: không seek được vì thiếu A. Vẫn có hai lối thoát: nếu index đã phủ đủ cột cần đọc, MySQL có thể quét toàn bộ index (type index), rẻ hơn đọc bảng; và từ 8.0.13 có skip scan: nếu A ít giá trị, MySQL seek B lần lượt trong từng nhóm A (Extra: Using index for skip scan).
• Thứ tự viết điều kiện trong WHERE không quan trọng: WHERE B = ? AND A = ? vẫn dùng được (A, B).

Range chặn việc seek các cột phía sau

Với index (A, B) và WHERE A < 10 AND B = 100, MySQL chỉ dùng A để seek. Cột B không dùng để seek, nhưng từ 5.6, Index Condition Pushdown (ICP) vẫn lọc B ngay trên entry index trước khi tra bảng (Extra: Using index condition). IN không chặn: A IN (1, 2, 3) AND B = 100 dùng được cả hai cột (thành 3 điểm seek). Tập giá trị nhỏ thì đổi range thành IN.

Index Lab: thử với index 2 cột

Bảng orders có 96 dòng. Mỗi ô là một entry ở tầng lá của B+Tree, xếp đúng thứ tự index. Đổi điều kiện WHERE hoặc thứ tự cột để xem MySQL phải đọc bao nhiêu entry.

Thử nhanh

Indexidx (customer_id, status)idx (status, customer_id)

customer_id (1–12)

bỏ qua=IN<>

status (1–4, 55% là 1)

bỏ qua=IN<>

IN nhận nhiều giá trị, cách nhau bằng dấu phẩy: 1,2,5

SELECT * FROM orders WHERE customer_id = 3 AND status = 2;

0entry index đọc

0lần tra sang bảng

0dòng trả về

type	key	key_len	rows	Extra
ref	idx_cust_status	5	0	NULL

Cột đầu so sánh bằng nên cột thứ hai vẫn dùng để seek. Số entry đọc bằng số dòng khớp: không đọc thừa.

khớp điều kiệnđọc nhưng loạikhông chạm tới

Ô hiển thị status; mỗi hàng là một nhóm customer_id

customer_id=1

111122224

customer_id=2

1223

customer_id=3

11111113344

customer_id=4

1111223333

customer_id=5

11112233

customer_id=6

111223333

customer_id=7

11122333

customer_id=8

1111122

customer_id=9

1112233

customer_id=10

11112344

customer_id=11

11111233

customer_id=12

1111222

Thứ tự cột trong index nhiều cột

Thứ tự cột được quyết định bởi tập query sẽ dùng index, theo các quy tắc sau (ưu tiên từ trên xuống):

1. Cột hay xuất hiện một mình trong WHERE đặt bên trái, để nhiều query cùng dùng được một index (leftmost prefix).

2. Quy tắc ESR: Equality → Sort → Range.

   • E: cột so sánh bằng (=, IN) đứng đầu, để seek thẳng.
   • S: cột trong ORDER BY đứng tiếp. Khi các cột phía trước đã cố định bằng =, các entry nằm sẵn theo thứ tự sort: không cần filesort, và có LIMIT thì dừng sớm.
   • R: cột range (>, <, BETWEEN) đứng cuối. Range đặt trước sẽ phá thứ tự của các cột sau nó.

   Không có ORDER BY thì đơn giản là E rồi R: với WHERE A = 1 AND B BETWEEN 100 AND 200, index (A, B) seek gọn đúng khoảng cần đọc; (B, A) vẫn được dùng nhưng phải đọc mọi entry có B trong khoảng rồi lọc A.

3. Selectivity chỉ là tiêu chí phụ. Khi mọi cột đều so sánh bằng, thứ tự không ảnh hưởng số entry phải đọc: WHERE status = ? AND customer_id = ? đọc đúng cùng số entry dù index là (status, customer_id) hay (customer_id, status), vì B+Tree seek thẳng tới tổ hợp hai giá trị. Thử preset “Đảo thứ tự cột” trong Index Lab.

Ví dụ ESR: WHERE customer_id = ? AND created_at >= '2026-01-01' ORDER BY total DESC LIMIT 10.

• (customer_id, created_at, total) (E-R-S): seek gọn khoảng thời gian, nhưng phải đọc mọi đơn khớp rồi sort theo total để lấy 10 dòng đầu.
• (customer_id, total, created_at) (E-S-R): đọc theo total giảm dần, lọc created_at ngay trên index (ICP), dừng khi đủ 10 dòng. Không filesort.

E-S-R thắng khi khoảng range khớp nhiều dòng và LIMIT nhỏ; E-R-S thắng khi khoảng range rất hẹp. Đo bằng EXPLAIN ANALYZE với dữ liệu thật.

Cột ít giá trị đứng đầu vẫn có thể rất tốt: index (status, created_at) phục vụ “lấy 100 đơn pending cũ nhất” (WHERE status = 0 ORDER BY created_at LIMIT 100) mà không cần sort, dù status chỉ có 4 giá trị.

Index cho ORDER BY, GROUP BY và LIMIT

B+Tree trả entry theo thứ tự của index. Nếu thứ tự đó trùng với ORDER BY, MySQL không cần sort riêng (filesort) và có thể dừng ngay khi đủ LIMIT. Với index (a, b, c):

Query	Filesort?	Vì sao
WHERE a = 1 ORDER BY b	không	a cố định, các entry đã theo thứ tự b
WHERE a = 1 ORDER BY b, c	không	Đúng thứ tự cột của index
WHERE a = 1 AND b = 2 ORDER BY c DESC	không	Đọc ngược index (backward scan)
ORDER BY a LIMIT 10	không	Đọc 10 entry đầu rồi dừng
WHERE a = 1 ORDER BY c	có	Bỏ qua b: trong nhóm a = 1, c không có thứ tự chung
WHERE a > 1 ORDER BY b	có	Range trên a: b chỉ có thứ tự trong từng giá trị a
WHERE a IN (1, 2) ORDER BY b	có	IN là nhiều khoảng, mỗi khoảng có thứ tự riêng
ORDER BY a, b DESC	có	Trộn chiều sort; cần index (a ASC, b DESC)
ORDER BY b	có	Không theo leftmost prefix

GROUP BY theo cùng quy tắc: GROUP BY a, b gom được theo thứ tự index, không cần bảng tạm; GROUP BY trên cột không theo prefix thì cần bảng tạm (Using temporary). Query dạng SELECT a, MIN(b) … GROUP BY a có thể nhảy qua từng nhóm (Using index for group-by, loose index scan).

Filesort không phải lúc nào cũng xấu: sort vài trăm dòng trong sort_buffer_size rất nhanh. Đáng lo khi phải sort hàng trăm nghìn dòng chỉ để lấy 20 dòng đầu.

Cardinality, selectivity và prefix index

• Cardinality: số giá trị khác nhau (ước tính) trong index, xem bằng SHOW INDEX FROM orders. Selectivity = cardinality / số dòng: bằng 1 với UNIQUE, gần 0 với cột cờ.
• InnoDB ước tính bằng cách lấy mẫu (innodb_stats_persistent_sample_pages, mặc định 20 page), tự cập nhật khi hơn 10% số dòng thay đổi. Thống kê lệch thì plan sai: chạy ANALYZE TABLE.
• Với điều kiện range và IN, optimizer còn index dive: đọc thật vào index để đếm số dòng trong khoảng. Khi IN có nhiều hơn eq_range_index_dive_limit (200) giá trị, nó chuyển sang dùng thống kê, kém chính xác hơn.
• Index đơn trên cột ít giá trị (is_deleted, gender) hầu như vô ích khi đứng một mình. Chúng có ích khi làm cột đầu của index nhiều cột phục vụ query cụ thể, hoặc khi tìm giá trị hiếm (status = 'failed' chỉ chiếm 0,1%).

Chọn độ dài prefix index: tăng dần n tới khi selectivity của prefix gần bằng của cả cột. Prefix index không làm covering index được và không phục vụ ORDER BY.

SELECT COUNT(DISTINCT email) / COUNT(*)            AS full_sel,
       COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS p10,
       COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS p15
FROM users;
ALTER TABLE users ADD INDEX idx_email (email(15));

Cái giá của index

• Ghi chậm hơn. Mỗi INSERT, UPDATE cột được index, DELETE đều phải cập nhật từng index; page đầy thì phải split. Change buffer giúp giảm phần ghi ngẫu nhiên vào secondary index không unique.
• Tốn RAM. Index cạnh tranh chỗ trong buffer pool với dữ liệu. Index thừa làm giảm tỷ lệ cache hit của mọi thứ khác.
• Không phải lúc nào cũng được dùng. Optimizer chọn theo chi phí. Nếu 95% dòng có status = 1, thì WHERE status = 1 qua secondary index nghĩa là hàng triệu lần tra clustered index ngẫu nhiên; quét toàn bảng tuần tự rẻ hơn, nên MySQL bỏ qua index.
• Dọn index thừa: sys.schema_unused_indexes (không được dùng từ lần khởi động gần nhất), sys.schema_redundant_indexes (ví dụ (A) thừa khi đã có (A, B)). Trước khi drop, đặt INVISIBLE để thử.

Index cho JOIN

Khi join hai bảng lớn, tốc độ phụ thuộc vào index trên cột join ở bảng được tra cứu (bảng “bên trong” của vòng lặp). Ba lỗi hay gặp:

• Bảng được tra cứu không có index trên cột join. MySQL 8.0.18+ sẽ dùng hash join, đỡ hơn trước, nhưng vẫn phải quét toàn bảng đó.
• Khác nhóm kiểu: cột VARCHAR join với cột số buộc MySQL ép kiểu cột chuỗi, mất index. INT join BIGINT thì vẫn dùng index được, nhưng nên đồng nhất kiểu.
• Khác charset hoặc collation giữa hai cột chuỗi (utf8mb3 với utf8mb4, utf8mb4_general_ci với utf8mb4_0900_ai_ci): MySQL phải chuyển đổi một phía và có thể mất index.

UNIQUE index

• Đảm bảo không trùng ở mức database (email, mã đơn, mã idempotency). App tự kiểm tra trước khi insert là không đủ: hai request song song đều thấy “chưa có” rồi cùng insert.
• Đọc: tra = trên UNIQUE dừng ngay khi thấy dòng (EXPLAIN const hoặc eq_ref).
• Ghi: mỗi INSERT phải đọc page để kiểm tra trùng, nên không dùng được change buffer; ghi hàng loạt chậm hơn index thường.
• Upsert và idempotency: INSERT … ON DUPLICATE KEY UPDATE, INSERT IGNORE dựa vào UNIQUE.
• UNIQUE cho phép nhiều dòng NULL. Tìm = trên UNIQUE và thấy dòng thì chỉ cần record lock, không gap lock (chương 7).

Index merge

Khi điều kiện chạm nhiều index đơn, MySQL có thể đọc từng index rồi gộp kết quả:

• WHERE a = 1 OR b = 2 với index riêng trên a và b: hợp hai tập (Using union(idx_a, idx_b)). Nếu không dùng được, viết thành hai câu nối bằng UNION ALL.
• WHERE a = 1 AND b = 2 với hai index đơn: giao hai tập (Using intersect(idx_a, idx_b)). Thường chậm hơn nhiều so với một index (a, b). Thấy intersect trong EXPLAIN là dấu hiệu nên tạo index nhiều cột.

Các loại index khác

Loại	Ý nghĩa	Ví dụ
Covering	Index chứa đủ cột cần đọc, không tra bảng	INDEX (customer_id, status, total)
Prefix	Chỉ index N ký tự đầu của chuỗi dài	INDEX (url(100))
Functional (8.0.13)	Index trên biểu thức	INDEX ((LOWER(email)))
Descending (8.0)	Phục vụ ORDER BY trộn ASC/DESC	INDEX (a ASC, b DESC)
Invisible (8.0)	Optimizer bỏ qua nhưng index vẫn được cập nhật; thử trước khi drop	ALTER TABLE t ALTER INDEX i INVISIBLE
FULLTEXT	Tìm từ trong văn bản	MATCH(title) AGAINST('...')
Index trên generated column	Thay cho partial index (MySQL không có CREATE INDEX … WHERE)	active_email AS (IF(deleted_at IS NULL, email, NULL)) + UNIQUE

Hash index chỉ có ở engine MEMORY. InnoDB có adaptive hash index do chính nó tự dựng trong RAM cho các page hay tra cứu; bạn không tạo trực tiếp được.

Tạo và xoá index trên bảng lớn

• Thêm index là online: ALTER TABLE orders ADD INDEX idx_cs (customer_id, status), ALGORITHM=INPLACE, LOCK=NONE. Bảng vẫn đọc ghi bình thường, nhưng MySQL phải quét toàn bảng và sort, tốn nhiều I/O; thay đổi trong lúc dựng được ghi tạm vào log (innodb_online_alter_log_max_size).
• Replica: chỉ bắt đầu dựng index sau khi source xong, nên lag tăng đúng bằng thời gian dựng. Bảng hàng trăm GB thường dùng gh-ost hoặc pt-online-schema-change.
• Xoá index chỉ sửa metadata nên nhanh. Trước khi xoá, đặt INVISIBLE và theo dõi vài ngày; có query chậm đi thì đặt VISIBLE lại ngay, không phải dựng lại.
• Đổi tên index là INSTANT.

Khi index không phải lời giải

Bảng nhỏ (vài nghìn dòng) không nhất thiết cần index ngoài khoá chính. Truy vấn phân tích quét nhiều dòng (OLAP: tổng hợp theo tháng, theo vùng) không nên cố giải bằng index trên DB giao dịch. Các hướng khác: bảng tổng hợp sẵn, partition theo thời gian, đẩy dữ liệu qua CDC sang database phân tích dạng cột (ClickHouse, BigQuery) hoặc search engine.

Quy trình thiết kế index cho một query

1. Lấy query thật và tần suất của nó từ slow log hoặc performance_schema. Ưu tiên query tốn tổng thời gian nhiều nhất.
2. Phân loại cột: cột so sánh bằng, cột ORDER BY/GROUP BY, cột range, cột trong SELECT.
3. Dựng thứ tự theo ESR. Nếu query rất nóng và các cột SELECT nhỏ, cân nhắc thêm chúng vào cuối để thành covering index.
4. Xem index sẵn có: mở rộng một index cũ (thêm cột vào cuối) thường tốt hơn tạo thêm index gần giống.
5. So EXPLAIN / EXPLAIN ANALYZE trước và sau; đo cả ảnh hưởng tới tốc độ ghi.
6. Sau vài tuần, xem sys.schema_unused_indexes và sys.schema_redundant_indexes để dọn.

Ghi nhớ

1. Bảng là clustered index theo PK; secondary index lưu PK ở lá.
2. Seek theo leftmost prefix; range chặn seek các cột sau (ICP vẫn lọc); IN không chặn.
3. Thứ tự cột: cột dùng một mình bên trái, rồi Equality → Sort → Range.
4. ORDER BY khớp index thì không filesort và LIMIT dừng sớm.
5. UNIQUE để chặn trùng ở mức DB; thấy index merge intersect thì nên tạo index nhiều cột.
6. Đánh index theo query thật, xác nhận bằng EXPLAIN, thử INVISIBLE trước khi xoá.