MySQL子查询导致索引失效查询慢的优化方案咨询

你遇到的超长耗时本质是MySQL优化器对WHERE条件里标量子查询的优化缺陷：优化器没有将这个仅返回单个固定值的子查询提前计算为常量，而是选择了嵌套循环的执行逻辑——即对主表扫描到的每一条满足bat=3的记录，都重复执行一次子查询做判断，最终执行次数和主表符合条件的行数正相关，才会出现12秒级的耗时。
你单独执行子查询、硬编码子查询结果速度极快，刚好验证了子查询本身执行效率很高，问题完全出在执行计划的选择上。

以下几种改写方式都能强制优化器先执行子查询拿到固定id值，再执行主查询，执行效率和你手动硬编码值的表现基本一致：

• 跨版本通用JOIN改写（所有MySQL版本均支持，无需额外配置）
  将子查询改写为仅返回一行结果的派生表，通过JOIN关联主查询，优化器会优先物化派生表拿到固定id值，不会产生重复执行子查询的问题：

SELECT t.* 
FROM ltowert t
INNER JOIN (
    SELECT id AS target_id
    FROM ltowert 
    WHERE bat = 3 AND ident = 'v0' 
    ORDER BY id DESC
    LIMIT 1
) tmp
WHERE t.bat = 3 
  AND t.id >= tmp.target_id
ORDER BY t.ident;

• MySQL 8.0+ 可用CTE改写
  CTE会默认先执行物化结果，执行逻辑和硬编码值完全一致，可读性更好：

WITH target AS (
    SELECT id
    FROM ltowert 
    WHERE bat = 3 AND ident = 'v0' 
    ORDER BY id DESC
    LIMIT 1
)
SELECT t.*
FROM ltowert t, target
WHERE t.bat = 3
  AND t.id >= target.id
ORDER BY t.ident;

• 低版本MySQL也可使用会话变量拆分
  如果是5.1及更早版本的旧MySQL，可通过会话变量提前存储子查询结果，单会话内一次执行完成，不需要应用层拆分两次请求：

SELECT @target_id := id
FROM ltowert
WHERE bat = 3 AND ident = 'v0'
ORDER BY id DESC
LIMIT 1;

SELECT *
FROM ltowert
WHERE bat = 3
  AND id >= @target_id
ORDER BY ident;

如果要进一步压缩耗时，可以给表建两个联合索引匹配查询逻辑，避免回表：

• 索引idx_bat_ident_id(bat, ident, id)：可以让子查询直接通过索引拿到目标id，不需要回表扫表
• 索引idx_bat_id_ident(bat, id, ident)：可以让主查询直接通过索引完成过滤和排序，不需要额外做filesort

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