什么是索引？

當我們使用漢語字典查找某個字時，我們會先通過拼音目錄查到那個字所在的頁碼，然后直接翻到字典的那一頁，找到我們要查的字，通過拼音目錄查找比我們拿起字典從頭一頁一頁翻找要快的多，數據庫索引也一樣，索引就像書的目錄，通過索引能極大提高數據查詢的效率。

索引的實現方式

在數據庫中，常見的索引實現方式有哈希表、有序數組、搜索樹

哈希表

哈希表是通過鍵值對（key-value）存儲數據的索引實現方式，可以將哈希表想象成是一個數組，將索引通過哈希函數計算得到該行數據在數組中的位置，然后將數據存到數組中，容易發(fā)現一個問題，如果兩個索引通過哈希函數計算后得到的數組位置相同要怎么辦？在這里，數組的每個value都是一個鏈表，鏈表上的每個元素都是一個數據，新數據直接添加到鏈表尾部。

所以數據庫查詢過程為：索引通過哈希函數計算數據所在位置--> 遍歷指定位置的鏈表，找到滿足條件的數據。
要注意的是，鏈表上的數據元素不是有序的，每次有新數據加入時，新數據時直接添加到鏈表尾部，這樣做的好處是添加數據時很方便。

哈希表不擅長進行區(qū)間查詢，一般都用于等值查詢：

• 1、兩個相鄰索引通過hash函數后計算得到的數組位置不一定還保持相鄰
•  2、鏈表上的數據是無序的

有序數組：

顧名思義，有序數組是按索引大小將數據保存在一個數組上，因為該數組是有序的，可以通過二分法很容易查到位置，找到第一個位置后，通過向左/向右遍歷很容易得到所求區(qū)間的數據。因此，無論是等值查詢還是區(qū)間查詢，效率都極高。但缺陷也是顯而易見的，當向數組中間n位置插入一條數據時，需將n后面的數據全部往后移動，所以，這種索引一般用于靜態(tài)存儲引擎。

搜索樹：

• 二叉搜索樹：一棵空樹，或者是具有下列性質的二叉樹： 若它的左子樹不空，則左子樹上所有結點的值均小于它的根結點的值； 若它的右子樹不空，則右子樹上所有結點的值均大于它的根結點的值； 二叉搜索樹的左、右子樹也分別為二叉搜索樹。
• 平衡二叉樹：平衡二叉樹是在二叉搜索樹的基礎上引入的，指的是結點的左子樹和右子樹的深度差不超過1.
• 多叉樹：每個結點可以有多個子結點，子節(jié)點的大小從左到右依次遞增。

當使用平衡二叉實現索引時，結構如下圖：

從圖中可發(fā)現，每次查詢最多需要訪問4個節(jié)點必能得到所要數據。例如查詢user2時，查詢過程為：userA-->userC-->userF-->user2。
所以查詢速度很高，同時，因為搜索樹的特性（左子樹小于右子樹），區(qū)間查詢也很方便。

如果搜索樹存于內存中，與多叉樹相比，二叉樹的搜索速率是最高的，但實際上數據庫使用的是n叉樹而不是二叉樹。

• 1、索引不僅存于內存，還是寫到磁盤上
• 2、搜索樹上的每個結點在磁盤上表現為一個數據塊
• 3、多叉樹每個結點下可以有多個子節(jié)點，所以存儲相同數據量時多叉樹的樹高比二叉樹小，查詢一個數據需要訪問的結點數更少，即查詢過程訪問更少的數據塊。查詢速度較高。

innodb的索引模型

innodb使用B+樹作為索引結構。
在B+樹中，我們將節(jié)點分為葉子結點和非葉子結點，非葉子結點上保存的是索引，而且一個節(jié)點可以保存多個索引；數據全部存于葉子結點上,根據葉子結點的內容不同，innodb索引分為主鍵索引和非主鍵索引。非主鍵索引也稱為二級索引。
主鍵索引的葉子結點中保存的數據為整行數據，而非主鍵索引葉子節(jié)點保存的是主鍵的值。

非主鍵索引圖;

