Hướng dẫn lập chỉ mục MySQL
Trang chủ > MySQL Indexes hoạt động như thế nào? Show
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ómCá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 sauCá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à 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'; 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; explain analyze select * from numbers n where numbercol = 1000; alter table numbers add primary key (numbercol); Khi tôi chạy cùng một truy vấn như trước đây, 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 sự cải thiện hiệu suất rất đáng kể. explain analyze select * from numbers n where numbercol = 1000; 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'; 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
Mẹo chuyên nghiệp. Chỉ mục cây B FTWB-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ỉ mục trong MySQL là gì?Chỉ mục được dùng để nhanh chóng tìm các hàng có giá trị cột cụ thể . Không có chỉ mục, MySQL phải bắt đầu với hàng đầu tiên và sau đó đọc toàn bộ bảng để tìm các hàng có liên quan. Bàn càng lớn, chi phí này càng cao.
Làm cách nào để đặt chỉ mục trong MySQL?Thông thường, chúng tôi tạo chỉ mục tại thời điểm tạo bảng trong cơ sở dữ liệu. Câu lệnh sau đây tạo một bảng có chỉ mục chứa hai cột col2 và col3. Nếu chúng ta muốn thêm chỉ mục vào bảng, chúng ta sẽ sử dụng câu lệnh CREATE INDEX như sau. mysql> TẠO INDEX [tên_chỉ mục] BẬT [tên_bảng] (tên cột)
Làm cách nào để lấy chỉ mục trong MySQL?Để xem chỉ mục cho một bảng cụ thể, hãy sử dụng SHOW INDEX. HIỂN THỊ CHỈ SỐ TỪ bảng của bạn ; . CHỌN DISTINCT TABLE_NAME, INDEX_NAME TỪ THÔNG TIN_SCHEMA.
Làm cách nào để gọi một chỉ mục trong MySQL?Chỉ cần bỏ qua từ khóa UNIQUE khỏi truy vấn để tạo một chỉ mục đơn giản . Một chỉ mục đơn giản cho phép các giá trị trùng lặp 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ừ dành riêng DESC sau tên cột. |