AWS Aurora MySQL多并发请求下查询性能骤降的排查与优化咨询

环境信息 #

• 数据库：AWS Aurora MySQL 8.0
• JDBC连接器：software.aws.rds,aws-mysql-jdbc / software.amazon.jdbc,aws-advanced-jdbc-wrapper
• 应用：Spring Boot 3.5.0
• Java版本：21

问题现象 #

负载测试结果 #

涉及的查询语句 #

SELECT product.id AS product_id 
FROM products product 
JOIN product_regions pr ON pr.product_id = product.id 
JOIN regions r ON r.region_id = pr.region_id 
WHERE product.is_active = 1 
  AND r.region_code = 'REGION_A' 
  AND ((product.product_code LIKE 'XYZ%' OR product.short_code LIKE 'XYZ%')) 
ORDER BY 
  CASE WHEN product.product_code LIKE 'XYZ%' THEN 0 ELSE 1 END, 
  product.product_code;

核心矛盾 #

• 单请求执行耗时稳定在~700ms
• 并发请求下耗时飙升至30秒以上，甚至触发504网关超时
• 日志显示并发时查询疑似被锁，最高耗时可达50秒

已尝试的优化动作 #

数据库配置调整 #

• 设置aurora_fwd_writer_max_connections_pct = 30
• 开启aurora_parallel_query = 1
• 开启aurora_read_replica_read_committed = ON

查询修改 #

• 移除了ORDER BY CASE子句进行测试，但效果不佳

索引调整 #

• 新增了(product_code, is_active)和(short_code, is_active)的复合索引

HikariCP连接池配置 #

spring.datasource.hikari.maximumPoolSize=100
spring.datasource.hikari.minimumIdle=75
spring.datasource.hikari.maxLifetime=900000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.leak-detection-threshold=240000
spring.datasource.hikari.register-mbeans=true
spring.datasource.hikari.connection-test-query=SELECT 1

针对问题的分析与建议 #

1. 为什么并发请求下性能会出现断崖式下跌？ #

结合你的现象和配置，大概率是这几个原因在共同作用：

• 锁竞争/资源争抢：虽然单请求是快照读（InnoDB默认RR隔离级别下），但如果查询需要创建临时表做排序或关联，并发下临时表的磁盘IO、内存资源会被争抢；另外如果你的业务有写操作在更新关联表（比如products、product_regions），哪怕是快照读也可能因为InnoDB的undo日志版本链过长，导致查询时版本回溯耗时增加。
• Aurora读写分流失效：你设置了aurora_fwd_writer_max_connections_pct=30，但如果这个只读查询没有被正确路由到读副本，所有并发请求都挤在写节点的30%连接配额里，会直接导致连接排队阻塞。
• 索引缺失导致的全表扫描/范围扫描并发放大：当前的索引只覆盖了products表的单一条件，关联查询时products、product_regions、regions三个表的关联逻辑没有对应的索引支持，单请求时IO压力不大，但并发下全表/范围扫描的IO开销会呈指数级放大。
• 连接池配置不合理：100的最大连接数太大，MySQL连接是重量级资源，过多并发连接会导致数据库上下文切换开销飙升，反而拖慢查询速度。

2. 查询优化与索引策略建议 #

先给你一套精准的复合索引方案，直接命中查询的过滤、关联、排序全链路：

• regions表：创建(region_code, region_id)复合索引。因为查询先通过region_code='REGION_A'过滤regions，再用region_id关联product_regions，这个索引可以让数据库直接定位到目标region_id，无需扫描全表。
• product_regions表：创建(region_id, product_id)复合索引。关联时用region_id快速找到所有关联的product_id，避免扫描整个product_regions表。
• products表：创建两个复合索引覆盖两种LIKE场景：
  • (is_active, product_code, id, short_code)：优先匹配product_code LIKE 'XYZ%'的过滤条件，同时包含关联需要的id和判断用的short_code，实现覆盖查询（无需回表取数据）。
  • (is_active, short_code, id, product_code)：匹配short_code LIKE 'XYZ%'的场景，同样实现覆盖查询。
• 针对排序逻辑：如果必须保留ORDER BY CASE，可以试试把排序逻辑和索引结合——比如给products表新增一个计算列sort_priority，用CASE WHEN product_code LIKE 'XYZ%' THEN 0 ELSE 1 END作为默认值，然后给(is_active, sort_priority, product_code, id)建复合索引，这样排序可以直接利用索引有序性，避免创建临时表排序。

另外，建议用EXPLAIN ANALYZE跑一下查询，确认执行计划里有没有用到并行查询、索引是否命中，有没有出现Using temporary或Using filesort的情况——这两个是并发性能杀手。

3. 数据库配置的调整建议 #

• 读写分流优化：确认你的JDBC连接器是否正确配置了只读路由规则，比如AWS高级JDBC wrapper的readFromReaderStrategy，确保这类只读查询100%路由到读副本，不要占用写节点的连接配额。如果写节点连接还是紧张，可以把aurora_fwd_writer_max_connections_pct调高到40-50，或者降低写节点的非必要连接占用。
• 并行查询验证：Aurora并行查询有适用条件，比如表数据量要足够大（一般建议10GB以上）、查询不能包含某些函数（比如UDF），可以用EXPLAIN看执行计划里有没有Using parallel query的标记，如果没有，说明并行查询没生效，可能需要调整aurora_parallel_query_scan_range_size等参数，或者放弃并行查询（小表用并行查询反而会有额外开销）。
• 隔离级别检查：如果你的应用用的是默认的REPEATABLE READ隔离级别，aurora_read_replica_read_committed=ON这个配置是无效的，建议把应用的事务隔离级别改成READ COMMITTED，让读副本可以提供更高效的快照读。
• 监控关键指标：去CloudWatch看Aurora的CPUUtilization、DiskQueueDepth、ReadLatency、CommitQueueLength这些指标——如果CPU打满，说明计算资源不够；如果DiskQueueDepth很高，说明IO瓶颈；如果CommitQueueLength飙升，说明写节点有事务堆积。

4. HikariCP连接池配置的优化建议 #

当前的配置太“激进”了，给你调整成更合理的参数：

spring.datasource.hikari.maximumPoolSize=25
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maxLifetime=900000
spring.datasource.hikari.connectionTimeout=5000
spring.datasource.hikari.idleTimeout=300000
spring.datasource.hikari.leak-detection-threshold=60000
spring.datasource.hikari.register-mbeans=true
#  MySQL 8.0支持JDBC4的isValid()，可以去掉connection-test-query
# spring.datasource.hikari.connection-test-query=SELECT 1
spring.datasource.hikari.testWhileIdle=true
spring.datasource.hikari.validationTimeout=3000

为什么这么调？

• maximumPoolSize=25：MySQL连接是重量级资源，过多连接会导致数据库上下文切换开销暴增，25的大小对于Aurora实例来说足够支撑高并发，同时避免资源浪费。
• minimumIdle=5：保持少量空闲连接即可，Hikari会自动根据请求量扩容，不用提前占满资源。
• connectionTimeout=5000：缩短连接等待超时，避免线程长时间阻塞在获取连接上。
• leak-detection-threshold=60000：缩短连接泄漏检测时间，方便及时发现连接未释放的问题。
• 去掉connection-test-query，用testWhileIdle+validationTimeout，更高效的连接验证方式。

另外，一定要检查应用是否有连接泄漏的情况——比如代码中有没有未关闭的Connection、Statement，或者事务未提交/回滚导致连接一直被占用。

内容来源于stack exchange