CH10.数据库优化.ppt

1、第十章 数据库性能优化,提纲 SQL语句对效率的影响 索引与查询性能 事务与锁管理 硬盘子系统设计 其它应注意的问题,SQL语句对效率的影响,WHERE子句的规范 SQL的注意事项,WHERE子句的规范,WHERE子句常犯的错误 对数据字段做运算 负向查询 对数据字段使用函数 使用OR运算符,不要对数据字段做运算,无运算的字段可以引用索引，有运算的字段将无法引用索引进行优化而需要扫描整个表 示例 比较下列语法的差异： SELECT * FROM Order Details WHERE Quantity=100 SELECT * FROM Order Details WHERE Quantity

2、+1 =101 还包括其它的运算，如字符连接等,不要使用负向查询,负向查询： NOT、!=、!、NOT EXISTS、NOT IN、NOT LIKE等 负向查询不能充分利用索引进行二分查找，需要扫描整张表 示例 SELECT * FROM Order Details WHERE Quantity!=100 可改成： SELECT * FROM Order Details WHERE Quantity100 OR Quantity100,不对数据字段使用函数,数据字段使用函数就是一种运算，将使效率低 比较： SELECT * FROM Order Details WHERE ABS(Quanti

3、ty-100)99 AND Quantity101 SELECT * FROM Employees WHERE SUBSTRING (LastName,1,1)=D SELECT * FROM Employees WHERE LastName LIKE D%,使用OR运算符,AND运算符可以充分引用索引 SELECT * FROM Orders WHERE CustomerID=IS10008 AND OrderDate=20060808 只需要在CustomerID上建索引就可以了 OR运算符需要对参与查询的多个字段都建索引，否则将可能扫描全表 SELECT * FROM Orders WH

4、ERE CustomerID=IS10008 OR OrderDate=20060808 需要在CustomerID和OrderDate两个属性上都建合适的索引,否则将扫描整个数据表,SQL的注意事项,SELECT语法 尽量不要传回数据表的所有字段，也不要不使用过滤条件，否则将极大地增加网络负担 若使用复合索引，索引顺序上的第一个字段才适合当作过滤条件 DISTINCT、ORDER BY等语法尽量等到查询需要时才使用，因为它们需要SQL SERVER的额外计算,SQL的注意事项,大量数据加载 大量加载某个数据表时，应考虑先删掉索引，加载完毕再重建索引（特别是多个用户端同时在做大量数据加载时）

5、BULK INSERT语法通常比bcp工具程序快 大量数据加载时，应设参数采用数据表锁定，而不要采用默认的记录锁 如果数据表的记录需要先做转换，应先导入临时表中，经过处理再大量加载到目的数据表中 INSERT、DELETE和UPDATE 对大量数据，SELECT INTO比INSERT快 对大量数据，TRUNCATE TABLE比DELETE TABLE快 UPDATE和DELETE采用WHERE子句时，条件要符合WHERE的有效格式,索引与查询性能,索引及其相关属性配置 聚集索引与非聚集索引 排序 Sysindexes系统数据表 索引是否值得 统计 联结与查询效率 覆盖索引 在视图与计算字段

6、上建索引 数据不连续的处理,索引及相关属性配置,索引是有效使用数据库系统的基础 索引建立是否适当是性能好坏的成功关键 索引数据放在分页中，用来当做索引的数据字段越小越好，也就是让分页尽量存放更多的索引项 索引结点有三种结构： 根结点分页 叶子层 非叶子层,索引及相关属性配置,创建索引的语法格式： CREATE INDEX 索引名 ON 表名（列名） 建索引时，可根据不同的需求进行选项配置： FILLFACTOR（填充因子） 在建（包括重建）索引时，保留部分空间让随后的新建、修改可直接利用这些空间 需要立刻对某个数据表更新所有的索引，最简单的方式是通过DBCC DBREINDEX命令重建该数据表

7、的聚集索引，则所有的非聚集索引都会同时自动更新 IGNORE_DUP_KEY 对于唯一索引，当插入多条记录（包括重复记录）时，若建索引没有配置该选项，将全部回滚，否则仅放弃重复记录,索引及相关属性配置,选项配置：（续） DROP_EXISTING 通过配置DROP_EXISTING可防止重建聚集索引时一并删除与重建该数据表上所有的非聚集索引，否则重建聚集索引会导致所有非聚集索引重建一次（若重建聚集索引采用相同的键值）或两次 STATISTICS_NORECOMPUTE 表示与该索引相关的统计信息不需要自动更新，系统管理员会手动更新 SORT_IN_TEMPDB 若系统的TEMPDB是建立在与该

