如何让GROUP BY强制使用索引？优化钱包交易聚合查询性能

当前查询触发全表扫描的核心原因是：GROUP BY子句使用了CAST(created_at AS DATE)表达式，而现有索引wallets_transactions(user_id, type, created_at)是基于原始datetime类型的created_at字段，数据库无法直接利用该索引完成分组聚合操作。

以下是针对性的优化方案：

方案1：创建匹配分组逻辑的函数索引 #

直接针对查询中用到的日期转换表达式创建复合索引，让数据库可以通过索引快速完成分组和聚合：

CREATE INDEX idx_wallets_transactions_user_type_created_date
ON wallets_transactions(user_id, type, CAST(created_at AS DATE));

该索引的字段顺序与GROUP BY的顺序完全一致，能够精准匹配查询逻辑，彻底避免全表扫描。

方案2：预计算并持久化日期字段（性能最优） #

如果业务场景允许，通过新增字段存储转换后的日期值，从根源上消除运行时的类型转换开销：

1. 新增日期字段并初始化数据：

   ALTER TABLE wallets_transactions ADD COLUMN created_date DATE;
   UPDATE wallets_transactions SET created_date = CAST(created_at AS DATE);
   

2. 创建触发器自动维护字段同步：

   CREATE TRIGGER trg_wallets_transactions_created_date
   BEFORE INSERT OR UPDATE ON wallets_transactions
   FOR EACH ROW SET NEW.created_date = CAST(NEW.created_at AS DATE);
   

3. 创建对应复合索引：

   CREATE INDEX idx_wallets_transactions_user_type_created_date
   ON wallets_transactions(user_id, type, created_date);
   

4. 修改查询语句使用预计算字段：

   SELECT user_id,
          type,
          created_date AS created_at,
          SUM(credit) AS credit
   FROM wallets_transactions
   GROUP BY user_id, type, created_date;
   

这种方式将转换开销转移到数据写入阶段，查询时无需额外计算，性能表现最佳，适合频繁执行此类统计查询的场景。

方案3：调整查询表达式适配现有索引（仅部分数据库适用） #

部分数据库（如PostgreSQL）支持通过DATE_TRUNC('day', created_at)替代CAST(created_at AS DATE)，如果现有索引的created_at字段是datetime类型，可能可以利用索引的前缀扫描特性。但该方案的兼容性和稳定性不如前两种，仅作为临时替代方案。

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