MySQL关联查询未使用主键索引，如何实现索引优化？

TL;DR：基于CTE生成的ID列表关联主键表时，MySQL选择全表扫描而非主键索引，尝试FORCE INDEX和调整max_seeks_for_key均无效，使用MySQL 8.0.35，需解决索引失效问题。

问题背景 #

需求为获取2022年1月1日至2022年12月31日期间，每天最接近12:00的天气数据，实现步骤如下：

1. 递归CTE生成日期范围：

with recursive
    dates (date) as
        (select cast('2022-01-01' as date)
         union all
         select date + interval 1 day
         from dates
         where date < '2023-01-01')

2. 调用自定义函数closest_to_noon获取每日目标数据ID：

ids as (select closest_to_noon(date(date), 2) as id
           from dates)

单独查询ids耗时约170ms，执行计划正常；但关联weather_condition表时：

# 省略with部分
select *
from ids
         join weather_condition w on w.id = ids.id;

总耗时增至1.77s，EXPLAIN显示weather_condition执行全表扫描，而该表id为bigint类型主键。

可能原因 #

• MySQL优化器无法准确评估CTE结果集的基数，误判全表扫描成本更低；
• 自定义函数closest_to_noon返回的ID类型与weather_condition.id存在隐式类型转换，导致索引无法匹配；
• MySQL对CTE的优化支持有限，默认将CTE视为“不可优化”的子查询，优先选择全表扫描。

解决方案 #

方案1：物化CTE结果到临时表 #

将CTE生成的ID存入带主键的临时表，让优化器明确识别索引关联：

-- 创建临时表，匹配主键类型
create temporary table temp_ids (id bigint primary key) engine=memory;

-- 插入CTE生成的ID
with recursive
    dates (date) as
        (select cast('2022-01-01' as date)
         union all
         select date + interval 1 day
         from dates
         where date < '2023-01-01'),
    ids as (select closest_to_noon(date(date), 2) as id from dates)
insert into temp_ids select id from ids;

-- 关联查询
select * from temp_ids join weather_condition w on w.id = temp_ids.id;

方案2：显式类型转换+强制索引 #

消除隐式类型转换，同时强制使用主键索引：

with recursive
    dates (date) as
        (select cast('2022-01-01' as date)
         union all
         select date + interval 1 day
         from dates
         where date < '2023-01-01'),
    ids as (select cast(closest_to_noon(date(date), 2) as bigint) as id from dates)
select *
from ids
join weather_condition w force index (primary) on w.id = ids.id;

方案3：强制物化CTE（MySQL 8.0.19+） #

使用materialized关键字强制MySQL物化CTE结果，帮助优化器准确评估执行计划：

with recursive
    dates (date) as materialized
        (select cast('2022-01-01' as date)
         union all
         select date + interval 1 day
         from dates
         where date < '2023-01-01'),
    ids as (select closest_to_noon(date(date), 2) as id from dates)
select * from ids join weather_condition w on w.id = ids.id;

方案4：改写CTE为派生表 #

将CTE结构改写为嵌套派生表，让优化器重新评估执行路径：

select *
from (
    select closest_to_noon(date(d.date), 2) as id
    from (
        select cast('2022-01-01' as date) as date
        union all
        select date + interval 1 day
        from dates
        where date < '2023-01-01'
    ) d
) ids
join weather_condition w on w.id = ids.id;

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