MySQL索引下推是什么

2023-06-15 mysql 索引下推

本文小编为大家详细介绍“MySQL索引下推是什么”，内容详细，步骤清晰，细节处理妥当，希望这篇“MySQL索引下推是什么”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。

SELECT 语句执行过程

MySQL

数据库由
Server
层和
Engine
层组成：

```
Server
```
层：有
```
SQL
```
分析器、
```
SQL
```
优化器、
```
SQL
```
执行器，用于负责
```
SQL
```
语句的具体执行过程。
```
Engine
```
层：负责存储具体的数据，如最常使用的
```
InnoDB
```
存储引擎，还有用于在内存中存储临时结果集的
```
TempTable
```
引擎。

MySQL索引下推是什么

通过客户端/服务器通信协议与
```
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

开头、年龄为

的记录

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

有这些记录：

MySQL索引下推是什么

不开启

ICP

时索引扫描是如何进行的：

通过索引元组，定位读取对应数据行。（实际上：就是回表）
对
```
WHERE
```
中字段做判断，过滤掉不满足条件的行。

MySQL索引下推是什么

使用

ICP

，索引扫描如下进行：

获取索引元组。
对
```
WHERE
```
中字段做判断，在索引列中进行过滤。
对满足条件的索引，进行回表查询整行。
对
```
WHERE
```
中字段做判断，过滤掉不满足条件的行。

MySQL索引下推是什么

动手实验：

实验：使用

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;

新增数据如下：

MySQL索引下推是什么

关闭
```
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;

MySQL索引下推是什么

开启
```
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;

MySQL索引下推是什么

由上实验可知，区别是否开启

ICP

：

Exira

字段中的

Using index condition

MySQL索引下推是什么

更进一步，来看下

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 '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          | 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索引下推是什么

如何使用SQL语句在MongoDB中进行索引创建和优化？

如何使用SQL语句在MySQL中进行数据索引和优化？

MSSQL中建立索引的简单语句教程（mssql 建索引语句）

MSSQL2000数据库重建索引：流程与操作（mssql200重建索引）

如何快捷地用MSSQL删除表索引（mssql删除表索引）

MySQL特殊函数如何使用

MSSQL中增加索引带来性能提升（mssql 增加索引）

用MSSQL创建聚集索引提升查询性能（mssql 创建聚集索引）

MSSQL查看索引：快速找到您需要的信息（mssql 查看索引）

MSSQL表中索引的魔力（mssql 表索引）

的表MSSQL导入索引表：保障你的数据安全（mssql 导入带索引）

数据库索引：用哪种方法建立？ (数据库索引用什么建的)

索引创建失败，原因竟是缺少左括号？！ (数据库表创建索引时缺少左括号)

如何制作MySQL数据库？ (制作mysql数据库)

如何使用MySQL进行数据库连接？

如何在MySQL中使用索引来提高查询性能？

如何在MySQL中进行多表查询？

Redis与Golang的数据结构操作：如何高效地存储和索引数据

利用MySQL的DATE_FORMAT函数自定义日期和时间的显示格式

支持GIS数据的MySQL存储引擎：InnoDB中的空间索引优化