文章目录
- 1. 简介
- 覆盖索引
- 索引列的利弊
- 索引下推
- 使用前后对比
- ICP的开启/关闭
- ICP的使用条件
1. 简介
上一篇文章我们介绍了 【MySQL从入门到精通】【高级篇】（二十八）子查询优化，排序优化，GROUP BY优化和分页查询优化。这篇文章我们接着来介绍覆盖索引。
覆盖索引
覆盖索引的定义是：一个索引包含了满足查询结果的数据就叫做覆盖索引。这样的话在查询的时候就不需要回表查询整列的数据。覆盖索引是非聚簇复合索引的一种形式。它包含了查询里的SELECT,JOIN和WHERE子句中的所有列（即索引的字段正好覆盖查询条件中所涉及的字段）。
简单的说就是，索引列+主键 包含SELECT 到 FROM 之间查询的列。
索引列的利弊
- 避免Innodb表进行索引的二次查询（回表）
Innodb是以聚簇索引的顺序来存储的，对于Innodb 来说，二级索引的在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。
在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。 - 可以把随机IO变成顺序IO加快查询效率
由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化策略。
索引下推
使用前后对比
Index Condition Pushdown(ICP) 是MySQL 5.6中的新特性，是一种在存储引擎层使用索引过滤数据优化方式。
如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。
启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
但是ICP得加速效果取决于在存储引擎内通过ICP筛选掉的数据比例。
ICP的开启/关闭
默认情况下启用索引条件下推，可以通过设置系统变量optimizier_switch控制：index_condition_pushdown
#关闭索引下推
SET optimizer_switch='index_condition_pushdown=off'
#打开索引下推
SET optimizer_switch='index_condition_pushdown=on'
当使用索引条件下推时，EXPLAIN语句输出结果中Extra列内容显示为Using index condition。
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。
本文链接：https://my.lmcjl.com/post/11498.html
展开阅读全文
4 评论