8、索引不同硬盘的文件组上，可通过该选项让临时空间利用另一个或一组TEMPDB所在的硬盘来做键值排序，以提升建立索引时的性能,聚集索引与非聚集索引,聚集索引 对聚集索引，数据表本身就是索引的一部分，是聚集索引的叶子层，整个数据表的摆放顺序按索引项由小到大排序 聚集索引的优点 如果记录较小，则在记录访问中有可能可以减少磁盘存取的次数；聚簇索引有利于多点查询，因为值相同的记录放在了一起（一个页内），这样一次磁盘访问就可以了，如果是非聚簇索引，因为可能存在不同的页上，可能需要好几次磁盘访问。 聚簇索引有助于在不同值较少的属性上进行的等值连接； 基于B-树结构的聚簇索引，可以很好的支持范围查询、前缀匹配查

9、询和排序查询。 节省存储空间。聚簇以后，聚簇码相同的元组集中在一起了，因而聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了,聚集索引与非聚集索引,聚集索引 聚集索引的缺点 建立与维护聚簇的开销相当大。 如果存在大量的溢出数据页，它的性能会下降很快。 原因：访问这些页面的磁盘定位需要花费很多时间。 非聚集索引 非聚集索引完全独立于数据表之外 一个数据表可建立249个索引（具体应用时一般不超过10个） 当查询条件的选择性不高，即符合条件的记录占很小比例时，通过非聚集索引查询效率非常低。 适合对精确匹配，以及搜寻结果集很小的查询,聚集索引与非聚集索引,聚集索引的选择至关重要 聚集索引的索引

10、项应该具有以下特性： 数据格式为整数 本身就唯一 不可为NULL 字段值不能太大 若选择聚集索引的字段值很大，则整个数据表的各种索引都将会变得低效，因为所有的非聚集索引的叶子层都会因为纳入聚集索引的键值而变大,排序,组织数据时需要排序的情况 GROUP BY、DISTINCT、ORDER BY、TOP等子句 虽然这些子句只是查询结果的产生方式，但抽取与显示都需要耗费系统资源 预先排序的数据 要使用索引有效地排序查询数据，最直接的方式就是在要排序的字段上建立聚集索引。 索引顺序 SQL SERVER可使用相同的聚集索引做升序和降序排序，因为叶子层的分页存储都以双向连接串行方式连接在一起,排序示例

11、,聚集索引可以自动正反扫描 CREATE CLUSTERED INDEX idx_LastName ON member(LastName) WITH DROP_EXISTING 查询： SELECT * FROM Member ORDER BY lastname SELECT * FROM Member ORDER BY lastname DESC 效果一样,排序示例,多关键字聚集索引 CREATE CLUSTERED INDEX idx_LastName ON member(LastName ASC,FirstName DESC) WITH DROP_EXISTING 查询： SELECT

12、* FROM Member ORDER BY lastname ASC FirstName DESC,利用Sysindexes系统数据表进行分析,在SQL SERVER中，每个数据库都有一个Sysindexes系统数据表，用来存放数据库内所有的索引细节。 在建立聚集索引或非聚集索引后可查询Sysindexes表的数据，也可以直接利用系统存储过程sp_spaceused查看数据表或索引所使用的存储空间。 Sysindexes 数据表的used字段：聚集索引（非聚集索引）已使用的总分页数 Sysindexes 数据表的dpage字段：聚集索引中的实际子叶，即数据表本身所占的页数（非聚集索引中叶子层

13、所占的页数）,索引是否值得,索引可以大大提高查询效率，若索引建少了，查找数据就效率低下 索引建得太多则不利于插删改操作 针对SQL语法或数据类型查看是否值得建索引时，可参考的方面： 选择性 选择性指符合查询条件的记录占总记录的百分比。 选择性越高，即该值越小，才越适合建索引 在选择性很低时，通过非聚集索引存取是非常没有效率的存取方式，还不如直接做数据表扫描,索引是否值得,是否值得建索引所参考的方面：(续) 数据密度 数据密度为键值唯一的记录笔数的倒数 数据密度越小，该字段越适合建立索引 平均查询到的记录数=数据密度*总记录数 数据分布 数据分布表示多笔数据记录组成的方式 表示数据记录是平均散布

