Bảng có Index nhưng truy vấn vẫn Full Table Scan: Vấn đề từ đâu?
Giả sử bạn có bảng users quy mô 5 triệu bản ghi. Cột created_at và username đều đã được đánh B-Tree Index đầy đủ. Mọi truy vấn theo id hay tìm kiếm chính xác đều phản hồi dưới 5ms.
Vấn đề phát sinh khi bạn cần lọc dữ liệu theo năm đăng ký hoặc tìm kiếm tài khoản không phân biệt hoa thường:
-- Tìm người dùng đăng ký trong năm 2023
SELECT id, full_name, email FROM users
WHERE YEAR(created_at) = 2023;
-- Tìm tài khoản không phân biệt chữ hoa, chữ thường
SELECT id, username FROM users
WHERE LOWER(username) = 'nguyenvana';
Thời gian thực thi lập tức nhảy vọt từ vài mili-giây lên 4 đến 6 giây, khiến truy vấn bị ghi nhận vào Slow Query Log. MySQL quét qua toàn bộ 5 triệu dòng. CPU máy chủ chạm ngưỡng 100% nếu có nhiều truy vấn cùng lúc.
Tại sao database lại làm ngơ trước index có sẵn? Bản chất của B-Tree Index truyền thống chỉ lưu giá trị gốc của cột. Index không lưu kết quả sau khi cột đó đã đi qua một hàm xử lý.
Bản chất kỹ thuật: Non-sargable query và Full Table Scan
Trong tối ưu hóa cơ sở dữ liệu, hai câu lệnh trên thuộc nhóm non-sargable query (Search Argument Able). Tức là bộ tối ưu hóa (Optimizer) không thể dùng cây chỉ mục để thu hẹp phạm vi tìm kiếm.
Cơ chế hoạt động rất dễ hiểu:
- Cây index của
created_atchỉ chứa giá trị thời gian thô dạng2023-05-12 08:30:00. Nó không chứa sẵn con số2023. - Khi bọc hàm
YEAR()quanh cột, MySQL buộc phải đọc từng dòng từ ổ đĩa. Sau đó, CPU thực thi hàm tính toán 5 triệu lần để lấy ra giá trị so sánh.
Bạn có thể xác thực điều này bằng lệnh EXPLAIN:
EXPLAIN SELECT id, full_name, email FROM users
WHERE YEAR(created_at) = 2023;
Cột type hiển thị ALL, còn cột key trả về NULL. Rõ ràng, chỉ mục gốc hoàn toàn bị vô hiệu hóa.
Các cách xử lý truyền thống và hạn chế
Trước khi MySQL 8 ra mắt, giới lập trình thường chọn một trong hai hướng xử lý sau:
1. Viết lại điều kiện truy vấn thành dạng khoảng (Range Query)
Với kiểu dữ liệu thời gian, cách xử lý sạch nhất là giữ nguyên cột gốc và lọc theo khoảng:
SELECT id, full_name, email FROM users
WHERE created_at >= '2023-01-01 00:00:00'
AND created_at < '2024-01-01 00:00:00';
Giải pháp này giúp tận dụng ngay index sẵn có. Tuy nhiên, nó chỉ áp dụng được cho số hoặc ngày tháng. Nó bất lực trước các tác vụ bóc tách chuỗi, tính toán biểu thức phức tạp hoặc trích xuất JSON.
2. Tạo Generated Column rồi đánh Index
Từ MySQL 5.7, giải pháp phổ biến là bổ sung một cột ảo (Generated Column):
-- Tạo cột ảo lưu năm tạo tài khoản
ALTER TABLE users
ADD COLUMN created_year INT GENERATED ALWAYS AS (YEAR(created_at)) VIRTUAL;
-- Đánh index trên cột ảo
CREATE INDEX idx_users_created_year ON users(created_year);
Tốc độ truy vấn được cải thiện rõ rệt. Đổi lại, schema của bảng trở nên cồng kềnh. Đội ngũ backend cũng phải sửa lại mã nguồn ứng dụng để truy vấn vào cột mới.
Giải pháp tối ưu: Functional Index trong MySQL 8
Từ bản 8.0.13, MySQL hỗ trợ Functional Index (Expression Index). Tính năng này cho phép bạn đánh chỉ mục trực tiếp trên kết quả của một biểu thức. Bảng dữ liệu không cần thêm cột mới.
Về bản chất, MySQL 8 tạo một cột ảo ẩn (hidden virtual column) ở tầng lưu trữ và xây B-Tree trên đó. Cấu trúc bảng công khai của bạn vẫn hoàn toàn gọn gàng.
Cú pháp khởi tạo Functional Index
Biểu thức đánh chỉ mục bắt buộc phải đặt trong hai lớp ngoặc đơn (()):
-- Đánh chỉ mục cho biểu thức năm
CREATE INDEX idx_users_year_created
ON users ((YEAR(created_at)));
-- Đánh chỉ mục cho chuỗi chữ thường
CREATE INDEX idx_users_lower_username
ON users ((LOWER(username)));
Cặp ngoặc đơn ngoài cùng dành cho cú pháp CREATE INDEX. Cặp ngoặc đơn bên trong bao bọc biểu thức cần đánh chỉ mục.
Đo lường hiệu năng thực tế
Giữ nguyên câu truy vấn ban đầu và kiểm tra lại qua EXPLAIN:
EXPLAIN SELECT id, full_name, email FROM users
WHERE YEAR(created_at) = 2023;
Các chỉ số cải thiện rõ rệt:
- Cột
keynhận chỉ mụcidx_users_year_created. - Cột
typechuyển từALLsangref. - Số bản ghi cần quét (
rows) giảm từ 5.000.000 xuống còn khoảng 120.000 dòng. - Thời gian thực thi giảm từ 4,2 giây xuống còn 3 mili-giây.
Quan trọng nhất: bạn không phải sửa một dòng mã nguồn backend nào.
Tăng tốc truy vấn với dữ liệu JSON
Functional Index đặc biệt hữu ích khi làm việc với cột JSON. Giả sử bảng có cột metadata chứa thông tin thiết bị: {"device": {"os": "iOS", "version": 16}}.
Vì JSON không hỗ trợ đánh index trực tiếp (ngoại trừ Multi-Valued Indexes khi làm việc với mảng JSON), bạn cần ép kiểu dữ liệu qua hàm CAST:
-- Đánh index cho trường os trong JSON
CREATE INDEX idx_user_os
ON users ((CAST(metadata->>'$.device.os' AS CHAR(20))));
-- Truy vấn: biểu thức ở WHERE phải trùng khớp chính xác với index
SELECT id, full_name FROM users
WHERE CAST(metadata->>'$.device.os' AS CHAR(20)) = 'iOS';
3 lưu ý kỹ thuật khi triển khai
Để tránh lỗi trong môi trường Production, bạn cần nắm rõ 3 quy tắc sau:
- Hàm phải mang tính tiền định (Deterministic): Cùng một input phải luôn cho ra một output duy nhất. MySQL sẽ từ chối tạo index nếu bạn dùng các hàm thay đổi theo thời gian thực như
NOW(),CURDATE()hayRAND(). - Chi phí ghi dữ liệu (Write Overhead): Mỗi thao tác
INSERThoặcUPDATEtrên cột nguồn sẽ kích hoạt việc tính toán lại biểu thức và cập nhật cây B-Tree. Chỉ đánh index cho những điều kiện lọc xuất hiện thường xuyên. - Biểu thức phải khớp tuyệt đối: Bộ Optimizer chỉ kích hoạt index khi biểu thức trong mệnh đề
WHEREgiống từng ký tự với biểu thức khai báo lúc tạo index.

