1、兩類(lèi)索引

為了更好地闡釋這個(gè)問(wèn)題，我們還是從索引來(lái)介紹吧。

InnoDB 索引分為兩大類(lèi)，一類(lèi)是聚集索引(Clustered Index)，一類(lèi)是非聚集索引(Secondary Index)

1.1 聚集索引（聚簇索引）

InnoDB聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄，因此InnoDB必須要有且只有一個(gè)聚集索引。

• 如果表定義了PK（Primary Key，主鍵），那么PK就是聚集索引。

• 如果表沒(méi)有定義PK，則第一個(gè)NOT NULL UNIQUE的列就是聚集索引。

• 否則InnoDB會(huì)另外創(chuàng)建一個(gè)隱藏的ROWID作為聚集索引。

這種機(jī)制使得基于PK的查詢(xún)速度非?？?，因?yàn)橹苯佣ㄎ坏男杏涗洝?/p>

1.2 非聚集索引（普通索引、非聚簇索引、二級(jí)索引）

普通索引也叫二級(jí)索引，除聚簇索引外的索引，即非聚簇索引。

InnoDB的普通索引葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵（聚簇索引）的值，而MyISAM的普通索引存儲(chǔ)的是記錄指針。

Q：為什么非主鍵索引結(jié)構(gòu)葉子結(jié)點(diǎn)存儲(chǔ)的是主鍵值？

A：減少了出現(xiàn)行移動(dòng)或者數(shù)據(jù)頁(yè)分裂時(shí)二級(jí)索引的維護(hù)工作（當(dāng)數(shù)據(jù)需要更新的時(shí)候，二級(jí)索引不需要修改，只需要修改聚簇索引，一個(gè)表只能有一個(gè)聚簇索引，其他的都是二級(jí)索引，這樣只需要修改聚簇索引就可以了，不需要重新構(gòu)建二級(jí)索引）

在使用非聚集索引時(shí)，為了取到具體數(shù)據(jù)，則需要通過(guò)PK回到聚集索引里去查詢(xún)數(shù)據(jù)。這就叫回表查詢(xún)，掃描了2次索引樹(shù)，所以效率相對(duì)較低。

2、應(yīng)用示例

一例勝千言，show me you code！

2.1 建表操作

mysql> create table user(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> sex tinyint(4),
    -> type varchar(8),
    -> primary key (id),
    -> index idx_name (name)
    -> )engine=innodb charset=utf8mb4;

id 字段是聚簇索引，name 字段是普通索引（二級(jí)索引）

2.2 填充數(shù)據(jù)

mysql> select * from user;
+----+--------+------+------+
| id |  name  |  sex | type |
+----+--------+------+------+
| 1 | sj  |  m  |  A  |
| 3 | zs  |  m  |  A  |
| 5 | ls  |  m  |  A  |
| 9 | ww  |  f  |  B  |
+----+-----+-----+-----+

2.3 索引結(jié)構(gòu)

• 聚簇索引（ClusteredIndex）

id 是主鍵，所以是聚簇索引，其葉子節(jié)點(diǎn)存儲(chǔ)的是對(duì)應(yīng)行記錄的數(shù)據(jù)

• 普通索引（secondaryIndex）

name 是普通索引（二級(jí)索引），非聚簇索引，其葉子節(jié)點(diǎn)存儲(chǔ)的是聚簇索引的的值

2.4 查找過(guò)程

• 普通索引查找過(guò)程

如果查詢(xún)條件為主鍵（聚簇索引），則只需掃描一次B+樹(shù)即可通過(guò)聚簇索引定位到要查找的行記錄數(shù)據(jù)。

select * from user where name = 'lisi';

普通索引因?yàn)闊o(wú)法直接定位行記錄，其查詢(xún)過(guò)程在通常情況下是需要掃描兩遍索引樹(shù)的。

實(shí)際執(zhí)行過(guò)程：

路徑需要掃描兩遍索引樹(shù)，第一遍先通過(guò)普通索引定位到主鍵值id=5，然后第二遍再通過(guò)聚集索引定位到具體行記錄。

這就是所謂的回表查詢(xún)，即先定位主鍵值，再根據(jù)主鍵值定位行記錄，性能相對(duì)于只掃描一遍聚集索引樹(shù)的性能要低一些。

3、索引覆蓋

索引覆蓋是一種避免回表查詢(xún)的優(yōu)化策略。

只需要在一棵索引樹(shù)上就能獲取SQL所需的所有列數(shù)據(jù)，無(wú)需回表，速度更快。

3.1 如何實(shí)現(xiàn)覆蓋索引

將要查詢(xún)的數(shù)據(jù)作為索引列建立普通索引（可以是單列索引，也可以一個(gè)索引語(yǔ)句定義所有要查詢(xún)的列，即聯(lián)合索引），這樣的話(huà)就可以直接返回索引中的的數(shù)據(jù)，不需要再通過(guò)聚集索引去定位行記錄，避免了回表的情況發(fā)生。

explain select id, name from user where name = 'lisi';

explain分析：因?yàn)閚ame是普通索引，使用到了name索引，通過(guò)一次掃描B+樹(shù)即可查詢(xún)到相應(yīng)的結(jié)果，這樣就實(shí)現(xiàn)了覆蓋索引

- END -

希望今天的講解對(duì)大家有所幫助，謝謝！

到此這篇關(guān)于一文帶你分清MySQL回表查詢(xún)與索引覆蓋的文章就介紹到這了,更多相關(guān)MySQL回表查詢(xún)與索引覆蓋內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論