正确建立索引以及最左字首原則

正确建立索引以及最左字首原則 http://www.bieryun.com/3128.html

1. 索引建立的原則

用于索引的最好的備選資料列是那些出現在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。

僅僅出現在SELECT關鍵字後面的輸出資料列清單中的資料列不是很好的備選列

SELECT

col_a <- 不是備選列

FROM

tbl1 LEFT JOIN tbl2

ON tbl1.col_b = tbl2.col_c <- 備選列

WHERE

col_d = expr; <- 備選列

當然，顯示的資料列與WHERE子句中使用的資料列也可能相同。

我們的觀點是輸出清單中的資料列本質上不是用于索引的很好的備選列。

2. 複合索引的建立以及最左字首原則

索引字元串值的字首（prefixe）。如果你需要索引一個字元串資料列，那麼最好在任何适當的情況下都應該指定字首長度。

例如，如果有CHAR(200)資料列，如果前面10個或20個字元都不同，就不要索引整個資料列。

索引前面10個或20個字元會節省大量的空間

你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT資料列的字首。

假設你在表的state、city和zip資料列上建立了複合索引。索引中的資料行按照state/city/zip次序排列，

是以它們也會自動地按照state/city和state次序排列。這意味着，即使你在查詢中隻指定了state值，

或者指定state和city值，MySQL也可以使用這個索引。是以，這個索引可以被用于搜尋如下所示的資料列組合：

state, city, zip

state, city

state

MySQL不能利用這個索引來搜尋沒有包含在最左字首的内容。例如，如果你按照city或zip來搜尋，

就不會使用到這個索引。如果你搜尋給定的state和具體的ZIP代碼（索引的1和3列），

該索引也是不能用于這種組合值的，盡管MySQL可以利用索引來查找比對的state進而縮小搜尋的範圍。

如果你考慮給已經索引過的表添加索引，那麼就要考慮你将增加的索引是否是已有的多列索引的最左字首。

如果是這樣的，不用增加索引，因為已經有了(例如，如果你在state、city和zip上建立了索引，那麼沒有必要再增加state的索引)。

3. 執行個體分析

通過執行個體了解單列索引、多列索引以及最左字首原則

執行個體：現在我們想查出滿足以下條件的使用者id：

mysql>SELECT ｀uid｀ FROM people WHERE lname｀='Liu'  AND ｀fname｀='Zhiqun' AND ｀age｀=26

因為我們不想掃描整表，故考慮用索引。

單列索引：

ALTER TABLE people ADD INDEX lname (lname);

将lname列建索引，這樣就把範圍限制在lname='Liu'的結果集1上，之後掃描結果集1，産生滿足fname='Zhiqun'的結果集2，再掃描結果集2，找到 age=26的結果集3，即最終結果。

由于建立了lname列的索引，與執行表的完全掃描相比，效率提高了很多，但我們要求掃描的記錄數量仍舊遠遠超過了實際所需 要的。雖然我們可以删除lname列上的索引，再建立fname或者age 列的索引，但是，不論在哪個列上建立索引搜尋效率仍舊相似。

多列索引：

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

為了提高搜尋效率，我們需要考慮運用多列索引,由于索引檔案以B－Tree格式儲存，是以我們不用掃描任何記錄，即可得到最終結果。

注：在mysql中執行查詢時，隻能使用一個索引，如果我們在lname,fname,age上分别建索引,執行查詢時，隻能使用一個索引，mysql會選擇一個最嚴格(獲得結果集記錄數最少)的索引。

3.最左字首：顧名思義，就是最左優先，上例中我們建立了lname_fname_age多列索引,相當于建立了(lname)單列索引，(lname,fname)組合索引以及(lname,fname,age)組合索引。

注：在建立多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。