Index Nâng Cao Trong MySQL: Khi Có Index Mà Query Vẫn Chậm

Giới Thiệu

Ở bài Index trong MySQL bạn đã biết index là gì, cách tạo và các loại index. Bài này dành cho tình huống khó chịu hơn nhiều: bảng đã có index, EXPLAIN cũng báo dùng index, nhưng query vẫn chạy 10-15 giây.

Chúng ta sẽ đi qua một case có thật. Đó là API danh sách đơn hàng trên bảng 1.6 triệu dòng. Lọc theo mã sản phẩm (product_cd) thì API mất 17 giây, trong khi kết quả chỉ có 4 dòng. Sau khi sửa, cùng API chạy dưới 1 giây. Không thêm index mới, không đổi schema, chỉ thêm đúng một điều kiện WHERE.

Để hiểu vì sao, bạn cần nắm được những thứ sau:

• Composite index và quy tắc leftmost prefix
• Đọc key_len trong EXPLAIN để biết index được dùng bao nhiêu cột
• Optimizer ước lượng số dòng như thế nào và vì sao nó ước lượng sai khi dữ liệu bị lệch (data skew)
• Thứ tự join, STRAIGHT_JOIN, FORCE INDEX, và cách "gợi ý" đường đi cho optimizer mà không cần hint
• Covering index, deferred join và chi phí của COUNT(*) khi phân trang

Yêu Cầu

• Đã đọc bài Index trong MySQL và EXPLAIN Statement
• MySQL 8.0+ hoặc MariaDB 10.6+ (các ví dụ chạy được trên cả hai, chỗ nào khác nhau sẽ ghi rõ)
• Biết đọc output của EXPLAIN ở mức cơ bản: type, key, rows, Extra

Bối Cảnh: Schema Của Case Study

Hệ thống bán hàng có các bảng sau (đã đơn giản hóa):

-- Sản phẩm: ~100k dòng
CREATE TABLE products (
  id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  product_cd  VARCHAR(10) NOT NULL,
  rev_no   BIGINT NOT NULL DEFAULT 0,   -- 0 = bản hiện hành, >0 = bản lịch sử
  name      VARCHAR(255),
  UNIQUE KEY uk_products (product_cd, rev_no)
);

-- SKU = sản phẩm x màu x size: ~1M dòng
CREATE TABLE product_variants (
  id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  sku_cd    VARCHAR(10) NOT NULL,
  product_cd  VARCHAR(10) NOT NULL,
  rev_no   BIGINT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_product_variants (sku_cd, rev_no),
  KEY idx_variants_product (product_cd)
);

-- Đơn hàng (mỗi dòng là một SKU trong đơn): ~1.66M dòng
CREATE TABLE orders (
  order_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no      VARCHAR(10) NOT NULL,
  sku_cd     VARCHAR(10) NOT NULL,
  status_cd  CHAR(2) NOT NULL,          -- charset utf8mb4
  rev_no    BIGINT NOT NULL DEFAULT 0,
  deleted_at DATETIME NULL,
  KEY idx_orders_sku (sku_cd),
  KEY idx_orders_active_no (rev_no, order_no, sku_cd),
  KEY idx_orders_active_status_no (rev_no, deleted_at, status_cd, order_no, sku_cd)
);

-- Phiếu giao hàng: ~2.7M dòng
CREATE TABLE shipments (
  id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no     VARCHAR(10) NOT NULL,
  sku_cd    VARCHAR(10) NOT NULL,
  rev_no   BIGINT NOT NULL DEFAULT 0,
  deleted_at DATETIME NULL,
  KEY idx_shipments_order_sku_active (order_no, sku_cd, rev_no, deleted_at)
);

