MySQL 回表,覆蓋索引,索引下推

2022-07-11 10:00:21

回表

在研究mysql二級索引的時候，發現Mysql回表這個操作，往下研究了一下

字面意思，找到索引，回到表中找資料

解釋一下就是：

先通過索引掃描出資料所在的行，再通過行主鍵ID 取出資料。

舉個例子說明：

SELECT * FROM INNODB_USER
WHERE AGE = 18 AND USER_NAME LIKE '模糊查%';

假如age和user_name兩個欄位是個聯合索引，我們通過age=18這個索引找到了二級索引樹對應頁所在的資料，但是由於user_name是模糊查詢，導致了這個欄位的索引失效，我們得到了二級索引的這一頁中age=18的很多個資料(主鍵id)，我們通過這些主鍵ID回到主鍵索引樹裡再查表裡的資料，這個操作就是回表。

另外回表的產生也是需要一定條件的，如果一次索引查詢就能獲得所有的select 記錄（也就是聯合索引已經包含了你查的欄位）就不需要回表，如果select 所需獲得列中有其他的非索引列，就會發生回表動作。即基於非主鍵索引的查詢需要多掃描一棵索引樹。

另外上面所說的不需要回表，其實還有另一個名詞

覆蓋索引

就是我們需要查詢的資料都在二級索引樹中，直接返回這種情況就叫做覆蓋索引。

上面提到的聯合索引、二級索引樹、主鍵索引樹這些名詞，如果同學們還沒有啥概念，請看我寫的這一篇文章，詳細介紹了MYSQL的索引
連結: MySQL索引詳解及演進過程及面試題延伸

索引下推

索引下推（index condition pushdown ）簡稱ICP，在Mysql5.6以後的版本上推出，用於優化回表查詢；

在不使用ICP的情況下，在使用非主鍵索引（又叫普通索引或者二級索引）進行查詢時，儲存引擎通過索引檢索到資料，然後返回給MySQL伺服器，伺服器然後判斷資料是否符合條件；

在使用ICP的情況下，如果存在某些被索引的列的判斷條件時，MySQL伺服器將這一部分判斷條件傳遞給儲存引擎，
然後由儲存引擎通過判斷索引是否符合MySQL伺服器傳遞的條件，只有當索引符合條件時才會將資料檢索出來返回給MySQL伺服器；

索引條件下推優化可以減少儲存引擎查詢基礎表的次數，也可以減少MySQL伺服器從儲存引擎接收資料的次數。

舉個栗子：

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(64) NOT NULL COMMENT '使用者名稱 ',
  `age` int(8) NOT NULL COMMENT '年紀',
	`address` varchar(255) DEFAULT NULL COMMENT '地址',
  `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '是否刪除 預設否',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='使用者資訊表';

-- 初始化資料 
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李四', 22, '中國');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李五', 22, '中國');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李六', 23, '中國');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('張三', 24, '中國');
INSERT INTO `t_user`(`name`, `age`, `address`) VALUES ('李期', 24, '中國');

用上面的語句建一個測試用的表

無索引下推：檢視索引下推的狀態

show VARIABLES like '%optimizer_switch%';
-------------------------------------------------------
optimizer_switch	index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on

關閉索引下推：

索引下推是mysql 5.6優化查詢回表的功能，在5.6之前都不支援索引下推，筆者用的8.0，則需要先關閉索引下推：
set optimizer_switch='index_condition_pushdown=off';

執行sql;

EXPLAIN SELECT * from t_user where name like '李%' and age = 22;

分析一下以上sql執行的過程：

1. idx_name_age`組合索引遵循最左匹配遇到非等值判斷時匹配停止，name的範圍查詢則會使age這個條件就不會走索引；
2. 會先在name索引上順序找到符合條件的name和id資料；
3. 然後通過id在聚簇索引上回表找到對應的age資料，將結果存放在臨時表中；
4. 最後在臨時表中通過age條件來篩選資料。

以上過程會掃描4條記錄，回表4次。
extra = Using where：表示優化器需要通過索引回表查詢資料。

有索引下推：開啟索引下推

set optimizer_switch='index_condition_pushdown=on';

執行sql:

EXPLAIN SELECT * from t_user where name like '李%' and age = 22;

開啟索引下推優化後再分析一下以上sql執行的過程：

1. 由於開啟了索引下推會在idx_name_age索引上同時檢索滿足name和age的條件的資料的id；
2. 再用id到聚簇索引上查詢完整的資料。

以上過程會掃描4行資料，回表次數是2次。

extra = Using index condition 表示索引下推。

總結：

索引下推功能是mysql 5.6推出優化回表的操作，只支援向上相容，低版本是不支援的；
索引下推優化的只是回表次數，掃描行數還是一樣的。

到此這篇關於MySQL 回表,覆蓋索引,索引下推的文章就介紹到這了,更多相關MySQL 回表內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL 回表,覆蓋索引,索引下推

目錄

回表

覆蓋索引

索引下推

無索引下推：檢視索引下推的狀態

有索引下推：開啟索引下推

熱門文章

MySQL 回表,覆蓋索引,索引下推

目錄

回表

覆蓋索引

索引下推

無索引下推： 檢視索引下推的狀態

有索引下推： 開啟索引下推

熱門文章

無索引下推：檢視索引下推的狀態

有索引下推：開啟索引下推