SQL按id、date分组查询各channel对应历史最大日期的优化咨询

原查询耗时过长的核心原因是多次自连接产生大量无效的笛卡尔积计算，再加上分组聚合的额外开销，数据量越大性能衰减越明显，以下是可落地的优化方案：

方案1：窗口函数单次扫描表（性能提升最显著） #

使用条件聚合窗口函数只需扫描一次原表即可得到结果，完全避免了多表连接和分组操作，是优先级最高的优化方案，SQL如下：

SELECT 
    id,
    channel_id,
    `date`,
    MAX(IF(channel_id = 1, `date`, NULL)) OVER (
        PARTITION BY id 
        ORDER BY `date` 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_date_channel_id1,
    MAX(IF(channel_id = 2, `date`, NULL)) OVER (
        PARTITION BY id 
        ORDER BY `date` 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_date_channel_id2
FROM `table`
ORDER BY id, `date`;

逻辑说明：窗口函数按id分组、按date升序排序，仅统计当前行之前所有行中对应channel_id的最大日期，完全匹配需求，没有多余计算开销。

方案2：添加覆盖索引（适配所有查询写法） #

不管使用哪种查询逻辑，添加联合覆盖索引都可以大幅降低磁盘IO消耗，不需要回表查询数据：

-- 适配窗口函数写法的覆盖索引
CREATE INDEX idx_id_date_channel ON `table` (id, `date`, channel_id);

-- 如果暂时无法修改查询逻辑，仍使用原自连接写法，可额外添加对应索引
CREATE INDEX idx_id_channel_date ON `table` (id, channel_id, `date`);

方案3：兼容不支持窗口函数的低版本数据库 #

如果使用的是MySQL 8.0以下不支持窗口函数的版本，可以把自连接改为相关子查询，避免分组操作，性能也远优于原写法：

SELECT 
    a.id,
    a.channel_id,
    a.`date`,
    (SELECT MAX(`date`) FROM `table` WHERE id = a.id AND channel_id = 1 AND `date` < a.`date`) AS prev_date_channel_id1,
    (SELECT MAX(`date`) FROM `table` WHERE id = a.id AND channel_id = 2 AND `date` < a.`date`) AS prev_date_channel_id2
FROM `table` a
ORDER BY a.id, a.`date`;

内容的提问来源于stack exchange，提问作者Eren Melih Altun