我的查询有多糟糕？

Question

好的，我需要基于一些用户输入构建查询以筛选结果。

查询基本上是这样的：

SELECT * FROM my_table ORDER BY ordering_fld;

有四个文本框，用户可以选择过滤数据，这意味着我必须动态构建一个"WHERE"子句来处理第一个使用的过滤器，然后对于每个后续输入的过滤器都要加上"AND"子句。

因为我太懒了，所以我将每个过滤器都变成了"AND"子句，并默认在查询中加入了一个"WHERE 1"子句。

现在我的查询语句看起来是这样的：

SELECT * FROM my_table WHERE 1 {AND filters} ORDER BY ordering_fld;

所以我的问题是，我是否做了什么会对我的查询性能产生不利影响或者破坏了其他方面让我应该担心的事情？

- Evernoob

---

这句话的意思是：“我的问题看起来很大吗？” - Question Mark

是只有我还是Evernoob非常勇敢？我从不想问一个充满其他开发者（众所周知他们很有意见）的网站我的代码有多糟糕？ - Justin Niessner

我假设 "1" 代表真？就我个人而言，我认为这相当简洁明了，前提是您先验证数据。 - PowerUser

一个警告，WHERE 1不是ANSI标准，因此在所有数据库管理系统上都无法工作，应该写成WHERE 1 = 1。 - Chris Chilvers

哥们，你真的很懒啊！我很惊讶你居然还有心情发帖子！;-) - NeedHack

@Chris Needham：这被称为理性好奇，而不是懒惰。如果每个数据库开发人员都在关注他们的数据库如何工作时问问题，我们早就会拥有治愈癌症、建立月球殖民地和利用氢能源的技术了。 - Quassnoi

Answer 1

MySQL会优化掉你的1。

我刚在我的测试数据库上运行了这个查询：

EXPLAIN EXTENDED
SELECT  *
FROM    t_source
WHERE   1 AND id < 100

我得到了以下描述：

select `test`.`t_source`.`id` AS `id`,`test`.`t_source`.`value` AS `value`,`test`.`t_source`.`val` AS `val`,`test`.`t_source`.`nid` AS `nid` from `test`.`t_source` where (`test`.`t_source`.`id` < 100)

你可以看到，没有1。

MySQL的WHERE语句优化文档提到了以下内容：

• 常量折叠（Constant folding）：

  (a<b AND b=c) AND a=5
  -> b>5 AND b=c AND a=5
  

• 常量条件删除（由于常量折叠而需要）：

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

请注意上面的示例中有5 = 5和5 = 6这两个部分。

- Quassnoi

---

尝试使用EXPLAIN并查阅文档，以进行测试。+1 - knittl

如果每个开发者都使用EXPLAIN并查阅文档，世界将会变得更美好！ - Quassnoi

@quassnoi：本来可以的，但现在不行了 ;) - knittl

Answer 2

你可以通过使用EXPLAIN命令解释你的查询：
http://dev.mysql.com/doc/refman/5.0/en/explain.html

并查看是否有任何不同，但我认为不会有。我建议使用1=1，这样更加清晰。

当没有使用参数且表格变得很大时，你可能需要添加LIMIT 1000或其他内容，你真的想要返回所有内容吗？

- KM.

---

根据标签，这应该是“LIMIT 1000” :) - Quassnoi

@Quassnoi，谢谢，我一开始回答时想的是MySQL，但当我编辑并添加了_TOP 1000_部分时，我想到了SQL Server。 - KM.

Answer 3

WHERE 1 是一个常量、确定性表达式，任何优秀的数据库引擎都会将其“优化掉”。

- harpo

Answer 4

如果你在所选语言中有一种好的方法可以避免自己构建SQL，请使用该方法。我喜欢Python和Django，Django ORM使得根据用户输入过滤结果非常容易。

如果你坚持自己构建SQL，请确保对用户输入进行SQL注入防护，并尝试将SQL构建封装在与筛选逻辑分离的单独模块中。

此外，在查询性能成为问题之前，不应考虑查询性能，这可能不会发生，直到你有数千或数百万行数据。当优化时间到来时，只需在用于WHERE和JOIN的列上添加一些索引即可大有裨益。

