索引下推

发表于 | 分类于
字数统计 837 | 阅读时长 3

mysql 架构

  • MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。
  • MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。
  • MySQL 索引扫描：根据指定索引过滤条件，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。
  • MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表。

Mysql 性能优化：什么是索引下推？

索引条件下推优化 （Index Condition Pushdown (ICP) ） 是 MySQL 5.6 添加的，用于优化数据查询。

  • 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给 MySQL Server，MySQL Server 进行过滤条件的判断。
  • 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server 将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合 MySQL Server 传递的条件，只有当索引符合条件时才会将数据检索出来返回给 MySQL 服务器。

例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于 name 使用了范围查询，根据最左匹配原则：

不使用 ICP，引擎层查找到 name like '张%' 的数据，再由 Server 层去过滤 age=10 这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段 age。

没有使用 ICP

没有使用 ICP

但是，使用了索引下推优化，把 where 的条件放到了引擎层执行，直接根据 name like '张%' and age=10 的条件进行过滤，减少了回表的次数。

使用 ICP

使用 ICP

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。

索引下推的使用条件

ICP 目标是减少全行记录读取，从而减少 IO 操作，只能用于非聚簇索引。聚簇索引本身包含的表数据，也就不存在下推一说。
只能用于 range、 ref、 eq_ref、ref_or_null 访问方法；
Where 条件中是用 and 而非 or 的时候。
ICP 适用于分区表。
ICP 不支持基于虚拟列上建立的索引，比如说函数索引
ICP 不支持引用子查询作为条件。
ICP 不支持存储函数作为条件，因为存储引擎无法调用存储函数。

1
2
3
4
5
6
# 查看索引下推是否开启
select @@optimizer_switch
# 开启索引下推
set optimizer_switch="index_condition_pushdown=on";
# 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

通过执行计划查看是否使用索引下推

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

1
2
3
4
5
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 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 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

参考文献

什么是索引下推？（深入精讲）
Mysql 性能优化：什么是索引下推？

0%