Báo cáo chạy chậm khi dữ liệu lớn: đọc EXPLAIN và đặt index

Báo cáo doanh thu theo ngày của một cửa hàng chạy 181,6 ms (median của 10 lần chạy) trên bảng 1.000.000 đơn, vì MySQL đọc cả bảng để lấy ra 596 dòng cần dùng. Một index ba cột (store_id, status, created_at) đưa thời gian xuống 0,965 ms. Cột so sánh bằng đứng trước, cột khoảng ngày đứng cuối; đảo thứ tự vẫn nhanh hơn không có index, nhưng chậm hơn 5,8 đến 6,7 lần so với thứ tự đúng.

Mọi lệnh trong bài chạy trong một database riêng, đã xoá sau khi đo. Thời gian ghi là median của 10 lần chạy thường, trừ khi ghi rõ là số trong cây EXPLAIN ANALYZE (một lần chạy riêng). Các số trong bài đo trên bảng thử, trên một laptop Apple M2 (8 lõi, 8 GB RAM, macOS 14.4.1) chạy MySQL 8.4.8 với InnoDB. Đó không phải số của hệ thống MONA hay của khách hàng, và chưa kiểm trên MariaDB. Người đọc cần biết SELECT, WHERE, GROUP BY của ngôn ngữ SQL và biết index là gì; không cần biết quản trị cơ sở dữ liệu.

Dựng bảng thử 1.000.000 đơn và chạy báo cáo khi chưa có index

Bảng orders có 50 cửa hàng, ngày tạo rải đều từ 01/01/2025 đến 31/12/2026, 70,07% đơn (700.707 trên 1.000.000) ở trạng thái paid. Các cột ngẫu nhiên sinh bằng CRC32, vì RAND(n) với hạt giống liên tiếp cho giá trị gần nhau và làm cột trạng thái trùng với ngày. Nếu bảng thật được nạp từ file Excel, cách làm sạch và chuyển dữ liệu nằm trong bài chuyển dữ liệu từ Excel sang phần mềm quản lý. Lệnh dưới tạo bảng và nạp 1.000.000 dòng bằng phép nối chéo của 6 bản bảng digits (mỗi bản 10 dòng):

CREATE DATABASE blog_lab_explain;
USE blog_lab_explain;

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  store_id SMALLINT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  status VARCHAR(12) NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  note VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE digits (d TINYINT UNSIGNED PRIMARY KEY);
INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO orders (store_id, customer_id, status, total_amount, created_at, note)
SELECT 1 + (n MOD 50),
       1 + CRC32(CONCAT('c',n)) MOD 200000,
       ELT(1 + CRC32(CONCAT('s',n)) MOD 10, 'paid','paid','paid','paid','paid','paid','paid','pending','cancelled','refunded'),
       50000 + CRC32(CONCAT('a',n)) MOD 4950000,
       TIMESTAMP('2025-01-01') + INTERVAL (CRC32(CONCAT('d',n)) MOD (730*86400)) SECOND,
       'don hang mau'
FROM (SELECT a.d + 10*b.d + 100*c.d + 1000*d.d + 10000*e.d + 100000*f.d AS n
      FROM digits a, digits b, digits c, digits d, digits e, digits f) t;
ANALYZE TABLE orders;

Bảng và dữ liệu giữ nguyên trong mọi lần đo sau. Báo cáo cần đo là doanh thu theo ngày của cửa hàng số 7, chỉ tính đơn paid, trong tháng 9/2026. Bộ lọc theo một cửa hàng cũng là cách phần mềm phân quyền người dùng cho quản lý chi nhánh chỉ xem dữ liệu của chi nhánh mình:

SELECT DATE(created_at) AS day, COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM orders
WHERE store_id = 7 AND status = 'paid'
  AND created_at >= '2026-09-01' AND created_at < '2026-10-01'
GROUP BY DATE(created_at) ORDER BY day;

