什么是索引下推？

发布时间：2024-01-01 05:57:29 所属栏目：MySql教程来源：DaWei

导读： 本文小编为大家详细介绍“MySQL索引下推是什么”，内容详细，步骤清晰，细节处理妥当，希望这篇“MySQL索引下推是什么”文章能帮助大家解决疑惑，下面跟着小编的思路慢

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

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 语句执行则直接返回结果。

什么是索引下推？
索引下推(Index Condition Pushdown)：简称 ICP，通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数和 MySQL 服务层访问存储引擎的次数。
索引下推 VS 覆盖索引：其实都是减少回表的次数，只不过方式不同
覆盖索引：当索引中包含所需要的字段（SELECT XXX），则不再回表去查询字段。
索引下推：对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的行数。

不开启 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

读到这里，这篇“MySQL索引下推是什么”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会。

（编辑：江门站长网）

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