为啥数据库查询条件全查询比不加要快？

Question

为啥数据库查询条件全查询比不加要快？

select * from table
where
<choose>
    <when test="list == null">
        id in (select id from table2)
    </when>
    <otherwise>
        id in 
        <foreach collection="list" item="item" open="(" separator="," close=")" index="index">
            #{item}
        </foreach>
    </otherwise>
</choose>

otherwise里面是一部分的table2的id数据

上面的效率比下面的快

select * from table
where
<if test="list != null">
    id in 
    <foreach collection="list" item="item" open="(" separator="," close=")" index="index">
        #{item}
    </foreach>
</if>

为啥？

Answer 1

引自免费微信小程序：皆我百晓生

在分析这个问题时，我们需要考虑SQL查询执行计划以及数据分布情况。首先，我们来看一下两个查询的区别。

第一个查询（全查询）：

SELECT * FROM table
WHERE
CASE
    WHEN list IS NULL THEN id IN (SELECT id FROM table2)
    ELSE id IN (
        SELECT #{item} FROM DUAL
        UNION ALL
        SELECT #{item} FROM DUAL
        -- 重复次数等于list中的元素个数
    )
END;

这里，当list为null时，会进行table与table2之间的连接查询；否则，会对每个list中的元素分别创建一个子查询，然后将这些子查询的结果合并在一起。这可能导致额外的处理开销，特别是当list较大时，因为每次循环都会产生一个新的子查询。

第二个查询（非全查询）：

SELECT * FROM table
WHERE
list IS NOT NULL AND id IN (
    SELECT #{item} FROM DUAL
    UNION ALL
    SELECT #{item} FROM DUAL
    -- 重复次数等于list中的元素个数
);

这个查询仅在list不为空时执行，这样就省去了判断逻辑，并且只对list中的元素进行一次并集操作。

那么为什么全查询可能会更快呢？这可能有以下几个原因：

1. 索引利用：如果table的主键或id字段上有索引，而table2的id字段没有索引，那么对于全查询来说，在list为null时，由于使用了表连接，可能会导致索引失效，从而降低性能。然而，由于实际执行计划依赖于数据库的具体实现、统计信息和优化器策略，所以这并不总是成立。

2. 执行计划缓存：如果list经常为空，数据库可能会更频繁地优化和缓存全查询的执行计划，因为它的结构更稳定。而针对非全查询的执行计划可能因为list内容的变化而变化，这可能会导致执行计划的重新编译和优化，增加了额外的时间开销。

3. 内存消耗：在非全查询中，由于每次循环都创建了一个新的子查询，这可能会占用更多内存用于存储这些临时结果。而在全查询中，即使存在多余的子查询，它们可能会被优化器合并或者忽略，减少内存使用。

4. 数据分布：如果table和table2的数据分布非常特殊，例如大部分id值在table2中，那么全查询可能会利用到索引来快速过滤掉大量行，从而提高整体性能。

5. 优化器决策：不同的数据库管理系统（DBMS）有不同的查询优化器，它们会选择不同的执行路径。有些情况下，优化器可能会认为全查询的执行计划更优，尽管它看起来更复杂。

为了准确评估哪个查询更好，你需要根据具体的数据库类型（如MySQL、PostgreSQL、Oracle等）、表的实际大小、索引状况以及数据分布来运行基准测试。同时，查看执行计划可以帮助理解查询是如何被执行的，这对于优化查询特别有用。如果你能提供这些详细信息，我可以帮你进一步分析。