還傻傻分不清MySQL回表查詢與索引覆蓋？

ITPUB社群發表於2023-02-20
MySql索引

最近的工作中，遇到一個查詢裡用到主鍵索引與二級索引並存的問題情況，那對於這種情況，索引是如何高效執行的，是否會產生回表查詢呢？

等等，首先解釋一下，什麼是回表？
回表定義：先索引掃描，再透過ID去取索引中未能提供的資料，即為回表。
即先定位主鍵值，再定位行記錄。

還傻傻分不清MySQL回表查詢與索引覆蓋？


1、兩類索引






為了更好地闡釋這個問題，我們還是從索引來介紹吧。
InnoDB 索引分為兩大類，一類是聚集索引(Clustered Index)，一類是非聚集索引(Secondary Index)

1.1 聚集索引（聚簇索引）

InnoDB聚集索引的葉子節點儲存行記錄，因此InnoDB必須要有且只有一個聚集索引。

  • 如果表定義了PK（Primary Key，主鍵），那麼PK就是聚集索引。
  • 如果表沒有定義PK，則第一個NOT NULL UNIQUE的列就是聚集索引。
  • 否則InnoDB會另外建立一個隱藏的ROWID作為聚集索引。

這種機制使得基於PK的查詢速度非常快，因為直接定位的行記錄。

1.2 非聚集索引（普通索引、非聚簇索引、二級索引）

普通索引也叫二級索引，除聚簇索引外的索引，即非聚簇索引。
InnoDB的普通索引葉子節點儲存的是主鍵（聚簇索引）的值，而MyISAM的普通索引儲存的是記錄指標。

Q：為什麼非主鍵索引結構葉子結點儲存的是主鍵值？
A：減少了出現行移動或者資料頁分裂時二級索引的維護工作（當資料需要更新的時候，二級索引不需要修改，只需要修改聚簇索引，一個表只能有一個聚簇索引，其他的都是二級索引，這樣只需要修改聚簇索引就可以了，不需要重新構建二級索引）
在使用非聚集索引時，為了取到具體資料，則需要透過PK回到聚集索引裡去查詢資料。這就叫回表查詢，掃描了2次索引樹，所以效率相對較低。

2、應用示例






一例勝千言，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 欄位是普通索引（二級索引）


2.2 填充資料

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 索引結構

  • 聚簇索引（ClusteredIndex）
id 是主鍵，所以是聚簇索引，其葉子節點儲存的是對應行記錄的資料

還傻傻分不清MySQL回表查詢與索引覆蓋？


  • 普通索引（secondaryIndex）
name 是普通索引（二級索引），非聚簇索引，其葉子節點儲存的是聚簇索引的的值

還傻傻分不清MySQL回表查詢與索引覆蓋？

2.4 查詢過程

  • 普通索引查詢過程
如果查詢條件為主鍵（聚簇索引），則只需掃描一次B+樹即可透過聚簇索引定位到要查詢的行記錄資料。


select * from user where name = 'lisi';

普通索引因為無法直接定位行記錄，其查詢過程在通常情況下是需要掃描兩遍索引樹的。

實際執行過程：

還傻傻分不清MySQL回表查詢與索引覆蓋？

路徑需要掃描兩遍索引樹，第一遍先透過普通索引定位到主鍵值id=5，然後第二遍再透過聚集索引定位到具體行記錄。
這就是所謂的回表查詢，即先定位主鍵值，再根據主鍵值定位行記錄，效能相對於只掃描一遍聚集索引樹的效能要低一些。

3、索引覆蓋






索引覆蓋是一種避免回表查詢的最佳化策略。
只需要在一棵索引樹上就能獲取SQL所需的所有列資料，無需回表，速度更快。

3.1 如何實現覆蓋索引

將要查詢的資料作為索引列建立普通索引（可以是單列索引，也可以一個索引語句定義所有要查詢的列，即聯合索引），這樣的話就可以直接返回索引中的的資料，不需要再透過聚集索引去定位行記錄，避免了回表的情況發生。

explain select id, name from user where name = 'lisi';
explain分析：因為name是普通索引，使用到了name索引，透過一次掃描B+樹即可查詢到相應的結果，這樣就實現了覆蓋索引

還傻傻分不清MySQL回表查詢與索引覆蓋？


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70024420/viewspace-2935962/，如需轉載，請註明出處，否則將追究法律責任。

相關文章