Mysql-高效能索引

索引一種資料結構，其目的是為了更快的查詢資料，在資料量很大的表中，建立良好的索引能夠提升極大的效能。

磁碟io與預讀

因為資料庫儲存資料量大，是不可能儲存在記憶體中以供查詢的，所以對於資料的查詢必然會跟磁碟打交道，所以只有瞭解了磁碟io和預讀的基本知識，我們才能真正的理解索引的原理。

磁碟讀取資料靠的是機械運動，每次讀取資料花費的時間可 以分為尋道時間、旋轉延遲、傳輸時間三個部分，尋道時間指的是磁臂移動到指定磁軌所需要的時間，主流磁碟一般在5ms以下；旋轉延遲就是我們經常聽說的磁 盤轉速，比如一個磁碟7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120/2 = 4.17ms；傳輸時間指的是從磁碟讀出或將資料寫入磁碟的時間，一般在零點幾毫秒，相對於前兩個時間可以忽略不計。那麼訪問一次磁碟的時間，即一次磁碟 IO的時間約等於5+4.17 = 9ms左右，聽起來還挺不錯的，但要知道一臺500MIPS的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行40萬條指令，資料庫動輒十萬百萬乃至千萬級數 據，每次9毫秒的時間，顯然是個災難。考 慮到磁碟IO是非常高昂的操作，計算機作業系統做了一些優化，當一次IO時，不光把當前磁碟地址的資料，而是把相鄰的資料也都讀取到記憶體緩衝區內，因為局 部預讀性原理告訴我們，當計算機訪問一個地址的資料的時候，與其相鄰的資料也會很快被訪問到。每一次IO讀取的資料我們稱之為一頁(page)。具體一頁 有多大資料跟作業系統有關，一般為4k或8k，也就是我們讀取一頁內的資料時候，實際上才發生了一次IO。

索引的資料結構

之前說到了磁碟讀取資料的方式，那麼我們的索引是如何配合這個方式更快的搜尋到資料呢？首先，我們要了解索引的資料結構。我們這裡講到的索引B+TREE的索引，因為這個索引我們最常用，實際上索引還有雜湊索引，空間資料索引，全文索引。

首先我們來理解B+TREE的資料結構： B+Tree是一種多路搜尋樹（並不是二叉的）：

1. 定義任意非葉子結點最多隻有M個兒子；且M>2；
2. 根結點的兒子數為[2, M]；
3. 除根結點以外的非葉子結點的兒子數為[M/2, M]；
4. 每個結點存放至少M/2-1（取上整）和至多M-1個關鍵字；（至少2個關鍵字）
5. 非葉子結點的關鍵字個數=指向兒子的指標個數-1；
6. 非葉子結點的關鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7. 非葉子結點的指標：P[1], P[2], …, P[M]；其中P[1]指向關鍵字小於K[1]的子樹，P[M]指向關鍵字大於等於K[M-1]的子樹，其它P[i]指向關鍵字屬於[K[i], K[i+1])的子樹；
8. 所有葉子結點位於同一層；
9. 為所有葉子結點增加一個鏈指標；
10. 所有關鍵字都在葉子結點出現； 如圖，是一個M為3的B-TREE

    

B+的特性：

1. 所有關鍵字都出現在葉子結點的連結串列中（稠密索引），且連結串列中的關鍵字恰好是有序的；
2. 不可能在非葉子結點命中；
3. 非葉子結點相當於是葉子結點的索引（稀疏索引），葉子結點相當於是儲存（關鍵字）資料的資料層；
4. 更適合檔案索引系統；

B+TREE索引效能分析：

B+TREE的深度最多是O(log[M/2]N)，在路徑上的每個節點需要用O(logM)s時間複雜度來確定是哪個分支（使用二分查詢），inset和delete可能需要O(M)的工作量來調整該節點上的所有資訊，所以insert和delete的執行時間最壞情況是O(Mlog[M]N),而每次的查詢只需要花費O(logN)。從剛剛的時間複雜度可以看出當在記憶體中查詢時，Mzuihaode選擇是3或者4，再增大時速度就會增加。但是我們的資料儲存是在磁碟中的，相比讀取一個儲存器所花費的時間，M增大所增加的時間花費不值一提。此時M的值選擇為使得一個內部節點能夠裝入一個磁碟區塊的最大值，所以M取值範圍為[32,256],這樣當一片樹葉上元素是滿的，而且樹葉是滿的那麼硬碟上一個區塊就被裝滿了。這樣意味著，一個記錄總可以在很少的磁碟訪問中被找到，因為此時B樹深度只有2或3，而根可以直接載入到記憶體中，所以整個訪問速度就會很快。

