MySQL 中多列索引中的欄位順序重要嗎？

Question

我知道索引的重要性以及連接順序如何改變效能。我已經閱讀了大量與多列索引相關的內容，但沒有找到我的問題的答案。

我很好奇，如果我做一個多列索引，它們指定的順序是否重要。我的猜測是不會，引擎會將它們視為一個群組，其中順序並不重要。但我想驗證一下。

例如，來自mysql的網站(http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html)

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

在任何情況下，以下內容會更好還是等效，是否會有任何好處？

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (first_name,last_name)
);

具體來說：

INDEX name (last_name,first_name)

對比

INDEX name (first_name,last_name)

Answer 1

兩個索引不同。這在 MySQL 和其他資料庫中都是如此。 MySQL 在文件中解釋了差異.

考慮兩個索引：

create index idx_lf on name(last_name, first_name);
create index idx_fl on name(first_name, last_name);

這兩者應該同樣適用於：

where last_name = XXX and first_name = YYY

idx_lf 對於以下條件來說是最佳的：

where last_name = XXX
where last_name like 'X%'
where last_name = XXX and first_name like 'Y%'
where last_name = XXX order by first_name

idx_fl 將最適合以下情況：

where first_name = YYY
where first_name like 'Y%'
where first_name = YYY and last_name like 'X%'
where first_name = XXX order by last_name

對於許多這樣的情況，可能使用兩個索引，但其中一個是最佳的。例如，考慮使用 idx_lf 查詢：

where first_name = XXX order by last_name

MySQL 可以使用 idx_lf 讀取整個表，然後在 order by 之後進行篩選。我不認為這在實踐中是一個最佳化選項（對於 MySQL），但在其他資料庫中可能會發生這種情況。

Answer 2

在討論多列索引時，我會用電話簿進行類比。電話簿基本上就是先姓後名的索引。因此排序順序是由哪個「列」在前決定的。搜尋分為以下幾類：

1. 如果您查找姓氏為 Smith 的人，您可以輕鬆找到他們，因為這本書是按姓氏排序的。

2. 如果您查找名字是約翰的人，電話簿沒有幫助，因為約翰分散在整本書中。您必須掃描整個電話簿才能找到它們。

3. 如果您查找具有特定姓氏史密斯和特定名字約翰的人，這本書會有所幫助，因為您會發現史密斯一家排序在一起，並且在史密斯一家中，約翰一家也按排序順序找到.

如果您有一本按名字排序的電話簿，然後按姓氏排序，則該電話簿的排序將在上述情況#2 和#3 中為您提供幫助，但不會在情況#1 中為您提供幫助。

這解釋了查找精確值的情況，但是如果您按值範圍查找怎麼辦？假設您想要找出名字為 John、姓氏以「S」開頭的所有人員（Smith、Saunders、Staunton、Sherman 等）。約翰在每個姓氏中按“J”排序，但如果您想要所有以“S”開頭的姓氏的所有約翰，則約翰不會分組在一起。他們又分散了，所以你最終不得不掃描所有姓氏以“S”開頭的名字。然而，如果電話簿按名字然後按姓氏進行組織，您會發現所有約翰都在一起，然後在約翰中，所有“S”姓氏將被分組在一起。

因此，多列索引中的列順序絕對很重要。一種類型的查詢可能需要索引的特定列順序。如果您有多種類型的查詢，則可能需要多個索引來幫助它們，並且列的順序不同。

您可以閱讀我的簡報真正如何設計索引 了解更多信息，或觀看影片。