Posted inMySQL

Tối ưu truy vấn MySQL với Covering Index: Bí quyết xóa sổ Table Lookup

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Cú sốc hiệu năng khi dữ liệu chạm mốc 10 triệu dòng

Hồi mới làm quen với MySQL, nhiều anh em thường giữ thói quen: thấy câu lệnh chạy chậm ở mệnh đề WHERE nào thì cứ tiện tay đè cột đó ra đánh index. Cách này chạy mượt mà khi dữ liệu chỉ dừng ở mức vài chục nghìn record. Nhưng đời không như là mơ.

Khi bảng users trong dự án của mình chạm ngưỡng 12 triệu dòng, hệ thống bắt đầu nấc cục. Hàng loạt câu truy vấn lấy profile người dùng bị tăng latency đột biến: từ 20ms vọt thẳng lên 1.8 giây. Đáng nói là lệnh EXPLAIN vẫn báo ăn đúng Secondary Index. Đỉnh điểm vào giờ cao điểm, CPU database chạm trần 92%, còn disk I/O thì đỏ lòm cảnh báo.

Database vẫn dùng index để lọc, vậy tại sao hệ thống vẫn nghẽn? Thủ phạm chính là thao tác Table Lookup (hay Clustered Index Lookup). Cơ chế này âm thầm bòn rút tài nguyên I/O đĩa cứng của bạn sau mỗi query.

Bản chất Table Lookup và nguyên lý Covering Index

Cơ chế B-Tree và cái giá đắt đỏ của Random I/O

Để bắt đúng bệnh, ta cần hiểu cấu trúc cây B-Tree trong InnoDB:

  • Clustered Index: Gắn liền với Primary Key. Node lá chứa toàn bộ dữ liệu thực tế của từng dòng (row data).
  • Secondary Index: Node lá của index phụ không chứa data cả dòng. Nó chỉ lưu giá trị cột được đánh index kèm khóa chính (Primary Key).

Thử mổ xẻ một câu SQL quen thuộc:

SELECT id, username, email, created_at 
FROM users 
WHERE status = 'active' 
ORDER BY created_at DESC 
LIMIT 20;

Giả sử bảng mới chỉ có index đơn lẻ trên cột status. Quá trình xử lý sẽ diễn ra như sau:

  1. MySQL lướt cây index status để lọc các bản ghi có giá trị 'active'.
  2. Tại mỗi node lá, MySQL nhặt được cặp giá trị (status, id).
  3. Câu lệnh cần thêm username, email, created_at. Vì index phụ không có các trường này, MySQL buộc phải cầm từng id nhảy sang cây Clustered Index để đào cả dòng dữ liệu lên. Hành động nhảy cóc này chính là Table Lookup.

Nếu điều kiện trả về 50.000 dòng, MySQL sẽ thực hiện đúng 50.000 lần truy xuất ngẫu nhiên (Random I/O) vào ổ đĩa. Đây là nút thắt cổ chai trực tiếp bóp nghẹt throughput của database.

Covering Index cứu nguy như thế nào?

Covering Index không phải một cú pháp mới lạ, mà là tư duy thiết kế index thông minh. Index được coi là “bao phủ” (covering) khi mọi cột trong truy vấn (từ SELECT, WHERE, JOIN cho tới ORDER BY, GROUP BY) đều nằm trọn vẹn ngay trong index đó.

Nhờ vậy, MySQL đọc thẳng dữ liệu từ bộ nhớ đệm (Buffer Pool) của Secondary Index rồi trả về cho client. Động tác Table Lookup vào Clustered Index được loại bỏ 100%. Lượng I/O đĩa giảm về gần như bằng không.

Thực hành tối ưu: Đo lường qua EXPLAIN

Dưới đây là ca tối ưu thực chiến trên môi trường staging với bảng 10 triệu bản ghi.

Bước 1: Cấu trúc bảng mẫu

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    bio TEXT,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Bước 2: Phân tích hiện trạng khi chưa có Covering Index

Chạy kiểm tra kế hoạch thực thi:

