MySQL回表查詢與索引覆寫的差異是什麼-mysql教程-PHP中文網

首頁

資料庫

mysql教程

MySQL回表查詢與索引覆寫的差異是什麼

王林

Jun 03, 2023 pm 02:22 PM

mysql

回表查詢

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

聚集索引（叢集索引）：葉子節點中存的是整行數據，找到索引也就找到了數據，索引即數據，表中行的物理順序與鍵值的邏輯（索引）順序相同，一個表只能包含一個聚集索引。因為索引（目錄）只能按照一種方法進行排序。

非聚集索引（普通索引、非聚集索引、二級索引）：非聚集索引的btree葉子節點中儲存的是當行資料的PK（主鍵）。例如MYISAM透過key_buffer把索引先快取到記憶體中，當需要存取資料時(透過索引存取資料)，在記憶體中直接搜尋索引，然後透過索引找到磁碟對應數據，這也就是為什麼索引不在key buffer命中時，速度慢的原因。

為什麼非主鍵索引結構葉子結點儲存的是主鍵值？

減少了出現行移動或資料頁分割時二級索引的維護工作（當資料需要更新的時候，二級索引不需要修改，只需要修改叢集索引，一個表只能有一個叢集索引，其他的都是二級索引，這樣只需要修改叢集索引就可以了，不需要重新建構二級索引）

當使用非聚集索引時，為了取得具體數據，我們需要透過主鍵返回到聚集索引並查詢數據。著就叫回表查詢。掃描了2次索引樹。所以效率相對較低。

索引覆寫

索引覆寫就是解決回表查詢的一種方案。見名知意，就是查詢的所有列都被所使用的索引列覆蓋(可以是單列索引也可以是聯合索引，通常是聯合索引，單列索引很難覆蓋查詢的所有列)。

因為索引中已經包含了要查詢的欄位的值，因此查詢的時候直接傳回索引中的欄位值就可以了，不需要再到表中查詢，避免了對主鍵索引的二次查詢，也就提高了查詢的效率。

id為聚集索引，name為非聚集索引：

select name, age from t where name = &#39;lcc&#39;;

就需要回表查詢

索引覆寫：

在SQL中只查詢name字段。這樣name的索引就覆蓋到了所有的查詢欄位。

select name  from t where name = &#39;lcc&#39;;

將name的索引修改為聯合索引(name, age )，之後還是執行select name, age from t where name = 'lcc'。這樣也覆蓋到了所有的查詢列。
因為覆寫索引必須要儲存索引的資料列值，而雜湊索引、空間索引和全文索引等都不儲存索引列值，因此只有使用B-Tree索引的資料可以做覆蓋索引。

進行索引覆寫查詢時，在explain（執行計畫）的Extra欄位可以看到【Using Index】的資訊。

索引覆寫的優點

索引條目通常遠小於資料行的大小，因為覆寫索引只需要讀取索引，大幅減少了資料的存取量。
索引是按照列值順序儲存的，對於IO密集的範圍查找會比隨機從磁碟讀取每一行資料的IO小得多。
有些儲存引擎例如MyISAM在記憶體中只快取索引，資料則依賴作業系統來緩存，因此要存取資料的話需要一次系統調用，使用覆蓋索引則避免了這一點。
針對InnoDB引擎下的資料庫表，覆寫索引因為InnoDB的叢集索引而非常實用。因為InnoDB的二級索引在葉子節點中保存了行的主鍵值，如果二級索引能夠覆蓋查詢，就避免了對主鍵索引的二次查詢。

哪些場景適合使用索引覆寫來最佳化SQL

#當不需要查詢整行記錄時；

#全表count查詢優化；

Limit分頁查詢；
表格記錄太少

經常增刪改的表格或欄位（如使用者餘額）

#Where條件裡用不到的字段不創建索引

過濾性不好的不適合建索引（如性別）

索引下推

索引下推優化是MySQL 5.6 引入的，可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數
建立聯合索引：

KEY `username` (`name`,`age`) )

執行：
```
select * from user2 where name like &#39;j%&#39; and age=99;
```
上面的查詢sql符合索引的最左前綴原則，所以將會用到username 索引####### 5.5中上面這個SQL 的執行流程是這樣的：############首先MySQL 的server 層呼叫儲存引擎取得第一個以j 開頭的username。 ############儲存引擎找到username=‘j’ 的第一筆記錄後，在B Tree 的葉子結點中保存主鍵id，此時透過回表操作，去主鍵索引中找到該筆記錄的完整數據，並傳回給server 層。 ###
server 層拿到資料之後，判斷該筆記錄的age 是否為99，如果age=99，就把該筆記錄回傳給客戶端，如果age!=99，那就丟棄該記錄。

5.6中上面這個SQL 的執行流程是這樣的：