MySQL索引下推需要了解下

前言

本文围绕这三个话题来学习索引下推：

• ​​SELECT​​ 语句执行过程
• 什么是索引下推？
• 索引下推限制

​​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 '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'