mysql - 在where子句中使用case语句更好还是构建动态查询更好

Question

我正在构建一个查询来搜索连接具有数百万条记录的多个表的结果。 db是mysql。

虽然我的表连接条件始终保持不变，但我的 where 子句语句实际上取决于用户从 UI 选择的参数。

案例1:

   WHERE table.column1 = something 
   AND
   table.column2 = CASE WHEN condition1 = 0 THEN table.column 
                          ELSE condition1 END 

案例2:构造一个动态查询，根据搜索条件附加 AND 条件。

案例 1 看起来需要更多时间。你们中的任何人都可以建议采取哪一种方法，或者是否有其他更好的方法来应对本地的动态条件。

感谢您的帮助。

Answer 1

典型的逻辑不使用case，只是 bool 逻辑:

WHERE table.column1 = something  AND
      (@condition1 = 0 OR table.column2 = @condition1)

(对于“全部”场景，可能经常使用 IS NULL 代替 = 0。)

这比动态 SQL 好还是坏？基本上，这是一个关于性能的问题。如果您仅在 (column1) 上有索引，那么它将用于动态查询或使用 bool (或 case)逻辑的查询。没关系。

但是，如果您在(column1，column2)上有索引，那么上面的代码可能不会使用该索引。使用动态 SQL 的等效版本将使用索引，因此当您不选择所有值时，查询的执行会更快。

针对这一点，您需要平衡编译查询。这通常需要一些时间，但不会太多。如果毫秒很重要，那么编译就很重要。如果几十秒很重要，那么可能不会。

因此，您需要平衡不同的事情。动态 SQL 成为性能更好的选项并不罕见，特别是在存在多个条件时。