Có một chi tiết rất quan trọng: cột rev_no. Đây là pattern "lưu lịch sử trong cùng bảng". Bản ghi đang dùng có rev_no = 0. Mỗi lần sửa, bản cũ được copy ra với rev_no là một số tăng dần. Vì vậy hầu như query nào cũng có WHERE rev_no = 0, và người thiết kế đặt rev_no làm cột đầu tiên của nhiều composite index.

Query của màn hình danh sách (phase 1, lấy id của một trang):

SELECT o.order_id
FROM orders o
JOIN order_statuses os ON o.status_cd = os.status_cd AND os.rev_no = 0
JOIN product_variants sku  ON o.sku_cd = sku.sku_cd     AND sku.rev_no = 0
JOIN products p      ON sku.product_cd = p.product_cd AND p.rev_no = 0
WHERE o.rev_no = 0
  AND o.deleted_at IS NULL
  AND EXISTS (SELECT 1 FROM shipments r
              WHERE r.order_no = o.order_no AND r.sku_cd = o.sku_cd
                AND r.rev_no = 0 AND r.deleted_at IS NULL)
  AND p.product_cd = 'P10023'
ORDER BY o.order_no, o.sku_cd
LIMIT 20;

Chỉ có 4 SKU thuộc sản phẩm P10023 và 4 đơn hàng tương ứng. Vậy mà query này mất 8.5 đến 12.4 giây, còn câu COUNT(*) để phân trang mất thêm 13.9 giây.

Bước 1: Composite Index Và Quy Tắc Leftmost Prefix

Composite index (a, b, c) được sắp xếp giống một danh bạ điện thoại: sắp theo a trước, trong cùng một a thì sắp theo b, rồi đến c. Vì vậy MySQL chỉ dùng được index này khi điều kiện bắt đầu từ cột ngoài cùng bên trái:

MySQL 8.0.13+ có skip scan, cho phép dùng index khi thiếu cột đầu, nhưng chỉ hiệu quả khi cột đầu có rất ít giá trị khác nhau. Đừng thiết kế index dựa vào nó.

Áp vào case của chúng ta với index idx_orders_active_status_no (rev_no, deleted_at, status_cd, order_no, sku_cd):

• rev_no = 0: seek được
• deleted_at IS NULL: seek được (IS NULL được coi là so sánh bằng)
• status_cd = os.status_cd: seek được
• order_no, sku_cd: không có điều kiện, vì filter của ta là theo product_cd, nằm ở bảng khác

Tức là khi đi vào orders qua index này, MySQL chỉ thu hẹp được đến mức "tất cả đơn active có trạng thái X". Con số đó là hàng trăm nghìn dòng.

Thứ Tự Cột: Selectivity Không Phải Là Tất Cả

Lời khuyên phổ biến là "đặt cột có selectivity cao lên đầu". Thực tế phức tạp hơn. Thứ tự cột nên dựa trên:

1. Cột so sánh bằng (=) đứng trước cột range (>, <, BETWEEN, LIKE 'x%')
2. Cột hay xuất hiện trong WHERE của nhiều query đứng trước, để một index phục vụ được nhiều query
3. Cột dùng cho ORDER BY đứng ngay sau các cột =, để bỏ được bước filesort
4. Trong các cột cùng nhóm, ưu tiên cột lọc mạnh hơn

rev_no đứng đầu thỏa điều kiện 1 và 2, nên thiết kế này không sai. Vấn đề nằm ở chỗ khác, ta sẽ thấy ở Bước 3.

Bước 2: Đọc key_len Để Biết Index Dùng Bao Nhiêu Cột

Cột key trong EXPLAIN chỉ cho biết index nào được dùng. Cột key_len mới cho biết index được dùng sâu đến đâu. Đây là cột hay bị bỏ qua nhất.

Cách tính key_len (InnoDB):

Với idx_orders_active_status_no (rev_no, deleted_at, status_cd, ...):

rev_no     BIGINT NOT NULL       = 8
deleted_at  DATETIME NULL         = 5 + 1 = 6
status_cd   CHAR(2) utf8mb4       = 2 × 4 = 8
                                  ---
                                  22 → dùng 3 cột đầu (khớp với EXPLAIN ở Bước 3)

