SQL调优：记一次慢查询导致的线上事故排查

---

一、背景

在节点式应用兼容版本上线的时候遇到的问题。

在节点式应用兼容版本中已经拆分成8个子表的image表新增了两个字段，其中result字段默认值为1，并且目前几乎所有已有的查询条件都需要补充“result = 1”的过滤条件。

二、事故表现

从上线初期测试提报的 /api/community/v1/publications/info 接口持续 pending，到整站无法访问，经过日志和运维同学的定位，是数据库CPU打满导致的。

三、排查思路

出现问题的第一时间我便想到可能是由于新增字段 result 的索引问题导致的；运维提供了可能导致问题的查询语句：

SELECT COUNT( * ) AS total FROM partition_image_0 WHERE (deleted = 0 AND result = 1) UNION ALL SELECT COUNT( * ) AS total FROM partition_image_1 WHERE (deleted = 0 AND result = 1) UNION ALL SELECT COUNT( * ) AS total FROM partition_image_2 WHERE (deleted = 0 AND result = 1) UNION ALL SELECT COUNT( * ) AS total FROM partition_image_3 WHERE (deleted = 0 AND result = 1) UNION ALL SELECT COUNT( * ) AS total FROM partition_image_4 WHERE (deleted = 0 AND result = 1) UNION ALL
SELEC …

马上查看了生产环境mysql监控，靠前的几项指标并没有什么异常，但问题仍可能出现在慢查询上。

通过运维调取了出现故障前后共一个小时的慢查询，在慢查询日志中，直接根据result = 1的条件查找并没有什么收获。

以 select count(*) from partition_image_0 where (deleted = 0 and result = 1) 举例:
目前线上有deleted的单列索引，索引应该是命中的；

但对于 select count(*) from partition_image_0 where (deleted = 0) 和 select count(*) from partition_image_0 where (deleted = 0 and result = 1) 分别使用explain进行执行计划分析，前者的extra为using index，后者为using where

虽然后者使用的是using where，但仍然是走了索引的

向下继续去翻监控盘，InnoDB Buffer Pool的指标异常比较可疑，并且其他指标都没有显著异常，这证明接收的请求没有明显变化。推测：在时间段内可能存在请求量级相当大的数据，未经过InnoDB缓存池，直接从MySQL磁盘中查询出来

询问GPT结果：

---

四、经验与结论

缓冲池及缓存替换策略

• 缓冲池作用：
  InnoDB 使用缓冲池来缓存磁盘中的数据页和索引页（以页），目的是减少磁盘 I/O 操作，提高数据库读写性能。当需要访问数据时，如果数据页已经在缓冲池中，就可以直接从内存中读取，避免了从磁盘读取的相对较慢的过程；同理，写操作也是先修改缓冲池中的数据页副本，后续再根据一定的时机将修改同步到磁盘上。
• 缓存替换策略：
  由于缓冲池的大小是有限的，不可能无限地缓存所有从磁盘读取的数据页，所以需要一种机制来决定当缓冲池空间不足时，哪些数据页应该被替换出去，为新读入的数据页腾出空间。InnoDB 采用了类似 LRU（Least Recently Used，最近最少使用）的算法及其变体来实现缓存替换策略，不同版本的 MySQL 在具体实现细节上可能会稍有差异。

当使用了where语句，SELECT COUNT(*) 时可能仍会读取全部数据，刚好Buffer Pool不存在这些数据，这就导致查询进行大量磁盘IO，导致CPU、磁盘IO升高。

---