欧美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 '模糊查%';

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

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

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

覆蓋索引

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

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

索引下推

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

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

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

索引條件下推優(yōu)化可以減少存儲引擎查詢基礎表的次數(shù)，也可以減少MySQL服務器從存儲引擎接收數(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 '年紀',
	`address` varchar(255) DEFAULT NULL COMMENT '地址',
  `is_delete` tinyint NOT NULL DEFAULT '0' COMMENT '是否刪除 默認否',
  `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

關閉索引下推：

索引下推是mysql 5.6優(yōu)化查詢回表的功能，在5.6之前都不支持索引下推，筆者用的8.0，則需要先關閉索引下推：
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在聚簇索引上回表找到對應的age數(shù)據(jù)，將結果存放在臨時表中；
  • 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 表示索引下推。

總結：

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

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

相關文章

  • MySQL數(shù)據(jù)庫優(yōu)化技術之配置技巧總結

    MySQL數(shù)據(jù)庫優(yōu)化技術之配置技巧總結

    這篇文章主要介紹了MySQL數(shù)據(jù)庫優(yōu)化技術之配置技巧,較為詳細的總結分析了MySQL進行硬件級軟件優(yōu)化的相關方法與注意事項,需要的朋友可以參考下
    2016-07-07
  • MySQL主從復制原理詳情

    MySQL主從復制原理詳情

    這篇文章主要介紹了MySQL主從復制原理詳情，MySQL?主從復制是指數(shù)據(jù)可以從一個MySQL數(shù)據(jù)庫服務器主節(jié)點復制到一個或多個從節(jié)點，文章圍繞主題展開詳細的內(nèi)容介紹。感興趣的小伙伴可以參考一下
    2022-06-06
  • mysql?子查詢的概述和分類及單行子查詢功能實現(xiàn)

    mysql?子查詢的概述和分類及單行子查詢功能實現(xiàn)

    本文詳細介紹了MySQL的子查詢概念和應用,解釋了子查詢是在主查詢中嵌套另一個查詢,包括外查詢和內(nèi)查詢,并從多個角度進行分類,文章還深入探討了子查詢的編寫技巧和使用場景,對于學習和應用MySQL的人來說,這是一篇非常有價值的指南
    2024-10-10
  • MySQL筆記之修改數(shù)據(jù)的解決方法

    MySQL筆記之修改數(shù)據(jù)的解決方法

    本篇文章介紹了，在mysql中修改數(shù)據(jù)的解決方法。需要的朋友參考下
    2013-05-05
  • mysql 超大數(shù)據(jù)/表管理技巧

    mysql 超大數(shù)據(jù)/表管理技巧

    在實際應用中經(jīng)過存儲、優(yōu)化可以做到在超過9千萬數(shù)據(jù)中的查詢響應速度控制在1到20毫秒?？瓷先ナ莻€不錯的成績，不過優(yōu)化這條路沒有終點，當我們的系統(tǒng)有超過幾百人、上千人同時使用時，仍然會顯的力不從心
    2013-03-03
  • MySQL中update語法的使用記錄

    MySQL中update語法的使用記錄

    在MySQL中,UPDATE?語句用于修改已存在的表中的記錄,下面通過示例介紹MySQL中update語法的使用記錄,感興趣的朋友一起看看吧
    2024-07-07
  • window下mysql 8.0.15 安裝配置方法圖文教程

    window下mysql 8.0.15 安裝配置方法圖文教程

    這篇文章主要為大家詳細介紹了window下mysql 8.0.15 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
    2019-02-02
  • docker 部署mysql詳細過程(docker部署常見應用)

    docker 部署mysql詳細過程(docker部署常見應用)

    這篇文章主要介紹了docker 部署mysql之docker部署常見應用，本文以docker部署mysql5.7.26為例，通過實例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
    2021-08-08
  • Mysql主從GTID與binlog的區(qū)別及說明

    Mysql主從GTID與binlog的區(qū)別及說明

    MySQL GTID（全局事務標識符）與binlog（二進制日志）是搭建主從復制的兩種機制,GTID為每個事務分配唯一標識,確保復制無數(shù)據(jù)沖突或丟失,便于復制配置和管理；binlog記錄所有數(shù)據(jù)庫更改,用于數(shù)據(jù)恢復和復制,GTID基于事務標識
    2024-10-10
  • mysql實現(xiàn)sequence功能的代碼

    mysql實現(xiàn)sequence功能的代碼

    今天小編就為大家分享一篇關于mysql實現(xiàn)sequence功能的代碼，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧
    2019-03-03

最新評論