AWS Aurora MySQL 8.0中io/table/sql/handler等待事件致CPU飙升求助

我在AWS Aurora MySQL（8.0.mysql_aurora.3.04.0）中频繁出现io/table/sql/handler等待事件，官方文档说明该事件是工作负载活动增加导致I/O升高，进而引发CPU使用率飙升。

相关查询仅在只读实例上运行SELECT语句，我们是基于API的气象应用，IN子句中的参数数量在1000至5000之间（max_allowed_packets = 1GB）。查询涉及的表大小为250GB，已使用正确索引，执行时间通常为几秒甚至毫秒级。我是MySQL新手，希望通过调整配置参数来避免/降低CPU飙升。

异常CloudWatch指标 #

• AuroraSlowConnectionHandleCount
• AuroraReplicaLag
• DBLoadNonCPU
• ReadIOPS
• ReadLatency
• ReadThroughput
• SelectLatency

补充信息 #

• 表未分区，查询返回约200行（占总行数<1%），只读实例无DML操作。
• 表结构：

CREATE TABLE `TABLE_NAME` (
  `DATETIMES` bigint unsigned NOT NULL,
  `S_ID` int unsigned NOT NULL,
  `V_ID` smallint unsigned NOT NULL,
  `NOS` int unsigned DEFAULT NULL,
  `DELTA1` float DEFAULT NULL,
  `DELTA2` int unsigned DEFAULT NULL,
  `FLAGS` tinyint unsigned DEFAULT NULL,
  `YEARS` float DEFAULT NULL,
  PRIMARY KEY (`S_ID`,`DATETIMES`,`V_ID`),
  KEY `DATETIMES` (`DATETIMES`,`S_ID`,`V_ID`,`NOS`,`DELTA1`,`DELTA2`,`FLAGS`,`YEARS`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

• 查询及执行计划：

SELECT a.S_ID, a.V_ID, a.DATETIMES, a.YEARS, a.NOS
FROM DB_NAME.TABLE_NAME a
INNER JOIN (
    SELECT S_ID, V_ID, max(DATETIMES) DATETIMES
    FROM DB_NAME.TABLE_NAME  force index(DATETIMES) 
    WHERE DATETIMES >= 202310161117 and DATETIMES <= 202310161317 and S_ID in (1,2,5,9,....,4890)
    GROUP BY S_ID, V_ID
) b ON a.S_ID = b.S_ID AND a.V_ID = b.V_ID and a.DATETIMES = b.DATETIMES

-> Limit: 200 row(s)  (cost=687820.47 rows=200) (actual time=621.565..622.059 rows=71 loops=1)
    -> Nested loop inner join  (cost=687820.47 rows=580086) (actual time=621.564..622.053 rows=71 loops=1)
        -> Filter: (b.DATETIMES is not null)  (cost=0.11..65262.18 rows=580086) (actual time=621.543..621.565 rows=71 loops=1)
            -> Table scan on b  (cost=2.50..2.50 rows=0) (actual time=0.001..0.010 rows=71 loops=1)
                -> Materialize  (cost=2.50..2.50 rows=0) (actual time=621.542..621.556 rows=71 loops=1)
                    -> Table scan on <temporary>  (actual time=0.003..0.014 rows=71 loops=1)
                        -> Aggregate using temporary table  (actual time=621.498..621.515 rows=71 loops=1)
                            -> Filter: ((TABLE_NAME.DATETIMES >= 202310161117) and (TABLE_NAME.DATETIMES <= 202310161317) and (TABLE_NAME.S_ID in (1,2,5,9,....,4890)))  (cost=238947.80 rows=580086) (actual time=0.377..620.724 rows=713 loops=1)
                                -> Covering index range scan on TABLE_NAME using DATETIMES over (202310161117 <= DATETIMES <= 202310161317 AND 1 <= S_ID <= 4890)  (cost=238947.80 rows=1160172) (actual time=0.021..520.516 rows=738696 loops=1)
        -> Single-row index lookup on a using PRIMARY (S_ID=b.S_ID, DATETIMES=b.DATETIMES, V_ID=b.V_ID)  (cost=0.97 rows=1) (actual time=0.007..0.007 rows=1 loops=71)

已确认无全表扫描。

一、配置参数调整（针对Aurora MySQL） #

1. 缓存相关参数 #

• 提高innodb_buffer_pool_size：Aurora默认会根据实例大小自动调整，但如果只读实例内存充足，可手动设置为实例内存的70%-80%（例如64GB内存实例设置为42949672960即40GB），让更多数据和索引驻留内存，减少磁盘I/O与CPU开销。
• 关闭查询缓存：Aurora 8.0中查询缓存已废弃，确保query_cache_type=0、query_cache_size=0，避免不必要的缓存管理开销。
• 保留innodb_adaptive_hash_index：默认开启，可加速索引查找，对频繁的主键/索引查询有帮助，无需关闭。

2. 连接与线程参数 #

• 合理设置max_connections：根据API并发量调整，避免过多连接导致线程上下文切换开销；配合thread_cache_size=8-16，减少线程创建销毁的CPU消耗。
• 回收闲置连接：设置wait_timeout=300、interactive_timeout=300（5分钟），避免连接堆积占用资源。

3. I/O相关参数 #

• 调整日志刷盘策略：只读实例无DML，设置innodb_flush_log_at_trx_commit=2，减少日志刷盘的I/O负载，降低CPU使用率。
• 提升并行I/O能力：将innodb_read_io_threads从默认4提高到8或16，增强I/O并行处理能力，缓解瓶颈。

4. 查询优化器参数 #

• 关闭派生表合并：设置optimizer_switch='derived_merge=off'，避免子查询被强制物化，让优化器选择更高效的执行路径；同时尝试去掉force index(DATETIMES)，让优化器自动选索引。
• 优化连接缓冲区：适当提高join_buffer_size至131072（128KB），减少嵌套循环连接中的I/O次数。

二、查询优化建议（从根源降低负载） #

1. 优化大IN子句 #

大IN列表会消耗CPU解析，还可能扩大索引扫描范围（当前执行计划扫描738696行，远多于实际需要的713行），建议：

• 将IN列表拆分为多个小批量查询（每次100个S_ID），在应用层合并结果。
• 用临时表JOIN替代IN子句：

CREATE TEMPORARY TABLE temp_s_ids (s_id int unsigned PRIMARY KEY);
INSERT INTO temp_s_ids VALUES (1),(2),(5),...,(4890);

SELECT a.S_ID, a.V_ID, a.DATETIMES, a.YEARS, a.NOS
FROM DB_NAME.TABLE_NAME a
INNER JOIN (
    SELECT S_ID, V_ID, max(DATETIMES) DATETIMES
    FROM DB_NAME.TABLE_NAME t
    JOIN temp_s_ids ts ON t.S_ID = ts.s_id
    WHERE DATETIMES >= 202310161117 and DATETIMES <= 202310161317
    GROUP BY S_ID, V_ID
) b ON a.S_ID = b.S_ID AND a.V_ID = b.V_ID and a.DATETIMES = b.DATETIMES;

临时表主键可让JOIN更高效，避免大IN列表的解析和扫描范围过大问题。

2. 优化覆盖索引 #

当前DATETIMES索引是覆盖索引，但主键为(S_ID,DATETIMES,V_ID)，可创建针对性覆盖索引：

CREATE INDEX idx_s_datetime_v_covering ON TABLE_NAME(S_ID,DATETIMES,V_ID,YEARS,NOS);

子查询可直接使用该索引，减少扫描行数。

3. 用窗口函数替代子查询 #

窗口函数可避免子查询物化生成临时表，减少CPU和I/O开销：

SELECT S_ID, V_ID, DATETIMES, YEARS, NOS
FROM (
    SELECT 
        S_ID, V_ID, DATETIMES, YEARS, NOS,
        ROW_NUMBER() OVER(PARTITION BY S_ID, V_ID ORDER BY DATETIMES DESC) rn
    FROM DB_NAME.TABLE_NAME
    WHERE DATETIMES >= 202310161117 and DATETIMES <= 202310161317 
        AND S_ID IN (1,2,5,...,4890)
) t
WHERE rn = 1
LIMIT 200;

三、Aurora专属优化 #

• 启用缓存预热：利用Aurora只读缓存特性，将常用数据预加载到内存，减少冷查询的I/O。
• 升级实例规格：若当前实例资源不足，可升级到更高内存、CPU的实例类型（如从r5.large到r5.xlarge），提升资源储备。

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