Chỉ mục (INDEX) trong MySQL

Một cơ sở dữ liệu với chỉ mục là một cấu trúc dữ liệu mà cải thiện tốc độ của các hoạt động trong một bảng. Các chỉ mục có thể được tạo bởi sử dụng một hoặc nhiều cột, cung cấp cơ sở cho việc tăng nhanh quá trình tìm kiếm ngẫu nhiên và sắp xếp thứ tự truy cập tới các bản ghi một cách hiệu quả.

Trong khi tạo chỉ mục, bạn nên cân nhắc xem cột nào sẽ được sử dụng để tạo các truy vấn SQL và tạo một hoặc nhiều chỉ mục trên các cột đó.

Thực tế, các chỉ mục cũng là kiểu của bảng, mà giữ trường Primary Key hoặc trường index và một con trỏ tới mỗi bản ghi bên trong bảng thực sự.

Người sử dụng không thể thấy các chỉ mục này, chúng chỉ được sử dụng để tăng tốc các truy vấn và sẽ được sử dụng bởi Database Search Engine để định vị các bản ghi một cách cực kỳ nhanh.

Các lệnh INSERT và UPDATE tốn nhiều thời gian hơn trên các bảng có chỉ mục trong khi các lệnh SELECT trở nên nhanh hơn trên các bảng này. Lý do là vì, trong khi chèn và cập nhật, cơ sở dữ liệu cũng phải cần chèn hoặc cập nhật các giá trị chỉ mục.

Simple Index và Unique Index trong MySQL

Bạn có thể tạo một Unique Index trên một bảng. Một Unique Index nghĩa là hai hàng không thể có cùng giá trị chỉ mục. Dưới đây là cú pháp để tạo một chỉ mục trên một bảng.

CREATE UNIQUE INDEX ten_chi_muc
ON ten_bang ( cot1, cot2,...);

Bạn có thể sử dụng một hoặc nhiều cột để tạo một chỉ mục. Ví dụ, chúng ta có thể tạo chỉ mục trên cột ten trong bảng sinhvienk60 như sau:

CREATE UNIQUE INDEX tenchimuc
ON sinhvienk60 (ten)

Bạn có thể tạo một Simple Index trên một bảng. Đơn giản, bạn chỉ cần bỏ qua từ khóa UNIQUE từ truy vấn để tạo Simple Index. Simple Index cho phép tồn tại bản sao các giá trị trong một bảng.

Nếu bạn muốn lập chỉ mục các giá trị trong một cột theo thứ tự giảm dần, bạn có thể thêm từ DESC sau tên cột.

CREATE UNIQUE INDEX tenchimuc
ON sinhvienk60 (ten DESC)

Lệnh ALTER để thêm và xóa INDEX trong MySQL

Có 4 kiểu lệnh để thêm các chỉ mục cho một bảng:

ALTER TABLE ten_bang ADD PRIMARY KEY (danh_sach_cot): Lệnh này thêm một PRIMARY KEY, nghĩa là các giá trị được lập chỉ mục phải là duy nhất và không thể là NULL.
ALTER TABLE ten_bang ADD UNIQUE ten_chi_muc (danh_sach_cot): Lệnh này tạo một chỉ mục cho các giá trị để giá trị đó phải là duy nhất (với giá trị NULL là ngoại lệ, chúng có thể xuất hiện nhiều lần).
ALTER TABLE ten_bang ADD INDEX ten_chi_muc (danh_sach_cot): Lệnh này thêm một chỉ mục thông thường, trong đó bất kỳ giá trị nào có thể xuất hiện nhiều hơn một lần.
ALTER TABLE ten_bang ADD FULLTEXT ten_chi_muc (danh_sach_cot): Lệnh này tạo một chỉ mục FULLTEXT đặc biệt, được sử dụng cho mục đích tìm kiếm văn bản.

Ví dụ sau để thêm chỉ mục cho cột ho trong bảng sinhvienk59 đang tồn tại.

ALTER TABLE sinhvienk59 ADD INDEX (ho);

Bạn có thể xóa bất kỳ INDEX nào bởi sử dụng mệnh đề DROP cùng với lệnh ALTER. Bạn xét ví dụ sau để xóa chỉ mục đã được tạo ở trên.

ALTER TABLE sinhvienk59 DROP INDEX (ho);

Lệnh ALTER để thêm và xóa PRIMARY KEY

Bạn cũng có thể thêm Primary Key theo cách tương tự. Nhưng đảm bảo rằng Primary Key làm việc trên các cột mà là NOT NULL.

Ví dụ sau để thêm Primary Key vào một bảng đang tồn tại. Đầu tiên, tạo cột là NOT NULL và sau đó thêm cho nó một Primary Key.

ALTER TABLE sinhvienk59 MODIFY diemthi INT NOT NULL;
ALTER TABLE sinhvienk59 ADD PRIMARY KEY (diemthi);

Bạn có thể sử dụng lệnh ALTER để xóa một Primary Key như sau:

ALTER TABLE sinhvienk59 DROP PRIMARY KEY;

Để xóa một chỉ mục mà không là một PRIMARY KEY, bạn phải xác định tên chỉ mục.

Hiển thị thông tin chỉ mục trong MySQL

Bạn sử dụng lệnh SHOW INDEX để liệt kê tất cả chỉ mục được liên kết với một bảng. Kết quả trong định dạng dọc (được xác định bởi \G) thường là có ích hơn với lệnh này, để tránh một dòng liệt kê dài.

Bạn thử ví dụ sau: