什么是索引？

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

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

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

哈希表

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

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

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

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

有序數(shù)組：

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

搜索樹：

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

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

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

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

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

innodb的索引模型

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

非主鍵索引圖;

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

索引維護(hù)

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

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

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

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

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

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

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

查看索引利用率

查看performance_schema.table_io_waits_summary_by_index_usage表

覆蓋索引

mysql的innodb引擎通過搜索樹方式實(shí)現(xiàn)索引，索引類型分為主鍵索引和二級索引（非主鍵索引），主鍵索引樹中，葉子結(jié)點(diǎn)保存著主鍵即對應(yīng)行的全部數(shù)據(jù)；而二級索引樹中，葉子結(jié)點(diǎn)保存著索引值和主鍵值，當(dāng)使用二級索引進(jìn)行查詢時，需要進(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í)行語句(A) select id from user_table where username = 'lzs'時，因?yàn)閡sername索引樹的葉子結(jié)點(diǎn)上保存有username和id的值，所以通過username索引樹查找到id后，我們就已經(jīng)得到所需的數(shù)據(jù)了，這時候就不需要再去主鍵索引上繼續(xù)查找了。
執(zhí)行語句(B) select password from user_table where username = 'lzs'時，

流程如下:

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

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

聯(lián)合索引

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

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

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

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

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

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

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

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

索引下推

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

過程如下圖:

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

過程如下圖:

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

SET optimizer_switch = 'index_condition_pushdown=off';

注意點(diǎn)：

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

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

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

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

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

最新評論