MySQL IN 里塞 10000 个值？90% 开发者都踩过的坑，底层原理 + 全场景解决方案一次讲透

日常开发中，我们几乎每天都会和MySQL的IN子句打交道：批量查询用户订单、过滤指定ID的商品数据、根据权限范围拉取业务数据……很多开发者为了图方便，会直接把成百上千甚至上万个值塞进IN子句，结果轻则出现慢查询拖垮接口性能，重则导致数据库连接耗尽、主从同步延迟，甚至引发线上故障。

很多开发者对IN子句的认知停留在“IN等价于多个OR”、“IN列表不能超过1000个值”的误区里，遇到大IN列表的问题只会盲目拆分SQL，结果越改性能越差。

一、先搞懂MySQL IN子句的底层执行逻辑

很多人对IN的理解完全错误，这是导致踩坑的核心原因。我们先从官方定义和底层执行流程，彻底搞懂IN子句到底是怎么工作的。

1.1 IN子句的官方定义

MySQL官方文档中，IN的语法为：expr IN (value1, value2, ..., valueN)，其核心语义是：判断expr是否等于IN列表中的任意一个值，匹配成功返回1，否则返回0。

这里有两个核心误区必须先纠正：

• 误区1：MySQL IN列表有1000个值的硬限制→ 完全错误。MySQL从5.7版本开始，就没有对IN列表的元素数量设置硬编码限制，唯一的限制是整个SQL语句的数据包大小，由系统参数max_allowed_packet控制（默认4MB）。所谓的1000个值限制，是Oracle数据库的规则，很多开发者误把Oracle的规则套到了MySQL上。
• 误区2：IN (a,b,c) 完全等价于 a OR b OR c→ 半对半错。从语义结果上看，两者是等价的，但从MySQL底层执行逻辑和性能上看，两者天差地别。MySQL对IN子句做了专门的优化，而OR条件的优化能力非常有限。

1.2 IN子句的完整执行流程

我们用一张流程图，清晰展示MySQL处理带常量IN列表的SQL的完整流程：

1.3 核心优化逻辑拆解

对于我们最常用的常量IN列表（比如IN (1,2,3,...,10000)），MySQL的核心优化点有两个：

1. 去重与排序：优化器会先对IN列表中的常量值进行去重，然后按照升序排序，消除重复值带来的无效匹配，同时为二分查找做准备。
2. 二分查找匹配：排序后的IN列表会被构建成一个有序数组，MySQL会使用二分查找来判断索引项是否匹配IN列表中的值，时间复杂度从OR条件的O(n)降到了O(logn)。

举个例子：IN (3,1,4,1,5) 会被优化器处理为有序数组[1,3,4,5]，然后用二分查找匹配索引中的user_id值，而OR条件 user_id=3 OR user_id=1 OR user_id=4 OR user_id=1 OR user_id=5 不会被排序和去重，只能逐个条件匹配，性能差距极大。

1.4 大IN列表的优化边界

既然MySQL对IN做了这么好的优化，为什么IN列表里放10000个值还是会出问题？ 因为MySQL的优化是有边界的，当IN列表的元素数量超过一定阈值后，优化带来的收益会被其他开销完全覆盖，甚至出现负优化。核心的阈值有两个：

• range_opt_max_mem_size：优化器处理范围查询（包括IN列表）的最大内存限制，默认2MB。当IN列表占用的内存超过这个值时，优化器会放弃range优化，转为全表扫描。
• 优化器搜索空间：IN列表的元素越多，优化器需要计算的访问路径成本就越高，生成执行计划的时间会呈线性增长，甚至出现执行计划生成时间远超查询执行时间的情况。

二、IN列表塞10000个值，到底会带来哪些致命问题？

很多开发者觉得“我的SQL能跑通就行”，却不知道大IN列表正在从多个维度拖垮你的系统，甚至埋下线上故障的隐患。我们逐一拆解核心问题：

2.1 解析与执行计划生成开销剧增

一条带10000个值的IN子句的SQL，文本长度可能超过几十KB，甚至上百KB。MySQL处理这条SQL时，首先要做词法解析和语法解析，长SQL会直接导致解析耗时成倍增长。 更严重的是优化器的开销：优化器需要为这条SQL计算所有可能的执行路径，判断是否走索引、走哪个索引、扫描行数预估、成本计算等。IN列表的元素越多，优化器的计算量就越大，极端情况下，执行计划生成的时间会超过100ms，是正常SQL的几十倍。 而MySQL的连接是串行处理请求的，长解析时间会直接导致连接占用时间变长，系统的吞吐量急剧下降。

2.2 执行器层面的性能损耗

虽然IN列表用了二分查找，但是10000个值的二分查找需要14次循环，而1000个值只需要10次，看似差距不大，但结合索引匹配、回表操作，开销会被放大。 更关键的是，InnoDB的缓冲池（Buffer Pool）是按页管理的，大IN列表会导致InnoDB扫描大量的索引页和数据页，把热点数据从缓冲池中挤出去，导致后续的正常查询命中率下降，出现性能抖动。 同时，大IN列表的范围查询会产生大量的离散IO，即使是SSD，大量的离散IO也会导致IO等待时间变长，查询性能下降。

2.3 网络传输与存储开销放大

• 客户端-服务端网络开销：一条几十KB的SQL，比正常SQL大几十倍，每次查询都要在客户端和服务端之间传输大量的无效数据，高并发场景下会直接打满网卡带宽。
• 主从同步开销：MySQL的binlog会记录完整的SQL语句，大IN列表的SQL会导致binlog体积急剧膨胀，主从同步时需要传输更大的binlog文件，导致主从延迟增加，甚至出现主从不一致的情况。
• 存储开销：大量的长SQL会占用更多的慢查询日志、通用日志存储空间，增加日志分析和问题排查的难度。

2.4 数据库稳定性风险

• 连接池耗尽：大IN列表的SQL执行时间长，会占用数据库连接更长的时间，高并发场景下，会导致应用的数据库连接池被快速耗尽，新的请求无法获取连接，直接出现服务不可用。
• 内存OOM风险：MySQL处理大IN列表时，会把IN列表加载到内存中进行排序和二分查找，同时执行查询时会占用sort_buffer、join_buffer等内存空间，大量并发的大IN列表查询，会导致MySQL的内存占用急剧上升，甚至触发OOM，导致数据库进程被系统杀死。
• 锁等待加剧：InnoDB的行锁是在索引上加的，大IN列表的查询会锁定大量的索引项，如果同时有更新操作，会导致锁等待时间变长，甚至出现死锁。

2.5 业务代码的安全与性能隐患

• SQL注入风险：很多开发者处理大IN列表时，会用字符串拼接的方式生成SQL，比如"SELECT * FROM t_order WHERE user_id IN (" + StringUtils.join(userIdList, ",") + ")"，如果userIdList中包含恶意内容，会直接导致SQL注入漏洞，造成数据泄露甚至数据被删除。
• 应用OOM风险：用MyBatis等ORM框架处理10000个元素的IN列表时，框架会生成10000个预编译参数，处理这些参数会占用大量的JVM内存，高并发场景下会导致JVM的GC频繁，甚至出现OOM。
• 预编译缓存失效：MySQL的预编译缓存是按SQL文本的哈希值来缓存的，IN列表的元素数量不同，SQL文本就不同，每个不同长度的IN列表都会生成一个新的预编译语句，导致预编译缓存命中率急剧下降，MySQL的解析开销进一步增加。

三、90%开发者都在用的错误解决方案，越改越坑

遇到大IN列表的问题，很多开发者会用一些想当然的解决方案，结果不仅没解决问题，反而带来了更严重的性能问题。我们逐一拆解这些错误方案，以及为什么不能用。

3.1 错误方案1：拆分为多个IN子句用OR拼接

很多人觉得“IN列表不能超过1000个值，那我拆成10个IN(1-1000) OR IN(1001-2000)不就行了？” 大错特错！ 这种写法会直接导致MySQL优化器放弃range优化，因为OR条件会破坏索引的有序性，优化器无法对多个OR连接的IN子句进行统一的排序和二分查找，最终大概率会走全表扫描，性能比原来的单个大IN列表差10倍以上。

-- 绝对禁止的错误写法
SELECT * FROM t_order WHERE
user_id IN (1,2,...,1000) OR
user_id IN (1001,1002,...,2000) OR
user_id IN (9001,9002,...,10000);


用EXPLAIN查看执行计划，你会发现type列是ALL，也就是全表扫描，即使user_id列有索引，也不会被用到。

