sql - 在 WHERE 子句中使用可选条件的正确方法

Question

我在 WHERE 中有一个可选条件SQL 查询中的子句。如果参数opt_y由用户提供，条件cond2 <> opt_y被检查，否则它被跳过。另一个条件 ( cond1 = x ) 保持不变。

在以下方法中，哪个更高效 & 安全(避免SQL注入(inject))。我正在使用 Django 原始查询管理器来运行我的 SQL(我知道性能取决于具体情况，但如果有一些明显的缺陷，比如数据库引擎无法优化特定技术或无法有效使用缓存，请突出显示).当然，如果有更好的方法，欢迎分享。

预期查询

MyTable.objects.raw("""
  SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
  FROM mytable
  WHERE cond1 = %s AND cond2 <> %s """, [x, opt_y])
)

方法一:CASE语句

MyTable.objects.raw("""
  SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
  FROM mytable
  WHERE cond1 = %s AND 
    CASE WHEN %s IS NULL THEN true ELSE cond2 <> %s END""", [x, opt_y, opt_y])
)

方法二:组合字符串

query  = """SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
            FROM mytable
            WHERE cond1 = %s """
params = [x]
if opt_y:
    q += """AND cond2 <> %s"""
    params.append(opt_y)
MyTable.objects.raw(query, params)

方法三:逻辑运算

MyTable.objects.raw("""
  SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
  FROM mytable
  WHERE cond1 = %s AND (%s IS NULL OR cond2 <> %s)""", [x, opt_y, opt_y])
)

更新，增加1个方法:

方法 4:这更像是一种 hack

if not opt_y:
    opt_y = -1 #Or some value that will NEVER appear in a column 

MyTable.objects.raw("""
  SELECT id, RANK() OVER (PARTITION BY name ORDER BY age)
  FROM mytable
  WHERE cond1 = %s AND cond2 <> %s""", [x, opt_y])
)

使用 Django 1.6 和 PostgreSQL 9.3

Answer 1

如果您不介意冗长的话，CASE 或逻辑操作很好 - 但是，如果您使用服务器端，它们将不会被优化准备好的陈述，因此它们可能会导致非最佳计划选择。

如果您使用客户端参数化查询，客户端驱动程序替换参数，它们就没问题。您可以通过查看 PostgreSQL 查询日志来判断您正在使用哪个 - 如果它记录您的语句，例如:

... WHERE $1 = 'fred' AND ...

那么您正在使用服务器端参数绑定(bind)。

因此，遗憾的是，将谓词添加到 SQL 字符串并将额外的参数添加到查询参数列表可能是许多应用程序最有效的方法。这是 SQL IMO 中最可怕的事情之一。

---

如果您对 PostgreSQL 的核心内容不感兴趣，请立即停止阅读。

如果您对如何判断特定结构是否被优化感到好奇，您可以检查 PostgreSQL 的低级查询解析树、重写和查询计划结构。如果您对优化器效果感兴趣，查询计划结构就是您想要的。

假设我对表格是否感兴趣:

                                 Table "public.manufacturers"
 Column  |         Type          |                         Modifiers                          
---------+-----------------------+------------------------------------------------------------
 id      | integer               | not null default nextval('manufacturers_id_seq'::regclass)
 name    | character varying(30) | not null
 country | character varying(40) | 
Indexes:
    "manufacturers_pkey" PRIMARY KEY, btree (id)
    "manufacturers_name_key" UNIQUE CONSTRAINT, btree (name)

一个(相当愚蠢的)查询，例如:

select * from manufacturers 
where case when null is null then true else id = id end
order by id;

是否优化了 CASE。我会:

SET debug_print_plan = on;
SET client_min_messages = debug1;
select * from manufacturers 
    where case when null is null then true else id = id end
    order by id;

和psql会打印:

LOG:  statement: select * from manufacturers where case when null is null then true else id = id end order by id;
LOG:  plan:
DETAIL:     {PLANNEDSTMT 
   :commandType 1 
   :queryId 0 
   :hasReturning false 
   :hasModifyingCTE false 
   :canSetTag true 
   :transientPlan false 
   :planTree 
      {INDEXSCAN 
      :startup_cost 0.15 
      :total_cost 53.85 
      :plan_rows 380 
      :plan_width 180 
      :targetlist (
         {TARGETENTRY 
         :expr 
            {VAR 
            :varno 1 
            :varattno 1 
            :vartype 23 
            :vartypmod -1 
            :varcollid 0 
            :varlevelsup 0 
            :varnoold 1 
            :varoattno 1 
            :location 7
            }
         :resno 1 
         :resname id 
         :ressortgroupref 1 
         :resorigtbl 104875 
         :resorigcol 1 
         :resjunk false
         }
         {TARGETENTRY 
         :expr 
            {VAR 
            :varno 1 
            :varattno 2 
            :vartype 1043 
            :vartypmod 34 
            :varcollid 100 
            :varlevelsup 0 
            :varnoold 1 
            :varoattno 2 
            :location 7
            }
         :resno 2 
         :resname name 
         :ressortgroupref 0 
         :resorigtbl 104875 
         :resorigcol 2 
         :resjunk false
         }
         {TARGETENTRY 
         :expr 
            {VAR 
            :varno 1 
            :varattno 3 
            :vartype 1043 
            :vartypmod 44 
            :varcollid 100 
            :varlevelsup 0 
            :varnoold 1 
            :varoattno 3 
            :location 7
            }
         :resno 3 
         :resname country 
         :ressortgroupref 0 
         :resorigtbl 104875 
         :resorigcol 3 
         :resjunk false
         }
      )
      :qual <> 
      :lefttree <> 
      :righttree <> 
      :initPlan <> 
      :extParam (b)
      :allParam (b)
      :scanrelid 1 
      :indexid 104879 
      :indexqual <> 
      :indexqualorig <> 
      :indexorderby <> 
      :indexorderbyorig <> 
      :indexorderdir 1
      }
   :rtable (
      {RTE 
      :alias <> 
      :eref 
         {ALIAS 
         :aliasname manufacturers 
         :colnames ("id" "name" "country")
         }
      :rtekind 0 
      :relid 104875 
      :relkind r 
      :lateral false 
      :inh false 
      :inFromCl true 
      :requiredPerms 2 
      :checkAsUser 0 
      :selectedCols (b 9 10 11)
      :modifiedCols (b)
      }
   )
   :resultRelations <> 
   :utilityStmt <> 
   :subplans <> 
   :rewindPlanIDs (b)
   :rowMarks <> 
   :relationOids (o 104875)
   :invalItems <> 
   :nParamExec 0
   }

阅读计划树需要一些实践和对 PostgreSQL 内部结构的理解。如果其中大部分内容毫无意义，请不要紧张。此处主要关注的是用于读取表的索引扫描的 qual(限定符或 where 子句)是空的。 PostgreSQL 不仅优化了 CASE，它注意到 id = id 始终为 true 并完全优化了 where 子句。