影响Sql server性能的因素

时　间:2018-06-20 08:18:33
作　者:宏鹏   ID：21115  城市：上海
摘　要:数据库性能取决于各方面综合因素：
硬件，操作系统，软件
硬件：内存，CPU，磁盘
正　文:

数据库性能取决于各方面综合因素：
硬件，操作系统，软件
硬件：内存，CPU，磁盘
　　当服务器的物理内存不足时，会产生大量的磁盘I/O，给磁盘带来压力；
　　当内存不足时，一些占用CPU资源较多的对象可能就无法被正常缓存在内存中，需要使用大量的CUP资源来处理这些对象的计算，从而给CPU带来更大的压力
　　内存：
　　　　1.执行计划缓存
　　　　　　数据库引擎接收到需要执行的语句时，首先会经过一系列复杂的计算和分析，得到相应的执行计划，然后再根据执行计划进行各种操作，
　　　　　　由于执行计划的计算和分析需要的CPU资源比较多，所以很容易引起CPU资源的紧张，为了解决这个问题，数据库就会把执行计划缓存起来
　　　　2.数据缓存
　　　　　　若遇到数据访问操作符，则会首先检查对应的数据是否在数据缓存中，如果存在就从缓存中取数据，否则会从磁盘中读取数据，然后再把数据缓存到缓存中
　　　　　　如果数据库内存不足，数据库引擎会算法把不常用的缓存清理，把需要的数据从磁盘中读取并缓存到数据缓存中，这会引起大量的磁盘I/O，并且导致执行语句的执行效率低下
　　　　　　若大批的语句出现这种情况，就会导致服务器CPU占用率飙高。如果服务器CPU长期处于繁忙状态，并且数据库的磁盘I/O偏高，估计是数据库的内存达到了瓶颈
　　CPU:
　　　　数据库在进行任务调度，执行计划分析，排序等计算时都需要使用大量的CPU资源
　　　　　　CPU资源在30%上下时：当前服务器较空闲
　　　　　　CPU资源在60%上下时：当前服务器较繁忙
　　　　　　CPU资源达到80%时：服务器非常繁忙，就要查找原因了（通常情况下，一些执行效率较高，并且性能不理想的语句会造成这样的问题；少数情况下是由于数据库服务器达到了瓶颈）
　　针对高并发的数据库系统，建议使用如下配置方案：
　　　　将主数据库的数据文件拆分成多个文件
　　　　将数据文件和日志文件存放在不同的物理磁盘，从而提高I/O的并发。
　　　　系统数据库文件，特别是Tempdb的数据文件要放在独立的物理磁盘，并将数据文件拆分成多个，建议与逻辑CPU的个数相同，以提高并发

设计表建议：
　　尽可能的添加数据完整约束，例如：非空约束，默认值约束，check约束，唯一约束，外键约束等，这些约束的添加降有助于数据库关系引擎分析执行计划
　　使用尽可能小的字段类型，特别是大表，尽量小的空间将可以带来更佳的性能
　　表结构的设计应考虑业务需求带来的操作及频率，尽可能的使业务逻辑实现简洁，使用简单的SQL语句，可避免过多的表关联
编写SQL语句建议：
　　编写语句前，先明确已经完全理解了业务需求，并知道表的用途及用法
　　确定业务需要用到的过滤字段能否使用索引，是否有必要在字段上添加索引
　　不要多有索引的字段进行任何的计算，包括函数，因为这会导致无法使用索引进行数据检索，从而导致扫描操作
　　小表操作优先，以小表驱动大表，使其尽量使用NESTED LOOP-嵌套循环(NESTED LOOP是表关联操作的一种物理操作方式，它使用foreach的方式以较小数据量的数据集为驱动，内嵌foreach循环较大的表进行对比，其效率比其他几个关联操作高，)
　　只查询需要的字段，避免*
　　尽量使用简单的SQL语句来实现业务功能，如果功能过于复杂，可以考虑将其拆分成若干个简单的SQL语句
简单的SQL：
　　关联的表最多不超过4个
　　没有复杂的过滤条件，只有2到3个过滤条件，可以使用索引查找操作

查询设计原则：
　　1.不要在过滤字段上使用任何的计算，包括：函数，逻辑计算，普通的计算等，因为这些计算公式的加入，将造成查询优化器无法使用相应字段的索引
　　2.尽量使用有索引的字段进行排序，特别是排序的数据量比较大时，这可以很大程度上降低排序操作带来的成本开销
　　3.填写查询表时，尽量使用Join关键字连接表，这样的语句清晰，易于阅读，不易缺失关联条件

排序优化：
　　在加入排序计算以后，尽量控制排序的数据量，尽量使排序操作能在额定的内存空间中完成，避免使用Tempdb。数据量较大时，可以考虑在排序字段上添加索引来避免执行排序操作。
　　查询大量的数据会造成CUP资源消耗，并且，在查询数据超过可以分配的内存大小时，会把查询的中间数据存放在Tempdb数据库中，这将增加I/O操作，导致语句的性能大大下降
　　在进行排序操作时，如果工作区内存不足，就需要使用Tempdb数据库来完成数据的排序，此时排序操作产品的部分中间数据将被写入Tempdb中，
　　由于有了磁盘I/O操作的开销，排序操作将受到影响，这时不在由单纯的内存操作那样快速了，而且当Tempdb比较繁忙时，若并发量达到一定量级，也会对排序操作造成影响

