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

磁盤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是一種多路搜索樹（并不是二叉的）：

定義任意非葉子結點最多只有M個兒子；且M>2；

根結點的兒子數為[2, M]；

除根結點以外的非葉子結點的兒子數為[M/2, M]；

每個結點存放至少M/2-1（取上整）和至多M-1個關鍵字；（至少2個關鍵字）

非葉子結點的關鍵字個數=指向兒子的指針個數-1；

非葉子結點的關鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

非葉子結點的指針：P[1], P[2], …, P[M]；其中P[1]指向關鍵字小于K[1]的子樹，P[M]指向關鍵字大于等于K[M-1]的子樹，其它P[i]指向關鍵字屬于[K[i], K[i+1])的子樹；

所有葉子結點位于同一層；

為所有葉子結點增加一個鏈指針；

所有關鍵字都在葉子結點出現； 如圖，是一個M為3的B-TREE

B+的特性：

所有關鍵字都出現在葉子結點的鏈表中（稠密索引），且鏈表中的關鍵字恰好是有序的；

不可能在非葉子結點命中；

非葉子結點相當于是葉子結點的索引（稀疏索引），葉子結點相當于是存儲（關鍵字）數據的數據層；

更適合文件索引系統；

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查找時過濾掉更多的行。比如主鍵和唯一索引，這種時候性能最好。所以在選擇索引長度時要同時考慮索引選擇性才能達到性能最優化。

多列索引

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

為很多個列創建獨立索引。在多個列上建立單獨索引并不能提高Mysql的查詢性能。5.0以后的Mysql引入了“索引合并”的策略，這樣可以多個單列索引就行掃描，并將結果進行合并。這種算法有三種變形： OR條件聯合，AND條件相交。這種情況下，合并結果會耗用大量的CPU，而且這個優化過程不計入“查詢成本”中。所以這些成本會被低估，有時候效率甚至低于全盤掃描，而且容易導致優化的時候注意不到這個點。

創建多列索引的順序有誤。查詢的時候沒有按照索引的順序來查詢，也會導致Mysql無法使用索引。在創建多列索引的時候有一些有用的原則：在不考慮排序和分組的情況下，將選擇性最高的列放在前面。但是很多時候這樣用也未必是好的，還是要根據具體的情況來判斷。

聚簇索引

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

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

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

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

可以把相關數據保存在一起，查詢時只需在磁盤讀取少數數據頁就能獲得所有的所需數據。

數據訪問速度快，因為數據和索引在一起所以查詢起來很快。

使用覆蓋索引掃描的查詢可以直接使用葉節點的主鍵值。二級索引（輔助索引）使用主鍵作為"指針" 而不是使用地址值作為指針的好處是，減少了當出現行移動或者數據頁分裂時輔助索引的維護工作，使用主鍵值當作指針會讓輔助索引占用更多的空間，換來的好處是InnoDB在移動行時無須更新輔助索引中的這個"指針"。也就是說行的位置會隨著數據庫里數據的修改而發生變化（后面缺點處會講到B+樹節點分裂以及頁分裂），使用聚簇索引就可以保證不管這個主鍵B+樹的節點如何變化，輔助索引樹都不受影響。

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

聚簇索引在數據都放在內存中的數據毫無優勢。

插入速度在按照主鍵順序插入的時候影響不大，但不按照順序加入的時候，速度會受到影響而且插入后要使用optimize table優化一下表，能更新索引統計數據并釋放成簇索引中的未使用的空間。

更新聚簇索引的成本很高，因為InnoDB會強制將每個被更新的行移動到新的位置上。

基于聚簇索引的表插入新行，或者主鍵被更新導致需要移動行時，可能面臨“頁分裂的問題”。當該行插入到一個某個已經滿了的頁的時候，存儲引擎會將頁分裂成兩個頁面來容納該行，這樣的頁分裂操作會導致表占用更多空間。而且這樣會造成數據存儲不連續，行比較稀疏的問題，也會導致全盤掃描變慢。頁分裂還會造成大量數據的移動，一次插入至少修改到3個頁。而順序插入的時候，很少遇到需要大量修改頁的情況，最大的性能瓶頸就在自動增減主鍵的時候鎖的開銷。如圖： 順序添加 插入無序值的時候

二級索引（輔助索引）可能比想象的要大，因為二級索引葉子節點包含了引用行主鍵的列。而且二級索引需要兩次查找。

覆蓋索引

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

索引條目比起數據行要小得多，所以如果只需要讀取索引，那MySQL就能極大的減少數據訪問量。

因為索引是按照列值順序存儲的，所以對于IO密集型的范圍查詢，只查找索引就會比去硬盤中隨機查詢每一行數據快得多。

數據庫引擎（如MyISAM）在內存中只緩存索引，數據緩存依賴于操作系統緩存，因此訪問數據需要系統調用，這個會造成嚴重的性能問題。

上面說的聚簇索引，覆蓋索引的時候就特別有用了。

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

索引掃描來做排序

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

索引和鎖

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

一些微小的建議

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

總結

以上是生活随笔為你收集整理的Mysql-高性能索引的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。