数据库程序设计—— SQL Server 2000 数据库程序设计

数据库程序设计—— SQL Server 2000 数据库程序设计 • 第12章全文索引查询 • 第13章规划索引 • 第14章创建和维护索引 • 第15章实现视图 • 第16章实现存储过程 • 第17章实现用户定义函数 • 第18章实现触发器 • 第19章多服务器编程 • 第20章优化查询性能 • 第21章分析查询 • 第22章管理事务和锁 • 第1章 SQL Server 概述 • 第2章创建和管理数据库 • 第3章创建数据类型和表 • 第4章实现数据完整性 • 第5章 Transact-SQL介绍 • 第6章使用 Transact-SQL查询工具 • 第7章检索数据 • 第8章数据分组与汇总 • 第9章多表联接 • 第10章子查询 • 第11章修改数据

第20章优化查询性能 • 查询优化器介绍 • 获得执行计划信息 • 使用索引来覆盖查询 • 索引策略 • 替代查询优化器 • 推荐操作

查询优化器介绍 20.1 查询优化器介绍 • 查询优化器的功能 • 查询优化器使用基于成本的优化 • 查询优化器工作过程 • 查询优化阶段 • 缓存执行计划 • 设置成本限制

查询优化器的功能 20.1.1 查询优化器的功能 • 查询优化器是负责为查询产生最优化的执行计划的组件 • 决定最有效率的执行计划 • 确定是否存在索引并评估索引对查询的有用性 • 确定可使用哪些索引和列来减少查询所检查的行的数目 • 确定处理联接操作的最佳策略，例如以何种顺序联接表和使用何种联接策略 • 对给定查询的多种计划使用基于成本的评估，以选择出最有效率的计划 • 创建列统计以提高查询性能

查询优化器的功能（续） 20.1.1 查询优化器的功能 • 使用的额外信息 • 底层数据 • 本身的内部操作中得出的分类信息 • 产生执行计划 • 查询优化器产生执行计划，概述了进行查询的步骤及其顺序 • 查询优化器优化寻找、联接、分组和排序行的处理过程

查询优化器使用基于成本的优化 20.1.2 查询优化器使用基于成本的优化 • 查询优化器的优化是基于成本的 • 限制优化计划的数量 • 查询优化器限制它所考虑的优化计划的数量 • 查询优化器选择最快地返回结果且资源消耗合理的计划 • 决定查询处理时间的因素 • 查询性能取决于查询优化器使用的物理运算符以及它们的顺序 • 优化目的是为了减少返回的行数、减少读入页的数目、通过使用最少的 I/O 和 CPU 资源以减少执行计划总的处理时间

查询优化器工作过程 20.1.3 查询优化器工作过程 • 查询提交后，经过多个步骤将原始的查询转换成查询优化器可以解释的格式 • 解析过程：此步骤的输出是解析查询树 • 标准化过程：此步骤的输出是标准化查询树 • 查询优化：包括查询分析、索引选择和联接选择步骤 • 编译：查询被编译成可执行代码 • 数据库访问路由：查询优化器通过进行数据表扫描或使用可用的索引来确定访问数据的最佳方法，然后应用此法

查询优化阶段 20.1.4 查询优化阶段 • 查询优化过程包括三个阶段：查询分析、索引选择和联接选择 • 查询分析 • 查询优化的第一个阶段称为查询分析 • 通过限制搜索，查询优化器尽量减少所处理的行的数目，这样减少了所要读入的索引和数据页的数目 • 在查询分析阶段，查询优化器查看查询的每个子句，确定是否能用来限制扫描的数据量。就是说，确定那个子句是否是个搜索参数（SARG）或联接条件的一部分

查询优化阶段（续） 20.1.4 查询优化阶段 • 索引选择 • 索引选择是查询优化的第二个阶段 • 索引的可用性是根据结果将会返回多少行决定的 • 若 SARG 中使用了索引的第一列，且 SARG 设定了搜索的下界、上界或者二者兼有，那么索引是潜在有用的 • 查询优化器使用索引和非索引列上的统计信息来确定对特定查询最合适的处理策略 • 联接选择 • 联接选择是查询优化的第三个阶段 • 若查询中包含多表联接或者自联接，这个阶段将根据选择性、密度、内存开销等等因素来评估使用何种联接策略

