《CH10.数据库优化PPT演示课件》由会员分享，可在线阅读，更多相关《CH10.数据库优化PPT演示课件（53页珍藏版）》请在金锄头文库上搜索。
1、第十章第十章 数据库性能优化数据库性能优化提纲n nSQL语句对效率的影响n n索引与查询性能n n事务与锁管理n n硬盘子系统设计n n其它应注意的问题1SQL语句对效率的影响语句对效率的影响n nWHERE子句的规范n nSQL的注意事项2WHERE子句的规范子句的规范n nWHERE子句常犯的错误n n对数据字段做运算对数据字段做运算n n负向查询负向查询n n对数据字段使用函数对数据字段使用函数n n使用使用OROR运算符运算符3不要对数据字段做运算不要对数据字段做运算n n无运算的字段可以引用索引，有运算的字段将无法引用索引进行优化而需要扫描整个表n n示例n n比较下列语法的差异：
2、比较下列语法的差异：SELECT * FROM Order Details WHERE Quantity=100SELECT * FROM Order Details WHERE Quantity=100SELECT SELECT * * FROM FROM Order Order Details Details WHERE WHERE Quantity+1 Quantity+1 =101=101n n还包括其它的运算，如字符连接等还包括其它的运算，如字符连接等4不要使用负向查询不要使用负向查询n n负向查询：负向查询：n nNOTNOT、!=!=、!、NOT NOT EXISTSEXISTS
3、、NOT NOT ININ、NOT NOT LIKELIKE等等n n负负向向查查询询不不能能充充分分利利用用索索引引进进行行二二分分查查找找，需需要要扫扫描描整张表整张表n n示例示例n nSELECT * FROM Order Details WHERE Quantity!=100SELECT * FROM Order Details WHERE Quantity!=100可改成：可改成：n nSELECT SELECT * * FROM FROM Order Order Details Details WHERE WHERE Quantity100 Quantity100 OR Quan
4、tity100OR Quantity1005不对数据字段使用函数不对数据字段使用函数n n数据字段使用函数就是一种运算，将使效率低数据字段使用函数就是一种运算，将使效率低n n比较：比较：n nSELECT SELECT * * FROM FROM Order Order Details Details WHERE WHERE ABS(Quantity-ABS(Quantity-100)1100)99 Quantity99 AND Quantity101AND Quantity101n nSELECT SELECT * * FROM FROM Employees Employees WHERE
5、 WHERE SUBSTRING SUBSTRING (LastName,1,1)=D(LastName,1,1)=D SELECT SELECT * * FROM FROM Employees Employees WHERE WHERE LastName LastName LIKE LIKE D%D%6使用使用OR运算符运算符n nANDAND运算符可以充分引用索引运算符可以充分引用索引n nSELECT SELECT * * FROM FROM Orders Orders WHERE WHERE CustomerID=IS10008 CustomerID=IS10008 AND Order
6、Date=20060808AND OrderDate=20060808n n只需要在只需要在CustomerIDCustomerID上建索引就可以了上建索引就可以了n nOROR运运算算符符需需要要对对参参与与查查询询的的多多个个字字段段都都建建索索引引，否否则将可能扫描全表则将可能扫描全表n nSELECT SELECT * * FROM FROM Orders Orders WHERE WHERE CustomerID=IS10008 CustomerID=IS10008 OR OrderDate=20060808OR OrderDate=20060808n n需需要要在在Customer
7、IDCustomerID和和OrderDateOrderDate两两个个属属性性上上都都建建合合适适的的索索引引, ,否则将扫描整个数据表否则将扫描整个数据表7SQL的注意事项的注意事项n nSELECT语法n n尽尽量量不不要要传传回回数数据据表表的的所所有有字字段段，也也不不要要不不使使用用过滤条件，否则将极大地增加网络负担过滤条件，否则将极大地增加网络负担n n若若使使用用复复合合索索引引，索索引引顺顺序序上上的的第第一一个个字字段段才才适适合当作过滤条件合当作过滤条件n nDISTINCTDISTINCT、ORDER ORDER BYBY等等语语法法尽尽量量等等到到查查询询需需要要时才
8、使用，因为它们需要时才使用，因为它们需要SQL SERVERSQL SERVER的额外计算的额外计算8SQL的注意事项的注意事项n n大量数据加载大量数据加载n n大大量量加加载载某某个个数数据据表表时时，应应考考虑虑先先删删掉掉索索引引，加加载载完完毕毕再再重重建建索索引引（特别是多个用户端同时在做大量数据加载时）（特别是多个用户端同时在做大量数据加载时）n nBULK INSERTBULK INSERT语法通常比语法通常比bcpbcp工具程序快工具程序快n n大大量量数数据据加加载载时时，应应设设参参数数采采用用数数据据表表锁锁定定，而而不不要要采采用用默默认认的的记记录锁录锁n n如如果
9、果数数据据表表的的记记录录需需要要先先做做转转换换，应应先先导导入入临临时时表表中中，经经过过处处理理再再大量加载到目的数据表中大量加载到目的数据表中n nINSERTINSERT、DELETEDELETE和和UPDATEUPDATEn n对大量数据，对大量数据，SELECT INTOSELECT INTO比比INSERTINSERT快快n n对大量数据，对大量数据，TRUNCATE TABLETRUNCATE TABLE比比DELETE TABLEDELETE TABLE快快n nUPDATEUPDATE和和DELETEDELETE采采用用WHEREWHERE子子句句时时，条条件件要要符符合
10、合WHEREWHERE的的有有效效格格式式9索引与查询性能索引与查询性能n n索引及其相关属性配置索引及其相关属性配置n n聚集索引与非聚集索引聚集索引与非聚集索引n n排序排序n nSysindexesSysindexes系统数据表系统数据表n n索引是否值得索引是否值得n n统计统计n n联结与查询效率联结与查询效率n n覆盖索引覆盖索引n n在视图与计算字段上建索引在视图与计算字段上建索引n n数据不连续的处理数据不连续的处理10索引及相关属性配置索引及相关属性配置n n索引是有效使用数据库系统的基础n n索引建立是否适当是性能好坏的成功关键n n索引数据放在分页中，用来当做索引的数据字
11、段越小越好，也就是让分页尽量存放更多的索引项n n索引结点有三种结构：n n根结点分页根结点分页n n叶子层叶子层n n非叶子层非叶子层11索引及相关属性配置索引及相关属性配置n n创建索引的语法格式：n nCREATE INDEX CREATE INDEX 索引名索引名 ON ON 表名（列名）表名（列名）n n建索引时，可根据不同的需求进行选项配置：n nFILLFACTORFILLFACTOR（填充因子）填充因子）n n在在建建（包包括括重重建建）索索引引时时，保保留留部部分分空空间间让让随随后后的的新新建建、修改可直接利用这些空间修改可直接利用这些空间n n需需要要立立刻刻对对某某个个
12、数数据据表表更更新新所所有有的的索索引引，最最简简单单的的方方式式是是通通过过DBCC DBCC DBREINDEXDBREINDEX命命令令重重建建该该数数据据表表的的聚聚集集索索引引，则所有的非聚集索引都会同时自动更新则所有的非聚集索引都会同时自动更新n nIGNORE_DUP_KEYIGNORE_DUP_KEYn n对对于于唯唯一一索索引引，当当插插入入多多条条记记录录（包包括括重重复复记记录录）时时，若若建建索索引引没没有有配配置置该该选选项项，将将全全部部回回滚滚，否否则则仅仅放放弃弃重重复记录复记录12索引及相关属性配置索引及相关属性配置n n选项配置：（续）n nDROP_EXI
13、STINGDROP_EXISTINGn n通通过过配配置置DROP_EXISTINGDROP_EXISTING可可防防止止重重建建聚聚集集索索引引时时一一并并删删除除与与重重建建该该数数据据表表上上所所有有的的非非聚聚集集索索引引，否否则则重重建建聚聚集集索索引引会会导导致致所所有有非非聚聚集集索索引引重重建建一一次次（若若重重建建聚聚集集索索引采用相同的键值）或两次引采用相同的键值）或两次n nSTATISTICS_NORECOMPUTESTATISTICS_NORECOMPUTEn n表表示示与与该该索索引引相相关关的的统统计计信信息息不不需需要要自自动动更更新新，系系统统管管理员会手动更
14、新理员会手动更新n nSORT_IN_TEMPDBSORT_IN_TEMPDBn n若若系系统统的的TEMPDBTEMPDB是是建建立立在在与与该该索索引引不不同同硬硬盘盘的的文文件件组组上上，可可通通过过该该选选项项让让临临时时空空间间利利用用另另一一个个或或一一组组TEMPDBTEMPDB所所在在的的硬硬盘盘来来做做键键值值排排序序，以以提提升升建建立立索索引引时时的性能的性能13聚集索引与非聚集索引聚集索引与非聚集索引n n聚集索引聚集索引n n对对聚聚集集索索引引，数数据据表表本本身身就就是是索索引引的的一一部部分分，是是聚聚集集索索引引的叶子层，整个数据表的摆放顺序按索引项由小到大排
15、序的叶子层，整个数据表的摆放顺序按索引项由小到大排序n n聚集索引的优点聚集索引的优点n n如如果果记记录录较较小小，则则在在记记录录访访问问中中有有可可能能可可以以减减少少磁磁盘盘存存取取的的次次数数；聚聚簇簇索索引引有有利利于于多多点点查查询询，因因为为值值相相同同的的记记录录放放在在了了一一起起（一一个个页页内内），这这样样一一次次磁磁盘盘访访问问就就可可以以了了，如如果果是是非非聚聚簇簇索索引引，因因为为可可能能存在不同的页上，可能需要好几次磁盘访问。存在不同的页上，可能需要好几次磁盘访问。n n聚簇索引有助于在不同值较少的属性上进行的等值连接；聚簇索引有助于在不同值较少的属性上进行的
16、等值连接；n n基基于于B-B-树树结结构构的的聚聚簇簇索索引引，可可以以很很好好的的支支持持范范围围查查询询、前前缀缀匹匹配配查查询和排序查询。询和排序查询。n n节节省省存存储储空空间间。聚聚簇簇以以后后，聚聚簇簇码码相相同同的的元元组组集集中中在在一一起起了了，因因而而聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了 14聚集索引与非聚集索引聚集索引与非聚集索引n n聚集索引聚集索引n n聚集索引的缺点聚集索引的缺点n n建立与建立与维护聚簇的开销相当大。维护聚簇的开销相当大。n n如果存在大量的溢出数据页，它的性能会下
17、降很快。如果存在大量的溢出数据页，它的性能会下降很快。n n原因：访问这些页面的磁盘定位需要花费很多时间。原因：访问这些页面的磁盘定位需要花费很多时间。n n非聚集索引非聚集索引n n非聚集索引完全独立于数据表之外非聚集索引完全独立于数据表之外n n一个数据表可建立一个数据表可建立249249个索引（具体应用时一般不超过个索引（具体应用时一般不超过1010个）个）n n当当查查询询条条件件的的选选择择性性不不高高，即即符符合合条条件件的的记记录录占占很很小小比比例例时，通过非聚集索引查询效率非常低。时，通过非聚集索引查询效率非常低。n n适合对精确匹配，以及搜寻结果集很小的查询适合对精确匹配，
18、以及搜寻结果集很小的查询15聚集索引与非聚集索引聚集索引与非聚集索引n n聚集索引的选择至关重要n n聚集索引的索引项应该具有以下特性：n n数据格式为整数数据格式为整数n n本身就唯一本身就唯一n n不可为不可为NULLNULLn n字段值不能太大字段值不能太大n n若若选选择择聚聚集集索索引引的的字字段段值值很很大大，则则整整个个数数据据表表的的各各种种索索引引都都将将会会变变得得低低效效，因因为为所所有有的的非非聚聚集集索索引引的的叶叶子子层层都都会因为纳入聚集索引的键值而变大会因为纳入聚集索引的键值而变大16排序排序n n组织数据时需要排序的情况n nGROUP BYGROUP BY、
19、DISTINCTDISTINCT、ORDER BYORDER BY、TOPTOP等子句等子句n n虽虽然然这这些些子子句句只只是是查查询询结结果果的的产产生生方方式式，但但抽抽取取与显示都需要耗费系统资源与显示都需要耗费系统资源n n预先排序的数据n n要要使使用用索索引引有有效效地地排排序序查查询询数数据据，最最直直接接的的方方式式就是在要排序的字段上建立聚集索引。就是在要排序的字段上建立聚集索引。n n索引顺序n nSQL SQL SERVERSERVER可可使使用用相相同同的的聚聚集集索索引引做做升升序序和和降降序序排排序序，因因为为叶叶子子层层的的分分页页存存储储都都以以双双向向连连接
20、接串串行行方式连接在一起方式连接在一起17排序示例排序示例n n聚集索引可以自动正反扫描 CREATE CREATE CLUSTERED CLUSTERED INDEX INDEX idx_LastName idx_LastName ON ON member(LastName) WITH DROP_EXISTINGmember(LastName) WITH DROP_EXISTING查询：查询： SELECT * FROM Member ORDER BY lastname SELECT * FROM Member ORDER BY lastname SELECT SELECT * * FROM
21、 FROM Member Member ORDER ORDER BY BY lastname lastname DESCDESC 效果一样效果一样18排序示例排序示例n n多关键字聚集索引CREATE CREATE CLUSTERED CLUSTERED INDEX INDEX idx_LastName idx_LastName ON ON member(LastName member(LastName ASC,FirstName ASC,FirstName DESC) DESC) WITH WITH DROP_EXISTINGDROP_EXISTING查询：查询： SELECT SELECT
22、 * * FROM FROM Member Member ORDER ORDER BY BY lastname lastname ASC FirstName DESCASC FirstName DESC19利用利用Sysindexes系统数据表进行分析系统数据表进行分析n n在在SQL SQL SERVERSERVER中中，每每个个数数据据库库都都有有一一个个SysindexesSysindexes系统数据表，用来存放数据库内所有的索引细节。系统数据表，用来存放数据库内所有的索引细节。n n在在建建立立聚聚集集索索引引或或非非聚聚集集索索引引后后可可查查询询SysindexesSysindex
23、es表表的的 数数 据据 ， 也也 可可 以以 直直 接接 利利 用用 系系 统统 存存 储储 过过 程程sp_spaceusedsp_spaceused查看数据表或索引所使用的存储空间。查看数据表或索引所使用的存储空间。n nSysindexesSysindexes 数数据据表表的的usedused字字段段：聚聚集集索索引引（非非聚聚集集索索引引）已使用的总分页数已使用的总分页数n nSysindexesSysindexes 数数据据表表的的dpagedpage字字段段：聚聚集集索索引引中中的的实实际际子子叶叶，即即数数据据表表本本身身所所占占的的页页数数（非非聚聚集集索索引引中中叶叶子子层
24、层所所占占的的页页数）数）20索引是否值得索引是否值得n n索引可以大大提高查询效率，若索引建少了，查找数据就效率低下n n索引建得太多则不利于插删改操作n n针对SQL语法或数据类型查看是否值得建索引时，可参考的方面：n n选择性选择性n n选择性指符合查询条件的记录占总记录的百分比。选择性指符合查询条件的记录占总记录的百分比。n n选择性越高，即该值越小，才越适合建索引选择性越高，即该值越小，才越适合建索引n n在在选选择择性性很很低低时时，通通过过非非聚聚集集索索引引存存取取是是非非常常没没有有效效率率的存取方式，还不如直接做数据表扫描的存取方式，还不如直接做数据表扫描21索引是否值得索
25、引是否值得n n是否值得建索引所参考的方面：(续)n n数据密度数据密度n n数据密度为键值唯一的记录笔数的倒数数据密度为键值唯一的记录笔数的倒数n n数据密度越小，该字段越适合建立索引数据密度越小，该字段越适合建立索引n n平均查询到的记录数平均查询到的记录数= =数据密度数据密度* *总记录数总记录数n n数据分布数据分布n n数据分布表示多笔数据记录组成的方式数据分布表示多笔数据记录组成的方式n n表表示示数数据据记记录录是是平平均均散散布布在在一一段段范范围围中中还还是是集集中中在在部部分分区块区块n n如均匀分布，正态分布等，需进一步确定其选择性如均匀分布，正态分布等，需进一步确定其
26、选择性22查看查询语法所使用的资源查看查询语法所使用的资源n n配置配置SET STATISTICSSET STATISTICS选项选项n n在查询分析器中配置，在在查询分析器中配置，在SQLSQL语句执行时返回语句执行时返回n n语法：语法：SET STATISTICS ONSET STATISTICS ONn nIOIO：返返回回扫扫描描次次数数（表表或或索索引引存存取取次次数数）、逻逻辑辑读读入入（缓缓冲冲区区读读取取页页数数）、物物理理读读取取（磁磁盘盘读读取取块块数数）、先先读读读读入入（先先读机制预先将数据放到缓存）读机制预先将数据放到缓存）n nTIMETIME：包括包括SQL
27、SERVERSQL SERVER分析与编译时间、服务器执行时间分析与编译时间、服务器执行时间n nPROFILEPROFILE：最最优优化化程程序序如如何何执执行行SQLSQL语语法法的的结结果果集集（执执行行计计划）划）n n使用使用SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT选项查看查询计划选项查看查询计划n n语法：语法： SET SHOWPLAN_TEXT ONSET SHOWPLAN_TEXT ONn n返回将要执行的查询计划，不会真正执行查询返回将要执行的查询计划，不会真正执行查询n nSTATISTICS IOSTATISTICS IO与与SHOWPLAN
28、_TEXT SHOWPLAN_TEXT 是互斥是互斥23建立最优执行计划的各阶段建立最优执行计划的各阶段n n最最优优化化程程序序的的主主要要工工作作是是将将没没有有执执行行步步骤骤、以以集集合合为为基础的基础的SQLSQL语法转换成有效率的可执行步骤语法转换成有效率的可执行步骤n n建立执行计划的过程：建立执行计划的过程：n n一般计划的最优化一般计划的最优化n n评估是否缓存中已经存在以前建立且当前可用的执行计划评估是否缓存中已经存在以前建立且当前可用的执行计划n n对显而易见的查询要求直接建立执行计划对显而易见的查询要求直接建立执行计划n n如如INSERT INSERT VALUESV
29、ALUES或或SELECTSELECT的的字字段段都都包包含含在在某某个个索索引引内内, ,且且没没有有其他合适的索引等其他合适的索引等n n单一化单一化n n单单一一化化主主要要做做语语句句转转换换，找找到到语语法法上上最最有有效效的的执执行行方方式式，处处理理一一些些不需要通过索引成本分析就可以决定有效执行步骤的工作不需要通过索引成本分析就可以决定有效执行步骤的工作n n加载统计加载统计n n多层次的以成本为基础的最优化多层次的以成本为基础的最优化n n最优化程序通过统计数据计算多种执行方式的成本进行选择最优化程序通过统计数据计算多种执行方式的成本进行选择24统计统计n n统计记录着数据内
30、容的分布统计记录着数据内容的分布n n可以针对索引或数据的某个字段建立统计可以针对索引或数据的某个字段建立统计n n查查询询优优化化程程序序可可依依据据数数据据分分布布的的统统计计信信息息完完成成下下列列工工作：作：n n可获取某个索引对查询的选择性如何可获取某个索引对查询的选择性如何n n能分析索引的执行成本高低能分析索引的执行成本高低n n从而建立最佳的执行计划从而建立最佳的执行计划n nSQL SERVERSQL SERVER获取统计的两种方式：获取统计的两种方式：n n完全扫描数据表：与建立索引时一并建立统计完全扫描数据表：与建立索引时一并建立统计n n抽抽样样分分析析：未未建建索索引
31、引的的字字段段建建立立统统计计，或或更更新新已已经经存存在在的的统计时统计时25统计统计n n统计数据记录sysindexes系统表的statblob字段中（image格式）n n查看统计数据的语法：DBCC SHOW_STATISTICS(DBCC SHOW_STATISTICS(表名表名, ,统计信息的目标统计信息的目标) )n n示例：n n在查询分析器运行获得完全扫描方式的统计信息在查询分析器运行获得完全扫描方式的统计信息CREATE INDEX idx_product_no ON Product(PNO)CREATE INDEX idx_product_no ON Product(P
32、NO)DBCC DBCC SHOW_STATISTICS(Product, SHOW_STATISTICS(Product, idx_product_noidx_product_no) )26统计统计n n统计结果以表格的形式显示，包括三部分：统计结果以表格的形式显示，包括三部分：n n第一部分：第一部分：n n索引最后被更新的时间索引最后被更新的时间( (UpdatedUpdated字段字段) )n n统计数据来源记录数统计数据来源记录数( (RowRow字段字段) )n n抽样记录数抽样记录数( (Row SampledRow Sampled字段字段) )n n分布组数分布组数( (Ste
33、psSteps字段字段) )n n数据密度（数据密度（DensityDensity字段）字段）n n键值平均长度键值平均长度n n第二部分：多个键值字段各自的统计数据第二部分：多个键值字段各自的统计数据n n第第三三部部分分：各各统统计计字字段段对对应应分分布布组组的的详详细细统统计计信信息息。起起字字段包括：段包括：n nRANGE_HI_KEY(RANGE_HI_KEY(涵涵盖盖记记录录上上限限值值) ) 、RANGE_ROW(RANGE_ROW(落落在在其其中中的的样样本记录数本记录数) )、EQ_ROWS(StepEQ_ROWS(Step值的样本记录数值的样本记录数) )n nDIST
34、INCT_RANGE_ROWSDISTINCT_RANGE_ROWS、AVG_RANGE_ROWSAVG_RANGE_ROWS27更新统计更新统计n n更新统计的方式有两种：手动更新、自动更新更新统计的方式有两种：手动更新、自动更新n n手动更新：手动更新：n n利利用用CREATE CREATE STATISTICSSTATISTICS对对未未建建索索引引的的字字段段直直接接产产生生统统计计信息信息n n利用利用sp_createstatssp_createstats存储过程对字段建立统计信息存储过程对字段建立统计信息n n利用利用UPDATE STATISTICSUPDATE STATIS
35、TICS更新某个统计更新某个统计n n利用利用sp_updatestatssp_updatestats更新统计更新统计n n手动更新的时机：手动更新的时机：n n索索引引中中的的键键值值有有大大量量的的新新建建、修修改改或或删删除除，而而马马上上要要用用到到该索引该索引n n通通过过TRUNCATE TRUNCATE TABLETABLE语语法法清清空空某某个个重重新新装装入入数数据据，而而又又要立即存取要立即存取n n一般情况一般情况SQL SERVERSQL SERVER能自动维护统计信息（属性要配置）能自动维护统计信息（属性要配置）28联结与查询效率联结与查询效率n n查查询询优优化化程
36、程序序决决定定联联结结的的执执行行方方式式时时，需需要要确确定定以以下下内容：内容：n n数据表之间联结最佳的先后关系数据表之间联结最佳的先后关系n n两两联结时找出合适的内层数据表和外层数据表两两联结时找出合适的内层数据表和外层数据表n n决定联结算法：嵌套循环连接、合并连接、哈希连接决定联结算法：嵌套循环连接、合并连接、哈希连接n n嵌套循环连接嵌套循环连接n n外外部部循循环环找找到到符符合合条条件件的的记记录录后后，逐逐列列要要求求内内部部循循环环搜搜寻寻符合的数据列。符合的数据列。n n适适合合只只影影响响一一小小部部分分数数据据记记录录的的查查询询，或或外外部部输输入入相相当当小小
37、，内部输入已建索引，且数据记录相当大的情况。内部输入已建索引，且数据记录相当大的情况。29联结与查询效率联结与查询效率n n合并连接合并连接n n要求两边参与连接的输入数据必须先排序要求两边参与连接的输入数据必须先排序n n如满足上述条件，合并连接的效率最高如满足上述条件，合并连接的效率最高n n哈希连接哈希连接n n前两种连接不合用时，才考虑此连接前两种连接不合用时，才考虑此连接n n建立两个输入：组建输入和探查输入建立两个输入：组建输入和探查输入n n组组建建输输入入将将符符合合条条件件但但数数据据较较少少的的表表的的字字段段值值计计算算得得到到哈哈希表放在内存中（相同键值记录链接起来）及
38、哈希桶希表放在内存中（相同键值记录链接起来）及哈希桶n n扫扫描描整整个个探探查查输输入入，计计算算哈哈希希值值，扫扫描描哈哈希希桶桶，产产生生符符合合项项n n哈哈希希连连接接用用于于集集合合对对比比作作业业：内内部部连连接接、外外连连接接、半半连连接接、交集等交集等n n在没有索引的情况下，在没有索引的情况下，SQL SERVERSQL SERVER默认哈希连接默认哈希连接30覆盖索引覆盖索引n n聚集索引的好处：n nSQL SQL SERVERSERVER找找到到正正确确的的索索引引键键值值后后不不需需要要再再用用指指针做额外的搜寻针做额外的搜寻n nSQL SERVERSQL SER
39、VER将符合相同条件的数据集中放在一起将符合相同条件的数据集中放在一起n n聚集索引只能建一个，非聚集索引有很多个（最多可达249个）n n非聚集索引只能在传回数据量占总数比例很少时才有用n n引入覆盖索引（一种的非聚集复合索引）可同样具有聚集索引的两个好处31覆盖索引覆盖索引n n覆覆盖盖索索引引是是指指那那些些索索引引项项中中包包含含查查询询所所需需要要的的全全部部信信息的非聚簇索引息的非聚簇索引n n可以是单索引或复合索引，但是一般都是非聚簇的。可以是单索引或复合索引，但是一般都是非聚簇的。n n覆覆盖盖索索引引之之所所以以比比较较快快是是因因为为索索引引页页中中包包含含了了查查询询所所
40、必须的数据，不需去访问数据页。必须的数据，不需去访问数据页。n n如如果果非非聚聚簇簇索索引引中中包包含含结结果果数数据据，那那么么它它的的查查询询速速度度将快于聚簇索引。将快于聚簇索引。n n什么时候建覆盖索引什么时候建覆盖索引n n经经常常同同时时存存取取多多列列，且且每每列列都都含含有有重重复复值值可可考考虑虑建建立立复复合合索索引引来来覆覆盖盖一一个个或或一一组组查查询询，并并把把查查询询引引用用最最频频繁繁的的列列作作为前导列。为前导列。n n经常查询涵盖经常查询涵盖GROUP BYGROUP BY或或ORDER BYORDER BY子句的字段子句的字段n n如果可能尽量使关键查询形
41、成覆盖查询。如果可能尽量使关键查询形成覆盖查询。 32覆盖索引覆盖索引n n建立覆盖索引的语法：建立覆盖索引的语法：n nCREATE INDEX CREATE INDEX 索引名索引名 ON ON 表名表名( (字段字段1 1，字段，字段2 2，)n n示例示例EXEC spCleanIdx MemberEXEC spCleanIdx MemberCREATE CREATE INDEX INDEX idx_LastFirstName idx_LastFirstName ON ON Member Member (Lastname(Lastname、Firstname)Firstname)SEL
42、ECT SELECT lastname,firstname lastname,firstname FROM FROM Member Member WHERE WHERE lastname BETWEEN Funk AND Langlastname BETWEEN Funk AND Langn n 注意：注意：n n建建立立覆覆盖盖查查询询时时尽尽量量限限制制索索引引项项的的大大小小，保保持持ROW/KEYROW/KEY越越大大越越好好，否否则则扫扫描描覆覆盖盖索索引引与与扫扫描描数数据据表表所所花花的的I/OI/O分分页页差差不多，就失去的覆盖索引的意义不多，就失去的覆盖索引的意义33组合索引
43、组合索引n n组合索引就是指建立在多个属性上的索引。n n组合索引可以是聚簇的，也可以是非聚簇的。组合索引可以是聚簇的，也可以是非聚簇的。n n比较在单个属性上建立的索引，组合索引具有以下优势：n n支支持持前前缀缀匹匹配配查查询询，支支持持的的前前缀缀就就是是组组合合索索引引（A A，B B，）的形式。的形式。n n更更易易覆覆盖盖查查询询条条件件，有有时时一一个个稠稠密密的的组组合合索索引引就就可可以以完完全全回回答答查查询询。例例如如查查姓姓为为“罗罗”，名名为为“强强”的人有多少个。的人有多少个。n n组合索引是支持多属性唯一性的一个有效办法组合索引是支持多属性唯一性的一个有效办法34
44、组合索引组合索引n n设计一个组合索引时，必须注意组合索引的顺序n n如如果果查查询询更更倾倾向向于于在在属属性性A A而而不不是是在在属属性性B B上上加加限限定定词词的的话话，那那么么应应该该建建立立把把A A放放在在B B前前面面的的组组合合索索引。引。 n n组合索引的缺点：n n它它们们趋趋向向于于比比较较长长的的索索引引键键。如如果果不不使使用用压压缩缩方方法，这会引起法，这会引起B-B-树树 层数的增加。层数的增加。n n因因为为组组合合索索引引包包含含多多个个属属性性，所所以以对对其其中中任任何何属属性性的的更更新新都都会会导导致致索索引引的的更更新新，组组合合索索引引的的维维
45、护护代价将会是比较高的。代价将会是比较高的。35在视图与计算字段上建索引在视图与计算字段上建索引n n为视图建索引能让逻辑的数据物理化n n为视图建立的第一个索引一定是聚集以及唯一索引n n 聚聚集集 是是让让索索引引的的叶叶子子层层可可以以涵涵盖盖视视图图定定义义的的所有记录所有记录n n 唯一唯一 是让索引维护比较方便是让索引维护比较方便n n相当于一个有聚集索引的数据表相当于一个有聚集索引的数据表n n若删除该聚集索引将导致其他索引一起删除若删除该聚集索引将导致其他索引一起删除n n对计算字段可以直接建立非聚集索引，索引需要的是该字段计算后的值36Indexed Viewn nIndex
46、ed View把符合定义的数据建立好另外存放，若视图包含汇总函数，建立索引时即完成汇总计算，当更新数据表时，系统会自动维护视图索引的汇总结果n n通过视图（含索引）查询时n n不需要重新计算汇总，提高性能不需要重新计算汇总，提高性能n n可以不必在查询时才做连接运算，提升性能可以不必在查询时才做连接运算，提升性能n n如如果果偏偏向向联联机机事事务务处处理理（绝绝大大多多数数运运算算是是插插删删改改），反而因为要维护索引而降低效率，反而因为要维护索引而降低效率37Indexed Viewn n示例示例CREATE VIEW Vdiscount WITH SCHEMABINDINGCREATE
47、VIEW Vdiscount WITH SCHEMABINDINGASASSELECT SUM(UnitPrice*Quatity*Discount) SumDiscountpriceSELECT SUM(UnitPrice*Quatity*Discount) SumDiscountpriceFROM dbo.order detailsFROM dbo.order detailsGROUP BY ProductIDGROUP BY ProductIDGOGOCREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount(ProductID)CRE
48、ATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount(ProductID)n n查询最高折扣款前五名的产品查询最高折扣款前五名的产品SELECT TOP 5 ProductID, SUM(UnitPrice*Quatity*Discount) SumDisSELECT TOP 5 ProductID, SUM(UnitPrice*Quatity*Discount) SumDisFROM order details FROM order details GROUP BY ProductIDGROUP BY ProductIDORDER BY S
49、umDisORDER BY SumDisn n考察查询最高销售额前五名的产品考察查询最高销售额前五名的产品38Indexed Viewn n查询最高销售额前五名的产品n n解决方法CREATE VIEW Vdiscount WITH SCHEMABINDINGCREATE VIEW Vdiscount WITH SCHEMABINDINGASASSELECT SUM(UnitPrice*Quatity) Sumprice,SELECT SUM(UnitPrice*Quatity) Sumprice, SUM(UnitPrice*Quatity*(1-Discount) SumDiscount,
50、 SUM(UnitPrice*Quatity*(1-Discount) SumDiscount, SUM(UnitPrice*Quatity*Discount) SumDiscountprice SUM(UnitPrice*Quatity*Discount) SumDiscountpriceFROM dbo.order detailsFROM dbo.order detailsGROUP BY ProductIDGROUP BY ProductIDGOGOCREATE CREATE UNIQUE UNIQUE CLUSTERED CLUSTERED INDEX INDEX VDiscountI
51、nd VDiscountInd ON ON Vdiscount(ProductID)Vdiscount(ProductID)n n考察求平均值（AVG）的情况n n增加子句增加子句SUM(Quatity) UnitsSUM(Quatity) Units39Indexed View的适用范围的适用范围n n适合建立适合建立Indexed ViewIndexed View的情况：的情况：n n减低决策支持查询的负载减低决策支持查询的负载n n对大型数据表做连接以及汇总运算对大型数据表做连接以及汇总运算n n重复同一种模式的查询重复同一种模式的查询n n对某些字段重复做汇总运算对某些字段重复做汇总运
52、算n n重复对相同的数据表、相同的属性做连接重复对相同的数据表、相同的属性做连接n n以上方式的综合使用以上方式的综合使用n n不适合建立不适合建立Indexed ViewIndexed View的情况：的情况：n n经常进行插删改的经常进行插删改的OLTPOLTP系统系统n n大量数据字段结合在一起的连接大量数据字段结合在一起的连接n n与原始数据表内容差不多大的与原始数据表内容差不多大的Indexed ViewIndexed View40数据不连续的处理数据不连续的处理n n数据经过插删改会造成不连续数据经过插删改会造成不连续n n数据不连续分两种：数据不连续分两种：n n内部不连续：物理
53、分页中有许多空间没有记录内部不连续：物理分页中有许多空间没有记录n n外外部部不不连连续续：磁磁盘盘分分页页与与扩扩展展分分页页不不连连续续，即即索索引引或或数数据据表可能散落在多个扩展分页中，使得其在物理上不连续。表可能散落在多个扩展分页中，使得其在物理上不连续。n n数数据据不不连连续续会会使使硬硬盘盘读读取取无无效效率率，而而且且读读出出来来的的数数据据还需要重新整理还需要重新整理n n索引需要空间时需要做分割操作索引需要空间时需要做分割操作n n外外部部不不连连续续只只在在做做大大量量数数据据扫扫描描时时才才影影响响效效率率，若若只只搜索某些记录，利用索引指针就可取得分页搜索某些记录，
54、利用索引指针就可取得分页41数据不连续的处理数据不连续的处理n n可以执行DBCC SHOWCONTIG指令得到数据表的不连续状况。n n数据不连续的处理：n n利利用用DBCC DBCC INDEXDEFRAGINDEXDEFRAG移移除除逻逻辑辑扫扫描描的的外外部部不不连续状况连续状况n n重建索引可以移除所有的不连续状况重建索引可以移除所有的不连续状况n n若若只只是是见见聚聚集集索索引引，最最好好搭搭配配CREATE CREATE INDEX INDEX WITH DROP_EXISTINGWITH DROP_EXISTING42事务与锁管理事务与锁管理n n锁n n死锁n n观察与分
55、析系统的锁定情况n n产生阻塞的原因43锁锁n nSQL SERVER中锁的种类：n n共享锁共享锁n n排他锁排他锁n n更新锁更新锁n n意向锁意向锁n n锁的相容性n n可锁定的资源n n数据库、文件、索引数据库、文件、索引n n数据表、分页、索引键值数据表、分页、索引键值n n数据行、应用程序等数据行、应用程序等44锁锁n n锁与事务的四个隔离等级：锁与事务的四个隔离等级：n nREAD UNCOMMITTEDREAD UNCOMMITTEDn nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET TRANSACTION ISOLAT
56、ION LEVEL READ UNCOMMITTEDn n可能读到脏数据可能读到脏数据n nREAD COMMITTEDREAD COMMITTEDn nSET TRANSACTION ISOLATION LEVEL READ COMMITTEDSET TRANSACTION ISOLATION LEVEL READ COMMITTEDn nSQL SERVERSQL SERVER默认配置默认配置n n不能读到脏数据，但不可重复读不能读到脏数据，但不可重复读n nREPEATABLE READREPEATABLE READn n可重复读可重复读n nSERIALIZABLESERIALIZAB
57、LEn n防止幻象现象防止幻象现象当当需需要要事事务务的的正正确确性性，就就会会提提高高事事务务的的隔隔离离等等级级，但但会会让并发度减低让并发度减低45死锁死锁n n死锁是在DBMS中的某组资源上发生了两个或多个线程之间循环相关性时，由于各个线程之间互不相让对方所需要的资源而造成的。 n n当当客客户户向向数数据据库库提提交交查查询询后后，客客户户机机可可能能会会感感觉觉到好像到好像“死机死机”了，这就可能是发生了锁争夺了，这就可能是发生了锁争夺n n当当系系统统中中出出现现锁锁争争夺夺的的时时候候，如如果果不不想想让让进进程程永永久久的的等等待待下下去去，解解决决的的办办法法是是通通过过设
58、设置置锁锁超超时时时时间间隔。间间隔。n n可以用可以用SET LOCK_TIMEOUTSET LOCK_TIMEOUT命令设置时间间隔。命令设置时间间隔。n nSQL Server中有循环死锁和转换死锁两大类。 46死锁死锁n n循环死锁n n由由于于系系统统或或用用户户进进程程之之间间彼彼此此都都只只有有得得到到对对方方持持有的资源才能执行时发生有的资源才能执行时发生n n转换死锁n n发发生生在在两两个个或或多多个个进进程程在在事事务务中中持持有有同同一一资资源源的的共共享享锁锁，而而且且都都需需要要将将共共享享锁锁升升级级为为独独占占锁锁，但但都要待其他进程释放这一共享锁时才能升级。都
59、要待其他进程释放这一共享锁时才能升级。n n分布式死锁47观察与分析系统的锁定情况观察与分析系统的锁定情况n n监视和跟踪SQL Server中的锁活动信息常见的方法有：n n使用使用sp_locksp_lock存储过程存储过程 n n使用企业管理器查看锁信息使用企业管理器查看锁信息n n使用使用SQL ProfileSQL Profile查看锁信息查看锁信息48观察与分析系统的锁定情况观察与分析系统的锁定情况n n锁定能造成性能影响，可以从下面几个方面观察系统是否因为锁定与阻塞导致运行问题：n n通通过过企企业业管管理理器器或或系系统统存存储储 过过程程查查看看是是否否有有许许多多进程被封锁
60、不能执行进程被封锁不能执行n nMaster.dbo.sysprocessedMaster.dbo.sysprocessed系系统统数数据据表表内内，被被封封锁锁的进程的的进程的waittimewaittime字段的值异常大字段的值异常大n nSQL SQL ProfilerProfiler工工具具程程序序所所获获取取的的结结果果中中，有有许许多多AttentionAttention事件事件n nSQL SQL SERVERSERVER所所在在的的机机器器并并没没有有显显得得相相当当忙忙碌碌，如如CPUCPU、内内存存或或硬硬盘盘、网网络络等等硬硬件件使使用用率率并并不不很很高高，但但效效率率
61、不不好好。或或某某个个作作业业持持续续高高度度使使用用，但但作作业业一直做不完，导致其占有资源无法释放一直做不完，导致其占有资源无法释放49产生阻塞的原因产生阻塞的原因n n产生阻塞的原因：产生阻塞的原因：n n费时的查询或事务费时的查询或事务n n不正确的事务或事务隔离级别配置不正确的事务或事务隔离级别配置n n嵌套事务未正确处理嵌套事务未正确处理n n未侦测到的分布式死锁未侦测到的分布式死锁n n编译存储过程导致阻塞编译存储过程导致阻塞n n减少锁定对索引的要求减少锁定对索引的要求n n数据表最好要有聚集索引数据表最好要有聚集索引n n聚集索引不要太大，因所有非聚集索引放有其键值聚集索引不
62、要太大，因所有非聚集索引放有其键值n n不不能能用用经经常常变变更更的的字字段段做做聚聚集集索索引引，因因为为聚聚集集索索引引一一改改变变，所有非聚集索引都需要修改键值，导致大量的锁定。所有非聚集索引都需要修改键值，导致大量的锁定。n n索引不能建太多，浪费维护资源。索引不能建太多，浪费维护资源。50硬盘子系统硬盘子系统n n硬硬盘盘子子系系统统一一般般来来说说是是数数据据库库的的性性能能瓶瓶颈颈，因因为为它它是是整个系统中运作最慢的部分整个系统中运作最慢的部分n n数数据据库库系系统统时时时时刻刻刻刻对对硬硬盘盘完完成成插插删删改改和和查查询询的的操操作作，硬盘子系统性能好，自然能提升整体性
63、能硬盘子系统性能好，自然能提升整体性能n n通通常常的的解解决决方方案案是是通通过过多多个个硬硬盘盘合合作作，平平均均分分散散工工作作量来提升效率量来提升效率n n由由于于存存取取数数据据模模式式不不同同，数数据据库库文文件件和和LogLog文文件件和和操操作系统的内存交换文件最好放在不同的物理硬盘。作系统的内存交换文件最好放在不同的物理硬盘。n nSQL SQL SERVERSERVER与与“ “恢恢复复间间隔隔” ”配配置置相相关关运运作作是是检检查查点点时间发生的频率时间发生的频率n n默默认认“ “恢恢复复间间隔隔” ”配配置置选选项项配配置置值值为为0 0，可可能能导导致致数数据据库
64、库几几乎乎每每分分钟钟要要发发生生一一次次以以上上的的检检查查点点事事件件，若若觉觉得得检检查查太太频频繁而导致太多的硬盘繁而导致太多的硬盘I/OI/O有损性能，可尝试将配置加大有损性能，可尝试将配置加大51硬盘子系统设计硬盘子系统设计n nRAIDRAID机制机制n nRAIDRAID可可以以不不仅仅可可以以提提升升存存取取效效率率，还还可可以以加加强强数数据据储储存存的的容错能力容错能力n nRAID 0RAID 0、RAID 1RAID 1、RAID 3RAID 3、RAID 5RAID 5、RAID 0+1RAID 0+1n n文件组文件组n n文文件件组组的的运运行行原原理理是是靠靠
65、多多个个硬硬盘盘同同时时存存取取，由由于于数数据据打打散散在多个硬盘上，多个硬盘一起运行，可以较有效率地存取在多个硬盘上，多个硬盘一起运行，可以较有效率地存取n n不同对象：数据表、索引等可考虑分散在不同的文件组不同对象：数据表、索引等可考虑分散在不同的文件组n n不不同同使使用用模模式式的的数数据据文文件件可可通通过过文文件件组组配配置置到到不不同同的的硬硬盘盘，如如：系系统统数数据据库库文文件件、用用户户数数据据库库频频繁繁操操作作的的多多个个表表以以及及日志文件可以分开存放到不同硬盘。日志文件可以分开存放到不同硬盘。n nRAIDRAID效效率率大大于于文文件件组组，因因为为RAIDRA
66、ID控控制制卡卡一一般般有有很很大大的的缓缓存存区区52其它应注意的问题其它应注意的问题n n注意批处理工作执行的时段分布注意批处理工作执行的时段分布n n各批处理工作不要集中在相同的时段各批处理工作不要集中在相同的时段n n批批处处理理一一般般采采用用均均分分时时段段的的周周期期运运行行，如如事事务务记记录录备备份份，由由于于周周期期运运行行，极极有有可可能能在在系系统统的的高高峰峰时时间间启启动动了了备备份份运运行行n n还还有有如如周周期期性性重重建建索索引引、复复制制数数据据、通通过过DTSDTS转转换换数数据据等等都都可能挤在系统忙碌是执行可能挤在系统忙碌是执行n n避免前端程序直接存取基础数据表避免前端程序直接存取基础数据表n n尽尽量量让让应应用用程程序序通通过过存存储储过过程程、视视图图或或用用户户自自定定义义函函数数来来存取数据存取数据n n若若直直接接存存取取数数据据表表，当当修修改改数数据据表表结结构构，重重新新切切割割，都都会会因为前端代码直接存取基础数据表而带来很大麻烦。因为前端代码直接存取基础数据表而带来很大麻烦。53
