1、第十章 数据库性能优化,提纲 数据库性能的影响因素 SQL语句对效率的影响 索引与查询性能 事务与锁管理 硬盘子系统设计 其它应注意的问题,数据库性能的影响因素,数据库性能各因素影响程度排序： D1 业务逻辑 （影响最大） D2 数据设计 （表结构设计，数据建模） D3 应用程序设计 （SQL 语句的写法） D4 数据库的逻辑结构 （索引，区间，段，表空间） D5 数据库操作 （参数和配置） D6 访问路径 D7 内存分配情况 D8 I/O 和物理设计 （DBA 与业务沟通）（峰值多大，使用周期） D9 资源的征用 D10 操作系统的调节,数据库性能的影响因素,说明 D1-D3 影响最大但比较
2、难修改 （事先去做，和程序员相关） D4-D9 性能影响不是最大但经常去做的（DBA） D10 手段不是很多,SQL语句对效率的影响,WHERE子句的规范SQL语句的规范SQL的注意事项,WHERE子句的规范,WHERE子句常犯的错误 对数据字段做运算 负向查询 对数据字段使用函数 使用OR运算符 使用 left join 和 null 值判断,不要对数据字段做运算,无运算的字段可以引用索引，有运算的字段将无法引用索引进行优化而需要扫描整个表 示例 学生表S(Sno,Sname,Birthdate,) 查找年龄大于等于21岁的学生 比较下列语法的差异： SELECT * FROM S WHER
3、E 2009-Birthdate=21 SELECT * FROM S WHERE Birthdate=2009-21 还包括其它的运算，如字符连接等,不要使用负向查询,负向查询： NOT、!=、!、NOT EXISTS、NOT IN、NOT LIKE等 负向查询不能充分利用索引进行二分查找，需要扫描整张表 示例 SELECT * FROM Order Details WHERE Quantity!=100 可改成： SELECT * FROM Order Details WHERE Quantity100 OR Quantity100,不对数据字段使用函数,数据字段使用函数就是一种运算，将使
4、效率低 比较： SELECT * FROM Order Details WHERE ABS(Quantity-100)99 AND Quantity101 SELECT * FROM Employees WHERE SUBSTRING (LastName,1,1)=DSELECT * FROM Employees WHERE LastName LIKE D%,使用OR运算符,AND运算符可以充分引用索引 SELECT * FROM Orders WHERE CustomerID=IS10008 AND OrderDate=20060808 只需要在CustomerID上建索引就可以了 OR运算
5、符需要对参与查询的多个字段都建索引，否则将可能扫描全表 SELECT * FROM Orders WHERE CustomerID=IS10008 OR OrderDate=20060808 需要在CustomerID和OrderDate两个属性上都建合适的索引,否则将扫描整个数据表,避免使用left join 和 null值判断,left join 比 inner join消耗更多的资源，因为它们包含与null数据匹配的数据。 示例： product(product_id int, product_type_id int null,.)，产品表，product_id为大于0的整数， prod
6、uct_type_id 与表 product_type 关联，但可为空，因为有的产品没有类别 product_type(product_type_id not null,product_type_name null,.)，产品类别表 此时如果要关联两表后查询product的内容，马上会想到使用 left join ，效率是一个问题。 解决方法： 在product_type中增加一条记录：0,.，并将product的 product_type_id设置为 not null ，当产品没有类别时将其 product_type_id设为0，这样查询就可以使用inner join了。,SQL语句的规范,
7、SQL语句中要注意的问题慎用IN 和 NOT IN 避免困难的表达式 WHERE子句中使用参数 不写没有意义的查询,慎用IN 和 NOT IN,示例： SELECT SNAME FROM S WHERE AGE IN (21,22,23) 对于连续的数值，能用BETWEEN就不要用IN了： SELECT SNAME FROM S WHERE AGE BETWEEN 21 AND 23,避免困难的表达式,避免困难的表达式 LIKE关键字支持通配符匹配，但这种匹配没法引用索引，特别耗时。 例如：SELECT * FROM Customer WHERE Zipcode LIKE “98_ _ _ _
8、”，即使在Zipcode字段上已建立了索引，在这种情况下也还是采用顺序扫描的方式。 把语句改为：SELECT * FROM Customer WHERE Zipcode=“980000” AND Zipcode“990000”，在执行查询时就会利用索引来查询，显然会大大提高速度。,WHERE子句中使用参数,如果在 where 子句中使用参数，将会导致全表扫描。 因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。这时变量的值还是未知的，因而无法作为索引选择的输入项。 示例： SELECT SNAME FROM S WHERE SNO=sn
9、o 该语句将进行全表扫描 可以改为强制查询使用索引： SELECT SNAME FROM S WITH(INDEX(索引名) WHERE SNO=sno,不写没有意义的查询,示例： 需要生成一个空表结构： SELECT SNO,GRADE INTO SC1 FROM SC WHERE 1=0 该查询不会返回任何结果集，但是会消耗系统资源的 应改成： CREATE TABLE SC1(.),SQL的注意事项,SELECT语法 尽量不要传回数据表的所有字段，也不要不使用过滤条件，否则将极大地增加网络负担 任何地方都不要使用 SELECT * FROM S，用具体的字段列表代替“*”，不要返回用不到
10、的任何字段。 若使用复合索引，索引顺序上的第一个字段才适合当作过滤条件 DISTINCT、ORDER BY等语法尽量等到查询需要时才使用，因为它们需要SQL SERVER的额外计算 避免相关子查询 ，如果一个列同时在主查询和子查询中出现，子查询必须对每一个外层查询重新查询一次。而且查询嵌套层次越多，效率越低，因此应当尽量避免子查询。,SQL的注意事项,大量数据加载 大量加载某个数据表时，应考虑先删掉索引，加载完毕再重建索引（特别是多个用户端同时在做大量数据加载时） BULK INSERT语法通常比bcp工具程序快 大量数据加载时，应设参数采用数据表锁定，而不要采用默认的记录锁 如果数据表的记录
11、需要先做转换，应先导入临时表中，经过处理再大量加载到目的数据表中 INSERT、DELETE和UPDATE 对大量数据，SELECT INTO比INSERT快 对大量数据，TRUNCATE TABLE比DELETE TABLE快 UPDATE和DELETE采用WHERE子句时，条件要符合WHERE的有效格式,索引与查询性能,索引的相关知识 索引的相关属性配置 聚集索引与非聚集索引 排序 Sysindexes系统数据表 索引是否值得 统计 联结与查询效率 覆盖索引 在视图与计算字段上建索引 数据不连续的处理,索引的相关知识,索引是有效使用数据库系统的基础 索引建立是否适当是性能好坏的成功关键 索
12、引数据放在分页中，用来当做索引的数据字段越小越好，也就是让分页尽量存放更多的索引项 索引结点有三种结构： 根结点分页 叶子层 非叶子层,索引的相关知识,索引使用原则如下： 在经常进行连接，但没有指定为外键的列上建索引，而不经常连接的字段则由优化器自动生成索引； 在频繁进行排序或分组（即进行group by或order by操作）的列上建索引； 在条件表达式中经常用到的不同值较多的列上建索引，在不同值少的列上不要建索引。 比如在雇员表的“性别”列上只有“男”与“女”，就无必要建立索引。如果建索引不但不会提高查询效率，反而严重降低更新速度。如果待排序的列有多个，可在这些列上建立复合索引。,索引的相
13、关知识,对于索引存在的几个误区： 误区1：索引创建得越多越好？ 实际上：创建的索引可能建立后从来未使用。索引的创建是需要代价的，对于插删改操作，对于每个索引都要进行维护。从而导致插删改操作的效率低下。 误区2：对于一个单表的查询，可以用索引1进行过滤，再使用索引2进行过滤？ 实际上：假设查询语句如下SELECT * FROM t1 WHERE c1=1 AND c2=2，c1列和c2列上分别建有索引ic1、ic2。先使用ic1(或ic2)进行过滤，产生的结果集是临时数据，不再具有索引，所以不可使用ic2(或ic1)进行再次过滤。,索引的相关属性配置,创建索引的语法格式： CREATE INDE
14、X 索引名 ON 表名（列名） 建索引时，可根据不同的需求进行选项配置： FILLFACTOR（填充因子） 在建（包括重建）索引时，保留部分空间让随后的新建、修改可直接利用这些空间 需要立刻对某个数据表更新所有的索引，最简单的方式是通过DBCC DBREINDEX命令重建该数据表的聚集索引，则所有的非聚集索引都会同时自动更新 IGNORE_DUP_KEY 对于唯一索引，当插入多条记录（包括重复记录）时，若建索引没有配置该选项，将全部回滚，否则仅放弃重复记录,索引的相关属性配置,选项配置：（续） DROP_EXISTING 通过配置DROP_EXISTING可防止重建聚集索引时一并删除与重建该数
15、据表上所有的非聚集索引，否则重建聚集索引会导致所有非聚集索引重建一次（若重建聚集索引采用相同的键值）或两次 STATISTICS_NORECOMPUTE 表示与该索引相关的统计信息不需要自动更新，系统管理员会手动更新 SORT_IN_TEMPDB 若系统的TEMPDB是建立在与该索引不同硬盘的文件组上，可通过该选项让临时空间利用另一个或一组TEMPDB所在的硬盘来做键值排序，以提升建立索引时的性能,聚集索引与非聚集索引,聚集索引 对聚集索引，数据表本身就是索引的一部分，是聚集索引的叶子层，整个数据表的摆放顺序按索引项由小到大排序 聚集索引的优点 如果记录较小，则在记录访问中有可能可以减少磁盘存
16、取的次数；聚簇索引有利于多点查询，因为值相同的记录放在了一起（一个页内），这样一次磁盘访问就可以了，如果是非聚簇索引，因为可能存在不同的页上，可能需要好几次磁盘访问。 聚簇索引有助于在不同值较少的属性上进行的等值连接； 基于B+树结构的聚簇索引，可以很好的支持范围查询、前缀匹配查询和排序查询。 节省存储空间。聚簇以后，聚簇码相同的元组集中在一起了，因而聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了,聚集索引与非聚集索引,聚集索引 聚集索引的缺点 建立与维护聚簇的开销相当大。 如果存在大量的溢出数据页，它的性能会下降很快。 原因：访问这些页面的磁盘定位需要花费很多时间。 非聚集索引
17、非聚集索引完全独立于数据表之外 一个数据表可建立249个索引（具体应用时一般不超过10个） 当查询条件的选择性不高，即符合条件的记录占很小比例时，通过非聚集索引查询效率非常低。 适合对精确匹配，以及搜寻结果集很小的查询,聚集索引与非聚集索引,聚集索引的选择至关重要 聚簇索引的侯选列是： 1、主键列,该列在where子句中使用并且插入是随机的。 2、按范围存取的列,如pri_order100 and pri_order200 3、在group by或order by中使用的列。 4、不经常修改的列。 应尽可能的避免更新聚集索引数据列，因为该数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导
18、致整个表记录的顺序的调整，会耗费相当大的资源。 若应用系统需要频繁更新聚集索引数据列，那么需要考虑是否应将该索引建为聚集索引。 5、在连接操作中使用的列。 聚集索引的索引项还应该具有以下特性： 数据格式为整数 本身就唯一 不可为NULL 字段值不能太大 若选择聚集索引的字段值很大，则整个数据表的各种索引都将会变得低效，因为所有的非聚集索引的叶子层都会因为纳入聚集索引的键值而变大,非聚集索引,考虑到更新的代价，建立非聚簇索引也要慎重。 非聚簇索引常被用在以下情况： 1、某列常用于集合函数(如Sum,)。 2、某列常用于join,order by,group by。 3、查询出的数据不超过表中数据
19、量的2%。,排序,组织数据时需要排序的情况 GROUP BY、DISTINCT、ORDER BY、TOP等子句 虽然这些子句只是查询结果的产生方式，但抽取与显示都需要耗费系统资源 预先排序的数据 要使用索引有效地排序查询数据，最直接的方式就是在要排序的字段上建立聚集索引。 索引顺序 SQL SERVER可使用相同的聚集索引做升序和降序排序，因为叶子层的分页存储都以双向连接串行方式连接在一起,排序示例,聚集索引可以自动正反扫描CREATE CLUSTERED INDEX idx_LastName ON member(LastName) WITH DROP_EXISTING 查询：SELECT *
20、 FROM Member ORDER BY lastnameSELECT * FROM Member ORDER BY lastname DESC效果一样,排序示例,多关键字聚集索引 CREATE CLUSTERED INDEX idx_LastName ON member(LastName ASC,FirstName DESC) WITH DROP_EXISTING 查询：SELECT * FROM Member ORDER BY lastname ASC FirstName DESC,利用Sysindexes系统数据表进行分析,在SQL SERVER中，每个数据库都有一个Sysindexe
21、s系统数据表，用来存放数据库内所有的索引细节。 在建立聚集索引或非聚集索引后可查询Sysindexes表的数据，也可以直接利用系统存储过程sp_spaceused查看数据表或索引所使用的存储空间。 Sysindexes 数据表的used字段：聚集索引（非聚集索引）已使用的总分页数 Sysindexes 数据表的dpage字段：聚集索引中的实际子叶，即数据表本身所占的页数（非聚集索引中叶子层所占的页数）,索引是否值得,索引可以大大提高查询效率，若索引建少了，查找数据就效率低下 索引建得太多则不利于插删改操作 针对SQL语法或数据类型查看是否值得建索引时，可参考的方面： 选择性 选择性指符合查询条
22、件的记录占总记录的百分比。 选择性越高，即该值越小，才越适合建索引 在选择性很低时，通过非聚集索引存取是非常没有效率的存取方式，还不如直接做数据表扫描,索引是否值得,是否值得建索引所参考的方面：(续) 数据密度 数据密度为键值唯一的记录笔数的倒数 数据密度越小，该字段越适合建立索引 平均查询到的记录数=数据密度*总记录数 数据分布 数据分布表示多笔数据记录组成的方式 表示数据记录是平均散布在一段范围中还是集中在部分区块 如均匀分布，正态分布等，需进一步确定其选择性,查看查询语法所使用的资源,配置SET STATISTICS选项 在查询分析器中配置，在SQL语句执行时返回 语法：SET STAT
23、ISTICS ON IO：返回扫描次数（表或索引存取次数）、逻辑读入（缓冲区读取页数）、物理读取（磁盘读取块数）、先读读入（先读机制预先将数据放到缓存） TIME：包括SQL SERVER分析与编译时间、服务器执行时间 PROFILE：最优化程序如何执行SQL语法的结果集（执行计划） 使用SET SHOWPLAN_TEXT选项查看查询计划 语法： SET SHOWPLAN_TEXT ON 返回将要执行的查询计划，不会真正执行查询 STATISTICS IO与SHOWPLAN_TEXT 是互斥,建立最优执行计划的各阶段,最优化程序的主要工作是将没有执行步骤、以集合为基础的SQL语法转换成有效率的
24、可执行步骤 建立执行计划的过程： 一般计划的最优化 评估是否缓存中已经存在以前建立且当前可用的执行计划 对显而易见的查询要求直接建立执行计划 如INSERT VALUES或SELECT的字段都包含在某个索引内,且没有其他合适的索引等 单一化 单一化主要做语句转换，找到语法上最有效的执行方式，处理一些不需要通过索引成本分析就可以决定有效执行步骤的工作 加载统计 多层次的以成本为基础的最优化 最优化程序通过统计数据计算多种执行方式的成本进行选择,统计,统计记录着数据内容的分布 可以针对索引或数据的某个字段建立统计 查询优化程序可依据数据分布的统计信息完成下列工作： 可获取某个索引对查询的选择性如何
25、 能分析索引的执行成本高低 从而建立最佳的执行计划 SQL SERVER获取统计的两种方式： 完全扫描数据表：与建立索引时一并建立统计 抽样分析：未建索引的字段建立统计，或更新已经存在的统计时,统计,统计数据记录sysindexes系统表的statblob字段中（image格式） 查看统计数据的语法： DBCC SHOW_STATISTICS(表名,统计信息的目标) 示例： 在查询分析器运行获得完全扫描方式的统计信息 CREATE INDEX idx_product_no ON Product(PNO) DBCC SHOW_STATISTICS(Product, idx_product_no)
26、,统计,统计结果以表格的形式显示，包括三部分： 第一部分： 索引最后被更新的时间(Updated字段) 统计数据来源记录数(Row字段) 抽样记录数(Row Sampled字段) 分布组数(Steps字段) 数据密度（Density字段） 键值平均长度 第二部分：多个键值字段各自的统计数据 第三部分：各统计字段对应分布组的详细统计信息。其字段包括： RANGE_HI_KEY(涵盖记录上限值) 、RANGE_ROW(落在其中的样本记录数)、EQ_ROWS(Step值的样本记录数) DISTINCT_RANGE_ROWS、AVG_RANGE_ROWS,更新统计,更新统计的方式有两种：手动更新、自动
27、更新 手动更新： 利用CREATE STATISTICS对未建索引的字段直接产生统计信息 利用sp_createstats存储过程对字段建立统计信息 利用UPDATE STATISTICS更新某个统计 利用sp_updatestats更新统计 手动更新的时机： 索引中的键值有大量的新建、修改或删除，而马上要用到该索引 通过TRUNCATE TABLE语法清空某个重新装入数据，而又要立即存取 一般情况SQL SERVER能自动维护统计信息（属性要配置）,联结与查询效率,查询优化程序决定联结的执行方式时，需要确定以下内容： 数据表之间联结最佳的先后关系 两两联结时找出合适的内层数据表和外层数据表
28、决定联结算法：嵌套循环连接、合并连接、哈希连接 嵌套循环连接 外部循环找到符合条件的记录后，逐列要求内部循环搜寻符合的数据列。 适合只影响一小部分数据记录的查询，或外部输入相当小，内部输入已建索引，且数据记录相当大的情况。,联结与查询效率,合并连接 要求两边参与连接的输入数据必须先排序 如满足上述条件，合并连接的效率最高 哈希连接 前两种连接不合用时，才考虑此连接 建立两个输入：组建输入和探查输入 组建输入将符合条件但数据较少的表的字段值计算得到哈希表放在内存中（相同键值记录链接起来）及哈希桶 扫描整个探查输入，计算哈希值，扫描哈希桶，产生符合项 哈希连接用于集合对比作业：内部连接、外连接、半
29、连接、交集等 在没有索引的情况下，SQL SERVER默认哈希连接,组合索引,组合索引就是指建立在多个属性上的索引。 组合索引可以是聚簇的，也可以是非聚簇的。 比较在单个属性上建立的索引，组合索引具有以下优势： 支持前缀匹配查询，支持的前缀就是组合索引（A，B，）的形式。 更易覆盖查询条件，有时一个稠密的组合索引就可以完全回答查询。例如查姓为“罗”，名为“强”的人有多少个。 组合索引是支持多属性唯一性的一个有效办法,组合索引,设计一个组合索引时，必须注意组合索引的顺序 如果查询更倾向于在属性A而不是在属性B上加限定词的话，那么应该建立把A放在B前面的组合索引。 组合索引的缺点： 它们趋向于比较
30、长的索引键。如果不使用压缩方法，这会引起B+树 层数的增加。 因为组合索引包含多个属性，所以对其中任何属性的更新都会导致索引的更新，组合索引的维护代价将会是比较高的。,示例,假设存在组合索引idx_t1c1c2(c1,c2) 查询语句SELECT * FROM t1 WHERE c1=1 AND c2=2能够使用该索引。 查询语句SELECT * FROM t1 WHERE c1=1能够使用该索引。 查询语句SELECT * FROM t1 WHERE c2=2不能够使用该索引，因为没有组合索引的引导列。根据WHERE条件的不同，归纳如下： 1) c1=1 AND c2=2：使用索引idx_t
31、1c1c2进行等值查找 2) c1=1 AND c22：使用索引idx_t1c1c2进行范围查找 3) c11：在B树中命中一条记录，以此向后扫描 4) c11 AND c2=2：c2=2没有参与到索引运算中去,覆盖索引,聚集索引的好处： SQL SERVER找到正确的索引键值后不需要再用指针做额外的搜寻 SQL SERVER将符合相同条件的数据集中放在一起 聚集索引只能建一个，非聚集索引有很多个（最多可达249个） 非聚集索引只能在传回数据量占总数比例很少时才有用 引入覆盖索引（一种的非聚集复合索引）可同样具有聚集索引的两个好处,覆盖索引,覆盖索引是指那些索引项中包含查询所需要的全部信息的非
32、聚簇索引 可以是单索引或复合索引，但是一般都是非聚簇的。 覆盖索引之所以比较快是因为索引页中包含了查询所必须的数据，不需去访问数据页。 如果非聚簇索引中包含结果数据，那么它的查询速度将快于聚簇索引。 什么时候建覆盖索引 经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列。 经常查询涵盖GROUP BY或ORDER BY子句的字段 如果可能尽量使关键查询形成覆盖查询。,覆盖索引,建立覆盖索引的语法： CREATE INDEX 索引名 ON 表名(字段1，字段2，) 示例 EXEC spCleanIdx Member CREATE INDE
33、X idx_LastFirstName ON Member (Lastname、Firstname) SELECT lastname,firstname FROM Member WHERE lastname BETWEEN Funk AND Lang注意： 建立覆盖查询时尽量限制索引项的大小，保持ROW/KEY越大越好，否则扫描覆盖索引与扫描数据表所花的I/O分页差不多，就失去的覆盖索引的意义,在视图与计算字段上建索引,Indexed View,Indexed View把符合定义的数据建立好另外存放，若视图包含汇总函数，建立索引时即完成汇总计算，当更新数据表时，系统会自动维护视图索引的汇总结果
34、 通过视图（含索引）查询时 不需要重新计算汇总，提高性能 可以不必在查询时才做连接运算，提升性能 如果偏向联机事务处理（绝大多数运算是插删改），反而因为要维护索引而降低效率,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 Vdisco
35、unt(ProductID) 查询最高折扣款前五名的产品 SELECT TOP 5 ProductID, SUM(UnitPrice*Quatity*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*(
36、1-Discount) SumDiscount,SUM(UnitPrice*Quatity*Discount) SumDiscountprice FROM dbo.order details GROUP BY ProductID GO CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount(ProductID) 考察求平均值（AVG）的情况 增加子句SUM(Quatity) Units,Indexed View的适用范围,适合建立Indexed View的情况： 减低决策支持查询的负载 对大型数据表做连接以及汇总运算 重复同一种模式的查询
37、 对某些字段重复做汇总运算 重复对相同的数据表、相同的属性做连接 以上方式的综合使用 不适合建立Indexed View的情况： 经常进行插删改的OLTP系统 大量数据字段结合在一起的连接 与原始数据表内容差不多大的Indexed View,数据不连续的处理,数据经过插删改会造成不连续 数据不连续分两种： 内部不连续：物理分页中有许多空间没有记录 外部不连续：磁盘分页与扩展分页不连续，即索引或数据表可能散落在多个扩展分页中，使得其在物理上不连续。 数据不连续会使硬盘读取无效率，而且读出来的数据还需要重新整理 索引需要空间时需要做分割操作 外部不连续只在做大量数据扫描时才影响效率，若只搜索某些记
38、录，利用索引指针就可取得分页,数据不连续的处理,可以执行DBCC SHOWCONTIG指令得到数据表的不连续状况。 数据不连续的处理： 利用DBCC INDEXDEFRAG移除逻辑扫描的外部不连续状况 重建索引可以移除所有的不连续状况 若只是建聚集索引，最好搭配CREATE INDEX WITH DROP_EXISTING,临时表的使用,使用临时表加速查询 把表的一个子集进行排序并创建临时表，有时能加速查询。 它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。 临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。 注意：临时
39、表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。,临时表的使用,避免频繁创建和删除临时表，以减少系统表资源的消耗。 当需要重复引用大型表或常用表中的某个数据集时,可使用临时表。但对一次性事件，最好使用导出表。 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免造成大量 log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert 。 如果使用了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table ，这样可以避
40、免系统表的较长时间锁定。,导出表（内嵌视图）,示例：查询最新五个定单的有关信息 说明：首先需要知道是哪些定单，可以使用返回定单ID的SQL查询来检索。此数据存储在临时表(这是一个常用技术)中，然后与Products表进行联接，以返回这些定单售出的产品数量 CREATE TABLE #Temp1 (OrderID INT,OrderDate DATETIME) INSERT INTO #Temp1 (OrderID, OrderDate) SELECT TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC SEL
41、ECT p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM #Temp1 t INNER JOIN Order Details od ON t.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY p.ProductName DROP TABLE #Temp1 会导致此查询进行大量 I/O 操作,导出表（内嵌视图）,使用导出表(内嵌视图)得到同样的结果： SELECT p.Prod
42、uctName, SUM(od.Quantity) AS ProductQuantity FROM ( SELECT TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC ) t INNER JOIN Order Details od ON t.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GROUP BY p.ProductName ORDER BY p.ProductName 此查询不仅比前面的查询效率更高，
43、而且长度更短。 临时表会消耗大量资源。如果只需要将数据联接到其他查询，则可以使用导出表（内嵌视图），以节省资源。,用户存储过程的使用,存储过程对效率的影响： 统一制定执行计划，还可使用前面的执行计划，得到较高的效率 客户程序向服务器传送较少代码数据 在所有的存储过程和触发器的开始处设置SET NOCOUNT ON，在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC消息。,使用内存配置选项来优化服务器性能,SQL Server 2000的内存管理组件消除了对 SQL Server 可用的内存进行手工管理的需要。 SQL Serv
44、er在启动时根据OS和其它应用程序当前正在使用的内存量，动态确定应分配的内存量。 当计算机和SQL Server上的负荷更改时，分配的内存也随之更改。 下列服务器配置选项可用于配置内存使用并影响服务器性能： min server memory:至少以最小的分配内存量启动 max server memory:指定SQL Server可分配的最大内存量 max worker threads:指定为用户连接提供支持的线程数 index create memory:控制创建索引时排序操作所使用的内存量 min memory per query:指定分配给查询执行的最小内存量,使用内存配置选项来优化服务
45、器性能,服务器配置选项设置应注意的问题： 始终将 min server memory 服务器配置选项设置为某个合理的值，以确保OS不向 SQL Server 请求太多内存而影响 SQL Server 性能 不要将 min server memory 和 max server memory 服务器配置选项设置为相同的值，这样做会使分配给 SQL Server 的内存量固定。 动态内存分配可以随时间提供最佳的总体性能。 一般情况下，应将max worker threads设置为并发连接数，但不能超过 1,024，默认为255。,事务与锁管理,锁 死锁 观察与分析系统的锁定情况 产生阻塞的原因,锁,
46、SQL SERVER中锁的种类： 共享锁 排他锁 更新锁 意向锁 锁的相容性 可锁定的资源 数据库、文件、索引 数据表、分页、索引键值 数据行、应用程序等,锁,锁与事务的四个隔离等级： READ UNCOMMITTED SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 可能读到脏数据 READ COMMITTED SET TRANSACTION ISOLATION LEVEL READ COMMITTED SQL SERVER默认配置 不能读到脏数据，但不可重复读 REPEATABLE READ 可重复读 SERIALIZABLE 防止幻象现象
47、当需要事务的正确性，就会提高事务的隔离等级，但会让并发度减低,死锁,死锁是在DBMS中的某组资源上发生了两个或多个线程之间循环相关性时，由于各个线程之间互不相让对方所需要的资源而造成的。 当客户向数据库提交查询后，客户机可能会感觉到好像“死机”了，这就可能是发生了锁争夺 当系统中出现锁争夺的时候，如果不想让进程永久的等待下去，解决的办法是通过设置锁超时时间间隔。 可以用SET LOCK_TIMEOUT命令设置时间间隔。 SQL Server中有循环死锁和转换死锁两大类。,死锁,循环死锁 由于系统或用户进程之间彼此都只有得到对方持有的资源才能执行时发生 转换死锁 发生在两个或多个进程在事务中持有
48、同一资源的共享锁，而且都需要将共享锁升级为独占锁，但都要待其他进程释放这一共享锁时才能升级。 分布式死锁,观察与分析系统的锁定情况,锁定能造成性能影响，可以从下面几个方面观察系统是否因为锁定与阻塞导致运行问题： 通过企业管理器或系统存储 过程查看是否有许多进程被封锁不能执行 Master.dbo.sysprocessed系统数据表内，被封锁的进程的waittime字段的值异常大 SQL Profiler工具程序所获取的结果中，有许多Attention事件 SQL SERVER所在的机器并没有显得相当忙碌，如CPU、内存或硬盘、网络等硬件使用率并不很高，但效率不好。或某个作业持续高度使用，但作业
49、一直做不完，导致其占有资源无法释放,产生阻塞的原因,产生阻塞的原因： 费时的查询或事务 不正确的事务或事务隔离级别配置 嵌套事务未正确处理 未侦测到的分布式死锁 编译存储过程导致阻塞 减少锁定对索引的要求 数据表最好要有聚集索引 聚集索引不要太大，因所有非聚集索引放有其键值 不能用经常变更的字段做聚集索引，因为聚集索引一改变，所有非聚集索引都需要修改键值，导致大量的锁定。 索引不能建太多，浪费维护资源。,硬盘子系统,硬盘子系统一般来说是数据库的性能瓶颈，因为它是整个系统中运作最慢的部分 数据库系统时时刻刻对硬盘完成插删改和查询的操作，硬盘子系统性能好，自然能提升整体性能 通常的解决方案是通过多
50、个硬盘合作，平均分散工作量来提升效率 由于存取数据模式不同，数据库文件和Log文件和操作系统的内存交换文件最好放在不同的物理硬盘。 SQL SERVER与“恢复间隔”配置相关运作是检查点时间发生的频率 默认“恢复间隔”配置选项配置值为0，可能导致数据库几乎每分钟要发生一次以上的检查点事件，若觉得检查太频繁而导致太多的硬盘I/O有损性能，可尝试将配置加大,硬盘子系统设计,RAID机制 RAID可以不仅可以提升存取效率，还可以加强数据储存的容错能力 RAID 0、RAID 1、RAID 3、RAID 5、RAID 0+1 文件组 文件组的运行原理是靠多个硬盘同时存取，由于数据打散在多个硬盘上，多个硬盘一起运行，可以较有效率地存取 不同对象：数据表、索引等可考虑分散在不同的文件组 不同使用模式的数据文件可通过文件组配置到不同的硬盘，如：系统数据库文件、用户数据库频繁操作的多个表以及日志文件可以分开存放到不同硬盘。 RAID效率大于文件组，因为RAID控制卡一般有很大的缓存区,
