結(jié)論：索引是把雙刃劍俐填，可以提高數(shù)據(jù)庫性能伶氢，也會影響數(shù)據(jù)庫性能

1. 利：

• 索引加快數(shù)據(jù)查詢速度澈蟆，提高數(shù)據(jù)庫查詢性能墨辛。

2. 弊：

• 數(shù)據(jù)庫中索引是以文件的方式存儲的，需要用的時候讀取到內(nèi)存中趴俘，因此索引的I/O操作會影響數(shù)據(jù)庫的性能背蟆；
• 此外插入和更新操作會更改索引，因此會影響數(shù)據(jù)庫插入和更新的性能哮幢，并且索引會占用一定的磁盤空間带膀，使數(shù)據(jù)庫變大。

背景知識

• 索引的本質(zhì)： MySQL官方定義為：索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)橙垢，所以索引的本質(zhì)是一種數(shù)據(jù)結(jié)構(gòu)垛叨。常用的數(shù)據(jù)結(jié)構(gòu)有：集合，線性結(jié)構(gòu)柜某，樹嗽元，圖等。
• 索引的目的：數(shù)據(jù)庫查詢是數(shù)據(jù)庫最主要的功能之一喂击，索引的目的在于加快數(shù)據(jù)庫的查詢速度剂癌，從而提高數(shù)據(jù)庫的使用性能。
• 最基本的查詢算法是順序查找翰绊，復(fù)雜度為O(n)佩谷，在數(shù)據(jù)量較大的情況下性能較差；其次有二分查找监嗜，復(fù)雜度為O(logn)谐檀，在數(shù)據(jù)量大的情況下性能較好。不同的查詢算法需要適配不同的數(shù)據(jù)結(jié)構(gòu)裁奇，順序查找主要針對的是線性結(jié)構(gòu)桐猬；而二分查找主要適用于二叉查找樹。
• 在數(shù)據(jù)庫中刽肠，數(shù)據(jù)本身的組織結(jié)構(gòu)不可能完全滿足各種數(shù)據(jù)結(jié)構(gòu)（比如溃肪，二叉查找樹需要排序）免胃，所以數(shù)據(jù)庫需要在數(shù)據(jù)之外，還維護滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)惫撰，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向具體的數(shù)據(jù)羔沙，通過查找這些數(shù)據(jù)結(jié)構(gòu)，就可以快速的查詢數(shù)據(jù)润绎。這種數(shù)據(jù)結(jié)構(gòu)撬碟，就是索引。一個簡單的示例如下：
  
  圖1.png
• 圖1中展示了一種簡單的索引方式莉撇，左邊記錄是物理地址呢蛤，存放在磁盤上，為了加快col2的查找棍郎，可以維護一個右邊所示的二叉查找樹其障，每個節(jié)點包含索引鍵值和對應(yīng)記錄在磁盤上的物理地址，這樣通過查找樹就可以在O(logn)的復(fù)雜度內(nèi)獲取相應(yīng)的數(shù)據(jù)涂佃。（圖中示例使用了二叉樹做為索引是數(shù)據(jù)結(jié)構(gòu)励翼，其實是一種不好的結(jié)構(gòu)，后續(xù)拓展中會說明）

Mysql索引

在Mysql中辜荠，索引是屬于存儲引擎級別的概念汽抚，因此不同的存儲引擎對索引的實現(xiàn)方式是不同的，主要常用的是MyISAM和InnoDB兩個存儲引擎伯病。（MyISAM提供表級鎖造烁，適用于基本上是查詢操作的數(shù)據(jù)庫；InnoDB提供行級鎖午笛，適用于更新惭蟋，插入較頻繁的表）

MyISAM和InnoDB兩個存儲引擎主要使用B+樹做為索引。B+樹是一個樹形的數(shù)據(jù)結(jié)構(gòu)药磺，特點是：

• 每個節(jié)點的指針上限是2d
• 內(nèi)節(jié)點不存儲data告组，只存儲key；只有葉子節(jié)點才存儲數(shù)據(jù)

聚簇索引和非聚簇索引區(qū)別：

聚簇索引和非聚簇索引

