SQL语句优化笔记

SQL语句优化笔记  1. 避免使用OR操作
在多数情形下，如果SQL语句的WHERE子句中包含了OR，那么SQL可能就不会使用到索引。可以试着用两种方式来代替OR操作，1）用IN 2）用UNION
例如：
SELECT *
FROM TEST A
WHERE A.NO = 9 OR A.NO=23 OR A.NO=21
就算在NO列上建立了索引，上面的SQL也不会使用到索引。可以使用IN的方式改写。
SELECT *
FROM TEST A
WHERE A.NO IN(9,23,21)
这时SQL则可能使用到索引

对于下面的例子
SELECT *
FROM TEST A
WHERE A.JOINEDYEAR=2007 OR A.CITY='NANJING'
在这种情况下，SQL会采用顺序处理策略，而不管JOINEDYEAE、CITY列上是否有索引。该语句也不用使用IN运算符来代替，但可以用UNION代替
SELECT *
FROM TEST
WHERE JOINEDYEAR=2007
UNION
SELECT *
FORM TEST
WHERE CITY='NANJING'

2. 避免UNION运算符的不必要使用
上面建议使用UNION来代替OR操作，但并不意味UNION总是高效，使用时需要仔细思考
例：计算每场比赛的所赢局数与所输局数之差
SELECT MATCHNO, WON - LOST
FROM MATHES
WHERE WON >= LOST
UNION
SELECT MATCHNO, LOST - WON
FROM MATCHES
WHERE WON < LOST
这条SQL将导致浏览整个MATCHES表两次，通过使用函数可以避免这种情况
SELECT MATCHNO, ABS(WON-LOST)
FROM   MATCHES
这时SQL仅浏览MATCHES表一次，买行执行一次计算。执行计算所导致的额外处理时间可由仅浏览一次表所节约的时间补偿。

3. 避免使用NOT运算符
如果在WHERE从句中包含NOT运算符，那么SQL一般不会使用索引。尽可能的使用比较运算符来代替NOT运算符

4. 隔离条件中的列
如果一个定义了索引的列出现在一个计算或标量函数的列上，则不会用到索引
例：
SELECT *
FROM TEST
WHERE NUMBER + 10 = 3500
在比较运算符=的左侧，有一个包含列名和一个常量的表达式，而在比较运算符=的右侧则是一个常量。在这种情况下不会使用NUMBER列上的索引。可以替换成如下形式
SELECT *
FROM TEST
WHERE NUMBER = 3400
这时比较运算符=左侧的表达式仅包含一个列名。也就是说列被隔离了。

5. 使用BETWEEN运算符
在使用AND运算符查看某个特殊范围的值的WHERE从句的条件，SQL一般不会使用索引，这种情况下可以使用BETWEEN运算符来代替用AND描述的条件。
例：
SELECT *
FROM TEST
WHERE DATE >= '1980-09-12' AND DATE <= '1985-12-12'
这样写可能导致用不到DATE列上建立的索引，建议使用如下形式
SELECT *
FROM DATE BETWEEN   '1980-09-12' AND '1985-12-12'

6. 避免LIKE运算符的特殊形式
如果LIKE运算符以一个百分号或一个下划线开始，则不能使用到索引。但有时必须使用这种方式，并没有其他的行之有效的解决方案。

7. 增加冗余条件
有时通过向WHERE从句增加冗余条件，可以加快SQL的处理速度。
例：
SELECT NO, NAME
FROM TEST1 A, TEST2 B
WHERE A.NO = B.NO AND A.NO = 9001
有时增加一个冗余条件可以SQL提高效率
SELECT NO, NAME
FROM TEST1 A, TEST2 B
WHERE A.NO = B.NO AND A.NO = 9001 AND   B.NO = 9001

8. 尽量避免HAVING从句
SQL中可以在两个地方指定条件，一是在WHERE从句中，一是在HAVING从句中。尽可能的在WHERE从句中指定条件。原因是索引不能用于在HAVING从句中指定的条件。
例：
SELECT *
FROM TEST
GROUP BY NO
HAVING NO>=9001
修改为
SELECT *
FROM TEST
WHERE NO >= 9001
GROUP BY NO

9. 尽可能是SELECT 从句小一些
SELECT 从句表达将被显示的列，要避免显示不必要的列，这会影响性能。如果子查询使用EXISTS运算符链接到主查询，但是，该SELECT语句的最终结果不受指定的表达式的影响，则建议在SELECT从句中由一个常量组成的唯一表达式，例如：
SELECT A.NO, A.NAME
FROM TEST1 A
WHERE EXISTS (
SELECT '1'
FROM TEST2
WHERE A.NO = B.NO
)

10. 尽量避免DISTINCT
DISTINCT在结果中删除重复行的同时也给SQL的处理时间带来了负面影响，所以除非必要，否则尽量不用DISTINCT

11. 尽可能使用集合运算符的ALL选项
集合运算符UNION、INTERSECT、EXCEPT的ALL选项的作用是不删除重复行，如果没有指定ALL选择，则必须对所有的行进行排序，以便能够删除重复的行（排序在幕后发生）
例：
SELECT NAME, INITIALS
FROM   PLAYERS
WHERE TOWN = 'STRAT'
UNION ALL
SELECT NAME, INITIALS
WHERE TOWN = 'DOUG'
这里用了关键字ALL，所以SQL将不执行排序来删除可能重复的行。但是思考一下：该结果不会返回重复的行的，因为每个运动员仅可能住在一个城镇。因此，在该例中，如果没有使用ALL关键字，则排序总是被不必要的执行，这会导致性能下降

12. 在可能的情况下，尽量使用外连接而不是UNION运算符
例：对每个运动员，给出其号码、名称、即罚款
SELECT A.PLAYERNO, NAME, AMOUNT
FROM   PLAYERS A, PENALTIES B
WHERE A.PLAYERNO = B.PLAYERNO
UNION
SELECT C.PLAYERNO, NAME, NULL
FROM PLAYERS A
WHERE PLAYERNO NOT IN(
SELECT PLAYERNO
FROM PENALTIES
)
这条SQL写的比较复杂，运行的也会比较慢，例如PLAYERS被访问两次。可以将也句改为如下方式：
SELECT A.PLAYERNO, NAME, AMOUNT
FROM PLAYERS A LEFT OUT JOIN PENALTIES B ON A.PLAYERNO = B.PLAYERNO

13. 避免数据类型转换
数据类型转换会影响SQL的处理速度，如果这种类型转换不是必要的话，应该避免

14. 最后指定最大的表
在表达式链接时，FROM从句内的表的顺序会影响SQL的处理速度，应遵循的规则是：在FROM从句的最后指定最大的表

15. 避免ANY, ALL运算符
许多优化程序在处理带有ALL，ANY运算符是不会使用索引，如果可能，尽量使用聚合函数MIN或MAX来替换
例1：代替ALL
SELECT NO,NAME,BDATE
FROM TEST
WHERE BDATE <= ALL(
SELECT BDATE
FROM TEST
)
用MIN代替为
SELECT NO, NAME, BDATE
FROM TEST
WHERE BDATE = (
SELECT MIN (BDATE)
FROM TEST
)
例2：代替ANY
SELECT NO,NAME,BDATE
FROM TEST
WHERE BDATE > ANY(
SELECT BDATE
FROM TEST
)
用MIN代替为
SELECT NO, NAME, BDATE
FROM TEST
WHERE BDATE > (
SELECT MIN (BDATE)
FROM TEST