Một số trường hợp thường gặp:

• key_len = 8: chỉ dùng rev_no. Gần như vô dụng nếu 60% bảng có rev_no = 0
• key_len = 14: dùng rev_no + deleted_at
• key_len bằng tổng độ dài mọi cột: dùng hết index, lý tưởng

Một trick khác: cột ref cho biết giá trị nào được đưa vào từng phần của key. Ví dụ ref = const,const,os.status_cd nghĩa là 2 cột đầu so với hằng số, cột thứ 3 lấy từ bảng os đã join trước đó.

Quy tắc: khi query chậm mà key có giá trị, hãy nhìn ngay key_len và ref. Nếu index chỉ được dùng ở các cột "ít lọc", query đang quét một khoảng rất lớn của index.

Bước 3: Optimizer Ước Lượng Số Dòng Như Thế Nào?

Đây là EXPLAIN của query chậm:

+-----+-------+--------+--------------------------+---------+---------------------------+------+------------------------------+
| id  | table | type   | key                      | key_len | ref                       | rows | Extra                        |
+-----+-------+--------+--------------------------+---------+---------------------------+------+------------------------------+
|  1  | p   | const  | uk_products                | 50      | const,const               | 1    | Using temporary; filesort    |
|  1  | os   | index  | uk_order_statuses           | 16      | NULL                      | 4    | Using where; Using index     |
|  1  | o     | ref    | idx_orders_active_status_no  | 22      | const,const,os.status_cd | 1    | Using where; Using index     |
|  1  | sku   | eq_ref | uk_product_variants            | 50      | o.sku_cd,const           | 1    | Using where                  |
|  1  | r     | ref    | idx_shipments_order_sku_...  | ...     | o.order_no,o.sku_cd,...    | 1    | Using where                  |
+-----+-------+--------+--------------------------+---------+---------------------------+------+------------------------------+

Đọc từ trên xuống, đây là kế hoạch optimizer chọn:

1. p: tìm sản phẩm P10023, 1 dòng. Tốt.
2. os: quét bảng trạng thái, 4 dòng.
3. o: với mỗi trạng thái, seek vào orders qua (rev_no=0, deleted_at IS NULL, status_cd=?). Ước lượng: 1 dòng.
4. sku: với mỗi đơn hàng, tra SKU rồi mới kiểm tra sku.product_cd = 'P10023'.

Bước 3 là chỗ hỏng. Optimizer nghĩ mỗi trạng thái chỉ có 1 đơn active, nên kế hoạch "4 × 1 = 4 dòng rồi join tiếp" trông rất rẻ. Thực tế mỗi trạng thái có hàng trăm nghìn đơn active. MySQL đọc gần như toàn bộ ~1M đơn active, join từng dòng với product_variants, rồi mới loại bỏ gần hết vì không thuộc sản phẩm P10023.

Vì Sao Lại Ước Lượng Là 1?

Với điều kiện so sánh bằng trong join (ref access), InnoDB không đếm thật. Nó dùng index statistics, cụ thể là chỉ số "trung bình bao nhiêu dòng cho mỗi giá trị của prefix" (trong MySQL gọi là records_per_key, có thể xem gián tiếp qua Cardinality trong SHOW INDEX):

rows_per_value ≈ tổng số dòng / số giá trị khác nhau (cardinality)

Hãy xem dữ liệu rev_no:

SELECT rev_no = 0 AS is_active, COUNT(*)
FROM orders
GROUP BY is_active;
-- is_active = 1 : 1,030,000 dòng
-- is_active = 0 :   630,000 dòng, mỗi dòng một rev_no KHÁC NHAU

