Sql Server SQL查询优化方法

1. 尽量避免使用select *，返回无用的字段会降低查询效率。

SQLSERVER在解析的过程中，会将’*'依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

2. 使用表的别名。

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上，这样可以减少解析的时间并减少那些由Column歧义引起的语法错误。

3. From后有多个表时，必须选择记录数最少的表作为基础表放在最后。

1. Sql Server的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理。
2. 如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

4. Where子句中的连接顺序。

SQLSERVER采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

SELECT *
FROM Employee E
WHERE 100 < (SELECT COUNT(*) FROM OrderInfo O WHERE O.EmployeeNo=E.EmpployeeNo)
AND Salary> 50000
AND Position= 'IT';
1
2
3
4
5

5. 用索引提高效率。

1、尽量避免在索引列上使用IN 和NOT IN，会导致数据库引擎放弃索引进行全表扫描。

优化方式：
a.如果是连续数值，可以用between代替。

SELECT * FROM UserInfo WHERE id IN (1,2,3,4,5,6,7,8)
修改为：
SELECT * FROM UserInfo WHERE UserId BETWEEN 1 AND 8
1
2
3

b.如果是子查询，可以用exists代替。

SELECT * 
FROM UserInfo 
WHERE UserName IN 
(
	SELECT UserName FROM PayUserInfo WHERE PaySalary>5000
)
修改为：
SELECT * 
FROM UserInfo t1 
WHERE EXISTS 
(
	SELECT * FROM PayUserInfo t2 WHERE t1.UserName= t2.UserName AND PaySalary>5000
)
1
2
3
4
5
6
7
8
9
10
11
12
13

2、尽量避免在索引列上使用OR，会导致数据库引擎放弃索引进行全表扫描。

优化方式：可以用union代替or。

3、尽量避免在索引列字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE username LIKE '%li%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE 'li%'
1
2
3
4
5

4、尽量避免索引列上使用IS NULL和IS NOT NULL进行值的判断，会导致数据库引擎放弃索引进行全表扫描。

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0
1
2
3
4
5

5、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

以下方式不可取：

SELECT * FROM t2 WHERE score/10 = 9

SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'
1
2
3

6、当数据量大时，避免使用where 1=1的条件。会导致数据库引擎放弃索引进行全表扫描。

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。

6.使用UNION-ALL和UNION。

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序

如果用UNION ALL替代UNION，这样排序就不是必要了，效率就会因此得到提高

需要注意的是，UNION ALL将重复输出两个结果集合中相同记录，因此还是要从业务需求分析使用UNION ALL的可行性

7. 用EXISTS替换DISTINCT。

避免在SELECT子句中使用DISTINCT，一般可以考虑用EXIST替换， EXISTS使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果

8. 用表连接替换EXISTS。

通常来说，采用表连接的方式比EXISTS更有效率