MyISAM索引結(jié)構(gòu)：

MyISAM存儲引擎中數(shù)據(jù)文件和索引文件是分離的癌佩。
MyISAM的索引主要分為主索引和輔助索引：MyISAM索引方式也稱為“非聚集”索引

• 主索引（primary key）：以主鍵做為索引木缝，因此key是唯一的；索引示例圖如下所示：

  
  
  圖2
  
  

  圖2中按照主鍵構(gòu)造了個B+樹驼卖，葉子節(jié)點中存儲了主鍵記錄在磁盤上的地址氨肌，因此通過O(logn)的復(fù)雜度就可以索引到記錄。

• 輔助索引（secondary key）：輔助索引以非主鍵做為索引酌畜，因此key是可以重復(fù)的

  
  
  圖3
  
  

  圖3中的輔助索引B+樹，葉子節(jié)點的內(nèi)容中也保存了磁盤上記錄的地址卿叽，然后讀取相應(yīng)的數(shù)據(jù)記錄桥胞。

InnoDB索引結(jié)構(gòu)：

InnoDB存儲引擎也分為主索引和輔助索引：InnoDB索引方式也稱為聚集索引
InnoDB和MyISAM最大的區(qū)別是：InnoDB數(shù)據(jù)文件本身就是索引文件恳守。因為InnoDB的數(shù)據(jù)文件本身是按主鍵聚集的，所以InnoDB要求表必須有主鍵贩虾，如果沒有顯示指定主鍵催烘，InnoDB會自動選擇可以唯一標(biāo)識數(shù)據(jù)記錄的列做主鍵；如果不存在缎罢，則表生成一個隱含字段作為主鍵（字段為6個字節(jié)伊群，長整形）。

• 主索引：主鍵做索引

  
  
  圖4
  
  

  數(shù)據(jù)記錄在葉子節(jié)點中策精，通過查找直接訪問查詢數(shù)據(jù)舰始。

• 輔助索引：非主鍵做索引

  
  
  圖5
  
  

  輔助索引的葉子節(jié)點中值存儲索引鍵值和主鍵值，然后通過主鍵值在主索引中進行搜索數(shù)據(jù)咽袜。

索引總結(jié)

• 不要使用過長的字段做為索引丸卷，過長的字段會使索引的數(shù)據(jù)結(jié)構(gòu)變大，占用更多的空間询刹，因此影響數(shù)據(jù)庫的性能谜嫉。（索引做為文件保存在磁盤上，當(dāng)需要查詢索引的時候會從文件讀取凹联，因此過長的字段會影響索引整體的I/O性能）
• 在數(shù)據(jù)庫中盡量使用單調(diào)增的字段做為主鍵沐兰，因為索引本身是一個B+樹，如果字段是非單調(diào)增的蔽挠，則插入操作會頻繁的分裂B+樹來調(diào)增數(shù)據(jù)的有序性和平衡性住闯，效率十分低下。

索引優(yōu)化策略

MySQL的優(yōu)化主要有結(jié)構(gòu)優(yōu)化和查詢優(yōu)化象泵。索引策略屬于結(jié)構(gòu)優(yōu)化的范疇寞秃。下面使用Mysql官方提供的employees數(shù)據(jù)庫示例來演示索引策略。

employees數(shù)據(jù)庫安裝：

數(shù)據(jù)下載：https://launchpad.net/test-db/employees-db-1/1.0.6
參考網(wǎng)頁：http://dev.mysql.com/doc/employee/en/employees-installation.html
Mysql5.7版本可能會報錯偶惠，參考網(wǎng)頁：http://stackoverflow.com/questions/36322903/error-1193-when-following-employees-database-install-tutorial-with-mysql-5-7-1

圖6

最左前綴匹配原理

要想高效的使用索引春寿，首先需要知道查詢操作怎么使用索引，目前常用的是兩種索引：單列索引忽孽，組合索引（多列順序組合）绑改。

以employees數(shù)據(jù)庫中的titles為例，索引如下圖所示：

• 圖7

從圖7中可以看出兄一，titles存在兩個索引厘线，第一個是<emp_no,title,from_date>元組的組合索引（主索引）；第二個是單列的輔助索引（emp_no）出革。下面通過語句看索引使用的幾種情況：