14、在一段范围中还是集中在部分区块 如均匀分布，正态分布等，需进一步确定其选择性,查看查询语法所使用的资源,配置SET STATISTICS选项 在查询分析器中配置，在SQL语句执行时返回 语法：SET STATISTICS ON IO：返回扫描次数（表或索引存取次数）、逻辑读入（缓冲区读取页数）、物理读取（磁盘读取块数）、先读读入（先读机制预先将数据放到缓存） TIME：包括SQL SERVER分析与编译时间、服务器执行时间 PROFILE：最优化程序如何执行SQL语法的结果集（执行计划） 使用SET SHOWPLAN_TEXT选项查看查询计划 语法： SET SHOWPLAN_TEXT ON

15、返回将要执行的查询计划，不会真正执行查询 STATISTICS IO与SHOWPLAN_TEXT 是互斥,建立最优执行计划的各阶段,最优化程序的主要工作是将没有执行步骤、以集合为基础的SQL语法转换成有效率的可执行步骤 建立执行计划的过程： 一般计划的最优化 评估是否缓存中已经存在以前建立且当前可用的执行计划 对显而易见的查询要求直接建立执行计划 如INSERT VALUES或SELECT的字段都包含在某个索引内,且没有其他合适的索引等 单一化 单一化主要做语句转换，找到语法上最有效的执行方式，处理一些不需要通过索引成本分析就可以决定有效执行步骤的工作 加载统计 多层次的以成本为基础的最优化

16、最优化程序通过统计数据计算多种执行方式的成本进行选择,统计,统计记录着数据内容的分布 可以针对索引或数据的某个字段建立统计 查询优化程序可依据数据分布的统计信息完成下列工作： 可获取某个索引对查询的选择性如何 能分析索引的执行成本高低 从而建立最佳的执行计划 SQL SERVER获取统计的两种方式： 完全扫描数据表：与建立索引时一并建立统计 抽样分析：未建索引的字段建立统计，或更新已经存在的统计时,统计,统计数据记录sysindexes系统表的statblob字段中（image格式） 查看统计数据的语法： DBCC SHOW_STATISTICS(表名,统计信息的目标) 示例： 在查询分析器运

17、行获得完全扫描方式的统计信息 CREATE INDEX idx_product_no ON Product(PNO) DBCC SHOW_STATISTICS(Product, idx_product_no),统计,统计结果以表格的形式显示，包括三部分： 第一部分： 索引最后被更新的时间(Updated字段) 统计数据来源记录数(Row字段) 抽样记录数(Row Sampled字段) 分布组数(Steps字段) 数据密度（Density字段） 键值平均长度 第二部分：多个键值字段各自的统计数据 第三部分：各统计字段对应分布组的详细统计信息。起字段包括： RANGE_HI_KEY(涵盖记录上限值

18、) 、RANGE_ROW(落在其中的样本记录数)、EQ_ROWS(Step值的样本记录数) DISTINCT_RANGE_ROWS、AVG_RANGE_ROWS,更新统计,更新统计的方式有两种：手动更新、自动更新 手动更新： 利用CREATE STATISTICS对未建索引的字段直接产生统计信息 利用sp_createstats存储过程对字段建立统计信息 利用UPDATE STATISTICS更新某个统计 利用sp_updatestats更新统计 手动更新的时机： 索引中的键值有大量的新建、修改或删除，而马上要用到该索引 通过TRUNCATE TABLE语法清空某个重新装入数据，而又要立即存取

19、 一般情况SQL SERVER能自动维护统计信息（属性要配置）,联结与查询效率,查询优化程序决定联结的执行方式时，需要确定以下内容： 数据表之间联结最佳的先后关系 两两联结时找出合适的内层数据表和外层数据表 决定联结算法：嵌套循环连接、合并连接、哈希连接 嵌套循环连接 外部循环找到符合条件的记录后，逐列要求内部循环搜寻符合的数据列。 适合只影响一小部分数据记录的查询，或外部输入相当小，内部输入已建索引，且数据记录相当大的情况。,联结与查询效率,合并连接 要求两边参与连接的输入数据必须先排序 如满足上述条件，合并连接的效率最高 哈希连接 前两种连接不合用时，才考虑此连接 建立两个输入：组建输入和