Số dòng khớp từng điều kiện là số đếm thật bằng COUNT(*) trên bảng thử, không phải ước tính của EXPLAIN, và cho thấy báo cáo chỉ cần 596 trong 1.000.000 dòng. Mỗi số là kết quả của một câu COUNT(*) với đúng các điều kiện ghi ở cột trái:

Đọc EXPLAIN và EXPLAIN ANALYZE của báo cáo chưa có index

Đặt EXPLAIN trước câu SELECT: MySQL in kế hoạch thực thi mà không chạy câu lệnh. Kết quả trên bảng chưa có index, nguyên văn từ máy khách mysql:

+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                        |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 995845 |     0.11 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+

Khi type là ALL và rows gần bằng cả bảng trong khi kết quả chỉ có vài trăm dòng, truy vấn đang quét thừa. Bốn cột cần đọc trước, điền từ kế hoạch trên (định nghĩa từng cột theo tài liệu MySQL 8.4):

Tài liệu MySQL xếp các kiểu type từ tốt đến kém, gồm range, index và ALL:

Con số ước tính cần được đối chiếu với thực tế. EXPLAIN ANALYZE chạy câu lệnh thật và in thêm thời gian cùng số dòng thực của từng bước (tài liệu MySQL 8.4 về EXPLAIN ANALYZE). Kết quả cho báo cáo trên:

-> Sort: `day`  (actual time=213..213 rows=30 loops=1)
    -> Table scan on <temporary>  (actual time=213..213 rows=30 loops=1)
        -> Aggregate using temporary table  (actual time=213..213 rows=30 loops=1)
            -> Filter: ((orders.store_id = 7) and (orders.`status` = 'paid') and (orders.created_at >= TIMESTAMP'2026-09-01 00:00:00') and (orders.created_at < TIMESTAMP'2026-10-01 00:00:00'))  (cost=100618 rows=1106) (actual time=0.317..213 rows=596 loops=1)
                -> Table scan on orders  (cost=100618 rows=995845) (actual time=0.237..186 rows=1e+6 loops=1)

Dòng cuối cho thấy, trong lần chạy này, 186 ms nằm ở việc đọc 1.000.000 dòng (rows=1e+6), còn bước Filter phía trên chỉ giữ lại 596. MySQL đọc khoảng 1.678 dòng cho mỗi dòng trả về (1.000.000 chia 596). Cây này là một lần chạy riêng (213 ms ở nút ngoài cùng, lần hai 200 ms) và dùng để đọc số dòng; so sánh thời gian trong bài dùng median của 10 lần chạy, ở đây là 181,6 ms. Dùng EXPLAIN ANALYZE để so số dòng và lần chạy thường để so thời gian.

Thêm một index ba cột và đo lại

Báo cáo lọc bằng dấu = trên store_id và status, rồi lọc khoảng trên created_at. Đặt index theo đúng thứ tự đó. Trong khối lệnh sau, câu SELECT của EXPLAIN được viết gọn là <câu báo cáo ở trên>:

ALTER TABLE orders ADD INDEX ix_store_status_created (store_id, status, created_at);
ANALYZE TABLE orders;
EXPLAIN <câu báo cáo ở trên>;

+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                                                  |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | range | ix_store_status_created | ix_store_status_created | 57      | NULL |  596 |   100.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------------------------------------+

Nhánh Index range scan thay cho nhánh Table scan của kế hoạch trước. Kết quả EXPLAIN ANALYZE của báo cáo có index, chạy lần thứ hai:

-> Sort: `day`  (actual time=1.4..1.4 rows=30 loops=1)
    -> Table scan on <temporary>  (actual time=1.39..1.39 rows=30 loops=1)
        -> Aggregate using temporary table  (actual time=1.39..1.39 rows=30 loops=1)
            -> Index range scan on orders using ix_store_status_created over (store_id = 7 AND status = 'paid' AND '2026-09-01 00:00:00' <= created_at < '2026-10-01 00:00:00'), with index condition: ((orders.store_id = 7) and (orders.`status` = 'paid') and (orders.created_at >= TIMESTAMP'2026-09-01 00:00:00') and (orders.created_at < TIMESTAMP'2026-10-01 00:00:00'))  (cost=268 rows=596) (actual time=0.171..1.26 rows=596 loops=1)

