本文主要是介绍MySQL——索引下推,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
1、使用前后对比
index Condition Pushdown(ICP)是MySQL5.6中新特性,是一种在存储引擎层使用索引过滤数据的优化方式。
- 如果没有ICP,存储引擎会遍历索引以定位基表中的行,并将它们返回给MySQL服务器,由MySQL服务器评估WHERE后面的条件是否保留行。
- 启用ICP后(默认启用),如果部分WHERE条件可以仅使用索引中的列进行筛选,则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后,存储引擎通过使用索引条目来筛选数据,并且只有在满足这一条件时才从表中读取行。
- 好处:ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。但是,ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。
例如以下语句
EXPLAIN SELECT * FROM `s1` WHERE key1 = 'vLuQVg' and key1 like '%v'
其中的key1 = 'vLuQVg’可以使用到索引,但是key1 like ‘%v’ 却无法使用到索引,在以前版本中的MySQL中,是按照下边步骤来执行这个查询的:
- 先根据key1 = 'vLuQVg’这个条件。从二级索引index_key1中获取到对应的二级索引记录。
- 根据上一步骤得到的二次索引记录中的主键进行回表,找到完整的用户记录在检测记录是否符合**key1 like ‘%v’**这个条件,将符合条件的记录加入最后的结果集。
但是虽然key1 like ‘%v’ 不能组成范围区间参与range访问方法的执行,但这个条件毕竟只涉及到了key1列,所以MySQL把上边的步骤改进了一下:
- 先根据key1 = 'vLuQVg’这个条件,定位到二级索引index_key1中对应的二级索引记录。
- 对于指定的二级索引记录,先不着急回表,而是先检测一下该记录是否满足key1 like ‘%v’ 这个条件,如果这个条件不满足,则该二级索引记录压根就没必要回表操作。
- 对于满足key1 like ‘%v’ 这个条件的二级索引记录进行回表操作。
我们说回表操作其实是一个随机IO,比较耗时,所以上述修改虽然只改进一点点,但是可以省去好多回表操作的成本。MySQL把它们的这个改进称之为索引条件下推(英文名:Index Condition Pushdown)。
2、ICP的使用条件
- 如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用ICP
- ICP可以用于Innodb和MyISAM表,包括分区表Innodb和MyISAM表
- 对于Innodb表,ICP仅用于二级索引。ICP的目标是减少全行读取次数,从而减少I/O操作
- 当SQL使用覆盖索引时,不支持ICP,因为这种情况使用ICP不会减少I/O
- 相关子查询的条件不能使用ICP
这篇关于MySQL——索引下推的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!