MariaDB InnoDB大表日期聚合慢查询优化与选型咨询

一、现有MariaDB架构下的零/低空间占用优化方案 #

1. 索引优化（几乎不增加额外存储，甚至可省空间） #

当前customer_daily_stats表的customer_daily_stats_customer_id_date_index索引可以替换为覆盖索引：

-- 先删除旧索引
DROP INDEX customer_daily_stats_customer_id_date_index ON customer_daily_stats;
-- 新建覆盖索引，包含查询需要的所有聚合字段
CREATE INDEX customer_daily_stats_customer_id_date_cover_idx ON customer_daily_stats (customer_id, date, event_1, event_2, event_3, event_4);

新索引以(customer_id, date)开头，完全可以替代旧索引的查询能力，不会影响原有查询逻辑；因为包含了聚合需要的所有event字段，关联查询时可以直接从索引取数，无需回表访问主键数据，能减少70%以上的随机IO。替换旧索引后总索引体积不会明显增长，长期来看因为减少回表带来的页分裂，还能节省部分存储空间。

2. 查询逻辑改写（零成本，性能提升最明显） #

当前写法是先全量关联两表再做聚合，会产生大量中间结果——单个用户对应时间段内有多条campaign记录时，中间行数会数倍于原表，改成先聚合统计结果，再关联用户表的写法，能大幅降低中间数据量：

SELECT 
    c.*,
    IFNULL(agg.event_1, 0) AS event_1,
    IFNULL(agg.event_2, 0) AS event_2,
    IFNULL(agg.event_3, 0) AS event_3,
    IFNULL(agg.event_4, 0) AS event_4
FROM customers c
LEFT JOIN (
    SELECT 
        customer_id,
        SUM(event_1) AS event_1,
        SUM(event_2) AS event_2,
        SUM(event_3) AS event_3,
        SUM(event_4) AS event_4
    FROM customer_daily_stats
    WHERE `date` BETWEEN '2021-09-06' AND '2022-07-06'
    GROUP BY customer_id
) agg ON c.id = agg.customer_id;

改写后子查询会先过滤指定时间范围的数据，按customer_id聚合后再和用户表关联，中间结果最多和customers表行数持平，配合上述覆盖索引，子查询阶段可以直接走索引完成计算，性能会有数量级提升。
另外注意：如果业务不需要统计已删除用户，记得加上WHERE c.deleted_at IS NULL条件，避免扫描无效数据。

3. 按时间分区（低空间成本，适配未来亿级数据量） #

customer_daily_stats表完全符合按日期做Range分区的条件：现有唯一键customer_daily_stats_date_customer_id_campaign_id_unique已经包含分区键date，不需要调整唯一约束，分区本身几乎不产生额外存储开销。
可以按月份对表做分区，查询指定日期范围时只会扫描命中的分区，不会全表扫描，未来数据量涨到1亿行时性能也不会明显衰减。

4. 优化器校正（零成本） #

执行查询前先跑EXPLAIN看执行计划，如果优化器选择先扫描customer_daily_stats全表再关联customers，可以在SELECT后加STRAIGHT_JOIN强制优化器先读customers表，再用主键关联stats表，避免优化器选错执行计划。

注意：不推荐用物化视图做优化，物化视图会存储多份全量数据，占用至少2-3倍存储空间，不符合当前磁盘紧张的约束。

二、适配场景的高性价比数据库选型 #

如果后续数据量涨到亿级以上，现有架构调优后仍不能满足性能要求，可以优先选以下高压缩比、低存储占用的OLAP数据库，不需要迁移全量业务数据，只需要同步customer_daily_stats表即可：

• ClickHouse：列存存储，默认LZ4压缩，1亿行统计类数据压缩后占用空间仅为InnoDB的1/5~1/10，单表亿级数据下做日期范围聚合查询耗时可以降到毫秒到百毫秒级，是当前场景的最优选择。部署只需要同步统计流水表，150万行的customers作为维度表同步过去仅占用几MB空间，整体存储增量完全在剩余磁盘容量范围内。
• DuckDB：如果该类统计查询是低并发的后台运营查询，不需要高并发支持，可以选嵌入式的DuckDB，不需要单独部署服务进程，直接将统计数据导出为Parquet列存文件存储，压缩比和ClickHouse相当，查询性能足够应对运营类统计需求，维护成本几乎为0。

不推荐选重型数仓或ES类产品，这类产品要么存储膨胀比高，要么内存占用大，不适配当前磁盘紧张的资源现状。

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