Aurora MySQL Serverless V2查询400万条数据过慢的优化咨询

• 数据库环境：AWS Aurora MySQL (Serverless V2)，引擎版本Aurora MySQL 3.04.0（兼容MySQL 8.0.28）
• 目标表结构：

CREATE TABLE "Test" (
  "key1" varchar(37) NOT NULL,
  "key2" varchar(50) NOT NULL,
  "key3" varchar(20) NOT NULL,
  "createdDate" timestamp(3) NOT NULL,
  "createdBy" varchar(50) NOT NULL,
  "updatedDate" timestamp(3) NOT NULL,
  "updatedBy" varchar(50) NOT NULL,
  PRIMARY KEY ("key1","key2","key3")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

• 慢查询语句：

select key3
 FROM Test WHERE key1 = 'value1' and key2= 'value2'

• 查询表现：返回3934328条数据，总耗时30-35秒，其中执行耗时0.141 sec，结果集获取耗时34.375 sec
• 已尝试操作：将innodb_buffer_pool_size从约2GB提升至7GB，无明显性能改善

1. 优化结果集传输与客户端处理 #

• 开启结果集压缩：在客户端连接参数中添加compress=true，大幅减少网络传输的字节量，针对大量字符串类型数据效果显著。
• 增大客户端fetch批次：默认客户端每次从数据库拉取的行数较少，可通过调整参数增加批次大小：
  • MySQL客户端：设置net_buffer_length=1M（根据实际情况调整）
  • 应用驱动：比如JDBC设置setFetchSize(10000)，减少数据库与客户端的往返次数
• 分页查询替代全量获取：若业务允许，改用分页方式获取数据，避免一次性加载百万级数据到内存。利用主键的有序性优化分页（避免大offset性能问题）：

select key3
from Test 
where key1 = 'value1' and key2 = 'value2' 
and key3 > '上一页最后一条key3值'
limit 10000;

2. 利用Aurora Serverless V2特性调优 #

• 检查并调整ACU配置：查看CloudWatch指标（CPUUtilization、NetworkTransmitThroughput），若CPU使用率接近100%或网络带宽饱和，临时提升ACU值，为查询分配更多计算/网络资源。
• 切换到只读副本执行查询：创建Aurora只读副本，将该查询路由到副本执行，既不占用主库资源，还可为副本单独配置更高的ACU。
• 启用结果缓存：若该查询会重复执行，将结果缓存到Redis等缓存服务中，直接从缓存返回结果，彻底绕过数据库查询。

3. 数据库层面细节优化 #

• 确认执行计划有效性：执行EXPLAIN ANALYZE验证查询是否走覆盖索引（当前主键(key1,key2,key3)前缀匹配，应触发Using index），确保数据库内部执行无瓶颈：

EXPLAIN ANALYZE
select key3
 FROM Test WHERE key1 = 'value1' and key2= 'value2';

• 调整只读实例IO参数：如果使用只读副本，可修改innodb_flush_log_at_trx_commit=2和sync_binlog=0，降低IO开销，提升查询响应速度。

4. 数据结构与存储优化 #

• 聚合查询替代明细查询：若业务仅需统计key3的分布而非全量明细，改用聚合查询缩小结果集：

SELECT key3, COUNT(*) 
FROM Test 
WHERE key1='value1' AND key2='value2' 
GROUP BY key3;

• 归档冷数据：若表中包含大量不常访问的历史数据，将其归档到S3或Aurora Archive存储，减少主表数据量，提升查询效率。

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