CH10.数据库优化

第十章数据库性能优化提纲SQL语句对效率的影响索引与查询性能事务与锁管理硬盘子系统设计其它应注意的问题SQL语句对效率的影响WHERE子句的规范SQL的注意事项WHERE子句的规范WHERE子句常犯的错误对数据字段做运算负向查询对数据字段使用函数使用OR运算符不要对数据字段做运算无运算的字段可以引用索引，有运算的字段将无法引用索引进行优化而需要扫描整个表示例比较下列语法的差异：SELECT*FROM[OrderDetails]WHEREQuantity=100SELECT*FROM[OrderDetails]WHEREQuantity+1=101还包括其它的运算，如字符连接等不要使用负向查询负向查询：NOT、!=、、!、!、NOTEXISTS、NOTIN、NOTLIKE等负向查询不能充分利用索引进行二分查找，需要扫描整张表示例SELECT*FROM[OrderDetails]WHEREQuantity!=100可改成：SELECT*FROM[OrderDetails]WHEREQuantity100ORQuantity100不对数据字段使用函数数据字段使用函数就是一种运算，将使效率低比较：SELECT*FROM[OrderDetails]WHEREABS(Quantity-100)1SELECT*FROM[OrderDetails]WHEREQuantity99ANDQuantity101SELECT*FROMEmployeesWHERESUBSTRING(LastName,1,1)=‘D’SELECT*FROMEmployeesWHERELastNameLIKE‘D%’使用OR运算符AND运算符可以充分引用索引SELECT*FROMOrdersWHERECustomerID=‘IS10008’ANDOrderDate=‘20060808’只需要在CustomerID上建索引就可以了OR运算符需要对参与查询的多个字段都建索引，否则将可能扫描全表SELECT*FROMOrdersWHERECustomerID=‘IS10008’OROrderDate=‘20060808’需要在CustomerID和OrderDate两个属性上都建合适的索引,否则将扫描整个数据表SQL的注意事项SELECT语法尽量不要传回数据表的所有字段，也不要不使用过滤条件，否则将极大地增加网络负担若使用复合索引，索引顺序上的第一个字段才适合当作过滤条件DISTINCT、ORDERBY等语法尽量等到查询需要时才使用，因为它们需要SQLSERVER的额外计算SQL的注意事项大量数据加载大量加载某个数据表时，应考虑先删掉索引，加载完毕再重建索引（特别是多个用户端同时在做大量数据加载时）BULKINSERT语法通常比bcp工具程序快大量数据加载时，应设参数采用数据表锁定，而不要采用默认的记录锁如果数据表的记录需要先做转换，应先导入临时表中，经过处理再大量加载到目的数据表中INSERT、DELETE和UPDATE对大量数据，SELECTINTO比INSERT快对大量数据，TRUNCATETABLE比DELETETABLE快UPDATE和DELETE采用WHERE子句时，条件要符合WHERE的有效格式索引与查询性能索引及其相关属性配置聚集索引与非聚集索引排序Sysindexes系统数据表索引是否值得统计联结与查询效率覆盖索引在视图与计算字段上建索引数据不连续的处理索引及相关属性配置索引是有效使用数据库系统的基础索引建立是否适当是性能好坏的成功关键索引数据放在分页中，用来当做索引的数据字段越小越好，也就是让分页尽量存放更多的索引项索引结点有三种结构：根结点分页叶子层非叶子层索引及相关属性配置创建索引的语法格式：CREATEINDEX索引名ON表名（列名）建索引时，可根据不同的需求进行选项配置：FILLFACTOR（填充因子）在建（包括重建）索引时，保留部分空间让随后的新建、修改可直接利用这些空间需要立刻对某个数据表更新所有的索引，最简单的方式是通过DBCCDBREINDEX命令重建该数据表的聚集索引，则所有的非聚集索引都会同时自动更新IGNORE_DUP_KEY对于唯一索引，当插入多条记录（包括重复记录）时，若建索引没有配置该选项，将全部回滚，否则仅放弃重复记录索引及相关属性配置选项配置：（续）DROP_EXISTING通过配置DROP_EXISTING可防止重建聚集索引时一并删除与重建该数据表上所有的非聚集索引，否则重建聚集索引会导致所有非聚集索引重建一次（若重建聚集索引采用相同的键值）或两次STATISTICS_NORECOMPUTE表示与该索引相关的统计信息不需要自动更新，系统管理员会手动更新SORT_IN_TEMPDB若系统的TEMPDB是建立在与该索引不同硬盘的文件组上，可通过该选项让临时空间利用另一个或一组TEMPDB所在的硬盘来做键值排序，以提升建立索引时的性能聚集索引与非聚集索引聚集索引对聚集索引，数据表本身就是索引的一部分，是聚集索引的叶子层，整个数据表的摆放顺序按索引项由小到大排序聚集索引的优点如果记录较小，则在记录访问中有可能可以减少磁盘存取的次数；聚簇索引有利于多点查询，因为值相同的记录放在了一起（一个页内），这样一次磁盘访问就可以了，如果是非聚簇索引，因为可能存在不同的页上，可能需要好几次磁盘访问。聚簇索引有助于在不同值较少的属性上进行的等值连接；基于B-树结构的聚簇索引，可以很好的支持范围查询、前缀匹配查询和排序查询。节省存储空间。聚簇以后，聚簇码相同的元组集中在一起了，因而聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了聚集索引与非聚集索引聚集索引聚集索引的缺点建立与维护聚簇的开销相当大。如果存在大量的溢出数据页，它的性能会下降很快。原因：访问这些页面的磁盘定位需要花费很多时间。非聚集索引非聚集索引完全独立于数据表之外一个数据表可建立249个索引（具体应用时一般不超过10个）当查询条件的选择性不高，即符合条件的记录占很小比例时，通过非聚集索引查询效率非常低。适合对精确匹配，以及搜寻结果集很小的查询聚集索引与非聚集索引聚集索引的选择至关重要聚集索引的索引项应该具有以下特性：数据格式为整数本身就唯一不可为NULL字段值不能太大若选择聚集索引的字段值很大，则整个数据表的各种索引都将会变得低效，因为所有的非聚集索引的叶子层都会因为纳入聚集索引的键值而变大排序组织数据时需要排序的情况GROUPBY、DISTINCT、ORDERBY、TOP等子句虽然这些子句只是查询结果的产生方式，但抽取与显示都需要耗费系统资源预先排序的数据要使用索引有效地排序查询数据，最直接的方式就是在要排序的字段上建立聚集索引。索引顺序SQLSERVER可使用相同的聚集索引做升序和降序排序，因为叶子层的分页存储都以双向连接串行方式连接在一起排序示例聚集索引可以自动正反扫描CREATECLUSTEREDINDEXidx_LastNameONmember(LastName)WITHDROP_EXISTING查询：SELECT*FROMMemberORDERBYlastnameSELECT*FROMMemberORDERBYlastnameDESC效果一样排序示例多关键字聚集索引CREATECLUSTEREDINDEXidx_LastNameONmember(LastNameASC,FirstNameDESC)WITHDROP_EXISTING查询：SELECT*FROMMemberORDERBYlastnameASCFirstNameDESC利用Sysindexes系统数据表进行分析在SQLSERVER中，每个数据库都有一个Sysindexes系统数据表，用来存放数据库内所有的索引细节。在建立聚集索引或非聚集索引后可查询Sysindexes表的数据，也可以直接利用系统存储过程sp_spaceused查看数据表或索引所使用的存储空间。Sysindexes数据表的used字段：聚集索引（非聚集索引）已使用的总分页数Sysindexes数据表的dpage字段：聚集索引中的实际子叶，即数据表本身所占的页数（非聚集索引中叶子层所占的页数）索引是否值得索引可以大大提高查询效率，若索引建少了，查找数据就效率低下索引建得太多则不利于插删改操作针对SQL语法或数据类型查看是否值得建索引时，可参考的方面：选择性选择性指符合查询条件的记录占总记录的百分比。选择性越高，即该值越小，才越适合建索引在选择性很低时，通过非聚集索引存取是非常没有效率的存取方式，还不如直接做数据表扫描索引是否值得是否值得建索引所参考的方面：(续)数据密度数据密度为键值唯一的记录笔数的倒数数据密度越小，该字段越适合建立索引平均查询到的记录数=数据密度*总记录数数据分布数据分布表示多笔数据记录组成的方式表示数据记录是平均散布在一段范围中还是集中在部分区块如均匀分布，正态分布等，需进一步确定其选择性查看查询语法所使用的资源配置SETSTATISTICS选项在查询分析器中配置，在SQL语句执行时返回语法：SETSTATISTICSIO|TIME|PROFILEONIO：返回扫描次数（表或索引存取次数）、逻辑读入（缓冲区读取页数）、物理读取（磁盘读取块数）、先读读入（先读机制预先将数据放到缓存）TIME：包括SQLSERVER分析与编译时间、服务器执行时间PROFILE：最优化程序如何执行SQL语法的结果集（执行计划）使用SETSHOWPLAN_TEXT选项查看查询计划语法：SETSHOWPLAN_TEXTON返回将要执行的查询计划，不会真正执行查询STATISTICSIO与SHOWPLAN_TEXT是互斥建立最优执行计划的各阶段最优化程序的主要工作是将没有执行步骤、以集合为基础的SQL语法转换成有效率的可执行步骤建立执行计划的过程：一般计划的最优化评估是否缓存中已经存在以前建立且当前可用的执行计划对显而易见的查询要求直接建立执行计划如INSERT…VALUES或SELECT的字段都包含在某个索引内,且没有其他合适的索引等单一化单一化主要做语句转换，找到语法上最有效的执行方式，处理一些不需要通过索引成本分析就可以决定有效执行步骤的工作加载统计多层次的以成本为基础的最优化最优化程序通过统计数据计算多种执行方式的成本进行选择统计统计记录着数据内容的分布可以针对索引或数据的某个字段建立统计查询优化程序可依据数据分布的统计信息完成下列工作：可获取某个索引对查询的选择性如何能分析索引的执行成本高低从而建立最佳的执行计划SQLSERVER获取统计的两种方式：完全扫描数据表：与建立索引时一并建立统计抽样分析：未建索引的字段建立统计，或更新已经存在的统计时统计统计数据记录sysindexes系统表的statblob字段中（image格式）查看统计数据的语法：DBCCSHOW_STATISTICS(表名,统计信息的目标)示例：在查询分析器运行获得完全扫描方式的统计信息CREATEINDEXidx_product_noONProduct(PNO)DBCCSHOW_STATISTICS(Product,idx_product_no)统计统计结果以表格的形式显示，包括三部分：第一部分：索引最后被更新的时间(Updated字段)统计数据来源记录数(Row字段)抽样记录数(RowSampled字段)分布组数(Steps字段)数据密度（Density字段）键值平均长度第二部分：多个键值字段各自的统计数据第三部分：各统计字段对应分布组的详细统计信息。起字段包括：RANGE_HI_KEY