MySQL Aurora中COUNT查询性能异常缓慢的排查求助

根据你提供的查询语句、EXPLAIN和ANALYZE结果，核心耗时点集中在project_reports表的索引查找步骤（实际耗时1.47~24709ms），结合Aurora的分布式存储特性，给出以下排查方向和优化方案：

1. 反转关联表查询优先级，优化执行计划 #

当前执行计划先扫描project_reports中deleted_at IS NULL的66265行数据，再通过嵌套循环关联project_user_cache。如果project_user_cache中user_id=5对应的project_id数量远小于该值，建议强制优化器优先查询project_user_cache：

SELECT count(pr.id) as aggregate
FROM project_user_cache puc
STRAIGHT_JOIN project_reports pr 
  ON pr.project_id = puc.project_id
WHERE puc.user_id = 5 
  AND pr.deleted_at IS NULL;

依托project_user_cache已有的idx_user_project_unique索引（包含user_id和project_id），快速定位该用户关联的所有项目ID，再关联project_reports时仅需匹配这些项目ID，大幅减少扫描行数。

2. 创建覆盖索引，避免回表操作 #

当前project_reports使用的idx_deleted_date仅包含deleted_at字段，查询时需要回表获取project_id才能完成关联。Aurora分布式存储的回表IO成本远高于本地MySQL，建议创建联合覆盖索引：

CREATE INDEX idx_deleted_project ON project_reports (deleted_at, project_id);

该索引直接包含查询所需的deleted_at和project_id字段，无需回表即可完成关联逻辑，显著降低IO耗时。

3. 更新表统计信息，确保优化器决策准确 #

Aurora的查询优化器依赖准确的表统计信息选择执行计划，若统计信息过时，可能导致低效的执行路径。执行以下命令更新统计信息：

ANALYZE TABLE project_reports, project_user_cache;

更新后重新执行EXPLAIN ANALYZE，确认执行计划是否得到优化。

4. 验证缓冲池配置与数据预热 #

测试环境中若数据未被预热到缓冲池，Aurora需要从分布式存储层读取冷数据，导致延迟升高。可先执行以下语句预热project_reports的索引数据：

SELECT project_id FROM project_reports WHERE deleted_at IS NULL;

预热后重新执行目标COUNT查询，观察耗时变化。同时检查缓冲池配置是否合理：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

建议将缓冲池大小设置为实例内存的70%~80%，避免因缓存不足导致频繁的存储层IO。

5. 排查Aurora存储层IO瓶颈 #

若上述优化无效，可通过AWS控制台查看Aurora实例的CloudWatch指标（如IOPS、读写延迟），确认是否存在存储层IO瓶颈。测试环境的冷数据读取可能会导致临时的高延迟，可考虑将数据加载到缓存层或调整实例存储类型。

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