为什么不要使用IN而要使用EXISTS

今天发现SQL语句执行速度过慢，然后我就认真看了看，用EXPLAIN看了一下

SQL

SELECT
    count(*)
FROM
    student_info AS s
LEFT JOIN class ON s.class_id = class.id
LEFT JOIN major AS m ON class.major_id = m.id
LEFT JOIN department AS d ON m.department_id = d.id
LEFT JOIN college AS c ON d.college_id = c.id
WHERE
    s.student_number NOT IN (
        SELECT
            b.student_id
        FROM
            bed AS b
        WHERE
            b.student_id IS NOT NULL
        ORDER BY
            b.student_id
    )

EXPLAIN

我发现这样SQL的执行速度太慢了，我就想着有什么办法可以优化。

网上那些count(1)、count(属性)，我都试过了，感觉变化不大

这个时候，我的师兄就给了一个建议，你不要使用IN，你使用EXIST，我之前就已经知道IN的性能要差于EXIST，但是心理一直没有一个概念，我就听从师兄的建议，将NOT IN转换为 NOT EXISTS，然后我又EXPLAIN一下。

SQL

SELECT
    count(*)
FROM
    student_info AS s
LEFT JOIN class ON s.class_id = class.id
LEFT JOIN major AS m ON class.major_id = m.id
LEFT JOIN department AS d ON m.department_id = d.id
LEFT JOIN college AS c ON d.college_id = c.id
WHERE
    NOT EXISTS (
        SELECT
	    b.student_id
	FROM
	    bed AS b
	WHERE
	    b.student_id IS NOT NULL
	AND b.student_id = s.student_number
	ORDER BY
	    b.student_id
    )

EXPLAIN

通过对比我们就发现，bed的索引，从rang变成ref，我又去百度了一下，他们两个之间的差距。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

震惊吧，他们之间相差这么多个量级。

然后他们之间的差距我们可以通过定义看出来

rang：range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及'>','<'外，in和or也是索引范围扫描。

ref:出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

你们知道了吧，IN是走rang，就是最差的索引=没有索引，而EXISTS走的ref，已经算是比较好的检索方式了。

记住了，以后不要再使用IN，要使用EXISTS了