一篇文章带你了解MySQL索引下推

一篇文章带你了解MySQL索引下推

前言

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

SELECT 语句执行过程

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

分析器生成新的解析树。

查询优化器生成执行计划。

查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由 MySQL Server 过滤后将查询结果缓存并返回给客户端。

若开启了 Query Cache，这时也会将 SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。

Tips：MySQL 8.0 已去掉 query cache（查询缓存模块）。

因为查询缓存的命中率会非常低。 查询缓存的失效非常频繁：只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

什么是索引下推？

索引下推(Index Condition Pushdown)： 简称 ICP，通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数 和 MySQL 服务层访问存储引擎的次数。

索引下推 VS 覆盖索引： 其实都是 减少回表的次数，只不过方式不同

要了解 ICP 是如何工作的，先从一个查询 SQL 开始：

举个栗子：查询名字 la 开头、年龄为 18 的记录

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

有这些记录：

不开启 ICP 时索引扫描是如何进行的：

使用 ICP，索引扫描如下进行：

动手实验：

实验：使用 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 关闭
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 打开
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)

由上实验可知：

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

索引下推限制

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

小结下：

拓展：虚拟列

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索引下推内容请搜索编程宝库以前的文章mysql 优化，希望以后支持编程宝库！

下一节：MySQL中关于datetime、date、time、str之间的转化与比较MySQL教程

datetime、date、time、str之间的转化与比较SELECT NOW(),CURDATE(),CURTIME(),-- datetime ...

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!