一文搞懂MySQL索引下推

這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於索引下推的相關內容，索引條件下推也叫索引下推，英文全稱Index Condition Pushdown ，簡稱ICP，用於優化資料查詢，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql影片教學

#SELECT 語句執行過程

MySQL 資料庫由Server 層和Engine 層組成：

• Server 層： 有SQL 分析器、SQL 最佳化器、SQL 執行器，用於負責SQL 語句的特定執行過程。
• Engine 層： 負責儲存特定的數據，如最常使用的InnoDB 儲存引擎，還有用於在記憶體中儲存臨時結果集的TempTable 引擎。

• 透過客戶端/伺服器通訊協定與 MySQL 建立連線。

• 查詢快取：

  • 如果開啟了Query Cache 且在查詢快取過程中查詢到完全相同的SQL 語句，則將查詢結果直接傳回給客戶端；
  • 如果沒有開啟Query Cache 或沒有查詢到完全相同的SQL 語句則會由解析器進行語法語意解析，並產生解析樹。

• 分析器產生新的解析樹。

• 查詢最佳化器產生執行計劃。

• 查詢執行引擎執行SQL 語句，此時查詢執行引擎會根據SQL 語句中資料表的儲存引擎類型，以及對應的API 介面與底層儲存引擎快取或實體檔案的互動情況，得到查詢結果，由MySQL Server 過濾後將查詢結果快取並傳回給客戶端。

  若開啟了Query Cache，這時也會將SQL 語句和結果完整地儲存到Query Cache 中，以後若有相同的SQL 語句執行則直接傳回結果。

Tips：MySQL 8.0 已移除 query cache（查詢快取模組）。

因為查詢快取的命中率會非常低。查詢快取的失效非常頻繁：只要有一個表格的更新，這個表上所有的查詢快取都會被清空。

什麼是索引下推？

索引下推(Index Condition Pushdown)： 簡稱ICP，透過把索引過濾條件推到儲存引擎，來減少MySQL 儲存引擎存取基底表的次數和MySQL 服務層存取儲存引擎的次數。

索引下推VS 覆蓋索引： 其實都是減少回表的次數，只不過方式不同

• 覆寫索引： 當索引中包含所需的欄位（SELECT XXX），則不再回表去查詢欄位。

• 索引下推： 對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表的行數。

要了解ICP 是如何運作的，先從一個查詢SQL# 開始：

舉個栗子：查詢名字la 開頭、年齡為18 的記錄

SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

有這些記錄：

不開啟ICP 時索引掃描是如何進行的：

• 透過索引元組，定位讀取對應數據行。 （實際上：就是回表）
• 對 WHERE 中字段做判斷，過濾掉不滿足條件的行。

使用ICP，索引掃描如下：

## 取得索引元組。
• 對
• WHERE 中欄位做判斷，在索引列中進行篩選。
對滿足條件的索引，進行回表查詢整行。
• 對
• WHERE 中欄位做判斷，過濾掉不符合條件的行。

#

动手实验：

实验：使用 MySQL 版本 8.0.16

-- 表创建
CREATE TABLE IF NOT EXISTS `user` (
`id` VARCHAR(64) NOT NULL COMMENT '主键 id',
`name` VARCHAR(50) NOT NULL COMMENT '名字',
`age` TINYINT NOT NULL COMMENT '年龄',
`address` VARCHAR(100) NOT NULL COMMENT '地址',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '用户表';

-- 创建索引
CREATE INDEX idx_name_age ON user (name, age);

-- 新增数据
INSERT INTO user (id, name, age, address) VALUES (1, 'tt', 14, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (2, 'lala', 18, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (3, 'laxi', 30, 'linhai');
INSERT INTO user (id, name, age, address) VALUES (4, 'lawa', 40, 'linhai');

-- 查询语句
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

新增数据如下：

• 关闭 ICP，再调用 EXPLAIN 查看语句：

-- 将 ICP 关闭
SET optimizer_switch = 'index_condition_pushdown=off';
-- 查看确认
show variables like 'optimizer_switch';

-- 用 EXPLAIN 查看
EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

• 开启 ICP，再调用 EXPLAIN 查看语句：

-- 将 ICP 打开
SET optimizer_switch = 'index_condition_pushdown=on';
-- 查看确认
show variables like 'optimizer_switch';

-- 用 EXPLAIN 查看
EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;

由上实验可知，区别是否开启 ICP： Exira 字段中的 Using index condition

更进一步，来看下 ICP 带来的性能提升：

通过访问数据文件的次数

-- 1. 清空 status 状态
flush status;
-- 2. 查询
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
-- 3. 查看 handler 状态
show status like '%handler%';

对比开启 ICP 和 关闭 ICP： 关注 Handler_read_next 的值

-- 开启 ICP
flush status;
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
show status like '%handler%';
+----------------------------|-------+
| Variable_name              | Value |
+----------------------------|-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |  
| Handler_read_last          | 0     |
| Handler_read_next          | 1     |  <---重点
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------|-------+
18 rows in set (0.00 sec)


-- 关闭 ICP
flush status;
SELECT * FROM user WHERE name LIKE &#39;la%&#39; AND age = 18;
show status like '%handler%';
+----------------------------|-------+
| Variable_name              | Value |
+----------------------------|-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 3     |  <---重点
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------|-------+
18 rows in set (0.00 sec)

由上实验可知：

• 开启 ICP：Handler_read_next 等于 1，回表查 1 次。
• 关闭 ICP：Handler_read_next 等于 3，回表查 3 次。

这实验跟上面的栗子就对应上了。

索引下推限制

根据官网可知，索引下推 受以下条件限制：

• 当需要访问整个表行时，ICP 用于 range、 ref、 eq_ref 和 ref_or_null

• ICP可以用于 InnoDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表。

• 对于 InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用 ICP 不会减少 I/O。

• 在虚拟生成列上创建的二级索引不支持 ICP。InnoDB 支持虚拟生成列的二级索引。

• 引用子查询的条件不能下推。

• 引用存储功能的条件不能被按下。存储引擎不能调用存储的函数。

• 触发条件不能下推。

• 不能将条件下推到包含对系统变量的引用的派生表。（MySQL 8.0.30 及更高版本)。

小结下：

• ICP 仅适用于 二级索引。
• ICP 目标是 减少回表查询。
• ICP 对联合索引的部分列模糊查询非常有效。

拓展：虚拟列

CREATE TABLE UserLogin (
userId BIGINT,
loginInfo JSON,
cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
PRIMARY KEY(userId),
UNIQUE KEY idx_cellphone(cellphone)
);

列 cellphone ：就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_cellphone 实质是一个函数索引。

好处： 在写 SQL 时可以直接使用这个虚拟列，而不用写冗长的函数。

举个栗子： 查询手机号

-- 不用虚拟列
SELECT * FROM UserLogin WHERE loginInfo->>"$.cellphone" = '13988888888'

-- 使用虚拟列
SELECT * FROM UserLogin WHERE cellphone = '13988888888'

推荐学习：mysql视频教程

以上是一文搞懂MySQL索引下推的詳細內容。更多資訊請關注PHP中文網其他相關文章！