如何消除查询执行中的Using temporary与Filesort并提升性能

首先，咱们拆解下你的问题核心：500万行的范围分区表，带GROUP BY的查询因为using temporary和filesort导致耗时43秒，而去掉GROUP BY仅需0.27秒，说明性能瓶颈完全集中在分组逻辑上。下面是针对性的优化建议：

1. 修正WHERE条件里的时间范围错误 #

你的查询中BETWEEN "1525113000000000" AND "1524680999999999"是反向的，前一个时间戳比后一个大，这会导致WHERE条件过滤不到任何数据（或者逻辑完全错误），这可能是测试时的笔误，但实际查询必须修正这个问题，否则所有优化都是无效的。正确写法应该是把小的时间戳放在前面：

subscribe_time BETWEEN "1524680999999999" AND "1525113000000000"

2. 预计算时间维度字段，避免函数运算 #

你当前GROUP BY的是EXTRACT(YEAR/MONTH/WEEK/DAY FROM FROM_UNIXTIME(subscribe_time * 0.000001))，这种对字段做函数运算的分组方式完全无法利用索引，MySQL只能全表扫描后再计算分组，必然会产生临时表和文件排序。

解决方法是在表中新增4个预计算字段：

ALTER TABLE tbl_subscription 
ADD COLUMN subscribe_year YEAR,
ADD COLUMN subscribe_month TINYINT,
ADD COLUMN subscribe_week TINYINT,
ADD COLUMN subscribe_day TINYINT;

然后批量更新这些字段的值（后续可以通过触发器或ETL流程自动维护）：

UPDATE tbl_subscription 
SET 
  subscribe_year = EXTRACT(YEAR FROM FROM_UNIXTIME(subscribe_time * 0.000001)),
  subscribe_month = EXTRACT(MONTH FROM FROM_UNIXTIME(subscribe_time * 0.000001)),
  subscribe_week = EXTRACT(WEEK FROM FROM_UNIXTIME(subscribe_time * 0.000001)),
  subscribe_day = EXTRACT(DAY FROM FROM_UNIXTIME(subscribe_time * 0.000001));

之后GROUP BY就可以直接用这些字段，不用每次重复计算：

GROUP BY subscribe_year, subscribe_month, subscribe_week, subscribe_day, sub_user, subscribe_ip, subscribe_zone, subscribe_approval

3. 建立针对性的联合索引 #

有了预计算的时间字段后，我们可以建立一个覆盖WHERE过滤和GROUP BY的联合索引，让MySQL直接通过索引完成分组，避免临时表和文件排序：

CREATE INDEX idx_subscribe_group ON tbl_subscription (
  subscribe_time, 
  subscribe_year, 
  subscribe_month, 
  subscribe_week, 
  subscribe_day, 
  sub_user, 
  subscribe_ip, 
  subscribe_zone, 
  subscribe_approval
);

这个索引的逻辑是：

• 先通过subscribe_time过滤出WHERE条件中的时间范围（利用范围分区的优势，只扫描符合条件的分区）
• 后续的分组字段都是索引的有序部分，MySQL可以直接按索引顺序分组，不需要额外排序或临时表

另外，如果你不需要同时按年、月、周、日分组（实际上同一天的记录，年、月、周必然相同），可以只保留subscribe_day（或直接用日期字段），进一步减少索引长度和分组复杂度，性能会更优。

4. 优化SELECT中的计算逻辑 #

你当前SELECT中的复杂CASE WHEN和除法运算可以适当简化，比如先计算两个聚合值再做除法，避免嵌套计算：

SELECT 
  subscribe_time * 0.000001 AS start_date, 
  subscribe_time * 0.000001 AS end_date,
  IFNULL(ROUND(
    SUM(CASE WHEN subscribe_duration > 20000 AND subscribe_status = '1' THEN 1 ELSE 0 END) / 
    NULLIF(SUM(CASE WHEN subscribe_status = '1' THEN 1 ELSE 0 END), 0) * 100, 
  2), 0) AS subscribe_avg

这里用NULLIF避免分母为0的情况，逻辑和原来一致，但可读性和执行效率会稍好。

5. 确保分区策略的有效性 #

你的表用了范围分区，要确认分区键是subscribe_time（或与subscribe_time强关联的字段），这样WHERE中的subscribe_time范围条件可以自动过滤掉不需要的分区，减少扫描的数据量。如果分区键不是subscribe_time，即使指定了时间范围，MySQL还是会扫描所有分区，这也是单分区查询性能没改善的可能原因之一。

6. 临时表内存化（治标不治本的应急方案） #

如果暂时无法修改表结构或索引，可以调整MySQL的临时表参数，让临时表尽量在内存中创建，避免写入磁盘：

tmp_table_size = 256M
max_heap_table_size = 256M

这个方法只能缓解using temporary的性能问题，无法从根本上解决分组的性能瓶颈，建议优先采用前面的表结构和索引优化方案。

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