如何利用CloudDBA解决MySQL实例CPU使用率过高的问题

在使用MySQL数据库的过程中，经常会因CPU使用率过高而导致系统异常，如响应变慢、无法获取连接、出现报错等。在CPU使用率过高的场景中，有95%以上都是由异常SQL所致。另外，大量行锁冲突、行锁等待或后台任务也有可能导致实例CPU使用率过高，但这些状况出现的概率非常低，本文不做讨论。本文将介绍如何使用RDS的CloudDBA功能来定位系统中的慢SQL和其它异常SQL语句，然后您可通过CloudDBA提供的建议优化这些SQL语句来降低实例的CPU使用率以提升系统效率。

导致CPU使用率较高的原因

数据库中的数据以数据块为基本操作单位，一个MySQL数据块是8KB，一次逻辑读或物理读对应一个数据块的读取操作。

当数据库执行业务查询语句（包括数据修改操作）时，CPU会先从内存中请求数据块。如果内存中有对应的数据，CPU执行计算任务后会将结果返回给用户。如果内存中没有对应的数据，系统会触发从磁盘读取数据的操作。这两个数据获取过程分别是逻辑读和物理读，如下图所示。

当业务提交的SQL语句不够优化时，就会对数据库的性能产生如下影响：

• 使数据库产生大量的逻辑读，从而导致CPU使用率过高。

• 使数据库产生大量的物理读，从而导致IOPS和I/O延时过高。

解决方法

CloudDBA提供了如下两种SQL诊断功能：

• 诊断慢SQL：诊断当前实例最近1个月内的慢SQL，并给出慢SQL的优化建议。

• SQL统计：利用云数据库SQL审计数据，从多个维度分析SQL语句并给出慢SQL的优化建议。

所以，您可以通过如下两种方法来排查和优化导致CPU使用率过高的异常SQL语句。

前提条件

实例是RDS for MySQL的实例（5.7基础版除外）。

使用SQL诊断功能排查异常SQL语句

1. 登录RDS管理控制台。

2. 选择目标实例所在地域。

3. 单击目标实例ID，进入基本信息页面。

4. 在左侧导航栏中，选择CloudDBA > 问题诊断，进入问题诊断页面。

5. 选择慢SQL标签页。

6. 选择要查询的时间，然后单击确定。

   说明：目前，系统只支持显示最近1个月内的慢SQL数据。

7. 若实例中有慢SQL，图示中会以柱形图的方式显示慢SQL产生的时间点和个数。单击柱形图，下方的列表就会显示其对应的所有慢SQL信息，且柱形图会变成红色。

   

8. 分析慢SQL，重点关注表格中返回行数和扫描行数的值。如下图中慢SQL，每条SQL语句都有很多扫描行数但返回行数都为0，说明系统产生了大量的逻辑读和物理读。产生物理读是因为内存大小有限，不可能缓存所有数据，当有大量数据请求时必然会产生大量物理I/O请求。大量的逻辑读会占用大量的CPU资源，导致CPU使用率上涨。

   

9. 单击SQL语句栏中的SQL语句，查看该SQL语句的详情。

10. 单击SQL优化建议，即可查看CloudDBA给该SQL语句提出的优化建议。

    

    从上图的分析结果可以看出，该SQL语句执行时缺少对应的索引，导致执行该语句时要全表扫描。另外，根据MySQL的数据更新机制，每次执行该语句时整个表格都会被锁，进而使问题更加严重。凡是执行该语句的session都会出现排队等待的状况，单次执行成本又极高，所以就很容易导致CPU使用率较高甚至达到100%的状况。

11. 根据优化建议优化异常SQL语句，CPU使用率过高的问题就会随之而解。

使用SQL统计功能排查异常SQL语句

若使用SQL统计功能排查异常SQL语句，实例必须先要开通SQL审计，详情请参见SQL审计。

说明：SQL审计需另计费，为节约成本，您可以在查看数据库SQL语句前开通SQL审计，然后待问题排查完毕后再关闭该功能。

1. 登录RDS管理控制台。

2. 选择目标实例所在地域。

3. 单击目标实例ID，进入基本信息页面。

4. 在左侧导航栏中，选择CloudDBA > SQL统计，进入SQL统计页面。

5. 选择CPU，并选择要进行数据分析的时间范围，然后单击确定，状态图中即会显示当前实例的CPU在指定时间段内的使用率状况。

   注意：您最多只能选择1天的时间段。

   

6. 选择获取审计日志的起始时间（需在步骤5中所选择的时间范围内）以及时长，然后单击获取审计日志。

   

7. 分析任务创建成功后，页面列表中会显示分析进度。

   

8. 分析任务完成后，找到目标分析记录，并单击SQL分析栏下的查看，进入SQL分析详情页面。

   

