MySQL Hướng dẫn chỉ mục – Tạo, thêm và thả

Chỉ số là gì?

Chỉ mục trong MySQL sắp xếp dữ liệu một cách tuần tự có tổ chức. Chúng được tạo trên (các) cột sẽ được sử dụng để lọc dữ liệu. Hãy coi chỉ mục như một danh sách được sắp xếp theo thứ tự bảng chữ cái. Việc tra cứu các tên đã được sắp xếp theo thứ tự bảng chữ cái sẽ dễ dàng hơn so với những tên không được sắp xếp.

Sử dụng chỉ mục trên các bảng được cập nhật thường xuyên có thể dẫn đến hiệu suất kém. Điều này là do MySQL tạo một khối chỉ mục mới mỗi khi dữ liệu đó được thêm hoặc cập nhật trong bảng. Nói chung, nên sử dụng chỉ mục trên các bảng có dữ liệu không thay đổi thường xuyên nhưng được sử dụng nhiều trong các truy vấn tìm kiếm chọn lọc.

Sử dụng Index để làm gì?

Không ai thích hệ thống chậm. Hiệu suất hệ thống cao có tầm quan trọng hàng đầu trong hầu hết các hệ thống cơ sở dữ liệu. Hầu hết các doanh nghiệp đều đầu tư mạnh vào phần cứng để việc truy xuất và thao tác dữ liệu có thể nhanh hơn. Nhưng có giới hạn đối với khoản đầu tư vào phần cứng mà một doanh nghiệp có thể thực hiện. Tối ưu hóa cơ sở dữ liệu của bạn là một giải pháp rẻ hơn và tốt hơn.

Thời gian phản hồi chậm thường là do các bản ghi được lưu trữ ngẫu nhiên trong các bảng cơ sở dữ liệu. Các truy vấn tìm kiếm phải lần lượt lặp qua toàn bộ bản ghi được lưu trữ ngẫu nhiên để xác định dữ liệu mong muốn. Điều này dẫn đến cơ sở dữ liệu có hiệu suất kém khi truy xuất dữ liệu từ các bảng lớn. Do đó, Chỉ mục được sử dụng để sắp xếp dữ liệu để giúp tìm kiếm dễ dàng hơn.

Cú pháp: Tạo chỉ mục

Chỉ mục có thể được xác định theo 2 cách

Tại thời điểm tạo bảng
Sau khi tạo bảng

Ví dụ:

Đối với myflixdb của chúng tôi, chúng tôi mong đợi có nhiều tìm kiếm về cơ sở dữ liệu theo tên đầy đủ.

Chúng tôi sẽ thêm cột “full_names” vào Index trong bảng mới “members_indexed”.

Kịch bản hiển thị bên dưới giúp chúng tôi đạt được điều đó.

CREATE TABLE `members_indexed` (
  `membership_number` int(11) NOT NULL AUTO_INCREMENT,
  `full_names` varchar(150) DEFAULT NULL,
  `gender` varchar(6) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `contact_number` varchar(75) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`membership_number`),INDEX(full_names)
) ENGINE=InnoDB;

Thực thi tập lệnh SQL ở trên trong MySQL bàn làm việc chống lại “myflixdb”.

Làm mới myflixdb sẽ hiển thị bảng mới được tạo có tên là member_indexed.

"Ghi chú" Bảng Member_indexed có “full_names” trong nút chỉ mục.

Khi cơ sở thành viên mở rộng và số lượng bản ghi tăng lên, các truy vấn tìm kiếm trên bảng member_indexed sử dụng mệnh đề WHERE và ORDER BY sẽ nhanh hơn nhiều so với các truy vấn được thực hiện trên bảng thành viên mà không xác định chỉ mục.

Thêm cú pháp cơ bản chỉ mục

Ví dụ trên đã tạo chỉ mục khi xác định bảng cơ sở dữ liệu. Giả sử chúng ta đã xác định một bảng và các truy vấn tìm kiếm trên đó rất chậm. Họ mất quá nhiều thời gian để trả về kết quả. Sau khi điều tra sự cố, chúng tôi phát hiện ra rằng chúng tôi có thể cải thiện đáng kể hiệu suất hệ thống bằng cách tạo INDEX trên cột được sử dụng phổ biến nhất trong mệnh đề WHERE.

Chúng ta có thể sử dụng truy vấn sau để thêm chỉ mục

CREATE INDEX id_index ON table_name(column_name);

Giả sử các truy vấn tìm kiếm trên bảng movies rất chậm và chúng ta muốn sử dụng chỉ mục trên "tiêu đề phim" để tăng tốc các truy vấn, chúng ta có thể sử dụng tập lệnh sau để thực hiện điều đó.

CREATE INDEX `title_index` ON `movies`(`title`);

Việc thực hiện truy vấn trên sẽ tạo một chỉ mục trên trường tiêu đề trong bảng phim.

Điều này có nghĩa là tất cả các truy vấn tìm kiếm trên bảng phim sử dụng “tiêu đề” sẽ nhanh hơn.

Tuy nhiên, các truy vấn tìm kiếm trên các trường khác trong bảng phim sẽ vẫn chậm hơn so với các truy vấn dựa trên trường được lập chỉ mục.

Lưu ý: Bạn có thể tạo chỉ mục trên nhiều cột nếu cần tùy thuộc vào các trường mà bạn định sử dụng cho công cụ tìm kiếm cơ sở dữ liệu của mình.

Nếu bạn muốn xem các chỉ mục được xác định trên một bảng cụ thể, bạn có thể sử dụng tập lệnh sau để thực hiện điều đó.

SHOW INDEXES FROM table_name;

Bây giờ chúng ta hãy xem tất cả các chỉ mục được xác định trên bảng phim trong myflixdb.

SHOW INDEXES FROM `movies`;

Thực thi đoạn script trên trong MySQL bàn làm việc so với myflixdb cho chúng tôi kết quả về chỉ mục được tạo.

Lưu ý: Khóa chính và khóa ngoại trên bảng đã được lập chỉ mục bởi MySQL. Mỗi chỉ mục có tên riêng và cột mà nó được xác định cũng được hiển thị.

Cú pháp: Chỉ mục thả

Lệnh drop được sử dụng để loại bỏ các chỉ mục đã được xác định trên một bảng.

Có thể đôi khi bạn đã xác định một chỉ mục trên bảng được cập nhật thường xuyên. Bạn có thể muốn xóa các chỉ mục trên bảng như vậy để cải thiện hiệu suất truy vấn CẬP NHẬT và CHÈN. Cú pháp cơ bản được sử dụng để xóa chỉ mục trên bảng như sau.

DROP INDEX `index_id` ON `table_name`;

Bây giờ chúng ta hãy xem một ví dụ thực tế.

DROP INDEX ` full_names` ON `members_indexed`;

Việc thực hiện lệnh trên sẽ loại bỏ chỉ mục có id `full_names` khỏi bảng Member_indexed.

Tổng kết

Các chỉ số rất có tác dụng khi giúp cải thiện đáng kể hiệu suất của MySQL các truy vấn tìm kiếm.
Chỉ mục có thể được xác định khi tạo bảng hoặc được thêm vào sau khi bảng đã được tạo.
Bạn có thể xác định chỉ mục trên nhiều cột trong một bảng.
SHOW INDEX FROM table_name được sử dụng để hiển thị các chỉ mục được xác định trên một bảng.
Lệnh DROP được sử dụng để xóa chỉ mục đã xác định trên một bảng nhất định.

Bạn có thể thích: