面试官：MySQL in 语句为什么要限制参数数量？

大家好，我是君哥。

我们在写 SQL 时，经常会考虑到 in 语句的参数数量限制，比如，Oracle 的 in 语句参数数量超过 1000 个时会报下面错误

ORA-01795: maximum number of expressions in a list is 1000

MySQL 虽然没有明确的限制不能超过 1000，但是也会受系统参数的影响。今天来聊一聊 MySQL in 语句为什么要限制参数数量。

1.限制原因

1.1 参数限制

MySQL server 端会限制返回的数据大小，比如下面两个参数：

• max_allowed_packet：单个数据包能够传输的最大字节数，如果 in 语句返回的结果超过这个值，服务端就会返回异常 Packet for query is too large；
• net_buffer_length：网络缓冲区的大小。如果 in 语句返回的结果超过网络缓冲区大小，可能导致传输问题。

1.2 性能考虑

MySQL server 处理 in 语句也会考虑内存大小的影响：

• in 语句要查询的数据量非常大，在 SQL 中完全没有限制，比如下面的 SQL。因为 MySQL Server 要在内存中完成处理，遇到大表的全表扫描时，会占用大量内存。如果是高并发场景，很容易因为耗费内存太大导致响应慢；

select * from table1 whereidin(selectidfrom table2)

• 如果查询涉及到排序，并且排序的数据量很大，导致 sort buffer 不够用，就需要利用磁盘临时文件辅助排序，性能下降。

即使 in 语句没有影响到 Server 端内存，in 语句中参数数量太多的话，也会增加比较次数，增加单个语句的执行时间，降低性能。

2.优化建议

2.1 拆分 SQL

如果 in 语句中的值太多，可以考虑在应用代码中进行拆分，比如每个 SQL 限制传入 1000 个值，下面是一个伪代码：

List<Long> allIds = table2Dao.selectAllIds();
List<Long> splitIds;
int start = 0;
while(true){
    splitIds = start + 1000 > allIds.size() ? allIds.subList(start, allIds.size())  : allIds.subList(start, start + 1000);
    List<ResultObject> batchResults = table1Dao.query(splitIds);
if(start + 1000 > allIds.size()){
break;
 }
    start += 1000;
}

2.2 使用临时表

可以使用临时表进行优化，把 table2 中的 id 插入到临时表，然后使用 table1 和临时表进行关联查询。

--创建临时表
CREATETEMPORARYTABLE temp_table2_ids (
idBIGINT PRIMARY KEY
);
--把 table2 的 id 插入临时表
insertinto temp_table2_ids selectidfrom table2;
--使用 EXISTS 语句代替 in
SELECT * FROM table1 t1 WHEREEXISTS (SELECT * FROM temp_table2_ids t2 WHERE t1.id = t2.id);

3.总结

在 MySQL 中使用 in 语句时，要注意 MySQL Server 端的限制，同时要考虑对内存和性能的影响。可以使用业务代码中拆分 SQL 和使用临时表的方法进行优化。