Khái niệm và vai trò của Index

Index là một cấu trúc dữ liệu giúp tối ưu hóa tốc độ tra cứu bản ghi bằng cách sắp xếp các giá trị của một hoặc nhiều cột, thường dựa trên cây B-Tree. Trong động cơ lưu trữ InnoDB, luôn tồn tại một clustered index ánh xạ trực tiếp đến dữ liệu thực tế thông qua khóa chính. Ngoài ra, người dùng có thể xác định các secondary index (chỉ mục phụ) trên các cột khác. Tùy thuộc vào phạm vi áp dụng, secondary index được phân loại thành chỉ mục đơn, chỉ mục hợp (composite) hoặc chỉ mục một phần.

Khi một bảng không được đánh index, trình tối ưu hóa MySQL buộc phải thực hiện full table scan, bắt đầu từ dòng đầu tiên và đọc tuần tự toàn bộ dữ liệu. Độ trễ sẽ tăng tuyến tính theo kích thước bảng. Ngược lại, nếu cột tìm kiếm đã có index, cơ sở dữ liệu có thể định vị trực tiếp địa chỉ khối dữ liệu liên quan nhờ cấu trúc cây phân nhánh, giảm thiểu đáng kể hoạt động I/O đĩa.

Phân loại và cú pháp khai báo

Các chỉ mục trong MySQL được phân loại dựa trên nhiều tiêu chí kỹ thuật:

• Cấu trúc lưu trữ: B-Tree (mặc định), Hash (chỉ cho Memory/Table tạm thời)
• Mức độ ràng buộc: Index thường, Unique, Primary Key, Composite, Full-Text, Spatial
• Mối quan hệ với dữ liệu: Clustered (lưu trữ dữ liệu ngay tại node lá) và Non-clustered (lưu trữ con trỏ/tham chiếu đến dữ liệu)

Cú pháp tổng quát để xác định index:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX ten_chi_muc
ON ten_bang (cot_chinh [(do_dai)] [ASC | DESC], ...)
USING {BTREE | HASH}
[ALGORITHM = INPLACE] [LOCK = NONE];

1. Chỉ mục Khóa chính (Primary Key) và Unique

Primary Key là dạng unique index đặc biệt, bắt buộc không chứa giá trị NULL và mỗi bảng chỉ được phép xác định duy nhất một lần. Unique index đảm bảo tính duy nhất của các giá trị trong cột được đánh index. Nếu cố gắng chèn dữ liệu trùng lặp, hệ thống sẽ trả về lỗi vi phạm ràng buộc. Lưu ý rằng nhiều hàng vẫn có thể chứa giá trị NULL trong cột unique nếu không đồng thời ràng buộc NOT NULL.

Khi primary key hoặc unique index NOT NULL là kiểu số nguyên duy nhất, MySQL cho phép sử dụng _rowid để tham chiếu nhanh trong các truy vấn SELECT, giúp rút ngắn cú pháp và cải thiện hiệu năng tham chiếu nội bộ.

2. Chỉ mục Full-Text (Tra cứu toàn văn)

Chỉ được hỗ trợ trên InnoDB và MyISAM, áp dụng cho các cột kiểu CHAR, VARCHAR, TEXT. Index này không hỗ trợ chỉ mục tiền tố (prefix index). Nếu khai báo độ dài tiền tố, hệ thống sẽ bỏ qua và index toàn bộ cột.

Kiến trúc Inverted Index của InnoDB

InnoDB Full-Text sử dụng mô hình Inverted Index. Thay vì lưu bản ghi theo dòng, nó lưu danh sách từ vựng kèm theo vị trí xuất hiện của từng từ trong các tài liệu. Để hỗ trợ tìm kiếm gần (proximity search), thông tin byte offset của mỗi từ cũng được ghi nhận chi tiết.

Khi tạo full-text index, InnoDB tự động sinh ra một tập hợp các bảng ẩn để quản lý từ điển, danh sách từ đã xóa, bộ đệm và cấu hình:

CREATE TABLE van_ban_sample (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    noi_dung TEXT,
    tac_gia VARCHAR(200),
    tieu_de VARCHAR(200),
    FULLTEXT(fts_idx) (noi_dung)
) ENGINE=InnoDB;

SELECT table_id, name, space
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE name LIKE '%fts_%';

Kết quả truy vấn sẽ hiển thị các bảng ẩn theo mẫu fts_<table_id>_index_<n>, fts_..._being_deleted, fts_..._config, v.v., cho thấy cơ chế quản lý từ điển tách biệt khỏi dữ liệu nghiệp vụ.

3. Chỉ mục Multi-Value (Nhiều giá trị)

Bắt đầu từ MySQL 8.0.17, InnoDB hỗ trợ index trên các mảng JSON. Khác với index thông thường (tỷ lệ 1:1 giữa bản ghi dữ liệu và bản ghi index), multi-value index cho phép một bản ghi dữ liệu tương ứng với nhiều bản ghi index (N:1). Điều này cực kỳ hữu ích khi cần tra cứu sâu vào các mảng giá trị bên trong tài liệu JSON.

Khai báo Multi-Value Index

Sử dụng hàm CAST(... AS ... ARRAY) để ép kiểu mảng JSON thành mảng SQL, từ đó tạo virtual generated column và xây dựng function index:

CREATE TABLE khach_hang (
    ma_kh BIGINT AUTO_INCREMENT PRIMARY KEY,
    ngay_cap_nhat DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    thong_tin JSON,
    INDEX idx_ma_bang ( (CAST(thong_tin->'$.ma_bang' AS UNSIGNED ARRAY)) )
);

Có thể kết hợp với các cột thông thường trong composite index, nhưng mỗi composite chỉ được phép chứa tối đa một phần multi-value:

ALTER TABLE khach_hang ADD INDEX idx_comp(
    ma_kh, 
    ngay_cap_nhat, 
    (CAST(thong_tin->'$.ma_bang' AS UNSIGNED ARRAY))
);

Sử dụng trong Truy vấn

Trình tối ưu hóa sẽ tự động kích hoạt multi-value index khi mệnh đề WHERE chứa các hàm: MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS().

-- Kiểm tra trước khi có index: quét toàn bộ
EXPLAIN SELECT * FROM khach_hang WHERE 94507 MEMBER OF(thong_tin->'$.ma_bang');
-- type: ALL, key: NULL

-- Thêm index multi-value
ALTER TABLE khach_hang ADD INDEX idx_ma_bang_mv ( (CAST(thong_tin->'$.ma_bang' AS UNSIGNED ARRAY)) );

-- Kiểm tra lại: đã sử dụng index
EXPLAIN SELECT * FROM khach_hang WHERE 94507 MEMBER OF(thong_tin->'$.ma_bang');
-- type: ref, key: idx_ma_bang_mv

Nếu cố gắng tạo UNIQUE index trên multi-value khi đã tồn tại giá trị trùng lặp trong mảng, lệnh sẽ thất bại với lỗi Duplicate entry.

Đặc điểm & Hạn chế

• Xử lý DML tương tự index thường, nhưng một thao tác INSERT/UPDATE có thể sinh nhiều bản ghi index tương ứng với số lượng phần tử trong mảng.
• Mảng rỗng [] không tạo entry index, dẫn đến không thể truy xuất bản ghi đó qua index scan.
• Giá trị NULL trong mảng JSON bị coi là lỗi cú pháp JSON khi đánh index.
• Không hỗ trợ ASC/DESC, không thể dùng làm Primary Key, không hỗ trợ covering index hay range scan.
• Giới hạn số lượng giá trị mỗi bản ghi phụ thuộc vào dung lượng undo log page (~65KB). Thực tế cho phép khoảng 1600 giá trị số nguyên mỗi bản ghi.
• Chỉ hỗ trợ hai bộ ký tự: binary (với default collation) và utf8mb4 (với utf8mb4_0900_as_cs).
• Không hỗ trợ USING HASH, bắt buộc dùng ALGORITHM=COPY khi tạo online.

4. Chỉ mục Spatial (Không gian)

Hỗ trợ các kiểu dữ liệu địa lý như POINT, GEOMETRY trên MyISAM và InnoDB. Khi áp dụng SPATIAL INDEX, các quy tắc sau bắt buộc:

• Chỉ dùng cho InnoDB/MyISAM. Các engine khác sẽ báo lỗi nếu cố gắng khai báo.
• Bắt buộc khai báo NOT NULL cho cột không gian.
• Không hỗ trợ tiền tố cột, toàn bộ chiều rộng cột được index.
• Không thể kết hợp với PRIMARY KEY hay UNIQUE.

Nếu chỉ dùng INDEX thường trên cột spatial, hệ thống sẽ sử dụng B-Tree thay vì cấu trúc không gian chuyên biệt (R-Tree), và cho phép giá trị NULL trên các bảng hỗ trợ (trừ ARCHIVE).

5. Chỉ mục Xuống dần (Descending Index)

MySQL hiện hỗ trợ lưu trữ giá trị key theo chiều giảm dần (DESC) thay vì chỉ đảo ngược quét index. Điều này cho phép trình tối ưu hóa thực hiện forward scan hiệu quả khi mệnh đề ORDER BY kết hợp cả chiều tăng và giảm trên các cột khác nhau, loại bỏ nhu cầu sử dụng file sort tốn kém.

Ví dụ về khai báo:

CREATE TABLE bang_so (
    cot_a INT, cot_b INT,
    INDEX idx_asc_asc (cot_a ASC, cot_b ASC),
    INDEX idx_asc_desc (cot_a ASC, cot_b DESC),
    INDEX idx_desc_asc (cot_a DESC, cot_b ASC),
    INDEX idx_desc_desc (cot_a DESC, cot_b DESC)
);

Khi thực thi ORDER BY cot_a DESC, cot_b DESC, optimizer sẽ chọn idx_desc_desc để quét tuyến tính. Trong EXPLAIN FORMAT=TREE, optimizer sẽ ghi chú (reverse) khi buộc phải quét ngược chiều index:

EXPLAIN FORMAT=TREE SELECT * FROM bang_so ORDER BY cot_a ASC;
-- Kết quả: Index scan on bang_so using idx_desc_asc (reverse)

Lưu ý quan trọng:

• Chỉ InnoDB hỗ trợ đầy đủ tính năng này.
• Không áp dụng cho Hash, Full-Text hay Spatial index.
• Các index chứa phần DESC không tham gia vào optimization MIN()/MAX() khi không có GROUP BY.
• Nếu primary key hoặc secondary index chứa cột DESC, InnoDB sẽ vô hiệu hóa change buffering cho index đó.