在前两讲中，我分别介绍了 MySQL 和 Linux 操作系统问题排查的基本思路，提供了一些判断数据库和操作系统是否有问题的方法。这一讲我们就以一个生产环境中发生的故障为例，来看看怎么运用前面讲到的基本方法，来分析和定位真实环境下的问题。
背景介绍
这是我们公司服务的一家客户在线业务系统中发生的故障。这家客户提供在线支付业务，大量的消费者日常都会使用到这项服务。比如我们中午或傍晚到店里扫码点餐付款时，或者在商场里扫码付款时，都可能会用到这项服务。当时，我们给这家公司提供了数据库运维保障的服务。客户的核心业务数据库采用了国内头部的一家云厂商提供的 RDS for MySQL 产品。
有一天下午，快接近 6 点钟的时候，短促的告警声打破了办公室的平静，客户的告警通知群里面，出现了大量的实例 CPU 和活跃会话数的告警消息。通过监控大屏，我们发现问题都出在一个核心数据库上，数据库实例的 CPU 资源，几乎在瞬间达到了 100%。同时客户也反馈，业务端有大量的付款失败了。
怎么分析数据库 CPU 使用率高的问题？
对于数据库 CPU 使用率打满的问题，一般我们都会先从实例中运行的 SQL 入手。
有两种情况都可能会导致 CPU 打满，
- 一种情况是实例中运行了执行效率特别低的 SQL，如大表全表扫描、大表连接并且缺少合适的连接条件或连接索引。
- 另外一种情况是 SQL 的执行频率太高了。
更差的情况是，SQL 效率很低，同时执行的并发量还很高。
一般我们会先到 PROCESSLIST 和慢 SQL 日志中找可疑的 SQL。但是 PROCESSLIST 只是实例所有会话的一个快照，如果 SQL 执行的时间比较长，那么在 PROCESSLIST 中看到这个 SQL 的概率就比较高，但是对于执行时间没那么长的 SQL，你很可能就看不到。
而慢查询日志，只有查询的执行时间超过 long_query_time 后，才会记录下来。有时候我们需要分析全量 SQL，才能精确定位问题。各大云厂商提供的 RDS 产品，通常会提供 SQL 审计功能，将实例中执行的每一条 SQL 都记录下来，我们可以充分利用全量 SQL 审计日志，来分析某一个时间数据库中运行的 SQL。
此外，对于 CPU 使用率突变的情况，我们还要找到是什么原因导致了 SQL 的执行情况发生突变。一般我们可以先从以下几个方面来考虑。
业务上有没有做推广引流，是不是有什么活动？
是不是触发了平时不怎么执行的一些 SQL，比如后台人员在对账，或者大数据系统在大规模抽取数据，或者是有大商户操作了管理后台，执行了一些开销很大的 SQL，或者研发人员执行数据订正。
应用程序是不是发布了新的版本，修改了 SQL 代码，导致 SQL 性能变化？
是不是有 SQL 的执行计划发生了变化，导致原先效率还可以的 SQL，性能急剧下降？
有没有可能数据库底层的操作系统和硬件环境的异常导致性能抖动，从而引起 SQL 积压？
在客户的这次故障中，我们排除了上面所有的可能性。由于使用了 RDS，我们没有底层操作系统的权限，但是云厂商的工程师也参与到了故障分析中，他们确认了底层操作系统和硬件并没有问题。而且分析之后，我们也没有发现特别明显的问题 SQL，ProcessList 中看到的都是平时也一直在运行的正常的业务 SQL。
优先恢复业务
故障期间数据库还发生了几次自动切换，但是业务依然没有恢复正常。初步分析业务 SQL，也没有找到明确的问题。此时故障还在持续，每一分钟的故障时间，都会给业务造成直接的资金损失。既然数据切换之后也没有恢复，当时我们就把应用程序进行了重启。应用重启之后，业务就立刻恢复了正常。
这也是我们处理故障的一个原则，遇到故障时，快速收集故障现场必要的信息之后，即使无法迅速定位到故障的根本原因，也要在第一时间恢复业务的正常运行。
全量 SQL 分析
当然，在故障恢复之后，我们需要找到引起故障的真正原因，因为只有定位到了根本原因，你才能避免下一次出现同样的故障。针对这次故障中的实例，已经启用了 RDS 产品提供的 SQL 洞察和审计功能，我们使用这个功能对故障期间执行的 SQL 做了分析。我们分析了故障发生前，数据库中扫描行数、返回行数看起来比较高的一些 SQL，并对这些 SQL 进行了一些优化。
故障再次发生
虽然我们找到了一些 SQL，做了相应的优化，但实际上，这并不是引起故障的真正原因。在接下来几天的时间里，我们严密监控数据库的运行情况，这期间数据库运行得很平稳。然后，差不多离上次故障近 1 周的一个下午，也是在下午 5 点多的业务高峰期，又发生了类似的故障。有了上次的经验，客户先重启了应用系统，恢复了业务。
但是故障的根本原因是什么？这次必须找出来。我和几位 DBA 同事一起赶到客户现场，在一间会议室里，跟客户侧的技术负责人、架构师、运维和研发人员，一起分析问题。上次使用常规的分析方法并没有定位到故障的根本原因。我们遗漏了哪些关键信息呢？
针对这次故障，我重新梳理了一下思路。从 RDS 的监控指标中可以确认几点。
数据库的 CPU 确实在极短的时间里，冲到了 100%。之后有短暂的回落，但是后面 CPU 使用率又上升到了 100%，并且一直持续高位运行，直到最后重启了应用系统后才恢复正常。
CPU 上涨的同时，数据库 SQL 执行量也差不多翻了一倍，时间上很吻合。
在 CPU 使用率上升的同时，数据库连接数上涨了好几倍。因此我们认为，由于某种原因，应用程序在瞬间创建了大量连接并执行 SQL，超过了 MySQL 的承受能力，引起性能恶化。
但是我们跟客户确认过，当时并没有做任何推广活动，应用系统也没有任何发布、变更。虽然当时是一天内业务的一个高峰期，但正常的业务流量是逐渐上涨的，也不会说一下子就涨好几倍。
我们再来看数据库的网络流量趋势图，和 CPU 使用率、QPS 等指标的变化趋势是完全一致的。
仔细观察上面这几个性能趋势图，可以发现有一个共同点，就是在指标暴涨之前，先有一个短暂的下跌的过程，这个过程很短，大概也就 10-20 秒的时间。实际上在上一次的故障中，我们也观察到了同样的现象。当时也对可能的原因做了一些思考，是不是数据库底层环境有性能抖动？但是云厂商的运维人员做了细致地排查后，排除了这种可能性。是不是应用程序的某些特定的逻辑引起的？我们和客户侧的技术人员进行了一些探讨，但也没找到确切的原因。是不是最终用户在那个时间点都不用这个系统了？我们想想也觉得可能性不大。
从业务链路上来看问题
我们跟客户侧的架构师一起，梳理了业务系统的部署结构和调用链路。下面这个架构图中，位于最上层的是负载均衡设备，会把来自用户的请求分发到 nginx，nginx 再把请求转到后端应用，后端应用还会通过 Web Service 调用一些服务接口，位于架构最底层的是 RDS 和 Redis。这也是比较典型的一种 Web 应用部署架构。
要判断来自用户的请求量是否有大的变化，实际上可以看入口处负载均衡的调用量，也可以通过分析 nginx 的访问日志得到。通过这些日志，我们得到了第一个结论，就是来自用户的请求在故障发生的时间点前后，并没有发生明显的变化。
是不是链路上的某个应用内部发生了什么变化？
我询问了客户侧的参会人员，当天下午发生故障前，有没有对应用系统做过任何操作？当天肯定没有做应用发布。后来一位工程师反馈，当时他做了一个数据订正，订正的内容是删除了 Redis 中的几个 Key。正常来说，删除缓存中的几个 Key，也不至于造成这么大的影响。我又详细询问了这位工程师，当时操作执行了哪些具体命令。工程师反馈说，他先通过 keys pattern 命令查找需要删除的 Key，然后再把这几个 Key 删除掉。
到这里，你可能已经知道问题出在哪里了。Redis 中执行 keys 命令是非常危险的一个操作，如果 key 的数量比较多，keys 命令可能需要执行很长的时间，更严重的是，执行期间会导致其他请求被阻塞。
这个客户的 Redis 没有在我们的服务范围内，因此我们也根本没有想过这方面的问题。听到工程师的反馈后，我们立刻查看了 Redis 的监控。在数据库流量下跌的同一个时间，Redis 的 CPU 使用率有大幅度的上涨，最高达到了 100%。
看来对 Redis 的操作才是引起故障的真正原因。为了更好地理解这个问题，我画了一个程序访问的时序图。用户扫码付款时，应用程序请求某个 API，读取 Redis 中的一个 Key，但由于当时 Redis 在执行 keys * 命令，读取 Key 的请求就被阻塞了。
keys 命令执行了十几秒，因此这十几秒里，调用这个接口的用户线程都需要等待。最终，keys 命令执行完成，等待的用户线程都完成了接口调用，然后再调用其他接口，到 MySQL 中执行 SQL。积压了十几秒的用户线程，几乎在同一个时间点连接数据库，执行 SQL，最终超出了数据库的承受能力，发生了故障。这和我们从前面几个监控图表中观察到的现象完全一致。
下面这个图可以更好地解释这个现象。应用程序发起的请求，先在 Redis 中积压了十几秒，然后又几乎在同一个时间点从 Redis 请求中返回，一瞬间，这些请求同时涌向了 MySQL，MySQL 不堪重负，发生了故障。
定位到最终问题后，我们和客户悬着的心终于放了下来。接下来，客户在内部进行了强调，禁止在线上执行危险的操作。对于 keys * 操作，也可以在 Redis 层面禁用掉。之后，就再也没有发生过这样的故障了。
总结
在这个故障案例中，MySQL 的故障，由看起来毫不相关的一个 Redis 操作引起。这也警示了我们，在分析问题时，要从系统全局出发，不能局限在数据库内部。同时也要注意，在给一个问题下结论的时候，要多问问自己，这个问题真的是这个原因引起的吗？是不是能解释问题中的所有现象？
思考题
MySQL 中存在这么一个现象，平时执行得好好的 SQL，在数据库很繁忙的时候，执行效率也会变得很差，当然，这可以理解。你从 Processlist 或慢 SQL 日志中看到执行耗时比较长的 SQL，其中有些是引起数据库性能问题的罪魁祸首，有些则是受害者。你应该怎么区分这两种情况，找到真正需要优化的那些 SQL 呢？
回答
当数据库出现性能问题时，我们往往会在 Processlist 或 慢查询日志 中发现一些执行耗时较长的 SQL。然而，这些慢 SQL 中有些是引发性能问题的 罪魁祸首，而有些则是因为数据库繁忙或资源被占用而变慢的 受害者。为了有效地解决问题，我们需要区分这两种情况，找到真正需要优化的 SQL。以下是具体的分析思路和方法：
1. 分析 SQL 的资源消耗
首先，我们需要评估慢 SQL 对数据库资源的消耗程度，包括 CPU、内存、磁盘 I/O 和网络 I/O 等。
- 高资源消耗的 SQL：如果某些 SQL 消耗了大量的 CPU 或 I/O 资源，那么它们很可能是性能问题的罪魁祸首，需要重点关注和优化。
- 低资源消耗但执行缓慢的 SQL：如果某些 SQL 资源消耗不高，但执行时间长，可能是由于等待资源（如锁）导致的，是受害者。
工具和方法：
- 使用 Performance Schema 或 EXPLAIN 命令，查看 SQL 的执行计划和资源消耗。
- 借助 慢查询日志，分析
Rows_examined、Rows_sent等指标，判断 SQL 的扫描行数和返回行数。
2. 检查等待事件和锁竞争
慢 SQL 可能是由于等待资源（如行锁、表锁）导致的。我们需要检查这些 SQL 是否在等待其他事务释放锁，从而导致执行时间变长。
- 锁等待的 SQL（受害者）：如果 SQL 长时间处于等待状态，可能是由于其他事务持有锁导致的，是性能问题的受害者。
- 持有锁的 SQL（罪魁祸首）：那些长时间持有锁的事务，会阻塞其他事务，需要重点优化。
工具和方法：
- 使用
SHOW ENGINE INNODB STATUS，查看当前的锁信息和等待事务。 - 查询 Information Schema 的
INNODB_LOCKS和INNODB_LOCK_WAITS表，分析锁竞争情况。 - 使用 Performance Schema 的
events_waits_current表，查看等待事件。
3. 评估 SQL 的执行频率
有些 SQL 单次执行效率不高，但执行频率很低，对整体性能影响有限。而一些执行频率非常高的 SQL，即使单次执行耗时短，但累计起来可能消耗大量资源。
- 高频率 SQL（罪魁祸首）：需要关注执行频率高、累计资源消耗大的 SQL，优化其效率。
- 低频率 SQL（可能非主要原因）：对于执行频率低的慢 SQL，可以根据实际情况决定是否优化。
工具和方法：
- 使用 MySQL 官方的 sys 库，查询
statement_analysis，统计各 SQL 的执行频率和累计耗时。 - 借助 pt-query-digest 等工具，分析慢查询日志，按执行频率和总耗时排序。
4. 对比正常运行时的基线
将当前慢 SQL 的执行情况与数据库正常运行时的基线数据进行对比，找出异常的 SQL。
- 异常变化的 SQL（罪魁祸首）：如果某些 SQL 的执行计划、耗时、资源消耗相比基线有明显变化，需要重点分析。
- 无明显变化的 SQL（可能是受害者）：如果 SQL 的执行情况与正常时无异，但耗时变长，可能是受其他因素影响。
工具和方法：
- 保留数据库的性能基线数据，定期收集关键 SQL 的执行计划和性能指标。
- 使用 EXPLAIN 对比 SQL 的执行计划是否发生变化，如索引未被使用、统计信息失效等。
5. 分析系统整体性能
有时候，系统资源（如 CPU、IO）达到瓶颈，导致所有 SQL 的执行效率都下降。此时，需要从系统层面找出资源消耗大的操作。
- 资源占用大的操作（罪魁祸首）：如大量的全表扫描、大量的数据导入导出等。
- 正常操作受影响（受害者）：常规的业务 SQL 由于资源紧张，执行变慢。
工具和方法：
- 监控系统资源使用情况，查看 CPU、内存、磁盘 I/O、网络等指标。
- 使用 操作系统工具（如
top、iostat、vmstat）和 数据库性能监控工具，定位资源消耗大的进程或线程。
6. 考虑锁的粒度和事务隔离级别
高并发环境下，事务的隔离级别和锁的使用方式也会影响 SQL 的执行效率。
- 导致锁争用的事务（罪魁祸首）：如长事务、大量更新操作，需要优化锁的粒度或调整隔离级别。
- 被锁阻塞的事务（受害者）：等待锁释放的 SQL，执行时间变长。
工具和方法：
- 检查事务的隔离级别，考虑是否可以降低隔离级别（如从 SERIALIZABLE 降到 READ COMMITTED）。
- 优化事务的设计，尽量缩短事务的执行时间，减少锁的持有时间。
7. 排查最近的变更
回顾数据库或应用层面的最近变更，可能是引发性能问题的原因。
- 新引入的低效 SQL（罪魁祸首）：最近的代码或配置变更可能引入了性能差的 SQL。
- 受变更影响的 SQL（受害者）：其他 SQL 可能因为变更导致资源竞争，执行变慢。
工具和方法：
- 与开发团队沟通，了解近期是否有应用发布、配置修改等。
- 查看数据库的 DDL 操作记录，是否有索引被删除、表结构被修改等。
8. 综合评估，制定优化策略
在收集和分析以上信息后，对慢 SQL 进行分类：
- 需要优化的 SQL：高资源消耗、执行计划不佳、执行频率高的 SQL，需要重点优化。
- 暂时受影响的 SQL：由于资源争用、锁等待等原因变慢的 SQL，可以先优化导致资源争用的操作。
优化建议：
- 索引优化：为慢 SQL 添加合适的索引，避免全表扫描。
- 查询优化：重写低效的 SQL，避免不必要的复杂查询。
- 事务优化：缩短事务时间，降低隔离级别，减少锁争用。
- 资源扩容：如果硬件资源长期处于瓶颈，考虑升级服务器配置或进行读写分离。
总结
要区分慢 SQL 是性能问题的 罪魁祸首 还是 受害者，需要从多维度进行分析，包括资源消耗、锁等待、执行频率、执行计划变化等。通过综合评估，找到真正导致性能问题的 SQL，针对性地进行优化，才能有效提升数据库的性能。
要点总结
- 数据库CPU使用率突然上升到100%，导致业务出现大量付款失败的问题。
- 通过全量SQL分析，找到了一些SQL并进行了优化，但并非引起故障的真正原因。
- 故障再次发生后，通过监控指标确认数据库CPU在极短时间内冲到100%，并且数据库连接数也上涨了好几倍，推测是应用程序瞬间创建了大量连接并执行SQL，超过了MySQL的承受能力。
- 通过RDS的监控指标确认数据库的CPU、SQL执行量和连接数的变化趋势，以及观察到指标暴涨之前的短暂下跌过程，来推测故障的根本原因。
- Redis中执行 keys * 命令是非常危险的一个操作，可能导致其他请求被阻塞，最终引起MySQL故障。
- 在分析问题时，要从系统全局出发，不能局限在数据库内部，同时要注意给问题下结论时，要多问问自己，这个问题真的是这个原因引起的吗？
- 思考如何区分在数据库繁忙时执行效率变差的SQL中的罪魁祸首和受害者，找到真正需要优化的SQL。
- 快速恢复业务的正常运行是处理故障的一个原则，即使无法迅速定位到故障的根本原因。
- 重点强调快速收集故障现场必要的信息，即使无法迅速定位到故障的根本原因，也要在第一时间恢复业务的正常运行。