- Christian Oudard

Answer 5

为了提高性能，请在“WHERE”中列出的字段上使用列索引。

- kolypto

Answer 6

我们最近也在做类似的事情，观察到以下几点：

• 在我们（可能）过滤的列上设置索引可以提高性能
• 如果没有使用筛选器，则WHERE 1部分可以完全省略。（不确定是否适用于您的情况）没有区别，但是感觉“正确”。
• 不能忘记SQL注入

此外，如果您仅有4个筛选器，则可以建立一个存储过程，并传递null值并检查它们（就像n8wrl在此期间建议的那样）。

- Adam Vigh

---

我不太担心 SQL 注入问题，我已经处理好了。更多的是我只是对我所做的事情是否可接受感到好奇。 - Evernoob

Answer 7

这个方法可以实现 - 但需要考虑以下几点：

关于动态构建SQL，一些数据库（至少Oracle）会缓存查询的执行计划，所以如果你多次运行相同的查询，它不必从头开始完全重新启动。如果你使用动态构建SQL，则每次都创建一个不同的查询，因此对于数据库来说，它看起来像是100个不同的查询而不是100次运行相同的查询。

你可能只需要测量性能，以确定它是否足够好。

你需要所有的列吗？显式指定它们可能比使用*更好，因为：

• 你可以直观地看到返回了哪些列
• 如果以后向表中添加或删除列，它们不会改变你的接口

- Shizzmo

---

Oracle根据提交的查询内容而不是用户运行特定函数或过程的事实来检查缓存。 - OMG Ponies

Answer 8

不错，我不知道这个代码片段可以解决“这是第三个过滤器吗？”的问题。

虽然你应该为你的代码感到羞耻（^^），但它对性能没有任何影响，因为任何数据库引擎都会优化它。

- Clement Herreman

---

哈哈，虽然我的代码可能有些不足，但我为什么要感到羞耻呢？如果数据库引擎能够优化它，那么对于数据库来说就没有任何伤害了，而且我的业务逻辑现在更加清晰易读，方便下一个人查看。那么，这里哪里会有羞耻呢？ - Evernoob

因为你添加了“1=1”，这是无用的代码，这是不好的...无害但不好。但每个人都这样做^^ - Clement Herreman

Answer 9

标准的SQL注入免责声明在此...

为了避免SQL注入攻击，您可以使用存储过程，在其中传递字段的值或NULL值，因为您知道只有四个参数。我不确定mySQL存储过程语法，但查询将简化为：

SELECT *
  FROM my_table
 WHERE Field1 = ISNULL(@Field1, Field1)
   AND Field2 = ISNULL(@Field2, Field2)
   ...
 ORDRE BY ordering_fld

- n8wrl

---

这往往会阻止在field1等字段上使用索引，应该使用COALESCE而不是ISNULL。 - Chris Chilvers

正确的格式应该是 @Field IS NULL OR t.col = @Field。否则就会浪费索引查找或表扫描。 - OMG Ponies

Answer 10

我使用WHERE 1 = 1的唯一原因是为了动态SQL。它是一种通过使用AND ...来更轻松地添加WHERE子句的hack。否则，我不会在我的SQL中包含它——它对查询总体没有任何影响，因为它始终计算为真，并且不会命中涉及的表，因此没有基于它的索引查找或表扫描。

我不清楚MySQL如何处理可选条件，但我知道使用以下内容：

WHERE (@param IS NULL OR t.column = @param)

以下是处理可选参数的典型方式。COALESCE和ISNULL并不理想，因为查询仍然基于哨兵值利用索引（或更糟的情况下，表扫描）。我提供的示例只有在提供了值时才会访问表。

尽管如此，我的Oracle经验（9i、10g）表明它无法很好地处理[WHERE (@param IS NULL OR t.column = @param)]。通过将SQL转换为动态的，并使用CONTEXT变量来确定要添加什么，我看到了巨大的性能提升。我对SQL Server 2005的印象是它们处理得更好。

- OMG Ponies