一起学习-MySQL的索引下推技术

1.前言

索引下推主要是做什么的？能为查询语句带来多大的性能提升？在弄清这些问题之前，先带你看看官方文档的相关说明。

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.

官方文档的大致意思就是：索引下推是MySQL通过索引从表中检索行记录的一种优化技术。如果不使用索引下推技术，存储引擎会根据索引去表中检索行记录，然后把检索结果返回给MySQL的Server层，Server层再根据Where条件对返回的结果进行二次过滤。如果使用索引下推技术，存储引擎先根据索引得到结果，在检索行记录之前，会根据索引中无法使用索引的列进行二次过滤，索引下推技术可以减少访问基表的次数以及Server层访问存储引擎的次数。

即便有了相应的解释，理解起来还是带有一定的困难。给你举个例子吧，有一张表t，表中有id、a、b、c四列，针对a、b、c三列创建联合索引(a, b, c)，执行下面的查询select * from t where a = 'xxx' and b like '%xx%' and c = 'xxx'，该查询符合最左匹配原则，由于b列使用了模糊查询，导致查询只能使用索引中的a列，无法使用b、c列。

如果不使用索引下推技术，不满足b like '%xx%' and c="xxx"的索引记录也会被查询出来，进而回表检索对应的行记录，检索结果返回给Server层，Server层再根据b like '%xx%' and c='xxx'条件进行数据过来。

如果使用索引下推技术，存储引擎会过滤出满足b like '%xx%' and c="xxx"的索引记录，这样Server层就不需要再根据b like '%xx%' and c="xxx"条件对结果进行过滤了。

套用伟人的一句话，实践是检验真理的唯一标准。

2. 实战

2.1 导入数据

实战数据可以在test_db上面进行下载，下载后将数据导入数据库中，导入完成后可以看到数据库中存在如下表

mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)
复制代码

2.2 创建索引

在employees表中针对first_name、last_name、birth_date三列创建联合索引idx_first_name_last_name_birthday

2.3 查看执行计划

2.3.1 开启索引下推

索引下推功能默认情况下是开启的，一起看下下面查询语句的执行计划

执行计划的Extra列中给出了Using index condition字样，说明查询用到了索引下推功能。

当前查询只能使用到联合索引中的first_name字段，在回表检索行记录之前，索引下推技术会根据last_name like '%ch%' and birthd_date > '1952-05-15'过滤出不满足条件索引记录。

在where条件中再添加一个查询条件，一起来看下效果

执行计划的Extra列中给出了Using index condition字样，说明查询用到了索引下推功能，同时也给出了Using Where的字样，表示Server层会对存储引擎返回的结果使用hire_date > '1986-01-01'条件进行过滤，由此我们可以知道索引下推技术使用到的是联合索引中无法用到索引的那部分字段进行过滤，无法使用非联合索引中的字段进行过滤。

2.3.2 关闭索引下推

下面我们通过set optimizer_switch = 'index_condition_pushdown=off';命令来关闭索引下推功能，关闭之后再来看下查询语句的执行计划

执行计划的Extra列中给出了Using where字样，可以看出在关闭索引下推功能的情况下过滤是发生在Server层，并不是在存储引擎层进行过滤

2.4 key_len字段

查询执行计划中的key_len字段表示用到索引对应的长度，在2.3中给出的结论是查询只使用到联合索引中的first_name字段，这个结论是可以通过计算得出来。在进行验证之前，先一起来看看表的定义:

通过表的定义可以得出3个重要的信息：

• 各字段定义的数据类型

• 各字段是否允许为null

• 表对应的字符编码

猜你应该知道不同的字符集占用的字节数也不尽相同：

字符集	占用字节数
latin1	1
ucs2	2
utf8mb3，utf8	3
utf8mb4	4

有了这些前提条件就可以计算出执行计划中key_len的长度 = 14 + 2 + 0(非空，如果允许为空则为1) = 16，其中的2具体代表什么意思也不是很清楚，有了解的可以帮忙解答下。

2.4 性能比较

说了这么多，你大概还是不太知道索引下推技术对查询性能到底能带来多少提升，请接着往下看

2.4.1 开启监控

set profiling = 1;

2.4.2 执行查询语句

select * from employees where first_name = 'Sanjiv' and last_name like '%cha%' and birth_date = '1952-05-15';

2.4.3 关闭索引下推功能

set optimizer_switch='index_condition_pushdown=off';

2.4.4 执行查询语句

select * from employees where first_name = 'Sanjiv' and last_name like '%cha%' and birth_date = '1952-05-15';

2.4.5 查看监控结果

可以看到开启索引下推功能比关闭索引下推功能在性能有2倍的提升

3.总结

这边文章和你一起学习了什么是索引下推以及分别演示了开启和关闭索引下推功能情况下查询执行计划的不同，也一起对开启和关闭情况下的性能做了一个说明。本次的学习到此结束，期待与你的下次相遇。