MySQL 大 IN 列表怎样理解索引估算成本

大 IN 列表真正需要关注的不是 SQL 字符串有多长，而是 MySQL 为范围访问估算候选条件时要保留多少组合。单个 IN 列表中的每个值都会按一个 OR 条件计入；如果两个列各有一个列表，候选组合还可能按两个列表长度的乘积增长。先看范围优化是否因为内存上限被放弃，再决定拆分查询、改用临时表 JOIN，或只在当前会话调整上限。

官方手册：https://dev.mysql.com/doc/refman/8.4/en/range-optimization.html

先判断：慢在列表长度，还是慢在范围候选膨胀

例如订单查询同时按客户和状态筛选：

-- 示例只用于观察计划，不代表真实运行结果
EXPLAIN FORMAT=JSON
SELECT id, total
FROM orders
WHERE customer_id IN (101, 102, 103, 104)
  AND status IN ('paid', 'shipped');

这里有两个候选集合。列表本身只有 4 个和 2 个值，但范围优化需要评估它们的组合关系。先记录 rows_examined、访问类型、使用的索引和成本估计，再与缩小列表后的计划比较。不要把“SQL 能执行”误认为“优化器一定走了索引”。

用 EXPLAIN、警告和 trace 找到估算退化点

MySQL 官方把 EXPLAIN 作为查看优化器计划的入口；如果范围优化的内存容量不足，可能出现 3170 警告，提示该语句没有完成范围优化。可以在同一会话先检查变量和警告：

-- 先确认当前会话的范围优化内存上限
SELECT @@session.range_optimizer_max_mem_size;

-- 运行目标查询后立即查看本会话警告
SHOW WARNINGS;

-- 只在诊断窗口临时提高上限，避免直接改变全局配置
SET SESSION range_optimizer_max_mem_size = 16777216;

若要解释“为什么选了这个访问路径”，可以开启当前会话的优化器跟踪，再查询 INFORMATION_SCHEMA.OPTIMIZER_TRACE。跟踪要在同一连接完成，结束后关闭；它适合定位范围候选、成本比较和被放弃的访问方式，不适合当作长期生产日志。

-- 只让当前会话记录优化过程，避免影响其他连接
SET SESSION optimizer_trace = 'enabled=ON';

-- 执行待诊断的 SELECT；正文中的集合按业务数据替换
SELECT id FROM orders WHERE customer_id IN (101,102,103,104);

-- 读取最近一次跟踪结果
SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

-- 诊断结束后关闭跟踪
SET SESSION optimizer_trace = 'enabled=OFF';

两组 IN 为什么要按乘积估算

官方文档给出的判断很实用：单个 IN 中的每个字面量相当于一个 OR 条件；当出现两个列表时，OR 条件数量按列表长度相乘。假设客户列表有 800 个值、地区列表有 20 个值，粗略组合规模就是 16000，而不是 820。多列索引、连接条件和其他 OR 约束还会继续增加实际成本，所以不能用一个固定阈值覆盖所有表。

拆分、临时表与调参：按生命周期选择方案

列表只偶尔超过上限时，可以按 200～500 个值分批查询，再在应用层合并并去重；批大小应由真实计划和延迟预算决定，不要把示例数字当成通用规则。列表来自用户上传或批处理任务、且需要与多列条件组合时，更适合写入临时表并为连接列建立索引，再用 JOIN 让优化器处理关系。

-- 把批量 ID 放入临时表，避免在主查询中展开超长 IN
CREATE TEMPORARY TABLE tmp_customer_ids (
  customer_id BIGINT PRIMARY KEY -- 主键帮助 JOIN 定位集合成员
);

-- 应用层批量写入待查询的客户 ID
INSERT INTO tmp_customer_ids (customer_id) VALUES (101), (102), (103);

-- 用关系连接替代大 IN，并保留主表上的业务过滤条件
SELECT o.id, o.total
FROM orders AS o
JOIN tmp_customer_ids AS ids ON ids.customer_id = o.customer_id
WHERE o.status = 'paid';

临时表不是免费的：要考虑写入成本、索引维护、事务生命周期和连接池复用。若只是为了验证“提高上限是否改变计划”，可以用会话级 SET 做对照；确认根因后再决定是否改配置。生产环境不建议为了绕过一次警告而无限调大内存，因为并发查询会共同放大资源压力。

常见问题

大 IN 列表一定比临时表 JOIN 慢吗？

不一定。集合很小且只执行一次时，IN 可能更简单；集合大、重复使用或与多列条件组合时，临时表 JOIN 往往更容易控制成本。

EXPLAIN ANALYZE 能直接证明范围优化内存超限吗？

不能。它用于比较实际执行与估算结果，超限线索仍应先看 Warning 3170，并结合变量和 optimizer trace 判断。

应该直接把 range_optimizer_max_mem_size 设为 0 吗？

不应直接这么做。0 表示不限制，适合受控诊断，不等于生产环境的稳定优化方案；先缩小集合或改变数据承载方式更安全。