通過主鍵索引查詢數據時，我們只需查找主鍵索引樹便可以獲取數據；通過非主鍵索引查詢數據時，我們先通過非主鍵索引樹查找到主鍵值，然后再在主鍵索引樹搜索一次，這個過程稱為回表，也就是說非主鍵索引查詢會比主鍵查詢多搜索一棵樹。所以我們應盡可能使用主鍵查詢。

索引維護

添加新行時，將會在索引表上添加一條記錄，如果是索引遞增插入時，數據都是追加在當前最大索引之后，不會對樹中其他數據造成影響；如果新加入的數據的索引值位于節(jié)點的中間，需要挪動部分節(jié)點的位置，從而保持索引樹的有序性。
而且，相鄰多個節(jié)點是存儲在同一個數據頁上的，此時，如果是在已經存儲滿狀態(tài)的數據頁中插入節(jié)點，會申請新的數據頁，將部分數據挪動到新的數據頁，這個過程稱為頁分裂，頁分裂除了會影響性能，還會降低磁盤空間利用率。不規(guī)則數據插入時,會造成頻繁的頁分裂。

當相鄰兩個頁由于刪除了數據，利用率很低之后，會將數據頁做合并

所以，一般情況下會采用遞增主鍵，使新數據遞增插入。

使用業(yè)務邏輯字段做主鍵有什么優(yōu)缺點？

• 1、業(yè)務邏輯字段不容易保證索引樹結點有序插入，這樣寫入成本較高。
• 2、innodb默認使用整數類型作為主鍵，主鍵長度較小，二級索引的葉子結點中保存的是主鍵值，主鍵長度越小，二級索引的葉子結點占用空間也就越小。
• 3、當然，使用業(yè)務邏輯字段做主鍵也有好處，可以避免回表，每次只需掃描一次主鍵索引樹即可

綜上，從性能和存儲空間方面考量，自增主鍵往往是更合理的選擇,當業(yè)務場景有且只有一個索引，而且該索引為唯一索引時，此時更適合使用業(yè)務邏輯字段作為主鍵。

因為數據修改/刪除、頁分裂等原因，會導致數據頁空間利用率降低，此時，可以考慮重建索引，將數據按順序插入，提高磁盤空間利用率。但重建主鍵索引和普通索引會有不同影響，重建普通索引，可以達到提高空間利用率的目的，且不會對其他索引造成影響，但如果重建主鍵索引就不合理了，會影響所有普通索引，性能影響較大，而且無論是新建/刪除主鍵，都會重建整張表。這時我們可以使用alter table T engine=InnoDB這個語句代替。

查看索引利用率

查看performance_schema.table_io_waits_summary_by_index_usage表

覆蓋索引

mysql的innodb引擎通過搜索樹方式實現索引，索引類型分為主鍵索引和二級索引（非主鍵索引），主鍵索引樹中，葉子結點保存著主鍵即對應行的全部數據；而二級索引樹中，葉子結點保存著索引值和主鍵值，當使用二級索引進行查詢時，需要進行回表操作。假如我們現在有如下表結構

