Advanced SQL

Index là gì?

Index là gì, cấu trúc B+Tree, khi nào dùng/không dùng index, cách tạo và xóa index (ALTER TABLE), functional index, và dùng EXPLAIN để kiểm tra kế hoạch truy vấn.

Index là gì?

Một index là cấu trúc dữ liệu riêng biệt ánh xạ key value → địa chỉ vật lý của dòng trong bảng. Tương tự mục lục sách — thay vì đọc từng trang, bạn tra mục lục để nhảy thẳng đến trang cần.

Ví dụ thực tế:
Index trỏ từ CustomerID = 42 → địa chỉ vật lý #30548 trên đĩa.
RDBMS đọc trực tiếp địa chỉ #30548 thay vì quét toàn bộ bảng từ đầu đến cuối.

1. Cấu trúc B+Tree Index

MySQL (InnoDB) dùng cấu trúc B+Tree (Balanced+ Tree) cho index:

Root Node
47
↙ ↘
23
72
↙ ↘
9 | 18
28 | 35
52 | 61
80 | 91
(Leaf nodes chứa key + physical address)

Tìm giá trị 15:

Đọc root: 15 < 47 → đi trái
Đọc node: 15 < 23 → đi trái
Đọc leaf node: 9 | 18 → 15 không có trong index → không tìm thấy dòng

Độ phức tạp: O(log n) — nhanh hơn nhiều so với O(n) của full table scan.

Tại sao B+Tree cập nhật chậm?

Khi INSERT/UPDATE/DELETE dữ liệu, RDBMS phải cập nhật cả bảng lẫn index. Nếu B+Tree không cân bằng, phải thực hiện node splitting (tách node) → tốn nhiều I/O.

2. Primary Key tự động được index

MySQL tự động tạo index cho Primary Key (clustered index trong InnoDB). Foreign Key cũng nên được index vì thường xuất hiện trong JOIN:

CREATE TABLE Enrolment (
    EnrolmentID INT NOT NULL AUTO_INCREMENT,
    StudentID   INT NOT NULL,
    SubjectID   VARCHAR(10) NOT NULL,
    Mark        DECIMAL(5,2),
    PRIMARY KEY (EnrolmentID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (SubjectID) REFERENCES Subject(SubjectID)
);

InnoDB tự động tạo index cho StudentID và SubjectID vì chúng là FK.

3. Khi nào KHÔNG nên dùng index

Index KHÔNG hữu ích (thậm chí gây hại) khi:

Bảng rất nhỏ (ít dòng) → full scan nhanh hơn
Query trả về hơn 25% dòng của bảng → full scan hiệu quả hơn
Cột có selectivity thấp (ít giá trị phân biệt) — ví dụ cột Gender chỉ có 'M', 'F', 'Other'
Bảng bị UPDATE/INSERT/DELETE thường xuyên → overhead cập nhật index lớn

Selectivity — Tính chọn lọc

Selectivity = số giá trị phân biệt / tổng số dòng. Cao = tốt cho index, thấp = không nên index.

Cột	Giá trị phân biệt	Selectivity	Nên index?
CustomerID (PK)	100,000	100%	✅ Đã tự động
Email	~100,000	~100%	✅ Rất nên
LastName	~20,000	20%	✅ Nên
Gender	3	0.003%	❌ Không nên
IsActive	2	0.002%	❌ Không nên

4. Chọn cột nào để index

Chiến lược chọn cột index:

Xem xét mệnh đề WHERE của các query phổ biến — cột xuất hiện trong WHERE là ứng viên tốt
Foreign Key columns — thường xuyên dùng trong JOIN
Cột dùng trong ORDER BY — index giúp sắp xếp nhanh
Kiểm tra selectivity bằng ANALYZE TABLE

5. Tạo Index với ALTER TABLE

ALTER TABLE Customer ADD INDEX idx_lastname (LastName);

ALTER TABLE Customer ADD INDEX idx_email (Email);

ALTER TABLE Enrolment ADD INDEX idx_student_subject (StudentID, SubjectID);

Index hợp (composite index) trên nhiều cột giúp query có nhiều điều kiện WHERE.

6. Functional Index

Index trên kết quả của một hàm — ví dụ index theo tháng của ngày đặt hàng:

ALTER TABLE Orders ADD INDEX idx_order_month ((MONTH(OrderDate)));

SELECT * FROM Orders
WHERE MONTH(OrderDate) = 3;

Không có functional index, query này phải quét toàn bảng dù OrderDate đã có index thông thường.

7. EXPLAIN — Kiểm tra kế hoạch truy vấn

EXPLAIN SELECT * FROM Customer WHERE LastName = 'Smith';

Kết quả EXPLAIN cho biết:

type: cách MySQL quét (ALL = full scan, ref = dùng index, ...)
key: index đang được dùng (NULL = không dùng index)
rows: ước tính số dòng phải đọc
Extra: thông tin thêm (Using filesort, Using index, ...)

EXPLAIN SELECT s.StudentName, e.Mark
FROM Student s
INNER JOIN Enrolment e ON s.StudentID = e.StudentID
WHERE e.SubjectID = 'COS20031';

8. Xóa Index

ALTER TABLE Customer DROP INDEX idx_lastname;

ALTER TABLE Orders DROP INDEX idx_order_month;

9. ANALYZE TABLE — Cập nhật thống kê

ANALYZE TABLE Customer;
ANALYZE TABLE Enrolment;

Lệnh này cập nhật thống kê bảng để MySQL optimizer có thông tin chính xác hơn về selectivity khi chọn index.