Mục lục trong MySQL đóng vai trò quan trọng đối với hiệu suất hoạt động của cơ sở dữ liệu, giúp tăng tốc độ truy vấn. Một ví dụ, nếu thiết kế và sử dụng chỉ mục một cách hợp lý, MySQL có thể được coi là một chiếc Lamborghini, còn không sử dụng chỉ mục thì giống như xe ba gác thủ công.
Có hai cách chính để truy cập dữ liệu từ bảng trong MySQL:
(1) Truy cập tuần tự
Truy cập tuần tự thực hiện việc quét toàn bộ bảng từ đầu đến cuối, tìm kiếm từng dòng dữ liệu cho đến khi tìm thấy dữ liệu phù hợp. Mặc dù đơn giản, nhưng khi bảng chứa nhiều dữ liệu, hiệu suất rất thấp. Ví dụ, tìm kiếm một số lượng nhỏ dữ liệu trong bảng có vài triệu bản ghi sẽ yêu cầu duyệt tất cả các dòng, tốn thời gian và ảnh hưởng đến hiệu suất xử lý.
(2) Truy cập thông qua chỉ mục
Truy cập thông qua chỉ mục thực hiện việc truy cập trực tiếp vào dòng dữ liệu bằng cách duyệt chỉ mục. Điều kiện tiên quyết là phải tạo chỉ mục trên bảng. Sau khi tạo chỉ mục trên một cột, khi tìm kiếm dữ liệu, hệ thống sẽ tìm đến vị trí của bản ghi tương ứng thông qua chỉ mục và trả về dữ liệu nhanh chóng. Chỉ mục lưu trữ con trỏ đến giá trị của cột cụ thể và sắp xếp chúng theo thứ tự xác định. Ví dụ, trong bảng thông tin sinh viên tb_students, khi tạo chỉ mục trên cột student_id, hệ thống sẽ tạo ra một bản đồ ánh xạ từ chỉ mục cột đến bản ghi thực tế. Khi người dùng cần tìm dữ liệu của student_id là 12022, hệ thống sẽ tìm chỉ mục sau đó thông qua bản đồ ánh xạ lấy dữ liệu và trả về.
Tóm lại, nếu không sử dụng chỉ mục, MySQL sẽ phải đọc toàn bộ bảng từ dòng đầu tiên cho đến khi tìm thấy dữ liệu liên quan. Càng lớn bảng, càng lâu thời gian tìm kiếm. Nếu cột được truy vấn có chỉ mục, MySQL có thể tìm đến vị trí cụ thể trong file dữ liệu mà không cần kiểm tra toàn bộ bảng, tiết kiệm thời gian đáng kể.
Định nghĩa chỉ mục
Chỉ mục là cấu trúc dữ liệu mà các engine lưu trữ sử dụng để tìm nhanh các bản ghi. Mỗi engine đều có các loại chỉ mục khác nhau, mỗi loại chỉ mục có cấu trúc dữ liệu riêng biệt. Chỉ mục là mối quan hệ giữa giá trị của một hoặc nhiều cột và các dòng dữ liệu trong bảng, thực chất là một bảng mô tả mối quan hệ giữa giá trị cột chỉ mục và các dòng dữ liệu gốc. Việc tạo chỉ mục không thay đổi dữ liệu trong bảng, chỉ tạo ra một cấu trúc dữ liệu trỏ đến bảng; chỉ mục là một file, cần phân bổ không gian đĩa và duy trì bảng chỉ mục.
Lợi ích và hạn chế của chỉ mục
Lợi ích
- Tăng tốc độ tìm kiếm dữ liệu đáng kể.
- Tăng hiệu suất hệ thống thông qua việc sử dụng tối ưu hóa truy vấn.
- Tạo chỉ mục duy nhất đảm bảo tính độc nhất của mỗi dòng dữ liệu trong bảng.
Hạn chế
- Thời gian: Tạo và duy trì chỉ mục tốn thời gian, đặc biệt khi thêm, xóa, sửa đổi dữ liệu trong bảng.
- Không gian: Chỉ mục cần không gian đĩa.
Các trường hợp sử dụng chỉ mục
(1) WHERE
Khi truy vấn theo cột id, chỉ mục có thể được sử dụng. Tuy nhiên, nếu truy vấn theo một cột chưa được lập chỉ mục, không có chỉ mục nào có thể sử dụng.
ALTER TABLE innodb1 ADD sex CHAR(1) NOT NULL COMMENT 'Giới tính' AFTER Guid;
(2) ORDER BY
Nếu không có chỉ mục trên cột sắp xếp, MySQL sẽ thực hiện sắp xếp bên ngoài (external sort) trên toàn bộ dữ liệu, điều này gây giảm hiệu suất. Tuy nhiên, khi có chỉ mục, MySQL có thể lấy dữ liệu theo thứ tự chỉ mục mà không cần sắp xếp lại.
ALTER TABLE ten_bang ADD INDEX(ten_cot);
(3) JOIN
Tạo chỉ mục trên các cột liên kết trong câu lệnh JOIN có thể cải thiện hiệu suất.
(4) Bao phủ chỉ mục
Nếu tất cả các cột cần truy vấn đều có chỉ mục, MySQL sẽ chỉ truy vấn trong bảng chỉ mục mà không cần truy cập dữ liệu gốc. Điều này gọi là bao phủ chỉ mục.
Các loại chỉ mục
Có nhiều loại chỉ mục như chỉ mục chính, chỉ mục duy nhất, chỉ mục thông thường và chỉ mục toàn văn. Mỗi loại chỉ mục có cách hoạt động riêng.
ALTER TABLE ten_bang ADD UNIQUE(ten_cot);
ALTER TABLE ten_bang ADD INDEX ten_chỉ_mục(ten_cot1, ten_cot2);
Chỉ mục tập trung và không tập trung
Chỉ mục tập trung (clustered index) lưu trữ dữ liệu cùng với chỉ mục, trong khi chỉ mục không tập trung (non-clustered index) chỉ lưu trữ con trỏ đến vị trí dữ liệu.
Các thuật toán chỉ mục phổ biến
Có nhiều thuật toán chỉ mục như bảng băm, mảng sắp xếp và cây tìm kiếm. Mỗi thuật toán có ưu nhược điểm riêng.
CREATE INDEX ten_chỉ_mục ON ten_bang(ten_cot);
Lưu ý khi sử dụng chỉ mục
- Tạo chỉ mục cho các cột được sử dụng nhiều trong điều kiện WHERE.
- Tránh tạo chỉ mục cho các cột thay đổi thường xuyên.
- Tránh tạo chỉ mục cho các cột có ít giá trị phân biệt.
Lời khuyên sử dụng chỉ mục
- Tên chỉ mục nên phản ánh rõ ràng mục đích.
- Sử dụng chỉ mục duy nhất cho các trường có tính duy nhất.
- Giới hạn độ dài chỉ mục cho các trường kiểu chuỗi.
- Tránh sử dụng truy vấn mờ từ trái sang phải.
- Lợi dụng tính sắp xếp của chỉ mục trong các câu lệnh ORDER BY.
- Sử dụng chỉ mục bao phủ để tránh truy cập lại bảng.
- Tạo chỉ mục hợp nhất với thứ tự ưu tiên cao nhất.