Site logo
Loading...
Tác giả
  • avatar Nguyễn Đức Xinh
    Name
    Nguyễn Đức Xinh
    Twitter
Ngày xuất bản
Ngày xuất bản

Tối Ưu Query MySQL Với Index: Từ Cơ Bản Đến Nâng Cao

Giới Thiệu

Bài trước (Index trong MySQL: Hướng dẫn toàn diện từ cơ bản đến nâng cao) đã giải thích index là gì, có những loại index nào. Bài này đi xa hơn một bước: có index rồi mà query vẫn chậm thì phải làm sao?

Đây là tình huống rất phổ biến trong thực tế: bảng có index, câu query "nhìn qua thì ổn", nhưng khi dữ liệu lớn dần (vài chục nghìn đến vài triệu dòng), một API vốn chạy dưới 1 giây bỗng chạy 10-15 giây, thậm chí timeout. Phần lớn nguyên nhân không phải "thiếu index" theo nghĩa chung chung, mà là:

  • Index có nhưng sai cột, sai thứ tự cột.
  • Query dùng đúng index nhưng MySQL chỉ dùng được một phần của nó (leftmost prefix bị cắt ngang).
  • Có những đoạn SQL tưởng vô hại (subquery lồng trong SELECT, hàm áp lên cột) âm thầm vô hiệu hoá index.

Bài viết này sẽ đi theo đúng quy trình một kỹ sư backend thực sự làm khi nhận ticket "API này chậm quá": đo trước, đọc EXPLAIN, tìm đúng điểm nghẽn, sửa, đo lại để chứng minh. Toàn bộ case study ở cuối bài là một tình huống có thật, đã được đo bằng số liệu thực, chỉ đổi tên bảng/cột sang tên chuẩn phổ biến để dễ áp dụng vào dự án của bạn.

Tại Sao Query Chậm Dù Đã Có Index?

Một sai lầm phổ biến của người mới là nghĩ "có index là nhanh". Thực tế, MySQL Optimizer chỉ dùng index khi:

  1. Cột lọc/join/sắp xếp nằm trong index, đúng thứ tự cần thiết.
  2. Kiểu dữ liệu khớp nhau giữa cột và giá trị so sánh.
  3. Điều kiện không bị bọc trong hàm/biểu thức làm cột (CAST(), DATE_FORMAT(), LOWER()...).
  4. Optimizer ước tính dùng index sẽ rẻ hơn quét toàn bảng (đôi khi optimizer đoán sai, gọi là "cardinality estimation error").

Chỉ cần vi phạm một trong 4 điều trên, index coi như "vô hình" — MySQL vẫn có thể chạy ra kết quả đúng, nhưng phải quét nhiều hơn cần thiết. Đây là lý do một query "trông có index" vẫn chậm.

Ôn Lại Nhanh: Composite Index Và Quy Tắc Leftmost Prefix

Nếu bạn chưa quen với index cơ bản, hãy đọc bài Index trong MySQL trước. Ở đây ta chỉ nhắc lại phần quan trọng nhất cho bài toán tối ưu: composite index (index nhiều cột).

Một composite index được lưu dưới dạng cây B-Tree, sắp xếp theo thứ tự cột khai báo, từ trái sang phải:

CREATE INDEX idx_orders_customer_status
  ON orders (customer_id, status, created_at);

Index này được sắp xếp giống một cuốn danh bạ: trước tiên theo customer_id, trong mỗi customer_id lại sắp theo status, trong mỗi status lại sắp theo created_at. Đây gọi là quy tắc leftmost prefix — MySQL chỉ dùng được index này một cách hiệu quả nếu điều kiện WHERE/JOIN chạm vào cột từ trái qua phải, liên tục:

Điều kiện WHERE Dùng được cột nào của index Hiệu quả
customer_id = ? customer_id Tốt
customer_id = ? AND status = ? customer_id, status Tốt hơn
customer_id = ? AND status = ? AND created_at > ? Cả 3 cột Tối ưu nhất
status = ? (bỏ qua customer_id) Không dùng được index này Full scan hoặc dùng index khác
customer_id = ? AND created_at > ? (bỏ qua status) Chỉ customer_id created_at không lọc được qua index

Dòng cuối cùng chính là lỗi hay gặp nhất: bỏ qua một cột ở giữa làm những cột phía sau trở nên vô dụng đối với index, dù chúng có trong index. Đây cũng chính là gốc rễ của case study ở phần sau bài viết.

Công Cụ Bắt Buộc: EXPLAIN Và EXPLAIN ANALYZE

Đừng bao giờ đoán index có được dùng hay không — hãy hỏi thẳng MySQL bằng EXPLAIN.

Đọc Kết Quả EXPLAIN Cơ Bản

EXPLAIN
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

Các cột quan trọng nhất cần nhìn:

Cột Ý nghĩa
type Kiểu truy cập. Từ tốt đến xấu: const > eq_ref > ref > range > index > ALL (full scan)
possible_keys Những index MySQL có thể dùng
key Index MySQL thực sự chọn — nếu NULL nghĩa là không dùng index nào
key_len Số byte thực sự dùng từ index — dùng để biết index bị cắt ở đâu (leftmost prefix)
rows Số dòng ước tính MySQL phải quét
Extra Cảnh báo quan trọng: Using filesort, Using temporary, Using where, Using index

Ba giá trị trong Extra cần đặc biệt cảnh giác:

  • Using filesort: MySQL phải sắp xếp kết quả bằng tay (không lấy được thứ tự có sẵn từ index) — tốn CPU/memory, đặc biệt nặng khi dữ liệu lớn.
  • Using temporary: MySQL phải tạo bảng tạm trong quá trình xử lý (GROUP BY/ORDER BY phức tạp, DISTINCT...) — chi phí I/O cao.
  • Using where đi kèm type: ALL: dấu hiệu rõ ràng của full table scan có lọc thêm — gần như chắc chắn cần thêm index.

EXPLAIN ANALYZE — Đo Thời Gian Thật, Không Chỉ Ước Tính

EXPLAIN thông thường chỉ cho ước tính (dựa trên thống kê bảng, có thể sai lệch nhiều nếu thống kê cũ). Muốn biết chính xác từng bước tốn bao nhiêu thời gian thật, hãy dùng:

-- MySQL 8.0.18+
EXPLAIN ANALYZE
SELECT ...

-- MariaDB 10.1+
ANALYZE FORMAT=JSON
SELECT ...

Kết quả trả về số liệu thực đo được khi query thực sự chạy: số lần lặp (r_loops — MariaDB) hoặc "actual loops" (MySQL), số dòng đọc thực tế (r_rows/"actual rows"), và thời gian thực tế từng bước (r_table_time_ms/"actual time"). Đây chính là công cụ quyết định trong case study bên dưới — không có nó, bạn chỉ đang đoán.

Case Study Thực Tế: Giảm Query Từ 11 Giây Xuống 2 Giây

Bài Toán

Hệ thống có màn hình "Xuất danh sách sản phẩm ra CSV" (bulk export), không phân trang — phải trả về toàn bộ sản phẩm đang active, kèm một cột cờ "Cảnh báo tồn kho thấp": cờ này bật lên nếu bất kỳ SKU nào (biến thể theo màu/size) của sản phẩm đó đang ở trạng thái tồn kho thấp.

Schema đơn giản hoá (đặt tên chuẩn, không dùng tên bảng nội bộ):

-- Sản phẩm gốc
CREATE TABLE products (
  id            BIGINT PRIMARY KEY AUTO_INCREMENT,
  sku_prefix    VARCHAR(20) NOT NULL,
  name          VARCHAR(255) NOT NULL,
  status        VARCHAR(20) NOT NULL,   -- 'active' | 'inactive'
  deleted_at    DATETIME NULL,
  INDEX idx_products_active (status, deleted_at)
);

-- Từng biến thể màu của sản phẩm — bảng lớn, join fan-out
CREATE TABLE product_variants (
  id            BIGINT PRIMARY KEY AUTO_INCREMENT,
  product_id    BIGINT NOT NULL,
  color_code    VARCHAR(20) NOT NULL,
  status        VARCHAR(20) NOT NULL,
  UNIQUE KEY uk_product_variants (product_id, color_code, status)
);

-- Từng SKU cụ thể (màu x size) — bảng lớn nhất
CREATE TABLE product_skus (
  id              BIGINT PRIMARY KEY AUTO_INCREMENT,
  product_id      BIGINT NOT NULL,
  variant_id      BIGINT NOT NULL,
  status          VARCHAR(20) NOT NULL,
  deleted_at      DATETIME NULL,
  low_stock_flag  TINYINT NOT NULL DEFAULT 0,
  INDEX idx_product_skus_product (product_id)   -- ← chỉ có 1 cột, đây là vấn đề
);

Query xuất dữ liệu (rút gọn, bỏ các cột không liên quan):

SELECT
  p.id,
  p.name,
  IF(EXISTS(
    SELECT 1 FROM product_skus s
    WHERE s.product_id = p.id
      AND s.status = 'active'
      AND s.deleted_at IS NULL
      AND s.low_stock_flag = 1
  ), 'LOW_STOCK', NULL) AS stock_alert
FROM products p
LEFT JOIN product_variants v ON v.product_id = p.id AND v.status = 'active'
WHERE p.status = 'active' AND p.deleted_at IS NULL
GROUP BY p.id
ORDER BY p.id;

Với ~20.000 sản phẩm active, mỗi sản phẩm trung bình có ~35 SKU (nhiều size, nhiều màu, cộng thêm các bản ghi lịch sử/đã xoá mềm), API này mất khoảng 11 giây — với một bulk export không phân trang, đây là con số không thể chấp nhận.

Bước 1: Đo Trước Khi Sửa

Việc đầu tiên không phải là đoán và thêm index bừa. Chạy EXPLAIN ANALYZE (hoặc ANALYZE FORMAT=JSON trên MariaDB) để có số liệu thật:

ANALYZE FORMAT=JSON
SELECT ...  -- (câu query ở trên)

Bước 2: Đọc Kết Quả Để Tìm Điểm Nghẽn

Kết quả (rút gọn phần quan trọng) cho thấy:

{
  "query_block": {
    "r_total_time_ms": 11231.7,
    "subqueries": [
      {
        "query_block": {
          "r_loops": 19595,
          "r_total_time_ms": 11184.9,
          "table": {
            "table_name": "s",
            "key": "idx_product_skus_product",
            "used_key_parts": ["product_id"],
            "r_rows": 35.7,
            "r_table_time_ms": 10934.9,
            "r_filtered": 0
          }
        }
      }
    ]
  }
}

Ba con số cần chú ý:

  • r_loops: 19595 — subquery EXISTS này chạy lại một lần cho mỗi sản phẩm (đây gọi là dependent subquery / correlated subquery). Bản thân điều này không sai — vấn đề là mỗi lần chạy nó có rẻ hay không.
  • used_key_parts: ["product_id"] — index idx_product_skus_product chỉ có 1 cột. MySQL dùng được cột này để tìm nhanh các dòng cùng product_id, nhưng ba điều kiện còn lại (status = 'active', deleted_at IS NULL, low_stock_flag = 1) phải lọc bằng tay sau khi đọc dòng — không qua được index.
  • r_filtered: 0 (làm tròn) — trong ~35.7 dòng đọc được mỗi lần, gần như toàn bộ bị loại bỏ ngay sau đó. Tức là MySQL đang đọc dư ~35 dòng chỉ để tìm ra trung bình chưa tới 1 dòng thật sự cần.

19.595 lần × 35.7 dòng ≈ 700.000 lượt đọc dư thừa — đây chính là 11 giây bị "đốt" ở đâu.

So sánh với một pattern tương tự trong cùng hệ thống (kiểm tra cờ "đang được xử lý gia công" của sản phẩm), vốn đã có index đúng dạng (product_id, status, deleted_at), subquery tương ứng chỉ tốn ~0.18 giây cho cùng 19.595 lần lặp. Cùng một pattern, một bên có composite index đúng, một bên không — chênh lệch gần 60 lần.

Bước 3: Thêm Composite Index Đúng Chỗ

Fix không phải là "thêm index cho có" — mà là thêm đúng cột, đúng thứ tự, khớp với toàn bộ điều kiện trong WHERE của subquery:

CREATE INDEX idx_product_skus_active
  ON product_skus (product_id, status, deleted_at);

Lưu ý thứ tự: product_id đứng trước vì đó là điều kiện join (equality), status và deleted_at theo sau vì cũng là điều kiện equality/IS NULL — cả ba đều có thể nằm trong index range scan liên tục, không bị leftmost prefix cắt ngang. low_stock_flag = 1 được cố ý không đưa vào index: đây là điều kiện lọc cuối cùng trên một tập dữ liệu đã được thu hẹp rất nhiều bởi 3 cột trước, thêm nó vào index chỉ tăng kích thước index mà lợi ích không đáng kể.

Một lưu ý quan trọng khác: không sửa lại UNIQUE KEY uk_product_variants hay bất kỳ unique constraint nào sẵn có để "tiện thể tối ưu luôn". Composite index cho một truy vấn cụ thể nên là index bổ sung, vì thứ tự cột của unique key hiện tại rất có thể đang phục vụ tốt cho một truy vấn khác (ví dụ tra cứu "một biến thể cụ thể của một sản phẩm"). Đổi thứ tự cột của một index đang được nhiều nơi dùng là việc rủi ro cao, cần đo tác động ở tất cả các truy vấn liên quan, không chỉ truy vấn bạn đang tối ưu.