缓存执行计划 20.1.5 缓存执行计划 • SQL Server 用一个内存池来存储执行计划和数据缓存 • 存储执行计划的那部分内存池称为过程缓存 • 不太可能重用的和成本低的计划不放入缓存中 • 在内存中存储执行计划 • 执行计划是可重用的、只读的数据结构，可被任意数量的用户使用，其中并不存储用户环境 • 内存中执行计划的副本从不超过两份：一份供所有的串行执行，另一份供所有的并行执行，而无论并行度如何

缓存执行计划（续） 20.1.5 缓存执行计划 • 使用执行环境 • 执行查询的用户都有一个数据结构，用于保存执行的特定数据，例如参数值。这个数据结构称为执行环境 • 当执行一条 Transact-SQL 语句的时候，SQL Server 扫描过程缓存，若找到相同语句的执行计划，则重用之；否则 SQL Server 为查询产生新的执行计划

缓存执行计划（续） 20.1.5 缓存执行计划 • 重新编译执行计划 • 数据库的某些变化会使执行计划效率变低或者不再准确 • 当 SQL Server 检测到使执行计划无效的变化时，将执行计划标记为无效。当下次连接执行查询的时候，编译新的执行计划

缓存执行计划（续） 20.1.5 缓存执行计划 • 使执行计划无效的情形 • 对查询所引用的表或视图的任何结构性的变化（ALTER TABLE 及 ALTER VIEW 语句） • 显式地用语句例如 UPDATE STATISTICS 或自动地产生新的分布统计信息 • 删除执行计划使用的索引 • 显式调用系统存储过程 sp_recompile • 对键的大量更改，或者对查询引用的表的大量 INSERT 或 DELETE 语句 • 对定义了触发器的表，若 inserted 或 deleted 表中行的数目显著增长

设置成本限制 20.1.6 设置成本限制 • 可以通过设定成本限制来控制执行查询的开销 • 查询成本是指在特定硬件配置中，执行查询所耗费的估计时间（以秒为单位） • 指定上限：通过查询调控器实现 • 可以使用 query governor cost limit选项阻止执行长时间运行的查询，从而防止消耗系统资源 • 虽然配置值是用秒指定的，但实际上与时间无关，而是查询的实际估计成本。可以指定允许查询运行的成本上限

设置成本限制（续） 20.1.6 设置成本限制 • 指定连接的限制 • 使用存储过程 sp_configure 对所有连接应用成本限制 • 运行 SET QUERY_GOVERNOR_COST_LIMIT 语句对特定连接应用成本限制 • 指定为 0（默认值）将关闭查询调控器

获得执行计划信息 20.2 获得执行计划信息 • 查看 STATISTICS 语句输出 • 查看 SHOWPLAN_ALL 和 SHOWPLAN_TEXT 输出 • 以图形方式查看执行计划

查看 STATISTICS 语句输出 20.2.1 查看 STATISTICS 语句输出 • STATISTICS TIME • 显示分析、编译和执行语句所需的时间（以毫秒为单位） • STATISTICS PROFILE • 显示语句的配置文件信息。当执行查询的时候，附加结果集里包含查询的 SHOWPLAN_ALL 列和下面这些附加列

查看 STATISTICS 语句输出（续） 20.2.1 查看 STATISTICS 语句输出 • STATISTICS IO • 显示查询产生的读入页面的次数信息。STATISTICS IO 的输出包括

查看 SHOWPLAN_ALL 和 SHOWPLAN_TEXT 输出 20.2.2 查看 SHOWPLAN_ALL 和 SHOWPLAN_TEXT 输出 • SHOWPLAN 语句输出的结构 • 以行集的形式返回信息 • 构成一棵层次树 • 表示了执行每条语句时查询优化器采取的步骤 • 显示查询优化的估计值，而不是实际的执行计划。估计值是基于现有统计信息的

查看 SHOWPLAN_ALL 和 SHOWPLAN_TEXT 输出（续） 20.2.2 查看 SHOWPLAN_ALL 和 SHOWPLAN_TEXT 输出 • 执行步骤的详细资料 • 哪一个表使用了哪一个索引 • 表的联接顺序 • 所选择的更新模式 • 工作策略以及其他策略 • SHOWPLAN_TEXT 和 SHOWPLAN_ALL 输出的不同 • SHOWPLAN_ALL 的输出返回额外信息，例如估计查询返回的行数、I/O、CPU 和平均行大小

以图形方式查看执行计划 20.2.3 以图形方式查看执行计划 • 图形执行计划的元素 • 读取图形执行计划的输出 • 使用 Bookmark Lookup 操作

以图形方式查看执行计划（续） 20.2.3 以图形方式查看执行计划 • 图形化执行计划使用图标表示语句和查询的特定部分的执行，它包含以下元素 • 步骤是用来处理查询的工作的单位 • 步骤序列是步骤处理的顺序 • 逻辑运算符描述用于处理语句的关系代数操作，如执行聚合等 • 物理运算符描述用于处理语句的物理实现算法，如扫描聚集索引

以图形方式查看执行计划（续） 20.2.3 以图形方式查看执行计划 • 在 SQL 查询分析器中，从右到左、从上到下读取图形执行计划输出 • 每个步骤可能有一个或多个处理节点 • 节点指的是查询优化器使用的一个操作，用图标表示 • 每个节点都与一个父节点相关 • 所有具有相同父节点的节点都绘制在相同的列内 • 用箭头将每个节点连接到其父节点 • 递归操作用迭代符号显示 • 运算符显示为与特定父节点相关的符号 • 如果查询包含多个语句，则绘制多个查询执行计划

以图形方式查看执行计划（续） 20.2.3 以图形方式查看执行计划 • 查看额外信息 • 当将游标指向每个节点（用图标表示）时，可以看到除下表之外的关于物理和逻辑运算符的详细信息

以图形方式查看执行计划（续） 20.2.3 以图形方式查看执行计划 • Bookmark Lookup（书签查找）是查询优化器频繁使用的内部操作 • 当查询优化器识别出结果集的可能候选记录时，它记录标识行位置的信息（书签）并继续改善查询 • 一般在分析查询计划的所有步骤处理完之后使用书签查找操作 • 检索行 • 行标识符：在堆集中找到相应的行 • 聚集键：在聚集索引中找到相应的行

以图形方式查看执行计划（续） 20.2.3 以图形方式查看执行计划 • 观察详情：在查询计划中书签查找操作的详情包括 • 用来在表或聚集索引中查找行的书签标签 • 行所在的表的名称或聚集索引的名称 • 若查询优化器确定预读是从表或聚集索引中找到书签的最好办法，则包含 WITH PREFETCH 子句 • 确定何时使用书签查找操作 • 在 WHERE 子句中包含 IN 子句或 OR 运算符的查询

使用索引来覆盖查询 20.3 使用索引来覆盖查询 • 覆盖查询的索引介绍 • 使用覆盖查询的索引定位数据 • 识别可以用来覆盖查询的索引 • 确定是否使用索引覆盖查询 • 创建覆盖查询的索引的指导原则

覆盖查询的索引介绍 20.3.1 覆盖查询的索引介绍 • 可创建覆盖常用查询的索引，以减少 I/O 数量 • 只有非聚集索引能覆盖查询 • 索引必须包含所有查询中引用的列 • 当查询被索引覆盖时，查询优化器不需要访问数据页 • 索引视图可以预聚合数据，提高查询性能 • 覆盖查询的索引能快速地检索数据

使用覆盖查询的索引定位数据 20.3.2 使用覆盖查询的索引定位数据 • 单页导航示例 • 部分扫描导航示例 • 完全扫描导航示例

Lang Akhtar Akhtar Eric Sarah Sarah Lang … … … … Eric … … Ganio … Jon … … … … … … … SELECT lastname, firstname FROM member WHERE lastname = 'Hall' 索引页改非叶级 Akhtar Sarah Ganio Jon Barr … Hall Hall Don Don Barr … Hart Sherri 叶级（键值） Borm … Jones Amy Lang Eric Buhl … Jones Beverly … … … Martin … Martin … Martin … 数据页改 Moris … 使用覆盖查询的索引定位数据（续） 20.3.2 使用覆盖查询的索引定位数据单页导航发生在只有一个叶级页被读入的情形

