20120718优化浅析——基于MS SQL Server

字数 4644阅读 2721评论 0赞 0

<DIV></DIV>
优化浅析 优化的适用范围、目标、流程 
 
通常我们所说的数据库优化，一般是指针对数据库系统、应用等运行效率越来越慢而进行的调整措施。而导致系统、应用越来越慢的主要原因通常都是由于负载的提升而暴露的硬件、设计、开发上的问题。这些问题中，有些是能通过优化解决的，比如架构不合理、索引不正确、SQL代码效率不高等；有些是无法进行优化的，比如巨大的业务量造成的cpu、内存、io全面瓶颈，非要优化到系统刚建立时那种健步如飞的状态，还是升级硬件比较靠谱^_^
 
做优化首先有个很重要的工作必须要先完成，即收集基线和确认预期目标。必须要有性能基线，才能把握系统的现状。有预期目标，才能确定优化的力度。有时基线和预期很接近，那可能只需要优化一点SQL语句就可以了；有时基线和目标相差甚远，可能整套系统都需要推倒重来甚至根本无法达到。
 
当拿到优化项目，确定基线和目标后，优化工作正式开始。
首先是根据采集的性能基线和系统指标确认系统瓶颈，是cpu繁忙？内存不足？IO异常？阻塞？MS还有个特别的tempdb冲突？这一步使用性能计数器和系统视图对cpu时间、缓存命中、checkpoint、SQL编译、IO队列长度、IO速度、锁等待时间、锁升级数量、工作表创建、工作表销毁等指标来确认系统瓶颈所在。
找到瓶颈后就要来找出瓶颈的原因。可以通过SQLtracer和性能计数器对比来很容易的找到问题所在。通常会体现为某些SQL语句占用大量资源。这时就可以针对这些SQL所占用的资源来进行针对性优化了。
优化完成后首先是在测试环境测试。可以将线上部分数据抽取到测试环境再进行测试，有可能还需要进行压力测试以确保优化有效及不会造成其他异常。测试环境通过后再是正式测试。此时还需要额外准备一个异常回滚方案以防万一。
 
 
优化的简单方案
         1对DBA来说，最简单的优化是硬件升级。当然一般老板都不愿选这种方式^_^
 
         2另一种简单且实用的方案就是降低数据量。
我们都很清楚，单表100万和单表1E在查询更新时的效率差别很大。就我自己的经验而言，百万行无索引全表扫描也就大半个小时，亿行无索引全表扫描至少就5个小时去了……
         分表有很多种方式，比较常用的是分区表。分表能极好的降低数据量，提高处理效率。
MS只有范围分区，而最常用的就是按时间跨度进行分表。如按月、按周、甚至按天分表。这种方式主要是用在明细记录上居多。特别是在需要将历史记录提取到分析系统或导出时尤为方便，毕竟只需要修改元数据而不是真正挪动数据。
其他数据库还有hash分区和list分区。两个都是针对重复度较高的某列进行分区。不同值很少就用list，较多就用hash。在管理方面不太方便，但同样也能很直接的降低处理数据量。
 
4降低数据量还有另外一个场景。有时需要进行超大数据量修改（insert、update、delete），简单写的话可能一个SQL语句就完成了。但由于数据量过于庞大（百万级），需要先生成大量的日志，从而整个语句执行非常慢。此时对整个系统也会造成极高的负载，很容易整个系统都陷入假死状态。很多时候这是不能容忍的。
而如果能将单步SQL改写为循环，每次只操作不太大量的数据（5万-10万，具体要看行大小）并提交。理论上说这样会比单SQL语句慢很多，但实际上，由于每次操作数据量小，生成日志也小。最终会出现5*200 << 1000的结果。
当然，单步改写为循环，有个很大隐患。一定要保证每条需要处理的数据都会且只会被处理一次。否则出现漏数据或者重复数据就得不偿失了。
 
5降低日志量？
MS的数据库有个恢复级别选项。完全、大容量日志、简单。可能很多人都以为‘完全’就是全部操作都生成日志，‘简单’就是很少生成日志。但实际上，‘完全’和‘简单’都会生成同样多的日志，只不过‘完全’的日志不会被删除（除非备份或dbcc），‘简单’的日志在提交以后就可以被覆盖掉，节省日志文件空间而已。只有‘大容量日志’能少些一部分日志，主要有create index、bulk insert等。在一定程度上确实可以提高效率。
 SQL语句优化 
         从SQL语句的逻辑处理来说，MS的SQL执行遵循比较严格的查找、计算、更新的顺序。而在查找阶段的执行效率在很大程度上决定了整个SQL执行的效率。
         MS的执行计划有很多算子。其中主要需要优化的是scan/seek算子、sort算子和join算子。
1，  scan/seek算子、覆盖索引、sort算子
查找数据的第一步。
Scan指遍历所有数据，特别对没有合适索引的scan，只能使用表扫描（无pk）或pk扫描，其执行效率非常低。实际中应尽量避免。

