Hướng dẫn sử dụng Functional Index trong MySQL 8 để tối ưu truy vấn biểu thức

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

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_at chỉ chứa giá trị thời gian thô dạng 2023-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 key nhận chỉ mục idx_users_year_created.
  • Cột type chuyển từ ALL sang ref.
  • 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:

  1. 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() hay RAND().
  2. Chi phí ghi dữ liệu (Write Overhead): Mỗi thao tác INSERT hoặc UPDATE trê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.
  3. 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 đề WHERE giống từng ký tự với biểu thức khai báo lúc tạo index.
Share: