Chế độ xem được lập chỉ mục của MySQL

Trang chủ > MySQL Indexes hoạt động như thế nào?

MySQL là cơ sở dữ liệu được sử dụng nhiều nhất trên thế giới. Và khi nó tiếp tục trở nên phổ biến như một hệ thống cơ sở dữ liệu nguồn mở dành cho các nhà phát triển, việc hiểu cách các chỉ mục này hoạt động là một bước quan trọng đối với các nhà phát triển và quản trị viên cơ sở dữ liệu. Hướng dẫn này sẽ khám phá các chỉ mục nhóm và chỉ mục phụ trong MySQL. MySQL cũng có các loại chỉ mục khác ngoài chỉ mục cây B mà tôi sẽ thảo luận, chẳng hạn như chỉ mục toàn văn và chỉ mục băm. Tuy nhiên, tôi sẽ bỏ qua các chỉ mục đó cho bài viết này vì chúng là các chỉ mục hơi đặc biệt phù hợp cho các tìm kiếm dựa trên văn bản và tra cứu một bản ghi nhanh chóng. Đối với các chuyên gia cơ sở dữ liệu, việc triển khai các chỉ mục chính xác cho khối lượng công việc là nền tảng của bất kỳ hệ thống cơ sở dữ liệu quan hệ nào đang hoạt động tốt. Việc thêm và điều chỉnh các chỉ mục cho phù hợp với khối lượng công việc đã mang lại một số hiệu suất tăng đáng kể nhất trong nhiều năm tư vấn của tôi. Tuy nhiên, để thêm đúng chỉ mục, trước tiên bạn phải hiểu các loại chỉ mục phổ biến trong MySQL, cách tạo chỉ mục của riêng bạn và tôi cũng sẽ cung cấp thêm thông tin chi tiết về lợi ích hiệu suất cơ sở dữ liệu khi sử dụng chúng đúng cách

Chỉ mục nhóm

Các chỉ mục được nhóm là các cấu trúc dữ liệu lưu trữ dữ liệu của các bảng cơ sở dữ liệu. Điều quan trọng là phải hiểu rằng chỉ mục được nhóm LÀ bảng và tất cả các bảng MySQL đều có chỉ mục được nhóm, chỉ mục này sắp xếp thứ tự các hàng trong bảng dựa trên khóa được nhóm. Trong MySQL, chỉ mục nhóm và chỉ mục phụ đều là cấu trúc dữ liệu cây B. Chỉ mục cây B là cấu trúc chỉ mục cơ sở dữ liệu thường được sử dụng trong thế giới cơ sở dữ liệu quan hệ vì nó cho phép tìm kiếm và sắp xếp dữ liệu nhanh chóng với chi phí lưu trữ tối thiểu cho chỉ mục. Để hoàn thành cả hai nhiệm vụ, chỉ mục cây B phải duy trì dữ liệu trong cấu trúc theo thứ tự được sắp xếp. Khi một bảng có khóa chính hoặc ràng buộc duy nhất, MySQL sẽ nhóm bảng dựa trên khóa chính hoặc ràng buộc duy nhất đó. Chúng có xu hướng là ứng cử viên tốt cho khóa chỉ mục được nhóm vì chúng rất độc đáo. Tuy nhiên, khi một bảng được tạo mà không có khóa chính hoặc ràng buộc duy nhất, thì một chỉ mục nhóm vẫn được tạo ngầm cho bạn. Thêm về điều này sau

Cách tạo một chỉ mục cụm trong MySQL

Đầu tiên, hãy tạo cơ sở dữ liệu thử nghiệm cho các ví dụ demo này. Đối với Giao diện người dùng đồ họa [GUI], tôi sẽ sử dụng Phiên bản cộng đồng DBeaver – một giao diện tuyệt vời để phát triển và quản lý các phiên bản cơ sở dữ liệu khác nhau. Đối với cơ sở dữ liệu MySQL, tôi sẽ sử dụng Cơ sở dữ liệu Azure cho Máy chủ linh hoạt MySQL. Azure giúp khởi động máy chủ cơ sở dữ liệu MySQL rất dễ dàng, vì vậy tôi có thể chạy các bản trình diễn của mình và sau đó phá bỏ phiên bản một cách nhanh chóng với chi phí phát sinh tối thiểu. MySQL là duy nhất ở chỗ nó có tùy chọn sử dụng các công cụ lưu trữ khác nhau. Các công cụ lưu trữ khác nhau có thể cung cấp các mức chức năng khác nhau. Công cụ lưu trữ mà tôi sẽ tập trung vào là công cụ lưu trữ InnoDB, vì nó là công cụ được sử dụng nhiều nhất cho các mục đích cơ sở dữ liệu quan hệ. Bước đầu tiên của tôi là tạo cơ sở dữ liệu thử nghiệm cho các bản trình diễn của chúng tôi. create database sqlskills; Sau đó tôi sẽ chuyển sang bối cảnh cơ sở dữ liệu sqlskills của mình thông qua lệnh sử dụng. use sqlskills; Tiếp theo, tôi sẽ tạo một bảng có tên là các số với hai cột; . create table numbers [ numbercol int not null, charcol varchar[100] not null ]; Ở đây tôi sẽ truy vấn hai bảng information_schema khác nhau cho công cụ lưu trữ InnoDB. Chế độ xem innodb_indexes lưu trữ thông tin liên quan đến chỉ mục và chế độ xem innodb_tables trả về thông tin liên quan đến bảng. Chúng tôi thấy từ đầu ra rằng có một chỉ mục có tên GEN_CLUST_INDEX, được tạo tự động cho chúng tôi khi bảng được tạo. select ii.index_id, ii.name, ii.table_id, t.name from information_schema.innodb_indexes ii join information_schema.innodb_tables t on ii.table_id = t.table_id where t.name = 'sqlskills/numbers'; Như tôi đã đề cập trước đây, khi không có khóa chính hoặc ràng buộc duy nhất trên bảng, InnoDB tạo chỉ mục nhóm ẩn có tên GEN_CLUST_INDEX trên cột tổng hợp 6 byte chứa các giá trị ID hàng tăng dần đều. Các giá trị này được sắp xếp theo thứ tự vật lý của ID hàng theo thứ tự chèn. Tôi sẽ sử dụng biểu thức bảng chung SQL đệ quy [CTE] để tạo danh sách 5.000 số và giá trị chuỗi ngẫu nhiên để chèn vào bảng số. Lưu ý rằng tôi sử dụng số ngẫu nhiên này trong câu lệnh ORDER BY để chèn dữ liệu vào bảng một cách ngẫu nhiên. set session cte_max_recursion_depth = 5000; insert into numbers[numbercol, charcol] with recursive cte [n] AS [ select 1 union all select n + 1 from cte where n < 5000 ] select n, left [md5 [rand []], 100] from cte order by 2; Bây giờ khi tôi chạy CHỌN đối với bảng, dữ liệu được trả về theo thứ tự ngẫu nhiên. select * from numbers n; Tôi có thể sử dụng từ khóa EXPLAIN để trả về kế hoạch thực hiện cho một truy vấn. Khi nó được sử dụng với tùy chọn PHÂN TÍCH, kế hoạch thực hiện thực tế của truy vấn được trả về. Nói cách khác, truy vấn được thực thi và kế hoạch thực hiện truy vấn được hiển thị. Chúng ta có thể thấy từ đầu ra của kế hoạch thực hiện rằng số hàng được chạm vào là 5.000 – hoặc toàn bộ số hàng trong bảng, cùng với chi phí cao. Nói cách khác, mặc dù bảng này có Chỉ mục cụm, nhưng nó không hữu ích cho mục đích truy vấn vì khóa chỉ mục về cơ bản là rowid – không bao gồm cột numbercol. explain analyze select * from numbers n where numbercol = 1000; Để tạo chỉ mục nhóm trên bảng trên cột numbercol, tôi có thể thêm ràng buộc khóa chính vào bảng. Cột này không chứa giá trị null; . alter table numbers add primary key [numbercol]; Khi tôi chạy cùng một truy vấn như trước, cùng với kế hoạch thực hiện thực tế, chúng tôi thấy số lượng hàng được chạm vào lần này chỉ là một – một cải tiến hiệu suất rất đáng kể. explain analyze select * from numbers n where numbercol = 1000; Chỉ mục cây B có thể được coi là cấu trúc cây lộn ngược trong đó trang gốc ở trên cùng. Các trang cấp trung gian lưu trữ các con trỏ tới các trang bổ sung ở các cấp thấp hơn trong chỉ mục. Đối với các chỉ mục được nhóm trong MySQL, khi bạn đến cấp độ lá của chỉ mục – tất cả các cột trong bảng được lưu trữ trong cấu trúc cây B. Các trang cấp độ trung gian và trang cấp độ lá này là các danh sách được liên kết đôi – chúng duy trì thứ tự của chỉ mục, do đó có thể dễ dàng tìm thấy các giá trị trong danh sách được sắp xếp nhưng cũng có thể sắp xếp tập kết quả theo thứ tự tăng dần hoặc giảm dần mà không cần bổ sung . Không có bộ lưu trữ riêng cho dữ liệu bảng. Bất kỳ chỉ mục nào trên bảng MySQL không nằm trong chỉ mục nhóm được gọi là chỉ mục phụ. Các chỉ mục phụ cũng là các chỉ mục B-tree và về cơ bản hoạt động tương tự như chỉ mục nhóm – chúng được lưu trữ và duy trì theo thứ tự được sắp xếp. Xem xét sơ đồ cấp cao sau đây của chỉ mục phụ MySQL. Thiết kế gần giống như chỉ mục nhóm – với sự khác biệt duy nhất là những gì được lưu trữ ở cấp độ lá của chỉ mục. Trong khi cấp độ lá của chỉ mục được nhóm lưu trữ tất cả các giá trị cột cho hàng, thì cấp độ lá của chỉ mục phụ chỉ lưu trữ các cột được xác định trong chỉ mục [với khóa ngoài cùng bên trái là cột sắp xếp cấu trúc dữ liệu] và một số con trỏ quay lại . Điều đáng chú ý là các trang ở cấp độ lá cũng là danh sách liên kết đôi, hỗ trợ tra cứu trang trước và trang tiếp theo cũng như sắp xếp hai chiều.