Cột key_len cho biết bao nhiêu cột của index đã được dùng. Số 57 là tổng của store_id (2 byte), status (50 byte: VARCHAR(12) ở bộ ký tự utf8mb4 là 12 × 4 + 2) và created_at (5 byte), tức cả ba cột. Nếu key_len ngắn hơn tổng này, MySQL đã dừng ở một cột phía trước, như ở trường hợp bọc hàm bên dưới. So sánh trước và sau, mỗi số có nguồn là lần đo ghi ở đầu bài:

Cách đo: một phiên MySQL chạy liên tiếp 11 câu giống nhau, đọc TIMER_WAIT của 10 câu cuối từ performance_schema.events_statements_history, lấy median. Bốn thứ tự index trong bảng so sánh thứ tự cột cũng được đo bằng đúng quy trình này, với một index duy nhất trên bảng ở mỗi lần đo. Đo lại với tên index cuối, ix_store_status_created, median là 0,82 ms. Hai lần EXPLAIN ANALYZE liên tiếp ngay sau khi tạo index ra 2,12 và 1,28 ms ở nút ngoài cùng, nên hãy chạy hai lần rồi mới đọc. Dòng Using temporary; Using filesort vẫn còn vì GROUP BY DATE(created_at) gom theo kết quả của một hàm, nhưng bước đó chỉ xử lý 596 dòng và nằm trong 1,4 ms của nút ngoài cùng ở khối trên.

Thứ tự cột: cột so sánh bằng trước, cột khoảng ngày sau cùng

Quy tắc nằm ở trang tối ưu khoảng giá trị của MySQL. Với index nhiều cột, bộ tối ưu chỉ dùng thêm cột kế tiếp khi phép so sánh là =. Gặp >, <, BETWEEN hay LIKE, MySQL vẫn dùng phép so sánh đó nhưng bỏ các cột phía sau (Range Optimization, MySQL 8.4). Với báo cáo này, created_at là cột khoảng nên phải đứng cuối.

Để kiểm quy tắc, cùng bảng và cùng câu báo cáo được chạy với bốn thứ tự cột và với hai index đơn lẻ, mỗi lần chỉ giữ index của phương án đang đo. Cột cuối là số mục index hoặc dòng nằm trong khoảng MySQL phải duyệt, lấy từ các số đếm ở bảng đầu bài và từ cây EXPLAIN ANALYZE của phương án hai index:

Kết quả sửa giả thuyết ban đầu của bài: thứ tự sai không vô dụng. Khoảng ngày tháng 9 chỉ chứa 4,15% bảng (41.544 chia 1.000.000), nên hai index đặt khoảng ngày trước vẫn nhanh gấp 28 đến 33 lần không có index (181,6 chia 6,435 và chia 5,56). Nhưng chúng phải duyệt 29.162 đến 41.544 mục để giữ lại 596 dòng, nên chậm hơn thứ tự đúng 5,8 đến 6,7 lần (6,435 và 5,56 chia 0,965). Thứ tự (store_id, created_at, status), với status ở cuối, chỉ chậm hơn một chút (1,355 so với 0,965 ms) vì cửa hàng 7 và tháng 9 chỉ còn 811 mục, và status được kiểm trong 811 mục đó. Hai index đơn lẻ không thay được index nhiều cột: MySQL chỉ chọn ix_store, đọc 20.000 dòng của cửa hàng 7 rồi lọc status và ngày, nên median là 20,82 ms, gấp 21,6 lần 0,965 ms (20,82 chia 0,965).

Báo cáo xuất nhập của phần mềm quản lý kho, lọc theo một kho bằng = rồi theo khoảng ngày, có cùng dạng này nên dùng cùng thứ tự cột. Một index (store_id, status, created_at) còn phục vụ truy vấn chỉ có store_id, hoặc có store_id và status, vì các cột đó là tiền tố bên trái của index. Tài liệu MySQL về cách dùng index nêu đúng ví dụ này với index ba cột:

