SQL Server数据库性能优化之SQL语句篇

SQL Server数据库性能优化之SQL语句篇 （转自重名鸟 bolg Java）

近期项目需要，做了一段时间的SQL Server性能优化，遇到了一些问题，也积累了一些经验，现总结一下，与君共享。SQL Server性能优化涉及到许多方面，如良好的系统和数据库设计，优质的SQL编写，合适的数据表索引设计，甚至各种硬件因素：网络性能、服务器的性能、操作系统的性能，甚至网卡、交换机等。这篇文章主要讲到如何改善SQL语句，还将有另一篇讨论如何改善索引。

如何改善SQL语句的一些原则：

1. 按需索取字段，跟“SELECT *”说拜拜

字段的提取一定要按照“用多少提多少”的原则，避免使用“SELECT *”这样的操作。做了这样一个实验，表tblA有1000万数据：

select top 10000 c1, c2, c3, c4 from tblA order by c1 desc --用时：4673毫秒 select top 10000 c1, c2, c3 from tblA order by c1 desc --用时：1376毫秒 select top 10000 c1, c2 from tblA order by c1 desc --用时：80毫秒

由此看来，我们每少提取一个字段，数据的提取速度就会有相应的提升。但提升的速度还要看您舍弃的字段的大小来判断。

另外，关于“SELECT *“的问题，可以参考这篇文章：

http://www.cnblogs.com:80/goodspeed/archive/2007/07/20/index_coverage.html

2. 字段名和表名要写规范，注意大小写

这一点要多注意，如果大小写写错的话，虽然SQL仍然能正常执行，但数据库系统会花一定的开销和时间先要把您写的规范成正确的，然后再执行SQL。写对的话，这个时间就省了。

正常的： select top 10 dteTransaction, txtSystem_id from tblTransactionSystem 不小心的：select top 10 dtetransaction, txtsystem_id from tbltransactionsystem

3. 适当使用过渡表

把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：

SELECT cust.name，rcvbles.balance，……other columns FROM cust，rcvbles

WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0

AND cust.postcode>“98000” ORDER BY cust.name

如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：

SELECT cust.name，rcvbles.balance，……other columns INTO temp_cust_with_balance FROM cust，rcvbles

WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 ORDER BY cust.name

然后以下面的方式在临时表中查询：

SELECT cl,c2 FROM temp_cust_with_balance WHERE postcode>“98000”

临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。注意：过渡临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。

4. 别在where条件中做函数计算

这样做的后果是将在每个行上进行运算，这将导致该列的索引失效而触发全表扫描。如下SQL：

select * from users where YEAR(dteCreated) < 2007

可以改成

select * from users where dteCreated

这样会使用针对dteCreated的索引，提高查询效率。

5. IN（NOT IN）操作符与EXISTS（NOT EXISTS）操作符

有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种方式的子查询。如下： 第一种方式使用IN操作符：

select a.id from tblA a where a.id in (select b.id from tblB b)

第二种方式使用EXIST操作符：

select a.id from tblA a where exists (select 1 from tblB b where b.id = a.id)

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，而第二种格式要远比第一种格式的效率高。从SQL执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

SQL试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。 第二种格式中，子查询以’select 1’开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。

通过使用EXIST，数据库系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。数据库系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。 同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。

6. IS NULL 或 IS NOT NULL操作（判断字段是否为空）

不能用null作索引，任何包含null值的列都将不会被包含在索引中，因为B树索引是不索引空值的。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

推荐方案：用其它相同功能的操作运算代替，如a is not null 改为 a>0 或a>’等。另外还设置字段不允许为空，而用一个缺省值代替空值，如一个datetime字段，可以将默认时间设为“1900-01-01”。

7. > 及 < 操作符（大于或小于操作符）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30 万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为 A>2时sql会先找出为2的记录索引再进行比较，而A>=3时sql则直接找到=3的记录索引。可结合非聚集索引一起考虑。

8. LIKE操作符

LIKE 操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘T00%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。因为索引的摆放是依据字段值升序或降序排列，like'%*'这种用法，不能利用有序的数据结构，利用二分法查找数据。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘T00%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。

9. 查询条件中的适当与不适当

查询参数可以包含一下操作：=、<、>、>=、<=、BETWEEN、部分like。其中，like当这样

本文来源：https://www.bwwdw.com/article/hbi2.html