USE credit SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Funk' AND 'Lang' Akhtar … Jordan Akhtar … … Jordan … 索引页改 Chai Lang … … … … 非叶级 Morgan Dunn … … Ganio Smith … … Dunn … Ganio … Jordan … Lang … 叶级（键值） Dunn … Hall … Kim … Martin … Fine … Hart … Kim … Martin … Fort … Jones … Koch … Martin … Chai Akhtar … … Smith Morgan … … Funk … Jones … Koch … Moris … Con Barr … … Nash Smith … … Con Barr … … Smith Nay … … Borm Cox … … Smith Ota … … 数据页改 Dale Buhl … … Smith Rudd … … 使用覆盖查询的索引定位数据（续） 20.3.2 使用覆盖查询的索引定位数据部分导航发生在从叶级读入一个范围的页的情形

USE credit SELECT lastname, firstname FROM member 索引页改非叶级 Akhtar … Chai … Dunn … Ganio … Jordan … Lang … Morgan … Smith … 叶级（键值） Barr … Con … Dunn … Hall … Kim … Martin … Nash … Smith … Barr … Con … Fine … Hart … Kim … Martin … Nay … Smith … Borm … Cox … Fort … Jones … Koch … Martin … Ota … Smith … … Buhl … Dale … Funk … Jones … Koch … Moris … Rudd … Smith … Martin Akhtar Akhtar Smith Ganio … 数据页改 … … Martin 使用覆盖查询的索引定位数据（续） 20.3.2 使用覆盖查询的索引定位数据完全扫描导航发生在读入所有叶级页的情形

识别可以用来覆盖查询的索引 20.3.3 识别可以用来覆盖查询的索引 • 以下事实影响索引覆盖查询的能力 • 查询需要的所有数据都必须包含在索引中，这包括所有被引用的列 • 组合索引中的非首列对覆盖查询也是有用的 • WHERE 子句不是必需的，查询优化器会扫描所有叶级页 • 若使用包含 WHERE 子句中所引用列的非聚集索引比聚集索引需要更少的 I/O 操作，则使用非聚集索引覆盖查询 • 可以联接多个索引以覆盖查询

确定是否使用索引覆盖查询 20.3.4确定是否使用索引覆盖查询 • 观察图形化执行计划输出 • 若执行计划输出显示“扫描非聚集索引，可以扫描全部内容，也可以只扫描一个范围”，则查询优化器可用索引覆盖查询 • 比较 I/O 操作 • 通过查看 STATISTICS IO 输出，比较 I/O 操作的次数来判定是否覆盖了查询 • 应该事先了解的信息 • 非聚集索引：非叶级的总级数、组成叶级的页的总数、每个叶级页的行的总数、每个数据页的行的总数 • 表：组成表的页的总数

创建覆盖查询的索引的指导原则 20.3.5 创建覆盖查询的索引的指导原则 • 当创建覆盖查询的索引时，考虑如下指导原则 • 往索引中添加合适的列 • 尽量减小索引键的大小 • 维护行和键值大小的比率

索引策略 20.4 索引策略 • 估算访问一定范围数据的查询所使用的 I/O 数量 • 多查询的索引 • 创建索引的指导原则

SELECT charge_noFROM chargeWHERE charge_amt BETWEEN 20 AND 30 访问方法页 I/O 表扫描 10 417 charge_amt 列上聚集索引 1 042 charge_amt 列上非聚集索引 100 273 charge_amt 和 charge_no 列上组合索引 273 估算访问一定范围数据的查询所使用的 I/O 数量 20.4.1估算访问一定范围数据的查询所使用的 I/O 数量查询优化器自动考虑多个执行计划并评估每个执行计划所需要的 I/O，然后使用最少 I/O 的执行计划