Seek根据指定值查找记录，在有索引的情况下效率非常高。

 
         在这一步，覆盖索引能起到非常大的作用。由于索引是B树结构，字段排在越前，越能快速的查找到记录。覆盖索引的字段顺序是个非常重要的问题。
         如索引1为A+B、索引2为A+C。对select * from tab where A=’1’ and B=’2’来说，索引1是个非常好的覆盖索引，其执行计划第一步极有可能是index1 scan（除非通过索引数据分布直方图发现满足A=’1’ and B=’2’的非常多，才可能使用pkscan或者table scan）。而对于select C from tab where B = ‘2’来说，索引1和索引2都不是很好的选择，如果B=’2’的结果不多，可能会使用index1scan + bookmark lookup。否则会使用pk scan或table scan。
         下面是个比较复杂的例子（假定每个字段的区分度都比较高）
         Select a.col1,b.col2 from a inner joinb on a.col3 = b.col4 where a.col5 = ‘a’ and b.col6= ‘b’
         此时覆盖索引应该按a(col5,col3,col1) b(col6,col4,col2)，即按照where-join-select的顺序来选择索引字段。
 
但对group和order子句来说又有不同了。由于group和order子句需要大量额外运算，带这两个子句的查询需要的覆盖索引不是以查找数据优先，而是以减少计算优先。

如Select a.col1,b.col2 from a inner join b ona.col3 = b.col4 where a.col5 = ‘a’ and b.col6= ‘b’ order by a.col7。覆盖索引应该是a(col7,col5,col3,col1)，即按照order-where-join-select的顺序。
对group子句也是一样，覆盖索引按group –where-join-select顺序。
 
当然，实际中不可能所有查询都建好完整的覆盖索引，索引太多对数据修改的效率影响也是很大的。这就需要权衡到底建哪些索引，哪些字段被覆盖。有可能需要多次调整才能找到一个较好的组合。
 
2， join算子
MS有3种join算子，   。
首先是3种算子的适用环境：
nest join不能用于right join。内表有索引
Merge join的关联条件必须包含一个等值条件，且两端都必须有序。
Hash join无限制
3种算子中以merge join限制最多，消耗较多，执行效率也最高。Hashjoin限制最小，执行消耗最高（效率不一定低）。Nest join比较平均
 
实际使用中，对较小的输入，最好能有合适的索引（主要是为了有序），使join算子更多的采用nest join。对小输入，nest join效率最高。
而对较中等的输入，尽量给两端都建索引，从而尽量利用到merge算子。
对较大的输入，由于大数据量下建索引消耗太大，有时只能在没有索引的情况下执行。只能采用hash join。此时需注意尽量减低并发以减低消耗。
 
 深层次优化 
1并发数设置
MS推荐的并发阀值是cpu个数/4。当ms认为cpu压力不大的时候会自动将任务拆分为多个子任务并发执行。但由于系统硬件性能不均衡，有可能cpu还有很大空闲，内存和IO都已经快爆了。
特别对于OLAP来说，大数据量查询是经常的事。如果经常看到大量子任务在等待io_complete，那就有可能是并发太高导致IO跟不上。此时就需要降低MS的并发设置。
 
2堆表和B树表的权衡
按有没有聚集索引，MS的表分为堆表（无聚集）和B树表（有聚集）两类。
下面是两个的对比
<TABLE border=1 cellSpacing=0 cellPadding=0>
<TBODY>
<TR>
<TD vAlign=top>
 </TD>
<TD vAlign=top>
堆表</TD>
<TD vAlign=top>
B树表</TD></TR>
<TR>
<TD vAlign=top>
物理有序</TD>
<TD vAlign=top>
否</TD>
<TD vAlign=top>
是</TD></TR>
<TR>
<TD vAlign=top>
物理序号</TD>
<TD vAlign=top>
Rid</TD>
<TD vAlign=top>
PK值</TD></TR>
<TR>
<TD vAlign=top>
数据存储位置</TD>
<TD vAlign=top>
链表指向空间</TD>
<TD vAlign=top>
叶子</TD></TR>
<TR>
<TD vAlign=top>
插入删除效率</TD>
<TD vAlign=top>
高</TD>
<TD vAlign=top>
低</TD></TR>
<TR>
<TD vAlign=top>
查询效率</TD>
<TD vAlign=top>
低</TD>
<TD vAlign=top>
高</TD></TR></TBODY></TABLE>
由于堆表也可以建立非聚集索引来提高查询速度，从上表对比来看似乎B树表木有优势。但MS的堆表有个类似bug的设定，它不会把占的空间返还给数据库！！dbcc收缩也不行。
 
3锁
很多时候我们都只注意了S锁和X锁的切换冲突，但实际上一个查询从解释到执行完成还获得释放了其他很多很多锁。其中较为重要的就是架构锁。
当一个SQL提交给数据库之后，没在缓存中找到其执行计划的话，会解析其SQL语法是否正确，进程A就会去申请相关表的架构锁。如果这个时候另外有个任务B在已经在操作这个表，A就会一直在waiting schema，从而影响到B的执行。为减少这种冲突，尽量少用select *！！

color 优化智能

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！

添加新评论0 条评论

Ctrl+Enter 发表

匿名评论

20120718优化浅析——基于MS SQL Server

添加新评论0 条评论

作者其他文章

相关文章

相关问题

相关资料