Mỗi chỉ mục trong MySQL vừa là công cụ tăng tốc truy vấn, vừa là chi phí cần trả khi ghi dữ liệu. Khi thêm một ch mục, các thao tác INSERT, UPDATE và DELETE đều phải duy trì thêm cấu trúc B+Tree, tiêu tốn không gian đĩa và CPU. Vì vậy, thiết kế chỉ mục hiệu quả đòi hỏi sự cân bằng giữa tốc độ đọc và chi phí ghi.

1. Ưu tiên cột có độ phân biệt cao

Độ phân biệt của một cột được tính bằng công thức:

SELECT COUNT(DISTINCT ten_cot) / COUNT(*) FROM ten_bang;

Giá trị càng gần 1, các giá trị trong cột càng khác nhau, chỉ mục càng có ý nghĩa. Ngược lại, nếu cột ch có vài giá trị lặp lại nhiều, MySQL thường chọn full table scan thay vì sử dụng chỉ mục.

• Nên đánh chỉ mục: email, so_dien_thoai, ma_dinh_danh.
• Không nên đánh chỉ mục độc lập: gioi_tinh, trang_thai_kich_hoat ch có giá trị 0/1.

Nếu điều kiện truy vấn thường kết hợp cột ít phân biệt với cột khác, hãy cân nhắc đưa nó vào chỉ mục đa cột ở vị trí phù hợp.

2. Ch mục dành cho cột thực sự tham gia lọc, sắp xếp hoặc nhóm

Chỉ mục chỉ phát huy tác dụng khi cột được sử dụng trong WHERE, JOIN, ORDER BY hoặc GROUP BY. Nếu một cột chỉ xuất hiện trong danh sách chọn SELECT mà không tham gia lọc, việc đánh chỉ mục là lãng phí.

Phân tích workload trước khi đánh chỉ mục:

-- Câu truy vấn thường gặp
SELECT ma_khach_hang, ngay_tao 
FROM don_hang 
WHERE ma_khach_hang = ? 
  AND ngay_tao BETWEEN ? AND ?
ORDER BY ngay_tao DESC;

Trường hợp này nên cân nhắc chỉ mục (ma_khach_hang, ngay_tao).

Phản ví dụ: đánh chỉ mục cho cột dia_chi nhưng ứng dụng chỉ tìm kiếm theo ma_khach_hang và so_dien_thoai.

3. Tránh đặt hàm hoặc biểu thức lên cột chỉ mục

Khi một cột đã được đánh chỉ mục, việc bọc nó trong hàm sẽ khiến MySQL không thể sử dụng chỉ mục đó theo cách thông thường.

-- Không sử dụng được chỉ mục
SELECT * FROM don_hang WHERE DATE(ngay_dat_hang) = '2024-08-15';

-- Tận dụng chỉ mục bằng khoảng giá trị
SELECT * FROM don_hang 
WHERE ngay_dat_hang >= '2024-08-15 00:00:00' 
  AND ngay_dat_hang < '2024-08-16 00:00:00';

Tương tự, các biểu thức như WHERE col + 1 = 10 cũng khiến chỉ mục bị bỏ qua. Từ MySQL 8.0, functional index cho phép đánh chỉ mục trên kết quả hàm, nhưng chỉ nên dùng khi thực sự cần thiết.

4. Sử dụng chỉ mục phủ để tránh truy cập bảng chính

Khi một chỉ mục chứa đủ các cột cần thiết cho truy vấn, MySQL chỉ cần đọc chỉ mục mà không cần tra bảng gốc. Trong EXPLAIN, cột Extra sẽ hiển thị Using index.

-- Giả sử có chỉ mục idx_email_sdt(email, so_dien_thoai)
SELECT email, so_dien_thoai FROM khach_hang WHERE email = 'abc@example.com';

-- Truy vấn sau yêu cầu truy cập bảng chính vì thiếu cột trong chỉ mục
SELECT * FROM khach_hang WHERE email = 'abc@example.com';

Thiết kế chỉ mục đa cột có thể bao gồm các trường hay dùng để tìm kiếm hoặc trả về, nhưng cũng cần cân nhắc chi phí lưu trữ.

