正文

五分钟搞懂mysql索引下推(代码片段)

三分恶  三分恶  2023-01-01  702

关键词：

大家好，我是老三，今天分享一个小知识点——索引下推。

如果你在面试中，听到MySQL5.6”、“索引优化” 之类的词语，你就要立马get到，这个问的是“索引下推”。

什么是索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

索引下推优化的原理

我们先简单了解一下MySQL大概的架构：

MySQL服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

存储引擎读取索引记录；
根据索引中的主键值，定位并读取完整的行记录；
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

存储引擎读取索引记录（不是完整的行记录）；
判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

索引下推的具体实践

理论比较抽象，我们来上一个实践。

使用一张用户表tuser，表里创建联合索引（name, age）。

如果现在有一个需求：检索出表中名字第一个字是张，而且年龄是10岁的所有用户。那么，SQL语句是这么写的：

select * from tuser where name like '张%' and age=10;

假如你了解索引最左匹配原则，那么就知道这个语句在搜索索引树的时候，只能用 张，找到的第一个满足条件的记录id为1。

那接下来的步骤是什么呢？

没有使用ICP

在MySQL 5.6之前，存储引擎根据通过联合索引找到name likelike '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。

我们看一下示意图：

可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

使用ICP

而MySQL 5.6 以后，存储引擎根据（name，age）联合索引，找到name likelike '张%'，由于联合索引中包含age列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

我们看一下示意图：

可以看到只回表了一次。

除此之外我们还可以看一下执行计划，看到Extra一列里Using index condition，这就是用到了索引下推。

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

索引下推使用条件

只能用于range、 ref、 eq_ref、ref_or_null访问方法；
只能用于InnoDB和 MyISAM存储引擎及其分区表；
对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

引用了子查询的条件不能下推；
引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

相关系统参数

索引条件下推默认是开启的，可以使用系统参数optimizer_switch来控制器是否开启。

查看默认状态：

mysql> select @@optimizer_switch\\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

切换状态：

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

参考：

[1].《 MySQL技术内幕 InnoDB存储引擎》

[2]. 《MySQL实战45讲》

[3]. MySQL索引下推（ICP）简单理解及例子

[4]. 一文读懂什么是MySQL索引下推（ICP）

五分钟告诉你什么是mysql的覆盖索引(代码片段)

文章目录五分钟告诉你什么是MySQL的覆盖索引覆盖索引总结参考五分钟告诉你什么是MySQL的覆盖索引前面我们已经对MySQL索引底层原理多少有一定的了解了，还不是很了解的小伙伴可以看我之前的博文:《不会吧不会吧，难... 查看详情

五分钟，让你明白mysql是怎么选择索引《死磕mysql系列六》(代码片段)

一网打尽MySQL的各种锁系列文章一、如何选择索引影响优化器的几大因素扫描行数从何而来？为什么优化器选择了扫描行数多的索引？二、索引选择异常如何处理三、总结系列文章二、一生挚友redolog、binlog《死磕MySQL系列... 查看详情

索引索引下推(代码片段)

导读索引下推（indexconditionpushdown）简称ICP，在Mysql5.6的版本上推出，用于优化查询。在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，... 查看详情

mysql之索引下推(代码片段)

说到索引下推之前，我们先讲一下“覆盖索引”，也称为“联合索引”覆盖索引一张表名为test，有两个字段name和age，建立联合索引idx(name,age)表的数据为（“张三”,18），（“李四”,24），&... 查看详情

五分钟搞懂vuex(代码片段)

这段时间一直在用vue写项目，vuex在项目中也会依葫芦画瓢使用，但是总有一种朦朦胧胧的感觉。于是决定彻底搞懂它。看了一下午的官方文档，以及资料，才发现vuexsoeasy！作为一个圈子中的人，决定输出一下文档，如果你仔细... 查看详情

mysql回表索引覆盖最左匹配索引下推(代码片段)

前言在执行一条sql的时候，在mysql内部都会通过以下四个流程+--------------+|连接器|+--------------+\\|/+--------------+|分析器|+--------------+\\|/+--------------+|优化器|+------------- 查看详情

给自己五分钟，彻底搞懂并优化冒泡排序(代码片段)

给自己五分钟，彻底搞懂并优化冒泡排序冒泡排序思想算法描述示例冒泡排序的Java代码实现冒泡排序的第一次优化冒泡排序弊端冒泡排序第一版优化冒泡排序第二次优化冒泡排序第二版优化冒泡排序思想给定一个无序数组&#x... 查看详情

mysql的索引条件下推（indexconditionpushdown，icp）(代码片段)

索引下推：不符合索引最左前缀原则，却还能利用复合索引的其他字段，减少回表次数。最左前缀可用于在索引中定位记录。那不符合最左前缀的部分，会怎样？用户表联合索引（name,age）为例，现... 查看详情

mysql性能调优05_覆盖索引索引下推如何选择合适的索引orderby与groupby优化索引设计原则(代码片段)

文章目录①.坏境准备②.覆盖索引、索引下推③.Mysql如何选择合适的索引④.Orderby与Groupby优化⑤.filesort文件排序方式(了解)⑥.索引设计原则①.坏境准备CREATETABLE`employees`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(24)NOTNU... 查看详情

mysql之索引下推(代码片段)

白日梦的mysql专题（第38篇文章）8分钟回顾mysql的索引(代码片段)

目录公众号首发-推荐阅读原文-格式更好看一、导读二、聚簇索引三、二级索引四、联合索引4.1、什么是联合索引4.2、左前缀原则4.3、联合索引的分组&排序五、覆盖索引六、倒排索引公众号首发-推荐阅读原文-格式更好看点击... 查看详情

mysql从入门到精通高级篇（二十九）覆盖索引的使用&索引下推(代码片段)

您好，我是码农飞哥（wei158556），感谢您阅读本文，欢迎一键三连哦。💪🏻1.Python基础专栏，基础知识一网打尽，9.9元买不了吃亏，买不了上当。Python从入门到精通❤️2.网上优质的Python题... 查看详情

五分钟搞懂pom设计模式(代码片段)

今天，我们来聊聊WebUI自动化测试中的POM设计模式。为什么要用POM设计模式前期，我们学会了使用Python+Selenium编写WebUI自动化测试线性脚本线性脚本（以快递100网站登录举栗）：importtimefromseleniumimportwebdriverfr... 查看详情

mongodb数据库|三分钟学会mongodb索引，快来看看与mysql索引有何不同？(代码片段)

目录一、创建索引1、创建索引2、通过索引查询二、复合索引三、索引内嵌文档四、索引基数五、explain六、为何不使用索引七、固定集合一、创建索引1、创建索引>db.student.createIndex("name":1)"numIndexesBefore":1,"numIndex... 查看详情

一文搞懂底层mysql索引那些事(代码片段)

前言因为一些原因，最近总有人问起mysql的一些问题，这个似乎成为检验程序员的技术的基本问题，今天就做个系统性的总结，留作以后复习，也分享给需要的同学。索引是为了加速对表中数据行的检索而创建... 查看详情

五分钟搞懂vuex(代码片段)

一本彻底搞懂mysql索引优化explain百科全书(代码片段)

(datetimePRIMARY(((((AUTO_INCREMENT,PRIMARY((((user_group;(PRIMARY((((user_group((...outer_tablesexpr(...inner_tables...)...*(cderived_uc>*(user_iduser_group==(AUTO_INCREMENT,PRIMARY(idx_s_name(s_n 查看详情