[MySQL 25] Index - Chỉ mục

Index là gì?

Không ai thích hệ thống chậm.

Hiệu năng 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 tư mạnh vào phần cứng để việc truy xuất dữ liệu và thao tác có thể nhanh hơn.

Nhưng có giới hạn cho các khoản đầu tư 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.

Để đạt được mục đích này, chúng ta có thể sử dụng INDEXES.

• Sự chậm chạp trong thời gian đáp ứng 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ặp qua toàn bộ các bản ghi được lưu ngẫu nhiên lần lượt để xác định dữ liệu mong muốn.
• Điều này dẫn đến cơ sở dữ liệu hiệu suất kém khi lấy dữ liệu từ các bảng lớn
• Các chỉ số có ích trong các tình huống như vậy. Lập chỉ mục sắp xếp dữ liệu theo cách tuần tự có tổ chức. Nhìn vào chỉ mục dưới dạng danh sách được sắp xếp theo thứ tự bảng chữ cái. Dễ dàng tìm kiếm các tên đã được sắp xếp theo thứ tự bảng chữ cái hơn các tên không được sắp xếp.
• INDEX được tạo trên (các) cột sẽ được sử dụng để lọc dữ liệu.
• Sử dụng các 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 ra 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, các chỉ mục nên được sử dụng trên các bảng có dữ liệu không thay đổi thường xuyên nhưng được sử dụng rất nhiều trong các truy vấn tìm kiếm được chọn.

Tạo cú pháp cơ bản chỉ mục

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

1. Tại thời điểm tạo bảng

2. Sau khi bảng đã được tạo

Thí dụ:

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

Chúng tôi sẽ thêm cột "full_names" vào Chỉ mục trong bảng mới "thành viên_indexed".

Kịch bản hiển thị dưới đây 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 bàn làm việc của MySQL đối với "myflixdb".  

Làm mới myflixdb cho thấy bảng mới được tạo có tên thành viên_indexed.

Bảng " Note " thành viên 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 thành viên_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 trong 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 đã có một bảng được xác định 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ả lại kết quả. Sau khi điều tra vấn đề, chúng tôi phát hiện ra rằng chúng tôi có thể cải thiện hiệu năng hệ thống rất nhiều 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 tôi có thể sử dụng truy vấn sau để thêm chỉ mục

SHOW INDEXES FROM table_name;

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

SHOW INDEXES FROM `movies`;

Thực hiện truy vấn trên tạo ra 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 bằng cách 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 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 để làm điều đó.

DROP INDEX `index_id` ON `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.

DROP INDEX ` full_names` ON `members_indexed`;

Việc thực thi đoạn script trên trong bàn làm việc của MySQL đối với myflixdb cho chúng ta các kết quả sau được hiển thị bên dưới.

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

Cú pháp thả chỉ mục cơ bản

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 thường xuyên được cập nhật. Bạn có thể muốn xóa các chỉ mục trên một bảng như vậy để cải thiện hiệu năng truy vấn CẬP NHẬT và CHERTN. Cú pháp cơ bản được sử dụng để thả chỉ mục trên bảng như sau.

INDOP DROP `index_id` ON` tên_bảng`;

Bây giờ chúng ta hãy nhìn vào một ví dụ thực tế.

INDOP DROP `full_names` ON` thành viên_indexed`;

Thực hiện lệnh trên sẽ loại bỏ chỉ mục với id `full_names` từ bảng thành viên_indexed.

Tóm lược

• Các chỉ mục rất mạnh khi cải thiện đáng kể hiệu năng của các truy vấn tìm kiếm MySQL.
• Các 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 các chỉ mục trên nhiều cột trên một bảng.
• SHOW INDEX TỪ tên_bảng đượ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 để loại bỏ một chỉ mục được xác định trên một bảng đã cho.