近期项目需要,做了一段时间的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 * 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 <‘2007-01-01’,这样会使用针对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 ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。因为索引的摆放是依据字段值升序或降序排列,like’%*’这种用法,不能利用有序的数据结构,利用二分法查找数据。一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。
9. 查询条件中的适当与不适当
查询参数可以包含一下操作:=、<、>、>=、<=、BETWEEN、部分like。其中,like当这样使用时会用到索引:like ‘*%’,但like’%*’就用不到索引。
不适当的查询参数有:NOT 、!= 、<>、 !>、 !< 、NOT EXISTS、 NOT IN 、NOT LIKE等,还有一些不当的用法,例如:对数据进行计算,负向查询、等号左边使用函数、使用OR。上述语法都不用不上索引,降低程序的效率。
分享到:
相关推荐
SQL Server数据库性能优化之SQL语句篇
sql 语句优化 SQL Server数据库查询速度慢的原因有很多
SQL Server 数据库连接数是一个重要的性能指标,它可以帮助数据库管理员了解当前数据库的工作负载和性能。查看数据库连接数有多种方法,本文将介绍四种不同的方法来查看 SQL Server 数据库连接数。 方法一:通过...
"SQL SERVER数据库性能...SQL Server数据库性能优化是数据库管理员和开发人员的主要任务之一。通过各种手段和方法来提高 SQL Server 数据库的性能,可以提高应用程序的响应速度和吞吐量,提高用户体验和企业的竞争力。
SQLServer数据库管理的常用SQL语句,比较实用的sql语句。
SQL Server数据库基本SQL语句汇总,xls格式,适合作为手册使用
输入ip,用户名,密码和数据库名,然后连接sqlserver数据库,等连接上数据库时,批量执行sql语句。
"医院HIS系统SQLServer数据库性能优化" 医院HIS系统SQLServer数据库性能优化是指通过调整规则将数据库服务器的网络流量、磁盘I/O和CPU时间减少到最小,使每个查询的响应时间最短,并最大限度地提高整个数据库服务器...
从SQLSERVER数据库中导出记录生成insert into 语句
快捷方便,可查看sqlserver数据库每个表的使用情况,一次下载终身使用,此文件为sql语句格式,免费试用
在本文中,我们将讨论50种方法来优化SQL Server数据库查询,以提高查询速度和数据库性能。这些方法可以分为以下几类:索引优化、硬件升级、服务器配置、查询语句优化、数据库设计等。 索引优化 索引是数据库查询...
用sql语句dbcclog查看SQLServer数据库的事务日志[定义].pdf
SQL Server数据库 安装与升级 SQL Server数据库 查询语句 SQL Server数据库 数据备份和恢复 ...SQL Server数据库 性能和优化 SQL Server数据库 集群 SQL Server数据库 常见问题 SQL Server数据库 精彩问答
对SQL Server 数据库基本SQL语句汇总,大部分复杂语句都可以通过这些基本语句组合实现
用于从从SQLServer数据库导出SQL语句
SQL Server数据库 安装与升级 SQL Server数据库 查询语句 SQL Server数据库 数据备份和恢复 ...SQL Server数据库 性能和优化 SQL Server数据库 集群 SQL Server数据库 常见问题 SQL Server数据库 精彩问答
从SQLServer数据库导出SQL语句 ;
SSM如何连接sqlserver数据库?目前网上的几乎都是连接mysql的。替换src目录下的configuration.properties,以及WEB-INF目录下的context.xml文件就可以连接上了。lib目录下加入sqljdbc42.jar用于连接Sqlserver。再把...
8.掌握应用Transact-SQL语句创建数据库的方法。 9.掌握应用Transact-SQL修改和查看数据库的方法。 10.掌握通过Transact-SQL删除数据库的方法。 11.掌握SQL Server 2005数据库和操作系统物理文件的关系。 12.掌握...
常用的数据库性能分析语句,服务器卡的时候,常用的可以查看后台连接、使用能耗比较高的语句代码