覆盖索引、索引下推（ICP）及其他优化策略

最新推荐文章于 2025-04-01 09:47:31 发布

哆啦咪嗦

最新推荐文章于 2025-04-01 09:47:31 发布

阅读量1.4k

点赞数 8

分类专栏： mysql优化文章标签： sql 数据库

本文链接：https://blog.youkuaiyun.com/m0_69766497/article/details/127566204

版权

本文深入探讨了数据库优化技术，包括覆盖索引的概念、使用场景及其优缺点，以及索引下推（ICP）的工作原理、启用与关闭的影响。通过案例分析展示了ICP如何提高查询效率。同时，文章还讨论了其他优化策略，如Exists与In的选择、count(*)与count(具体字段)的效率对比，并提到了实际开发中主键设计的推荐实践。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2.count(*) 与count(具体字段)效率

覆盖索引

如何理解覆盖索引：

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到部分列的数据，毕竟索引叶子节点存储了他们索引的数据，一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇索引的一种形式，简单来说就是：索引列+主键包含select到from之间查询的列。

覆盖索引的使用

举例1：

SHOW INDEX FROM student;

EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

结果：还是沿用本专栏的数据表student，执行第一条sql语句查询到该表的二级索引有idx_age_name 当执行第二条sql语句时索引失效了，执行第三条sql语句时索引命中

举例2：

EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';

结果：和上述结果一样，当需要返回的字段都存在于二级索引上的叶子节点上时索引命中，而需要query的列只有一部分字段存在二级索引叶子节点上时，索引失效。

覆盖索引的利弊

好处：

1.避免Innodb表进行索引的二次查询（回表）

Innodb是以聚簇索引的顺序来存储的，对于Innodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率

2.可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

弊端：

索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这就是业务DBA，或者称为业务数据架构师的工作。

索引下推

Index Condition Pushdown(ICP)是MySQL5.6中的新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

使用前后对比（理论上）

启用ICP前，存储引擎会遍历索引以定位基表中的行，并将他们返回给MySQL服务器，由MySQL服务器评估where后面的条件是否保留行

启用ICP后，如果部分where条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分where条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件是才从表中读取行。好处是ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数，但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据比例。

索引下推案例分析

举例1：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

执行结果：当使用索引条件下推时，Explain

最低0.47元/天解锁文章