🪴ttuan's garden

2 min read

Functional Index (Index trên biểu thức)

Khi cột bị biến đổi (Column Transformation) mà KHÔNG THỂ viết lại query → đánh index trên KẾT QUẢ của biểu thức.

Ví dụ: tìm liên hệ sinh vào tháng 5

MONTH(birthday) = 5 không thể viết thành BETWEEN vì tháng 5 xuất hiện ở mọi năm:

-- Tạo index trên KẾT QUẢ của hàm (lưu ý cặp ngoặc kép bên ngoài)
CREATE INDEX contacts_birthmonth ON contacts ((MONTH(birthday)));
 
-- Query dùng đúng biểu thức đó → database tự nhận diện và dùng index
SELECT * FROM contacts WHERE MONTH(birthday) = 5;

Index đã tính sẵn month → [danh sách con trỏ] → nhảy thẳng tới tháng 5, nhanh gấp nhiều lần so với quét 500,000 dòng và tính MONTH() từng dòng.

Ứng dụng phổ biến: tìm kiếm không phân biệt hoa thường

CREATE INDEX users_email_lower ON users ((LOWER(email)));
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

Quy tắc nhớ

Biểu thức trong index phải khớp CHÍNH XÁC với biểu thức trong WHERE. Index dùng MONTH(birthday) mà query dùng EXTRACT(MONTH FROM birthday) - cùng kết quả nhưng database KHÔNG nhận diện được.

MariaDB và SQL Server: không hỗ trợ trực tiếp

Phải tạo Virtual Column rồi đánh index lên cột đó:

-- MariaDB:
CREATE TABLE contacts (
    ...
    birthday_month INT AS (MONTH(birthday)) VIRTUAL NOT NULL,
    INDEX contacts_birthmonth (birthday_month)
);
-- MariaDB: query phải dùng TÊN CỘT ẢO (không tự nhận diện biểu thức)
SELECT * FROM contacts WHERE birthday_month = 5;
-- SQL Server (computed column): TỰ nhận diện MONTH(birthday) và ánh xạ tới cột ảo

Liên quan

Graph View

Backlinks