欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間:2022年07月11日 08:48:54   作者:向著百萬年薪努力的小  
這篇文章主要介紹了MySQL 回表,覆蓋索引,索引下推,就是我們需要查詢的數(shù)據(jù)都在二級索引樹中,直接返回這種情況就叫做覆蓋索引

回表

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

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

解釋一下就是:

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

舉個例子說明:

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

假如ageuser_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打開時一閃而過閃退問題的解決

    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-01
  • mysql安全啟動腳本mysqld_safe詳細(xì)介紹

    mysql安全啟動腳本mysqld_safe詳細(xì)介紹

    這篇文章主要介紹了mysql安全啟動腳本mysqld_safe詳細(xì)介紹,mysqld_safe增加了一些安全特性,需要的朋友可以參考下
    2014-07-07
  • mysql 替換字段部分內(nèi)容及mysql 替換函數(shù)replace()

    mysql 替換字段部分內(nèi)容及mysql 替換函數(shù)replace()

    這篇文章主要介紹了mysql 替換字段部分內(nèi)容及mysql 替換函數(shù)replace()的相關(guān)知識,本文給大家介紹的非常詳細(xì),具有一定的參考借鑒價值,需要的朋友參考下吧
    2020-02-02
  • SQL update多表關(guān)聯(lián)更新方法解讀

    SQL update多表關(guān)聯(lián)更新方法解讀

    這篇文章主要介紹了SQL update 多表關(guān)聯(lián)更新方法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
    2024-08-08
  • 深入理解MySQL事務(wù)的4種隔離級別

    深入理解MySQL事務(wù)的4種隔離級別

    本文主要介紹了MySQL事務(wù)的4種隔離級別,事務(wù)的4種隔離級別分別是讀未提交,讀已提交,可重復(fù)讀和串行化,具有一定的參考價值,感興趣的可以了解一下
    2022-04-04
  • MySQL 如何查詢 JSON 數(shù)組是否包含特定的值

    MySQL 如何查詢 JSON 數(shù)組是否包含特定的值

    本文給大家介紹MySQL 如何查詢 JSON 數(shù)組是否包含特定的值,假設(shè)定義了一張表 School,其中字段 stages 為 JSON 類型,本文通過實例代碼給大家詳細(xì)講解,感興趣的朋友一起看看吧
    2023-11-11
  • MySQL中的SUM函數(shù)使用教程

    MySQL中的SUM函數(shù)使用教程

    這篇文章主要介紹了MySQL中的SUM函數(shù)使用教程,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識,需要的朋友可以參考下
    2015-05-05
  • MySQL建表語句基礎(chǔ)及示例詳解

    MySQL建表語句基礎(chǔ)及示例詳解

    在數(shù)據(jù)庫中,創(chuàng)建表格是存儲和組織數(shù)據(jù)的基本操作之一,下面這篇文章主要給大家介紹了關(guān)于MySQL建表語句基礎(chǔ)及示例的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2024-07-07
  • Mysql空間清理的幾種具體方法

    Mysql空間清理的幾種具體方法

    在Mysql環(huán)境下,常常由于數(shù)據(jù)磁盤滿而導(dǎo)致Mysql故障。本文就介紹了幾種清理方法,具有一定的參考價值,感興趣的可以了解一下
    2021-09-09
  • mysql主從復(fù)制的實現(xiàn)步驟

    mysql主從復(fù)制的實現(xiàn)步驟

    本文主要介紹了mysql主從復(fù)制的實現(xiàn)步驟,文中通過示例代碼介紹的非常詳細(xì),具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2021-10-10

最新評論