AWS RDS MySQL 5.6.40高负载下存储过程性能优化咨询

这种高负载下存储过程性能跳水的情况我碰到过好多次，咱们从几个核心方向入手排查优化，争取把执行时间稳定在1秒左右：

• 先抓执行计划：针对存储过程里的每一条查询，用EXPLAIN看执行路径，重点盯有没有全表扫描、临时表创建、文件排序这些高消耗操作——高负载下，这些操作的资源占用会被成倍放大，本来1秒能搞定的，负载上来就会卡。如果是复杂的存储过程，还可以用SET profiling = 1;执行存储过程后，SHOW PROFILES;看每一步的耗时，精准定位拖慢的环节。
• 核对索引有效性：存储过程涉及的所有表，检查where过滤、join关联、排序分组的字段有没有建立合适的索引。比如有没有用WHERE name LIKE '%xxx%'这种导致索引失效的写法？或者join的时候两边字段类型不匹配？可以用SHOW INDEX FROM 表名查看现有索引，也可以通过EXPLAIN里的key列看有没有用到索引，rows列看扫描的行数是不是过多。
• 拆解冗余逻辑：如果存储过程里有循环、多层嵌套查询，或者反复调用同一个子查询，试着把这些改成更高效的写法。比如把循环处理改成批量SQL操作，把子查询换成join，或者把重复计算的结果存到内存临时表（记得设置ENGINE=MEMORY，避免磁盘临时表的IO开销）。

你的实例是db.t2.medium（4GB内存），高负载下很容易出现资源耗尽的情况：

• 监控CPU credits：t2是突发性能实例，高负载下会快速耗尽CPU credits，导致性能骤降。去AWS控制台看CloudWatch的CPUCreditBalance指标，如果这个值持续走低，说明CPU资源不够——要么换成db.m系列的通用型实例（比如db.m4.medium），要么开启CPU credits的无限模式，解决突发性能瓶颈。
• 调整InnoDB缓冲池：MySQL 5.6默认的innodb_buffer_pool_size可能没用到4GB内存的最优配置，一般建议设置为内存的70%-80%（比如4GB内存就设为2.8GB左右）。用SHOW VARIABLES LIKE 'innodb_buffer_pool_size'查看当前值，然后在RDS参数组里调整。另外看SHOW ENGINE INNODB STATUS里的缓冲池命中率，如果低于99%，说明缓冲池不够，数据频繁换入换出，高负载下更严重。
• 检查IO性能：高负载下IO瓶颈会凸显，看CloudWatch的ReadLatency、WriteLatency指标，如果延迟过高，考虑换成GP3存储（如果当前是GP2），或者增加IOPS，甚至用Provisioned IOPS存储，尤其是当存储过程涉及大量磁盘读写的时候。

高负载下，锁竞争是常见的性能杀手：

• 检查InnoDB锁等待：用SHOW ENGINE INNODB STATUS里的TRANSACTIONS部分，看有没有长时间的锁等待事务。存储过程里如果涉及更新、删除操作，有没有加不必要的锁？比如用了SELECT ... FOR UPDATE但其实不需要，或者事务太长导致锁持有时间久。
• 缩短事务时长：存储过程里的事务尽量短小，把无关的查询操作移出事务，只把必须的更新/删除逻辑放在事务里，减少锁的持有时间。比如如果存储过程里有查询+更新，能不能先执行查询拿到结果，再开启事务做更新？
• 避免热点行竞争：如果存储过程里频繁更新同一条记录，高负载下会导致大量锁等待。这时候可以考虑拆分热点数据，或者用乐观锁（比如加版本号字段）代替悲观锁，减少锁冲突。

• 查询缓存（谨慎使用）：MySQL 5.6默认开启查询缓存，如果存储过程里的查询是参数化的，且对应的数据更新不频繁，查询缓存能大大减少重复计算。但如果数据更新频繁，查询缓存的失效会带来额外开销，这时候可以关闭。用SHOW VARIABLES LIKE 'query_cache_type'查看状态，SHOW STATUS LIKE 'Qcache%'查看缓存命中率。
• 应用层缓存：如果存储过程的结果不需要实时更新，可以在应用层把结果缓存到Redis或者Memcached里，直接返回缓存结果，减少数据库的请求量——高负载下这招能显著降低数据库压力。

MySQL 5.6已经停止官方维护了（EOL是2021年），新版本比如5.7或者8.0在性能、并发控制、优化器方面都有很大提升：比如5.7的优化器更智能，支持更多索引类型，InnoDB的并发性能更好。如果业务允许，升级RDS的MySQL版本，能从底层解决很多性能问题。

内容的提问来源于stack exchange，提问作者neildt