Bước 4: Đo Lại Và So Sánh

Chạy lại đúng câu ANALYZE FORMAT=JSON ở Bước 1:

{
  "r_loops": 19595,
  "r_total_time_ms": 1118.1,
  "table": {
    "key": "idx_product_skus_active",
    "used_key_parts": ["product_id", "status", "deleted_at"],
    "r_rows": 16.2,
    "r_table_time_ms": 950.9,
    "r_filtered": 0
  }
}

Thời gian subquery giảm từ 11.184,9 ms xuống 1.118,1 ms — nhanh hơn ~10 lần. Số dòng đọc mỗi lần lặp cũng giảm từ 35,7 xuống 16,2 vì deleted_at IS NULL giờ được lọc ngay ở tầng index (index_condition), không còn phải đọc rồi loại bỏ bằng tay.

Áp dụng cùng phương pháp cho join product_variants (thêm idx_product_variants_active (product_id, status)), tổng thời gian câu query giảm tiếp từ khoảng 7 giây xuống còn ~2 giây — và toàn bộ API export giảm từ 9-11 giây xuống còn 3-5 giây. Đây là mức cải thiện đạt được chỉ bằng cách thêm 2 index, không cần viết lại một dòng application code nào.

Những Lỗi Thường Gặp Khiến Index Bị Bỏ Qua

Ngoài case study trên, có một số lỗi kinh điển khác cũng khiến index "có mà như không":

1. Dùng Hàm/Biến Đổi Trên Cột Được Index

-- KHÔNG dùng được index trên created_at
WHERE DATE(created_at) = '2026-01-01'

-- Dùng được index — viết lại thành khoảng giá trị
WHERE created_at >= '2026-01-01 00:00:00'
  AND created_at <  '2026-01-02 00:00:00'

Bất kỳ hàm nào bọc quanh cột (DATE(), LOWER(), CAST(), phép cộng/trừ...) đều buộc MySQL phải tính toán lại giá trị cho từng dòng trước khi so sánh — index trên cột gốc không dùng được nữa. MySQL 8.0.13+/MariaDB có hỗ trợ functional index (index trên biểu thức) để giải quyết trường hợp bắt buộc phải lọc theo hàm, nhưng cách rẻ nhất vẫn luôn là viết lại điều kiện để cột đứng "trần" một mình.

2. So Sánh Sai Kiểu Dữ Liệu (Implicit Type Conversion)

-- sku_code là VARCHAR nhưng so sánh với số
WHERE sku_code = 12345

MySQL sẽ tự động ép kiểu — và tuỳ tình huống, việc ép kiểu này có thể diễn ra ở phía cột thay vì phía giá trị, khiến index bị vô hiệu hoá toàn bộ. Luôn so sánh đúng kiểu dữ liệu gốc của cột (WHERE sku_code = '12345').

3. LIKE Với Wildcard Ở Đầu Chuỗi

-- Không dùng được index (phải quét toàn bộ)
WHERE name LIKE '%shirt%'

-- Dùng được index (prefix match)
WHERE name LIKE 'shirt%'

Nếu bắt buộc phải tìm kiếm theo kiểu "chứa chuỗi bất kỳ vị trí nào", giải pháp đúng là Full-Text Index hoặc một search engine chuyên dụng (Elasticsearch, MeiliSearch...), không phải B-Tree index thông thường.

4. ORDER BY/GROUP BY Không Khớp Thứ Tự Index (Filesort)

Nếu index đang dùng để lọc WHERE không cùng thứ tự với cột trong ORDER BY, MySQL phải tốn thêm một bước filesort sau khi đã lấy dữ liệu — dù dữ liệu vào lúc đó có thể đã ít, filesort trên tập dữ liệu lớn (vài chục nghìn dòng trở lên) vẫn có chi phí đáng kể. Muốn tránh, cột ORDER BY nên là phần tiếp theo của composite index đang dùng để lọc, theo đúng leftmost prefix.

5. Correlated Subquery / Dependent Subquery Chạy Lại N Lần

Đây chính là gốc rễ của case study bên trên. Một EXISTS(...)/subquery lồng trong SELECT hoặc WHERE, có tham chiếu tới bảng ngoài (s.product_id = p.id), sẽ được MySQL đánh giá là dependent subquery — chạy lại một lần cho mỗi dòng của bảng ngoài. Cách nhận diện qua EXPLAIN: select_type là DEPENDENT SUBQUERY. Khi thấy dòng này, luôn hỏi: bảng bên trong subquery đã có composite index khớp đủ điều kiện WHERE của chính subquery đó chưa?