3.2 错误方案2：用UNION ALL拆分多个小IN查询

还有人会把10000个值拆成10个SELECT，用UNION ALL拼接：

-- 错误写法
SELECT * FROM t_order WHERE user_id IN (1,2,...,1000)
UNION ALL
SELECT * FROM t_order WHERE user_id IN (1001,1002,...,2000)
UNION ALL
SELECT * FROM t_order WHERE user_id IN (9001,9002,...,10000);


这种写法的问题非常多：

1. 多次网络往返：应用需要把一条大SQL拆成10条小SQL，发送给MySQL，MySQL需要执行10次查询，多次网络往返会导致接口耗时成倍增加。
2. 多次执行计划生成：MySQL需要为10条SQL分别生成执行计划，优化器开销是原来的10倍。
3. 事务一致性问题：如果在两次查询之间，有数据被更新，会导致查询结果不一致，出现脏读、幻读的问题。
4. 结果集合并开销：MySQL需要把10个结果集合并成一个，占用大量的内存和CPU资源。

3.3 错误方案3：无脑调大max_allowed_packet参数

当遇到SQL数据包过大的报错时，很多人会直接把max_allowed_packet从默认的4MB调到100MB甚至1GB，觉得这样就能解决问题。 这是典型的饮鸩止渴！ max_allowed_packet参数控制的是MySQL单个数据包的最大大小，调大这个参数，会带来两个致命风险：

1. 内存OOM风险：MySQL会为每个连接预分配max_allowed_packet大小的内存缓冲区，如果你把这个参数调到1GB，那么10个并发连接就会占用10GB的内存，很容易导致系统内存不足，触发OOM，杀死MySQL进程。
2. 拒绝服务攻击风险：攻击者可以发送超大的SQL数据包，直接打满MySQL的内存，导致数据库不可用。

3.4 错误方案4：用FIND_IN_SET函数替代IN子句

还有人会用FIND_IN_SET函数来替代IN子句，比如：

-- 错误写法
SELECT * FROM t_order WHERE FIND_IN_SET(user_id, '1,2,3,...,10000');


这种写法的问题是致命的：FIND_IN_SET函数会导致索引完全失效，因为MySQL无法对函数作用后的列进行索引匹配，最终一定会走全表扫描。如果你的表有1000万条数据，这条SQL的执行时间会超过几十秒，完全无法在生产环境使用。

四、全场景最优解决方案，从临时修复到根治

我们按照“紧急临时修复→长期根治”的顺序，给出全场景的最优解决方案，每个方案都有明确的适用场景、详细的代码示例，以及优缺点分析。

4.1 方案1：分批查询，紧急线上修复

适用场景：线上出现慢查询故障，需要快速修复，无法改动表结构和业务逻辑；IN列表的元素数量在10000以内，并发量不高。核心逻辑：把大的IN列表拆分成多个小批次，每个批次的IN元素数量控制在500-1000个，分批查询后合并结果集。 为什么是500-1000个？因为MySQL优化器对这个区间的IN列表优化效果最好，执行计划生成时间极短，二分查找的效率最高，同时不会触发优化器的内存限制。

4.1.1 代码实现

我们基于MyBatis Plus实现分批查询：

package com.jam.demo.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.jam.demo.entity.Order;
import com.jam.demo.mapper.OrderMapper;
import com.jam.demo.service.OrderService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
* 订单服务实现类
* @author ken
*/
@Slf4j
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {

   /**
    * 每批查询的最大元素数量
    */
   private static final int BATCH_SIZE = 500;

   @Override
   public List<Order> listOrderByUserIdBatch(List<Long> userIdList) {
       List<Order> resultList = new ArrayList<>();
       if (CollectionUtils.isEmpty(userIdList)) {
           return resultList;
       }
       // 拆分用户ID列表为固定大小的批次
       List<List<Long>> partitionList = Lists.partition(userIdList, BATCH_SIZE);
       for (List<Long> batchList : partitionList) {
           LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<Order>()
                   .in(Order::getUserId, batchList);
           List<Order> batchResult = this.list(queryWrapper);
           if (!CollectionUtils.isEmpty(batchResult)) {
               resultList.addAll(batchResult);
           }
       }
       return resultList;
   }
}


4.1.2 关键注意事项

