SQL语句书可以提高执行效率的方法
1、操作符号: NOT IN操作符
此操作是强列推荐不使用的,因为它不能应用表的索引。推荐方案:用NOT EXISTS 或(外连接+判断
为空)方案代替"IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", "LIKE
'%500'",因为他们不走索引全是表扫描。NOT IN 会多次扫描表,使用EXISTS、NOT EXISTS、IN、LEFT
OUTER JOIN 来替代,特别是左连接,而Exists 比IN 更快,最慢的是NOT 操作。
2、注意union 和union all 的区别。union 比union all 多做了一步distinct 操作。能用union all 的情况下尽
量不用union。
如: 两个表A 和B 都有一个序号字段ID,要求两个表中的ID 字段最大的值:
select max(id) as max_id
from(
select id from 表A
union all
select id from 表B ) t
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时,尽量改成连接查询,少用子查询。
4、尽量少用视图,它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不
要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质:它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数
据时,不要使用指向多个表的视图,
直接从表检索或者仅仅包含这个表的视图上读,否则增加了不必要的开销,查询受到干扰.为了加快视图
的查询,MsSQL 增加了视图索引的功能。
5、创建合理的索引,对于插入或者修改比较频繁的表,尽量慎用索引。因为如果表中存在索引,插入
和修改时也会引起全表扫描。
索引一般使用于where 后经常用作条件的字段上。
6、在表中定义字段或者存储过程、函数中定义参数时,将参数的大小设置为合适即可,勿设置太大。
这样开销很大。
7、Between 在某些时候比IN 速度更快,Between 能够更快地根据索引找到范围。用查询优化器可见到差
别。
select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'是一样的。由于in 会在比较多次,所以有时会慢些。
8、在必要是对全局或者局部临时表创建索引,有时能够提高速度,但不是一定会这样,因为索引也耗
费大量的资源。他的创建同是实际表一样。
9、WHERE 后面的条件顺序影响
WHERE 子句后面的条件顺序对大数据量表的查询会产生直接的影响,如
Select * from zl_yhjbqk where dy_dj = '1KV 以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV 以下'
以上两个SQL 中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都
是全表扫描,如果dy_dj = '1KV 以下'条件在记录集内比率为99%,而xh_bz=1 的比率只为0.5%,在进
行第一条SQL 的时候99%条记录都进行dy_dj 及xh_bz 的比较,而在进行第二条SQL 的时候0.5%条记
录都进行dy_dj 及xh_bz 的比较,以此可以得出第二条SQL 的CPU 占用率明显比第一条低。所以尽量将
范围小的条件放在前面。。
10、用OR 的字句可以分解成多个查询,并且通过UNION 连接多个查询。他们的速度只同是否使用索
引有关,如果查询需要用到联合索引,用UNION all 执行的效率更高.多个OR 的字句没有用到索引,改
写成UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。
11、没有必要时不要用DISTINCT 和ORDER BY,这些动作可以改在客户端执行。它们增加了额外的开
销。这同UNION 和UNION ALL 一样的道理。
12、使用in 时,在IN 后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,这
样可以减少判断的次数
13、当用SELECT INTO 时,它会锁住系统表(sysobjects,sysindexes 等等),阻塞其他的连接的存取。创
建临时表时用显示声明语句,在另一个连接中SELECT * from sysobjects 可以看到SELECT INTO 会锁
住系统表, Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它!!!
这样的话如果是经常要用的临时表请使用实表,或者临时表变量。
14、一般在GROUP BY 和HAVING 字句之前就能剔除多余的行,所以尽量不要用它们来做剔除行的工
作。他们的执行顺序应该如下最优:select 的Where 字句选择所有合适的行,Group By 用来分组个统计
行,Having 字句用来剔除多余的分组。这样Group By 和Having 的开销小,查询快.对于大的数据行进行
分组和Having 十分消耗资源。如果Group BY 的目的不包括计算,只是分组,那么用Distinct 更快
15、一次更新多条记录比分多次更新每次一条快,就是说批处理好
16、慎用临时表,临时表存储于tempdb 库中,操作临时表时,会引起跨库操作。尽量用结果集和表变
量来代替它。
17、尽量将数据的处理工作放在服务器上,减少网络的开销,如使用存储过程。存储过程是编译好、优
化过,并且被组织到一个执行规划里、且存储在数据库中的SQL 语句,是控制流语言的集合,速度当
然快。
18、不要在一段SQL 或者存储过程中多次使用相同的函数或相同的查询语句,这样比较浪费资源,建议
将结果放在变量里再调用。这样更快。
19、按照一定的次序来访问你的表。如果你先锁住表A,再锁住表B,那么在所有的存储过程中都要按
照这个顺序来锁定它们。如果你(不经意的)某个存储过程中先锁定表B,再锁定表A,这可能就会导
致一个死锁。
此操作是强列推荐不使用的,因为它不能应用表的索引。推荐方案:用NOT EXISTS 或(外连接+判断
为空)方案代替"IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", "LIKE
'%500'",因为他们不走索引全是表扫描。NOT IN 会多次扫描表,使用EXISTS、NOT EXISTS、IN、LEFT
OUTER JOIN 来替代,特别是左连接,而Exists 比IN 更快,最慢的是NOT 操作。
2、注意union 和union all 的区别。union 比union all 多做了一步distinct 操作。能用union all 的情况下尽
量不用union。
如: 两个表A 和B 都有一个序号字段ID,要求两个表中的ID 字段最大的值:
select max(id) as max_id
from(
select id from 表A
union all
select id from 表B ) t
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时,尽量改成连接查询,少用子查询。
4、尽量少用视图,它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不
要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质:它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数
据时,不要使用指向多个表的视图,
直接从表检索或者仅仅包含这个表的视图上读,否则增加了不必要的开销,查询受到干扰.为了加快视图
的查询,MsSQL 增加了视图索引的功能。
5、创建合理的索引,对于插入或者修改比较频繁的表,尽量慎用索引。因为如果表中存在索引,插入
和修改时也会引起全表扫描。
索引一般使用于where 后经常用作条件的字段上。
6、在表中定义字段或者存储过程、函数中定义参数时,将参数的大小设置为合适即可,勿设置太大。
这样开销很大。
7、Between 在某些时候比IN 速度更快,Between 能够更快地根据索引找到范围。用查询优化器可见到差
别。
select * from chineseresume where title in ('男','女')
Select * from chineseresume where between '男' and '女'是一样的。由于in 会在比较多次,所以有时会慢些。
8、在必要是对全局或者局部临时表创建索引,有时能够提高速度,但不是一定会这样,因为索引也耗
费大量的资源。他的创建同是实际表一样。
9、WHERE 后面的条件顺序影响
WHERE 子句后面的条件顺序对大数据量表的查询会产生直接的影响,如
Select * from zl_yhjbqk where dy_dj = '1KV 以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV 以下'
以上两个SQL 中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都
是全表扫描,如果dy_dj = '1KV 以下'条件在记录集内比率为99%,而xh_bz=1 的比率只为0.5%,在进
行第一条SQL 的时候99%条记录都进行dy_dj 及xh_bz 的比较,而在进行第二条SQL 的时候0.5%条记
录都进行dy_dj 及xh_bz 的比较,以此可以得出第二条SQL 的CPU 占用率明显比第一条低。所以尽量将
范围小的条件放在前面。。
10、用OR 的字句可以分解成多个查询,并且通过UNION 连接多个查询。他们的速度只同是否使用索
引有关,如果查询需要用到联合索引,用UNION all 执行的效率更高.多个OR 的字句没有用到索引,改
写成UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。
11、没有必要时不要用DISTINCT 和ORDER BY,这些动作可以改在客户端执行。它们增加了额外的开
销。这同UNION 和UNION ALL 一样的道理。
12、使用in 时,在IN 后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,这
样可以减少判断的次数
13、当用SELECT INTO 时,它会锁住系统表(sysobjects,sysindexes 等等),阻塞其他的连接的存取。创
建临时表时用显示声明语句,在另一个连接中SELECT * from sysobjects 可以看到SELECT INTO 会锁
住系统表, Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它!!!
这样的话如果是经常要用的临时表请使用实表,或者临时表变量。
14、一般在GROUP BY 和HAVING 字句之前就能剔除多余的行,所以尽量不要用它们来做剔除行的工
作。他们的执行顺序应该如下最优:select 的Where 字句选择所有合适的行,Group By 用来分组个统计
行,Having 字句用来剔除多余的分组。这样Group By 和Having 的开销小,查询快.对于大的数据行进行
分组和Having 十分消耗资源。如果Group BY 的目的不包括计算,只是分组,那么用Distinct 更快
15、一次更新多条记录比分多次更新每次一条快,就是说批处理好
16、慎用临时表,临时表存储于tempdb 库中,操作临时表时,会引起跨库操作。尽量用结果集和表变
量来代替它。
17、尽量将数据的处理工作放在服务器上,减少网络的开销,如使用存储过程。存储过程是编译好、优
化过,并且被组织到一个执行规划里、且存储在数据库中的SQL 语句,是控制流语言的集合,速度当
然快。
18、不要在一段SQL 或者存储过程中多次使用相同的函数或相同的查询语句,这样比较浪费资源,建议
将结果放在变量里再调用。这样更快。
19、按照一定的次序来访问你的表。如果你先锁住表A,再锁住表B,那么在所有的存储过程中都要按
照这个顺序来锁定它们。如果你(不经意的)某个存储过程中先锁定表B,再锁定表A,这可能就会导
致一个死锁。
作者:JumpByte
来源:http://www.cnblogs.com/yja9010
更新: http://jumpbyte.cn
声明:本博客原创文字只代表本人的观点或结论,于网站他人无关,非商业,未授权,贴子请以现状保留,转载时必须保留此段声明,且在文章页面明显位置给出原文连接。
来源:http://www.cnblogs.com/yja9010
更新: http://jumpbyte.cn
声明:本博客原创文字只代表本人的观点或结论,于网站他人无关,非商业,未授权,贴子请以现状保留,转载时必须保留此段声明,且在文章页面明显位置给出原文连接。