圖解 MySQL 索引，清晰易懂，寫得太好了！
AI全套：Python3+TensorFlow打造人臉識別智能小程序
最新人工智能資料-Google工程師親授 Tensorflow－入門到進(jìn)階
黑馬頭條項目 - Java Springboot2.0（視頻、資料、代碼和講義）14天完整版
作者：shuaibing90
來源：www.xysycx.cn/articles/2020/12/05/1607146183637.html什么是索引？
索引是輔助存儲引擎高效獲取數(shù)據(jù)的一種數(shù)據(jù)結(jié)構(gòu)。
很多人形象的說索引就是數(shù)據(jù)的目錄，便于存儲引擎快速的定位數(shù)據(jù)。 索引的分類
數(shù)據(jù)結(jié)構(gòu)角度看索引
對上表進(jìn)行橫向查看可以了解到，B+tree 是 MySQL 中被存儲引擎采用最多的索引類型。
這里淺嘗輒止的談一下 B+tree 與 Hash 和紅黑樹的區(qū)別。這方面系列面試題和答案全部整理好了，微信搜索互聯(lián)網(wǎng)架構(gòu)師，在后臺發(fā)送：面試，可以在線閱讀。
B+tree 和 B-tree
B+tree 是 B-Tree 的一個變種。（哦，對了，B-tree 念 B 樹，它不叫 B 減樹。。。）
B-tree :? https://www.cs.usfca.edu/~galles/visualization/BTree.htmlB+tree :? https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
因此，B+tree 單個節(jié)點(diǎn)的數(shù)量更小，在相同的磁盤 IO 下能查詢更多的節(jié)點(diǎn)。
B+tree 和紅黑樹
B+tree 索引與 Hash 表
物理存儲角度看索引
InnoDB 的索引
首先看一下 InnoDB 存儲引擎中的索引，InnoDB 表的索引按照葉子節(jié)點(diǎn)存儲的是否為完整表數(shù)據(jù)分為聚簇索引和二級索引。
全表數(shù)據(jù)就是存儲在聚簇索引中的。
聚簇索引以外的其它索引叫做二級索引。
create?table?workers
?(
?????id????int(11)?????not?null?auto_increment?comment?'員工工號',
?????name??varchar(16)?not?null?comment?'員工名字',
?????sales?int(11)?default?null?comment?'員工銷售業(yè)績',
?????primary?key?(id)
?)?engine?InnoDB
???AUTO_INCREMENT?=?10
???default?charset?=?utf8;
?insert?into?workers(id,?name,?sales)
?values?(1,?'江南',?12744);
?insert?into?workers(id,?name,?sales)
?values?(3,?'今何在',?14082);
?insert?into?workers(id,?name,?sales)
?values?(7,?'路明非',?14738);
?insert?into?workers(id,?name,?sales)
?values?(8,?'呂歸塵',?7087);
?insert?into?workers(id,?name,?sales)
?values?(11,?'姬野',?8565);
?insert?into?workers(id,?name,?sales)
?values?(15,?'凱撒',?8501);
?insert?into?workers(id,?name,?sales)
?values?(20,?'繪梨衣',?7890);
包含 id(主鍵),name,sales 三個字段，指定表的存儲引擎為 InnoDB。
然后插入 8 條數(shù)據(jù)
為了準(zhǔn)確模擬，我們先把主鍵 id 插入 b+tree 得到下圖
然后在此圖基礎(chǔ)上，我畫出了高清版。
alter?table?workers?add?index?index_name(name);
同樣我們畫出了二級索引 index_name 的 B+tree 示意圖
圖中可以看出二級索引的葉子節(jié)點(diǎn)并不存儲一行完整的表數(shù)據(jù)，而是存儲了聚簇索引所在列的值，也就是workers 表中的 id 列的值。
這兩張示意圖中 B+tree 的度設(shè)置為了 3 ，這也主要是為了方便演示。
實(shí)際的 B+tree 索引中，樹的度通常會大于 100。
說了聚簇索引和二級索引 肯定要提到「回表查詢」。
由于二級索引的葉子節(jié)點(diǎn)不存儲完整的表數(shù)據(jù)，所以當(dāng)通過二級索引查詢到聚簇索引的列值后，還需要回到局促索引也就是表數(shù)據(jù)本身進(jìn)一步獲取數(shù)據(jù)。
分享資料：2T架構(gòu)師學(xué)習(xí)資料干貨分享
比如說我們要在 workers 表中查詢 名叫呂歸塵的人
select?*?from?workers?where?name='呂歸塵';
select?id,name?from?workers?where?name='呂歸塵';
這句 SQL 只查詢了 id，和 name，二級索引就已經(jīng)包含了 Query 所以需要的所有字段，就無需回表查詢。
explain?select?id,name?from?workers?where?name='呂歸塵';
使用 explain 查看此條 SQL 的執(zhí)行計劃
explain?select?id,name,sales?from?workers?where?name='呂歸塵';
Using Index Condition?表示會先條件過濾索引，過濾完索引后找到所有符合索引條件的數(shù)據(jù)行，隨后用 WHERE 子句中的其他條件去過濾這些數(shù)據(jù)行。Index Condition Pushdown (ICP)是 MySQL 5.6 以上版本中的新特性，是一種在存儲引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式。ICP 開啟時的執(zhí)行計劃含有 Using index condition 標(biāo)示 ，表示優(yōu)化器使用了 ICP 對數(shù)據(jù)訪問進(jìn)行優(yōu)化。
Using where?只是提醒我們 MySQL 將用 where 子句來過濾結(jié)果集。這個一般發(fā)生在 MySQL 服務(wù)器，而不是存儲引擎層。一般發(fā)生在不能走索引掃描的情況下或者走索引掃描，但是有些查詢條件不在索引當(dāng)中的情況下。這里表明沒有觸發(fā)索引覆蓋，進(jìn)行回表查詢。
MyISAM 的索引
說完了 InnoDB 的索引，接下來我們來看 MyISAM 的索引
以 MyISAM 存儲引擎存儲的表不存在聚簇索引。
他們的葉子節(jié)點(diǎn)是不存儲表數(shù)據(jù)的，節(jié)點(diǎn)中存放的是表數(shù)據(jù)的地址，所以 MyISAM 表可以沒有主鍵。
MyISAM 表的數(shù)據(jù)和索引是分開的，是單獨(dú)存放的。
MyISAM 表中的主鍵索引和非主鍵索引的區(qū)別僅在于主鍵索引 B+tree 上的 key 必須符合主鍵的限制，
非主鍵索引 B+tree 上的 key 只要符合相應(yīng)字段的特性就可以了。
索引字段特性角度看索引
create?table?persons
?(
?????id???int(11)?not?null?auto_increment?comment?'主鍵id',
?????eno??int(11)?comment?'工號',
?????eid??int(11)?comment?'身份證號',
?????veid?int(11)?comment?'虛擬身份證號',
?????name?varchar(16)?comment?'名字',
?????primary?key?(id)?comment?'主鍵索引',
?????UNIQUE?key?(eno)?comment?'eno唯一索引',
?????UNIQUE?key?(eid)?comment?'eid唯一索引'
?)?engine?=?InnoDB
???auto_increment?=?1000
???default?charset?=?utf8;
?alter?table?persons
?????add?unique?index?index_veid?(veid)?comment?'veid唯一索引';
show index from persons;命令我們看到已經(jīng)成功創(chuàng)建了三個唯一索引。
普通索引
主鍵索引和唯一索引對字段的要求是要求字段為主鍵或 unique 字段，
而那些建立在普通字段上的索引叫做普通索引，既不要求字段為主鍵也不要求字段為 unique。
另外，關(guān)注公眾號互聯(lián)網(wǎng)架構(gòu)師，在后臺回復(fù)：面試，可以獲取我整理的 MySQL 系列面試題和答案，非常齊全。
前綴索引
前綴索引是指對字符類型字段的前幾個字符或?qū)ΧM(jìn)制類型字段的前幾個 bytes 建立的索引，而不是在整個字段上建索引。
例如，可以對 persons 表中的 name(varchar(16))字段 中 name 的前 5 個字符建立索引。
create?index?index_name?on?persons?(name(5))?comment?'前綴索引';
show?index?from?persons;
char varchar binary varbinary
索引列的個數(shù)角度看索引
建立在單個列上的索引為單列索引
上文演示的都是單列索引 建立在多列上的稱為聯(lián)合索引（復(fù)合索引）
演示一下聯(lián)合索引create index index_id_name on workers(id,name) comment '組合索引';這條語句在我們演示表 workers 中建立 id，name 這兩個字段的聯(lián)合索引。借助 show index 命令查看索引的詳細(xì)信息 操作后結(jié)果如下：
同樣我們來看下聯(lián)合索引的 B+tree 示意圖
?「全棧架構(gòu)社區(qū)」建立了讀者架構(gòu)師交流群，大家可以添加小編微信進(jìn)行加群。歡迎有想法、樂于分享的朋友們一起交流學(xué)習(xí)。
看完本文有收獲？請轉(zhuǎn)發(fā)分享給更多人
Flutter 移動應(yīng)用開發(fā)實(shí)戰(zhàn) 視頻（開發(fā)你自己的抖音APP） Java面試進(jìn)階訓(xùn)練營 第2季（分布式篇） Java高級 - 分布式系統(tǒng)開發(fā)技術(shù)視頻