摘要：使用索引也很簡單，然而， 會使用索引是一回事， 而深入理解索引原理又能恰到好處使用索引又是另一回事。
本文分享自華為雲社群《索引到底能提升多少查詢效率？何時該使用索引？一文快速搞懂資料庫索引及合理使用它》，作者： 曲鳥。
一、前言
無論是面試、還是日常工作中，或多或少都會使用或者聽到別人談論索引這個技術。
然而很大一部份程式設計師對索引的瞭解僅限於到“加索引能使查詢變快”這個概念為止。
使用索引也很簡單，然而， 會使用索引是一回事， 而深入理解索引原理又能恰到好處使用索引又是另一回事。
這已經是兩個相差甚遠的技術層級了。
二、千萬級資料表索引和無索引查詢效率對比
現在有一個學生表student，有1000萬條資料
未加索引，查詢class_id=2的學生資訊的耗時：SELECT * FROM student WHERE class_id=2 花費了3.357秒
加上索引，查詢class_id=2的學生資訊的耗時：SELECT * FROM student WHERE class_id=2 花費了0.017秒
1000萬條資料下，兩個查詢的效能差了近200倍！！
這個差距是特別大的！ 難怪需要加索引！！！
三、什麼是索引
網上很多講解索引的文章對索引的描述是這樣的：
索引就像書的目錄， 通過書的目錄就可以準確的定位到書籍的具體的內容。
這句話概述的非常正確！
但說了跟沒說一樣，懂的人自然懂！不懂的人感覺懂了，但還是一臉蒙的狀態！
其實想要理解索引原理，必須清楚一種資料結構：
平衡樹」(非二叉)，也就是b tree或者 b+ tree
當然， 有的資料庫也使用雜湊桶作用索引的資料結構 ， 然而， 主流的RDBMS都是把平衡樹當做資料表預設的索引資料結構的。
我們平時建表的時候都會為表加上主鍵， 在某些關聯式資料庫中， 如果建表時不指定主鍵，資料庫會拒絕建表的語句執行。
事實上， 一個加了主鍵的表，並不能被稱之為“表”。一個沒加主鍵的表，它的資料無序的放置在磁碟儲存器上，一行一行的排列的很整齊。如果給表上了主鍵，那麼表在磁碟上的儲存結構就由整齊排列的結構轉變成了樹狀結構，也就是上面說的“平衡樹”結構，換句話說，就是整個表就變成了一個索引。
沒錯， 再說一遍， 整個表變成了一個索引！
也就是所謂的“聚集索引”。 這就是為什麼一個表只能有一個主鍵， 一個表只能有一個“聚集索引”，因為主鍵的作用就是把“表”的資料格式轉換成“樹（索引）”的格式。
未加索引時，之前執行的查詢sql會讓資料庫系統逐行的遍歷整張表，對於每一行都要檢查其class_id欄位是否等於“2”。因為我們要查詢所有class_id為“2”的員工，所以當我們發現了一條class_id是“2”的記錄後，並不能停止繼續查詢，因為可能還有class_id等於“2”的其他記錄。
這就意味著，對於表中的千萬條記錄，資料庫每一條都要檢查。這就是所謂的“全表掃描”（ full table scan）
而加上索引的最大作用就是加快查詢速度，它能從根本上減少需要掃表的記錄/行的數量。
四、Mysql中的索引
在MySQL中， 索引有兩種分類方式：邏輯分類和物理分類。
按照邏輯分類，索引可分為：
主鍵索引：一張表只能有一個主鍵索引，不允許重複、不允許為 NULL；
唯一索引：資料列不允許重複，允許為 NULL 值，一張表可有多個唯一索引，但是一個唯一索引只能包含一列，比如身份證號碼、卡號等都可以作為唯一索引；
普通索引：一張表可以建立多個普通索引，一個普通索引可以包含多個欄位，允許資料重複，允許 NULL 值插入；
全文索引：讓搜尋關鍵詞更高效的一種索引。
按照物理分類，索引可分為：
聚集索引：一般是表中的主鍵索引，如果表中沒有顯示指定主鍵，則會選擇表中的第一個不允許為 NULL 的唯一索引，如果還是沒有的話，就採用 Innodb 儲存引擎為每行資料內建的 6 位元組 ROWID 作為聚集索引。每張表只有一個聚集索引，因為聚集索引的鍵值的邏輯順序決定了表中相應行的物理順序。聚集索引在精確查詢和範圍查詢方面有良好的效能表現（相比於普通索引和全表掃描），聚集索引就顯得彌足珍貴，聚集索引選擇還是要慎重的（一般不會讓沒有語義的自增 id 充當聚集索引）；
非聚集索引：該索引中索引的邏輯順序與磁碟上行的物理儲存順序不同（非主鍵的那一列），一個表中可以擁有多個非聚集索引。
在目前用的最多的mysql的InnoDB儲存引擎中，是使用B+Tree索引方法來進行索引建立的。
B+樹索引是B+樹在資料庫中的一種實現，是最常見也是資料庫中使用最為頻繁的一種索引。
B+樹中的B代表平衡（balance），而不是二叉（binary），因為B+樹是從最早的平衡二叉樹演化而來的。先了解二叉查詢樹、平衡二叉樹（AVLTree）和平衡多路查詢樹（B-Tree），B+樹即由這些樹逐步優化而來。
具體的講解可參考：https://www.cnblogs.com/wuzhenzhao/p/10341114.html 該部落格。
五、索引的優缺點
優點：
1、索引能夠提高資料檢索的效率，降低資料庫的IO成本。
2、通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性，建立唯一索引
3、在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間
4、加速兩個表之間的連線，一般是在外來鍵上建立索引
缺點：
1、建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加
2、索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大
3、當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，降低了資料的維護速度
六、索引何時應該使用
需建立索引的情況：
1.主鍵，自動建立唯一索引
2.頻繁作為查詢的條件的欄位
3.查詢中與其他表關聯的欄位存在外來鍵關係
4.查詢中排序的欄位，排序欄位若通過索引去訪問將大大提高排序的速度
5.查詢中統計或者分組欄位
避免建立索引的情況：
1.資料唯一性差的欄位不要使用索引
比如性別，只有兩種可能資料。意味著索引的二叉樹級別少，多是平級。這樣的二叉樹查詢無異於全表掃描。
2.頻繁更新的欄位不要使用索引
比如登入次數，頻繁變化導致索引也頻繁變化，增大資料庫工作量，降低效率。
3.欄位不在where語句出現時不要新增索引
只有在where語句出現，mysql才會去使用索引
4.資料量少的表不要使用索引
使用了改善也不大
七、哪些sql能命中索引
1.前導模糊查詢不能使用索引， 如name like ‘%濤’
2、Union、in、or可以命中索引，建議使用in。
3、負條件查詢不能使用索引，可以優化為in查詢，其中負條件有！=、<>、not in、not exists、not like等
4、聯合索引最左字首原則，又叫最左側查詢，如果在（a，b，c）三個欄位上建立聯合索引，那麼它能夠加快a|（a，b）|（a，b，c）三組的查詢速度。
5、建立聯合查詢時，區分度最高的欄位在最左邊
6、如果建立了（a，b）聯合索引，就不必再單獨建立a索引。同理，如果建立了（a，b，c）索引就不必再建立a，（a，b）索引
7、存在非等號和等號混合判斷條件時，在建索引時，要把等號條件的列前置
8、範圍列可以用到索引，但是範圍列後面的列無法用到索引。
索引最多用於一個範圍列，如果查詢條件中有兩個範圍列則無法全用到索引。範圍條件有：<、<=、>、>=、between等。
9、把計算放到業務層而不是資料庫層。在欄位上計算不能命中索引，
10、強制型別轉換會全表掃描，
如果phone欄位是varcher型別，則下面的SQL不能命中索引。Select * fromuser where phone=13800001234
11、更新十分頻繁、資料區分度不高的欄位上不宜建立索引。
更新會變更B+樹，更新頻繁的欄位建立索引會大大降低資料庫效能。
“性別”這種區分度不太大的屬性，建立索引是沒有什麼意義的，不能有效過濾資料，效能與全表掃描類似。
一般區分度在80%以上就可以建立索引。區分度可以使用count（distinct（列名））/count（*）來計算。
12、利用覆蓋索引來進行查詢操作，避免回表。
被查詢的列，資料能從索引中取得，而不是通過定位符row-locator再到row上獲取，即“被查詢列要被所建的索引覆蓋”，這能夠加速度查詢。
13、建立索引的列不能為null，使用not null約束及預設值
14、利用延遲關聯或者子查詢優化超多分頁場景，
MySQL並不是跳過offset行，而是取offset+N行，然後放棄前offset行，返回N行，那當offset特別大的時候，效率非常低下，要麼控制返回的總數，要麼對超過特定閾值的頁進行SQL改寫。
15、業務上唯一特性的欄位，即使是多個欄位的組合，也必須建成唯一索引。
16、超過三個表最好不要用join，需要join的欄位，資料型別必須一致，多表關聯查詢時，保證被關聯的欄位需要有索引。
17、如果明確知道查詢結果只要一條，limit 1能夠提高效率，比如驗證登入的時候。
18、Select語句務必指明欄位名稱
19、如果排序欄位沒有用到索引，就儘量少排序
20、儘量用union all 代替 union。Union需要將集合合併後在進行唯一性過濾操作，這會涉及到排序，大量的cpu運算，加大資源消耗及延遲，當然，使用union all的前提條件是兩個結果集沒有重複資料。
八、總結
索引是非常重要的技術！
但每建立一個索引，實際上都需要在硬碟上開闢一塊空間用於儲存這個索引所需要的資料結構（雖然表述不太準確但是是這個意思），因此不建議對太長的欄位建立索引。
而且建立的索引並不是越多越好，因為索引雖然能夠提高查詢效率，但是會大大得影響插入、刪除和修改的效率，因為每一次資料的更新都會牽涉到對索引的修改。
綜上所述，往往在對於大量資料的插入的情況的時候，我們需要先刪除掉資料表的索引，等插入完畢後重新建立索引，這樣才能最大限度地保證資料庫的效率！