所以我們再來看上圖： 如果要查詢資料項30，那麼首先會把磁碟塊1由磁碟載入到記憶體，此時發生一次IO，在記憶體中用二分查詢確 定29在28和65之間，鎖定磁碟塊1的P2指標，記憶體時間因為非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指標的磁碟地址把磁碟塊3由磁 盤載入到記憶體，發生第二次IO，30在28和35之間，鎖定磁碟塊3的P2指標，通過指標載入磁碟塊8到記憶體，發生第三次IO，同時記憶體中做二分查詢找到 30，結束查詢，總計三次IO。真實的情況是，3層的b+樹可以表示上百萬的資料，如果上百萬的資料查詢只需要三次IO，效能提高將是巨大的，如果沒有索引，每個資料項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。

高效能的索引策略

獨立的列

有些查詢不當的使用索引，使得Mysql無法使用已有的索引。比如查詢中列不是獨立的，則Mysql不會使用索引。獨立的列指的是：索引不能是表示式的一部分，更不能是函式的引數，例如：

select app_id from app where app_id + 1 = 5;
複製程式碼

MySQL無法解析app_id + 1這個表示式，所以無法使用索引。

字首索引和索引選擇性

有時候索引的欄位特別的長，這會讓索引變得又大又慢。這種情況可以通過只取出欄位前面幾個字元來做索引，這樣可以節約索引空間，從而提高索引的效率，但是這樣會減少索引的選擇性。索引的選擇性，指的是不重複的索引值（基數）跟資料表的總數的比值。索引選擇性越高查詢的效率就越快，因為這樣索引能幫助Mysql查詢時過濾掉更多的行。比如主鍵和唯一索引，這種時候效能最好。所以在選擇索引長度時要同時考慮索引選擇性才能達到效能最優化。

多列索引

大多數對於索引理解不夠的，所以容易犯以下兩個：

1. 為很多個列建立獨立索引。在多個列上建立單獨索引並不能提高Mysql的查詢效能。5.0以後的Mysql引入了“索引合併”的策略，這樣可以多個單列索引就行掃描，並將結果進行合併。這種演算法有三種變形： OR條件聯合，AND條件相交。這種情況下，合併結果會耗用大量的CPU，而且這個優化過程不計入“查詢成本”中。所以這些成本會被低估，有時候效率甚至低於全盤掃描，而且容易導致優化的時候注意不到這個點。
2. 建立多列索引的順序有誤。查詢的時候沒有按照索引的順序來查詢，也會導致Mysql無法使用索引。在建立多列索引的時候有一些有用的原則：在不考慮排序和分組的情況下，將選擇性最高的列放在前面。但是很多時候這樣用也未必是好的，還是要根據具體的情況來判斷。

聚簇索引

聚簇索引並不是一個單獨的索引形式，而是Mysql在B+TREE索引上的資料儲存形式。InnoDB的聚簇索引實現就是在統一結構裡面儲存了B+TREE索引和資料行如圖：

聚簇索引有時候對效能很有幫助，但有時候也對效能造成嚴重的問題。下面我們用幾張圖來分辨下非聚簇索引的表和聚簇索引的表的區別：

非聚簇索引表的資料如上圖

非聚簇索引表的主鍵索引圖

聚簇索引的主鍵索引圖

從上面幾張圖可以看出，非聚簇索引表的主鍵索引跟普通的索引沒有區別，他直接就是索引裡有個指向資料所在指標的形式，但是聚簇索引表的主鍵索引“就是”一張表，所以不需要非聚簇索引表那樣資料的獨立行儲存。我們直接來看兩者的對比圖：

瞭解了他們的區別我們接著來討論聚簇索引的優點：

1. 可以把相關資料儲存在一起，查詢時只需在磁碟讀取少數資料頁就能獲得所有的所需資料。
2. 資料訪問速度快，因為資料和索引在一起所以查詢起來很快。
3. 使用覆蓋索引掃描的查詢可以直接使用葉節點的主鍵值。二級索引（輔助索引）使用主鍵作為"指標" 而不是使用地址值作為指標的好處是，減少了當出現行移動或者資料頁分裂時輔助索引的維護工作，使用主鍵值當作指標會讓輔助索引佔用更多的空間，換來的好處是InnoDB在移動行時無須更新輔助索引中的這個"指標"。也就是說行的位置會隨著資料庫裡資料的修改而發生變化（後面缺點處會講到B+樹節點分裂以及頁分裂），使用聚簇索引就可以保證不管這個主鍵B+樹的節點如何變化，輔助索引樹都不受影響。

下面我們來看看聚簇索引的缺點：

