MySQL查詢效能優化索引下推

2022-08-16 14:07:16

前言

前面已經講了MySQL的其他查詢效能優化方式，沒看過可以去了解一下：

MySQL查詢效能優化七種方式索引潛水

MySQL查詢效能優化武器之鏈路追蹤

今天要講的是MySQL的另一種查詢效能優化方式 — 索引下推（Index Condition Pushdown，簡稱ICP），是MySQL5.6版本增加的特性。

1. 索引下推的作用

主要作用有兩個：

減少回表查詢的次數
減少儲存引擎和MySQL Server層的資料傳輸量

總之就是了提升MySQL查詢效能。

2. 案例實踐

建立一張使用者表，造點資料驗證一下：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  `age` tinyint NOT NULL COMMENT '年齡',
  `gender` tinyint NOT NULL COMMENT '性別',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB COMMENT='使用者表';

在姓名和年齡 (name,age) 兩個欄位上建立聯合索引。

查詢SQL執行計劃，驗證一下是否用到索引下推：

explain select * from user where name='一燈' and age>2;

執行計劃中的Extra列顯示了Using index condition，表示用到了索引下推的優化邏輯。

3. 索引下推設定

檢視索引下推的設定：

show variables like '%optimizer_switch%';

如果輸出結果中，顯示 index_condition_pushdown=on，表示開啟了索引下推。

也可以手動開啟索引下推：

set optimizer_switch="index_condition_pushdown=on";

關閉索引下推：

set optimizer_switch="index_condition_pushdown=off";

4. 索引下推原理剖析

索引下推在底層到底是怎麼實現的？

是怎麼減少了回表的次數？

又減少了儲存引擎和MySQL Server層的資料傳輸量？

在沒有使用索引下推的情況，查詢過程是這樣的：

儲存引擎根據where條件中name索引欄位，找到符合條件的3個主鍵ID
然後二次回表查詢，根據這3個主鍵ID去主鍵索引上找到3個整行記錄
把資料返回給MySQL Server層，再根據where中age條件，篩選出符合要求的一行記錄
返回給使用者端

畫兩張圖，就一目瞭然了。

下面這張圖是回表查詢的過程：

先在聯合索引上找到name=‘一燈’的3個主鍵ID
再根據查到3個主鍵ID，去主鍵索引上找到3行記錄

下面這張圖是儲存引擎返回給MySQL Server端的處理過程：

我們再看一下在使用索引下推的情況，查詢過程是這樣的：

儲存引擎根據where條件中name索引欄位，找到符合條件的3行記錄，再用age條件篩選出符合條件一個主鍵ID
然後二次回表查詢，根據這一個主鍵ID去主鍵索引上找到該整行記錄
把資料返回給MySQL Server層
返回給使用者端

現在是不是理解了索引下推的兩個作用：

減少回表查詢的次數
減少儲存引擎和MySQL Server層的資料傳輸量

5. 索引下推應用範圍

適用於InnoDB 引擎和 MyISAM 引擎的查詢
適用於執行計劃是range, ref, eq_ref, ref_or_null的範圍查詢
對於InnoDB表，僅用於非聚簇索引。索引下推的目標是減少全行讀取次數，從而減少 I/O 操作。對於 InnoDB聚集索引，完整的記錄已經讀入InnoDB 緩衝區。在這種情況下使用索引下推不會減少 I/O。
子查詢不能使用索引下推
儲存過程不能使用索引下推

再附一張Explain執行計劃詳解圖：

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