惊！一条SQL竟有如此威力 | 黑微狗的技术博客

URL

date

AI summary

slug

status

背景

前段时间的某一天下午，例行巡检了一下MySQL的监控指标，发现某个时间点的iops有一个突增，于是打算找一下原因

分析

观察了其他指标，比如qps、cpu，都没有发现明显的异常。猜测应该是大量的读写磁盘造成的。查看审计日志，发现当时只有一条查询语句存在大量物理读。

我们知道，innodb读取磁盘是按照页的维度来读的，一次io就是读取一个页的数据。这条语句竟然读取了2w多个页，每页16k，那么总共就读取了16Kb * 20247 / 1024 = 316.36Mb。3s不到读取了300多M，平均每秒差不多确实要100M+了，和图中的监控基本能对得上。

我们再分析一下这条语句：


select
  *
from
  `notify_record`
WHERE
  (
    user_id = 2574491
    and read_status = 0
    and revoke_status = 0
    and show_type in (1, 2)
    and user_type = 4
  )

查看执行计划走到了idx_userId_revokeStatus_insTm索引，revokeStatus代表消息是否被撤回，大多数消息都是没撤回的（revoke_status = 0），而这个用户的总消息量大概是2w条，所以需要遍历这2w条记录。

我们粗略计算一下这2w条记录在二级索引上大概需要几页，这个索引是由下面三个字段组成的


`user_id` bigint
`revoke_status` tinyint(1)
`ins_tm` datetime

由于ins_tm没有使用毫秒数，所以占用存储为5个字节。再加上bigint类型的主键id，单个索引值占用的大小为8 + 1 + 5 + 8 = 22个字节。

那么一页可以存储16384 / 22 ≈ 744行记录，2w条记录大概需要27个二级索引页。这个数量和2w个页还相差甚远呢。那是因为我们还没有考虑回表，我看了一下，这个用户的两条消息记录中间基本上都隔着1000+的记录，所以其实每条消息基本都分布在不同的数据页上，这就导致回表也产生了近2w次的磁盘io。

到这里，我们已经搞清楚问题是怎么产生的了，下面看看如何解决。

解决方案

先介绍一下使用这条SQL的业务场景：我们的消息系统里有一个功能，叫一键已读，用来把所有的未读消息标记成已读。它的实现逻辑为：

从数据库一次性查出所有未读消息（也就是上面这条SQL）

拿到id列表按500一批分批去数据库里更新（包裹在事务中）

从这里可以看出，第一步只需要查询出所有未读消息的id即可，不用查询全字段。但是即使只指定id，由于查询走不到覆盖索引，还是需要回表过滤，性能没有实质性的提升，关键还是要优化索引。

所以这里的优化方案包含两个步骤

增加覆盖索引

只返回id

关于一键已读功能比较优雅的设计

如果我们想得更深一点，为什么更新考虑了分批，查询却没有考虑分批呢？如果某个用户存在非常多的未读消息，一键已读功能会不会直接导致内存溢出？关于这个功能我觉得可以从同步和异步两个实现方式考虑。

同步方式

我们先考虑同步实现的方式。这种方式我个人觉得应该从前端出发来优雅解决，因为一键已读侧重的并不是强事务，而是侧重便捷性。在未读消息非常多的时候，一键已读需要长一点的时间用户应该也可以理解，产品设计上我们可以增加进度条，让用户感受友好一些。很多邮箱也是这么实现的。具体的步骤：

先分页查询出所有的未读消息，这里只需要id即可，由于数据量可能比较大，只保留必要的列。

有了总量之后，可以基于此在前端展示处理进度
当然为了更好的用户体验，查询所有未读消息的时候也增加进度条

按批次分批提交已读请求，每批次处理完之后更新前端展示进度

用户可以随时终止

异步方式

异步方式就比较简单了，用户点击一键已读按钮，我们就告诉他已经成功提交任务，后台会慢慢帮它标为已读，它可以不用关心了，过一段时间来看即可。当然如果用户体验想做得更好，可以配合消息通知等手段。实现上需要注意的是要记录好时间或者最大消息id之类的信息，防止异步处理的时候多标记了后产生的消息。

思考

其实刚看到上述的监控信息的时候，我完全不敢相信这是一条SQL造成的，因为它直接给iops使用率快跑满了。但是现实给了我沉重的打击，确实就是这一条语句导致的，当然主要是因为数据都不在缓存中，导致了全部要从磁盘上读取。这里也可以看出，缓存的重要性，也就是MySQL里的BufferPool。

另外，解释一下标题，一条烂SQL的威力大我们肯定都知道（比如直接查询全表数据或者是N张大表关联查询），只是这里在初看的时候我并没有任务这条SQL有这么烂，最终发现它造成了IOPS飙升对我来说有点颠覆，所以才取了这么个名字~不是标题党哈！😝