仅拥有MySQL读权限时如何高效匹配近百万条待查值

方案1 优化现有IN查询逻辑 #

• 先确认匹配字段column已创建索引，无索引的前提下任何批量查询效率都会极低
• 调整IN查询的批次大小，不要直接用5万的上限值。实测MySQL IN列表的解析和匹配开销在参数超过1万后会陡增，建议测试1000、3000、5000三个批次的耗时，绝大多数场景下5000参数批次的查询效率比5万批次高30%以上
• 每次传入IN列表的参数先做升序排序再传入，有序列表匹配B+树索引时扫描效率更高，能减少索引查找的耗时

方案2 用常量集合JOIN替代IN查询 #

该方案比同参数规模的IN查询效率高20%~50%，根据你的MySQL版本选择对应写法：

MySQL 8.0+ 写法 #

用VALUES语句构造待查常量集合，走等值JOIN匹配：

SELECT t.column
FROM `table` t
JOIN (
    VALUES ROW(value1), ROW(value2), ..., ROW(value5000)
) AS v(check_val) ON t.column = v.check_val

MySQL 5.x 写法 #

用UNION ALL构造待查常量集合：

SELECT t.column
FROM `table` t
JOIN (
    SELECT value1 AS check_val UNION ALL
    SELECT value2 UNION ALL
    ...
    SELECT value5000
) AS v ON t.column = v.check_val

方案3 拆分范围查询降低参数规模 #

如果待查值是数值型、时间型或可排序的字符串类型，可以先将所有待查值排序，拆分出连续区间用范围查询替代IN参数，比如连续的1~1000的数值直接用column BETWEEN 1 AND 1000，仅保留离散的非连续值走参数匹配，能大幅减少单次查询的参数数量。

方案4 本地缓存减少重复查询 #

本地维护一个哈希集合存储已经确认匹配存在的值，每次生成查询批次前先过滤掉已经查到的结果，避免重复传入数据库查询，降低无效查询开销。

内容的提问来源于stack exchange，提问作者Dylan