• 全列匹配：

  • 順序查詢索引：

    
    
    圖8
    
    

    從圖中可以看出造壮，查詢條件的順序和索引一致，全列匹配使用了主索引。

  • 亂序查詢索引：

    
    
    圖9
    
    

    從圖中看出耳璧，不管查詢條件的順序如何成箫，全列匹配都會使用索引，因為數(shù)據(jù)庫本身會把sql語句進行優(yōu)化來匹配索引旨枯。

• 部分列匹配（最左前綴匹配）：

  • 第一列查詢條件：

    
    
    
    
    圖10
    
    

    從圖10中可以看出蹬昌，如果按第一列進行匹配，后續(xù)的列也必須按照順序排列攀隔，不然只會使用第一列來索引皂贩，第一張圖片，用來兩列做為索引昆汹；第二張圖片只匹配第一列（兩張圖片key_len不同）明刷。

  • 非第一列查詢：

    
    
    
    
    圖11
    
    

    從圖11中可以看出，如果查詢條件中不含有第一列筹煮，則不能使用索引遮精。

• 優(yōu)化示例：
  當(dāng)我們使用emp_no和from_date來進行索引時，只會用到emp_no的最左匹配索引败潦，索引語句如下所示：

  select * from titles where emp_no=10009 and from_date='1990-02-18';

但是這只用到了emp_no這個字段的索引本冲，因為缺少中間的title字段，我們使用distinct劫扒，發(fā)現(xiàn)title只有固定幾個值檬洞，因此，可以在sql中補全title來進行全列匹配索引沟饥。

圖12

索引選擇性

既然索引可以加快查詢速度添怔，是不是意味著只要查詢語句需要，就可以建上索引贤旷？答案是否定的广料。因為索引雖然可以加快查詢的速度，但索引本身會占用存儲空間幼驶，并且數(shù)據(jù)庫進行DML操作時會調(diào)整索引的架構(gòu)艾杏，同時mysql也會消耗資源來維護索引，因此索引并不是越多越好盅藻。

一般有兩種情況不建議添加索引：

• 表的記錄少购桑，很少的數(shù)據(jù)就沒有必要建立索引，一般來說氏淑，超過2000條記錄可以考慮建立索引勃蜘。
• 索引的選擇性較低，也不建議建立索引假残。索引的選擇性是指不重復(fù)的索引值與表記錄數(shù)的比值缭贡。（簡單的理解就是：如果索引的列存在大量相同的值，那么它的索引是沒有意義的）
  示例：比如employees.titles表中title列只有固定的幾個值：
  
  圖13
  
  如圖13中所示，title列的索引選擇性很低匀归，因此沒有必要建立一個單獨的title列索引坑资。

我們再看一個示例：employees.employees表中的索引如下圖所示耗帕，從圖中看出employees表中只有一個主鍵索引穆端，如果我們需要按照名字（first_name，last_name）來查詢數(shù)據(jù)仿便，在數(shù)據(jù)量較大的情況下速度會很慢体啰，因此我們需要建立名字查詢的索引。

圖14

首先看下按名字索引的選擇性：

圖15

圖16

拓展：

• 為啥Mysql索引不使用紅黑樹？
• 為何Mysql索引傾向于B+樹而不是B-樹镀岛？

兩個問題的答案都在于使用B+樹做為索引弦牡，可以減少索引的磁盤I/O性能。從前面我們可知漂羊，索引是一個數(shù)據(jù)結(jié)構(gòu)驾锰，存放在磁盤中，當(dāng)需要使用索引時走越，從磁盤讀取到內(nèi)存中椭豫，然后在內(nèi)存中進行索引查詢數(shù)據(jù)。因此索引的查找過程要產(chǎn)生磁盤I/O消耗，相對于內(nèi)存存取赏酥，I/O的速度要比內(nèi)存低好幾個數(shù)量級喳整，所以一個索引的優(yōu)劣性能可以通過索引文件的磁盤I/O消耗來衡量，如果磁盤I/O消耗越低裸扶，這就是一個越高效的索引框都。（所以B+優(yōu)于紅黑樹和B-樹的點就在于，B+樹的數(shù)據(jù)結(jié)構(gòu)有更小的磁盤I/O消耗）呵晨。下面詳細介紹：

• 主存存取原理：

  • 主存讀任罕！：將地址信號放在地址總線上傳給主存，主存讀取信號摸屠，然后到指定主存位置讀取數(shù)據(jù)谓罗，再傳輸?shù)綌?shù)據(jù)總線上，供其他部件讀取
  • 主存寫入：將要寫入的數(shù)據(jù)放入數(shù)據(jù)總線中季二，寫入的地址放入地址總線上檩咱，然后主存讀取兩個總線內(nèi)容，把數(shù)據(jù)放入相應(yīng)地址上胯舷。

• 磁盤存取原理：

  • 磁盤主要由大小相同且同軸的盤片組成刻蚯，磁盤可以轉(zhuǎn)動，磁盤的一頭有一個固定的磁頭需纳，用于讀取磁盤的數(shù)據(jù)芦倒，磁頭可以只能沿盤片半徑方向運動。盤片上一個個同心圓叫做磁道不翩，磁盤分成若干個扇區(qū)兵扬，如果要讀取某一扇區(qū)的內(nèi)容，需要先將磁頭移動到相應(yīng)的磁道上口蝠，這叫尋道時間器钟；再將盤片旋轉(zhuǎn)到相應(yīng)的扇區(qū)，這叫旋轉(zhuǎn)時間妙蔗。通常情況下磁盤的讀取主要是尋道時間傲霸。
  • 磁盤本身的速度相比于主存，cpu而言是非常慢的眉反，為了提交效率昙啄，就需要盡量減少磁盤的I/O次數(shù)，因此磁盤在讀取一個扇區(qū)后寸五，不會直接停止梳凛，而是向后多讀取一些內(nèi)容，這就是磁盤預(yù)讀梳杏。磁盤預(yù)讀的理論依據(jù)是局部性原理韧拒，當(dāng)程序用到一個數(shù)據(jù)時淹接，它周圍的數(shù)據(jù)也將會被使用到。磁盤預(yù)讀的長度一般為頁的整數(shù)倍（頁的相關(guān)知識請參考Linux內(nèi)存管理相關(guān)知識）叛溢，主存和磁盤以頁為交換單位塑悼。

• B+樹的索引：

  • 在Mysql索引的設(shè)計中，當(dāng)索引每次新建一個節(jié)點時楷掉，就會創(chuàng)建一個頁厢蒜，因此一個節(jié)點就存放在一個頁上，磁盤的一次I/O和主存交換一個頁（不考慮預(yù)讀）靖诗，也就是一次可以讀取一個節(jié)點郭怪，如果使用紅黑樹，可以發(fā)現(xiàn)紅黑樹是二叉樹刊橘，它的高度要遠遠高于B+/B-樹，因此每次讀取一個節(jié)點進行查找颂鸿，需要大量的磁盤I/O操作才能查詢到數(shù)據(jù)促绵，因為樹的查詢性能和樹的高度線性相關(guān)，因此B+/B-樹要優(yōu)于紅黑樹嘴纺。當(dāng)考慮預(yù)讀的話败晴，紅黑樹是二叉樹，兄弟節(jié)點只有一個栽渴；而B+/B-樹的兄弟節(jié)點有多個尖坤，可以充分發(fā)揮磁盤預(yù)讀的功能。
    
    圖17
  • 由于B+樹把數(shù)據(jù)放在葉子節(jié)點上闲擦，因此B+樹在非葉子節(jié)點的一個節(jié)點上可以存放更多的鍵值慢味；而B-樹把數(shù)據(jù)和鍵值放在一起，則一個節(jié)點放置的鍵值相對較少墅冷，也就是一個頁中存放較少的鍵值纯路，因此使用B+樹會達到更少的磁盤I/O次數(shù)，因此性能更好寞忿。

B+樹磁盤索引過程：

圖18

B+樹更適合操作系統(tǒng)文件和數(shù)據(jù)庫文件的索引驰唬。