1.什么是覆盖索引
覆盖索引是指一个索引包含了查询所需的所有数据列(可以不包含主键),从而可以直接通过索引完成查询,而无需访问实际的数据行(即进行回表操作)。这样的索引能够减少查询时需要读取的数据量,减少IO次数,从而提高查询性能和减少系统资源消耗。
具体来说,当一个查询语句涉及到的列都包含在某个索引中,并且查询条件、排序方式等操作可以直接利用这个索引完成时,就可以说这个索引是覆盖索引。
覆盖索引是非聚簇复合索引的一种形式。
简单说就是,== 索引列+主键== 包含 ==SELECT 到 FROM之间查询的列 ==。
2.覆盖索引的优缺点
好处:
(1)避免Innodb表进行索引的二次查询(回表)
在使用覆盖索引查询时,二级索引的键值就可以获取所要的数据,避免了对主键的二次查询,减少了IO操作,提升了查询效率
(2)可以把随机IO变成顺序IO加快查询效率
由于覆盖索引是按键值的顺序存储的,对于IO密集型的范围查找来说,对磁盘的访问是顺序IO的,与随机IO对比查询效率要高的多。如果没有使用覆盖索引,需要遍历全表或者要进行会表操作,回表查询时数据行存储的位置就是随机的了,属于随机IO。故覆盖索引可以把随机IO变成顺序IO加快查询效率。
弊端:
索引维护总是有代价的。因此建立冗余索引来支持覆盖索引时就要权衡考虑了,这时业务DBA,或者称为业务数据架构师的工作。
举例:
假设我们有一个包含订单信息的数据库表 orders,其中包含了订单编号 order_id、客户姓名 customer_name、订单金额 order_amount 等字段。
现在我们经常需要执行的查询是根据订单编号查询订单金额,我们可以通过建立冗余索引来支持这个查询:
原有索引: 假设已经存在了一个索引 idx_order_id,包含了订单编号 order_id 字段。
冗余索引: 现在我们可以创建一个冗余索引,包含了订单编号 order_id 和订单金额 order_amount 两个字段,我们将其命名为 idx_order_id_amount。
-- 创建冗余索引
CREATE INDEX idx_order_id_amount ON orders(order_id, order_amount);
这样,当我们需要查询订单编号对应的订单金额时,数据库可以直接利用冗余索引 idx_order_id_amount 进行覆盖查询,而不需要访问实际的数据行,从而提高查询性能并将随机IO转换为顺序IO。
需要注意的是,冗余索引的建立需要根据具体的查询需求和业务场景来合理设计。过多的冗余索引可能会增加数据库存储和维护成本,并且会影响写操作的性能,因此需要权衡考虑。在实际应用中,可以通过监控和分析查询执行计划来优化索引设计,以达到最佳的查询性能。
总结:
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。