圖解索引

全表數(shù)據(jù)就是存儲在聚簇索引中的。

聚簇索引以外的其它索引叫做二級索引。

下面結(jié)合實際的例子介紹下這兩類索引。

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);

我們現(xiàn)在自己的測試數(shù)據(jù)庫中創(chuàng)建一個包含銷售員信息的測試表workers

包含id(主鍵),name,sales三個字段，指定表的存儲引擎為InnoDB。

然后插入8條數(shù)據(jù)

這個例子當(dāng)中，workers表的聚簇索引建立在字段id上

為了準(zhǔn)確模擬，我們先把主鍵id插入b+tree得到下圖

然后在此圖基礎(chǔ)上，我畫出了高清版。

從圖中可以看到，聚簇索引的每個葉子節(jié)點存儲了一行完整的表數(shù)據(jù)，葉子節(jié)點間采用單向鏈表按id列遞增連接，可以方便的進(jìn)行順序檢索。

InnoDB表要求必須有聚簇索引，默認(rèn)在主鍵字段上建立聚簇索引，在沒有主鍵字段的情況下，表的第一個NOT NULL 的唯一索引將被建立為聚簇索引，在前兩者都沒有的情況下，InnoDB將自動生成一個隱式自增id列并在此列上創(chuàng)建聚簇索引。

接著來看二級索引。

還以剛才的workers表為例

我們在name字段上添加二級索引index_name

alter?table??workers?add?index??index_name(name);

同樣我們畫出了二級索引index_name的B+tree示意圖

圖中可以看出二級索引的葉子節(jié)點并不存儲一行完整的表數(shù)據(jù)，而是存儲了聚簇索引所在列的值，也就是

workers表中的id列的值。

這兩張示意圖中B+tree的度設(shè)置為了3 ，這也主要是為了方便演示。

實際的B+tree索引中，樹的度通常會大于100。

說了聚簇索引和二級索引 肯定要提到回表查詢。

由于二級索引的葉子節(jié)點不存儲完整的表數(shù)據(jù)，所以當(dāng)通過二級索引查詢到聚簇索引的列值后，還需要回到局促索引也就是表數(shù)據(jù)本身進(jìn)一步獲取數(shù)據(jù)。

比如說我們要在workers表中查詢 名叫呂歸塵的人

select?*?from?workers?where?name='呂歸塵';

這條sql通過name='呂歸塵'的條件

在二級索引index_name中查詢到主鍵id=8 ,接著帶著id=8這個條件

進(jìn)一步回到聚簇索引查詢以后才能獲取到完整的數(shù)據(jù)，很顯然回表需要額外的B+tree搜索過程，必然增大查詢耗時。

需要注意的是通過二級索引查詢時，回表不是必須的過程,當(dāng)Query的所有字段在二級索引中就能找到時，就不需要回表，MySQL稱此時的二級索引為覆蓋索引或稱觸發(fā)了索引覆蓋。

select?id,name?from?workers?where?name='呂歸塵';

這句sql只查詢了id，和name,二級索引就已經(jīng)包含了Query所以需要的所有字段，就無需回表查詢。

explain?select?id,name?from?workers?where?name='呂歸塵';

使用explain查看此條sql的執(zhí)行計劃

執(zhí)行計劃的Extra字段中出現(xiàn)了Using where;Using index表明查詢觸發(fā)了索引index_name的索引覆蓋,且對索引做了where篩選，這里不需要回表。

下面做對比，查詢一下沒有索引的

explain?select?id,name,sales?from?workers?where?name='呂歸塵';

Extra為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)化。

如果你對此感興趣去查閱對應(yīng)的官方文檔和技術(shù)博客。

這次我們簡化來理解，不考慮ICP對數(shù)據(jù)訪問的優(yōu)化，

當(dāng)關(guān)閉ICP時,Index僅僅是data access的一種訪問方式，存儲引擎通過索引回表獲取的數(shù)據(jù)會傳遞到MySQL Server 層進(jìn)行WHERE條件過濾。

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唯一索引';

普通索引

主鍵索引和唯一索引對字段的要求是要求字段為主鍵或unique字段，

而那些建立在普通字段上的索引叫做普通索引，既不要求字段為主鍵也不要求字段為unique。

前綴索引

前綴索引是指對字符類型字段的前幾個字符或?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é)果如下：

雖然詳細(xì)信息當(dāng)中列出了兩條關(guān)于聯(lián)合索引的條目，但并不表示聯(lián)合索引是建立了多個索引，聯(lián)合索引是一個索引結(jié)構(gòu)，這兩個條目表示的是組合索引中字段的具體信息，按建立索引時的書寫順序排序。

同樣我們來看下聯(lián)合索引的B+tree示意圖

MySQL 時間類型 datetime、bigint、timestamp，選哪個？

MySQL 大批量插入，如何過濾掉重復(fù)數(shù)據(jù)？

MySQL 主從復(fù)制解決了什么問題？出現(xiàn)同步延遲如何解決？

MySQL 定時備份數(shù)據(jù)庫（非常全）

MySQL 高頻面試題！