AWS Aurora MySQL Serverless长耗时查询性能优化求助

1. 修正生成列+覆盖索引配置 #

你之前添加生成列未生效大概率是没有为生成列创建对应覆盖索引，操作步骤如下：

• 先添加持久化的小时生成列：ALTER TABLE queue_length ADD COLUMN location_hour TINYINT GENERATED ALWAYS AS (HOUR(location_datetime_start)) STORED;
• 再创建前缀匹配查询过滤条件、覆盖分组和查询字段的联合索引，顺序要匹配查询的过滤、分组逻辑：

-- 支持INCLUDE语法的高版本MySQL可用该写法
CREATE INDEX idx_queue_length_query_cover ON queue_length (location_id, location_date_key, location_hour) INCLUDE (value);
-- 不支持INCLUDE语法的版本直接把value加到索引末尾即可
CREATE INDEX idx_queue_length_query_cover ON queue_length (location_id, location_date_key, location_hour, value);

• 修改查询语句中的HOUR(location_datetime_start)直接使用location_hour字段，查询优化器才能直接命中索引，不需要实时计算小时值，分组也可以直接利用索引有序性避免临时表和排序开销：

SELECT 
  location_id AS LocationID,
  location_date_key AS BusinessDayKey,
  location_hour AS Hour,
  CEILING(AVG(VALUE)) AS Value
FROM queue_length
WHERE location_id IN @locationIDs
  AND location_date_key BETWEEN 20210914 AND 20210921
GROUP BY location_id, location_date_key, location_hour;

2. 开启Aurora MySQL特定优化参数 #

Aurora Serverless针对聚合查询有专属优化开关，可调整以下参数：

• 开启optimizer_switch='derived_merge=on'，避免不必要的临时表物化
• 适当调大group_concat_max_len和sort_buffer_size，避免分组排序时数据刷入磁盘
• 如果是Aurora MySQL 3.0+版本，开启parallel_execution_enabled参数，允许并行执行分组聚合操作，千万级大表的聚合效率可提升数倍

3. 预聚合物化表方案（适合高频查询场景） #

如果该查询是业务高频调用的报表类查询，建议建设按小时预聚合的物化表：

• 新建预聚合表queue_length_hour_agg，字段包含location_id、location_date_key、location_hour、sum_value、cnt
• 用定时任务、触发器或者CDC同步工具，每小时增量写入前一小时的sum(value)和count(value)
• 查询时直接从预聚合表计算结果：CEILING(SUM(sum_value)/SUM(cnt)) AS Value，7000万行的原始表预聚合后单点位单小时仅对应1条记录，查询耗时可降到毫秒级

4. 临时应急优化方案 #

如果暂时不想修改表结构，也可以将查询拆分为先过滤再分组的分段逻辑：

SELECT 
  location_id AS LocationID,
  location_date_key AS BusinessDayKey,
  HOUR(location_datetime_start) AS Hour,
  CEILING(AVG(VALUE)) AS Value
FROM (
  SELECT location_id, location_date_key, location_datetime_start, value
  FROM queue_length
  WHERE location_id IN @locationIDs
    AND location_date_key BETWEEN 20210914 AND 20210921
) t
GROUP BY location_id, location_date_key, HOUR(location_datetime_start);

该写法会先过滤出符合条件的小数据集再做小时计算和分组，避免全表扫描时逐行计算小时值的额外开销。

内容的提问来源于stack exchange，提问作者Piotrek Poliński