20、探查输入 组建输入将符合条件但数据较少的表的字段值计算得到哈希表放在内存中（相同键值记录链接起来）及哈希桶 扫描整个探查输入，计算哈希值，扫描哈希桶，产生符合项 哈希连接用于集合对比作业：内部连接、外连接、半连接、交集等 在没有索引的情况下，SQL SERVER默认哈希连接,覆盖索引,聚集索引的好处： SQL SERVER找到正确的索引键值后不需要再用指针做额外的搜寻 SQL SERVER将符合相同条件的数据集中放在一起 聚集索引只能建一个，非聚集索引有很多个（最多可达249个） 非聚集索引只能在传回数据量占总数比例很少时才有用 引入覆盖索引（一种的非聚集复合索引）可同样具有聚集索引的两个好处

21、,覆盖索引,覆盖索引是指那些索引项中包含查询所需要的全部信息的非聚簇索引 可以是单索引或复合索引，但是一般都是非聚簇的。 覆盖索引之所以比较快是因为索引页中包含了查询所必须的数据，不需去访问数据页。 如果非聚簇索引中包含结果数据，那么它的查询速度将快于聚簇索引。 什么时候建覆盖索引 经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列。 经常查询涵盖GROUP BY或ORDER BY子句的字段 如果可能尽量使关键查询形成覆盖查询。,覆盖索引,建立覆盖索引的语法： CREATE INDEX 索引名 ON 表名(字段1，字段2，) 示例 EX

22、EC spCleanIdx Member CREATE INDEX idx_LastFirstName ON Member (Lastname、Firstname) SELECT lastname,firstname FROM Member WHERE lastname BETWEEN Funk AND Lang 注意： 建立覆盖查询时尽量限制索引项的大小，保持ROW/KEY越大越好，否则扫描覆盖索引与扫描数据表所花的I/O分页差不多，就失去的覆盖索引的意义,组合索引,组合索引就是指建立在多个属性上的索引。 组合索引可以是聚簇的，也可以是非聚簇的。 比较在单个属性上建立的索引，组合索引具有以下

23、优势： 支持前缀匹配查询，支持的前缀就是组合索引（A，B，）的形式。 更易覆盖查询条件，有时一个稠密的组合索引就可以完全回答查询。例如查姓为“罗”，名为“强”的人有多少个。 组合索引是支持多属性唯一性的一个有效办法,组合索引,设计一个组合索引时，必须注意组合索引的顺序 如果查询更倾向于在属性A而不是在属性B上加限定词的话，那么应该建立把A放在B前面的组合索引。 组合索引的缺点： 它们趋向于比较长的索引键。如果不使用压缩方法，这会引起B-树 层数的增加。 因为组合索引包含多个属性，所以对其中任何属性的更新都会导致索引的更新，组合索引的维护代价将会是比较高的。,在视图与计算字段上建索引,为视图建索

24、引能让逻辑的数据物理化 为视图建立的第一个索引一定是聚集以及唯一索引 聚集是让索引的叶子层可以涵盖视图定义的所有记录 唯一是让索引维护比较方便 相当于一个有聚集索引的数据表 若删除该聚集索引将导致其他索引一起删除 对计算字段可以直接建立非聚集索引，索引需要的是该字段计算后的值,Indexed View,Indexed View把符合定义的数据建立好另外存放，若视图包含汇总函数，建立索引时即完成汇总计算，当更新数据表时，系统会自动维护视图索引的汇总结果 通过视图（含索引）查询时 不需要重新计算汇总，提高性能 可以不必在查询时才做连接运算，提升性能 如果偏向联机事务处理（绝大多数运算是插删改），反

