SQL性能调优：1.1 一般性语法调优

from http://www.beyondrails.com/blogs/42

遵守以下准则：
1，查询条件的左边应该是简单的column名，右边则应该是很容易查询的值
所有的DBMS都会把5 = column1转换为column1 = 5

2，查询条件的每个组件都有一个point count，查询速度越快，花的时间越少，points越高

操作符、操作数                           points
=                                          10
>                                           5
>=                                          5
<                                           5
<=                                          5
LIKE                                        3
<>                                          0
Literal alone                              10
Column alone                                5
Parameter alone                             5
Multioperand expression                     3
Exact numeric data type                     2
Other numeric data type                     1
Temporal data type                          1
Character data type                         0
NULL                                        0

例子

... WHERE smallint_column = 12345

points为27:
1，Column alone(smallint_column) = 5
2，Exact numeric data type(smallint_column) = 2
3，等号 = 10
4，Literal alone(12345) = 10

3，对于多个表达式条件通过优化顺序来提高速度
优化前：

SELECT * FROM Table1
  WHERE column4 < 117
    AND column4 > column5
    AND column3 = 'Smith'
    AND column1 = 5
    AND column2 = 77.3

优化后：

SELECT * FROM Table1
  WHERE column1 = 5
    AND column2 = 77.3
    AND column3 = 'Smith'
    AND column4 < 117
    AND column4 > column5

4，利用Law of Transitivity和Constant propagation的概念，使用字面量来替换column名和column表达式而不改变表达式的含义来提高速度
优化前：

SELECT * FROM Table1
  WHERE column1 = 5 AND
    NOT (column3 = 7 OR column1 = column2)

优化后：

SELECT * FROM Table1
  WHERE column1 = 5
    AND column3 <> 7
    AND column2 <> 5

5，删除那些永远不会执行的代码

SELECT * FROM Table1
  WHERE 0 = 1
    AND column1 = 'I hope we never execute this'

6，有些DBMS不会对大部分obvious-looking表达式做“折叠”，记住一点“更改代码之前先理解它的含义”

... WHERE column1 + 0

... WHERE 5 + 0.0

... WHERE column1 IN (1, 3, 3)

... CAST(1 AS INTEGER)

... WHERE 'a' || 'b'

7，如果代码包括一些显然的算术表达式，则用计算后的值来替换。
优化前：

... WHERE a - 3 = 5

优化后：

... WHERE a = 8

8，尽量避免在column里引入function
优化前：

SELECT * FROM Table1
  WHERE date_column = CURRENT_DATE
    AND amount * 5 > 100.00

优化后：

SELECT * FROM Table1
  WHERE date_column = DATE '2002-01-01'
    AND amount * 5 > 100.00

9，不要使用UPPER来做大小写不敏感的匹配，使用LOWER
优化前：

... WHERE UPPER(column1) = 'résumé'

优化后：

... WHERE LOWER(column1) = 'résumé'

评论

3 楼 jzcsl700 2008-08-28  

对于oracle来说，好象8和9是有区别的（我也是看到网上有些人说，没有具体说明）。楼主能否介绍下啊

2 楼 hideto 2008-07-30  

为什么没有科学依据？
假设数据库是按从左到右的方式解析表达式，
AND左边如果返回false，则AND右边就不要计算了，这就是所谓的“短路”

1 楼 jzcsl700 2008-07-30  

这些没有没科学依据啊

还有
1，一般数据库按从左到右方式解析表达式(Oracle是从右到左)，对AND连接的表达式优化顺序，将更小可能发生的表达式放在前面
优化前：
Java代码 
1. ... WHERE column1 = 'A' AND column2 = 'B' 

优化后：
Java代码 
1. ... WHERE column2 = 'B' AND column1 = 'A' 
2. （assuming column2 = 'B' is less likely） 


对于oracle来说应该就是这样吧？
WHERE column1 = 'A' AND column2 = 'B' 
（assuming column2 = 'B' is less likely）