Covering Index: Tối Ưu Ở Mức Cao Hơn

Một bước tối ưu nâng cao hơn nữa là covering index — index chứa đủ mọi cột mà query cần đọc, để MySQL trả lời hoàn toàn từ index, không cần đọc lại bảng gốc (no bookmark lookup). Trong EXPLAIN, dấu hiệu là Extra: Using index (khác với Using where — không kèm chữ "index" nghĩa là vẫn phải quay lại đọc bảng).

-- Nếu chỉ cần product_id + status cho một thống kê nào đó
CREATE INDEX idx_covering ON product_skus (product_id, status, low_stock_flag);

SELECT product_id, low_stock_flag
FROM product_skus
WHERE product_id = 123 AND status = 'active';
-- EXPLAIN → Extra: Using index (không đọc bảng gốc)

Covering index đặc biệt hiệu quả cho các API list/dashboard đọc nhiều, ghi ít — nhưng đổi lại index sẽ lớn hơn (chiếm thêm disk, chậm hơn khi ghi), nên chỉ nên áp dụng cho những truy vấn thực sự nóng (hot path), không áp dụng tràn lan.

Chi Phí Của Index — Không Phải Cứ Thêm Là Tốt

Một sai lầm ngược lại cũng phổ biến không kém: thêm index tràn lan "cho chắc". Mỗi index thêm vào đồng nghĩa:

  • Mỗi lệnh INSERT/UPDATE/DELETE phải cập nhật thêm cấu trúc B-Tree đó → ghi chậm hơn.
  • Tốn thêm dung lượng đĩa (một bảng nhiều cột, nhiều index có thể khiến tổng dung lượng index lớn hơn cả dữ liệu thật).
  • Optimizer có nhiều lựa chọn hơn để cân nhắc → đôi khi chọn sai index tưởng là tối ưu.

Nguyên tắc thực dụng: chỉ thêm composite index khi có query cụ thể, đo được, lặp lại thường xuyên cần nó — không thêm "phòng khi sau này cần". Với bảng ghi nhiều (order, log, transaction), cân nhắc kỹ trước khi thêm index mới; với bảng chủ yếu đọc (master data, catalog), có thể thoải mái hơn.

Checklist Tối Ưu Query MySQL

Trước khi kết luận "cần thêm index", hãy đi qua checklist sau:

  • [ ] Đã chạy EXPLAIN ANALYZE/ANALYZE FORMAT=JSON và đọc r_total_time_ms/actual time theo từng bảng, chưa chỉ nhìn rows ước tính?
  • [ ] key trong EXPLAIN có phải index bạn nghĩ nó sẽ dùng không? used_key_parts có bị cắt ngang giữa chừng không?
  • [ ] Có select_type: DEPENDENT SUBQUERY nào đang chạy lại hàng chục nghìn lần không?
  • [ ] Extra có Using filesort/Using temporary không — nếu có, cột ORDER BY/GROUP BY đã khớp thứ tự index chưa?
  • [ ] Điều kiện WHERE có bị bọc trong hàm/ép kiểu làm mất index không?
  • [ ] Composite index mới có thứ tự cột đúng leftmost prefix cho chính truy vấn đang tối ưu, và không phá vỡ truy vấn khác đang dùng chung bảng?
  • [ ] Đã đo lại sau khi sửa để có con số so sánh trước/sau, không chỉ "cảm thấy nhanh hơn"?

Kết Luận

Tối ưu query bằng index không phải là "thêm index cho mọi cột trong WHERE" — mà là một quy trình có phương pháp: đo trước → đọc EXPLAIN ANALYZE để tìm đúng điểm nghẽn → sửa đúng chỗ (thường là composite index đúng thứ tự cột) → đo lại để chứng minh. Case study trong bài — giảm một API export từ 11 giây xuống 2 giây chỉ bằng cách thêm đúng composite index cho một correlated subquery — là minh chứng rằng phần lớn thời gian không nằm ở "thiếu index" chung chung, mà nằm ở những chi tiết rất cụ thể: thứ tự cột, điều kiện nào thực sự được index phục vụ, và điều kiện nào chỉ đang bị lọc bằng tay sau khi đọc dư hàng trăm nghìn dòng không cần thiết.

Ở bài tiếp theo, chúng ta sẽ đi sâu hơn vào EXPLAIN cho các truy vấn có nhiều JOIN phức tạp và cách MySQL Optimizer quyết định thứ tự join giữa các bảng.