第32期：索引設計（索引設計詳細規範）

透過前面一些關於索引設計的相關介紹與示例，相信大家已經對索引設計這塊有了一些零碎的認識，那本篇來做下總結，給出一個索引設計的詳細規範。

索引命名規範：

單值索引，建議以 idx_ 為開頭，字母全部小寫。

例如：alter table t1 add key idx_r1（r1）；

組合索引，建議以 dx_multi_ 開頭，字母全部小寫。

例如：alter table t1 add key idx_multi_1（r1，r2，r3） ；

唯一索引，建議以 udx_ 為開頭，字母全部小寫；如果是多值唯一索引，則命名方式類似 udx_multi_1 等。

例如：alter table t1 add unique key udx_f1（r1）；或者alter table t1 add key udx_multi_1（r1，r2，r3）；

全文索引，建議以 ft_ 開頭，字母全部小寫，並且建議預設用 ngram 外掛。

例如：alter table t1 add fulltext ft_r1（r1） with parser ngram；

字首索引，建議以 idx_ 開頭，以 _prefix 結尾。

例如： alter table t1 add key idx_r1_prefix（r1（10））；

函式索引，建議以 idx_func_ 開頭，字母全部小寫。

例如： alter table t1 add key idx_func_r1（（mod（r1，4）））；

索引列選擇規範：

索引列的欄位型別：

索引本身有有序的，儘量選擇整型列做索引，如果避免不了字串做索引，可以選擇對字元型別做 HASH ，再基於 HASH 結果做索引；

主鍵列資料型別最好也是整型，避免對不規則的字串建立主鍵（比如在探討主鍵時提到的 UUID ，由於 INNODB 表即索引，所以應該避免掉。並不是由於 UUID 非有序，而是因為單個 UUID 太大）

索引列的字元長度：

儘量控制索引的字元長度。比如欄位 r1 varchar（200）， 如果僅前10個字元頻繁變化，後面的字元接近於靜態資料，可以基於前10個字元建立字首索引。大物件欄位僅支援建立字首索引。

alter table t1 add key idx_r1_prefix（r1（10））；

索引列的可選擇性：

基於可選擇性較高的欄位建立索引，對可選擇性較低的欄位建立索引可能適得其反；這條規則也適用於組合索引，多個欄位一起建立組合索引，按照索引鍵選擇性高低來決定組合索引的順序。舉個例子：

表 t1 有1000條記錄，欄位 r1 有800條不同的值，欄位 r2 有500條不同的值，欄位 r3 有100條不同的值。那組合索引的順序應該是（r1，r2，r3）。

單張表索引個數控制：

雖然 MySQL 單表最多支援64個索引，但是關於具體索引個數，最好越少越好。比如可以根據表結構和業務訪問模型來分別對待，不同的表可以有不同數量的索引。

頻繁更新的列不建議有索引：

不建議對頻繁更新的列建立索引。對這樣的列建立索引，在資料頻繁更新時，會同時更新此列對應的索引鍵值分佈。比如貨物的庫存數量屬性，會經常更新，應該避免建立索引，一般資料訪問請求中，商品 ID 是必要過濾條件，庫存數量只做展示即可。

函式索引：

非必要條件不建立函式索引，除非基於這列必須做函式檢索。

比如列 r1 ，基於 mod（r1，4） 的運算比基於 r1 的運算要頻繁的多，則有必要針對列r1建立一個函式索引。

重複索引：

索引都是基於最左列掃描找到入口，再繼續遍歷，非必要條件下，避免以最左列開始的多個索引同時存在。

比如列 r1 被三個索引包含，可能會想到如下語句：

select * from t1 where r1 = 2 and r2 = 2 and r3 = 2； select * from t1 where r1 = 2； select * from t1 where r1 = 2 and r2 = 2；

如果上面這三條語句頻繁執行，則只需建立一個以 r1 開頭的組合索引即可，即索引 idx_multi_1 。

例如： alter table t1 add key idx_multi_1（r1，r2，r3）；

但是如果下面語句也頻繁執行：

select * from t1 where r1 = 1 and r4 = 1 and r5 = 1； select * from t1 where r1 = 1 and r4 = 1；

則需要再建一個以欄位 r1 開頭的，以欄位 r4，r5 結尾的組合索引。

例如： alter table t1 add key idx_multi_2（r1，r4，r5）；

再比說過濾條件（r1，r2，r3） 比過濾條件 （r1，r4，r5）的使用頻率高，則可以考慮合併這兩個組合索引為一個大的組合索引。（r1，r2，r3，r4，r5）。

例如：alter table t1 drop key idx_multi_1， drop key idx_multi_2， add key idx_multi_1（r1，r2，r3，r4，r5）；

基於表查詢語句反向索引建立：

例如以下語句：

select * from t1 where r1 = 2；select * from t1 where r2 = 2；select * from t1 where r3 = 2；select * from t1 where r4 = 2；select * from t1 where r1 = 2 or r2 = 2 or r3 = 2 or r4 = 2；select * from t1 where r2 = 2 or r4 = 2；select * from t1 where r2 = 2 and r3 = 2；select * from t1 where r3 = 2 or r4 = 2；select * from t1 where r1 = 2 and r2 = 2 and r3 = 2 and r4 = 2；

上面這些 SQL 過濾欄位侷限在欄位 r1，r2，r3，r4 四個，而且不固定，這時候可以單獨每個列建立索引，由 MySQL 來決定用哪個索引或者說多個索引一起用。

例如：alter table t1 add key idx_r1（r1），add key idx_r2（r2）， add key idx_r3 （r3）， add key idx_r4（r4）；

避免基於小表加索引：

比如表 t1 記錄數可能永遠只有1000行，那除了主鍵外，完全沒有必要建立其他索引。

例如： select * from t1 where r1 = 10；

上面語句非常簡單，走索引速度可能還沒有順序掃表來的快。

索引順序的指定：

一般場景若非強制需求，預設按照索引鍵值升序，除非查詢過濾條件指定排序。

比如查詢語句：select r1，r2，r3 from t1 order by r1 desc， r2 asc， r3 desc

如果按照預設方式建立索引，針對這條 SQL 序開銷又很大。此時可以指定欄位順序建立索引。

例如：alter table t1 add key idx_multi_sort_1（r1 desc， r2 asc， r3 desc）；

關於索引的設計這塊，到本章就已完結，如果在讀完索引設計相關篇章後還有疑問的，歡迎私信或者留言。

關於 MySQL 的技術內容，你們還有什麼想知道的嗎？趕緊留言告訴小編吧！