Hai trường hợp có index mà vẫn quét cả bảng hoặc bỏ cột cuối

Trường hợp thứ nhất: truy vấn thiếu cột đứng đầu index. Triệu chứng là type vẫn là ALL dù possible_keys liệt kê index. Cách kiểm là đọc cột key, không đọc possible_keys. Báo cáo toàn chuỗi bỏ điều kiện store_id và vẫn tính SUM(total_amount), cột không nằm trong index:

EXPLAIN SELECT DATE(created_at) AS day, COUNT(*) AS orders, SUM(total_amount) AS revenue
FROM orders WHERE status = 'paid' AND created_at >= '2026-09-01' AND created_at < '2026-10-01'
GROUP BY DATE(created_at) ORDER BY day;

+----+-------------+--------+------------+------+-------------------------+------+---------+------+--------+----------+----------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows   | filtered | Extra                                        |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | ix_store_status_created | NULL | NULL    | NULL | 995638 |     1.11 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+--------+----------+----------------------------------------------+

Trong cây EXPLAIN ANALYZE, câu này ra 341 ms và 405 ms ở hai lần chạy, cao hơn 213 ms và 200 ms của báo cáo một cửa hàng khi chưa có index, vì phải gom 29.162 dòng thay cho 596. Sửa bằng cách thêm điều kiện cột đầu vào truy vấn nếu nghiệp vụ cho phép, hoặc tạo index riêng bắt đầu bằng cột có trong WHERE (cách này chưa đo trên bảng thử). Tài liệu MySQL còn một ngoại lệ gọi là skip scan: truy vấn chỉ đọc cột có trong index, một bảng, không GROUP BY và không DISTINCT. Cùng index đó, câu SELECT COUNT(*) với status và tháng 9 cho key_len 52 và Extra là Using index for skip scan: truy vấn này được index giúp, còn truy vấn kế bên thì không.

Trường hợp thứ hai: bọc cột trong hàm. Kế hoạch vẫn dùng index, vì vậy lỗi này khó thấy khi chỉ đọc cột key. Viết DATE(created_at) >= '2026-09-01' thay cho khoảng trên cột trần làm MySQL bỏ cột created_at của index. Kiểm bằng key_len:

EXPLAIN SELECT COUNT(*) FROM orders
WHERE store_id = 7 AND status = 'paid'
  AND DATE(created_at) >= '2026-09-01' AND DATE(created_at) < '2026-10-01';

+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+-------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref         | rows  | filtered | Extra                    |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+-------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | ref  | ix_store_status_created | ix_store_status_created | 52      | const,const | 28774 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+-------+----------+--------------------------+

EXPLAIN SELECT COUNT(*) FROM orders
WHERE store_id = 7 AND status = 'paid'
  AND created_at >= '2026-09-01' AND created_at < '2026-10-01';

+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | range | ix_store_status_created | ix_store_status_created | 57      | NULL |  607 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+

key_len là 52 (store_id cộng status), không phải 57, và rows ước tính là 28.774 thay cho 607 của câu viết bằng khoảng trần (cả hai câu đều đếm ra 596 dòng). Sửa bằng cách giữ created_at >= '2026-09-01' AND created_at < '2026-10-01', đúng dạng của báo cáo ban đầu.

Index đổi lấy dung lượng và tốc độ ghi

Index ba cột chiếm 29,6 MB, trong khi dữ liệu bảng chiếm 65,6 MB (đọc từ mysql.innodb_index_stats và information_schema.tables). Thêm 100.000 dòng bằng INSERT ... SELECT, lặp 5 lần: median là 0,44 giây khi chưa có index và 1,06 giây khi có, tức chậm gấp 2,4 lần (1,06 chia 0,44). Hai index đơn lẻ chiếm 17,5 và 20,5 MB (tổng 38,0 MB) và INSERT như trên có median 0,94 giây. Bảng bị ghi liên tục phải tính khoản này trước khi thêm index thứ hai, thứ ba.