1. 批次大小固定：必须使用固定的批次大小（比如500），即使最后一批不足500个，也不要调整批次大小。这样可以保证SQL文本的固定，提高MySQL预编译缓存的命中率。
2. 去重处理：合并结果集后，如果需要去重，可以用Stream的distinct()方法，或者在查询时用DISTINCT关键字。
3. 事务一致性：如果需要保证所有批次查询的数据一致性，必须把所有批次的查询放在同一个事务中，使用RR（可重复读）隔离级别，避免出现幻读。
4. 禁止并行查询：很多人会用多线程并行查询多个批次，这会导致数据库的并发压力瞬间增大，高并发场景下会直接打满数据库连接，绝对禁止。

4.1.3 优缺点分析

优点：

• 改造成本极低，无需改动SQL和表结构，只需修改业务代码，适合线上紧急修复。
• 性能提升明显，相比单个大IN列表，执行时间可以降低60%以上。
• 避免了预编译缓存失效的问题，固定批次大小可以保证SQL文本固定。缺点：
• 多次数据库查询，增加了网络往返开销。
• 并发量高的场景下，会增加数据库的QPS压力。
• 无法从根本上解决问题，只是缓解了大IN列表的性能问题。

4.2 方案2：临时表JOIN查询，中大规模数据最优方案

适用场景：IN列表的元素数量超过10000个，或者并发量较高，分批查询无法满足性能要求；无法改动业务逻辑和表结构。核心逻辑：把IN列表中的值批量插入到MySQL的临时表中，然后用业务表和临时表做INNER JOIN查询，替代IN子句。 MySQL的临时表有三个核心优势：

1. 会话级别：临时表只对当前会话可见，会话结束后自动删除，不会出现表名冲突的问题。
2. 支持索引：可以给临时表的主键建立索引，JOIN查询时可以用到索引，性能极高。
3. 优化器友好：MySQL优化器可以对两个表的JOIN做全面的优化，选择最优的JOIN方式，生成最优的执行计划。

4.2.2 代码实现

首先是Mapper接口：

package com.jam.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jam.demo.entity.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface OrderMapper extends BaseMapper<Order> {

   /**
    * 批量插入用户ID到临时表
    * @param idList 用户ID列表
    */
   void batchInsertTempTable(@Param("idList") List<Long> idList);

   /**
    * 临时表关联查询订单数据
    * @return 订单列表
    */
   List<Order> selectByTempTableJoin();

   /**
    * 通过权限编码关联查询订单
    * @param privilegeCode 权限编码
    * @return 订单列表
    */
   List<Order> selectByJoin(@Param("privilegeCode") String privilegeCode);
}


