MySQL查询优化：9.94亿条记录的NOT IN子句改写方案咨询

针对你提到的tableA有9.94亿条记录的场景，这里提供两种NOT IN子句的改写方案，同时附上大表场景下的优化建议：

原始查询语句 #

SELECT DISTINCT
    a.Cola1 AS Col1
FROM
    db.tableA a
        INNER JOIN
    db.tableB b ON a.cola2 = b.colb1
        INNER JOIN
    db.tableC c ON b.colb2 = c.colc1
WHERE
    (c.colc2 = 2 OR (c.colc2 = 7047 AND a.cola3 = '04250'))
        AND a.Col1 NOT IN (334394 , 334223, 334222, 305669)
ORDER BY a.Col1

改写方案1：使用多条件不等于组合 #

因为NOT IN的是固定的少量数值，可以直接拆分为多个<>条件的逻辑与，这种写法在MySQL中执行效率稳定，尤其是当a.Col1存在索引时：

SELECT DISTINCT
    a.Cola1 AS Col1
FROM
    db.tableA a
INNER JOIN db.tableB b ON a.cola2 = b.colb1
INNER JOIN db.tableC c ON b.colb2 = c.colc1
WHERE
    (c.colc2 = 2 OR (c.colc2 = 7047 AND a.cola3 = '04250'))
    AND a.Col1 <> 334394
    AND a.Col1 <> 334223
    AND a.Col1 <> 334222
    AND a.Col1 <> 305669
ORDER BY a.Col1

改写方案2：LEFT JOIN + IS NULL #

将需要排除的数值放到派生表中，通过左连接后筛选未匹配的记录，适合后续排除数值可能扩展的场景：

SELECT DISTINCT
    a.Cola1 AS Col1
FROM
    db.tableA a
INNER JOIN db.tableB b ON a.cola2 = b.colb1
INNER JOIN db.tableC c ON b.colb2 = c.colc1
LEFT JOIN (
    SELECT 334394 AS val UNION ALL
    SELECT 334223 UNION ALL
    SELECT 334222 UNION ALL
    SELECT 305669
) excluded ON a.Col1 = excluded.val
WHERE
    (c.colc2 = 2 OR (c.colc2 = 7047 AND a.cola3 = '04250'))
    AND excluded.val IS NULL
ORDER BY a.Col1

这里用UNION ALL代替UNION，避免不必要的去重操作，提升派生表的生成效率。

大表场景额外优化建议 #

由于tableA数据量极大，索引优化是关键：

• 给tableA创建联合索引(cola2, cola3, Col1)，覆盖JOIN、WHERE筛选和SELECT的字段，避免回表查询。
• 给tableB的colb1、colb2分别创建索引，tableC创建联合索引(colc1, colc2)，加速多表JOIN和条件筛选。
• 如果DISTINCT和ORDER BY的开销过大，可以考虑利用索引的有序性来减少排序和去重的资源消耗，比如让Col1作为联合索引的最后一列，利用索引排序。

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