Thêm index không giúp trong hai tình huống. Thứ nhất, bảng nhỏ hoặc báo cáo cần đọc gần hết bảng: tài liệu MySQL nói đọc tuần tự có thể nhanh hơn đi qua index (How MySQL Uses Indexes). Thứ hai, bảng bị ghi liên tục: mỗi index thêm vào làm INSERT chậm đi theo số đo ở trên. Index chọn theo báo cáo hôm nay có thể không phục vụ báo cáo tháng sau, nên mỗi index mới cần đi kèm câu truy vấn đã được EXPLAIN kiểm, và nên chạy lại báo cáo theo kịch bản nghiệm thu UAT để xác nhận kết quả vẫn đủ 596 dòng như trước. Trước khi thêm index trên bảng của hệ thống đang chạy, hãy có bản sao lưu đã thử khôi phục; thời gian ALTER TABLE trên bảng thật chưa được đo.

Hỏi đáp

EXPLAIN có chạy câu truy vấn không?

EXPLAIN không chạy câu lệnh, còn EXPLAIN ANALYZE thì có. Tài liệu MySQL liệt kê EXPLAIN ANALYZE hỗ trợ SELECT, UPDATE và DELETE có nối bảng, vì vậy hai lệnh sau cần chạy trên bản sao dữ liệu. Trên bảng thử, EXPLAIN trả kết quả ngay còn EXPLAIN ANALYZE của báo cáo chưa có index ra 213 ms ở lần chạy đầu vì phải đọc hết 1.000.000 dòng. EXPLAIN thuần chỉ ước tính, số dòng thực chỉ có sau khi chạy.

Cột nào đứng đầu index nhiều cột?

Cột bị so sánh bằng = trong mọi truy vấn cần index đứng trước, cột khoảng đứng cuối. Với báo cáo ở bài, quy tắc cho ra thứ tự (store_id, status, created_at); bảng bốn thứ tự ở trên cho thấy chênh lệch 0,965 ms với 6,435 ms. Lý do là khoảng ngày chặn các cột đứng sau nó, nên index đặt khoảng ngày trước phải duyệt 41.544 mục để giữ lại 596 dòng. Nếu cột status đứng sau khoảng ngày, mức chậm chỉ tăng từ 0,965 lên 1,355 ms vì khoảng đã thu còn 811 mục.

Tạo index riêng cho từng cột có đủ không?

Không đủ cho báo cáo này. Với (store_id) và (created_at) riêng, MySQL chỉ dùng ix_store, đọc 20.000 dòng và ra median 20,82 ms, so với 0,965 ms của một index ba cột đúng thứ tự. Tài liệu MySQL nói bộ tối ưu có thể dùng Index Merge hoặc chọn index lọc chặt nhất; trên bảng thử nó chọn một index. Hai index đơn lẻ chiếm 38,0 MB, một index ba cột chiếm 29,6 MB.

Làm sao biết index đang được dùng thật?

Đọc key (tên index, NULL nghĩa là không dùng) rồi đọc key_len: 57 là dùng cả ba cột, 52 là dừng sau status như ở trường hợp bọc hàm. possible_keys chỉ cho biết index có thể chọn. Câu bọc DATE(created_at) ở trên cho key_len 52 dù index có đủ ba cột.

Báo cáo cần đọc gần hết bảng thì index có giúp không?

Gần như không. Tài liệu MySQL nói với báo cáo xử lý hầu hết dòng, đọc tuần tự nhanh hơn đi qua index. Báo cáo toàn chuỗi ở trên không lọc cửa hàng nên vẫn quét cả bảng (type là ALL) dù bảng có index, và EXPLAIN ANALYZE ra 341 và 405 ms.

Doanh nghiệp có báo cáo chạy chậm trong phần mềm đang dùng có thể trao đổi với MONA về dịch vụ viết phần mềm theo yêu cầu, hotline 1900 636 648.