Chỉ mục cơ sở dữ liệu là một cấu trúc dữ liệu giúp cải thiện tốc độ hoạt động trong một bảng. Các chỉ mục có thể được tạo bằng cách sử dụng một hoặc nhiều cột, cung cấp cơ sở cho cả tra cứu ngẫu nhiên nhanh chóng và sắp xếp hiệu quả quyền truy cập vào bản ghi
Khi tạo chỉ mục, cần xem xét tất cả các cột nào sẽ được sử dụng để thực hiện các truy vấn SQL và tạo một hoặc nhiều chỉ mục trên các cột đó
Trên thực tế, các chỉ mục cũng là một loại bảng, giữ khóa chính hoặc trường chỉ mục và một con trỏ tới từng bản ghi trong bảng thực tế
Người dùng không thể nhìn thấy các chỉ mục, chúng chỉ được sử dụng để tăng tốc độ truy vấn và sẽ được Công cụ tìm kiếm cơ sở dữ liệu sử dụng để xác định vị trí các bản ghi rất nhanh
Các câu lệnh INSERT và UPDATE mất nhiều thời gian hơn trên các bảng có chỉ mục, trong khi các câu lệnh SELECT trở nên nhanh hơn trên các bảng đó. Lý do là trong khi thực hiện thao tác chèn hoặc cập nhật, cơ sở dữ liệu cũng cần chèn hoặc cập nhật các giá trị chỉ mục
Chỉ mục đơn giản và độc đáo
Bạn có thể tạo một chỉ mục duy nhất trên một bảng. Chỉ mục duy nhất có nghĩa là hai hàng không thể có cùng giá trị chỉ mục. Đây là cú pháp để tạo một Index trên một bảng
CREATE UNIQUE INDEX index_name ON table_name [ column1, column2,...];
Bạn có thể sử dụng một hoặc nhiều cột để tạo chỉ mục
Ví dụ: chúng ta có thể tạo chỉ mục trên tutorials_tbl bằng hướng dẫn_tác giả
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]
Bạn có thể tạo một chỉ mục đơn giản trên một bảng. Chỉ cần bỏ 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
mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author DESC]
Lệnh ALTER để thêm và xóa INDEX
Có bốn loại câu lệnh để thêm chỉ mục vào bảng -
ALTER TABLE tbl_name THÊM KHÓA CHÍNH [column_list] − Câu lệnh này thêm KHÓA CHÍNH, có 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 tbl_name ADD UNIQUE index_name [column_list] − Câu lệnh này tạo một chỉ mục mà các giá trị phải là duy nhất [ngoại trừ các giá trị NULL, có thể xuất hiện nhiều lần]
ALTER TABLE tbl_name THÊM INDEX index_name [column_list] − Thao tác này thêm một chỉ mục thông thường trong đó bất kỳ giá trị nào cũng có thể xuất hiện nhiều lần
ALTER TABLE tbl_name ADD FULLTEXT index_name [column_list] − Điều này tạo ra 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
Khối mã sau đây là một ví dụ để thêm chỉ mục vào một bảng hiện có
mysql> ALTER TABLE testalter_tbl ADD INDEX [c];
Bạn có thể loại bỏ bất kỳ INDEX nào bằng cách sử dụng mệnh đề DROP cùng với lệnh ALTER
Hãy thử ví dụ sau để loại bỏ chỉ mục được tạo ở trên
mysql> ALTER TABLE testalter_tbl DROP INDEX [c];
Bạn có thể loại bỏ bất kỳ INDEX nào bằng cách sử dụng mệnh đề DROP cùng với lệnh ALTER
ALTER Lệnh thêm và bỏ PRIMARY KEY
Bạn cũng có thể thêm khóa chính theo cách tương tự. Nhưng hãy đảm bảo rằng Khóa chính hoạt động trên các cột KHÔNG NULL
Khối mã sau đây là một ví dụ để thêm khóa chính vào bảng hiện có. Điều này sẽ tạo một cột KHÔNG NULL trước và sau đó thêm nó làm khóa chính
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY [i];
Bạn có thể sử dụng lệnh ALTER để xóa khóa chính như sau -
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
Để xóa một chỉ mục không phải là KHÓA CHÍNH, bạn phải chỉ định tên chỉ mục
Hiển thị thông tin INDEX
Bạn có thể sử dụng lệnh SHOW INDEX để liệt kê tất cả các chỉ mục được liên kết với một bảng. Đầu ra định dạng dọc [được chỉ định bởi \G] thường hữu ích với câu lệnh này, để tránh một dòng dài bao quanh -
Hướng dẫn ngắn gọn về các phương pháp hay nhất xung quanh việc lập chỉ mục cho các bảng lớn và cách sử dụng phân vùng để giảm tải cho việc lập chỉ mục
Bài viết này được cập nhật lần cuối vào ngày 5 tháng 8 năm 2021. Cảm ơn John Trollope đã chỉ ra một số lỗi chính tả đáng xấu hổ
S số lượng truy vấn thấp là một trong những vấn đề chính trong bất kỳ nhóm dữ liệu hoặc phần mềm nào. Thông thường, phản ứng giật đầu gối đầu tiên để tăng tốc các truy vấn của bạn là tạo tất cả các chỉ mục mà bạn có thể nghĩ đến. Mặc dù các chỉ mục được cho là làm cho các truy vấn nhanh hơn, nhưng chúng không thể tha thứ cho bạn khỏi những tội lỗi mà bạn đã phạm phải khi viết các truy vấn. Ngoài ra, việc tạo các chỉ mục đi kèm với chi phí, đó là điều mà nếu không nghĩ đến, có thể giết chết hiệu suất cơ sở dữ liệu ở mọi khía cạnh chứ không chỉ đọc dữ liệu.
Các vấn đề truy vấn chậm được nhìn thấy thường xuyên hơn với các bảng lớn. Chúng ta có thể thấy các truy vấn chậm trong hai loại thiết lập khác nhau — một loại không quan trọng về thời gian và loại kia là. Hãy nói về những truy vấn chậm đối với truy vấn quan trọng trước tiên. Nếu bạn đang phát triển một ứng dụng, một nguyên tắc nhỏ là viết các truy vấn chạy thường xuyên của bạn theo cách sao cho chúng trả về phản hồi trong vòng 500 mili giây. Đối với các hệ thống phân tích [không quan trọng về thời gian], nó mang tính chủ quan cao. Các công cụ báo cáo và BI có thể phức tạp. Ai đó đã từng nói với tôi rằng việc chạy một bảng điều khiển BI mất hơn một ngày và họ không thực sự bận tâm về điều đó
Đã làm việc với các bảng lớn [> 1 tỷ bản ghi] khá nhiều, tôi nhận ra rằng chúng ta có thể giải quyết một số việc trong khi lập chỉ mục trên chúng
1. Sửa đổi một Chỉ mục;Tạo và duy trì một chỉ mục trên một bảng lớn tốn kém hơn trên các bảng nhỏ hơn. Bất cứ khi nào bạn tạo một chỉ mục, một bản sao của cột được lập chỉ mục + khóa chính sẽ được tạo trên đĩa và chỉ mục được lưu trong bộ nhớ càng nhiều càng tốt. Nếu một chỉ mục có tất cả dữ liệu theo yêu cầu của một truy vấn, thì nó sẽ không bao giờ chuyển đến bảng thực tế. Ví dụ: nếu bạn đã lọc dữ liệu trên customer_id và được nhóm theo year và chỉ mục nằm trên [customer_id, year] và bạn không truy vấn bất kỳ điều gì khác trong câu lệnh
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]0, thì truy vấn sẽ không chuyển đến bảng đĩa để tìm nạp các bản ghi dưới dạng chỉ mục
Bạn nên cố gắng có các chỉ mục tối ưu cho tất cả các truy vấn, tôi. e. , chỉ mục bao gồm hầu hết, nếu không phải tất cả, các cột nên sử dụng chỉ mục trong các hoạt động như lọc, nhóm, sắp xếp, chọn, v.v. Các chỉ số này được gọi là chỉ số bao trùm. Họ bao gồm các yêu cầu cho truy vấn của bạn
Nhiều chỉ mục hơn có nghĩa là chèn chậm hơn;
Thiết kế chỉ mục là khó khăn. Nếu hệ thống của bạn có nhiều truy vấn, một hoặc thậm chí một vài chỉ mục có thể không đáp ứng yêu cầu của tất cả các truy vấn đó. đó là nguy hiểm. Phản ứng đầu tiên, như đã đề cập trước đó, là tạo một chỉ mục phù hợp với các truy vấn của bạn, không sử dụng các chỉ mục
Chi phí chung của các chỉ mục là rất lớn. Mỗi khi bạn thêm một chỉ mục, nó sẽ ghi trên bảng được lập chỉ mục trở nên chậm hơn vì nó phải cân bằng nhiều cấu trúc cây B hơn mỗi khi có bản ghi. Chi phí chắc chắn cao hơn khi việc ghi B-tree không tuần tự, đó là lý do tại sao, tốt hơn hết là chọn một chỉ mục hiện có và sửa đổi nó bằng cách thêm một cột khác hoặc thay đổi thứ tự cột hoặc cả hai
Giảm dấu chân của bạn bằng cách sửa đổi một chỉ mục hiện có thay vì thêm nhiều chỉ mục khác một cách thiếu suy nghĩ
Để điều này xảy ra, bạn phải có cái nhìn sâu sắc về các truy vấn của mình. Sẽ thật tuyệt nếu có một danh mục cho bạn biết truy vấn nào yêu cầu chỉ mục nào để những sửa đổi bạn thực hiện đối với chỉ mục không có tác động ngoài ý muốn đối với phần còn lại của truy vấn
MySQL hỗ trợ các thay đổi trực tuyến cho hoạt động lập chỉ mục — vì vậy nếu bạn tạo hoặc xóa một chỉ mục, việc đọc và ghi trên bảng sẽ không bị ảnh hưởng trong quá trình tạo chỉ mục. Đó là những gì tài liệu nói, nhưng tôi đã thấy vấn đề với điều đó. Để có hiệu suất tốt hơn với các thao tác lập chỉ mục, hãy sử dụng công cụ thay đổi giản đồ pt-online của Percona. Nó đã được thử và kiểm tra
2. Sử dụng lập chỉ mục và phân vùng cùng nhauCân bằng cây B lớn hơn sẽ tốn nhiều tài nguyên hơn so với cân bằng cây B nhỏ hơn. Phân vùng chia bảng của bạn thành các bảng con nhỏ hơn dưới mui xe, nhưng bạn không biết về nó trừ khi vào đĩa và thấy rằng có các tệp bảng khác nhau được tạo thay vì một tệp cho bảng của bạn. Điều tương tự cũng xảy ra với các chỉ mục trên bảng đó
Động cơ chính của lập chỉ mục và phân vùng là như nhau - loại bỏ dữ liệu cho các truy vấn. Ngoài việc loại bỏ dữ liệu bằng cách cắt xén phân vùng, các phân vùng còn có tác dụng phụ tích cực mà tôi vừa đề cập — nó mang lại cho chúng ta các cây nhỏ hơn để cân bằng, các chỉ mục nhỏ hơn để tính toán lại
Một nguyên tắc nhỏ khác là sử dụng phân vùng cho các bảng thực sự lớn, tôi. e. , các bảng có ít nhất 100 triệu hàng. Sau khi phân vùng xong, tất cả các truy vấn của bạn đối với bảng được phân vùng phải chứa
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]1 trong mệnh đề
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]2, nếu không, đó sẽ là quét toàn bộ bảng — tương đương với tìm kiếm tuyến tính trên toàn bộ bảng
Các phân vùng dẫn đến các cây B/chỉ mục nhỏ hơn, do đó sẽ ít phải tính toán lại các chỉ mục đó trên các phần chèn
Để lập chỉ mục hoạt động, bạn phải có một trường phù hợp mà tất cả các truy vấn của bạn có thể sử dụng. Có nhiều loại phân vùng khác nhau mà bạn có thể sử dụng trên dữ liệu của mình. Tìm hiểu thêm về họ ở đây
3. Tránh hoạt động trên đĩa bằng mọi giáMặc dù điều này nên được che đậy bằng cách tạo các chỉ mục bao hàm, nhưng tôi nghĩ nó xứng đáng được đề cập riêng vì các chỉ mục chỉ có thể giúp bạn che giấu việc viết truy vấn không tốt. Hoạt động trên đĩa rất tốn kém. Tôi tiếp tục đưa ra phép loại suy của Jim Gray để chứng minh Disk I/O vì điều quan trọng là phải hiểu điều này trong bối cảnh hiện tại
Các hoạt động sắp xếp không sử dụng chỉ mục được thực hiện trên đĩa. Đĩa chậm. Để tránh các hoạt động của đĩa, đảm bảo rằng bạn tìm kiếm các gợi ý & thông tin trong
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]3 của truy vấn của bạn. Khi bạn nhìn thấy
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]4, hãy hiểu rằng nó sẽ cố gắng khớp toàn bộ bảng vào bộ nhớ thành nhiều phần. Nếu bảng quá lớn để vừa trong bộ nhớ, nó sẽ tạo một bảng tạm thời trên đĩa và thực hiện ở đó. Hãy tìm một
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]5 có hoặc không có sự kết hợp của
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]6. Bất cứ khi nào MySQL không thể thực hiện sắp xếp bằng chỉ mục, thì
CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl [tutorial_author]5 sẽ hiển thị trong kế hoạch
Để hiểu sâu về vấn đề này, vui lòng xem qua bài đăng rất cũ này của một người làm việc trên trình tối ưu hóa MySQL. Mặc dù bài đăng đã cũ nhưng tôi nghĩ nó vẫn còn phù hợp và không có nhiều thay đổi đối với phần này
Blog của Sergey Petrunia " MySQL thực thi ORDER BY như thế nào
Trong vài tuần qua, đã có một loạt các lỗi tối ưu hóa liên quan đến ĐẶT HÀNG/NHÓM THEO, trong đó tôi là người sửa lỗi hoặc…
s. dã yên thảo. bọc lưới
Có một số điều bạn nên quan tâm khi sắp xếp — hãy nhớ rằng bạn không thể bỏ qua các cột trong chỉ mục. Bạn không thể viết một truy vấn sắp xếp theo thứ tự của year1 và mong đợi nó sử dụng chỉ mục đầy đủ cho một chỉ mục. Chỉ mục cũng sẽ không hoạt động nếu bạn có bộ lọc year2 hoặc mệnh đề year3 trên cột đầu tiên. Nó cũng sẽ không hoạt động khi hai cột year4 được sắp xếp theo một thứ tự khác trong truy vấn, tôi. e. , year5. Nếu bạn không quan tâm đến bất kỳ điểm nào trong số này, chỉ mục sẽ không thực hiện truy vấn
Bản chất của điểm này là hiểu ý tưởng đảm bảo rằng bạn cũng sử dụng các chỉ mục để sắp xếp, nếu không, đó sẽ là một hoạt động rất tốn kém
Xử lý các bảng lớn trong cơ sở dữ liệu rất thú vị. Đó thực sự là khi bạn hiểu được sự phức tạp của hệ thống cơ sở dữ liệu, điểm mạnh và điểm yếu của nó. Tôi khuyên bạn nên tạo một cơ sở dữ liệu thử nghiệm, tạo dữ liệu thử nghiệm để điền vào một bảng lớn và chạy các truy vấn để hiểu cách thức hoạt động của tất cả. Thực hành lý thuyết trumps
Cảm ơn Kai Sassnowski vì bài nói chuyện này đã xác thực và củng cố những năm tôi tìm hiểu về lập chỉ mục trong MySQL
Bài nói chuyện của Kai Sassnowski về Lập chỉ mục cơ sở dữ liệu tại Laracon EUĐây là một phần của loạt bài viết về hiệu suất cơ sở dữ liệu & SQL cho Hướng tới Khoa học Dữ liệu. Dưới đây là một số trong số họ -