OrderMapper.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jam.demo.mapper.OrderMapper">
   <insert id="batchInsertTempTable">
       INSERT INTO temp_user_id (id)
       VALUES
       <foreach collection="idList" item="id" separator=",">
           (#{id})
       </foreach>
       ON DUPLICATE KEY UPDATE id = id
   </insert>

   <select id="selectByTempTableJoin" resultType="com.jam.demo.entity.Order">
       SELECT o.* FROM t_order o
       INNER JOIN temp_user_id t ON o.user_id = t.id
   </select>

   <select id="selectByJoin" resultType="com.jam.demo.entity.Order">
       SELECT o.* FROM t_order o
       INNER JOIN t_user_privilege up ON o.user_id = up.user_id
       WHERE up.privilege_code = #{privilegeCode}
   </select>
</mapper>


Service层实现，用编程式事务保证临时表操作在同一个会话内：

package com.jam.demo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jam.demo.entity.Order;
import com.jam.demo.mapper.OrderMapper;
import com.jam.demo.service.OrderService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
* 订单服务实现类
* @author ken
*/
@Slf4j
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {

   private final PlatformTransactionManager transactionManager;

   public OrderServiceImpl(PlatformTransactionManager transactionManager) {
       this.transactionManager = transactionManager;
   }

   @Override
   public List<Order> listOrderByTempTable(List<Long> userIdList) {
       if (CollectionUtils.isEmpty(userIdList)) {
           return new ArrayList<>();
       }
       // 编程式事务控制，保证所有操作在同一个数据库会话内
       DefaultTransactionDefinition def = new DefaultTransactionDefinition();
       TransactionStatus status = transactionManager.getTransaction(def);
       try {
           // 1. 创建临时表，指定主键索引
           baseMapper.execute("CREATE TEMPORARY TABLE IF NOT EXISTS temp_user_id (" +
                   "id bigint NOT NULL PRIMARY KEY" +
                   ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
           // 2. 批量插入数据到临时表
           baseMapper.batchInsertTempTable(userIdList);
           // 3. 关联查询订单数据
           List<Order> resultList = baseMapper.selectByTempTableJoin();
           // 4. 提交事务
           transactionManager.commit(status);
           return resultList;
       } catch (Exception e) {
           // 异常回滚
           transactionManager.rollback(status);
           log.error("临时表关联查询订单失败", e);
           throw new RuntimeException("查询订单数据失败", e);
       }
   }

   @Override
   public List<Order> listOrderByJoin(String privilegeCode) {
       return baseMapper.selectByJoin(privilegeCode);
   }
}


4.2.3 关键注意事项

1. 会话一致性：临时表是会话级别的，必须保证创建临时表、插入数据、关联查询三个操作在同一个数据库会话内。使用编程式事务可以保证这一点，因为Spring的事务会绑定同一个数据库连接，也就是同一个会话。
2. 索引必须加：创建临时表时，必须给关联字段加上主键或者唯一索引，否则JOIN查询时会走全表扫描，性能极差。
3. 临时表命名：临时表的名字要唯一，避免和其他业务的临时表冲突，建议加上业务前缀。
4. 连接池配置：如果使用了共享连接池，必须保证事务结束后连接被释放，否则临时表不会被删除，会占用数据库内存。

4.2.4 优缺点分析

优点：

• 性能极高，相比分批查询，执行时间可以降低50%以上，相比单个大IN列表，执行时间可以降低80%以上。
• 一次查询即可获取结果，只有一次网络往返，减少了网络开销。
• 优化器可以生成最优的执行计划，JOIN查询可以充分利用索引。
• 避免了预编译缓存失效的问题，SQL文本固定，预编译缓存命中率100%。缺点：
• 改造成本比分批查询高，需要新增Mapper方法和XML配置。
• 临时表会占用数据库的内存空间，大量并发的临时表操作会增加数据库的内存压力。
• 需要注意事务和连接会话的管理，否则会出现临时表不存在的报错。

4.3 方案3：直接关联查询/EXISTS子查询，根治方案

适用场景：IN列表中的值来自于数据库中的其他表，这是开发中最常见的场景，也是最应该优先使用的方案。核心逻辑：很多开发者的错误写法是，先从A表查询出符合条件的10000个ID，然后把这些ID放到B表的IN子句中，分两次查询。其实完全可以直接用A表和B表做INNER JOIN，或者用EXISTS子查询，一次查询即可完成，从根源上避免了大IN列表的问题。

这是MySQL官方最推荐的写法，也是性能最好、最稳定的方案，没有之一。

4.3.1 错误写法vs正确写法

我们举一个最常见的场景：查询拥有指定权限的用户的所有订单。错误写法（先查ID再放IN）：

// 第一步：查询拥有指定权限的用户ID列表，返回10000个用户ID
List<Long> userIdList = userPrivilegeMapper.selectUserIdByPrivilegeCode("order:view");
// 第二步：把用户ID列表放到IN子句中查询订单
List<Order> orderList = orderMapper.selectList(new LambdaQueryWrapper<Order>().in(Order::getUserId, userIdList));


正确写法（直接关联查询）：

SELECT o.* FROM t_order o
INNER JOIN t_user_privilege up ON o.user_id = up.user_id
WHERE up.privilege_code = 'order:view';


Service层调用：

@Override
public List<Order> listOrderByJoin(String privilegeCode) {
   return baseMapper.selectByJoin(privilegeCode);
}


4.3.2 IN vs EXISTS，到底怎么选？

很多人搞不清IN和EXISTS的区别，这里给一个明确的、基于MySQL官方优化逻辑的选择标准：

• 当子查询的结果集很小，外表很大的时候，用IN子查询，性能更好。
• 当外表很小，子查询的结果集很大的时候，用EXISTS子查询，性能更好。
• MySQL 8.0版本中，优化器会对IN和EXISTS子查询做语义等价转换，最终生成的执行计划是一样的，性能几乎没有差距。

EXISTS子查询示例：

SELECT o.* FROM t_order o
WHERE EXISTS (
   SELECT 1 FROM t_user_privilege up
   WHERE up.user_id = o.user_id AND up.privilege_code = 'order:view'
);


4.3.3 优缺点分析

优点：

• 性能最好，相比临时表方案，执行时间可以降低70%以上，相比单个大IN列表，执行时间可以降低90%以上。
• 一次查询即可完成，无需在应用层处理数据，减少了网络往返和应用层的内存开销。
• 从根源上避免了大IN列表的问题，不会出现解析开销、预编译缓存失效、内存占用等问题。
• 代码更简洁，可维护性更高，无需处理分批、临时表等复杂逻辑。缺点：
• 需要改动业务逻辑和SQL写法，对于老系统的改造成本较高。
• 需要保证关联字段有合适的索引，否则会出现全表扫描的问题。

4.4 方案4：流式查询，超大规模数据拉取方案

适用场景：需要拉取超大规模的数据（IN列表超过10万个值），比如数据同步、离线计算、批量数据导出等场景，普通的查询会导致应用和数据库的内存溢出。核心逻辑：使用MySQL的流式查询，一次发起查询，然后逐条从服务端读取结果，不会把整个结果集一次性加载到内存中，避免了OOM的问题。

MyBatis Plus的流式查询实现：

@Override
public void exportOrderByUserId(List<Long> userIdList, OutputStream outputStream) {
   if (CollectionUtils.isEmpty(userIdList)) {
       return;
   }
   LambdaQueryWrapper<Order> queryWrapper = new LambdaQueryWrapper<Order>()
           .in(Order::getUserId, userIdList);
   // 流式查询，逐条处理结果
   baseMapper.selectList(queryWrapper, resultContext -> {
       Order order = resultContext.getResultObject();
       // 逐条写入输出流，无需把整个结果集加载到内存
       writeOrderToStream(order, outputStream);
   });
}


注意：流式查询需要在JDBC连接参数中添加useCursorFetch=true，同时设置fetchSize=Integer.MIN_VALUE，才能真正开启流式查询，否则MyBatis还是会把整个结果集一次性加载到内存中。

五、性能对比测试，用数据说话

我们在标准测试环境下，对不同方案做了全面的性能测试，测试数据如下：测试环境：

• MySQL 8.0.36，InnoDB引擎，Buffer Pool Size 4G
• 服务器配置：8核16G，SSD硬盘
• 测试表：t_order，1000万条数据，100万个不同的user_id，user_id列有二级索引
• 测试场景：查询user_id在10000个随机值中的订单数据，重复测试100次取平均值

从测试结果可以清晰的看到：

1. 直接关联查询的性能是最好的，执行时间只有直接大IN列表的1/16。
2. 临时表JOIN的性能次之，完全可以满足中大规模数据的查询需求。
3. 分批查询的性能稳定，改造成本低，适合线上紧急修复。
4. 直接大IN列表的性能最差，CPU和内存占用最高，完全不推荐在生产环境使用。

六、生产环境最佳实践与避坑指南

我们结合生产环境的实战经验，总结了一套IN子句的最佳实践，帮你彻底避开所有坑。

6.1 核心使用规范

1. 优先使用关联查询/EXISTS子查询：只要IN列表中的值来自于数据库的其他表，必须优先使用关联查询或者EXISTS子查询，从根源上避免大IN列表的问题。
2. IN列表长度严格控制：如果必须使用IN子句，单个IN列表的元素数量必须控制在500-1000以内，绝对禁止超过2000个。
3. 固定批次大小：分批查询时，必须使用固定的批次大小，保证SQL文本固定，提高预编译缓存的命中率。
4. 绝对禁止字符串拼接SQL：所有IN列表的参数必须使用预编译的方式处理，比如MyBatis的标签用#{}，绝对禁止用${}或者字符串拼接，避免SQL注入。
5. 必须保证IN字段有索引：IN查询的字段必须建立合适的索引，联合索引必须满足最左匹配原则，否则会导致全表扫描。

6.2 避坑指南

1. 避开IN列表中的NULL值：IN列表中如果包含NULL值，会导致匹配结果为未知，无法匹配到任何行，必须提前过滤掉NULL值。
2. 避开函数包裹IN字段：比如WHERE DATE(create_time) IN ('2024-01-01', '2024-01-02')，会导致索引失效，必须改为范围查询。
3. 避开不同数据类型的匹配：比如user_id是bigint类型，IN列表中的值是字符串类型，会导致隐式类型转换，索引失效，必须保证数据类型一致。
4. 避开高并发场景下的大IN列表：高并发场景下，即使是分批查询，也会导致数据库的QPS急剧上升，必须使用临时表或者关联查询的方案。
5. 避开主从库的大IN列表查询：大IN列表的查询会导致从库的IO压力增大，主从延迟增加，必须尽量优化为关联查询。

6.3 监控与告警

1. 慢查询监控：对慢查询日志进行监控，设置阈值，比如执行时间超过100ms的SQL，包含大IN列表的SQL，必须触发告警。
2. SQL文本长度监控：对超过10KB的SQL进行监控，及时发现大IN列表的SQL，推动优化。
3. 预编译缓存命中率监控：对MySQL的预编译缓存命中率进行监控，命中率低于90%时，及时排查是否有大量不同长度的IN列表SQL。

七、总结

IN子句是MySQL开发中最高频使用的语法之一，也是最容易踩坑的语法之一。很多开发者因为不了解IN子句的底层执行逻辑，盲目使用大IN列表，导致了无数的线上性能故障。

配套基础代码

实体类Order：

package com.jam.demo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
* 订单实体类
* @author ken
*/
@Data
@TableName("t_order")
@Schema(description = "订单实体")
public class Order {

   @TableId(type = IdType.AUTO)
   @Schema(description = "主键ID")
   private Long id;

   @Schema(description = "订单编号")
   private String orderNo;

   @Schema(description = "用户ID")
   private Long userId;

   @Schema(description = "订单金额")
   private BigDecimal orderAmount;

   @Schema(description = "订单状态：0-待付款，1-已付款，2-已发货，3-已完成，4-已取消")
   private Integer orderStatus;

   @Schema(description = "创建时间")
   private LocalDateTime createTime;

   @Schema(description = "更新时间")
   private LocalDateTime updateTime;
}


Service接口：

package com.jam.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.jam.demo.entity.Order;

import java.io.OutputStream;
import java.util.List;

/**
* 订单服务接口
* @author ken
*/
public interface OrderService extends IService<Order> {

   /**
    * 分批查询用户订单
    * @param userIdList 用户ID列表
    * @return 订单列表
    */
   List<Order> listOrderByUserIdBatch(List<Long> userIdList);

   /**
    * 临时表关联查询用户订单
    * @param userIdList 用户ID列表
    * @return 订单列表
    */
   List<Order> listOrderByTempTable(List<Long> userIdList);

   /**
    * 关联查询用户订单
    * @param privilegeCode 权限编码
    * @return 订单列表
    */
   List<Order> listOrderByJoin(String privilegeCode);

   /**
    * 流式导出订单数据
    * @param userIdList 用户ID列表
    * @param outputStream 输出流
    */
   void exportOrderByUserId(List<Long> userIdList, OutputStream outputStream);
}


pom.xml核心依赖：

<dependencies>
   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-web</artifactId>
       <version>3.2.5</version>
   </dependency>
   <dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>mybatis-plus-boot-starter</artifactId>
       <version>3.5.7</version>
   </dependency>
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-j</artifactId>
       <version>8.0.36</version>
   </dependency>
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.30</version>
       <scope>provided</scope>
   </dependency>
   <dependency>
       <groupId>com.google.guava</groupId>
       <artifactId>guava</artifactId>
       <version>33.0.0-jre</version>
   </dependency>
   <dependency>
       <groupId>org.springdoc</groupId>
       <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
       <version>2.5.0</version>
   </dependency>
   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-jdbc</artifactId>
       <version>3.2.5</version>
   </dependency>
</dependencies>


测试表建表语句：

CREATE TABLE `t_order` (
 `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `order_no` varchar(64) NOT NULL COMMENT '订单编号',
 `user_id` bigint NOT NULL COMMENT '用户ID',
 `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
 `order_status` tinyint NOT NULL COMMENT '订单状态：0-待付款，1-已付款，2-已发货，3-已完成，4-已取消',
 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_order_no` (`order_no`),
 KEY `idx_user_id` (`user_id`),
 KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';