MySQL如何优化含MIN、MAX、SUM的查询以降低全列索引开销

优化方案如下 #

方案1：联合覆盖索引+OR条件拆分（优先推荐）

你当前的索引开销过高是因为创建了大量冗余单列索引，只需替换为2个针对性联合索引即可同时满足性能和存储要求：

• 先将原查询的OR条件拆分为两个独立查询用UNION ALL合并，避免OR导致索引失效，同时排除重复统计的数据：

SELECT * FROM (
    SELECT
        MIN(id) AS first_id,
        MAX(id) AS last_id,
        column_1,
        column_2,
        column_3,
        SUM(column_4 + column_5) AS total_4_5,
        SUM(column_6 + column_7) AS total_6_7,
        SUM(column_8 + column_9) AS total_8_9,
        SUM(column_10 + column_11) AS total_10_11
    FROM `table` 
    WHERE created_at BETWEEN '2021-10-01 07:45:00' and '2021-11-01 07:44:59'
    GROUP BY column_1, column_2
    
    UNION ALL
    
    SELECT
        MIN(id) AS first_id,
        MAX(id) AS last_id,
        column_1,
        column_2,
        column_3,
        SUM(column_4 + column_5) AS total_4_5,
        SUM(column_6 + column_7) AS total_6_7,
        SUM(column_8 + column_9) AS total_8_9,
        SUM(column_10 + column_11) AS total_10_11
    FROM `table` 
    WHERE 
        `id` IN (1,2,3,4,5,6,7...)
        AND created_at NOT BETWEEN '2021-10-01 07:45:00' and '2021-11-01 07:44:59'
    GROUP BY column_1, column_2
) AS union_res
GROUP BY column_1, column_2;

• 仅需创建2个联合覆盖索引，不需要其他任何单列索引：
  • 适配时间范围查询的索引：idx_crt_grp(created_at, column_1, column_2, column_3, id, column_4, column_5, column_6, column_7, column_8, column_9, column_10, column_11)，符合最左前缀匹配规则，所有查询字段都在索引中，无需回表
  • 适配ID查询的索引：idx_id_grp(id, column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10, column_11)，精准匹配ID后直接取索引中的所有字段
• 收益：索引总占用可从6.3GiB压缩至3~4GiB，查询性能和原有方案持平甚至更高。

方案2：预计算汇总表（适合高频查询场景）

如果该查询为固定统计逻辑的高频查询，可通过预聚合彻底降低存储和性能开销：

• 新增月度汇总表，结构和查询输出字段完全一致，包含统计月份、column_1、column_2、first_id、last_id、column_3、total_4_5、total_6_7、total_8_9、total_10_11字段
• 原始表有数据写入/更新时，异步更新对应月份的汇总数据，查询时直接访问汇总表即可
• 收益：汇总表仅需创建column_1、column_2联合索引，总索引占用不足100MiB，查询性能可提升10倍以上。

方案3：精简冗余索引（适合可接受轻微性能下降的场景）

如果不想修改现有查询逻辑，直接删除所有非必要的单列索引，仅保留created_at和id两个单列索引即可。

• 虽然查询会触发回表取字段，但200多万行数据的回表开销在绝大多数场景下均可接受，不会出现明显性能下降
• 收益：索引总占用可直接降至1GiB以内。

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