EXPLAIN SELECT id, username, email 
FROM users 
WHERE status = 'active';

Kết quả hiển thị:

+----+-------------+-------+------------+------+---------------+------------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_status    | idx_status | 82      | const | 450000 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+--------+----------+-------------+

Cột Extra đang báo NULL. Nghĩa là sau khi quét 450.000 bản ghi trên idx_status, MySQL phải lookup ngược lại bảng chính chừng đó lần để nhặt username và email. Đo thực tế bằng SHOW PROFILE, câu lệnh mất 1.45 giây.

Bước 3: Bổ sung Composite Index

Ta gom các cột cần đọc vào chung một index tổ hợp:

ALTER TABLE users ADD INDEX idx_status_user_email (status, username, email);

Mẹo nhỏ: Node lá của Secondary Index trong InnoDB luôn có sẵn khóa chính id. Vì vậy bạn không cần tốn dung lượng nhét thêm id vào khai báo index.

Bước 4: Kiểm chứng kết quả

Chạy lại EXPLAIN sau khi đánh index:

+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys         | key                   | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_status_user_email | idx_status_user_email | 82      | const | 450000 |   100.00 | Using index |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+--------+----------+-------------+

Tín hiệu ăn tiền nằm ở dòng chữ Using index trong cột Extra. MySQL đã đọc dữ liệu trực tiếp từ B-Tree index mà không sờ vào bảng chính. Thời gian phản hồi thực tế giảm ngoạn mục từ 1.45s xuống chỉ còn 2.8ms.

Ứng dụng nâng cao: Kỹ thuật Deferred Join trị bệnh phân trang sâu

Một bài toán kinh điển khác là phân trang bảng lớn với OFFSET sâu. Càng về trang sau, query càng ì ạch:

-- Mất khoảng 4.2 giây để hoàn thành
SELECT id, username, email, bio 
FROM users 
WHERE status = 'active' 
ORDER BY id 
LIMIT 500000, 20;

Cột bio chứa dữ liệu dạng TEXT, đưa vào index là phương án bất khả thi. Để xử lý, ta áp dụng kỹ thuật Deferred Join (Join trễ) kết hợp Covering Index:

SELECT u.id, u.username, u.email, u.bio
FROM users u
INNER JOIN (
    -- Subquery này 100% là Covering Index, chạy cực nhanh
    SELECT id 
    FROM users 
    WHERE status = 'active' 
    ORDER BY id 
    LIMIT 500000, 20
) AS tmp ON u.id = tmp.id;

Sự khác biệt nằm ở số lần đọc đĩa. Câu lệnh cũ lookup 500.020 lần rồi thẳng tay vứt bỏ 500.000 dòng đầu. Câu lệnh mới dùng index để lướt qua 500.000 dòng, chỉ thực hiện vỏn vẹn đúng 20 lần Table Lookup cho 20 kết quả cần render. Thời gian thực thi giảm từ 4.2 giây xuống 115ms.

Đánh đổi kỹ thuật: Khi nào KHÔNG nên dùng?

Covering Index mang lại bước nhảy vọt về tốc độ đọc. Dẫu vậy, kỹ thuật nào cũng có cái giá phải trả:

  • Tốn RAM Buffer Pool: Index càng phình to, bộ nhớ RAM khả dụng cho caching càng teo lại. Đừng biến index thành bản sao thu nhỏ của cả table chỉ để phục vụ SELECT *.
  • Gánh nặng thao tác ghi (Write Penalty): Mỗi lệnh INSERT, UPDATE, DELETE đều kéo theo việc cập nhật cây B-Tree. Trên các hệ thống có tần suất ghi cao (từ 5.000 write ops/sec trở lên), quá nhiều index sẽ làm nghẽn hàng đợi transaction.
  • Quy tắc Leftmost Prefix: Luôn đặt cột lọc chính xác (=) ở đầu, kế tiếp là các cột lọc dải (BETWEEN, >, <), và cuối cùng mới tới các cột chỉ dùng để SELECT.
Share:
Tags: