带动态过滤的通用查询（含EXISTS）性能担忧：是否应改用动态SQL？

当前查询的性能隐患 #

你现在用的这种带OR和NULL判断的静态SQL，最大问题是数据库优化器很难生成最优执行计划：

• 因为参数可能为NULL也可能有值，优化器需要生成适配所有参数组合的通用计划，这往往会牺牲针对性。比如当:param1为NULL时，t.example = :param1是无效条件，但优化器可能还是会扫描example字段的索引，或者选择低效的表扫描。
• 外层的OR条件会干扰EXISTS子句的优化：当:param2不为NULL时，本来可以通过other_table(test, id)的复合索引快速定位关联数据，但因为:param2 IS NULL的存在，优化器可能会放弃最优索引，或者选择不合适的表连接方式，拖慢查询速度。

是否应该优先用动态SQL？ #

多数场景下，优先选择动态SQL是更优的方案，原因如下：

• 动态SQL可以根据参数是否为NULL，只生成实际需要的过滤逻辑，让SQL语句更“干净”。优化器能针对具体的条件组合生成精准的执行计划，充分利用索引。
• 举几个参数组合对应的动态SQL示例：
  • 当:param1和:param2都有值时：

    SELECT *
    FROM table t
    WHERE t.example = :param1
      AND EXISTS (
          SELECT 1
          FROM other_table o
          WHERE o.id = t.other_id
            AND o.test = :param2
        )
    

  • 当:param1为NULL，:param2有值时：

    SELECT *
    FROM table t
    WHERE EXISTS (
        SELECT 1
        FROM other_table o
        WHERE o.id = t.other_id
          AND o.test = :param2
      )
    

  • 当:param2为NULL，:param1有值时：

    SELECT *
    FROM table t
    WHERE t.example = :param1
    

• 每个场景的SQL都没有冗余条件，优化器可以精准选择索引、连接方式，性能会更稳定。

动态SQL的注意事项 #

• 必须防范SQL注入：所有参数都用绑定变量传递，绝对不要直接把参数值拼接到SQL字符串里。现在主流的ORM框架或者数据库驱动都支持参数化的动态SQL，不用自己手动拼接。
• 如果实在不想用动态SQL，也可以尝试给查询加索引提示，或者改写条件，但这种方式的效果通常不如动态SQL稳定，因为优化器还是要处理条件的不确定性。
• 基础优化不能忘：确保table表有(example, other_id)的复合索引，other_table表有(test, id)的复合索引，不管用哪种写法，合适的索引都是性能的基础。

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