sql语句优化.docx
- 文档编号:30753100
- 上传时间:2023-08-20
- 格式:DOCX
- 页数:23
- 大小:102.30KB
sql语句优化.docx
《sql语句优化.docx》由会员分享,可在线阅读,更多相关《sql语句优化.docx(23页珍藏版)》请在冰豆网上搜索。
sql语句优化
sql语句优化
性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。
为了获得稳定的执行性能,SQL语句越简单越好。
对复杂的SQL语句,要设法对之进行简化。
常见的简化规则如下:
1)不要有超过5个以上的表连接(JOIN)
2)考虑使用临时表或表变量存放中间结果。
3)少用子查询
4)视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
连接的表越多,其编译的时间和连接的开销也越大,性能越不好控制。
最好是把连接拆开成较小的几个部分逐个顺序执行。
优先执行那些能够大量减少结果的连接。
拆分的好处不仅仅是减少SQLServer优化的时间,更使得SQL语句能够以你可以预测的方式和顺序执行。
如果一定需要连接很多表才能得到数据,那么很可能意味着设计上的缺陷。
连接是outerjoin,非常不好。
因为outerjoin意味着必须对左表或右表查询所有行。
如果表很大而没有相应的where语句,那么outerjoin很容易导致tablescan或indexscan。
要尽量使用innerjoin避免scan整个表。
优化建议:
1)使用临时表存放t1表的结果,能大大减少logicalreads(或返回行数)的操作要优先执行。
仔细分析语句,你会发现where中的条件全是针对表t1的,所以直接使用上面的where子句查询表t1,然后把结果存放再临时表#t1中:
Selectt1…..into#tt1fromt1where…(和上面的where一样)
2)再把#tt1和其他表进行连接:
Select#t1…
Leftouterjoin…
Leftouterjoin…
3)修改like程序,去掉前置百分号。
like语句却因为前置百分号而无法使用索引
4)从系统设计的角度修改语句,去掉outerjoin。
5)考虑组合索引或覆盖索引消除clusteredindexscan。
上面1和2点建议立即消除了worktable,性能提高了几倍以上,效果非常明显。
1)限制结果集
要尽量减少返回的结果行,包括行数和字段列数。
返回的结果越大,意味着相应的SQL语句的logicalreads就越大,对服务器的性能影响就越甚。
一个很不好的设计就是返回表的所有数据:
Select*fromtablename
即使表很小也会导致并发问题。
更坏的情况是,如果表有上百万行的话,那后果将是灾难性的。
它不但可能带来极重的磁盘IO,更有可能把数据库缓冲区中的其他缓存数据挤出,使得这些数据下次必须再从磁盘读取。
必须设计良好的SQL语句,使得其有where语句或TOP语句来限制结果集大小。
2)合理的表设计
SQLServer2005将支持表分区技术。
利用表分区技术可以实现数据表的流动窗口功能。
在流动窗口中可以轻易的把历史数据移出,把新的数据加入,从而使表的大小基本保持稳定。
另外,表的设计未必需要非常范式化。
有一定的字段冗余可以增加SQL语句的效率,减少JOIN的数目,提高语句的执行速度。
3)OLAP和OLTP模块要分开
OLAP和OLTP类型的语句是截然不同的。
前者往往需要扫描整个表做统计分析,索引对这样的语句几乎没有多少用处。
索引只能够加快那些如sum,groupby之类的聚合运算。
因为这个原因,几乎很难对OLAP类型的SQL语句进行优化。
而OLTP语句则只需要访问表的很小一部分数据,而且这些数据往往可以从内存缓存中得到。
为了避免OLAP和OLTP语句相互影响,这两类模块需要分开运行在不同服务器上。
因为OLAP语句几乎都是读取数据,没有更新和写入操作,所以一个好的经验是配置一台standby服务器,然后OLAP只访问standby服务器。
4)使用存储过程
可以考虑使用存储过程封装那些复杂的SQL语句或商业逻辑,这样做有几个好处。
一是存储过程的执行计划可以被缓存在内存中较长时间,减少了重新编译的时间。
二是存储过程减少了客户端和服务器的繁复交互。
三是如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序,避免需要重新安装部署程序。
索引优化
很多数据库系统性能不理想是因为系统没有经过整体优化,存在大量性能低下的SQL语句。
这类SQL语句性能不好的首要原因是缺乏高效的索引。
没有索引除了导致语句本身运行速度慢外,更是导致大量的磁盘读写操作,使得整个系统性能都受之影响而变差。
解决这类系统的首要办法是优化这些没有索引或索引不够好的SQL语句。
创建索引的关键
优化SQL语句的关键是尽可能减少语句的logicalreads。
这里说的logicalreads是指语句执行时需要访问的单位为8K的数据页总数。
logicalreads越少,其需要的内存和CPU时间也就越少,语句执行速度就越快。
不言而喻,索引的最大好处是它可以极大减少SQL语句的logicalreads数目,从而极大减少语句的执行时间。
创建索引的关键是索引要能够大大减少语句的logicalreads。
一个索引好不好,主要看它减少的logicalreads多不多。
运行setstatisticsio命令可以得到SQL语句的logicalreads信息。
setstatisticsioon
selectau_id,au_lname,au_fname
frompubs..authorswhereau_lname='Green'
setstatisticsioon
如果Logicalreads很大,而返回的行数很少,也即两者相差较大,那么往往意味者语句需要优化。
Logicalreads中包含该语句从内存数据缓冲区中访问的页数和从物理磁盘读取的页数。
而physicalreads表示那些没有驻留在内存缓冲区中需要从磁盘读取的数据页。
Read-aheadreads是SQLServer为了提高性能而产生的预读。
预读可能会多读取一些数据。
优化的时候我们主要关注LogicalReads就可以了。
注意如果physicalReads或Read-aheadreads很大,那么往往意味着语句的执行时间(duration)里面会有一部分耗费在等待物理磁盘IO上。
二、单字段索引,组合索引和覆盖索引
单字段索引是指只有一个字段的索引,而组合索引指有多个字段构成的索引。
1.对出现在where子句中的字段加索引
setstatisticsprofileon
setstatisticsioon
go
select....fromtbwhere...
go
setstatisticsprofileoff
setstatisticsiooff
setstatisticsprofile命令将输出语句的执行计划。
也许你会问,为什么不用SETSHOWPLAN_ALL呢?
使用SETSHOWPLAN_ALL也是可以的。
不过setstatisticsprofile输出的是SQL语句的运行时候真正使用的执行计划,
而SETSHOWPLAN_ALL输出的是预计(Estimate)的执行计划。
使用SETSHOWPLAN_ALL是后面的语句并不会真正运行。
用了TableScan,也就是对整个表进行了全表扫描。
全表扫描的性能通常是很差的,要尽量避免。
如果上面的select语句是数据库系统经常运行的关键语句,那么应该对它创建相应的索引。
创建索引的技巧之一是对经常出现在where条件中的字段创建索引
TableScan也变成了IndexSeek,性能极大提高
设法避免Tablescan或Indexscan是优化SQL语句使用的常用技巧。
通常IndexSeek需要的logicalreads比前两者要少得多。
2.组合索引
如果where语句中有多个字段,那么可以考虑创建组合索引。
组合索引中字段的顺序是非常重要的,越是唯一的字段越是要靠前。
另外,无论是组合索引还是单个列的索引,尽量不要选择那些唯一性很低的字段。
比如说,在只有两个值0和1的字段上建立索引没有多大意义。
所以如果对单字段进行索引,建议使用setstatisticsprofile来验证索引确实被充分使用。
logicalreads越少的索引越好。
3.覆盖索引
覆盖索引能够使得语句不需要访问表仅仅访问索引就能够得到所有需要的数据。
因为聚集索引叶子节点就是数据所以无所谓覆盖与否,所以覆盖索引主要是针对非聚集索引而言。
执行计划中除了indexseek外,还有一个BookmarkLookup关键字。
BookmarkLookup表示语句在访问索引后还需要对表进行额外的BookmarkLookup操作才能得到数据。
也就是说为得到一行数据起码有两次IO,一次访问索引,一次访问基本表。
如果语句返回的行数很多,那么BookmarkLookup操作的开销是很大的。
覆盖索引能够避免昂贵的BookmarkLookup操作,减少IO的次数,提高语句的性能。
覆盖索引需要包含select子句和WHERE子句中出现的所有字段。
Where语句中的字段在前面,select中的在后面。
logicalreads,是大大减少了。
BookmarkLookup操作也消失了。
所以创建覆盖索引是减少logicalreads提升语句性能的非常有用的优化技巧。
实际上索引的创建原则是比较复杂的。
有时候你无法在索引中包含了Where子句中所有的字段。
在考虑索引是否应该包含一个字段时,应考虑该字段在语句中的作用。
比如说如果经常以某个字段作为where条件作精确匹配返回很少的行,那么就绝对值得为这个字段建立索引。
再比如说,对那些非常唯一的字段如主键和外键,经常出现在groupby,orderby中的字段等等都值得创建索引。
问题1,是否值得在identity字段上建立聚集索引。
答案取决于identity字段如何在语句中使用。
如果你经常根据该字段搜索返回很少的行,那么在其上建立索引是值得的。
反之如果identity字段根本很少在语句中使用,那么就不应该对其建立任何索引。
问题2,一个表应该建立多少索引合适。
如果表的80%以上的语句都是读操作,那么索引可以多些。
但是不要太多。
特别是不要对那些更新频繁的表其建立很多的索引。
很少表有超过5个以上的索引。
过多的索引不但增加其占用的磁盘空间,也增加了SQLServer维护索引的开销。
问题4:
为什么SQLServer在执行计划中没有使用你认为应该使用的索引?
原因是多样的。
一种原因是该语句返回的结果超过了表的20%数据,使得SQLServer认为scan比seek更有效。
另一种原因可能是表字段的statistics过期了,不能准确反映数据的分布情况。
你可以使用命令UPDATESTATISTICStablenamewithFULLSCAN来更新它。
只有同步的准确的statistics才能保证SQLServer产生正确的执行计划。
过时的老的statistics常会导致SQLServer生成不够优化的甚至愚蠢的执行计划。
所以如果你的表频繁更新,而你又觉得和之相关的SQL语句运行缓慢,不妨试试UPDATESTATISTICwithFULLSCAN语句。
问题5、什么使用聚集索引,什么时候使用非聚集索引
在SQLServer中索引有聚集索引和非聚集索引两种。
它们的主要差别是前者的索引叶子就是数据本身,而后者的叶子节点包含的是指向数据的书签(即数据行号或聚集索引的key)。
对一个表而言聚集索引只能有一个,而非聚集索引可以有多个。
只是聚集索引没有BookmarkLookup操作。
什么时候应该使用聚集索引?
什么时候使用非聚集索引?
取决于应用程序的访问模式。
我的建议是在那些关键的字段上使用聚集索引。
一个表一般都需要建立一个聚集索引。
对于什么时候使用聚集索引,SQLServer2000联机手册中有如下描述:
在创建聚集索引之前,应先了解您的数据是如何被访问的。
可考虑将聚集索引用于:
包含大量非重复值的列。
使用下列运算符返回一个范围值的查询:
BETWEEN、>、>=、<和<=。
被连续访问的列。
返回大型结果集的查询。
经常被使用联接或GROUPBY子句的查询访问的列;一般来说,这些是外键列。
对ORDERBY或GROUPBY子句中指定的列进行索引,可以使SQLServer不必对数据进行排序,因为这些行已经排序。
这样可以提高查询性能。
OLTP类型的应用程序,这些程序要求进行非常快速的单行查找(一般通过主键)。
应在主键上创建聚集索引。
聚集索引不适用于:
频繁更改的列
这将导致整行移动(因为SQLServer必须按物理顺序保留行中的数据值)。
这一点要特别注意,因为在大数据量事务处理系统中数据是易失的。
宽键
来自聚集索引的键值由所有非聚集索引作为查找键使用,因此存储在每个非聚集索引的叶条目内。
总结:
如何使一个性能缓慢的系统运行更快更高效,不但需要整体分析数据库系统,找出系统的性能瓶颈,更需要优化数据库系统发出的SQL语句。
一旦找出关键的SQL语句并加与优化,性能问题就会迎刃而解。
《数据库技术内幕》
处理百万级以上的数据提高查询速度的方法:
1.应尽量避免在 where 子句中使用!
=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
2.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
3.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
5.下面的查询也将导致全表扫描:
(不能前置百分号)
select id from t where name like ‘%abc%’
若要提高效率,可以考虑全文检索。
6.in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
7.如果在 where 子句中使用参数,也会导致全表扫描。
因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。
然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。
如下面语句将进行全表扫描:
select id from t where num=@num
可以改为强制查询使用索引:
select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。
如:
select id from t where num/2=100
应改为:
select id from t where num=100*2
9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。
如:
select id from t where substring(name,1,3)=’abc’–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使 用,并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询,如需要生成一个空表结构:
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
create table #t(…)
13.很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。
一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。
若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。
17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。
20.尽量使用表变量来代替临时表。
如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。
21.避免频繁创建和删除临时表,以减少系统表资源的消耗。
22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。
但是,对于一次性事件,最好使 用导出表。
23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。
26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。
27.与临时表一样,游标并不是不可使用。
对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。
如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。
无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
30.尽量避免大事务操作,提高系统并发能力。
查询速度慢的原因:
1、没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)
2、I/O吞吐量小,形成了瓶颈效应。
3、没有创建计算列导致查询不优化。
4、内存不足
5、网络速度慢
6、查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)
7、锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷)
8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。
9、返回了不必要的行和列
10、查询语句不好,没有优化
可以通过如下方法来优化查询
1、把数据、日志、索引放到不同的I/O设备上,增加读取速度,以前可以将Tempdb应放在RAID0上,SQL2000不在支持。
数据量(尺寸)越大,提高I/O越重要.
2、纵向、横向分割表,减少表的尺寸(sp_spaceuse)
3、升级硬件
4、根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的数据量。
注意填充因子要适当(最好是使用默认值0)。
索引应该尽量小,使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段
5、提高网速;
6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。
配置虚拟内存:
虚拟内存大小应基于计算机上并发运行的服务进行配置。
运行 Microsoft SQL Server?
2000 时,可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 语句 优化