1. 聚簇索引在資料都放在記憶體中的資料毫無優勢。
2. 插入速度在按照主鍵順序插入的時候影響不大，但不按照順序加入的時候，速度會受到影響而且插入後要使用optimize table優化一下表，能更新索引統計資料並釋放成簇索引中的未使用的空間。
3. 更新聚簇索引的成本很高，因為InnoDB會強制將每個被更新的行移動到新的位置上。
4. 基於聚簇索引的表插入新行，或者主鍵被更新導致需要移動行時，可能面臨“頁分裂的問題”。當該行插入到一個某個已經滿了的頁的時候，儲存引擎會將頁分裂成兩個頁面來容納該行，這樣的頁分裂操作會導致表佔用更多空間。而且這樣會造成資料儲存不連續，行比較稀疏的問題，也會導致全盤掃描變慢。頁分裂還會造成大量資料的移動，一次插入至少修改到3個頁。而順序插入的時候，很少遇到需要大量修改頁的情況，最大的效能瓶頸就在自動增減主鍵的時候鎖的開銷。如圖：

   

   順序新增

   

   插入無序值的時候
5. 二級索引（輔助索引）可能比想象的要大，因為二級索引葉子節點包含了引用行主鍵的列。而且二級索引需要兩次查詢。

覆蓋索引

覆蓋索引指的是包含了一個查詢所有需要查詢欄位的值。覆蓋索引是一個非常有用的工具，能夠極大的提升效率，因為索引的葉子節點已經包含了所有需要的資料，無需再去讀取資料行。其優點如下：

1. 索引條目比起資料行要小得多，所以如果只需要讀取索引，那MySQL就能極大的減少資料訪問量。
2. 因為索引是按照列值順序儲存的，所以對於IO密集型的範圍查詢，只查詢索引就會比去硬碟中隨機查詢每一行資料快得多。
3. 資料庫引擎（如MyISAM）在記憶體中只快取索引，資料快取依賴於作業系統快取，因此訪問資料需要系統呼叫，這個會造成嚴重的效能問題。
4. 上面說的聚簇索引，覆蓋索引的時候就特別有用了。

因為覆蓋索引是索引中儲存了列的值，所以覆蓋索引的適用範圍僅適用於B+TREE的時候.

索引掃描來做排序

掃描索引本身很快，因為只需要一條索引記錄移動到下一條索引記錄就行了。但是如果索引不能覆蓋查詢的所有列，那就只能每一條索引記錄都要去查詢一次對應的行。這基本上都是隨機IO,所以按索引順序讀取資料的速度反而要比順序的全表掃描慢，尤其是IO密集型的工作負載時。 只有當索引的列順序和order by的字句順序完全一致，並且所有列的排序方向（正序倒序）都一樣時，Mysql才能使用索引來對結果做排序。當然如果最左字首在where條件中已經是一個常量了，可以不用滿足最左字首的order by子句。

索引和鎖

索引可以讓查詢鎖定更少的行。比如之前我們遇到過得for update語句如果用了主鍵的索引，那麼就只鎖定一行，而其他的就會鎖表。還有很多情況查詢的時候只鎖定索引查出來的行，這樣的話能減少很多鎖的開銷。還有個InnoDB的細節需要注意： InnoDB在二級索引（輔助索引）上用的是共享鎖（讀鎖），但是訪問主鍵索引就用的是排他鎖（寫鎖）（其實也就是之前我們工作中遇到的那個用主鍵的索引就是行鎖，但是其他索引就是表鎖）。這種情況就沒法使用之前講到的覆蓋索引（二級索引訪問主鍵索引），並且使用 for update 比share in share mode或非鎖定查詢要慢得多。

一些微小的建議

資料庫的優化是一個非常重要的工作，很多時候不能犯教條主義錯誤，最主要的方式還是要通過自己操作來驗證到底該如何優化。很可能一次優化在100M的資料量的時候起作用了，但是1G的時候又會變成慢查詢，這種情況就要去思考如何才能避免再出這樣的問題。資料庫可以儲存一些歷史資料作為記錄，但是大多數情況我們需要的是最近的資料或者是少數的幾個熱資料，這種情況下就需要用快取記憶體的方式來完成這方面的工作，而不是一味的只用資料庫。這才設計構思的時候很重要。還有索引不是萬能的，千萬不要亂建索引，有時候還會造成速度變得更低，而且還浪費了空間，再建立索引的時候也要好好思考一下這個索引的必要性和用處。另外優化慢查詢有時候也未必非要加索引，很多時候通過一些語句的構造也能實現優化的目的。以上內容主要來自我之前閱讀的一本書籍《高效能MySQL》，配合一些演算法知識再加上我自身的一些經驗，寫在這裡只是起到拋磚引玉的作用，資料庫優化的路還很長，坑還很多，希望與大家一起學習，共同進步。