文档

RDS MySQL SQL执行变慢问题

更新时间:

RDS MySQL SQL执行性能受实例负载、SQL执行计划等诸多因素影响。本文将从实例出现SQL性能波动时,针对扫描行数是否变化这一方面,分别介绍可能原因及排查、解决办法。

扫描行数变多的情况

扫描行数变多可能由两种因素引起:

因素一:执行计划发生变化

SQL执行计划发生变化的可能原因包括:

原因1:实例发生高可用HA切换,切换前后主备实例统计信息差异导致。

  • 排查方法:查看实例SQL性能下降前后是否有HA切换。

    您可以在RDS控制台实例详情页的服务可用性 > 主备切换日志页面查看实例切换日志进行确认。

  • 解决办法:主动对SQL变慢涉及的表进行统计信息收集,或者执行optimize重组表。如果没有效果可以增加表的统计信息采样页个数,然后再重新收集表统计信息;或者手动进行一次HA切换。相关操作,请参见管理主备切换

    说明

    手动HA切换并不一定能使用到之前的节点,如果切换后发生备节点重建,那么最终使用的还是新节点,这可能导致统计信息的差异,从而影响SQL性能差异。

原因2:数据是从一个实例迁移到另一个实例的,例如通过DTS从实例A迁移到实例B,可能导致表的碎片率和统计信息出现差异。

  • 排查方法:确认数据是否刚从其他实例迁移而来。

  • 解决办法:主动对SQL变慢涉及的表进行统计信息收集,或者执行optimize重组表。如果没有效果可以增加表的统计信息采样页个数,然后再重新收集表统计信息。

原因3:表的统计信息手动或自动更新后,更新后统计信息差异较大导致。

  • 排查方法:检查SQL变慢涉及的表的统计信息,查看是否发生更新。

  • 解决办法:主动对SQL变慢涉及的表进行统计信息收集,或者执行optimize重组表。如果没有效果可以增加表的统计信息采样页个数,然后再重新收集表统计信息。

原因4:内核小版本或者大版本发生变化。

  • 排查方法:您可以在RDS控制台的任务列表页面查看实例管控任务进行确认。更多详情,请参见任务列表

  • 解决办法:内核小版本或者大版本发生变化引起SQL执行计划发生变化时,可以主动对SQL变慢涉及的表进行统计信息收集,或者执行optimize重组表。如果没有效果可以增加表的统计信息采样页个数,然后再重新收集表统计信息。

原因5:典型排序分页场景中,使用select ... group byorder by limit语句形式,优化器使用prefer_ordering_index导致。

  • 排查方法:确认变慢扫描行数变多的SQL语句形式是否是select ... group byorder by limit

  • 解决办法:

    • 对于MySQL 5.6及5.7.33版本之前:可以通过在group byorder by后的字段后加一个空字符的方式来避免使用该索引,提高性能。例如group by 字段+''order by 字段+''

    • 对于MySQL 5.7.33以上及8.0版本:可以通过设置optimizer_switch变量中的prefer_ordering_indexoff来关闭这种优化器行为,从而提高性能。详细信息,请参见ORDER BY LIMIT执行效率低

因素二:相同执行计划下,满足条件的数据数量增多

  • 原因:此类场景可能是本身导入了新的数据,导致满足条件的数据数量增加;也可能是查询条件变化后满足条件的数据增多导致。

  • 排查方法:可以通过洞察日志或者binlog日志分析。更多详情,请参见SQL洞察和审计

扫描行数基本不变的情况

在扫描行数不变的情况下,SQL执行效率变低可能有如下原因:

原因1:CPU或IOPS或IO带宽影响。

  • 排查方法:您可以在RDS控制台实例详情页的监控与报警 > 标准监控页面查看MySQL CPU使用率、MySQL IOPS使用率、MySQL每秒读写吞吐量指标进行确认。

  • 解决办法:扩容提升资源或者优化相关SQL,减少资源使用量。如何扩容,请参见变更配置

原因2:半同步提交机制会影响写入类操作。

  • 排查方法:您可以在RDS控制台实例详情页的日志管理 > 错误日志页面查看实例是否存在半同步超时日志(关键字:Timeout waiting for reply of binlog)。

  • 解决办法:评估是否可以调整复制模式为异步模式。如需调整,请参见查询和修改数据复制方式

原因3:客户端应用从服务器获取大量数据,客户端处理不及时。

  • 排查方法:待复现时查看客户端会话状态,确认是否处于Sending to clientWrinting to net的状态。

  • 解决办法:客户端应用尽量避免一次获取大量数据返回。