在MySQL中如何使用覆盖索引优化limit分页查询

时间:2023-12-13 10:31:50

背景

今年3月份时候,线上发生一次大事故。公司主要后端服务器发生宕机,所有接口超时。宕机半小时后,又自动恢复正常。但是过了2小时,又再次发生宕机。

通过接口日志,发现MySQL数据库无法响应服务器。在阿里云的技术支持的帮助下,发现了MySQL数据库中存在大量慢查询,导致CPU负载过高。最后,根据慢查询日志,定位到了出问题的SQL和业务接口。

业务接口是一个分页接口,莫名被刷到7000多页,偏移量(offset)高达20w多。每当这条SQL执行时,数据库CPU直接打满。查询时间超过1分钟才有响应。由于慢查询导致数据库CPU使用率爆满,其他业务的数据库请求无法得到及时响应,接口超时。最后,拖垮主服务器。

limit分页查询性能问题

MySQL Limit 语法格式:

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

分页查询时,我们会在 LIMIT 后面传两个参数,一个是偏移量(offset),一个是获取的条数(limit)。当偏移量很小时,查询速度很快,但是当 offset 很大时,查询速度就会变慢。

下面我们以一个实例,讲解一下分页性能问题。假设有一张 300w 条数据的表,对其进行分页查询。

select * from tbl_works limit 1, 10  // 32.8ms
select * from tbl_works limit 10, 10 // 34.2ms
select * from tbl_works limit 100, 10 // 35.4ms
select * from tbl_works limit 1000, 10 // 39.6ms
select * from tbl_works limit 10000, 10 // 5660ms
select * from tbl_works limit 100000, 10 // 61.4 秒
select * from tbl_works limit 1000000, 10 // 273 秒

可以看到,随着偏移量(offset)的增加,查询时间变得越长。对于普通的业务而言,超过1秒的查询是绝对不可以忍受的。上例中,当偏移的起始位置超过10万时,分页查询的时间超过61秒。当偏移量超过100万时,查询时间竟然长达273秒。

从上例中,我们可以总结出:LIMIT分页查询的时间与偏移量值成正比。当偏移量越大时,查询时间越长。这种情况,会随着业务的增加,数据的增多,会越发的明显。那么,如何优化这种情况呢?答案是,覆盖索引。

优化方法

对于LIMIT分页查询的性能优化,主要思路是利用覆盖索引字段定位数据,然后再取出内容。

不使用覆盖索引,查询耗时情况:

SELECT * FROM `tbl_works`
WHERE `status`=1
LIMIT 100000, 10 // 78.3 秒

1)子查询分页方式

SELECT * FROM tbl_works
WHERE id >= (SELECT id FROM tbl_works limit 100000, 1)
LIMIT 20 // 54ms

子查询分页方式,首先通过子查询和覆盖索引定位到起始位置ID,然后再取所需条数的数据。

缺点是,不适用于结果集不以ID连续自增的分页场景。在复杂分页场景,往往需要通过过滤条件,筛选到符合条件的ID,此时的ID是离散且不连续的。如果使用上述的方式,并不能筛选出目标数据。

当然,我们也可以对此方法做一些改进,首先利用子查询获取目标分页的 ids,然后再根据 ids 获取内容。
根据直觉将SQL改造如下:

SELECT * FROM tbl_works
WHERE id IN (SELECT id FROM tbl_works limit 100000, 10) // 错误信息:
// This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

然而,并不尽人意。我们得到一个错误提示。
错误信息的含义是,子查询不能有 limit操作。于是,我们对SQL进行了改造,对子查询包了一层:

SELECT t1.* FROM tbl_works t1
WHERE t1.id in (SELECT t2.id from (SELECT id FROM tbl_works limit 100000, 10) as t2) // 53.9ms

执行成功,且查询效率很高。但是,这种写法非常繁琐。我们可以使用下面的 join 分页方式,达到相同的优化效果。实际上,两者的原理是相同的。

2)join 分页方式

SELECT * FROM tbl_works t1
JOIN (SELECT id from tbl_works WHERE status=1
limit 100000, 10) t2
ON t1.id = t2.id // 53.6 ms

这条SQL的含义是,通过自连接与join定位到目标 ids,然后再将数据取出。在定位目标 ids时,由于 SELECT的元素只有主键 ID,且status 存在索引,因此MySQL只需在索引中,就能定位到目标 ids,不用在数据文件上进行查找。因而,查询效率非常高。

覆盖索引(Cover Index)

如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是平时所说的不需要回表操作。

简单的说,覆盖索引覆盖所有需要查询的字段(即,大于或等于所查询的字段)。MySQL可以通过索引获取查询数据,因而不需要读取数据行。

覆盖索引的好处:

  1. 索引大小远小于数据行大小。因而,如果只读取索引,则能极大减少对数据访问量。
  2. 索引按顺序储存。对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少。
  3. 避免对主键索引的二次查询。二级索引的叶子节点包含了主键的值,如果二级索引包含所要查询的值,则能避免二次查询主键索引(聚簇索引,聚簇索引既存储了索引,也储存了值)。

总结

通过利用覆盖索引,能极大的优化了Limit分页查询的效率。在真正的实践中,除了使用覆盖索引,优化查询速度外,我们还可以使用 Redis 缓存,将热点数据进行缓存储存。

背景描述的事故,我们考虑了时间成本和业务复杂度后,最后采取的是限制分页和增加缓存。所谓的限制分页,即在不影响阅读体验的前提下,只允许用户可以查看前几千条的数据。经测验,偏移量较小时的查询效率较令人满意,查询效率接近使用覆盖索引查询的速度。

参考资料

作者:youthcity
链接:https://www.jianshu.com/p/c6290e65d8b5
來源:简书
简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。