rev_no có khoảng 630 nghìn giá trị khác nhau. Trung bình mỗi giá trị chỉ ứng với khoảng 2-3 dòng. Optimizer áp con số trung bình này cho rev_no = 0 và kết luận "chỉ vài dòng". Thực tế rev_no = 0 chiếm 1.03 triệu dòng.

Đây là data skew: phân bố dữ liệu lệch nặng về một giá trị. Thống kê dạng "trung bình" không mô tả được nó.

Vì Sao ANALYZE TABLE Không Cứu Được?

Phản xạ đầu tiên của nhiều người là chạy ANALYZE TABLE orders. Lệnh này cập nhật lại thống kê. Nhưng thống kê vốn đã đúng: trung bình đúng là 2-3 dòng mỗi giá trị. Vấn đề nằm ở phân bố, không phải ở độ cũ của thống kê. Chạy bao nhiêu lần kết quả vẫn vậy.

Histogram (MySQL 8.0+, MariaDB 10.0+) mô tả phân bố tốt hơn:

-- MySQL 8.0
ANALYZE TABLE orders UPDATE HISTOGRAM ON rev_no WITH 64 BUCKETS;

-- MariaDB
SET SESSION histogram_size = 254;
ANALYZE TABLE orders PERSISTENT FOR COLUMNS (rev_no) INDEXES ();

Nhưng đừng kỳ vọng quá nhiều. Trên MySQL, histogram chủ yếu dùng cho cột không có index (với cột có index, optimizer ưu tiên index dive hoặc index statistics). Với ref access trong join như trên, ước lượng vẫn đến từ records_per_key. Histogram có thể giúp ở một số query nhưng không đảm bảo sửa được join order.

Dấu hiệu nhận biết data skew trong EXPLAIN: type = ref, ref = const trên một cột "trạng thái" hoặc "cờ" (rev_no, is_deleted, status), rows rất nhỏ (1, 2), nhưng query thực tế chạy rất lâu. Dùng EXPLAIN ANALYZE (MySQL 8.0.18+) hoặc ANALYZE FORMAT=JSON (MariaDB) để so sánh rows ước lượng với số dòng thực tế:

-- MySQL 8
EXPLAIN ANALYZE SELECT ...;
-- -> ... (cost=1.2 rows=1) (actual time=0.05..9832.1 rows=1030412 loops=4)

-- MariaDB
ANALYZE FORMAT=JSON SELECT ...;
-- "rows": 1, "r_rows": 257603

Ước lượng rows=1 trong khi thực tế là rows=1030412. Lệch 6 bậc độ lớn như vậy là đủ kết luận.

Bước 4: Ép Thứ Tự Join Với STRAIGHT_JOIN Và FORCE INDEX

Ta biết kế hoạch đúng phải là:

products (1 dòng) → product_variants của sản phẩm đó (4 dòng) → orders theo sku_cd (vài dòng) → ...

Cách trực tiếp nhất là ép MySQL đi theo thứ tự đó:

SELECT STRAIGHT_JOIN o.order_id
FROM products p
JOIN product_variants sku ON sku.product_cd = p.product_cd AND sku.rev_no = 0
JOIN orders o FORCE INDEX (idx_orders_sku) ON o.sku_cd = sku.sku_cd
JOIN order_statuses os ON o.status_cd = os.status_cd AND os.rev_no = 0
WHERE p.rev_no = 0
  AND p.product_cd = 'P10023'
  AND o.rev_no = 0
  AND o.deleted_at IS NULL
  AND EXISTS (SELECT 1 FROM shipments r
              WHERE r.order_no = o.order_no AND r.sku_cd = o.sku_cd
                AND r.rev_no = 0 AND r.deleted_at IS NULL)
ORDER BY o.order_no, o.sku_cd
LIMIT 20;

Giải thích:

• SELECT STRAIGHT_JOIN: join các bảng đúng theo thứ tự viết trong FROM, optimizer không được đảo
• Bảng selective nhất (products với 1 dòng) được đặt đầu tiên
• FORCE INDEX (idx_orders_sku): ép dùng index (sku_cd) khi vào orders. Nếu không có hint này, optimizer có thể vẫn chọn idx_orders_active_no vì tin rằng rev_no = 0 chỉ có 1 dòng

Kết quả đo:

(Khoảng 0.7-0.8s trong đó là latency mạng từ máy client đến RDS. Thời gian query thật chỉ vài ms.)

Nhược Điểm Của Hint

Hint giải quyết được vấn đề ngay, nhưng có cái giá:

• Cứng nhắc: khi không lọc theo sản phẩm (ví dụ chỉ lọc theo trạng thái), đi từ products lại là đường sai. Bạn phải có hai nhánh code, một có hint và một không
• Dễ vỡ khi đổi tên index: FORCE INDEX (idx_orders_sku) báo lỗi nếu index bị đổi tên hoặc bị xóa
• Ngăn optimizer tiến bộ: khi dữ liệu thay đổi hoặc nâng cấp version, kế hoạch bị ép có thể không còn tối ưu

Vì vậy hãy coi hint là phương án cuối cùng hoặc biện pháp tạm thời. Có một cách tốt hơn.

Bước 5: Cho Optimizer Một Đường Đi Selective (Không Cần Hint)

Ý tưởng: thay vì ép thứ tự, hãy cho optimizer thêm thông tin để nó tự tìm ra đường đúng. Ta giữ nguyên query gốc, chỉ thêm một điều kiện:

SELECT o.order_id
FROM orders o
JOIN order_statuses os ON o.status_cd = os.status_cd AND os.rev_no = 0
JOIN product_variants sku  ON o.sku_cd = sku.sku_cd     AND sku.rev_no = 0
JOIN products p      ON sku.product_cd = p.product_cd AND p.rev_no = 0
WHERE o.rev_no = 0
  AND o.deleted_at IS NULL
  AND EXISTS (SELECT 1 FROM shipments r
              WHERE r.order_no = o.order_no AND r.sku_cd = o.sku_cd
                AND r.rev_no = 0 AND r.deleted_at IS NULL)
  AND p.product_cd = 'P10023'
  -- Dòng mới: lọc trực tiếp trên orders
  AND o.sku_cd IN (SELECT s2.sku_cd FROM product_variants s2
                    WHERE s2.product_cd = 'P10023' AND s2.rev_no = 0)
ORDER BY o.order_no, o.sku_cd
LIMIT 20;

EXPLAIN mới:

table  type          key                          rows
p    const         uk_products                    1
s2     ref           idx_variants_product         4
sku    eq_ref        uk_product_variants                1
o      ref           idx_orders_sku                   2
os    eq_ref        uk_order_statuses               1
r      ref           idx_shipments_order_sku_active   1

Không có hint nào, nhưng optimizer tự chọn đúng đường: product_variants (4 dòng) → orders qua idx_orders_sku.

Vì Sao Thêm Một Điều Kiện "Thừa" Lại Hiệu Quả?

Về mặt logic, điều kiện o.sku_cd IN (...) là thừa, vì join với sku và p đã đảm bảo điều đó. Nhưng với optimizer, nó khác hoàn toàn:

1. Trước đây, filter product_cd nằm ở bảng products. Để lọc orders theo sản phẩm, MySQL phải đi qua 2 lần join. Điều kiện không trực tiếp chạm vào orders, nên không index nào trên orders dùng được nó.
2. Bây giờ, có một điều kiện trực tiếp trên cột o.sku_cd, mà cột này có index idx_orders_sku. MySQL chuyển IN (subquery) thành semi-join và ước lượng subquery trả về 4 dòng. Chi phí "4 lần seek theo sku_cd" rõ ràng rẻ hơn mọi phương án khác, kể cả khi optimizer vẫn tin sai về rev_no.