9. 选择分析维度（执行次数、执行时间、扫描行数、返回行数、更新行数），并从不同维度分析SQL语句。

   • 执行次数维度

     在执行次数维度下，默认根据总执行次数倒序排序SQL语句，以定位执行量较大或者执行量有异常变化的语句，然后分析语句执行量的合理性并根据CloudDBA给出的建议优化SQL语句。

     如果语句执行量相对比较合理，而且执行量大的SQL也是最优的，那么建议您升级实例规格，或者使用读写分离功能来分担执行量较大的SQL语句。您也可以通过优化程序来解决数据库的查询问题，例如采用Redis的缓存系统来缓存量大的最优SQL语句。缓存的成本相对较低，且响应速度更快，能够极大地优化系统的整体性能。

     分析示例：

     

     上图中，通过对比平均执行时间和最大执行时间，可以判断出执行次数最多的前两个SQL语句的平均执行时间很短，说明这两个SQL语句性能没有问题，您可以通过升级实例规格或开通读写分离功能来解决主库CPU压力大的问题。但这两个SQL语句的最大执行时间却很大，达到了30ms以上，说明整个系统存在波动，需要通过其它维度找出其它异常SQL语句。

   • 执行时间维度

     该维度默认按照执行时间倒序排列SQL语句，可以协助您筛选出执行总量不大但单次执行时间却很长的SQL语句。每次执行这类语句时，都会极大影响系统的响应时间和CPU使用率，当被大量执行时，CPU使用率就会较高甚至达到100%，系统卡死。

     分析示例：

     

     上图中，第二条SQL语句总共执行了100次，执行次数占比为0，但总执行时间占比却是12%，且单次最大执行时间约为300秒，说明了该语句的性能极差。单击该语句，通过语句详情可以看出，order by子句中既有asc又有desc排序，导致无法高效使用SQL排序，而where条件的过滤性很低，所以该SQL语句的性能很差。另外，由where条件过滤性能较低导致的SQL语句性能问题，还可以通过扫描行数维度排查。

   • 扫描行数维度

     该维度默认按照扫描行数倒序排列SQL语句，可以协助您筛选出每次执行都会查询很多条记录的SQL语句，每次执行这类语句就会产生大量的逻辑读甚至物理读。针对这类SQL语句，如果语句已经足够优化，建议您优化程序，在where条件中传入更多的筛选条件，从而可以避免SQL语句单次查询时扫描很多行。

     分析示例1：

     

     与执行时间维度相比，从扫描行数维度查看SQL语句性能时能筛选出更多异常SQL。例如，上图中执行次数为58次的语句占总执行时间的比例仅为0.63%，所以在执行时间维度的查询结果中很难搜索到该异常SQL。单击该SQL语句并查看CloudDBA给出的优化建议，如下图所示。

     

     根据CloudDBA的分析可知，该SQL语句的写法存在很大的性能问题。因为数据库已经有了本条语句所需要的索引，所以SQL语句中不需要额外创建索引来优化性能。此类写法导致无法使用正确的索引，所以执行该类SQL时会出现性能问题。根据CloudDBA给出的建议，针对此类异常SQL，您只需要修改SQL语句的写法即可降低CPU使用率，从而优化系统性能。

     分析示例2：

     

     此外，扫描行数维度有时还能找出有性能隐患的SQL语句。例如，对比上图中第一条和第四条SQL语句，它们的执行次数占比分别是40.99%和47.93%，都占系统运行量的很大比重。同时，第一条和第四条语句的平均执行时间分别是1.35毫秒和0.19毫秒，虽然有些差距但性能都还不错。但若对比二者扫描行数和执行次数的比值，即平均单次扫描的行数，可看出单次执行第一条SQL时会扫描约500行数据，单次执行第二条SQL时会扫描约15行数据，说明第一条语句单次扫描行数过多，可能有异常。第一条SQL语句的执行次数占比很大，如果能将该语句的单次扫描行数从500行优化到10行以内，可以极大地提升系统的性能。

   • 返回行数维度

     该维度默认按照返回行数倒序排列SQL语句，会展示出单次执行返回很多行的SQL语句，一般这类SQL语句常见于导出数据的场景。其实，非正常业务的这类SQL语句破坏力很大，当大量返回数据库的数据时很容易撑爆程序的内存，会严重破坏业系统整体的稳定性。

     分析示例：

     

     上图中，执行次数最多的SQL语句总的返回行数也较多是正常现象。但从第二条语句开始，语句的总执行量很小，但返回行数却很多，都需要优化。例如，第三条语句，每次执行该语句时都会均等地返回1000条数据，业务场景应该是获取TOP 1000的数据的需求或某种数据导出需求。 除了优化该SQL语句外，还建议您采用缓存、利用只读实例、给应用程序做开关限流排队等优化手段降低数据库CPU的压力。

   • 更新行数维度

     该维度默认按照更新行数倒序排列SQL语句，可协助您找出单次执行写SQL语句时所影响的行数。另外，写SQL语句还会影响到锁、事务、连接数等问题，所以有性能问题的写SQL语句对数据库的破坏力极大，很容易导致系统卡住、大量锁争用甚至死锁、有很多执行和排队的连接等，最终使系统无法响应业务请求。

     分析示例：

     

     上图中，更新行数和执行次数的比例基本为1：1，即平均执行一次写SQL时会更新一条记录，理论上性能应该很好。但根据最大执行时间可以看出，大部分写SQL的最大执行时间都超过了10ms，这说明整个数据库有很多异常时刻，极大地影响了更新性能。根据最小执行时间可以看出，大部分写SQL的性能都很好，但最后一条语句的性能极差，每次执行时都会影响系统的稳定性，例如CPU的使用率会出现一个尖刺，所以需要优化该语句。

     单击最后一条SQL语句，并查看其SQL优化建议，系统会返回如下结果。经CloudDBA分析，该语句的写法和索引都没有问题，但由于存在where条件的字段隐式转换，使update语句不能正确使用索引，导致每次执行该语句时都是全表扫描并且锁全表，这就是该语句性能较差的原因。所以，只需要在where条件中显示转换sid字段类型即可优化该SQL语句。