小北說在前面： 在一線互聯網企業種，如網易、美團、位元組、如阿裡、滴滴、極兔、有贊、希音、百度、美團等大廠，資料庫的面試題，一直是核心和重點的提問點，比如前段時間有位小伙伴面試位元組，就遇到了下麵這道面試題： 索引的設計規範，你知道那些？ 小伙伴雖然用過索引，但是索引的設計規範忘記得一干二凈，回答也是朦 ...
小北說在前面：
在一線互聯網企業種，如網易、美團、位元組、如阿裡、滴滴、極兔、有贊、希音、百度、美團等大廠，資料庫的面試題，一直是核心和重點的提問點，比如前段時間有位小伙伴面試位元組，就遇到了下麵這道面試題：
索引的設計規範，你知道那些？
小伙伴雖然用過索引，但是索引的設計規範忘記得一干二凈，回答也是朦朦朧朧、支支吾吾， 當然，面試也就掛了。
在這裡，小北給大家做一下系統化、體系化的梳理，按照下麵的套路去回答，可以充分展示一下大家扎實的 “技術功底”，讓面試官眼前一亮。
這個題目以及參考答案，也會收錄入咱們的 《[小北Java面試寶典PDF][Java_PDF]》V154版本，供後面的小伙伴參考，提升大家的 3高 架構、設計、開發水平。
本文目錄：
-1、索引原理
-2、索引的分類
-3、索引的優缺點
-4、參考的索引設計規範
-4.1 索引命名規範
-4.2 儘量選擇整型列做索引
-4.3 優先建立唯一性索引
-4.4 為經常需要排序、分組和聯合操作的欄位建立索引
-4.5 為常作為查詢條件的欄位建立索引
-4.6 限制索引的數目
-4.7 儘量使用數據量少的索引
-4.9 儘量使用首碼來索引
-4.10 刪除不再使用或者很少使用的索引
-4.11 最左首碼匹配原則，非常重要的原則
-4.12 儘量選擇區分度高的列作為索引
-4.13 索引列不能參與計算，保持列“乾凈”
-4.14 儘量的擴展索引，不要新建索引
-4.15 考慮建立聯合索引來提高查詢效率
-參考文獻
-說在最後：有問題可以找老架構取經
-部分歷史案例
1、索引原理：
索引是幫助MySQL高效獲取數據的數據結構，註意，是幫助高性能的獲取數據
索引好比是一本書的目錄，可以直接根據頁碼找到對應的內容，目的就是為了加快資料庫的查詢速度。
- 索引是對資料庫表中一列或多列的值進行排序的一種結構，使用索引可快速訪問資料庫表中的特定信息。
- 索引是一種能幫助mysql提高了查詢效率的數據結構：索引數據結構。
索引的存儲原理大致可以概括為一句話：以空間換時間。
資料庫在未添加索引, 進行查詢的時候預設是進行全文搜索，也就是說有多少數據就進行多少次查詢，然後找到相應的數據就把它們放到結果集中，直到全文掃描完畢。
資料庫添加了索引之後，通過索引快速找到數據在磁碟上的位置，可以快速地讀取數據，而不用從頭開始全表掃描。
一般來說索引本身也很大，不可能全部存儲在記憶體中，因此索引往往是存儲在磁碟上的文件中的（可能存儲在單獨的索引文件中，也可能和數據一起存儲在數據文件中）。
2、索引的分類：
主鍵索引：primary key
- 設定為主鍵後，資料庫自動建立索引，InnoDB為聚簇索引，主鍵索引列值不能為空（Null）。
唯一索引：
- 索引列的值必須唯一，但允許有空值（Null），但只允許有一個空值（Null）。
複合索引：
- 一個索引可以包含多個列，多個列共同構成一個複合索引。
全文索引：
- Full Text（MySQL5.7之前，只有MYISAM存儲引擎引擎支持全文索引）。
- 全文索引類型為FULLTEXT，在定義索引的列上支持值的全文查找允許在這些索引列中插入重覆值和空值。全文索引可以在Char、VarChar 上創建。
空間索引：
- MySQL在5.7之後的版本支持了空間索引，而且支持OpenGIS幾何數據模型，MySQL在空間索引這方面遵循OpenGIS幾何數據模型規則。
首碼索引：
- 在文本類型為char、varchar、text類列上創建索引時，可以指定索引列的長度，但是數值類型不能指定。
3、索引的優缺點：
優點：
- 大大提高數據查詢速度。
- 可以提高數據檢索的效率，降低資料庫的IO成本，類似於書的目錄。
- 通過索引列對數據進行排序，降低數據的排序成本降低了CPU的消耗。
- 被索引的列會自動進行排序，包括【單例索引】和【組合索引】，只是組合索引的排序需要複雜一些。
- 如果按照索引列的順序進行排序，對order 不用語句來說，效率就會提高很多。
缺點：
- 索引會占據磁碟空間。
- 索引雖然會提高查詢效率，但是會降低更新表的效率。比如每次對錶進行增刪改查操作，MySQL不僅要保存數據，還有保存或者更新對應的索引文件。
- 維護索引需要消耗資料庫資源。
綜合索引的優缺點：
- 資料庫表中不是索引越多越好，而是僅為那些常用的搜索欄位建立索引效果最佳!
4、參考的索引設計規範：
每個公司，都有自己的 設計規範，
尼恩這裡的梳理的設計規範，可以作為大家參考。當然，如果面試的時候能講到這個水平，已經很牛掰了。
4.1 索引命名規範
單值索引，建議以 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)));
4.2 儘量選擇整型列做索引
索引本身有有序的，儘量選擇整型列做索引，
所以，儘量不用uuid，而是使用雪花id，頁段id，等整數id去建立索引 。
雪花id，頁段id的源碼和原理，請參見尼恩的《視頻第32章：超高併發、超高可用1000W級 ID組件 架構與實操》
如果避免不了，只有字元串做索引，可以選擇對字元類型做 HASH ，再基於 HASH 結果做索引；
主鍵列數據類型最好也是整型，
避免對不規則的字元串建立主鍵（由於 INNODB 表即索引，所以應該避免掉。不僅僅 UUID 非有序，而是因為單個 UUID 太大）
4.3 優先建立唯一性索引
唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。
例如，學生表中學號是具有唯一性的欄位。為該欄位建立唯一性索引可以很快的確定某個學生的信息。
如果使用姓名的話，可能存在同名現象，從而降低查詢速度。
4.4 為經常需要排序、分組和聯合操作的欄位建立索引
經常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的欄位，排序操作會浪費很多時間。
如果為其建立索引，可以有效地避免排序操作。
4.5 為常作為查詢條件的欄位建立索引
如果某個欄位經常用來做查詢條件，那麼該欄位的查詢速度會影響整個表的查詢速度。
因此，為這樣的欄位建立索引，可以提高整個表的查詢速度。
4.6 限制索引的數目
索引的數目不是越多越好。
每個索引都需要占用磁碟空間，索引越多，需要的磁碟空間就越大。
修改表時，對索引的重構和更新很麻煩。
越多的索引，會使更新表變得很浪費時間。
4.7 儘量使用數據量少的索引
如果索引的值很長，那麼查詢的速度會受到影響。
例如，對一個CHAR(100)類型的欄位進行全文檢索需要的時間肯定要比對CHAR(10)類型的欄位需要的時間要多。
4.9 儘量使用首碼來索引
如果索引欄位的值很長，最好使用值的首碼來索引。
例如，TEXT和BLOG類型的欄位，進行全文檢索會很浪費時間。
如果只檢索欄位的前面的若幹個字元，這樣可以提高檢索速度。
4.10 刪除不再使用或者很少使用的索引
表中的數據被大量更新，或者數據的使用方式被改變後，原有的一些索引可能不再需要。
資料庫管理員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。
4.11 最左首碼匹配原則，非常重要的原則
mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，
比如a1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
註意：=和in可以亂序。
比如a= 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，
mysql的查詢優化器會幫你優化成索引可以識別的形式
4.12 儘量選擇區分度高的列作為索引
區分度的公式是count(distinct col)/count(*)，表示欄位不重覆的比例，比例越大我們掃描的記錄數越少，
唯一鍵的區分度是1，而一些狀態、性別欄位可能在大數據面前區分度就 是0，
那可能有人會問，這個比例有什麼經驗值嗎？
使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條 記錄
4.13 索引列不能參與計算，保持列“乾凈”
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的欄位值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本 太大。
所以語句應該寫成 create_time = unix_timestamp(’2014-05-29’);
4.14 儘量的擴展索引，不要新建索引
比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可
4.15 考慮建立聯合索引來提高查詢效率
當單個索引欄位查詢數據很多，區分度都不是很大時，則需要考慮建立聯合索引來提高查詢效率
註意：選擇索引的最終目的是為了使查詢的速度變快。
說在最後：有問題可以找小北取經
mysql相關的面試題，是非常常見的面試題。
以上的內容，如果大家能對答如流，如數家珍，基本上 面試官會被你 震驚到、吸引到。
最終，讓面試官愛到 “不能自已、口水直流”。offer， 也就來了。
如有收穫，請點擊底部的"在看"和"贊"，謝謝
本文由博客一文多發平臺 OpenWrite 發佈！
