开发者应了解的一些SQL优化准则

4顶
3踩

2013-02-03 15:47 by 见习记者车丽评论(13) 有9571人浏览

sql 优化查询

下面介绍一些开发者在数据库操作中要注意的SQL编码准则。虽然本文不能覆盖所有的准则，但还是希望能给开发者带来些许帮助。下面就来看看在编码实践中哪些应该做，哪些不应该做。

1. 在长时间运行的查询和短查询中使用事务

如果预期有一个长时间运行的查询，并且有大量的数据输出时，开发者就应该在BEGIN TRAN 和END TRAN之间使用事务。

这样事务会在缓冲区缓存为独立事务，并会被分配特定内存，以此来提高处理速度。

2. 不要使用SELECT *

如果使用SELECT * 来选择表中的所有记录，那么一些不必要的记录也被读取、缓存，增加了磁盘的I/O和内存消耗。

3. 避免在WHERE子句中使用显式或隐式函数，比如Convert ()

4. 避免在触发器中执行长时间的操作

5. 适当使用临时表和表变量

当结果集较小的时候，请尽量使用表变量；当结果集相当大时，使用临时表。

6. 使用连接（JOIN）代替子查询（Sub-Queries）

子查询通常作为内联代码来使用，而连接（JOIN）则作为表来使用，这样速度会更快。所以，应尽量避免在连接中使用子查询。

7. 连接条件中表的顺序

在连接条件中，应尽量首先使用较小的表，然后逐步使用较大的表。

8. 循环优化

如果操作在循环内部没有任何影响，那么应尽量将操作放到循环外面，这样可以减少不必要的重复工作。因为，SQL Server优化器不会自动识别这种低效率的代码，更不会自动优化（其他一些语言的编译器可以）。

9. 参数探测

不要在正执行的SP（存储过程）中使用SP参数，这样会导致参数探测（Parameter Sniffing）。应该在声明和设置后再使用SP参数。由于这个原因，SP的行为在每次运行期间都不相同。

10. 当使用条件语句时，可以使用Index（索引）Hint（提示）

比如在SQL Server 2008中，可以使用Index hint，也可以使用fixed plan hint强制在查询中使用hint，以提高运行速度。

11. 在声明中明确指定存储过程中数据类型的大小

开发者随机声明数据类型的大小是不可取的，如：Varchar (500)。这在执行时会在缓冲区中增加不必要的预留空间。

12. 在查询中有效利用MAXDOP（最大并行度）设置

询问数据库管理员关于四核CPU可用性的设置，包括内存的设置，然后适当使用hint，可以有效改善查询速度。

13. SQL Server 2008中的GROUPING SETS

如果数据库服务器为SQL Server 2008，那么可以在所有的Unions中使用Grouping Set来代替Group By。这样在Union中重新进行group by排序时，优化器不会每次都制定一个计划。

14. 当发生死锁时，总是使用With (nolock) 和With (rowlock)

15. 使用Update From，而不是简单的Update

UPDATE titleauthor
SET royaltyper = 90
WHERE au_id = (SELECT au_id FROM authors
WHERE au_lname = ‘Ringer’ AND au_fname = ‘Albert’)
AND title_id = (SELECT title_id FROM titles
WHERE title = ‘Life without Fear’)
Update from
UPDATE titleauthor
SET royaltyper = 90
FROM authors a, titles t
WHERE titleauthor.au_id = a.au_id
AND a.au_lname = ‘Ringer’
AND a.au_fname = ‘Albert’
AND titleauthor.title_id = t.title_id
AND t.title = ‘Life Without Fear’

16. 使用UNION ALL代替UNION

UNION和UNION ALL的差别就在于UNION会对数据做一个distanct的动作，而这个distanct动作的速度则取决于现有数据的数量，数量越大时间越长。因此尽量使用UNION ALL来代替UNION。

17. 避免高成本操作，如NOT LIKE

英文原文：Coding Guidelines for the Developer During Development

分享到：

4
顶

3
踩

评论共 13 条请登录后发表评论

13 楼 dohkoos 2014-07-01 10:05

标题误人。应该用《开发者应了解的一些SQL Server优化准则》

12 楼 MrLee23 2013-02-05 13:15

这篇文章对新手来说误导太大，但是有那么几条说的还算对，因为这几天是基本知识。。。。

11 楼 icefishc 2013-02-04 17:04

javaeye上几乎所有的关于数据库的文章都有无数槽点，　不管是翻译的抄来的还是原创的，鲜有例外。　这种误人子弟的文章还是直接删掉好了。

10 楼 icefishc 2013-02-04 16:38

javaeye上几乎所有的关于数据库的文章都有无数槽点，　不管是翻译的抄来的还是原创的，鲜有例外。　这种误人子弟的文章还是直接删掉好了。

9 楼 hot66hot 2013-02-04 14:57

sql优化，没有准则，就是准则～～～

8 楼 cfan1874 2013-02-04 13:54

这条有些误人子弟-----子查询。
要根据情况而定，很多时候用子查询速度比多表连接查询快，Oracle就可以用PLSQL测试。

7 楼 bitray 2013-02-04 13:53

zrz_1989 写道

7. 连接条件中表的顺序
oracle应该是将大表放前面，小表放后吧

从oracle10g开始就不需要重视这个了吧？

6 楼 zrz_1989 2013-02-04 13:41

7. 连接条件中表的顺序
oracle应该是将大表放前面，小表放后吧

5 楼 caihemm3 2013-02-04 11:19

6. 使用连接（JOIN）代替子查询（Sub-Queries）

不太赞同，对于字典表还是使用子查询，否则，表关联起来大的要死，查起来要命的。

4 楼 hellostory 2013-02-04 11:16

ccxw1983 写道

很明显，这是sql server的优化建议。
oracle不支持update form，这个功能用起来非常的爽，多表查询先确定下受影响范围，然后再执行update比较安全。

不知道你在爽什么！难道你不知道除了“Update from”语法以外，还可以这样写吗？

update jc_user a, jc_user_ext b
   set b.realname="不知道"
where a.user_id=b.user_id
   and a.user_id=5

3 楼 ccxw1983 2013-02-04 09:15

很明显，这是sql server的优化建议。
oracle不支持update form，这个功能用起来非常的爽，多表查询先确定下受影响范围，然后再执行update比较安全。

2 楼 fanlei77 2013-02-04 08:30

有一些不适用于Oracle啊

1 楼 hellostory 2013-02-04 08:11

使用Update From，而不是简单的Update？？？？？？？？？？？

发表评论

您还没有登录,请您登录后再发表评论

4顶3踩