CREATE TABLE `user_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `age` int(11) unsigned Not NULL,
  PRIMARY KEY (`id`),
  key (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

執(zhí)行語句(A) select id from user_table where username = 'lzs'時，因為username索引樹的葉子結點上保存有username和id的值，所以通過username索引樹查找到id后，我們就已經得到所需的數據了，這時候就不需要再去主鍵索引上繼續(xù)查找了。
執(zhí)行語句(B) select password from user_table where username = 'lzs'時，

流程如下:

• 1、username索引樹上找到username=lzs對應的主鍵id
• 2、通過回表在主鍵索引樹上找到滿足條件的數據

由上面可知，當sql語句的所求查詢字段（select列）和查詢條件字段（where子句）全都包含在一個索引中，可以直接使用索引查詢而不需要回表。這就是覆蓋索引，通過使用覆蓋索引，可以減少搜索樹的次數，是常用的性能優(yōu)化手段。
例如上面的語句B是一個高頻查詢的語句，我們可以建立(username,password)的聯(lián)合索引，這樣，查詢的時候就不需要再去回表操作了，可以提高查詢效率。當然，添加索引是有維護代價的，所以添加時也要權衡一下。

聯(lián)合索引

mysql的b+樹索引遵循“最左前綴”原則，繼續(xù)以上面的例子來說明，為了提高語句B的執(zhí)行速度，我們添加了一個聯(lián)合索引（username,password）,特別注意這個聯(lián)合索引的順序，如果我們顛倒下順序改成（password,username),這樣查詢能使用這個索引嗎？答案是不能的！這是最左前綴的第一層含義：聯(lián)合索引的多個字段中，只有當查詢條件為聯(lián)合索引的一個字段時，查詢才能使用該索引。

現在，假設我們有一下三種查詢情景：

• 1、查出用戶名的第一個字是“張”開頭的人的密碼。即查詢條件子句為"where username like '張%'"
• 2、查處用戶名中含有“張”字的人的密碼。即查詢條件子句為"where username like '%張%'"
• 3、查出用戶名以“張”字結尾的人的密碼。即查詢條件子句為"where username like '%張'"

以上三種情況下，只有第1種能夠使用（username,password）聯(lián)合索引來加快查詢速度。這就是最左前綴的第二層含義：索引可以用于查詢條件字段為索引字段，根據字段值最左若干個字符進行的模糊查詢。

維護索引需要代價，所以有時候我們可以利用“最左前綴”原則減少索引數量，上面的（username,password）索引，也可用于根據username查詢age的情況。當然，使用這個索引去查詢age的時候是需要進行回表的，當這個需求（根據username查詢age）也是高頻請求時，我們可以創(chuàng)建（username,password,age）聯(lián)合索引，這樣，我們需要維護的索引數量不變。

創(chuàng)建索引時，我們也要考慮空間代價，使用較少的空間來創(chuàng)建索引
假設我們現在不需要通過username查詢password了，相反，經常需要通過username查詢age或通過age查詢username,這時候，刪掉（username,password）索引后，我們需要創(chuàng)建新的索引，我們有兩種選擇

• 1、（username,age）聯(lián)合索引+age字段索引
• 2、（age,username）聯(lián)合索引+username單字段索引

一般來說，username字段比age字段大的多，所以，我們應選擇第一種，索引占用空間較小。

索引下推

對于user_table表，我們現在有（username,age）聯(lián)合索引
如果現在有一個需求，查出名稱中以“張”開頭且年齡小于等于10的用戶信息，語句C如下："select * from user_table where username like '張%' and age > 10".
語句C有兩種執(zhí)行可能：
1、根據（username,age）聯(lián)合索引查詢所有滿足名稱以“張”開頭的索引，然后回表查詢出相應的全行數據，然后再篩選出滿足年齡小于等于10的用戶數據。

過程如下圖:

2、根據（username,age）聯(lián)合索引查詢所有滿足名稱以“張”開頭的索引，然后直接再篩選出年齡小于等于10的索引，之后再回表查詢全行數據。

過程如下圖:

明顯的，第二種方式需要回表查詢的全行數據比較少，這就是mysql的索引下推。mysql默認啟用索引下推，我們也可以通過修改系統(tǒng)變量optimizer_switch的index_condition_pushdown標志來控制

SET optimizer_switch = 'index_condition_pushdown=off';

注意點：

1、innodb引擎的表，索引下推只能用于二級索引。

就像之前提到的，innodb的主鍵索引樹葉子結點上保存的是全行數據，所以這個時候索引下推并不會起到減少查詢全行數據的效果。

2、索引下推一般可用于所求查詢字段（select列）不是/不全是聯(lián)合索引的字段，查詢條件為多條件查詢且查詢條件子句（where/order by）字段全是聯(lián)合索引。

假設表t有聯(lián)合索引（a,b）,下面語句可以使用索引下推提高效率
select * from t where a > 2 and b > 10;

到此這篇關于mysql索引(覆蓋索引,聯(lián)合索引,索引下推)的文章就介紹到這了,更多相關mysql索引內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論