MySQL 回表,覆蓋索引,索引下推
回表
在研究mysql二級索引的時候,發(fā)現(xiàn)Mysql回表這個操作,往下研究了一下
字面意思,找到索引,回到表中找數(shù)據(jù)
解釋一下就是:
先通過索引掃描出數(shù)據(jù)所在的行,再通過行主鍵ID 取出數(shù)據(jù)。
舉個例子說明:
SELECT * FROM INNODB_USER WHERE AGE = 18 AND USER_NAME LIKE '模糊查%';
假如age
和user_name
兩個字段是個聯(lián)合索引,我們通過age
=18這個索引找到了二級索引樹對應(yīng)頁所在的數(shù)據(jù),但是由于user_name
是模糊查詢,導(dǎo)致了這個字段的索引失效,我們得到了二級索引的這一頁中age
=18的很多個數(shù)據(jù)(主鍵id),我們通過這些主鍵ID回到主鍵索引樹里再查表里的數(shù)據(jù),這個操作就是回表。
另外回表的產(chǎn)生也是需要一定條件的,如果一次索引查詢就能獲得所有的select 記錄(也就是聯(lián)合索引已經(jīng)包含了你查的字段)就不需要回表,如果select 所需獲得列中有其他的非索引列,就會發(fā)生回表動作。即基于非主鍵索引的查詢需要多掃描一棵索引樹。
另外上面所說的不需要回表,其實還有另一個名詞
覆蓋索引
就是我們需要查詢的數(shù)據(jù)都在二級索引樹中,直接返回這種情況就叫做覆蓋索引。
上面提到的聯(lián)合索引、二級索引樹、主鍵索引樹這些名詞,如果同學(xué)們還沒有啥概念,請看我寫的這一篇文章,詳細(xì)介紹了MYSQL的索引
鏈接: MySQL索引詳解及演進(jìn)過程及面試題延伸
索引下推
索引下推(index condition pushdown )簡稱ICP,在Mysql5.6以后的版本上推出,用于優(yōu)化回表查詢;
在不使用ICP的情況下,在使用非主鍵索引(又叫普通索引或者二級索引)進(jìn)行查詢時,存儲引擎通過索引檢索到數(shù)據(jù),然后返回給MySQL服務(wù)器,服務(wù)器然后判斷數(shù)據(jù)是否符合條件 ;
在使用ICP的情況下,如果存在某些被索引的列的判斷條件時,MySQL服務(wù)器將這一部分判斷條件傳遞給存儲引擎,
然后由存儲引擎通過判斷索引是否符合MySQL服務(wù)器傳遞的條件,只有當(dāng)索引符合條件時才會將數(shù)據(jù)檢索出來返回給MySQL服務(wù)器 ;
索引條件下推優(yōu)化可以減少存儲引擎查詢基礎(chǔ)表的次數(shù),也可以減少MySQL服務(wù)器從存儲引擎接收數(shù)據(jù)的次數(shù)。
舉個栗子:
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)建時間', 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, '中國');
用上面的語句建一個測試用的表
無索引下推: 查看索引下推的狀態(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`組合索引 遵循最左匹配遇到非等值判斷時匹配停止,name的范圍查詢則會使age這個條件就不會走索引;
- 2. 會先在name索引上順序找到 符合條件的name和id數(shù)據(jù);
- 3. 然后通過id在聚簇索引上回表找到對應(yīng)的age數(shù)據(jù),將結(jié)果存放在臨時表中;
- 4. 最后在臨時表中通過age條件來篩選數(shù)據(jù)。
以上過程會掃描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. 由于開啟了索引下推會在idx_name_age索引上同時檢索滿足name和age的條件的數(shù)據(jù)的id;
- 2. 再用id到聚簇索引上查詢完整的數(shù)據(jù)。
以上過程會掃描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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL 8.2 Command Line Client打開時一閃而過閃退問題
MySQL8.2安裝成功后,發(fā)現(xiàn)打開MySQL 8.0 Command Line Client時出現(xiàn)一閃而過,打不開的情況,所以下面這篇文章主要給大家介紹了關(guān)于MySQL 8.2 Command Line Client打開時一閃而過閃退問題的解決,需要的朋友可以參考下2024-01-01mysql安全啟動腳本mysqld_safe詳細(xì)介紹
這篇文章主要介紹了mysql安全啟動腳本mysqld_safe詳細(xì)介紹,mysqld_safe增加了一些安全特性,需要的朋友可以參考下2014-07-07mysql 替換字段部分內(nèi)容及mysql 替換函數(shù)replace()
這篇文章主要介紹了mysql 替換字段部分內(nèi)容及mysql 替換函數(shù)replace()的相關(guān)知識,本文給大家介紹的非常詳細(xì),具有一定的參考借鑒價值,需要的朋友參考下吧2020-02-02SQL update多表關(guān)聯(lián)更新方法解讀
這篇文章主要介紹了SQL update 多表關(guān)聯(lián)更新方法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08MySQL 如何查詢 JSON 數(shù)組是否包含特定的值
本文給大家介紹MySQL 如何查詢 JSON 數(shù)組是否包含特定的值,假設(shè)定義了一張表 School,其中字段 stages 為 JSON 類型,本文通過實例代碼給大家詳細(xì)講解,感興趣的朋友一起看看吧2023-11-11