Tổng Quan Về Hiệu Suất Truy Vấn

Việc tối ưu hóa hiệu suất dữ liệu trong MySQL đòi hỏi sự hiểu biết sâu sắc về cơ chế hoạt động của Optimizer và cách nó lựa chọn chỉ mục (index) dựa trên điều kiện truy vấn. Bài viết này tập trung phân tích ba nhóm lỗi phổ biến khiến chỉ mục bị bỏ qua, dẫn đến quét toàn bộ bảng (full table scan) thay vì tìm kiếm theo khóa chính.

Khi xử lý các thay đổi liên quan đến cấu trúc hoặc logic truy vấn, bắt buộc phải thực hiện kiểm thử trên môi trường Staging trước khi triển khai lên Production. Các công cụ hỗ trợ xác minh bao gồm lệnh EXPLAIN, EXPLAIN ANALYZE, cùng với việc kiểm tra thông tin thống kê từ SLOW_QUERY_LOG.

1. Thực Hiện Phép Tính Trực Tiếp Trên Cột Có Chỉ Mục

Đây là nguyên nhân hàng đầu gây mất hiệu lực chỉ mục. Khi toán tử được đặt ở vế trái của câu WHERE, database engine không thể dùng giá trị lưu trữ gốc để so sánh mà buộc phải tính toán từng dòng.

Thay vì thao tác trên cột đích, hãy đưa phần tính toán sang vế phải của phép so sánh để duy trì cấu trúc cây B-Tree của chỉ mục.

-- ❌ Sai: Perform calculation on indexed column
SELECT id, amount FROM transactions 
WHERE amount * 1.05 > 10000;

-- ✅ Đúng: Rearrange condition without function
SELECT id, amount FROM transactions 
WHERE amount > 9523.81;

-- ❌ Sai: Dùng hàm thời gian
SELECT * FROM logs 
WHERE DAY(log_timestamp) = 15;

-- ✅ Đúng: Sử dụng khoảng thời gian chuẩn
SELECT * FROM logs 
WHERE log_timestamp >= '2023-10-15 00:00:00' 
AND log_timestamp < '2023-10-16 00:00:00';

2. Hàm Số Học Hoặc Chuỗi Bao Quét Cột

Nếu một biểu thức hàm như CONCAT, FLOOR, hoặc CMD5 bao quanh tên cột trong mệnh đề WHERE, bộ nhớ đệm chỉ mục (Index Cache) sẽ vô hiệu hóa vì dữ liệu lưu trữ đã được biến đổi.

Một số giải pháp bao gồm: Chuyển đổi dữ liệu nhập liệu (Normalization) hoặc thiết kế lại chỉ mục chức năng (Functional Index - tùy phiên bản MySQL).

-- ❌ Sai: Dùng UPPER/LowerCase wrapper
SELECT * FROM accounts 
WHERE UPPER(username) = 'ADMIN';

-- ✅ Đúng: Chuẩn hóa dữ liệu lưu trữ
SELECT * FROM accounts 
WHERE username = 'admin'; -- Lưu trữ luôn dạng chữ thường

-- ❌ Sai: Duyệt chuỗi với CHAR_LENGTH
SELECT * FROM files 
WHERE CHAR_LENGTH(file_path) > 50;

-- ✅ Đúng: Trích xuất tiền tố nếu cần
SELECT * FROM files 
WHERE file_path LIKE '/usr/share/%';

3. Từ Khóa LIKE Với Ký Tự Lạ Ở Đầu

Biểu thức LIKE '%pattern' ngăn cản việc sử dụng B-Tree traversal vì hệ thống không thể xác định điểm khởi đầu cho tìm kiếm. Điều này ép buộc việc quét tuần tự.

Biên độ phù hợp nhất là ký tự bất kỳ nằm ở cuối chuỗi ('pattern%') hoặc sử dụng các cơ chế full-text search nếu nhu cầu tìm kiếm phức tạp.

-- ❌ Sai: Wildcard đứng đầu %
SELECT * FROM employees 
WHERE department_name LIKE '%Tech%';

-- ✅ Đúng: Wildcard đứng sau
SELECT * FROM employees 
WHERE department_name LIKE 'Tech%';

-- ✅ Thay thế bằng Fulltext Search
ALTER TABLE articles ADD FULLTEXT(content);
SELECT * FROM articles MATCH (content) AGAINST ('keyword');

Quy Trình Xác Minh Sản Xuất

Trước khi chấp nhận các thay đổi về DDL hoặc update cấu trúc truy vấn, cần đánh giá tác động thực tế. Quy trình chuẩn bị bao gồm:

1. Snapshots: Lưu lại trạng thái chỉ mục và thống kê trước khi chạy lệnh.
2. Analysis: Chạy EXPLAIN FORMAT=JSON để xem chi tiết hành vi truy cập dữ liệu.
3. Monitoring: Theo dõi CPU và I/O trong thời gian thực khi áp dụng truy vấn lớn.
4. Rollback Plan: Đảm bảo có kịch bản khôi phục nếu thay đổi gây suy giảm hiệu năng.

Việc chỉ kiểm tra cú pháp SQL là chưa đủ. Phải đảm bảo rằng lượng dữ liệu (data skew) và tỷ lệ chọn lọc (selectivity) thực tế khớp với ước tính của Optimizer. Nếu thống kê lạc hậu, lệnh ANALYZE TABLE nên được chạy ngay lập tức.