静态表与InnoDB表查询执行时间差异大的原因探究

核心原因分析 #

• CTE临时表无索引支持：CTE生成的静态日历表属于临时表（内存或磁盘存储），默认没有任何索引。而InnoDB版本的calendar表可以针对s和e字段建立联合索引，MySQL优化器在关联huge_table时，能通过索引快速定位匹配的日期范围，大幅减少不必要的数据扫描开销。
• 执行计划效率差异：对于InnoDB的calendar表，优化器会优先将其作为驱动表，利用索引高效过滤huge_table的记录；但CTE临时表没有索引，优化器只能采用效率更低的连接策略（比如对huge_table做多次全表扫描，或低效的嵌套循环），直接导致IO和CPU开销翻倍。
• 临时表存储引擎的局限性：临时表默认使用MEMORY或MyISAM引擎，MEMORY引擎对范围查询的支持远不如InnoDB的B+树索引；如果CTE数据量较大，临时表会转为磁盘存储，性能会进一步下降。

验证与优化方案 #

1. 对比执行计划：用EXPLAIN分别执行两个查询，查看type（连接类型）、key（使用的索引）、rows（预估扫描行数）字段，就能直观看到CTE版本是否在做全表扫描，而InnoDB表是否用到了索引。
2. 给临时表手动加索引：将CTE改为带索引的临时表，性能会和InnoDB版本接近：

CREATE TEMPORARY TABLE calendar_temp (
  s DATETIME,
  e DATETIME,
  INDEX idx_s_e (s, e)
);
INSERT INTO calendar_temp VALUES
('2022-05-01 00:00:00', '2022-05-02 00:00:00'),
('2022-05-02 00:00:00', '2022-05-03 00:00:00'),
-- 补充其他日期行
('2022-05-04 00:00:00', '2022-05-05 00:00:00');

SELECT COUNT(*)
FROM calendar_temp
LEFT JOIN huge_table ON huge_table.end_date >= s AND huge_table.start_date < e
GROUP BY huge_table.group_id;

3. 优化huge_table的索引：给huge_table建立包含start_date、end_date、group_id的联合索引（如idx_start_end_group (start_date, end_date, group_id)），这样数据库可以直接通过索引完成过滤和分组操作，无需回表查询原数据，进一步提升关联效率。

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