Đây là nguyên tắc quan trọng: optimizer chỉ chọn được những đường đi mà query cho phép nó thấy. Khi điều kiện lọc mạnh nhất nằm ở bảng xa, hãy "kéo" nó về gần bảng lớn bằng một IN/EXISTS trên cột có index.

Một lợi ích nữa: khi không có filter product_cd, ta chỉ đơn giản là không thêm điều kiện này, query trở về như cũ. Không cần hai nhánh code với hint khác nhau.

Trong code ứng dụng (ví dụ Laravel Query Builder):

if ($productCd !== '') {
    $query->where('p.product_cd', $productCd)
        // Cho optimizer đường đi selective: SKU của sản phẩm → orders qua idx_orders_sku.
        // Thiếu dòng này, MariaDB đi qua (rev_no, deleted_at, status_cd) và quét ~1M dòng.
        ->whereIn('o.sku_cd', fn ($q) => $q->select('sku_cd')
            ->from('product_variants')
            ->where('product_cd', $productCd)
            ->where('rev_no', 0));
}

Hãy luôn để lại comment giải thích vì sao có điều kiện "thừa" này. Nếu không, người sau sẽ xóa nó đi vì nghĩ là dư.

Bước 6: Covering Index Và Deferred Join Cho Phân Trang

Query trong case trên chỉ SELECT o.order_id chứ không lấy toàn bộ cột. Đó là kỹ thuật deferred join, đi cùng với covering index.

Covering Index

Một index là covering cho một query khi mọi cột query cần (SELECT, WHERE, ORDER BY, JOIN) đều nằm trong index. Khi đó MySQL không cần quay lại bảng chính (clustered index) để đọc dòng. EXPLAIN sẽ hiện Using index trong cột Extra.

Trong InnoDB, mọi secondary index tự động chứa primary key. Vì vậy index (rev_no, order_no, sku_cd) thực chất là (rev_no, order_no, sku_cd, order_id), và query sau hoàn toàn covering:

SELECT order_id FROM orders
WHERE rev_no = 0
ORDER BY order_no, sku_cd
LIMIT 20 OFFSET 10000;
-- Extra: Using where; Using index   ← không đụng vào bảng chính

Deferred Join

Phân trang kiểu LIMIT 20 OFFSET 10000 với SELECT * rất tốn, vì MySQL phải đọc đầy đủ 10,020 dòng (kể cả các cột lớn) rồi bỏ đi 10,000 dòng. Deferred join tách làm hai phase:

-- Phase 1: chỉ lấy id, chạy hoàn toàn trên covering index
SELECT order_id FROM orders
WHERE rev_no = 0
ORDER BY order_no, sku_cd
LIMIT 20 OFFSET 10000;

-- Phase 2: lấy đầy đủ dữ liệu + join + GROUP BY cho đúng 20 id đó
SELECT o.*, SUM(r.qty) ...
FROM orders o
JOIN shipments r ON ...
WHERE o.order_id IN (/* 20 id từ phase 1 */)
GROUP BY o.order_id;

Hoặc gộp lại bằng subquery:

SELECT o.*
FROM orders o
JOIN (
  SELECT order_id FROM orders
  WHERE rev_no = 0
  ORDER BY order_no, sku_cd
  LIMIT 20 OFFSET 10000
) page USING (order_id)
ORDER BY o.order_no, o.sku_cd;

Trong hệ thống của case study, deferred join đưa màn hình danh sách (không filter, sort mặc định) từ 66 giây xuống 4ms cho phase 1. Nhưng như bạn đã thấy, khi có thêm filter, kế hoạch của phase 1 lại hỏng vì data skew. Deferred join không miễn nhiễm với lỗi join order.

Cái Giá Của COUNT(*)

Phân trang kiểu "Trang 1/523" cần tổng số dòng, và COUNT(*) không dừng ở LIMIT. Nó phải duyệt toàn bộ tập kết quả. Trong case study, phase 1 mất 4ms nhưng COUNT(*) vẫn mất ~1.8 giây khi không có filter. Lúc lỗi join order xảy ra, nó lên 13.9 giây.

Một số cách giảm chi phí:

Lưu ý: mọi tối ưu join order cho query lấy id cũng phải áp dụng cho câu COUNT(*). Trong case study, cả hai câu dùng chung một hàm build filter, nên thêm một điều kiện ở đó là sửa được cả hai, kể cả luồng export CSV.

Bước 7: Các Kỹ Thuật Index Nâng Cao Khác

Index Condition Pushdown (ICP)

Khi index (a, b, c) và điều kiện là a = 1 AND c LIKE '%x%', cột c không dùng để seek được. Nhưng với ICP (bật mặc định từ MySQL 5.6), điều kiện c được kiểm tra ngay trên index entry, trước khi đọc dòng đầy đủ. EXPLAIN hiện Using index condition. Đây là lý do thêm cột "không seek được" vào cuối index đôi khi vẫn có lợi.

Invisible Index (MySQL 8.0) / Ignored Index (MariaDB 10.6)

Trước khi xóa một index nghi là thừa, hãy ẩn nó trước để kiểm tra:

-- MySQL 8.0
ALTER TABLE orders ALTER INDEX idx_orders_active_no INVISIBLE;
-- Theo dõi slow query log vài ngày, nếu ổn:
ALTER TABLE orders DROP INDEX idx_orders_active_no;
-- Nếu có vấn đề, bật lại ngay (không cần rebuild):
ALTER TABLE orders ALTER INDEX idx_orders_active_no VISIBLE;

-- MariaDB 10.6+
ALTER TABLE orders ALTER INDEX idx_orders_active_no IGNORED;

Kỹ thuật này cũng giúp thử nghiệm khi muốn biết "nếu không có index X thì optimizer chọn đường nào" mà không cần drop rồi build lại index trên bảng lớn.

Functional Index (MySQL 8.0.13+)

Điều kiện WHERE DATE(created_at) = '2026-10-14' không dùng được index trên created_at. Có hai cách: viết lại thành range (created_at >= '2026-10-14' AND created_at < '2026-10-15'), hoặc index chính biểu thức:

CREATE INDEX idx_orders_created_date ON orders ((DATE(created_at)));

MariaDB chưa hỗ trợ cú pháp này. Thay vào đó, dùng virtual column rồi index cột đó.

Descending Index (MySQL 8.0)

Khi query ORDER BY a ASC, b DESC, index (a, b) thông thường không phục vụ được việc sắp xếp. MySQL 8.0 hỗ trợ:

CREATE INDEX idx_mixed ON t (a ASC, b DESC);

Partial Pattern Cho Dữ Liệu Bị Lệch

Quay lại vấn đề rev_no: MySQL không có partial index (WHERE rev_no = 0) như PostgreSQL. Có ba cách giải quyết tận gốc:

1. Tách bảng lịch sử (orders chỉ chứa bản hiện hành, orders_history chứa lịch sử). Cột rev_no biến mất khỏi bảng chính, vấn đề skew cũng hết.
2. Thêm cột cờ is_active (0/1) và dùng nó thay rev_no trong index. Optimizer vẫn không biết tỷ lệ 60/40, nhưng chỉ có 2 giá trị nên ước lượng "mỗi giá trị ~50% bảng" gần với thực tế hơn nhiều so với "mỗi giá trị 2 dòng".
3. Đưa cột selective lên trước trong index dành cho filter đó, ví dụ (sku_cd, rev_no). Khi đó rev_no chỉ còn tác dụng lọc thêm trong phạm vi nhỏ.

Xử Lý Lỗi Thường Gặp

EXPLAIN báo dùng index nhưng query vẫn chậm

Kiểm tra theo thứ tự:

1. key_len: index có được dùng đủ sâu không?
2. rows so với thực tế (dùng EXPLAIN ANALYZE): có lệch nhiều bậc không?
3. Thứ tự bảng trong EXPLAIN: bảng selective nhất có đứng đầu không?
4. Extra: có Using temporary; Using filesort trên tập lớn không?

Thêm index mới nhưng optimizer không dùng

• Optimizer cho rằng index đó không rẻ hơn (đúng hoặc sai). Thử FORCE INDEX để đo. Nếu nhanh hơn hẳn, vấn đề nằm ở ước lượng.
• Kiểu dữ liệu không khớp, ví dụ so sánh VARCHAR với số: WHERE sku_cd = 12345 sẽ cast từng dòng và bỏ qua index. Phải viết WHERE sku_cd = '12345'.
• Collation hai bên JOIN khác nhau, khiến index của một bên không dùng được.

Query nhanh trên dev nhưng chậm trên production

• Dữ liệu dev ít hơn, hoặc không bị skew như production. Một query "đúng" với 10k dòng có thể chọn kế hoạch sai với 1.6M dòng.
• Hãy test performance trên dữ liệu cùng quy mô và cùng phân bố với production, ví dụ bản dump đã mask dữ liệu nhạy cảm.

Query timeout nhưng vẫn chạy trên server

Khi ứng dụng timeout, query có thể vẫn đang chạy trên MySQL và chiếm tài nguyên. Xem bài Query MySQL Bị Treo để biết cách tìm và KILL, cũng như cách đặt max_execution_time.

Best Practices

1. Luôn đo, đừng đoán. Dùng EXPLAIN ANALYZE để có số dòng thực tế, đừng chỉ dựa vào EXPLAIN.
2. Đọc key_len mỗi lần đọc EXPLAIN. key đúng chưa có nghĩa là index được dùng hiệu quả.
3. Cảnh giác với cột "cờ" hoặc "trạng thái" đứng đầu index (rev_no, is_deleted, status). Chúng dễ bị skew và làm hỏng ước lượng.
4. Ưu tiên viết lại query thay vì dùng hint. Kéo điều kiện selective về gần bảng lớn bằng IN/EXISTS trên cột có index.
5. Nếu buộc phải dùng hint, để lại comment kèm số đo (trước/sau, kích thước dữ liệu, ngày đo) để người sau biết lúc nào có thể bỏ.
6. Tối ưu cả câu COUNT(*), không chỉ câu lấy dữ liệu. Thường câu count mới là câu chậm nhất.
7. Dùng deferred join cho phân trang trên bảng lớn, nhưng nhớ rằng phase 1 vẫn cần join order đúng.
8. Dùng invisible/ignored index trước khi drop, để có đường lui nhanh.
9. Test với dữ liệu cùng quy mô production. Lỗi do skew không xuất hiện trên bảng 1,000 dòng.

Tổng Kết

Quay lại case study: API mất 17 giây không phải vì thiếu index. Index cần thiết (sku_cd) đã có từ đầu. Optimizer không chọn nó vì filter nằm ở bảng xa, còn thống kê của rev_no khiến một đường đi tệ trông như rẻ nhất. Chỉ cần một dòng whereIn để optimizer thấy đường đi đúng, API xuống dưới 1 giây.

Bài học lớn nhất: index chỉ là một nửa câu chuyện. Nửa còn lại là optimizer có chọn đúng index hay không, và đôi khi bạn phải giúp nó.

Tham Khảo

• MySQL 8.0 Reference: Multiple-Column Indexes
• MySQL 8.0 Reference: EXPLAIN Output Format
• MySQL 8.0 Reference: Optimizer Statistics / Histograms
• MySQL 8.0 Reference: Invisible Indexes
• MariaDB: Engine-Independent Table Statistics
• MariaDB: Ignored Indexes
• Bài liên quan trong khóa học: Index trong MySQL, EXPLAIN Statement, Query MySQL Bị Treo