分组优化：
　　Group by 和distinct需要进行哈希或排序计算。与排序相同，哈希计算需要使用大量的内存空间，当工作区内存不足时，会将一部分中间数据存放到Tempdb中。
　　因此，使用哈希计算时同样需要注意数据量的控制。当然，如果有必要，可以在Group by的字段上建立索引，以避免哈希计算

更新优化： 
　　由于update语句对数据加的是更新锁或排他锁，更容易并发阻塞，因此，我们应尽量保证更新语句高效，以减少阻塞的影响
　　在默认事务级别中，update语句进行查询时会添加更新锁，若找到了需要更新的数据，就会将更新锁转换为排他锁，由于更新锁和排他锁的兼容性弱，
　　在需要较长执行时间的更新语句或事务中，容易造成阻塞。为了避免造成大量阻塞，应尽量保证更新语句的效率，适当建立索引。
　　长时间的更新维护通常会造成表被锁，从而影响业务功能，无法正常运作，不得不停机操作，解决办法：预先计算好结果，然后分批更新，这样就可避免在更新物理表时再进行计算，从而影响更新时间

过滤条件：
　　是否有合适索引可供使用
　　字段上是否有函数计算
　　返回的结果集是否过大
　　是否紧查询需要的字段

在语句存在性能瓶颈下，如果有扫描操作或者是标签查找操作，都会被认为是有问题的，问题产生的原因是索引缺少，或者索引没有真正的被覆盖到语句中。

查询设计原则
　　使查询结果尽可能小.例如：Top ，分页
　　返回大量的查询结果可能意味着需求存在问题
　　尽量避免表扫描和索引扫描
　　充分使用索引
　　扫描并不总是有害的

尽量使用有索引的字段进行排序，特别是排序的数据量比较大时，这可以很大程度上降低排序操作带来的成本消耗

索引：

索引的选择：
　　1.超高频率的查询，以及低频率的数据更新，插入，删除（索引覆盖，如果覆盖索引含比较多的字段，可以使用包含索引来解决:include关键字）

　　　　对于这种情况除了索引还有其他解决办法：读写分离或者开启数据库的行版本控制功能
　　2.超高频率的查询，以及超高频率的数据更新

　　　　若是高频率的查询需求，通常会因为更新的频率过高，而导致存在一定的阻塞，或者产生死锁, 其他解决办法：

　　　　　　1.创建索引时避免让高频率的更新字段成为索引的一部分；

　　　　　　2.读写分离；

　　　　　　3.根据需求将表纵向拆分成多个窄表

索引覆盖：覆盖索引通常都是复合索引，即：索引字段有多个
　　1.查询字段
　　2.过滤字段
　　3.关联字段：链接两个表的字段，on后的字段
　　索引覆盖可以考虑onclude关键字

　　例如：复合索引 IX_tbTable_Index ，包含字段column1,column2,column3三个字段

　　　　　当查询使用column1时，被识别

　　　　　当查询使用column1,column2时，被识别

　　　　 当查询只使用column2时，将无法使用索引

书签查找：

　　当一条SQL通过非聚集索引找到请求的数据，如果查询还需要返回除索引键以外的字段，那么这个查询很有可能使用书签查找来查找其他字段，书签查找分为：建查找和RID查找
　　对于书签查找的可以使用索引覆盖

　　在使用非覆盖索引时，对于不在索引中输出的字段，将引起bookmark loop的操作，在一些高性能要求的场景中，bookmark loop往往是造成性能问题的一个主要因素，在这种情况下，

　　覆盖索引可以考虑使用include关键字将输出字段包含在叶子节点中，从而避免bookmark loop

seek操作针对的是筛选率高的小数据量返回的情况，如果所筛选的数据量很大，查询优化器发现bookmark loop操作会给当前查询带来很大的成本负担，会进行复杂的成本计算和评估，然后可能使用Scan操作以避免过多的成本消耗，解决办法如下：

　　1.修改当前索引，将不包含的字段列入索引中，即将该字段直接加到索引中，使索引成为一个更大的复合索引

　　2.使用include子句将需要查下出的字段包含在索引的叶子结点中，以避免bookmark loop的额外操作，这种情况要根据具体需求，权衡利弊之后再做决定

索引查询的数据量要尽可能的小，否则会对进行全表扫描操作

索引覆盖语法
　　Create nonclustered index IX_tbTest_UserID 
　　ON tbTest(UserID) 
　　INCLUDE (Name,AddTime);

创建索引：[unique] [clustered] [nonclustered]表示要创建索引的类型，以此为唯一索引，聚集索引，和非聚集索引
　　create nonclustered index IX_Product_Name_Class 
　　on Production.Product(Name,Class)

查询索引：EXEC Sp_helpindex table_name

删除索引：drop Index tbTest.IX_tbTest_UserID_Name

set statistics io on
set statistics profile on
set statistics time on
select * from Table
set statistics time off
set statistics profile off
set statistics io off