MySQL索引下推

MySQL是我们常用的一个关系型数据，也是面试时常考的内容，与其相关的知识点更是数不胜数。包括引擎、索引、锁、事务、主从一致性，持久化等等。

对于面试离不开的引擎话题，我们同样需要了解索引下推的概念，简单概括来说：

• 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
• 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
• 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

如果用一句话简单概括，一种优化查询的方式，可以由存储引擎来判断检索数据是否符合一定的条件，而不必交给服务器处理，减少查表次数，提高查询效率。

具体来说，在没有使用ICP的情况下，MySQL的查询过程为：

1. 存储引擎读取索引记录；

2. 根据索引中的主键值，定位并读取完整的行记录；

3. 存储索引把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP后的查询过程：

1. 存储引擎读取索引记录（不是完整的行记录）

2. 判断WHERE条件部分能否用索引中的列来进行检查，如果条件不满足，则处理下一行索引记录

3. 条件满足，使用索引中的主键去定位，并读取完整的行记录。

4. 存储引擎把记录交给Server层，Server层检测该记录是否满足Where条件的其余部分。

但是空说原理或者直接对齐作用进行概括还是比较抽象，我们需要做一些实验来验证。

下设计实验来验证索引下推是否对实际查找性能有所提高。

首先进行一个功能性验证实验。

创建一张数据表：

 我们利用Navicat进行数据生成：

我们选择插入20W条数据

 

 我们根据(name, age)建立联合索引。

符合最左匹配原则。

我们先将索引下推关闭：

set optimizer_switch="index_condition_pushdown=off";

我们执行一条查询语句

select * from tuser where name like '张%';

 > 查询时间: 0.066s

具体的查询过程为

该过程发生了两次回表，联合索引中的age字段并没有使用上。

我们使用icp

查询时间为0.062s，稍微提速了。

此时其查询过程为

我们利用explain语句观察该sql查询语句的执行计划：

索引下推的使用条件

1. 只能用于range、ref、eq_ref、ref_or_nul访问方法；

2. 只能用于InnoDB和MyISAM存储引擎及其分区表

3. 对于InnoDB引擎来说，索引下推只适用于二级索引。

4. 引用了子查询的条件不能下推。

5. 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

对于第3.条的解释，我们引入索引下推的目的是为了减少回表，如果我们本身使用的是聚簇索引来查询数据，数据和索引是存放在一起的，不存在回表这一说。

---

数据准备

1. 生成具有随机值的大型数据集。
2. 创建两个具有相同架构的表：一个表在列上具有索引，另一个表将没有索引。
3. 将同一组数据加载到两个表中。

实验过程

1. 编写一个查询，根据条件使用带有索引的表中的索引列检索行的子集。
2. 对两个表运行查询并测量每个表的执行时间。
3、重复查询测量数次，得到可靠的平均值。
4. 开启索引下推功能，重复以上步骤。

验证指标

验证指标应该是具有和不具有索引下推功能的两种情况下的查询执行时间。 我们可以计算加速比作为验证指标，看看启用索引下推功能时查询运行速度有多快。

分析与结果比较

通过对不同的验证指标进行比较，判断索引下推是否对实际查找性能有所改善。

需要注意的是，实验过程要严谨且可控，数据要具有代表性，验证指标要合理且明确，结果判别要科学且客观。

---

我们需要验证索引下推，实验中的变量即是索引，我们需要建立索引。

两个实验，建立主键索引、简历联合索引。

此时我们明确id是唯一的，name也是唯一的。

根据相同的数据生成规则得到两张表。

我们要用到索引，那么查询语句的where中就应该包含索引字段

题外话：

MySQL构建索引的原则有什么？

1.  选择唯一性索引
2. 为经常需要排序、分组和联合操作的字段建立索引
3. 为常作为查询条件的字段建立索引
4. 限制索引的数目
5. 尽量使用数据量少的索引
6. 尽量使用前缀来索引
7. 删除不再使用或很少使用的索引
8. 遵守最左匹配原则
9. 尽量选择区分度高的列作为索引
10. 索引列不能参与计算
11. = 和 in可以乱序， MySQL优化器会帮程序员把查询优化为可识别的形式

选取一条基础查询语句

select name, age, address from tuser_copy1 where name = '刘云熙';

在copy1中查询时间为0.025s

在copy2中查询时间为0.025s

关闭copy1和copy2的索引下推功能，为copy1表添加 name 主键索引。

此时查询指令影响的效果并没有影响。

添加主键索引或联合索引后，explain查询语句确实会有extra的信息。

但是困于数据量太小，应该100w级别会有更多的优化，对于主键索引的效果。

我们进行联合索引的测试，将<name, age> 创建一个联合索引，然后减少回表获得对应name的age，观察是否能得到明显的优化表现。

实际测试的表现仍然看不出太大的性能差异，可能还是由于数据总量才1k吧。

后面有时间继续测试

---

 

参考资料：

Mysql性能优化：什么是索引下推？ - 知乎 (zhihu.com)五分钟搞懂MySQL索引下推 - 掘金 (juejin.cn)

mysql建立索引的几大原则（仅学习） - 知乎 (zhihu.com)