Mysql系列(十二)—— 索引下推优化
索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP,存储引擎会遍历索引以查找基表中的行,并将它们返回给MySQL服务器,该服务器会评估WHERE行的条件。启用ICP后,如果WHERE只使用索引中的列来评估部分 条件,MySQL服务器会推送这部分内容。WHERE条件下到存储引擎。然后,存储引擎通过使用索引条目来评估推送的索引条件,并且仅当满足该条件时才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。
指数条件下推优化的适用性受以下条件限制:
-
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 支持虚拟生成列上的二级索引。
-
引用子查询的条件无法下推。
-
引用存储函数的条件无法下推。存储引擎无法调用存储的函数。
-
触发条件无法下推。(有关触发条件的信息,请参见 第8.2.2.4节“使用EXISTS策略优化子查询”。)
要了解此优化的工作原理,请首先考虑在不使用索引条件下推时索引扫描的进度:
获取下一行,首先读取索引元组,然后使用索引元组找到并读取整个表行。
测试WHERE适用于此表的条件部分。根据测试结果接受或拒绝该行。
使用索引条件下推,扫描会像这样进行:
获取下一行的索引元组(但不是完整的表行)。
测试WHERE适用于此表的条件部分,并且只能使用索引列进行检查。如果不满足条件,则继续下一行的索引元组。
如果满足条件,请使用索引元组来查找并读取整个表行。
测试WHERE 适用于此表的条件的剩余部分。根据测试结果接受或拒绝该行。
EXPLAIN使用“索引条件下推”时,输出显示 Using index condition在 Extra列中。它没有显示,Using index 因为当必须读取完整的表行时,这不适用。
假设一个表包含有关人员及其地址的信息,并且该表的索引定义为 INDEX (zipcode, lastname, firstname)。如果我们知道一个人的zipcode价值但不确定姓氏,我们可以这样搜索:
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
MySQL可以使用索引来扫描人 zipcode='95054'。第二部分(lastname LIKE '%etrunia%')不能用于限制必须扫描的行数,因此,如果没有Index Condition Pushdown,此查询必须为所有拥有的人检索完整的表行 zipcode='95054'。
使用索引条件下推,MySQL lastname LIKE '%etrunia%'在读取整个表行之前检查该 部分。这样可以避免读取与索引元组相对应的完整行,这些索引元组与zipcode条件匹配 但不符合 lastname条件。
默认情况下启用索引条件下推。可以optimizer_switch通过设置index_condition_pushdown标志来控制 系统变量 :
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
总结
1.对于 where constant + like查询可以尝试使用联合索引
// name和stu_id有联合索引
explain select * from course where name = 'ww' and stu_id like '%4%';
2.对于 where constant + order by index column可以尝试使用联合索引;
// name和stu_id有联合索引
explain select * from course where name = 'ww' order by stu_id;
以上另种情况都会使用Using index condition。第一种是过滤like的模糊匹配,第二种是进行联合索引的排序。
索引下推经常使用的场景:
- 对于二级索引
- select的列不使用覆盖索引
- 多条件查询(where中多条件,where + order by) + 联合索引