AWS RDS MariaDB单值结果查询异常缓慢问题求助

数据表结构 #

create table account_transactions
(
    id                      int auto_increment   primary key,
    account_id              int                  not null,
    amount                  decimal(10, 2)       not null,
    type                    varchar(255)         not null,
    created_at              datetime             not null,
    updated_at              datetime             not null,
    constraint FK_does_not_matter
        foreign key (account_id) references accounts (id),
    index account_transactions_created_at (created_at),
    -- + some other irrelevant columns and indexes
)
collate = utf8mb4_unicode_ci;

create table accounts
(
    id                    int auto_increment          primary key,
    project_id            int                         not null,
    user_id               int                         not null,
    constraint FK_does_not_matter
        foreign key (user_id) references users (id),
    constraint FK_does_not_matter
        foreign key (project_id) references projects (id),
    -- + some other irrelevant columns and indexes
)
    collate = utf8mb4_unicode_ci;

问题背景 #

• 数据规模：account_transactions约1100万条，accounts约2.2万条，projects不足100条
• 目标查询（Doctrine生成）：

SELECT SUM(c0_.amount) AS sclr_0
FROM account_transactions c0_
LEFT JOIN accounts c1_ ON c0_.account_id = c1_.id
WHERE c0_.created_at >= ? -- 当前值为'2025-12-04 00:00:00'
  AND c1_.project_id = ?;

• 性能问题：平均耗时260ms，峰值400-500ms，实际仅需聚合859行数据
• 反常现象：
  • 查询未使用created_at索引，执行计划预估行数与实际偏差大
  • 改为查询c0_.*耗时降至86ms，改为仅查c0_.amount耗时波动120-300ms
• 环境：AWS RDS db.t3.medium（MariaDB 10.11.13），EC2同区域连接
• 尝试操作：
  • 更新1：添加account_transactions (account_id, created_at, amount)复合索引后，耗时降至30-108ms，平均50-60ms，但存在波动
  • 更新2：使用STRAIGHT_JOIN强制关联顺序，耗时超300ms，执行计划先扫描28万+条交易记录

根源分析 #

1. 执行计划选择偏差：
   初始查询优化器选择先通过accounts的project_id索引获取270个账户，再关联account_transactions的account_id索引，但因无覆盖索引，需回表读取amount和created_at字段，加上统计信息过时（预估行数270*257 vs实际859），导致IO开销超出预期。
2. 聚合操作的额外开销：
   SUM(amount)需要遍历匹配的行并计算聚合值，相比直接返回行数据，CPU和内存开销更高；而查询c0_.*时，优化器可能触发了更高效的批量读取逻辑，抵消了回表的部分开销。
3. STRAIGHT_JOIN的错误路径：
   强制先扫描account_transactions的created_at索引，返回28万+条记录后再关联accounts过滤项目ID，IO量远大于先过滤账户再查交易的路径，导致性能恶化。
4. 实例资源限制：
   db.t3.medium是突发性能实例，CPU credits耗尽会导致性能波动；同时4GB内存无法完全缓存1100万条交易数据，频繁磁盘IO也是耗时波动的原因之一。

优化方案 #

1. 保留并依赖复合覆盖索引：
   account_transactions (account_id, created_at, amount)是最优索引，包含关联、过滤、聚合所需的所有字段，无需回表，能大幅降低IO开销。
2. 刷新统计信息：
   执行ANALYZE TABLE account_transactions, accounts;让优化器获取准确的行数分布数据，避免因统计过时选错执行路径。
3. 修正关联类型：
   原查询中WHERE c1_.project_id = ?会将LEFT JOIN自动转为INNER JOIN，显式改为INNER JOIN可让优化器更精准判断关联逻辑，减少不必要的计算。
4. 调整实例配置：
   • 监控CPU credits使用情况，若频繁耗尽，切换至db.m5.large等通用型实例
   • 调大innodb_buffer_pool_size（建议设置为内存的70%-80%），提升数据缓存率，减少磁盘IO
5. 避免强制关联顺序：
   除非明确知道最优路径，否则不要使用STRAIGHT_JOIN，让优化器根据统计信息自动选择关联顺序。

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