Cách tạo chỉ mục phụ trong MySQL + Ví dụ

Hãy xem ví dụ về vị trí chỉ mục phụ có thể tăng tốc hiệu suất truy vấn. Ở đây tôi đã lấy một trong các giá trị ngẫu nhiên từ bảng số của mình để sử dụng trong mệnh đề where. Bạn sẽ cần phải làm tương tự vì bạn có thể sẽ không có cùng các giá trị văn bản ngẫu nhiên trong cột charcol của mình như tôi có trong bảng của mình. Lưu ý trong kế hoạch thực hiện, quá trình quét bảng đã được thực hiện và tất cả các hàng trong bảng đều được kiểm tra để tìm ra hàng duy nhất này. Đây không phải là một hoạt động hiệu quả và là một cơ hội tuyệt vời để thêm một chỉ mục. explain analyze select charcol from numbers n where charcol = 'b331b0fe3e800bd8fb0fd0786c5a2067'; Bây giờ tôi sẽ tạo chỉ mục phụ trên cột charcol. Cú pháp ở đây rất đơn giản. tạo chỉ mục index_name trên table_name [column_name]. use sqlskills;0 Khi tôi chạy lại cùng một truy vấn và lấy kế hoạch thực hiện, tôi có thể thấy sự khác biệt đáng kể về hiệu suất. Lần này có một chỉ mục bao phủ [bao phủ có nghĩa là nó bao phủ tất cả các cột cần thiết được trả về trong câu lệnh CHỌN] mà trình tối ưu hóa truy vấn có thể sử dụng và chỉ một hàng duy nhất được chạm vào. Đây là trường hợp lý tưởng để tạo chỉ mục phụ nhằm cải thiện hiệu suất truy vấn. explain analyze select charcol from numbers n where charcol = 'b331b0fe3e800bd8fb0fd0786c5a2067';

Chỉ mục nhóm so với. Chỉ mục phụ

Có ba sự khác biệt đáng kể giữa hai loại chỉ mục MySQL mà bạn nên biết

1. Vì chỉ mục được nhóm LÀ bảng nên bạn chỉ có thể có một trong số chúng. Bạn có thể có nhiều chỉ mục phụ trên mỗi bảng trong MySQL và điều này được khuyến khích để tạo các chỉ mục cụ thể cho nhu cầu truy vấn của bạn
2. Các chỉ mục phụ thường chỉ chứa một tập hợp con của các cột trong bảng. Các chỉ mục này nhằm tăng tốc độ tìm kiếm dữ liệu và giúp sắp xếp dữ liệu, do đó, thông thường không nên đưa tất cả các cột trong bảng vào chỉ mục phụ. Sẽ rất lãng phí không gian và bộ nhớ để làm như vậy
3. Có một mối quan hệ giữa khóa chỉ mục nhóm và khóa của chỉ mục phụ. Vì các chỉ mục phụ là bản sao của dữ liệu trong bảng nhóm cơ sở nên phải có cách liên kết bản ghi trong chỉ mục phụ với hàng đầy đủ trong chỉ mục nhóm. Để tạo điều kiện thuận lợi cho việc này, đối với mỗi hàng trong chỉ mục phụ, khóa nhóm cho hàng đó cũng được lưu trữ. Điều này rất quan trọng để hiểu vì khóa chỉ mục nhóm dành cho bảng cơ sở của bạn càng rộng thì các chỉ mục phụ của bạn sẽ càng rộng.

Mẹo chuyên nghiệp. Chỉ mục cây B FTW

B-tree indexes are generally the index of choice for most InnoDB implementations in MySQL because they allow for the quick searching and sorting of data, have little overhead, and are great with inequality lookups. Understanding when and how to implement these indexes is one of the best tools in the toolbelt of a database professional. From MariaDB and Percona servers to cloud and VM deployments, SolarWinds has database performance management solutions built to support a range of MySQL instances. Learn more about our MySQL Solutions.

Chế độ xem được lập chỉ mục trong MySQL là gì?

Sự khác biệt giữa chỉ mục và chế độ xem trong MySQL là gì?

Làm cách nào để xem các chỉ mục bảng trong MySQL?

Chúng tôi có thể sử dụng lập chỉ mục trong chế độ xem không?