做后端这几年被IN查询“背刺”的次数可不算少。尤其是权限过滤、批量 ID 查询、批量导出这类业务产品一句话“把选中的 ID 都查出来”SQL 里就甩进来几百甚至上千个 ID。数据量小的时候IN用着是真爽可一旦目标表到了千万级IN列表超过几百查询时长直接从毫秒级变成秒级严重的时候能把从库拖出明显延迟。MySQLIN查询在大数据量业务无法避免的情境下优化从来不是一句“改成JOIN就行”能糊弄过去的。这篇文章聊的就是在“必须用IN、业务不能改、数据量又很大”这种夹缝里还能做哪些有实际效果的优化。我会先讲清楚IN到底慢在哪里再给出一套从分片、索引、覆盖索引到临时表JOIN的落地方案最后用一个批量导出的真实案例串起来。适合正在排查慢 SQL、被 DBA 追问、或者在设计查询方案时想少走弯路的后端同学。1. 先搞清楚IN 查询到底慢在哪一步1.1 慢 SQL 日志里的假象不要只看扫描行数很多同学拿到慢 SQL第一件事是EXPLAIN看到typerange、rows几万就以为问题不大。实际上IN查询最容易骗人的就是这一行rows。举一个我踩过的例子。业务表orders有 3000 万行SQL 长这样SELECT * FROM orders WHERE user_id IN (1001, 1002, 1003, ... ) -- 几百个 ORDER BY create_time DESC LIMIT 20;EXPLAIN显示typerangerows8000左右。当时我也差点被糊弄过去结果这个查询在从库上跑了 2 秒多。问题出在哪rows只是优化器估算的“索引扫描范围”而真正要命的是ORDER BY create_time和LIMIT组合。MySQL 需要把所有命中的行先找出来再按create_time排序最后才取 20 条。这个排序过程会用到内存临时表数据量大一点就会落到磁盘临时表性能瞬间崩掉。所以解读IN查询的慢不要只盯着rows。要看全链路索引定位、回表次数、排序、临时表、行数放大这些环节里任何一个都可能成为瓶颈。1.2 三个容易被忽略的隐形瓶颈除了排序和临时表还有三个隐形瓶颈平时看EXPLAIN看不出来但实际影响非常大。第一是回表放大效应。假如IN列表里有 500 个值每个值都能命中索引那么就要进二级索引找 500 次位置再回聚簇索引取 500 次完整行。如果每行数据很宽或者二级索引区分度不高回表成本会被成倍放大。这个放大效应呈线性列表越长越明显。第二是 binlog 和主从复制放大。在基于行row格式的 binlog 下一条UPDATE ... WHERE id IN (...)或者DELETE ... WHERE id IN (...)主库执行时会逐行产生 binlog 事件。主库可能 1 秒执行完从库要回放几万条事件延迟就出来了。很多同学只测主库耗时忽略了从库延迟上线之后才被报警打脸。第三是优化器对超长IN列表的处理。MySQL 6.0 之前的优化器比较“死板”列表太长时它会把这个范围条件展开成一个巨大的range扫描或者因为代价估算过高而放弃最优索引。虽然 8.0 之后的优化器改进了不少但列表超过一定长度执行计划依然可能“抽风”。2. 先别急着改 SQL你的业务到底属于哪一种“无法避免”2.1 多大才算“大数据量 IN”我自己的经验阈值是这样的目标表行数过百万IN列表超过 500或者EXPLAIN里的估算行数超过全表的 5%就要高度警惕。500 这个数字不是拍脑袋拍出来的。它和优化器的一个行为有关IN列表会被转换成一堆OR条件的范围扫描列表越长优化器在评估执行计划时的代价估算越高越可能出现执行计划抖动。另外IN列表过长时整个 SQL 文本会变得很大极端情况下还会碰到max_allowed_packet的限制。比列表长度更关键的是筛选项的区分度。IN用的是主键、唯一索引、普通二级索引还是完全没有索引的普通列如果是普通列且没有索引那不管IN写得多短都白搭。如果字段区分度很差比如状态字段只有两三个值即使命中行数很少回表成本和排序成本也一点都不会少。所以在优化之前先确认索引和区分度否则后面的方案都建立在沙滩上。2.2 表面上“无法避免”其实可以绕开的场景我见过很多项目嘴上说着“必须用IN改不了”分析一圈之后发现其实是能绕开的。第一种是多租户权限过滤。这种场景通常可以先按租户和角色把数据范围“圈定”成一个更小的集合再通过JOIN去关联过滤而不是一上来就用一个大IN列表去WHERE里硬筛。第二种是大量 ID 来自外部接口。比如上游系统给你返回了 5000 个 ID业务上必须用这 5000 个 ID 去本地库查明细。这种完全可以先把 ID 落地成本地临时表再JOIN而不必在 SQL 里塞一个巨型IN列表。第三种是分页查询的 ID 集合。很多时候业务先查出符合条件的 ID 分页然后拿着当前页的 ID 去查完整数据。这种场景适合用延迟关联先只查主键 ID 分页再回表组装数据。IN本身不是原罪损耗出在“使用方式”上。上面这三种场景优化思路其实是“改变数据流向”而不是硬扛IN。2.3 真正躲不掉 IN 的典型业务长什么样真正无法避免的IN通常同时满足这么几个条件ID 列表来自用户选择或上游系统业务上必须按这个集合精确过滤集合本身很大比如批量审核、批量导出、标签人群圈选而且无法用JOIN或临时表替换比如上游数据不在本库、格式频繁变化、不想引入额外表结构。这种时候优化方向就要变一变不是“不用IN”而是“让IN查询尽量少吃资源、少放大回表、少制造临时表”。下面的优化手段就是围绕这个目标展开的。3. 直接能落地的优化手段分片、索引、临时表 JOIN3.1 分片 IN把大 IN 拆成小批最朴素也最有效分片IN是我在实战里用得最多、见效最快的一招没有之一。做法很简单把一个大IN列表拆成若干个几百一批的小IN分批查询最后在业务层合并结果。我一般把批量大小控制在 500 到 1000 之间。为什么是这个区间主要是让优化器处理起来更舒服执行计划更容易走range回表次数和临时表压力都能显著下降。分片大小不用死记线上压测一下就能找到自己的阈值。一个简单的分片查询模板def batch_in_query(conn, id_list, batch_size500): result [] for i in range(0, len(id_list), batch_size): batch id_list[i:i batch_size] placeholders ,.join([%s] * len(batch)) sql fSELECT * FROM orders WHERE user_id IN ({placeholders}) result.extend(conn.execute(sql, batch)) return result这里要注意如果原始 SQL 里有ORDER BY和LIMIT分片之后不能直接拼结果因为每片各自排序、各自LIMIT整体结果就错了。正确做法是每片只查候选数据在业务内存里做全局排序和截断。如果数据量实在太大内存排序也扛不住就用UNION ALL把各片查出来再统一排序但UNION ALL也可能引入临时表需要压测验证。分片带来的额外好处是单次查询的锁范围更小、错误重试粒度更小、对主库和从库的压力更平滑。缺点是代码稍微复杂一点需要多几次网络往返。为了抵消这点可以用并发但要克制一般并发数别超过 4否则就是把一个慢查询变成多个快查询总资源开销实际上可能更高。3.2 让 IN 查询走对索引复合索引与 ICP分片是“减量”索引是“定向”。IN查询能不能快索引的设计非常关键。先说复合索引。如果你的 SQL 是WHERE status IN (...) ORDER BY create_time DESC那么建立一个(status, create_time)的复合索引就能让 MySQL 在索引内部完成排序避免filesort。这是最典型的“索引对齐排序键”的用法。如果漏了create_time这一列即使status的IN筛选很快最后的排序照样会把性能拖下来。再看 ICP也就是 Index Condition Pushdown索引条件下推。MySQL 5.6 之后默认开启它能把IN条件直接下推到存储引擎层在读取索引的时候就过滤掉不符合条件的记录减少回表。判断方法很简单看EXPLAIN的Extra列如果出现Using index condition说明 ICP 生效了。如果你的表引擎和版本都支持通常不需要额外配置但要注意不要在IN列上套函数比如WHERE DATE(create_time) IN (...)一旦套了函数索引就废了。复合索引也不是越多越好。每个索引都要占空间、拖慢写入。建索引之前先看看这个表是不是写多读少如果是线上高并发写入的表那么宁愿花点功夫做分片和延迟关联也不要为每一个查询单独建一个宽索引。3.3 覆盖索引和延迟关联让查询尽量别回表覆盖索引是容易被低估的救星。简单说如果查询需要的所有列都在二级索引里那么 MySQL 根本不用回表直接在索引上就能拿到全部数据。EXPLAIN的Extra列显示Using index就是覆盖索引生效。举个例子批量导出时经常只需要id, user_id, order_no, create_time这几列那么建一个(user_id, create_time, id, order_no)的复合索引查询就直接在索引里完成回表次数降为 0。注意不要为了覆盖而把大量文本字段塞进索引那会让索引页过大、占用大量内存反而拖慢全局性能。延迟关联则是另一种思路。当业务确实需要查出完整行数据但排序和过滤又很重时可以先只查主键 ID-- 原始写法慢 SELECT * FROM orders WHERE user_id IN (...) ORDER BY create_time DESC LIMIT 50; -- 延迟关联写法快 SELECT t.* FROM orders t JOIN ( SELECT id FROM orders WHERE user_id IN (...) ORDER BY create_time DESC LIMIT 50 ) tmp ON t.id tmp.id ORDER BY t.create_time DESC;子查询里只查id和排序列可以走覆盖索引先拿回 50 个主键 ID再回表取完整行。这个时候回表只有 50 次而不是原来的一次性几百上千次效果立竿见影。3.4 临时表 JOIN 替代大 IN从集合运算的角度破解如果IN列表大到一个离谱的程度比如上万甚至几万那分片和索引都还不够我推荐用临时表JOIN来替代。这个方案的最强形态是让 MySQL 把 ID 列表当成一张小表和业务大表做JOIN由优化器决定驱动顺序和连接算法。MySQL 8.0 之后支持 hash join对这种场景尤其友好。具体步骤建一张临时表比如tmp_ids字段就是id最好加主键索引。把业务拿到的 ID 列表批量插入这张临时表。插入方式用INSERT INTO ... VALUES (...), (...), ...几千条一批或者用客户端 load data。执行业务查询把WHERE id IN (...)改成JOIN tmp_ids t ON t.id o.id。用完就DROP TEMPORARY TABLE避免影响后续连接。临时表方案为什么能赢因为一个个走索引定位的IN是重复随机 IO而JOIN可以让优化器选择把小表作为驱动表用 hash join 或 block nested loop 做一次扫描匹配整体 IO 更有规律。尤其是当驱动表小、被驱动表大且连接字段是主键时性能差距会非常明显。但临时表方案不是银弹。临时表如果太大超过tmp_table_size和max_heap_table_size的限制MySQL 会把内存临时表转成磁盘临时表性能照样崩。所以无论IN还是临时表都要控制单批数据量必要时分批处理。另外MySQL 8.0 里可以用EXPLAIN FORMATTREE看执行计划是否真的用了 hash join不要凭感觉。3.5 业务缓存兜底减少同样的“大 IN”反复执行技术手段都做完了还有一个容易被忽略的层面业务重复查询。很多慢 SQL 之所以可恨是因为同样的IN列表反复出现每次都把数据库拖下水。比如标签系统里同一批人群 ID 被不同的报表反复查询权限系统里同一组角色 ID 被多个请求反复过滤。这种场景适合加一层业务缓存把“ID 集合 版本号”缓存起来配合合理的 TTL在版本号变化时重建缓存。比如用 Redis 缓存一份查询结果或者 ID 集合下次同样的IN进来时直接命中缓存数据库连看都不用看。要提醒的是缓存解决的是“重复的大查询”解决不了“真正的大数据量查询”。如果每次请求的 ID 列表都不同缓存意义不大。另外别尝试缓存全表数据那会把内存打爆缓存“结果集 版本号”这种细粒度数据就好。4. 一个真实案例批量导出场景的 IN 查询优化4.1 背景和原始 SQL这个案例是我在实际项目中处理过的一个批量导出需求。业务场景是后台管理员选了一批用户 ID按 ID 列表导出订单明细一次最多选 5000 个 ID。订单表有 3000 万行SQL 大概是这样的SELECT * FROM orders WHERE user_id IN (…5000 个 ID…) AND create_time BETWEEN 2023-01-01 AND 2023-06-30 ORDER BY id;这个查询在从库上要跑 2 到 8 秒而且并发一高从库延迟就飙升。一开始 DBA 给的建议是加索引但加了(user_id, create_time)索引后效果十分有限该慢还是慢。4.2 执行计划诊断问题到底在哪EXPLAIN的结果是这样的typerangekeyidx_user_createrows显示 12 万Extra里有Using index condition; Using filesort。看到Using filesort我基本就锁定了核心矛盾。5000 个IN值每个值都需要到二级索引定位再回表取完整行取完 12 万行之后还要在临时表里按id排序最后才输出。回表 12 万次加上 12 万行的排序这个成本是非常可观的。单纯的索引已经救不了它因为问题不只是字段上没索引而是“回表次数”和“结果集处理方式”都出了问题。4.3 优化方案组合分片 覆盖索引 内存排序针对这个案例我用了三招组合。第一招是分片。把 5000 个 ID 拆成每批 500 个一共 10 批。每批的索引定位次数从 5000 降到了 500单次查询的压力立刻下来。第二招是覆盖索引。导出业务其实只需要id, user_id, order_no, create_time, amount这几个字段。我调整了查询让每批只查这几列而不是SELECT *这样查询可以在覆盖索引内完成回表次数降为 0。建一个合适的复合索引来覆盖查询和排序列。第三招是内存排序。由于导出的最终结果要按照id排序输出而分片后每批只能保证局部有序所以最后在导出服务的内存里做一次全局排序再落盘或写 CSV。内存排序对 10 批、每批几百上千条结果来说毫无压力。优化后的结果是单批查询耗时 50 毫秒左右10 批加上内存排序总耗时不到 300 毫秒主库和从库的压力都明显下降。这个效果比单纯加索引好了一个数量级。4.4 给类似导出场景的参考模板导出类业务有一个通用模板可以套用先明确最终需要哪些字段别一股脑SELECT *。根据字段设计覆盖索引尽量让查询在索引里完成。把大IN拆成每批 500 到 1000。每批只查必要字段必要时延迟关联回表。结果在业务侧合并、排序、分页。如果业务允许异步把导出任务丢到队列避免用户长时间等待。这套模板不复杂但每一步都是在减少数据库需要处理的数据量属于“积小胜为大胜”的思路。5. 常见问题与排查技巧实录5.1 IN 列表到底多长应该分片?别信网上的阈值网上经常看到“超过 1000 必须分片”之类的说法说实话意义不大。分片的阈值和表结构、数据量、索引设计、服务器配置都有关系真正靠得住的判断方法是实测。用EXPLAIN ANALYZEMySQL 8.0.18或者performance_schema里的语句统计信息对比一下 500、1000、2000 三档列表长度下的执行时间和资源消耗。哪个长度出现耗时拐点就把分片阈值定在那里。我在不同项目里定过的最优值有 300 的也有 1500 的完全不一样。除了耗时还要看一个容易被忽略的指标临时表落盘次数。如果EXPLAIN里显示Using temporary且状态变量里Created_tmp_disk_tables在上涨说明临时表已经转磁盘了这时候就该缩小分片或者调整排序方式。5.2 IN 和 EXISTS、JOIN 能互相替换吗IN、EXISTS、JOIN这三者在语义上并不完全等价但 MySQL 8.0 的优化器已经足够聪明很多时候会自动做子查询转换。8.0.16 之后优化器会把IN子查询改写成半连接semijoin执行计划里能看到相关提示。这里要特别提醒一句如果IN列表不是来自一个常量列表而是来自子查询比如WHERE user_id IN (SELECT id FROM vip_users WHERE level 1)这时候优先考虑改写成JOIN或EXISTS。因为子查询结果可能很大MySQL 会把子查询结果物化成一张临时表物化过程本身就是一大开销。最理想的写法是明确告诉优化器你的数据关系。比如子查询返回的结果集很大但过滤后很小可以先JOIN如果子查询很快、外层表很大且只要能判断存在性就用EXISTS。这需要结合实际执行计划判断不要背口诀。5.3 参数配置和连接层还有哪些坑第一个坑是max_allowed_packet。当IN列表特别长预编译 SQL 语句本身就可能超过这个限制客户端会直接报错。解决方法要么改大这个参数要么分片。从稳定性角度我更推荐分片因为你永远不知道业务下次会传进来多少个 ID。第二个坑是临时表相关的参数。MySQL 8.0 默认内存临时表引擎是TempTabletmp_table_size和max_heap_table_size控制内存临时表大小上限。查询里如果有ORDER BY、GROUP BY、DISTINCT或UNION并且结果集超出限制就会转磁盘。优化时可以适当调大这两个值但要清楚这是用内存换性能内存不是无限的。第三个坑是连接池的查询超时。有些框架默认查询超时只有几秒大IN查询一旦超过就会抛异常表现为“时好时坏”。排查慢 SQL 时不要只盯着数据库客户端的超时配置也要一起看。5.4 大数据量 IN 更新和删除比查询更危险最后重点讲一个大家都在踩、但很少被写进文档的坑对大数据量IN做UPDATE或DELETE。查询再慢顶多拖慢一条链路但更新和删除会锁行、会产生大量 binlog、会拖慢从库。我处理过一次事故一条DELETE FROM t WHERE id IN (...)在主库执行只要 5 秒结果从库延迟了 20 分钟线上读请求直接雪崩。正确的姿势是分批、带条件、逐步推进。比如-- 每次只删 1000 行避免大事务 DELETE FROM t WHERE id IN (…) AND id last_id ORDER BY id LIMIT 1000;然后在事务里循环执行直到影响行数为 0。这样每一批的锁范围小、binlog 事件少从库压力平滑。如果业务允许还可以在低峰期执行或者用异步队列慢慢清理。看到这里你会发现IN查询优化没有一招鲜关键在于把问题拆开是回表太多还是排序太重或者是写放大太吓人我个人的体会是数据库优化最忌凭感觉改 SQL一定要先用EXPLAIN ANALYZE和状态变量把耗时分布看清楚再决定分片、索引、临时表还是缓存。分片、覆盖索引、延迟关联这三个组合已经能覆盖绝大多数“无法避免的大 IN”场景。最后分享一个小习惯每次给IN查询做完优化我会把优化前后的执行计划和耗时记录在案下次再有人质疑“为什么不一次查完”直接拿数据说话。这样既是对别人负责也是在给自己画一条安全边界。
MySQL IN查询优化：分片、覆盖索引与临时表JOIN的落地实践
做后端这几年被IN查询“背刺”的次数可不算少。尤其是权限过滤、批量 ID 查询、批量导出这类业务产品一句话“把选中的 ID 都查出来”SQL 里就甩进来几百甚至上千个 ID。数据量小的时候IN用着是真爽可一旦目标表到了千万级IN列表超过几百查询时长直接从毫秒级变成秒级严重的时候能把从库拖出明显延迟。MySQLIN查询在大数据量业务无法避免的情境下优化从来不是一句“改成JOIN就行”能糊弄过去的。这篇文章聊的就是在“必须用IN、业务不能改、数据量又很大”这种夹缝里还能做哪些有实际效果的优化。我会先讲清楚IN到底慢在哪里再给出一套从分片、索引、覆盖索引到临时表JOIN的落地方案最后用一个批量导出的真实案例串起来。适合正在排查慢 SQL、被 DBA 追问、或者在设计查询方案时想少走弯路的后端同学。1. 先搞清楚IN 查询到底慢在哪一步1.1 慢 SQL 日志里的假象不要只看扫描行数很多同学拿到慢 SQL第一件事是EXPLAIN看到typerange、rows几万就以为问题不大。实际上IN查询最容易骗人的就是这一行rows。举一个我踩过的例子。业务表orders有 3000 万行SQL 长这样SELECT * FROM orders WHERE user_id IN (1001, 1002, 1003, ... ) -- 几百个 ORDER BY create_time DESC LIMIT 20;EXPLAIN显示typerangerows8000左右。当时我也差点被糊弄过去结果这个查询在从库上跑了 2 秒多。问题出在哪rows只是优化器估算的“索引扫描范围”而真正要命的是ORDER BY create_time和LIMIT组合。MySQL 需要把所有命中的行先找出来再按create_time排序最后才取 20 条。这个排序过程会用到内存临时表数据量大一点就会落到磁盘临时表性能瞬间崩掉。所以解读IN查询的慢不要只盯着rows。要看全链路索引定位、回表次数、排序、临时表、行数放大这些环节里任何一个都可能成为瓶颈。1.2 三个容易被忽略的隐形瓶颈除了排序和临时表还有三个隐形瓶颈平时看EXPLAIN看不出来但实际影响非常大。第一是回表放大效应。假如IN列表里有 500 个值每个值都能命中索引那么就要进二级索引找 500 次位置再回聚簇索引取 500 次完整行。如果每行数据很宽或者二级索引区分度不高回表成本会被成倍放大。这个放大效应呈线性列表越长越明显。第二是 binlog 和主从复制放大。在基于行row格式的 binlog 下一条UPDATE ... WHERE id IN (...)或者DELETE ... WHERE id IN (...)主库执行时会逐行产生 binlog 事件。主库可能 1 秒执行完从库要回放几万条事件延迟就出来了。很多同学只测主库耗时忽略了从库延迟上线之后才被报警打脸。第三是优化器对超长IN列表的处理。MySQL 6.0 之前的优化器比较“死板”列表太长时它会把这个范围条件展开成一个巨大的range扫描或者因为代价估算过高而放弃最优索引。虽然 8.0 之后的优化器改进了不少但列表超过一定长度执行计划依然可能“抽风”。2. 先别急着改 SQL你的业务到底属于哪一种“无法避免”2.1 多大才算“大数据量 IN”我自己的经验阈值是这样的目标表行数过百万IN列表超过 500或者EXPLAIN里的估算行数超过全表的 5%就要高度警惕。500 这个数字不是拍脑袋拍出来的。它和优化器的一个行为有关IN列表会被转换成一堆OR条件的范围扫描列表越长优化器在评估执行计划时的代价估算越高越可能出现执行计划抖动。另外IN列表过长时整个 SQL 文本会变得很大极端情况下还会碰到max_allowed_packet的限制。比列表长度更关键的是筛选项的区分度。IN用的是主键、唯一索引、普通二级索引还是完全没有索引的普通列如果是普通列且没有索引那不管IN写得多短都白搭。如果字段区分度很差比如状态字段只有两三个值即使命中行数很少回表成本和排序成本也一点都不会少。所以在优化之前先确认索引和区分度否则后面的方案都建立在沙滩上。2.2 表面上“无法避免”其实可以绕开的场景我见过很多项目嘴上说着“必须用IN改不了”分析一圈之后发现其实是能绕开的。第一种是多租户权限过滤。这种场景通常可以先按租户和角色把数据范围“圈定”成一个更小的集合再通过JOIN去关联过滤而不是一上来就用一个大IN列表去WHERE里硬筛。第二种是大量 ID 来自外部接口。比如上游系统给你返回了 5000 个 ID业务上必须用这 5000 个 ID 去本地库查明细。这种完全可以先把 ID 落地成本地临时表再JOIN而不必在 SQL 里塞一个巨型IN列表。第三种是分页查询的 ID 集合。很多时候业务先查出符合条件的 ID 分页然后拿着当前页的 ID 去查完整数据。这种场景适合用延迟关联先只查主键 ID 分页再回表组装数据。IN本身不是原罪损耗出在“使用方式”上。上面这三种场景优化思路其实是“改变数据流向”而不是硬扛IN。2.3 真正躲不掉 IN 的典型业务长什么样真正无法避免的IN通常同时满足这么几个条件ID 列表来自用户选择或上游系统业务上必须按这个集合精确过滤集合本身很大比如批量审核、批量导出、标签人群圈选而且无法用JOIN或临时表替换比如上游数据不在本库、格式频繁变化、不想引入额外表结构。这种时候优化方向就要变一变不是“不用IN”而是“让IN查询尽量少吃资源、少放大回表、少制造临时表”。下面的优化手段就是围绕这个目标展开的。3. 直接能落地的优化手段分片、索引、临时表 JOIN3.1 分片 IN把大 IN 拆成小批最朴素也最有效分片IN是我在实战里用得最多、见效最快的一招没有之一。做法很简单把一个大IN列表拆成若干个几百一批的小IN分批查询最后在业务层合并结果。我一般把批量大小控制在 500 到 1000 之间。为什么是这个区间主要是让优化器处理起来更舒服执行计划更容易走range回表次数和临时表压力都能显著下降。分片大小不用死记线上压测一下就能找到自己的阈值。一个简单的分片查询模板def batch_in_query(conn, id_list, batch_size500): result [] for i in range(0, len(id_list), batch_size): batch id_list[i:i batch_size] placeholders ,.join([%s] * len(batch)) sql fSELECT * FROM orders WHERE user_id IN ({placeholders}) result.extend(conn.execute(sql, batch)) return result这里要注意如果原始 SQL 里有ORDER BY和LIMIT分片之后不能直接拼结果因为每片各自排序、各自LIMIT整体结果就错了。正确做法是每片只查候选数据在业务内存里做全局排序和截断。如果数据量实在太大内存排序也扛不住就用UNION ALL把各片查出来再统一排序但UNION ALL也可能引入临时表需要压测验证。分片带来的额外好处是单次查询的锁范围更小、错误重试粒度更小、对主库和从库的压力更平滑。缺点是代码稍微复杂一点需要多几次网络往返。为了抵消这点可以用并发但要克制一般并发数别超过 4否则就是把一个慢查询变成多个快查询总资源开销实际上可能更高。3.2 让 IN 查询走对索引复合索引与 ICP分片是“减量”索引是“定向”。IN查询能不能快索引的设计非常关键。先说复合索引。如果你的 SQL 是WHERE status IN (...) ORDER BY create_time DESC那么建立一个(status, create_time)的复合索引就能让 MySQL 在索引内部完成排序避免filesort。这是最典型的“索引对齐排序键”的用法。如果漏了create_time这一列即使status的IN筛选很快最后的排序照样会把性能拖下来。再看 ICP也就是 Index Condition Pushdown索引条件下推。MySQL 5.6 之后默认开启它能把IN条件直接下推到存储引擎层在读取索引的时候就过滤掉不符合条件的记录减少回表。判断方法很简单看EXPLAIN的Extra列如果出现Using index condition说明 ICP 生效了。如果你的表引擎和版本都支持通常不需要额外配置但要注意不要在IN列上套函数比如WHERE DATE(create_time) IN (...)一旦套了函数索引就废了。复合索引也不是越多越好。每个索引都要占空间、拖慢写入。建索引之前先看看这个表是不是写多读少如果是线上高并发写入的表那么宁愿花点功夫做分片和延迟关联也不要为每一个查询单独建一个宽索引。3.3 覆盖索引和延迟关联让查询尽量别回表覆盖索引是容易被低估的救星。简单说如果查询需要的所有列都在二级索引里那么 MySQL 根本不用回表直接在索引上就能拿到全部数据。EXPLAIN的Extra列显示Using index就是覆盖索引生效。举个例子批量导出时经常只需要id, user_id, order_no, create_time这几列那么建一个(user_id, create_time, id, order_no)的复合索引查询就直接在索引里完成回表次数降为 0。注意不要为了覆盖而把大量文本字段塞进索引那会让索引页过大、占用大量内存反而拖慢全局性能。延迟关联则是另一种思路。当业务确实需要查出完整行数据但排序和过滤又很重时可以先只查主键 ID-- 原始写法慢 SELECT * FROM orders WHERE user_id IN (...) ORDER BY create_time DESC LIMIT 50; -- 延迟关联写法快 SELECT t.* FROM orders t JOIN ( SELECT id FROM orders WHERE user_id IN (...) ORDER BY create_time DESC LIMIT 50 ) tmp ON t.id tmp.id ORDER BY t.create_time DESC;子查询里只查id和排序列可以走覆盖索引先拿回 50 个主键 ID再回表取完整行。这个时候回表只有 50 次而不是原来的一次性几百上千次效果立竿见影。3.4 临时表 JOIN 替代大 IN从集合运算的角度破解如果IN列表大到一个离谱的程度比如上万甚至几万那分片和索引都还不够我推荐用临时表JOIN来替代。这个方案的最强形态是让 MySQL 把 ID 列表当成一张小表和业务大表做JOIN由优化器决定驱动顺序和连接算法。MySQL 8.0 之后支持 hash join对这种场景尤其友好。具体步骤建一张临时表比如tmp_ids字段就是id最好加主键索引。把业务拿到的 ID 列表批量插入这张临时表。插入方式用INSERT INTO ... VALUES (...), (...), ...几千条一批或者用客户端 load data。执行业务查询把WHERE id IN (...)改成JOIN tmp_ids t ON t.id o.id。用完就DROP TEMPORARY TABLE避免影响后续连接。临时表方案为什么能赢因为一个个走索引定位的IN是重复随机 IO而JOIN可以让优化器选择把小表作为驱动表用 hash join 或 block nested loop 做一次扫描匹配整体 IO 更有规律。尤其是当驱动表小、被驱动表大且连接字段是主键时性能差距会非常明显。但临时表方案不是银弹。临时表如果太大超过tmp_table_size和max_heap_table_size的限制MySQL 会把内存临时表转成磁盘临时表性能照样崩。所以无论IN还是临时表都要控制单批数据量必要时分批处理。另外MySQL 8.0 里可以用EXPLAIN FORMATTREE看执行计划是否真的用了 hash join不要凭感觉。3.5 业务缓存兜底减少同样的“大 IN”反复执行技术手段都做完了还有一个容易被忽略的层面业务重复查询。很多慢 SQL 之所以可恨是因为同样的IN列表反复出现每次都把数据库拖下水。比如标签系统里同一批人群 ID 被不同的报表反复查询权限系统里同一组角色 ID 被多个请求反复过滤。这种场景适合加一层业务缓存把“ID 集合 版本号”缓存起来配合合理的 TTL在版本号变化时重建缓存。比如用 Redis 缓存一份查询结果或者 ID 集合下次同样的IN进来时直接命中缓存数据库连看都不用看。要提醒的是缓存解决的是“重复的大查询”解决不了“真正的大数据量查询”。如果每次请求的 ID 列表都不同缓存意义不大。另外别尝试缓存全表数据那会把内存打爆缓存“结果集 版本号”这种细粒度数据就好。4. 一个真实案例批量导出场景的 IN 查询优化4.1 背景和原始 SQL这个案例是我在实际项目中处理过的一个批量导出需求。业务场景是后台管理员选了一批用户 ID按 ID 列表导出订单明细一次最多选 5000 个 ID。订单表有 3000 万行SQL 大概是这样的SELECT * FROM orders WHERE user_id IN (…5000 个 ID…) AND create_time BETWEEN 2023-01-01 AND 2023-06-30 ORDER BY id;这个查询在从库上要跑 2 到 8 秒而且并发一高从库延迟就飙升。一开始 DBA 给的建议是加索引但加了(user_id, create_time)索引后效果十分有限该慢还是慢。4.2 执行计划诊断问题到底在哪EXPLAIN的结果是这样的typerangekeyidx_user_createrows显示 12 万Extra里有Using index condition; Using filesort。看到Using filesort我基本就锁定了核心矛盾。5000 个IN值每个值都需要到二级索引定位再回表取完整行取完 12 万行之后还要在临时表里按id排序最后才输出。回表 12 万次加上 12 万行的排序这个成本是非常可观的。单纯的索引已经救不了它因为问题不只是字段上没索引而是“回表次数”和“结果集处理方式”都出了问题。4.3 优化方案组合分片 覆盖索引 内存排序针对这个案例我用了三招组合。第一招是分片。把 5000 个 ID 拆成每批 500 个一共 10 批。每批的索引定位次数从 5000 降到了 500单次查询的压力立刻下来。第二招是覆盖索引。导出业务其实只需要id, user_id, order_no, create_time, amount这几个字段。我调整了查询让每批只查这几列而不是SELECT *这样查询可以在覆盖索引内完成回表次数降为 0。建一个合适的复合索引来覆盖查询和排序列。第三招是内存排序。由于导出的最终结果要按照id排序输出而分片后每批只能保证局部有序所以最后在导出服务的内存里做一次全局排序再落盘或写 CSV。内存排序对 10 批、每批几百上千条结果来说毫无压力。优化后的结果是单批查询耗时 50 毫秒左右10 批加上内存排序总耗时不到 300 毫秒主库和从库的压力都明显下降。这个效果比单纯加索引好了一个数量级。4.4 给类似导出场景的参考模板导出类业务有一个通用模板可以套用先明确最终需要哪些字段别一股脑SELECT *。根据字段设计覆盖索引尽量让查询在索引里完成。把大IN拆成每批 500 到 1000。每批只查必要字段必要时延迟关联回表。结果在业务侧合并、排序、分页。如果业务允许异步把导出任务丢到队列避免用户长时间等待。这套模板不复杂但每一步都是在减少数据库需要处理的数据量属于“积小胜为大胜”的思路。5. 常见问题与排查技巧实录5.1 IN 列表到底多长应该分片?别信网上的阈值网上经常看到“超过 1000 必须分片”之类的说法说实话意义不大。分片的阈值和表结构、数据量、索引设计、服务器配置都有关系真正靠得住的判断方法是实测。用EXPLAIN ANALYZEMySQL 8.0.18或者performance_schema里的语句统计信息对比一下 500、1000、2000 三档列表长度下的执行时间和资源消耗。哪个长度出现耗时拐点就把分片阈值定在那里。我在不同项目里定过的最优值有 300 的也有 1500 的完全不一样。除了耗时还要看一个容易被忽略的指标临时表落盘次数。如果EXPLAIN里显示Using temporary且状态变量里Created_tmp_disk_tables在上涨说明临时表已经转磁盘了这时候就该缩小分片或者调整排序方式。5.2 IN 和 EXISTS、JOIN 能互相替换吗IN、EXISTS、JOIN这三者在语义上并不完全等价但 MySQL 8.0 的优化器已经足够聪明很多时候会自动做子查询转换。8.0.16 之后优化器会把IN子查询改写成半连接semijoin执行计划里能看到相关提示。这里要特别提醒一句如果IN列表不是来自一个常量列表而是来自子查询比如WHERE user_id IN (SELECT id FROM vip_users WHERE level 1)这时候优先考虑改写成JOIN或EXISTS。因为子查询结果可能很大MySQL 会把子查询结果物化成一张临时表物化过程本身就是一大开销。最理想的写法是明确告诉优化器你的数据关系。比如子查询返回的结果集很大但过滤后很小可以先JOIN如果子查询很快、外层表很大且只要能判断存在性就用EXISTS。这需要结合实际执行计划判断不要背口诀。5.3 参数配置和连接层还有哪些坑第一个坑是max_allowed_packet。当IN列表特别长预编译 SQL 语句本身就可能超过这个限制客户端会直接报错。解决方法要么改大这个参数要么分片。从稳定性角度我更推荐分片因为你永远不知道业务下次会传进来多少个 ID。第二个坑是临时表相关的参数。MySQL 8.0 默认内存临时表引擎是TempTabletmp_table_size和max_heap_table_size控制内存临时表大小上限。查询里如果有ORDER BY、GROUP BY、DISTINCT或UNION并且结果集超出限制就会转磁盘。优化时可以适当调大这两个值但要清楚这是用内存换性能内存不是无限的。第三个坑是连接池的查询超时。有些框架默认查询超时只有几秒大IN查询一旦超过就会抛异常表现为“时好时坏”。排查慢 SQL 时不要只盯着数据库客户端的超时配置也要一起看。5.4 大数据量 IN 更新和删除比查询更危险最后重点讲一个大家都在踩、但很少被写进文档的坑对大数据量IN做UPDATE或DELETE。查询再慢顶多拖慢一条链路但更新和删除会锁行、会产生大量 binlog、会拖慢从库。我处理过一次事故一条DELETE FROM t WHERE id IN (...)在主库执行只要 5 秒结果从库延迟了 20 分钟线上读请求直接雪崩。正确的姿势是分批、带条件、逐步推进。比如-- 每次只删 1000 行避免大事务 DELETE FROM t WHERE id IN (…) AND id last_id ORDER BY id LIMIT 1000;然后在事务里循环执行直到影响行数为 0。这样每一批的锁范围小、binlog 事件少从库压力平滑。如果业务允许还可以在低峰期执行或者用异步队列慢慢清理。看到这里你会发现IN查询优化没有一招鲜关键在于把问题拆开是回表太多还是排序太重或者是写放大太吓人我个人的体会是数据库优化最忌凭感觉改 SQL一定要先用EXPLAIN ANALYZE和状态变量把耗时分布看清楚再决定分片、索引、临时表还是缓存。分片、覆盖索引、延迟关联这三个组合已经能覆盖绝大多数“无法避免的大 IN”场景。最后分享一个小习惯每次给IN查询做完优化我会把优化前后的执行计划和耗时记录在案下次再有人质疑“为什么不一次查完”直接拿数据说话。这样既是对别人负责也是在给自己画一条安全边界。