示例 1 USE credit SELECT charge_no, charge_dt, charge_amt FROM charge WHERE statement_no = 19000 AND member_no = 3852 示例 2 USE credit SELECT member_no, charge_no, charge_amt FROM charge WHERE charge_dt between '07/30/1999' AND '07/31/1999' AND member_no = 9331 多查询的索引 20.4.2 多查询的索引为多个查询选择合适的索引比为单个查询困难，因为对一条查询是最佳的索引对其他查询未必是最佳的。目标是通过评估 I/O，使所有高优先级的查询获得可接受的性能

创建索引的指导原则 20.4.3 创建索引的指导原则 • 创建对查询优化器有用的索引的指导原则 • 确定所有查询的优先权 • 确定每个查询的 WHERE 子句的每个部分的选择性 • 确定是否创建索引 • 确定应该被索引的列 • 确定组合索引的最佳列顺序 • 确定对其他索引的需求程度 • 创建索引后，测试每个优先级最高的查询的性能

替代查询优化器 20.5 替代查询优化器 • 确定何时替代查询优化器 • 使用提示和 SET FORCEPLAN 语句 • 替代查询优化器之后确认查询性能

确定何时替代查询优化器 20.5.1 确定何时替代查询优化器 • 若查询无法有效执行，可使用优化器提示替代查询优化器 • 优化器提示是包含在查询中的关键字，用来强制特定的优化操作 • 应该限制使用优化器提示，因为它生成的优化是静态的 • 优化器提示使查询优化器不能随变化的环境而调整 • 使用优化器提示后，应该经常监视查询性能以检验查询是否被优化执行 • 考虑替代查询优化器之前，应尝试其他方法，如 • 更新统计信息 • 重新编译存储过程 • 重新查看查询或 SARG 以确定是否要重写 • 评估建立不同索引的可能性

使用提示和 SET FORCEPLAN 语句 20.5.2 使用提示和 SET FORCEPLAN 语句 • 可使用提示或 SET FORCEPLAN 语句替代查询优化器 • 可在 SELECT、INSERT、UPDATE 或 DELETE 语句内指定优化器提示 • 一共有三种提示可用来替代查询优化器：表提示、联接提示、查询提示 • 表提示：表提示指定了表扫描、查询优化器使用的一个或多个索引、查询优化器在表或查询上使用的锁定方法 • 虽然可以指定多个表提示，但每个表提示只能指定一次 • WITH 子句必须在紧接着表名称之后指定 • 联接提示 • 联接提示在两表间强制联接策略。联接提示在查询的 FROM 子句指定

使用提示和 SET FORCEPLAN 语句（续） 20.5.2 使用提示和 SET FORCEPLAN 语句 • 查询提示：可控制更大范围内的动作。通过使用 OPTION 子句，可指定查询优化器使用特定提示 • 可指定多个提示，但每个提示只能指定一次 • OPTION 子句必须与语句最外层的查询一起指定 • 查询提示影响语句中的所有操作 • 若主查询中使用了 UNION，只有最后一个使用 UNION 运算符的查询可以使用 OPTION 子句 • SET FORCEPLAN 语句 • 可以强制查询优化器以表在 FROM 子句中列出的顺序联接表。当使用 SET FORCEPLAN 语句时，查询优化器只使用嵌套循环联接 • 语法：SET FORCEPLAN {ON | OFF}

替代查询优化器之后确认查询性能 20.5.3 替代查询优化器之后确认查询性能 • 校验性能改善 • 为了校验优化器提示是否改善性能，打开 STATISTICS IO 和 STATISTICS TIME 选项并在查询分析器中选择“显示执行计划” • 将使用优化器提示的原因写入文档 • 若替代查询优化器改善了性能，确保将原因写入文档 • 经常重新测试查询 • 查询优化器是动态的，随数据变化不断评估最好的执行计划。若使用优化器提示，则执行计划变成静态的。所以，应该经常重新测试查询

使用查询调控器阻止长时间运行的查询消耗系统资源使用查询调控器阻止长时间运行的查询消耗系统资源充分理解数据，以及查询如何访问数据创建覆盖最常用的查询的索引为单个和多个查询建立索引策略尽量避免替代查询优化器推荐操作 20.6 推荐操作

数据库程序设计—— SQL Server 2000 数据库程序设计