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 by
或order by limit
语句形式,优化器使用prefer_ordering_index
导致。
排查方法:确认变慢扫描行数变多的SQL语句形式是否是
select ... group by
或order by limit
。解决办法:
对于MySQL 5.6及5.7.33版本之前:可以通过在
group by
或order by
后的字段后加一个空字符的方式来避免使用该索引,提高性能。例如group by 字段+''
或order by 字段+''
。对于MySQL 5.7.33以上及8.0版本:可以通过设置
optimizer_switch
变量中的prefer_ordering_index
为off
来关闭这种优化器行为,从而提高性能。详细信息,请参见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 client
或Wrinting to net
的状态。解决办法:客户端应用尽量避免一次获取大量数据返回。