MySQL 大量 IN 的查询优化

背景

（1）MySQL 8.0 版本

（2）业务中遇到大量 IN 的查询，例：

SELECT id, username, icon 
FROM users 
WHERE id IN (123, 523, 1343, ...);

其中 id 为主键，IN 的列表长度有 8000 多个

问题

行数扫描 30W+，无法用到主键索引
造成 MySQL CPU 突升，其它的 SQL 堆积导致 HTTP 502 响应

原因

MySQL 的范围优化器在执行查询优化时，所需消耗的内存超出系统所配置的默认内存（range_optimizer_max_mem_size 8M），导致查询走次优的查询方式（全表扫描）

官方文档：

For individual queries that exceed the available range optimization memory and for which the optimizer falls back to less optimal plans, increasing the range_optimizer_max_mem_size value may improve performance.
https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html

解决

适当增大 range_optimizer_max_mem_size 内存
（通过试验，将默认的 8M 提高至 24M 后，大量 IN 的查询不再导致 MySQL CPU 突升）

其它解决方法

使用临时表的方案

WITH t1(user_id) AS (
  VALUES
     ROW(123)
    ,ROW(523)
    ,ROW(1343)
    ,ROW(66892)
    ...
	...
	...
    ,ROW(65815)
    ,ROW(357112)
)
SELECT
 id, username, icon
FROM t1 INNER JOIN users 
AS t ON t.id = t1.user_id

参考

• https://blog.csdn.net/qq_37107851/article/details/122688567 Mysql（3）Range 优化
• https://www.cnblogs.com/nanxiang/p/15133394.html MySQL数据库in 太多不走索引案例
• https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html 10.2.1.2 Range Optimization