Để hiểu cách MySQL sử dụng các chỉ mục, trước tiên, tốt nhất bạn nên hiểu các hoạt động và tính năng cơ bản của các chỉ mục. Khi bạn đã hiểu cơ bản về đặc điểm của chúng, bạn có thể bắt đầu đưa ra những lựa chọn thông minh hơn về cách sử dụng chúng phù hợp.
Để hiểu những gì các chỉ mục cho phép MySQL làm, tốt nhất bạn nên nghĩ về cách MySQL hoạt động để trả lời một truy vấn. Hãy tưởng tượng rằng
ALTER TABLE phone_book ADD INDEX [last_name]2 là một bảng chứa danh bạ điện thoại tổng hợp của bang California, với khoảng 35 triệu mục nhập. Và hãy nhớ rằng các bản ghi trong bảng vốn không được sắp xếp. Hãy xem xét một truy vấn như thế này
SELECT * FROM phone_book WHERE last_name = 'Zawodny'
Không có bất kỳ loại chỉ mục nào để tham khảo, MySQL phải đọc tất cả các bản ghi trong bảng
ALTER TABLE phone_book ADD INDEX [last_name]2 và so sánh trường
ALTER TABLE phone_book ADD INDEX [last_name]4 với chuỗi “Zawodny” để xem chúng có khớp không. Rõ ràng là không hiệu quả. Khi số lượng bản ghi tăng lên, thì nỗ lực cần thiết để tìm một bản ghi nhất định cũng tăng theo. Trong khoa học máy tính, chúng tôi gọi đó là vấn đề O[n]
Nhưng được cung cấp một danh bạ điện thoại thực, tất cả chúng ta đều biết cách nhanh chóng xác định vị trí của bất kỳ ai tên Zawodny. lật đến chữ Z ở cuối sách và bắt đầu từ đó. Vì chữ cái thứ hai là “a”, nên chúng tôi biết rằng bất kỳ kết quả trùng khớp nào sẽ nằm ở hoặc gần phía trước danh sách tất cả các tên bắt đầu bằng Z. Phương pháp được sử dụng dựa trên kiến thức về dữ liệu và cách sắp xếp dữ liệu
Đó là gian lận, phải không? . Lý do bạn có thể tìm thấy Zawodnys nhanh như vậy là vì chúng được sắp xếp theo thứ tự abc theo họ. Vì vậy, thật dễ dàng để tìm thấy chúng, tất nhiên là miễn là bạn biết ABC của mình
Hầu hết các sách kỹ thuật [như cuốn sách này] đều cung cấp chỉ mục ở phía sau. Nó cho phép bạn nhanh chóng tìm thấy vị trí của các thuật ngữ và khái niệm quan trọng vì chúng được liệt kê theo thứ tự được sắp xếp cùng với số trang tương ứng. Cần biết mysqlhotcopy được thảo luận ở đâu?
Chỉ mục cơ sở dữ liệu là tương tự. Giống như tác giả hoặc nhà xuất bản sách có thể chọn tạo chỉ mục cho các khái niệm và thuật ngữ quan trọng trong sách, bạn có thể chọn tạo chỉ mục trên một cột cụ thể của bảng cơ sở dữ liệu. Sử dụng ví dụ trước, bạn có thể tạo một chỉ mục trên họ để tra cứu số điện thoại nhanh hơn
ALTER TABLE phone_book ADD INDEX [last_name]
Khi làm như vậy, bạn đang yêu cầu MySQL tạo một danh sách theo thứ tự của tất cả các họ trong bảng
ALTER TABLE phone_book ADD INDEX [last_name]2. Cùng với mỗi cái tên, nó ghi lại vị trí của các bản ghi phù hợp—giống như phần mục lục ở cuối cuốn sách này liệt kê số trang của mỗi mục nhập. []
Từ quan điểm của máy chủ cơ sở dữ liệu, các chỉ mục tồn tại để cơ sở dữ liệu có thể nhanh chóng loại bỏ các hàng có thể có khỏi tập kết quả khi thực hiện truy vấn. Không có bất kỳ chỉ mục nào, MySQL [giống như bất kỳ máy chủ cơ sở dữ liệu nào] phải kiểm tra mọi hàng trong bảng. Điều đó không chỉ tốn thời gian mà còn sử dụng nhiều I/O của đĩa và có thể gây ô nhiễm hiệu quả cho bộ đệm đĩa
Trong thế giới thực, rất hiếm khi tìm thấy dữ liệu động vừa được sắp xếp [và vẫn được sắp xếp]. Sách là một trường hợp đặc biệt;
Bởi vì MySQL cần duy trì một danh sách các giá trị của chỉ mục riêng biệt và cập nhật chúng khi dữ liệu của bạn thay đổi, nên bạn thực sự không muốn lập chỉ mục cho mọi cột trong bảng. Chỉ mục là sự đánh đổi giữa không gian và thời gian. Bạn đang hy sinh một số dung lượng ổ đĩa bổ sung và một chút chi phí hoạt động của CPU trên mỗi truy vấn
ALTER TABLE phone_book ADD INDEX [last_name]6,
ALTER TABLE phone_book ADD INDEX [last_name]7 và
ALTER TABLE phone_book ADD INDEX [last_name]8 để thực hiện hầu hết [nếu không phải tất cả] các truy vấn của bạn nhanh hơn nhiều
Phần lớn tài liệu MySQL sử dụng thuật ngữ chỉ mục và khóa thay thế cho nhau. Nói rằng
ALTER TABLE phone_book ADD INDEX [last_name]4 là một khóa trong bảng
ALTER TABLE phone_book ADD INDEX [last_name]2 cũng giống như nói rằng trường
ALTER TABLE phone_book ADD INDEX [last_name]4 của bảng
ALTER TABLE phone_book ADD INDEX [last_name]2 được lập chỉ mục
Chỉ số đánh đổi không gian lấy hiệu suất. Nhưng đôi khi bạn không muốn đánh đổi quá nhiều không gian cho hiệu suất mà bạn đang theo đuổi. May mắn thay, MySQL cung cấp cho bạn nhiều quyền kiểm soát đối với dung lượng được sử dụng bởi các chỉ mục. Có thể bạn có một bảng
ALTER TABLE phone_book ADD INDEX [last_name]2 với 2 tỷ hàng trong đó. Thêm một chỉ mục trên
ALTER TABLE phone_book ADD INDEX [last_name]4 sẽ cần nhiều dung lượng. Nếu
ALTER TABLE phone_book ADD INDEX [last_name]4 trung bình dài 8 byte, thì bạn đang xem khoảng 16 GB dung lượng cho phần dữ liệu của chỉ mục; . []
Thay vì lập chỉ mục toàn bộ họ, bạn có thể chỉ lập chỉ mục 4 byte đầu tiên
SELECT * FROM phone_book WHERE last_name = 'Zawodny'6
Khi làm như vậy, bạn đã giảm khoảng một nửa yêu cầu về không gian cho phần dữ liệu của chỉ mục. Sự đánh đổi là MySQL không thể loại bỏ khá nhiều hàng bằng cách sử dụng chỉ mục này. Một truy vấn như
SELECT * FROM phone_book WHERE last_name = 'Zawodny'7
truy xuất tất cả các trường bắt đầu bằng
SELECT * FROM phone_book WHERE last_name = 'Zawodny'66, bao gồm tất cả những người có tên
SELECT * FROM phone_book WHERE last_name = 'Zawodny'67,
SELECT * FROM phone_book WHERE last_name = 'Zawodny'68, v.v. Sau đó, truy vấn phải loại bỏ
SELECT * FROM phone_book WHERE last_name = 'Zawodny'68 và tất cả các hàng không liên quan khác
Giống như nhiều công cụ cơ sở dữ liệu quan hệ, MySQL cho phép bạn tạo các chỉ mục bao gồm nhiều cột
ALTER TABLE phone_book ADD INDEX [last_name]2
Các chỉ mục như vậy có thể cải thiện tốc độ truy vấn nếu bạn thường truy vấn tất cả các cột cùng nhau trong mệnh đề
SELECT * FROM phone_book WHERE last_name = 'Zawodny'70 hoặc nếu một cột không có đủ loại. Tất nhiên, bạn có thể sử dụng các chỉ mục một phần để giảm dung lượng cần thiết
ALTER TABLE phone_book ADD INDEX [last_name]4
Trong cả hai trường hợp, một truy vấn để tìm Josh Woodward thực hiện nhanh chóng
ALTER TABLE phone_book ADD INDEX [last_name]5
Có họ và tên được lập chỉ mục cùng nhau có nghĩa là MySQL có thể loại bỏ các hàng dựa trên cả hai trường, do đó giảm đáng kể số lượng hàng mà nó phải xem xét. Xét cho cùng, có nhiều người trong danh bạ điện thoại có họ bắt đầu bằng “Wood” hơn là những người có họ bắt đầu bằng “Wood” và tên cũng bắt đầu bằng “Josh”. ”
Khi thảo luận về các chỉ mục nhiều cột, bạn có thể thấy các cột được lập chỉ mục riêng lẻ được gọi là các phần chính hoặc “các phần của khóa. ” Chỉ mục nhiều cột còn được gọi là chỉ mục tổng hợp hoặc chỉ mục tổng hợp
Vậy tại sao không tạo hai chỉ mục, một trên
ALTER TABLE phone_book ADD INDEX [last_name]4 và một trên
SELECT * FROM phone_book WHERE last_name = 'Zawodny'72? . Trên thực tế, MySQL sẽ chỉ sử dụng một chỉ mục cho mỗi bảng cho mỗi truy vấn—ngoại trừ các
SELECT * FROM phone_book WHERE last_name = 'Zawodny'73. [] Thực tế này đủ quan trọng để nói lại. MySQL sẽ chỉ sử dụng một chỉ mục trên mỗi bảng cho mỗi truy vấn
Với các chỉ mục riêng biệt trên
SELECT * FROM phone_book WHERE last_name = 'Zawodny'72 và
ALTER TABLE phone_book ADD INDEX [last_name]4, MySQL sẽ chọn cái này hay cái kia. Nó làm như vậy bằng cách đưa ra một dự đoán có giáo dục về chỉ mục nào cho phép nó khớp với ít hàng hơn. Chúng tôi gọi đó là phỏng đoán có giáo dục vì MySQL theo dõi một số thống kê chỉ mục cho phép nó suy ra dữ liệu trông như thế nào. Các số liệu thống kê, tất nhiên, là khái quát. Mặc dù họ thường để MySQL đưa ra quyết định thông minh, nhưng nếu bạn có dữ liệu rất lộn xộn, MySQL có thể đưa ra lựa chọn dưới mức tối ưu về việc sử dụng chỉ mục. Chúng tôi cho rằng dữ liệu bị vón cục nếu khóa được lập chỉ mục thưa thớt ở một số khu vực [chẳng hạn như tên bắt đầu bằng X] và tập trung cao ở những khu vực khác [chẳng hạn như tên
SELECT * FROM phone_book WHERE last_name = 'Zawodny'67 ở các quốc gia nói tiếng Anh]. Đây là một chủ đề quan trọng mà chúng ta sẽ xem xét lại sau trong cuốn sách này
MySQL sắp xếp các giá trị trong chỉ mục như thế nào? . MySQL không cung cấp cho bạn quyền kiểm soát đối với việc sắp xếp nội bộ các giá trị chỉ mục. Nó có ít lý do để. Kể từ phiên bản 4. 0, nó thực hiện tốt công việc tối ưu hóa các trường hợp gây ra hiệu suất chậm hơn cho các hệ thống cơ sở dữ liệu khác
Ví dụ: một số sản phẩm cơ sở dữ liệu có thể thực hiện truy vấn này nhanh chóng
ALTER TABLE phone_book ADD INDEX [last_name]2
Và truy vấn này từ từ
ALTER TABLE phone_book ADD INDEX [last_name]3
Tại sao? . Trong trường hợp đầu tiên, cơ sở dữ liệu sử dụng chỉ mục nhiều cột để định vị tất cả các bản ghi phù hợp. Vì các bản ghi đã được lưu trữ theo thứ tự giảm dần nên không cần sắp xếp chúng. Nhưng trong trường hợp thứ hai, máy chủ tìm thấy tất cả các bản ghi phù hợp và sau đó thực hiện chuyển lần thứ hai qua các hàng đó để sắp xếp chúng
MySQL đủ thông minh để “đi ngược lại chỉ mục” khi cần thiết. Nó sẽ thực hiện cả hai truy vấn rất nhanh. Trong cả hai trường hợp, nó không cần sắp xếp các bản ghi
Các chỉ mục không phải lúc nào cũng được sử dụng để định vị các hàng phù hợp cho một truy vấn. Một chỉ mục duy nhất xác định rằng một giá trị cụ thể chỉ có thể xuất hiện một lần trong một cột nhất định. [] Trong ví dụ về danh bạ điện thoại, bạn có thể tạo một chỉ mục duy nhất trên
SELECT * FROM phone_book WHERE last_name = 'Zawodny'77 để đảm bảo rằng mỗi số điện thoại chỉ xuất hiện một lần. []
ALTER TABLE phone_book ADD INDEX [last_name]5
Chỉ mục duy nhất phục vụ mục đích kép. Nó hoạt động giống như bất kỳ chỉ mục nào khác khi bạn thực hiện truy vấn dựa trên số điện thoại
ALTER TABLE phone_book ADD INDEX [last_name]0
Tuy nhiên, nó cũng kiểm tra mọi giá trị khi cố gắng chèn hoặc cập nhật một bản ghi để đảm bảo rằng giá trị đó chưa tồn tại. Theo cách này, chỉ mục duy nhất hoạt động như một ràng buộc
Chỉ mục duy nhất sử dụng nhiều không gian như chỉ mục không duy nhất. Giá trị của mọi cột cũng như vị trí của bản ghi được lưu trữ. Điều này có thể gây lãng phí nếu bạn sử dụng chỉ mục duy nhất làm ràng buộc và không bao giờ làm chỉ mục. Nói cách khác, bạn có thể dựa vào chỉ mục duy nhất để thực thi tính duy nhất nhưng không bao giờ viết truy vấn sử dụng giá trị duy nhất. Trong trường hợp này, không cần MySQL lưu trữ vị trí của mọi bản ghi trong chỉ mục. bạn sẽ không bao giờ sử dụng chúng
Thật không may, không có cách nào để báo hiệu ý định của bạn với MySQL. Trong tương lai, chúng tôi có thể sẽ tìm thấy một tính năng được giới thiệu cho trường hợp cụ thể này. Công cụ lưu trữ MyISAM đã hỗ trợ cho các cột duy nhất không có chỉ mục [công cụ này sử dụng hệ thống dựa trên hàm băm], nhưng cơ chế này chưa được hiển thị ở cấp độ SQL
Chỉ mục nhóm và phụ
Với các bảng MyISAM, các chỉ mục được giữ trong một tệp hoàn toàn riêng biệt có chứa danh sách các khóa chính [và có thể là phụ] và một giá trị biểu thị độ lệch byte cho bản ghi. Những điều này đảm bảo MySQL có thể tìm thấy và sau đó nhanh chóng chuyển đến điểm đó trong cơ sở dữ liệu để xác định vị trí bản ghi. MySQL phải lưu trữ các chỉ mục theo cách này vì các bản ghi được lưu trữ theo thứ tự cơ bản là ngẫu nhiên
Với các chỉ mục được nhóm, khóa chính và bản ghi được "nhóm" lại với nhau và tất cả các bản ghi đều được lưu trữ theo thứ tự khóa chính. InnoDB sử dụng các chỉ mục được nhóm. Trong thế giới Oracle, các chỉ mục được nhóm được gọi là "bảng được tổ chức theo chỉ mục", có thể giúp bạn ghi nhớ mối quan hệ giữa khóa chính và thứ tự hàng
Khi dữ liệu của bạn hầu như luôn được tìm kiếm thông qua khóa chính, các chỉ mục được nhóm có thể giúp việc tra cứu cực kỳ nhanh chóng. Với chỉ mục MyISAM tiêu chuẩn, có hai lần tra cứu, một lần tra cứu chỉ mục và lần thứ hai tìm chính bảng thông qua vị trí được chỉ định trong chỉ mục. Với các chỉ mục được nhóm, có một tra cứu duy nhất trỏ trực tiếp đến bản ghi được đề cập
Một số hoạt động làm cho các chỉ mục nhóm kém hiệu quả hơn. Chẳng hạn, hãy xem xét khi sử dụng chỉ mục phụ. Quay lại ví dụ về danh bạ điện thoại của chúng ta, giả sử bạn đã đặt
ALTER TABLE phone_book ADD INDEX [last_name]4 làm chỉ mục chính và đặt
SELECT * FROM phone_book WHERE last_name = 'Zawodny'77 làm chỉ mục phụ và bạn thực hiện truy vấn sau
ALTER TABLE phone_book ADD INDEX [last_name]0
MySQL quét chỉ mục
SELECT * FROM phone_book WHERE last_name = 'Zawodny'77 để tìm mục nhập cho
ALTER TABLE phone_book ADD INDEX [last_name]21, mục nhập này chứa mục nhập khóa chính
ALTER TABLE phone_book ADD INDEX [last_name]22 vì chỉ mục chính của
ALTER TABLE phone_book ADD INDEX [last_name]2 là họ. MySQL sau đó bỏ qua mục có liên quan trong chính cơ sở dữ liệu
Nói cách khác, việc tra cứu dựa trên khóa chính của bạn diễn ra cực kỳ nhanh và việc tra cứu dựa trên các chỉ mục phụ diễn ra với tốc độ cơ bản giống như việc tra cứu chỉ mục MyISAM.
Nhưng trong các trường hợp đúng [hay đúng hơn là sai], chỉ mục được nhóm thực sự có thể làm giảm hiệu suất. Khi bạn sử dụng cùng với chỉ mục phụ, bạn phải xem xét tác động kết hợp đối với việc lưu trữ. Chỉ mục phụ trỏ đến khóa chính chứ không phải hàng. Do đó, nếu bạn lập chỉ mục trên một giá trị rất lớn và có nhiều chỉ mục phụ, bạn sẽ nhận được nhiều bản sao trùng lặp của chỉ mục chính đó, đầu tiên dưới dạng chỉ mục nhóm được lưu trữ cùng với bản ghi, nhưng sau đó lặp lại nhiều lần như bạn có. . Với một giá trị nhỏ làm khóa chính, điều này có thể không quá tệ, nhưng nếu bạn đang sử dụng thứ gì đó có khả năng dài, chẳng hạn như URL, thì việc lưu trữ lặp lại khóa chính trên đĩa này có thể gây ra sự cố lưu trữ
Một tình trạng khác ít phổ biến hơn nhưng không kém phần rắc rối xảy ra khi dữ liệu bị thay đổi sao cho khóa chính bị thay đổi trên một bản ghi. Đây là chức năng tốn kém nhất của các chỉ mục nhóm. Một số điều có thể xảy ra để làm cho hoạt động này trở thành một hiệu suất nghiêm trọng hơn
Thay đổi bản ghi được đề cập theo truy vấn đã được đưa ra
Xác định khóa chính mới cho bản ghi đó, dựa trên bản ghi dữ liệu đã thay đổi
Định vị lại các bản ghi được lưu trữ để bản ghi được đề cập đến vị trí thích hợp trong không gian bảng
Cập nhật mọi chỉ mục phụ trỏ đến khóa chính đó
Như bạn có thể tưởng tượng, nếu bạn đang thay đổi khóa chính cho một số bản ghi, lệnh
ALTER TABLE phone_book ADD INDEX [last_name]7 đó có thể mất khá nhiều thời gian để thực hiện công việc của nó, đặc biệt là trên các bảng lớn hơn. Chọn khóa chính của bạn một cách khôn ngoan. Sử dụng các giá trị khó có thể thay đổi, chẳng hạn như số tài khoản An sinh xã hội thay vì họ, số sê-ri thay vì tên sản phẩm, v.v.
Chỉ mục duy nhất so với khóa chính
Nếu bạn đến từ các cơ sở dữ liệu quan hệ khác, bạn có thể thắc mắc sự khác biệt giữa khóa chính và chỉ mục duy nhất trong MySQL là gì. Như thường lệ, nó phụ thuộc vào. Trong các bảng MyISAM, hầu như không có sự khác biệt. Điều đặc biệt duy nhất về khóa chính là nó không thể chứa giá trị NULL. Khóa chính đơn giản là một
ALTER TABLE phone_book ADD INDEX [last_name]25
ALTER TABLE phone_book ADD INDEX [last_name]26
ALTER TABLE phone_book ADD INDEX [last_name]27
ALTER TABLE phone_book ADD INDEX [last_name]28 tên là
ALTER TABLE phone_book ADD INDEX [last_name]29. Các bảng MyISAM không yêu cầu bạn khai báo khóa chính
Các bảng InnoDB và BDB yêu cầu các khóa chính cho mọi bảng. Tuy nhiên, không có yêu cầu bạn chỉ định một. Nếu không, công cụ lưu trữ sẽ tự động thêm khóa chính ẩn cho bạn. Trong cả hai trường hợp, các khóa chính chỉ đơn giản là các giá trị số tăng dần, tương tự như cột
ALTER TABLE phone_book ADD INDEX [last_name]40. Nếu bạn quyết định thêm khóa chính của riêng mình sau đó, chỉ cần sử dụng ________ 241 ________ 242 để thêm một. Cả hai công cụ lưu trữ sẽ loại bỏ các khóa được tạo nội bộ của chúng để thay thế cho bạn. Bảng heap không yêu cầu khóa chính nhưng sẽ tạo một khóa cho bạn. Trên thực tế, bạn có thể tạo các bảng Heap mà không có chỉ mục nào cả
Thường khó nhớ rằng SQL sử dụng logic ba trạng thái khi thực hiện các phép toán logic. Trừ khi một cột được khai báo
ALTER TABLE phone_book ADD INDEX [last_name]25
ALTER TABLE phone_book ADD INDEX [last_name]26, có ba kết quả có thể xảy ra trong phép so sánh logic. So sánh có thể đúng vì các giá trị là tương đương; . Bất cứ khi nào một trong các giá trị là NULL, kết quả cũng là NULL
Các lập trình viên thường nghĩ NULL là không xác định hoặc không xác định. Đó là một cách để thông báo cho máy chủ cơ sở dữ liệu “một giá trị không xác định xuất hiện ở đây. ” Vậy giá trị NULL ảnh hưởng đến chỉ mục như thế nào?
Các giá trị NULL có thể được sử dụng trong các chỉ mục bình thường [không duy nhất]. Điều này đúng với tất cả các máy chủ cơ sở dữ liệu. Tuy nhiên, không giống như nhiều máy chủ cơ sở dữ liệu, MySQL cho phép bạn sử dụng các giá trị NULL trong các chỉ mục duy nhất. [] Bạn có thể lưu trữ bao nhiêu giá trị NULL tùy thích trong một chỉ mục như vậy. Điều này có vẻ hơi phản trực giác, nhưng đó là bản chất của NULL. Bởi vì NULL đại diện cho một giá trị không xác định, MySQL cần khẳng định rằng tất cả các giá trị NULL đều giống nhau nếu nó chỉ cho phép một giá trị duy nhất trong một chỉ mục duy nhất
Để làm cho mọi thứ thú vị hơn một chút, giá trị NULL chỉ có thể xuất hiện một lần dưới dạng khóa chính. Tại sao? . Đây là một trong số ít cách mà các khóa chính khác với các chỉ mục duy nhất trong MySQL. Và, trong trường hợp bạn đang thắc mắc, việc cho phép các giá trị NULL trong chỉ mục thực sự không ảnh hưởng đến hiệu suất