回表

在研究mysql二級(jí)索引的時(shí)候，發(fā)現(xiàn)Mysql回表這個(gè)操作，往下研究了一下

字面意思，找到索引，回到表中找數(shù)據(jù)

解釋一下就是：

先通過索引掃描出數(shù)據(jù)所在的行，再通過行主鍵ID 取出數(shù)據(jù)。

舉個(gè)例子說明：

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

假如age和user_name兩個(gè)字段是個(gè)聯(lián)合索引，我們通過age=18這個(gè)索引找到了二級(jí)索引樹對(duì)應(yīng)頁所在的數(shù)據(jù)，但是由于user_name是模糊查詢，導(dǎo)致了這個(gè)字段的索引失效，我們得到了二級(jí)索引的這一頁中age=18的很多個(gè)數(shù)據(jù)(主鍵id)，我們通過這些主鍵ID回到主鍵索引樹里再查表里的數(shù)據(jù)，這個(gè)操作就是回表。

另外回表的產(chǎn)生也是需要一定條件的，如果一次索引查詢就能獲得所有的select 記錄（也就是聯(lián)合索引已經(jīng)包含了你查的字段）就不需要回表，如果select 所需獲得列中有其他的非索引列，就會(huì)發(fā)生回表動(dòng)作。即基于非主鍵索引的查詢需要多掃描一棵索引樹。

另外上面所說的不需要回表，其實(shí)還有另一個(gè)名詞

覆蓋索引

就是我們需要查詢的數(shù)據(jù)都在二級(jí)索引樹中，直接返回這種情況就叫做覆蓋索引。

上面提到的聯(lián)合索引、二級(jí)索引樹、主鍵索引樹這些名詞，如果同學(xué)們還沒有啥概念，請(qǐng)看我寫的這一篇文章，詳細(xì)介紹了MYSQL的索引
鏈接: MySQL索引詳解及演進(jìn)過程及面試題延伸

索引下推

索引下推（index condition pushdown ）簡(jiǎn)稱ICP，在Mysql5.6以后的版本上推出，用于優(yōu)化回表查詢；

在不使用ICP的情況下，在使用非主鍵索引（又叫普通索引或者二級(jí)索引）進(jìn)行查詢時(shí)，存儲(chǔ)引擎通過索引檢索到數(shù)據(jù)，然后返回給MySQL服務(wù)器，服務(wù)器然后判斷數(shù)據(jù)是否符合條件 ；

在使用ICP的情況下，如果存在某些被索引的列的判斷條件時(shí)，MySQL服務(wù)器將這一部分判斷條件傳遞給存儲(chǔ)引擎，
然后由存儲(chǔ)引擎通過判斷索引是否符合MySQL服務(wù)器傳遞的條件，只有當(dāng)索引符合條件時(shí)才會(huì)將數(shù)據(jù)檢索出來返回給MySQL服務(wù)器 ；

索引條件下推優(yōu)化可以減少存儲(chǔ)引擎查詢基礎(chǔ)表的次數(shù)，也可以減少M(fèi)ySQL服務(wù)器從存儲(chǔ)引擎接收數(shù)據(jù)的次數(shù)。

舉個(gè)栗子：

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 '年紀(jì)',
	`address` varchar(255) DEFAULT NULL COMMENT '地址',
  `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '是否刪除 默認(rèn)否',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用戶信息表';

-- 初始化數(shù)據(jù) 
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, '中國');

用上面的語句建一個(gè)測(cè)試用的表

無索引下推： 查看索引下推的狀態(tài)

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

關(guān)閉索引下推：

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

執(zhí)行sql;

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

分析一下以上sql執(zhí)行的過程：

• 1. idx_name_age`組合索引 遵循最左匹配遇到非等值判斷時(shí)匹配停止，name的范圍查詢則會(huì)使age這個(gè)條件就不會(huì)走索引；
• 2. 會(huì)先在name索引上順序找到 符合條件的name和id數(shù)據(jù)；
• 3. 然后通過id在聚簇索引上回表找到對(duì)應(yīng)的age數(shù)據(jù)，將結(jié)果存放在臨時(shí)表中；
• 4. 最后在臨時(shí)表中通過age條件來篩選數(shù)據(jù)。

以上過程會(huì)掃描4條記錄，回表4次。
extra = Using where：表示優(yōu)化器需要通過索引回表查詢數(shù)據(jù)。

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

set optimizer_switch='index_condition_pushdown=on';

執(zhí)行sql:

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

開啟索引下推優(yōu)化后再分析一下以上sql執(zhí)行的過程：

• 1. 由于開啟了索引下推會(huì)在idx_name_age索引上同時(shí)檢索滿足name和age的條件的數(shù)據(jù)的id；
• 2. 再用id到聚簇索引上查詢完整的數(shù)據(jù)。

以上過程會(huì)掃描4行數(shù)據(jù)，回表次數(shù)是2次。

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

總結(jié)：

• 索引下推功能是mysql 5.6推出優(yōu)化回表的操作，只支持向上兼容，低版本是不支持的；
• 索引下推優(yōu)化的只是回表次數(shù)，掃描行數(shù)還是一樣的。

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

最新評(píng)論