25、而因为要维护索引而降低效率,Indexed View,示例 CREATE VIEW Vdiscount WITH SCHEMABINDING AS SELECT SUM(UnitPrice*Quatity*Discount) SumDiscountprice FROM dbo.order details GROUP BY ProductID GO CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount(ProductID) 查询最高折扣款前五名的产品 SELECT TOP 5 ProductID, SUM(UnitPrice*Quatit

26、y*Discount) SumDis FROM order details GROUP BY ProductID ORDER BY SumDis 考察查询最高销售额前五名的产品,Indexed View,查询最高销售额前五名的产品 解决方法 CREATE VIEW Vdiscount WITH SCHEMABINDING AS SELECT SUM(UnitPrice*Quatity) Sumprice, SUM(UnitPrice*Quatity*(1-Discount) SumDiscount, SUM(UnitPrice*Quatity*Discount) SumDiscountpric

27、e FROM dbo.order details GROUP BY ProductID GO CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount(ProductID) 考察求平均值（AVG）的情况 增加子句SUM(Quatity) Units,Indexed View的适用范围,适合建立Indexed View的情况： 减低决策支持查询的负载 对大型数据表做连接以及汇总运算 重复同一种模式的查询 对某些字段重复做汇总运算 重复对相同的数据表、相同的属性做连接 以上方式的综合使用 不适合建立Indexed View的情况： 经常进行插删

28、改的OLTP系统 大量数据字段结合在一起的连接 与原始数据表内容差不多大的Indexed View,数据不连续的处理,数据经过插删改会造成不连续 数据不连续分两种： 内部不连续：物理分页中有许多空间没有记录 外部不连续：磁盘分页与扩展分页不连续，即索引或数据表可能散落在多个扩展分页中，使得其在物理上不连续。 数据不连续会使硬盘读取无效率，而且读出来的数据还需要重新整理 索引需要空间时需要做分割操作 外部不连续只在做大量数据扫描时才影响效率，若只搜索某些记录，利用索引指针就可取得分页,数据不连续的处理,可以执行DBCC SHOWCONTIG指令得到数据表的不连续状况。 数据不连续的处理： 利用D

29、BCC INDEXDEFRAG移除逻辑扫描的外部不连续状况 重建索引可以移除所有的不连续状况 若只是见聚集索引，最好搭配CREATE INDEX WITH DROP_EXISTING,事务与锁管理,锁 死锁 观察与分析系统的锁定情况 产生阻塞的原因,锁,SQL SERVER中锁的种类： 共享锁 排他锁 更新锁 意向锁 锁的相容性 可锁定的资源 数据库、文件、索引 数据表、分页、索引键值 数据行、应用程序等,锁,锁与事务的四个隔离等级： READ UNCOMMITTED SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 可能读到脏数据 READ C

30、OMMITTED SET TRANSACTION ISOLATION LEVEL READ COMMITTED SQL SERVER默认配置 不能读到脏数据，但不可重复读 REPEATABLE READ 可重复读 SERIALIZABLE 防止幻象现象 当需要事务的正确性，就会提高事务的隔离等级，但会让并发度减低,死锁,死锁是在DBMS中的某组资源上发生了两个或多个线程之间循环相关性时，由于各个线程之间互不相让对方所需要的资源而造成的。 当客户向数据库提交查询后，客户机可能会感觉到好像“死机”了，这就可能是发生了锁争夺 当系统中出现锁争夺的时候，如果不想让进程永久的等待下去，解决的办法是通过设

31、置锁超时时间间隔。 可以用SET LOCK_TIMEOUT命令设置时间间隔。 SQL Server中有循环死锁和转换死锁两大类。,死锁,循环死锁 由于系统或用户进程之间彼此都只有得到对方持有的资源才能执行时发生 转换死锁 发生在两个或多个进程在事务中持有同一资源的共享锁，而且都需要将共享锁升级为独占锁，但都要待其他进程释放这一共享锁时才能升级。 分布式死锁,观察与分析系统的锁定情况,监视和跟踪SQL Server中的锁活动信息常见的方法有： 使用sp_lock存储过程 使用企业管理器查看锁信息 使用SQL Profile查看锁信息,观察与分析系统的锁定情况,锁定能造成性能影响，可以从下面几个方面观察系统是否因为锁定与阻塞导致运行问题： 通过企业管理器或系统存储 过程查看是否有许多进程被封锁不能执行 Master.dbo.sysprocessed系统数据表内，被封锁的进程的waittime字段的值异常大 SQL Profiler工具程序所获取的结果中，有许多At