Khái niệm chỉ mục

Chỉ mục là cấu trúc dữ liệu có thứ tự giúp MySQL truy xuất dữ liệu hiệu quả. Hệ thống cơ sở dữ liệu duy trì các cấu trúc dữ liệu đặc biệt tham chiếu đến dữ liệu gốc, cho phép triển khai thuật toán tìm kiếm nâng cao.

Ưu điểm và nhược điểm

• Ưu điểm:
  • Tăng tốc độ truy vấn, giảm chi phí I/O
  • Hỗ trợ sắp xếp dữ liệu, giảm tiêu thụ CPU
• Nhược điểm:
  • Chiếm dung lượng lưu trữ
  • Giảm hiệu năng ghi (INSERT, UPDATE, DELETE)

Cấu trúc chỉ mục

B-Tree

Cấu trúc đa luồng cân bằng (max-degree = 5) khắc phục hạn chế về độ sâu của cây khi xử lý dữ liệu lớn. Các nút trung gian phân tách giá trị lên trên khi đầy.

B+Tree

Phiên bản nâng cấp của B-Tree với các đặc điểm:

• Chỉ nút lá chứa dữ liệu đầy đủ
• Nút phi lá chứa khóa chỉ mục
• Các nút lá liên kết tuần tự

MySQL tối ưu hóa B+Tree bằng cách bổ sung con trỏ liên kết giữa các nút lá liền kề.

Hash

Ánh xạ giá trị khóa thành giá trị băm sử dụng thuật toán hash:

• Chỉ hỗ trợ so sánh đẳng thức (=, IN)
• Không hỗ trợ truy vấn phạm vi hay sắp xếp
• Hiệu quả cao với truy vấn đơn

Được hỗ trợ bởi InnoDB (tự động chuyển đổi) và Memory storage engine.

Phân loại chỉ mục

Chỉ mục cụm (Clustered Index)

• Nút lá chứa toàn bộ dòng dữ liệu
• Mỗi bảng chỉ có một chỉ mục cụm
• Quy tắc ưu tiên: Khóa chính → Unique → RowID ẩn

Truy vấn hồi chuyển

Quá trình truy xuất dữ liệu qua hai bước: Tìm khóa chính trong chỉ mục phụ → Truy xuất dữ liệu đầy đủ từ chỉ mục cụm.

Cú pháp quản lý chỉ mục

-- Tạo chỉ mục thường
CREATE INDEX chi_muc_ten ON khach_hang(ho_ten);

-- Tạo chỉ mục duy nhất
CREATE UNIQUE INDEX chi_muc_dt ON khach_hang(so_dien_thoai);

-- Tạo chỉ mục kết hợp
CREATE INDEX chi_muc_nghe ON khach_hang(nghe_nghiep, tuoi, trang_thai);

-- Xem chỉ mục
SHOW INDEX FROM khach_hang;

-- Xóa chỉ mục
DROP INDEX chi_muc_email ON khach_hang;

Nguyên tắc tối ưu

Luật tiền tố trái

Với chỉ mục kết hợp (A,B,C):

• Truy vấn phải bắt đầu từ cột trái nhất
• Bỏ qua cột giữa làm mất hiệu lực phần sau
• Toán tử phạm vi (<,>) làm mất hiệu lực cột bên phải

Tình huống mất hiệu lực

1. Thao tác tính toán trên cột chỉ mục: WHERE YEAR(ngay_sinh) = 1990
2. Kiểu chuỗi không dùng nháy: WHERE so_dien_thoai = 0987654321
3. Tìm kiếm mờ đầu chuỗi: WHERE ho_ten LIKE '%Nguyen'
4. Điều kiện OR chứa cột không được lập chỉ mục
5. MySQL ước tính quét toàn bảng nhanh hơn

Gợi ý chỉ mục

-- Đề xuất chỉ mục
SELECT * FROM khach_hang USE INDEX(chi_muc_nghe) WHERE nghe_nghiep='Kỹ sư';

-- Bắt buộc dùng chỉ mục
SELECT * FROM khach_hang FORCE INDEX(chi_muc_nghe) WHERE nghe_nghiep='Kỹ sư';

-- Bỏ qua chỉ mục
SELECT * FROM khach_hang IGNORE INDEX(chi_muc_nghe) WHERE nghe_nghiep='Kỹ sư';

Chỉ mục phủ & Tiền tố

Chỉ mục phủ

Tối ưu khi truy vấn chỉ cần dữ liệu trong chỉ mục:

• using index: Không cần truy xuất bảng
• using index condition: Cần truy xuất bổ sung

Ví dụ tối ưu: SELECT id, ho_ten FROM khach_hang WHERE ho_ten='Mai'

Chỉ mục tiền tố

Tối ưu cho trường chuỗi dài:

CREATE INDEX chi_muc_email ON khach_hang(email(5));

Độ dài tối ưu xác định bằng tỷ lệ duy nhất: SELECT COUNT(DISTINCT LEFT(email,5))/COUNT(*) FROM khach_hang;

Nguyên tắc thiết kế

1. Ưu tiên bảng lớn, truy vấn thường xuyên
2. Tập trung vào cột WHERE, ORDER BY, GROUP BY
3. Chọn cột có độ phân biệt cao
4. Dùng chỉ mục tiền tố cho chuỗi dài
5. Ưu tiên chỉ mục kết hợp thay vì đơn lẻ
6. Kiểm soát số lượng chỉ mục
7. Đánh dấu NOT NULL cho cột chỉ mục