MySQL索引下推是什么

MySQL索引下推是什么

本文讲解"MySQL索引下推是什么"，希望能够解决相关问题。

SELECT 语句执行过程

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

分析器生成新的解析树。

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

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

若开启了Query CacheMySQL 索引，这时也会将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存储引擎之InnoDB架构的知识点有哪些MySQL教程

本文讲解"MySQL存储引擎之InnoDB架构的知识点有哪些"，希望能够解决相关问题。一、支持的存储引擎进入MySQL的数据库查看存储引擎就可以看到MySQL数据库所有支持的存储引擎：SH ...

（编辑：通辽站长网）

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