MySQL 的 IN 查询碰上大数据量属于那种听着简单、一上线就头疼的经典问题。业务需求写WHERE id IN (几千、几万个ID)的时候DBA 和开发都会皱眉但很多场景下这写法又躲不掉——比如从接口里拿一批用户ID去批量查明细、基于某个外部系统的结果集做关联过滤或者报表模块里用户勾选了一堆筛选条件。这类需求不是你随手能重构表结构绕开的你只能在不改变业务逻辑的前提下把查询本身做到最优。这篇文章我就围绕“IN 查询数据量大且无法避免”这个场景把我实际排查和优化过程中沉淀下来的思路、命令、参数和坑完整梳理一遍。适合正在处理慢查询的研发、DBA也适合刚接触 SQL 优化、想搞清楚 IN 到底慢在哪的初级同学。1. 先搞明白大数据量的 IN 到底慢在哪1.1 MySQL 处理 IN 列表的两个核心路径很多人第一反应是“IN 列表太长SQL 语句太长导致网络包过大”这确实是问题之一但往往不是最要命的。MySQL 收到一条带大 IN 列表的语句后优化器要做两件事解析 SQL、生成执行计划。解析阶段需要把成千上万个常量逐个提取出来这本身就是 CPU 开销生成执行计划阶段优化器要决定用索引还是全表扫描以及怎么估算成本这个过程会触发一个关键机制——索引基数估算。MySQL 5.6 之前优化器对 IN 列表中的每个值都要做一次索引 dive也就是实际去索引里看一下每个值对应的行数然后汇总估算成本。IN 列表有 1 万个值它就可能做上万次索引 dive这个成本非常可观。5.6 之后引入了eq_range_index_dive_limit参数默认是 200意思是当 IN 列表里的值个数超过 200 时优化器不再逐个 dive而是改用索引统计信息来估算行数。这个机制本身是好的但也带来了一个问题当列表长度远超 200 时基于统计信息的估算可能严重失准优化器会低估或高估行数导致选错执行计划甚至放弃索引。1.2 索引失效并不是 IN 的专利但列表过长会加重它很多文章会告诉你“IN 会导致索引失效”严格来说不太准确。WHERE id IN (...)在列表值不多、且字段有索引时完全可以走索引。问题出在列表值多了以后优化器根据估算判断“回表次数太多不如全表扫描”然后真的给你走全表扫描。这种情况在数据量大的表上就是灾难。我看过一条慢 SQL业务从第三方接口拿了一万个员工工号去查一个两千万行的员工明细表SQL 写成SELECT ... FROM emp_detail WHERE emp_no IN (001,002,...,10000);执行计划显示typeALL扫描行数接近全表耗时二十多秒。单独拿几个工号查却是typerange毫秒级返回。这就是因为 IN 列表超过优化器阈值后它估算出的行数几乎覆盖全表认定走索引没意义。1.3 还有一个容易忽略的坑临时文件与排序IN 列表在语义上等价于“多个等值条件的 OR”如果字段上没有索引或者优化器选择全表扫描它需要对每一行判断“是否在列表中”。实现时 MySQL 可能会把 IN 列表构造成一个临时结构和表数据做比对这个过程会产生额外的内存或磁盘临时表消耗。如果 IN 列表来自子查询情况更复杂SELECT ... FROM a WHERE a.id IN (SELECT b.a_id FROM b WHERE b.type1);这条语句在很多版本里会被优化成semi-join但如果你关闭了optimizer_semijoin或者子查询本身返回数据量巨大就可能出现“逐行执行子查询”的糟糕计划也就是相关子查询外层每扫一行就执行一次子查询慢到怀疑人生。所以说IN 查询慢往往是多个环节叠加的结果SQL 解析成本高、优化器估算失准、全表扫描、临时表开销、子查询未扁平化。只有先定位到具体是哪个环节拖后腿优化才有方向。2. 优化方案的选型逻辑不是让你不用 IN而是让 IN 更可控2.1 业务无法避免 IN那优化的本质是什么我先说结论优化大数据量 IN 查询的核心不是消灭 IN而是降低优化器的决策成本并把“批量判断”转换成更高效的执行路径。你可以从四个方向入手控制单次 IN 列表的数据量让优化器回到逐个 dive 的可控区间比如单批低于 200 个值或者自定义合理阈值。把 IN 列表变成临时表改用 JOIN让优化器用统计信息和索引 join 算法来做而不是面对一堆常量。用 EXISTS 或窗口函数改写在特定场景下让 MySQL 选择更优的执行顺序。从应用层分批并发把一次大查询拆成多个小查询配合连接池和事务边界控制。2.2 为什么临时表 JOIN 是“无法避免”场景下最稳的解法前面说了IN 列表最大的问题是 MySQL 需要对每个常量做估算同时 SQL 文本巨大解析开销高。如果把列表先落到一张临时表内存表或普通表再把原来的 IN 改成 JOINSQL 就变成了SELECT a.* FROM temp_ids t JOIN emp_detail a ON a.emp_no t.emp_no;此时 MySQL 面对的只有一张“小表 大表”的常规 JOIN优化器会按小表驱动大表的原则用 emp_detail 的索引去逐行探测每行走一次索引查找。更重要的是临时表上的数据分布比如重复值、总行数可以直接通过统计信息感知执行计划稳定可控不会再出现“上万个常量导致估算失准”的情况。而且临时表方案还有一个额外好处可以给临时表加索引。如果业务允许你把列表插入临时表后直接建主键索引JOIN 时还能走unique_subquery或ref比普通range扫描更快。在大厂的实际案例里几万级别的 IN 列表通过这个方案耗时能从几十秒降到几百毫秒提升非常明显。2.3 分批查询适合哪些场景适合哪些不适合分批的核心思路是控制每次 IN 的数量比如每次只查 500 个 ID循环查完再合并结果。这个方案写起来简单但有一个前提业务对结果顺序和一致性要求不高或者你可以接受应用层做归并。适合的场景包括数据只需要最终展示不需要在事务里强一致每批查询耗时低总耗时可控数据库连接池资源充足能承受一定并发。不适合的场景是必须在一个事务里完成计数或状态更新分批会导致查询期间数据变化影响业务正确性你的 IN 列表本身就是从一个大结果集里来的分批之后总查询次数太多网络往返成为瓶颈。在实际业务里我一般会先看列表总量。如果列表小于 5000优先直接优化执行计划如果 5000 到 5 万强烈建议临时表 JOIN如果超过 5 万那大概率业务设计有问题得回头梳理是不是一定要一次传这么多 ID。3. 实操从定位慢 SQL 到临时表 JOIN 落地的完整步骤3.1 第一步用 EXPLAIN 看执行计划别盲目优化我接到慢 SQL 后第一件事就是在这条 SQL 前加EXPLAIN看执行计划。有次帮一个团队排查他们一口咬定是“IN 列表太长把索引搞失效了”结果 EXPLAIN 显示其实走了索引慢在排序和回表上方向完全错了。所以优化之前先确认几点type是ALL还是range/ref/eq_refkey是否用到了预期索引rows估算值是否接近全表Extra里有没有Using temporary、Using filesort用一个实际的例子说明EXPLAIN SELECT * FROM orders WHERE order_id IN (100001, 100002, ..., 5000个值);如果看到typerange、keyPRIMARY、rows5000那性能也许没那么差只是回表多。如果看到typeALL、rows20000000那才是需要下重手的类型。3.2 第二步确认 eq_range_index_dive_limit 的影响这个参数是很多性能问题的源头。默认 200意味着 IN 列表超过 200 个值后优化器不再做精确的 index dive而是用基数统计估算。要判断是不是它的问题最简单的办法是SET SESSION eq_range_index_dive_limit 10000;然后把慢 SQL 跑一遍看执行计划是否变成 range。如果变了说明确实索引估算失准如果没变则该参数不是主因。注意别盲目把全局参数调大因为 index dive 本身也有代价。在 IN 列表几千到一万这个区间可以适当调大但如果列表经常几万几十万index dive 本身就会成为新的瓶颈。我的经验值应用常见列表长度在 1 千以内时把参数调到 1000 或 2000 是安全的。3.3 第三步临时表 JOIN 的完整落地写法这里我给出一个实际可用的模板。假设业务场景是外部系统传来了一批用户 ID去重后约 8000 个要查用户表获取昵称、等级、最近登录时间。应用层操作步骤先在 MySQL 会话中创建临时表注意临时表是会话级的连接池使用时要小心CREATE TEMPORARY TABLE tmp_user_ids ( user_id INT PRIMARY KEY ) ENGINEInnoDB;批量插入 ID建议用多值插入INSERT INTO tmp_user_ids VALUES (101),(102),...,(8000个);对原表执行 JOINSELECT u.user_id, u.nickname, u.level, u.last_login FROM tmp_user_ids t JOIN users u ON u.user_id t.user_id;用完即删DROP TEMPORARY TABLE IF EXISTS tmp_user_ids;这套流程看起来简单但有几个细节要注意。事务与临时表的关系如果你在事务里使用临时表事务提交后临时表依然存在但连接归还连接池后临时表会被自动清理所以必须保证同一连接内完成建表、插入、查询、删表全部操作。很多连接池框架如 HikariCP默认不会在归还连接后清理临时表但 MySQL 在连接关闭时会自动 drop只是如果连接被复用之前残留的临时表会被新的建表语句覆盖或者报“table already exists”。稳妥做法是每次用完立即 DROP并在建表时用CREATE TEMPORARY TABLE IF NOT EXISTS兜底。临时表的引擎选择默认临时表是 MyISAM 或 MEMORY取决于配置。数据量几千到几万时MEMORY 表更快但要注意max_heap_table_size和tmp_table_size的限制超出后会转为磁盘临时表。我一般把 ID 列表控制在几万以内直接用默认临时表如果列表更大建议用 InnoDB 临时表避免内存溢出。给临时表加索引如果你的列表插入时有重复别急着加重。可以先插入再对临时表执行ALTER TABLE tmp_user_ids ADD PRIMARY KEY (user_id);去重后再 JOIN防止一对多膨胀。3.4 第四步分批查询的落地姿势如果因为某些原因不能用临时表比如数据库账号没有建临时表权限或者业务框架强制走 MyBatis 的foreach生成的 IN那只能在应用层分批。我对 MyBatis 批量处理有具体实践用一个Lists.partition把 ID 列表切成每份 500 个然后循环查询最后合并结果。ListListLong partitions Lists.partition(idList, 500); for (ListLong ids : partitions) { ListUser users userMapper.selectByIds(ids); result.addAll(users); }分批量不是越大越好也不是越小越好。我测试过一张两千万行的表主键查询每批 100、500、1000、5000 的耗时变化每批数量单批平均耗时总耗时1万ID说明10015ms约1.5s网络往返过多50035ms约700ms比较均衡100060ms约600ms耗时略降但 SQL 文本变大5000200ms约400ms接近优化器阈值需关注估算失准这个数据是单线程顺序执行的测试如果开并发每批 500 到 1000 配合 4~8 个线程整体还能再降。但并发会占用更多连接和事务资源要和 DBA 确认连接池上限避免把数据库打满。4. 常见问题与排查技巧实录4.1 优化后还是慢从哪里继续找原因我把实际踩过的坑按频率排个序你们可以对照排查第一临时表 JOIN 后驱动表选反了。虽然优化器一般会选择小表驱动大表但如果你 JOIN 时条件写反比如FROM users u JOIN tmp_user_ids t ON u.user_id t.user_idusers 是大表tmp_user_ids 是小表优化器依然会先扫描临时表再探测 users问题不大。但如果临时表数据量也不小几十万并且 users 表的索引基数统计不准确优化器可能选错驱动表。这时可以用STRAIGHT_JOIN强制SELECT u.* FROM tmp_user_ids t STRAIGHT_JOIN users u ON u.user_id t.user_id;第二回表太多导致慢。即使走了索引如果查询列很多InnoDB 需要拿着主键去聚簇索引回表每次回表都是一次随机 IO。这种情况下可以考虑覆盖索引把需要的列都加到索引里。比如常用查询是SELECT user_id, nickname, level可以在 users 表建一个(user_id, nickname, level)的联合索引但注意不要盲目加太多索引影响写入性能。第三临时表本身数据量过大导致 JOIN 退化。如果外部传来的 ID 列表有几百万那临时表 JOIN 也不再高效因为小表不再“小”。此时需要回到业务层面去思考有没有过滤维度比如加一个时间范围条件或者把大列表拆成多个子任务。4.2 索引明明有但执行计划显示 possible_keys 为空这个我在 MySQL 5.7 上遇到过好几次。原因是 IN 列表里的值类型和字段类型不一致导致隐式类型转换索引失效。举个例子user_id 是 BIGINT但 IN 列表里的值是字符串MySQL 会把字符串转成数字再比较虽然很多时候也能走索引但一旦字符串里有非数字字符就会全表扫描。排查技巧检查 SQL 里参数绑定的类型在应用层确保传入的 ID 列表都是 Long/Long 对应的字符串避免歧义。另外如果字段有字符集差异比如表字段是 utf8mb4连接字符集是 utf8也可能导致索引失效。这种情况在关联表时更容易出现可以通过CONVERT或统一连接参数解决但 IN 列表本身是常量一般不受影响。4.3 子查询 IN 优化后变成“相关子查询”怎么处理有些开发会写SELECT * FROM orders o WHERE o.customer_id IN ( SELECT c.customer_id FROM customers c WHERE c.level gold );如果 customers 表 level 字段没有索引或者数据量很大这个子查询结果集可能很大MySQL 会尝试做 semi-join但如果optimizer_switch里 semijoin 相关项被关闭就可能退化成相关子查询外层每行都执行一遍子查询性能极其糟糕。遇到这种情况优先改写为SELECT o.* FROM orders o JOIN customers c ON o.customer_id c.customer_id WHERE c.level gold;注意 JOIN 可能会因为一对多产生重复数据如果 customer_id 在 customers 表不唯一需要加去重或用 EXISTSSELECT o.* FROM orders o WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.customer_id o.customer_id AND c.level gold );EXISTS 在驱动表外层的行数较少时非常高效它关心的是“存在性”不需要且尽量避免返回多条。所以到底用 JOIN 还是 EXISTS要看两个维度外层表大小、内层表能否快速命中唯一索引。4.4 大 IN 列表带来的 SQL 文本长度问题MySQL 默认 max_allowed_packet 通常是 64MB但在某些云数据库上可能只有 16MB。一个 10 万 ID 的 IN 列表每个 ID 按 10 字节算大约 1MB加上前后 SQL 文本一般不会超限但如果每行还有别的大字段或者 MyBatis 拼接时带了大量多余空格也可能触发报错。常见报错是Got a packet bigger than max_allowed_packet bytes。这类问题用临时表方案能直接绕开因为 SQL 文本里不再携带大量常量。如果是分批方案记得控制每批列表长度避免触发包大小限制。我见过一个极端案例同事用 Python 脚本生成 100 万 ID 的 IN 查询直接把数据库连接搞断最后只能改文件导入方式解决。4.5 如果数据库是只读副本能不能继续用临时表这个问题比较现实。只读实例上无法创建临时表实际上 MySQL 的临时表在只读模式下也不允许但业务又无法避免 IN 列表。我的建议是这种情况下优先用分批查询因为不需要建表如果列表实在太大可以在应用层先把数据请求到一个临时存储或本地缓存再分批查询。本质上就是把“数据库临时表”的职责上移到应用层。还有一种偏门做法是使用VALUES表构造函数MySQL 8.0 支持SELECT t.user_id FROM (VALUES ROW(101), ROW(102), ...) AS t(user_id) JOIN users u ON u.user_id t.user_id;但它本质上还是把列表写进了 SQL只是换了个语法而且 8.0 之后某些版本对大数据量 VALUES 的性能并不见得更好所以我没有把它作为首选。5. 一些收敛经验与参数配置建议5.1 一套适合大多数场景的配置模板如果你的业务经常使用几百到几千长度的 IN 查询下面几个参数可以作为起点来微调参数建议值说明eq_range_index_dive_limit500~2000根据列表最大长度调整没必要设成几万max_allowed_packet64MB避免超大 SQL 文本报错tmp_table_size64MB临时表内存上限防止转磁盘max_heap_table_size64MBMEMORY 临时表上限optimizer_switch默认不要全盘关闭 semijoin除非你能明确掌控执行计划强调一下参数调整只是辅助真正的性能提升还是来自改 SQL 结构。我在 5.7 上做过一个压测一万个 ID 查两千万行表直接 IN 的 P99 在 8 秒左右改成临时表 JOIN 后 P99 在 200 毫秒以内差别是数量级的。参数调优只能把直接 IN 从 8 秒优化到 5 秒依然无法和临时表方案相比。5.2 别忘了从业务层面控制列表规模技术优化做到极致也是给业务兜底。我遇到过一个报表系统一次请求里带了全公司 3 万员工 ID还要求实时展示结果就是每周五下午数据库 CPU 被打满。后来产品和研发一起做了两件事一是把 ID 列表按部门分组默认只查当前部门二是对历史数据走汇总表而不是明细表。这两件事做完数据库压力直接降了 80%。所以当 IN 列表规模超过一定程度你应该反向思考能不能减少一次要查的数据量能不能用缓存能不能提前聚合数据量真的到了“百万级 ID 一次性查询”这个量级那任何 SQL 写法都很难救必须上搜索引擎或者列存数据库这类更适合大规模过滤分析的方案而不是让 MySQL 硬扛。MySQL 擅长的是事务和中小规模复杂查询拿着它当大数据过滤引擎从一开始就选错了工具。5.3 监控与告警建议优化完成后建议把原慢 SQL 的关键变体加入慢查询监控里。我习惯的做法是把临时表 JOIN 和分批查询分别做两个监控项看平均耗时和扫描行数。一旦某个版本执行计划从ref变成ALL要能及时收到告警。可以通过performance_schema.events_statements_summary_by_digest来跟踪同类 SQL 的累计耗时也可以用sys.statement_analysis视图快速定位 Top N 慢 SQL。另外要观察临时表的创建次数。EXPLAIN里的Using temporary如果过多说明 SQL 里隐含了排序或去重这时候要检查是不是 JOIN 让结果集变大了。我见过有人用临时表 JOIN 后忘了对user_id去重结果返回了 8 万行应用层再逐条去重耗时全耗在内存排序上了。正确的做法是在临时表插入时就INSERT IGNORE或者建主键去重从源头保证列表唯一。6. 一个完整的实战案例复盘最后分享一个最近处理的线上案例把这个流程串起来。业务背景是电商后台的“高级筛选”功能前端把符合条件的商品 ID 一次性传给后端最多 2 万个后端需要用这些 ID 去商品主表查询商品详情并分页展示。商品主表有约 1.2 亿行刚开始是 MyBatis 的 foreach 生成 2 万 IN执行计划直接全表扫描数据库 CPU 飙到 90%SQL 跑了 40 秒接口超时。我们第一步先用临时表方案应用层把 2 万个 ID 去重后写入临时表再 JOIN 商品表并让商品表走主键索引SQL 如下CREATE TEMPORARY TABLE tmp_spu_ids (spu_id BIGINT PRIMARY KEY) ENGINEInnoDB; -- 分批插入 INSERT INTO tmp_spu_ids VALUES (...); -- JOIN SELECT p.spu_id, p.spu_name, p.price, p.stock_status FROM tmp_spu_ids t JOIN product p ON p.id t.spu_id;第一次测试执行计划显示typeref耗时 0.9 秒。这个结果已经可以接受但分页需求要求返回总数我们还需要 COUNTSELECT COUNT(*) FROM tmp_spu_ids t JOIN product p ON p.id t.spu_id;由于全部走主键COUNT 也很快约 0.3 秒。整体接口从 40 秒降到 1.2 秒数据库 CPU 降到 10% 以下。但上线后遇到一个偶发问题连接池复用连接时第二次请求创建临时表报错Table tmp_spu_ids already exists。排查后发现代码里调用了DROP TABLE IF EXISTS tmp_spu_ids但这个 DROP 是显式执行的如果在异常分支没执行连接归还后临时表还在下一次请求又建同名表就冲突。后来我们把建表语句改成CREATE TEMPORARY TABLE IF NOT EXISTS tmp_spu_ids (...)并在 finally 块里强制 DROP问题解决。另外有次接口突然又慢了EXPLAIN 发现 JOIN 方向变了优化器用商品表驱动临时表。原因是商品表统计信息过期优化器认为商品表只有 1000 行实际 1.2 亿于是选择商品表作为驱动表执行计划变坏。我们用ANALYZE TABLE product刷新统计信息并加了一个STRAIGHT_JOIN强制让临时表驱动商品表后续执行计划稳定下来。这也是我前面强调eq_range_index_dive_limit和统计信息重要的原因——MySQL 优化器有时候不聪明你得帮它一把。这个案例说完你应该发现了IN 查询大数据量优化其实没有魔法核心还是理解优化器怎么工作然后顺着它的机制把 SQL 改到最优。我个人实际优化过的项目里临时表 JOIN 是适用面最广、效果最稳的方案同时它会带来临时表生命周期管理、事务边界、连接池等麻烦你需要在上线前把这些细节理清楚。如果你的业务里 IN 列表经常只有几十个完全不必折腾但如果到了几千上万别犹豫照着文里的流程试一遍你会回来感谢我的。