本文小编为大家详细介绍“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语句执行则直接返回结果。

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

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

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

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

覆盖索引：当索引中包含所需要的字段（SELECT XXX），则不再回表去查询字段。

索引下推：对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表的行数。

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

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

SELECT*FROMuserWHEREnameLIKE'la%'ANDage=18;

有这些记录：

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

通过索引元组，定位读取对应数据行。（实际上：就是回表）

对WHERE中字段做判断，过滤掉不满足条件的行。

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

获取索引元组。

对WHERE中字段做判断，在索引列中进行过滤。

对满足条件的索引，进行回表查询整行。

对WHERE中字段做判断，过滤掉不满足条件的行。

实验：使用MySQL版本8.0.16

--表创建CREATETABLEIFNOTEXISTS`user`(`id`VARCHAR(64)NOTNULLCOMMENT'主键id',`name`VARCHAR(50)NOTNULLCOMMENT'名字',`age`TINYINTNOTNULLCOMMENT'年龄',`address`VARCHAR(100)NOTNULLCOMMENT'地址',PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSETutf8mb4COLLATE=utf8mb4_unicode_ciCOMMENT'用户表';--创建索引CREATEINDEXidx_name_ageONuser(name,age);--新增数据INSERTINTOuser(id,name,age,address)VALUES(1,'tt',14,'linhai');INSERTINTOuser(id,name,age,address)VALUES(2,'lala',18,'linhai');INSERTINTOuser(id,name,age,address)VALUES(3,'laxi',30,'linhai');INSERTINTOuser(id,name,age,address)VALUES(4,'lawa',40,'linhai');--查询语句SELECT*FROMuserWHEREnameLIKE'la%'ANDage=18;

新增数据如下：

关闭ICP，再调用EXPLAIN查看语句：

--将ICP关闭SEToptimizer_switch='index_condition_pushdown=off';--查看确认showvariableslike'optimizer_switch';--用EXPLAIN查看EXPLAINSELECT*FROMuserWHEREnameLIKE'la%'ANDage=18;

开启ICP，再调用EXPLAIN查看语句：

--将ICP打开SEToptimizer_switch='index_condition_pushdown=on';--查看确认showvariableslike'optimizer_switch';--用EXPLAIN查看EXPLAINSELECT*FROMuserWHEREnameLIKE'la%'ANDage=18;

由上实验可知，区别是否开启ICP：Exira字段中的Using index condition

更进一步，来看下ICP带来的性能提升：

通过访问数据文件的次数

--1.清空status状态flushstatus;--2.查询SELECT*FROMuserWHEREnameLIKE'la%'ANDage=18;--3.查看handler状态showstatuslike'%handler%';

对比开启ICP和 关闭ICP：关注Handler_read_next的值

--开启ICPflushstatus;SELECT*FROMuserWHEREnameLIKE'la%'ANDage=18;showstatuslike'%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|+----------------------------|-------+18rowsinset(0.00sec)--关闭ICPflushstatus;SELECT*FROMuserWHEREnameLIKE'la%'ANDage=18;showstatuslike'%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|+----------------------------|-------+18rowsinset(0.00sec)

由上实验可知：

开启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对联合索引的部分列模糊查询非常有效。

CREATETABLEUserLogin(userIdBIGINT,loginInfoJSON,cellphoneVARCHAR(255)AS(loginInfo->>"$.cellphone"),PRIMARYKEY(userId),UNIQUEKEYidx_cellphone(cellphone));

列cellphone：就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引idx_cellphone实质是一个函数索引。

好处：在写SQL时可以直接使用这个虚拟列，而不用写冗长的函数。

举个栗子： 查询手机号

--不用虚拟列SELECT*FROMUserLoginWHEREloginInfo->>"$.cellphone"='13988888888'--使用虚拟列SELECT*FROMUserLoginWHEREcellphone='13988888888'

读到这里，这篇“MySQL索引下推是什么”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注亿速云行业资讯频道。