MySQL如何建立一個好索引？建立索引的5條建議
過濾效率高的放前面
對於一個多列索引，它的儲存順序是先按第一列進行比較，然後是第二列，第三列...這樣。查詢時，如果第一列能夠排除的越多，那麼後面列需要判斷的行數就越少，效率越高。
關於如何判斷哪個列的過濾效率更高，可以通過選擇性計算來決定。例如我們要在books表建立一個name列和author列的索引，可以計算這兩列各自的選擇性：
select count(distinct name) / count(*) as name, count(distinct author) / count(*) as author from books;
最後得出結果如下：
|
Name |
author |
|
0.95 |
0.9 |
顯然name欄位的選擇性更高，那麼如果把name放第一列，在name條件過濾時就可以排除更多的列，減少接下來 author的過濾。
使用頻率高的放前面
其實該建議比上一個建議優先順序更高
例如一個商品管理頁面，一般都是基於該店家的上架或已下架的商品，再新增其他的查詢條件等等。由於所有的查詢都需要帶有shopid和status條件，此時應該優先將這兩個條件作為基本字首，這樣就可以方便複用索引。
例如一個(shopid, status, createdat)的索引，當查詢條件只有shopid和status時，也可以使用該索引。如果完全根據欄位的過濾效率來決定索引，就需要建立很多不同的索引。
避免排序
索引的值都是有序排列的，在建立索引時還可以顯式指定每個列的排序方式，例如
create index idx_books_author_created_at on books (author, created_at DESC);
此時，如果執行下面的的查詢
select * from books where author = 'author' order by created_at DESC;
由於滿足auhtor的索引的created_at列都是有序排列的，所以不需要再進行額外的排序操作。
當結果資料集很大時，應該儘可能的通過索引來避免查詢的額外排序，因為當記憶體排序空間(sort_buffer_size)不夠用時，就需要把一部分內容放到硬碟中，此時會很影響效能。
例如一個分頁查詢每頁顯示100條，按從大到小的順序顯示，當瀏覽到第100頁時，如果查詢是file sort的，資料庫需要使用堆排序先計算出這個表裡面前100 * 100 = 10000條最大的資料，然後取9900 - 10000之間的資料返回給客戶端，在計算的過程中，這個最大堆如果放不下就需要儲存到磁碟中，但是又需要頻繁比較和替換。
減少隨機IO
在之前對硬碟知識瞭解後可以知道，一次隨機讀會有10ms的定址延遲，如果一次查詢涉及達到多次的隨機讀，會很大程度的限制查詢效能。常見的sql查詢造成隨機IO的包括回表和join
例如下面的查詢
select * from books where author = 'author1';
如果author1有100本書，但是這100本書並不是連續錄入的，也就是說這100本書在硬碟中的儲存是分離的。那麼在有二級索引(author, created_at)的情況下，MySQL先通過二級索引找到滿足author1的所有books的id，然後再通過id在聚簇索引中找到具體資料。
在這一過程中，二級索引的儲存可以認為是連續的，那麼二級索引耗時就是10ms + 100 * 0.01 = 11ms，包含一次定址以及接下來的順序讀。而主鍵索引回表造成的隨機IO最差情況是10ms * 100 = 1000ms。那麼一共就需要11ms + 1000ms = 1011ms
通常減少隨機IO的一種方式就使用覆蓋索引。例如上面的查詢中，如果我們只是想要該作者的書名，可以將(author, createdat)擴充套件為(author, createdat,name)，然後將sql修改如下
select name from books where author = 'author1';
由於索引中已經有name的資訊，此時就不會再次回表，查詢耗時就變成了10ms + 100 * 0.01 = 11ms
值得一提的是mysql5.6新增一個叫做索引條件下推的優化，例如在有索引(author, created_at,name)的情況下，進行下面的查詢：
select name from books where author = 'author1' and name like '%name%' and created_at > '2020-01-01';
根據最左匹配原則，這個查詢只能用到索引的author欄位，如果沒有索引條件下推優化，資料庫需要在二級索引找到滿足author條件的所有列id，然後回表找到剩餘資訊後，再過濾name和created_at條件。
有了索引條件下推，在找到滿足author條件的所有索引後，會再用索引的name欄位進行普通過濾，儘量減少回表的次數，減少隨機IO
避免重複索引
以減少隨機IO中的查詢為例，我們最終是把(author, createdat)擴充套件為(author, createdat,name)，而不是建立一個新的(author, name)的索引。
在實際應用場景中也有類似的情況，例如建立一個userid的外來鍵索引，然後又建立(userid, xxx)的索引。由於索引儲存的順序性，其實可以將這兩個索引進行合併，如果我們先建立(userid, xxx)的索引，然後再新增userid的外來鍵，mysql會自動使用前面建立索引。
索引是否越多越好呢？
顯然不是，因為索引是對原表的資料冗餘，那麼他就必須要保證資料的一致性。如果原表增加了一條資料，索引也需要增加。如果原表修改了一條資料，那麼對應的索引可能也要修改內容以及排序的位置，這可能會造成頁分裂或頁合併。一個表如果索引過多，那麼維護索引與表的資料一致性也是不小的壓力。通常建議在滿足需求前提下，索引越少越好。