前言

前面已經(jīng)講了MySQL的其他查詢性能優(yōu)化方式，沒(méi)看過(guò)可以去了解一下：

MySQL查詢性能優(yōu)化七種方式索引潛水

MySQL查詢性能優(yōu)化武器之鏈路追蹤

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

1. 索引下推的作用

主要作用有兩個(gè)：

• 減少回表查詢的次數(shù)
• 減少存儲(chǔ)引擎和MySQL Server層的數(shù)據(jù)傳輸量

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

2. 案例實(shí)踐

創(chuàng)建一張用戶表，造點(diǎn)數(shù)據(jù)驗(yàn)證一下：

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) 兩個(gè)字段上創(chuàng)建聯(lián)合索引。

查詢SQL執(zhí)行計(jì)劃，驗(yàn)證一下是否用到索引下推：

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

執(zhí)行計(jì)劃中的Extra列顯示了Using index condition，表示用到了索引下推的優(yōu)化邏輯。

3. 索引下推配置

查看索引下推的配置：

show variables like '%optimizer_switch%';

如果輸出結(jié)果中，顯示 index_condition_pushdown=on，表示開(kāi)啟了索引下推。

也可以手動(dòng)開(kāi)啟索引下推：

set optimizer_switch="index_condition_pushdown=on";

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

set optimizer_switch="index_condition_pushdown=off";

4. 索引下推原理剖析

索引下推在底層到底是怎么實(shí)現(xiàn)的？

是怎么減少了回表的次數(shù)？

又減少了存儲(chǔ)引擎和MySQL Server層的數(shù)據(jù)傳輸量？

在沒(méi)有使用索引下推的情況，查詢過(guò)程是這樣的：

• 存儲(chǔ)引擎根據(jù)where條件中name索引字段，找到符合條件的3個(gè)主鍵ID
• 然后二次回表查詢，根據(jù)這3個(gè)主鍵ID去主鍵索引上找到3個(gè)整行記錄
• 把數(shù)據(jù)返回給MySQL Server層，再根據(jù)where中age條件，篩選出符合要求的一行記錄
• 返回給客戶端

畫(huà)兩張圖，就一目了然了。

下面這張圖是回表查詢的過(guò)程：

• 先在聯(lián)合索引上找到name=‘一燈’的3個(gè)主鍵ID
• 再根據(jù)查到3個(gè)主鍵ID，去主鍵索引上找到3行記錄

下面這張圖是存儲(chǔ)引擎返回給MySQL Server端的處理過(guò)程：

我們?cè)倏匆幌略谑褂盟饕峦频那闆r，查詢過(guò)程是這樣的：

• 存儲(chǔ)引擎根據(jù)where條件中name索引字段，找到符合條件的3行記錄，再用age條件篩選出符合條件一個(gè)主鍵ID
• 然后二次回表查詢，根據(jù)這一個(gè)主鍵ID去主鍵索引上找到該整行記錄
• 把數(shù)據(jù)返回給MySQL Server層
• 返回給客戶端

現(xiàn)在是不是理解了索引下推的兩個(gè)作用：

• 減少回表查詢的次數(shù)
• 減少存儲(chǔ)引擎和MySQL Server層的數(shù)據(jù)傳輸量

5. 索引下推應(yīng)用范圍

• 適用于InnoDB 引擎和 MyISAM 引擎的查詢
• 適用于執(zhí)行計(jì)劃是range, ref, eq_ref, ref_or_null的范圍查詢
• 對(duì)于InnoDB表，僅用于非聚簇索引。索引下推的目標(biāo)是減少全行讀取次數(shù)，從而減少 I/O 操作。對(duì)于 InnoDB聚集索引，完整的記錄已經(jīng)讀入InnoDB 緩沖區(qū)。在這種情況下使用索引下推 不會(huì)減少 I/O。
• 子查詢不能使用索引下推
• 存儲(chǔ)過(guò)程不能使用索引下推

再附一張Explain執(zhí)行計(jì)劃詳解圖：

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