Đã đăng vào Thứ Ba, 12:49 SA 5 phút đọc

Amazon Aurora MySQL Bài 22: Advanced Query Optimization chuyên sâu trên Aurora MySQL

Khi ứng dụng backend của bạn tăng trưởng từ vài nghìn lên hàng triệu người dùng, những câu lệnh SQL từng chạy mượt mà ở môi trường Staging bỗng trở thành những "cơn ác mộng" gây nghẽn CPU và làm tăng vọt chi phí I/O trên Aurora MySQL.

Mặc dù Aurora có tầng lưu trữ phân tán hiệu năng cao, nó không thể cứu vãn một câu lệnh SQL viết sai logic hoặc thiếu Index tối ưu. Bài viết này sẽ mổ xẻ các kỹ thuật Advanced Query Optimization (Tối ưu hóa câu lệnh nâng cao) chuyên sâu dành riêng cho Aurora MySQL.

1. Nguyên lý hoạt động của MySQL Cost-Based Optimizer (CBO)

Trước khi tối ưu câu lệnh, bạn cần hiểu cách Aurora MySQL (dựa trên engine InnoDB) quyết định chạy một câu lệnh SQL như thế nào:

Optimizer: Công cụ phân tích câu lệnh truy vấn và tính toán ra nhiều hướng thực thi khác nhau (Execution Plans).
Cost Calculation: Dựa trên số liệu thống kê (Statistics) về kích thước bảng, số lượng dòng, và độ phân tán của Index (Cardinality), CBO sẽ chọn ra hướng đi có chi phí thấp nhất (Lowest Cost) để thực thi.
Điểm mù: Nếu thống kê bảng (Table Statistics) cũ hoặc sai lệch, CBO sẽ chọn sai execution plan, dẫn đến việc chọn Full Table Scan thay vì dùng Index, làm sập hiệu năng hệ thống.

2. Các kỹ thuật Tối ưu hóa Câu lệnh nâng cao (Advanced Optimization Techniques)

a. Tối ưu hóa các truy vấn phân trang lớn (Deep Pagination Optimization)

Trong các ứng dụng backend, khi làm tính năng phân trang cho danh sách hàng triệu dòng, lập trình viên thường dùng cú pháp kinh điển: SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;

Vấn đề: MySQL sẽ phải đọc tới 100.020 dòng từ đĩa/buffer pool rồi vứt đi 100.000 dòng đầu tiên, gây tốn kém CPU và I/O khủng khiếp.

Giải pháp (Deferred Join / Keyset Pagination): Sử dụng phương pháp lọc qua khóa chính (Cursor-based / Keyset pagination) thay vì offset lớn:

SQL

-- Thay vì dùng OFFSET lớn, hãy lọc theo ID cuối cùng của trang trước
SELECT * FROM orders 
WHERE id < 123456789 
ORDER BY id DESC 
LIMIT 20;

b. Xử lý bẫy "Leading Wildcard" trong câu lệnh LIKE

Khi tìm kiếm văn bản: SELECT * FROM users WHERE email LIKE '%@gmail.com';

Vấn đề: Dấu % ở đầu câu lệnh khiến B-Tree Index trên cột email trở nên vô tác dụng, buộc Aurora phải quét toàn bộ bảng (Full Table Scan).
Giải pháp: Nếu cần tìm kiếm từ khóa linh hoạt khối lượng lớn trên Aurora MySQL, thay vì lạm dụng LIKE '%keyword%' trên bảng quan hệ thuần túy, hãy tích hợp Aurora MySQL với Amazon OpenSearch Service hoặc sử dụng tính năng Full-Text Search đi kèm tối ưu hóa FULLTEXT INDEX.

c. Tránh lạm dụng hàm toán học trên cột Index (SARGable Queries)

Vấn đề: SELECT * FROM transactions WHERE YEAR(created_at) = 2026; Việc bọc hàm YEAR() quanh cột created_at làm mất tính năng của Index trên cột đó.
Giải pháp: Viết lại câu lệnh dưới dạng khoảng giá trị (Range query) để CBO có thể tận dụng B-Tree Index:

SQL
```
SELECT * FROM transactions 
WHERE created_at >= '2026-01-01 00:00:00' 
  AND created_at < '2027-01-01 00:00:00';
```

d. Tối ưu hóa câu lệnh JOIN trong môi trường Concurrency cao

Đảm bảo các cột tham gia điều kiện JOIN (Foreign Keys) luôn luôn phải được tạo Index.
Sắp xếp thứ tự các bảng trong mệnh kính JOIN từ bảng có tập kết quả nhỏ nhất (sau khi lọc WHERE) sang bảng lớn để giảm thiểu số lượng dòng phải ghép nối trung gian trong bộ nhớ (Join Buffer).

3. Tận dụng công cụ phân tích Execution Plan (`EXPLAIN ANALYZE`)

Đừng đoán mò nguyên nhân câu lệnh chậm. Hãy sử dụng câu lệnh EXPLAIN ANALYZE trên Aurora MySQL (hỗ trợ từ MySQL 8.0):

Khác với EXPLAIN thông thường chỉ trả về kế hoạch lý thuyết, EXPLAIN ANALYZE thực sự chạy câu lệnh, đo lường thời gian thực tế (actual time), số vòng lặp (loops), và số lượng dòng thực tế (rows) tại từng bước thực thi (iterator).
Giúp bạn phát hiện chính xác nút thắt cổ chai nằm ở bước quét bảng nào (Table scan, Filesort, hay Temporary table in disk).

4. Góc nhìn vận hành cho Backend Engineer

Cập nhật Table Statistics định kỳ: Đôi khi dữ liệu thay đổi đột ngột nhưng Optimizer chưa cập nhật thống kê, hãy chủ động chạy lệnh ANALYZE TABLE table_name; trên Aurora MySQL để cập nhật lại metadata cho CBO.
Quy chuẩn Code Review: Đưa việc kiểm tra EXPLAIN của các câu lệnh truy vấn phức tạp vào quy trình Code Review trước khi merge code lên môi trường Staging và Production.

amazon