MySql如何使用not in实现优化

最近项目上用select查询时使用到了not in来排除用不到的主键id一开始使用的sql如下： select 　　s.SORT_ID， 　　s.SORT_NAME, 　　s.SORT_STATUS, 　　s.SORT_LOGO_URL, 　　s.SORT_LOGO_URL_LIGHT from SYS_SORT_PROMOTE s 　　WHERE 　　　　s.SORT_NAME = '必听经典' 　　　　AND s.SORT_ID NOT IN ("SORTID001") 　　limit 1; 表中的数据较多时这个sql的执行时间较长、执行效率低，在网上找资料说可以用 left join进 在SQL查询中，`IN` 和 `NOT IN` 子句是经常用于过滤数据的工具。然而，当处理大量数据时，尤其是与子查询结合使用时，这些操作可能导致性能问题。`NOT IN` 子句在某些情况下可能会执行全表扫描，这在大数据集上非常耗时。本文将探讨如何通过优化 `NOT IN` 查询来提高 MySQL 的执行效率。 原始的 SQL 查询如下： ```sql SELECT s.SORT_ID, s.SORT_NAME, s.SORT_STATUS, s.SORT_LOGO_URL, s.SORT_LOGO_URL_LIGHT FROM SYS_SORT_PROMOTE s WHERE s.SORT_NAME = '必听经典' AND s.SORT_ID NOT IN ('SORTID001') LIMIT 1; ``` 这个查询在 `SORT_NAME` 为 '必听经典' 的记录中排除了 `SORT_ID` 为 'SORTID001' 的记录。当表中的数据量较大时，`NOT IN` 子句可能执行得较慢，因为它需要对每一行都进行检查，以确定是否包含在提供的列表中。 为了优化这个查询，我们可以考虑使用 `LEFT JOIN`。这种技术通常比 `NOT IN` 更高效，因为它可以利用索引来加速查询。优化后的 SQL 查询如下： ```sql SELECT s.SORT_ID, s.SORT_NAME, s.SORT_STATUS, s.SORT_LOGO_URL, s.SORT_LOGO_URL_LIGHT FROM SYS_SORT_PROMOTE s LEFT JOIN ( SELECT SORT_ID FROM SYS_SORT_PROMOTE WHERE SORT_ID = 'SORTID001' ) b ON s.SORT_ID = b.SORT_ID WHERE b.SORT_ID IS NULL AND s.SORT_NAME = '必听经典' LIMIT 1; ``` 在这个优化后的查询中，我们创建了一个临时表 `b` 来存储需要排除的 `SORT_ID` 值，然后通过 `LEFT JOIN` 将 `SYS_SORT_PROMOTE` 表与这个临时表连接。由于 `LEFT JOIN` 会返回所有左侧表（`s`）的记录，即使右侧表（`b`）没有匹配项，所以在 `WHERE` 子句中添加 `b.SORT_ID IS NULL` 可以筛选出那些在 `b` 表中不存在的记录，即我们要找的那些不包含在排除列表中的记录。 这种方法的关键在于，如果 `SORT_ID` 已经被索引，那么 `LEFT JOIN` 将能够更有效地查找不匹配的记录，因为它可以直接利用索引进行查找，而无需遍历整个表。 需要注意的是，对于 `NULL` 值的处理，`NOT IN` 和 `LEFT JOIN` 方法可能会有所不同。在 `NOT IN` 中，如果被比较的列有 `NULL` 值，`NULL` 不会被视为 `IN` 或 `NOT IN` 的一部分，这可能导致预期之外的结果。而在 `LEFT JOIN` 中，`NULL` 值将被视为不匹配，因此在使用 `LEFT JOIN` 时，要确保正确处理这种情况。 此外，如果你的数据库版本是 MySQL 5.7 或更高，可能存在对 `GROUP BY` 语句的限制，要求所有的非聚合列都出现在 `GROUP BY` 子句中。这可能会对包含 `NOT IN` 的查询产生影响，需要根据具体情况进行调整。 通过使用 `LEFT JOIN` 替换 `NOT IN` 是一种常见的查询优化策略，特别是在处理大量数据时。但也要注意，优化查询不仅仅是改变语法，还需要考虑实际的数据分布、索引的存在和选择以及数据库的特定配置。在实际应用中，应始终结合 EXPLAIN 分析和性能测试来评估和实施任何优化措施。