Cách đánh Index trong MySQL: Index là gì và cách tạo thế nào?

Trong bài này bạn sẽ được học cách sư dụng lệnh CREATE INDEX để tạo Index trong MySQL, đây là một kỹ thuật giúp bạn tối ưu truy vấn trong CSDL MySQL.

Thực ra khóa chính Primary Key cũng là một loại Index có tên gọi là Primary Key Index. Đây là kiểu index đặc biệt nên cho tốc độ truy vấn cực nhanh. Và để hiểu rõ hơn thì ta sẽ đi vào một tình huống cụ thể.

1. MySQL Index là gì?

Index là dữ liệu có cấu trúc như B-Tree giúp cải thiện tốc độ tìm kiếm trên một bảng, làm giảm chi phí thực hiện truy vấn. Việc tối ưu hóa chỉ mục sẽ giúp xác định được vị trí của dữ liệu cần tìm thay vì phải dò theo thứ tự hàng triệu record trong bảng.

Khi bạn tạo một khóa chính thì đồng nghĩa với việc bạn đã tạo một chỉ mục duy nhất, các hàng không được trùng dữ liệu với nhau, tên gọi của nó là PRIMARY, thuộc thể loai clustered index.

Bài viết này được đăng tại [free tuts .net]

Những chỉ mục khác khong phải là PRIMARY thì ta gọi là chỉ mục phụ secondary index hoặc non-clustered indexes.

2. Đặt vấn đề về Index trong MySQL

Giả sử bạn có một danh bạ điện thoại chứa tất cả tên và số điện thoại của người dân trong thành phố. Bây giờ bạn muốn tìm số điện thoại của Bob Cat. Vì các tên được sắp xếp theo thứ tự abc nên trước tiên bạn tìm trang có tên cuối cùng là Cat, sau đó bạn tìm Bob và số điện thoại của anh ấy.

Nếu tên trong danh bạ điện thoại không được sắp xếp theo thứ tự abc, bạn sẽ cần phải đi qua tất cả các trang, đọc mọi tên trên đó cho đến khi bạn tìm thấy Bob Cat. Điều này được gọi là [tìm kiếm tuần tự]. Bạn đi qua tất cả các hàng cho đến khi bạn tìm thấy người có số điện thoại mà bạn đang tìm kiếm.

Liên hệ danh bạ điện thoại với bảng cơ sở dữ liệu, nếu bạn có bảng phonebooks và bạn phải tìm số điện thoại của Bob Cat, bạn sẽ thực hiện truy vấn sau:

SELECT
    phone_number
FROM 
    phonebooks
WHERE 
    first_name = 'Bob' AND 
    last_name = 'Cat';

Truy vấn thực hiện khá nhanh trong trường hợp table có một vài ngàn dữ liệu. Nhưng nếu bảng có hàng triệu record và không được đánh dấu chỉ mục thì truy vấn này sẽ mất khá nhiều thời gian xử lý.

3. Lệnh CREATE INDEX trong MySQL

Thông thường ta sẽ tạo chỉ mục ngay lúc tạo bảng bằng lệnh Create Table. Ví dụ dưới đây mình đã tạo một bảng và thiết lập chỉ mục cho nó luôn.

CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);

Ngoài ra, để thêm một chỉ mục vào bảng có sẵn thì ta sử dụng lệnh CREATE INDEX, cú pháp như sau:

CREATE INDEX index_name ON table_name (column_list)

Để tạo chỉ mục cho một cột hoặc nhiều cột thì tốt nhất ta đặt cho nó một cái tên, phía sau là danh sách các cột.

CREATE INDEX idx_c4 ON t(c4);

Theo mặc định, MySQL tạo chỉ mục B-Tree nếu bạn không chỉ định loại chỉ mục. Sau đây cho thấy loại chỉ mục cho phép dựa trên kiểu lưu trữ của bảng:

Lưu ý rằng câu lệnh CREATE INDEX ở trên là phiên bản đơn giản hóa của câu lệnh CREATE INDEX được giới thiệu bởi MySQL. Chúng ta sẽ học cách sử dụng nó nhiều hơn ở các ví dụ tiếp theo.

4. Dùng CREATE INDEX tạo Index trong MySQL

Câu lệnh SQL sau đây sẽ tìm các nhân viên có chức danh là Sales Rep:

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';

Kết quả như sau:

Kết quả trả về 10 nhân viên đang làm việc với chức danh là Sales Rep. Và để xem chi tiết câu truy vấn này thì ta thêm lệnh EXPLAIN ngay đầu lệnh Select thì sẽ thu được kết quả như sau:

Như bạn thấy, câu truy vấn đã duyệt qua tổng cộng là 23 rows để trả về 10 kết quả như trên.

Bây giờ mình sẽ tạo một chỉ mục cho cột jobTitle như sau:

CREATE INDEX jobTitle ON employees(jobTitle);

Và chạy lại câu truy vấn thêm một lần nữa:

EXPLAIN SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';

Kêt quả thu được như sau:

Thật tuyệt vời, MySQL chỉ duyệt qua 17 rows thay vì 23 rows như ở trên. Điều này chính là nhờ vào kỹ thuật tạo index mà mình đã làm.

Để xem tất cả những index thuộc một table nào đó thì ta sử dụng lệnh SHOW INDEXES.

SHOW INDEXES FROM employees;

Kết quả như hình dưới đây là một ví dụ khá chi tiết, nó hiển thị thông tin và kiểu index rõ ràng.

Như vậy là chúng ta đã học xong cách tạo Index trong MySQL. Đây là một bài khá quan trọng để giúp bạn hiểu được các kiểu index ở những bài tiếp theo.