5. Áp dụng quy tắc tiền tố trái với chỉ mục đa cột

Chỉ mục (a, b, c) được tổ chức trong B+Tree theo thứ tự a trước, sau đó đến b và c. Điều này có nghĩa là điều kiện truy vấn phải bắt đầu từ cột ngoài cùng bên trái.

Các truy vấn tận dụng được chỉ mục (loai, nhom, ma):

WHERE loai = 'A';
WHERE loai = 'A' AND nhom = 'B';
WHERE loai = 'A' AND nhom = 'B' AND ma = 'C';

Các truy vấn không tận dụng đầy đủ chỉ mục:

WHERE nhom = 'B';                    -- thiếu cột loai
WHERE loai = 'A' AND ma = 'C';       -- thiếu cột nhom ở giữa, chỉ loai được dùng

Lưu ý rằng MySQL có thể tự điều chỉnh thứ tự điều kiện, ví dụ WHERE nhom = 'B' AND loai = 'A' vẫn sử dụng được chỉ mục.

Gợi ý: đặt cột có độ phân biệt cao ở vị trí trái nhất để nhanh chóng thu hẹp phạm vi quét.

6. Giới hạn số lượng chỉ mục trên một bảng

Mỗi chỉ mục thêm vào đều làm chậm các thao tác ghi. Khi số lượng chỉ mục quá lớn, chi phí duy trì cây B+Tree, cập nhật trang và ghi WAL có thể trở thành điểm nghẽn.

• Tốt nhất là giữ mỗi bảng dưới 5–7 chỉ mục, tùy vào tỷ lệ đọc/ghi.
• Loại bỏ chỉ mục trùng lặp: nếu đã có (a, b), chỉ mục (a) là thừa.
• (a, b) và (b, a) phục vụ hai mục đích khác nhau, nên không tự động coi là trùng lặp.

7. Chọn kiểu dữ liệu phù hợp khi đánh chỉ mục

Kiểu dữ liệu ảnh hưởng trực tiếp đến kích thước mỗi nút trong B+Tree và số lượng khóa mà một trang có thể cha.

• Số nguyên tốt hơn chuỗi: so sánh nhanh hơn, tiêu tốn ít byte hơn, giảm I/O.
• Khóa chính tự tăng: giá trị mới luôn được chèn vào cuối, giảm tối thiểu phân trang và phân mảnh. UUID ngẫu nhiên gây phân trang liên tục.
• Chuỗi dài: dùng chỉ mục tiền tố, chỉ lập chỉ mục một phần đầu của chuỗi.

CREATE INDEX idx_dia_chi_prefix ON khach_hang(dia_chi(20));

Để chọn độ dài tiền tố hợp lý, tính tỷ lệ phân biệt:

SELECT COUNT(DISTINCT LEFT(dia_chi, 20)) / COUNT(*) AS do_phan_biet 
FROM khach_hang;

Chọn độ dài nhỏ nhất sao cho độ phân biệt vẫn gần 1.

8. Tránh đánh chỉ mục trên cột thay đổi liên tục

Khi giá trị một cột thay đổi, MySQL phải xóa khóa cũ và chèn khóa mới vào B+Tree. Nếu cột này được cập nhật liên tục, chi phí duy trì chỉ mục sẽ rất lớn.

Các ví dụ thường gặp:

• thoi_gian_truy_cap_cuoi
• so_luot_dang_nhap
• trang_thai_xu_ly_thoi_gian_thuc

Với những cột này, chỉ đánh chỉ mục nếu thực sự cần lọc hoặc sắp xếp theo chúng, và cân nhắc chi phí ghi phát sinh.

Bảng kiểm tra nhanh

Thiết kế chỉ mục hiệu quả là sự kết hợp giữa độ phân biệt, thứ tự cột, khả năng phủ, số lượng và kiểu dữ liệu. Không có cấu hình chung cho mọi hệ thống; cần đo lường dựa trên workload thực tế bằng EXPLAIN và SHOW PROFILE hoặc performance_schema.