什么是索引？

當(dāng)我們使用漢語(yǔ)字典查找某個(gè)字時(shí)，我們會(huì)先通過(guò)拼音目錄查到那個(gè)字所在的頁(yè)碼，然后直接翻到字典的那一頁(yè)，找到我們要查的字，通過(guò)拼音目錄查找比我們拿起字典從頭一頁(yè)一頁(yè)翻找要快的多，數(shù)據(jù)庫(kù)索引也一樣，索引就像書的目錄，通過(guò)索引能極大提高數(shù)據(jù)查詢的效率。

索引的實(shí)現(xiàn)方式

在數(shù)據(jù)庫(kù)中，常見的索引實(shí)現(xiàn)方式有哈希表、有序數(shù)組、搜索樹

哈希表

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

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

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

• 1、兩個(gè)相鄰索引通過(guò)hash函數(shù)后計(jì)算得到的數(shù)組位置不一定還保持相鄰
•  2、鏈表上的數(shù)據(jù)是無(wú)序的

有序數(shù)組：

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

搜索樹：

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

當(dāng)使用平衡二叉實(shí)現(xiàn)索引時(shí)，結(jié)構(gòu)如下圖：

從圖中可發(fā)現(xiàn)，每次查詢最多需要訪問(wèn)4個(gè)節(jié)點(diǎn)必能得到所要數(shù)據(jù)。例如查詢user2時(shí)，查詢過(guò)程為：userA-->userC-->userF-->user2。
所以查詢速度很高，同時(shí)，因?yàn)樗阉鳂涞奶匦裕ㄗ笞訕湫∮谟易訕洌?，區(qū)間查詢也很方便。

如果搜索樹存于內(nèi)存中，與多叉樹相比，二叉樹的搜索速率是最高的，但實(shí)際上數(shù)據(jù)庫(kù)使用的是n叉樹而不是二叉樹。

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

innodb的索引模型

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

非主鍵索引圖;

通過(guò)主鍵索引查詢數(shù)據(jù)時(shí)，我們只需查找主鍵索引樹便可以獲取數(shù)據(jù)；通過(guò)非主鍵索引查詢數(shù)據(jù)時(shí)，我們先通過(guò)非主鍵索引樹查找到主鍵值，然后再在主鍵索引樹搜索一次，這個(gè)過(guò)程稱為回表，也就是說(shuō)非主鍵索引查詢會(huì)比主鍵查詢多搜索一棵樹。所以我們應(yīng)盡可能使用主鍵查詢。

索引維護(hù)

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

當(dāng)相鄰兩個(gè)頁(yè)由于刪除了數(shù)據(jù)，利用率很低之后，會(huì)將數(shù)據(jù)頁(yè)做合并

所以，一般情況下會(huì)采用遞增主鍵，使新數(shù)據(jù)遞增插入。

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

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

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

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

查看索引利用率

查看performance_schema.table_io_waits_summary_by_index_usage表

覆蓋索引

mysql的innodb引擎通過(guò)搜索樹方式實(shí)現(xiàn)索引，索引類型分為主鍵索引和二級(jí)索引（非主鍵索引），主鍵索引樹中，葉子結(jié)點(diǎn)保存著主鍵即對(duì)應(yīng)行的全部數(shù)據(jù)；而二級(jí)索引樹中，葉子結(jié)點(diǎn)保存著索引值和主鍵值，當(dāng)使用二級(jí)索引進(jìn)行查詢時(shí)，需要進(jìn)行回表操作。假如我們現(xiàn)在有如下表結(jié)構(gòu)

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í)行語(yǔ)句(A) select id from user_table where username = 'lzs'時(shí)，因?yàn)閡sername索引樹的葉子結(jié)點(diǎn)上保存有username和id的值，所以通過(guò)username索引樹查找到id后，我們就已經(jīng)得到所需的數(shù)據(jù)了，這時(shí)候就不需要再去主鍵索引上繼續(xù)查找了。
執(zhí)行語(yǔ)句(B) select password from user_table where username = 'lzs'時(shí)，

流程如下:

• 1、username索引樹上找到username=lzs對(duì)應(yīng)的主鍵id
• 2、通過(guò)回表在主鍵索引樹上找到滿足條件的數(shù)據(jù)

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

聯(lián)合索引

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

現(xiàn)在，假設(shè)我們有一下三種查詢情景：

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

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

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

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

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

一般來(lái)說(shuō)，username字段比age字段大的多，所以，我們應(yīng)選擇第一種，索引占用空間較小。

索引下推

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

過(guò)程如下圖:

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

過(guò)程如下圖:

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

SET optimizer_switch = 'index_condition_pushdown=off';

注意點(diǎn)：

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

就像之前提到的，innodb的主鍵索引樹葉子結(jié)點(diǎn)上保存的是全行數(shù)據(jù)，所以這個(gè)時(shí)候索引下推并不會(huì)起到減少查詢?nèi)袛?shù)據(jù)的效果。

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

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

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

最新評(píng)論