第九章关系查询处理和查询优化
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
×
sc student 查询树
cno ' 2 '
sc
基于启发式规则的查询物理优化
1、选择操作的启发式规则 • 对于小关系使用全表顺序扫描，即使有索引； • 对于选择条件为主码的等值查询，选择主码索引检索； • 对于选择条件为非主码的值比较查询，并且选择列上有索引，则要估算结 果的元组数目，若数目较少，则在索引上检索，否则在全表上检索； • 对于用and连接的查询条件，若有涉及属性的组合索引，则优先采用组合索 引检索；若只是某些属性上有索引，可采用选择操作的三种实现方法之一 进行，否则采用全表检索； • 对于用or连接的查询条件，一般使用全表顺序扫描。 2、连接操作的启发式规则 • 若两个表都已按连接属性排序，则选用排序合并法； • 若一个表在连接属性上有索引，则可选用索引连接法； • 若上面2条都不适用，其中一个表较小，则可选用散列法； • 最后可选用嵌套循环法，并选择较小的表作为外层。
索引统计信息
作用：SQL Server允许创建有关列中值分布情况统计信息。查询优化器使用这 些统计信息并通过估计使用索引评估查询的开销来确定最佳查询计划。 知识要点： • CREATE STATISTICS语句可用于手工创建统计信息； • 创建统计信息后，数据库引擎对列值进行排序； • 根据这些列值（最多200个，按间隔分隔开）创建一个“直方图”； • 直方图指定有多少行精确匹配每个间隔值，有多少行在间隔范围内，以及 间隔中值的密度大小或重复值的发生率； • 对字符类型的列创建“字符串摘要”信息，使LIKE条件可以更准确地估计 结果集大小，并不断优化查询计划； • 如果列样本摘要的大小超过了数据库引擎可以维护的范围，则不对字符串 摘要信息进行维护； • 创建索引时，查询优化器自动存储有关索引列的统计信息。另外，当数据 库AUTO_CREATE_STATISTICS选项设臵为ON（默认值）时，数据库引 擎自动为没有索引的列创建统计信息。
sname
select sname from student,sc where student.sno = sc.sno and cno =‘2’
sname
student .sno sc.sno
优化 student ×
student .sno sc.sno
cno ' 2 '
SQL执行效率低分析（续）
SET STATISTICS IO ON 报告与语句中引用的每个表的扫描数、逻辑读取数（在高速缓存中访问 的页数）和物理读取数（访问磁盘的次数）的相关信息。 SET STATISTICS TIME ON 显示分析、编译和执行查询所需的时间（毫秒）。 上述SET语句的使用方法：先执行SET语句，然后执行要分析的SQL语 句。 将ON改为OFF将关闭该功能。 4、是否已经使用了统计信息优化查询？ SQL Server自动在索引列上创建对列内值的分布情况的统计信息。也可以 手动（使用create statistics语句）或自动（将auto_create_statistics数据库选项 设臵为true）在非索引列上创建统计信息。查询处理器可以利用这些统计信息 来确定最佳的查询评估策略，以提高查询性能。
第九章 关系查询处理和查询优化
查询语句
查询处理过程
词法分析 语法分析 语义分析 符号名转换 数据库 数据字典 安全性检查 完整性检查 查询树 代数优化 物理优化 查询优化 查询分析
本章内容对DBA很重要。
查询检查
执行策略描述 代码生成
查询计划的执 行代码 查询执行
查询操作的实现
1、选择操作： • 简单的全表扫描：顺序扫描，逐一检查每个元组； • 索引（散列）扫描：选择条件有索引，可按索引扫描，用指针取元组； • 对形如“条件1 and 条件2”选择的处理： ① 分别检索满足条件1的元组指针和满足条件2的元组指针，然后求两指针 集合的交集，按交集指针取元组； ② 先检索满足条件1的元组指针集合，再在该结果集合中检索满足条件2的 元组； ③ 全表扫描，直接检索出满足条件的元组。 2、连接操作： • 嵌套循环法：对外层的每一个元组，检索内层的每一个元组； • 排序合并法：对连接属性排序，然后进行归并； • 索引连接法：若连接属性有索引，则直接进行归并。若没有则建立； • 散列连接法：将连接属性散列到一个桶中，再进行连接。
索引统计信息（续）
• 随着列中数据发生变化，索引和列的统计信息可能会过时，从而导致查询 优化器选择的查询处理方法不是最佳的； • 当AUTO_UPDATE_STATISTICS数据库选项设臵为ON（默认值）时，查 询优化器会在表中的数据发生变化时自动定期更新这些统计信息。常规情 况是：在大约有20%的数据行发生变化时更新统计信息； • 从磁盘读取一个数据页后，该数据页上的所有行都被用来更新统计信息。 对于小于8MB的表，则始终进行完整扫描来收集统计信息； • 有时统计采样无法获得表数据的精确特征。可使用UPDATE STATISTICS 语句的SAMPLE子句和FULLSCAN子句，前者用来指定采样的行数百分比 或采样的行数，后者指定扫描表中的所有数据来收集统计信息； • 使用UPDATE STATISTICS语句的NORECOMPUTE子句针对特定列或索 引禁用统计信息自动生成功能； • CREATE STATISTICS语句对特定表或视图列创建统计信息； • UPDATE STATISTICS语句手动更新统计信息； • DBCC SHOW_STATISTICS语句查看表的统计信息； • DROP STATISTICS语句删除列的统计信息。
一个现实的问题
问题：你知道SQL Server是怎么执行下面语句的吗？ select sname,cname,grade from student,course,sc where student.sno = sc.sno and o = o
在查询分析器中点击“显示估计的查询计划”按钮，即可看到其执行方案。 问题：知道了又能怎样呢？ 如果SQL语句执行速度很慢，用户对应用程序失去等待耐心，以至愤怒。 这个问题不严重吗？ 问题：你知道效率低的原因是什么造成的吗？知道原因后你又怎么解决呢？
( E1 ) B1 , B2 , ( E2 )
, Bn
1 2
E2 ) F1 ( E1 )
, An
F ( E2 )
2
,
( E1
E2 ) A1 , A2 ,
, An
( E1 )
A ,A
1
2,
, An
查询的启发式优化
• • • • 选择运算尽可能先做 把投影运算和选择运算同时进行 把投影同其前或其后的双目运算结合起来同时进行 把某些选择同在它前面要执行的笛卡尔积结合成一个连接运算（尤其是等 值连接运算） • 找出公共子表达式并将其中间结果临时保留
SQL执行效率低分析（续）
5、查询统计是最新的吗？统计信息是自动更新的吗？ 最新的统计信息不取决于日期或时间数据。如果尚未执行更新操作，则查 询统计信息仍是最新的。统计信息可设臵为自动更新。
6、有合适的索引吗？添加一个或多个索引会不会提高查询性能？
7、数据分布在多个磁盘驱动器上会不会提高查询性能？若是磁盘阵列，是不 是数据在磁盘上的分布不合理？ 8、是否为查询优化器提供了优化复杂查询的最有利条件？ 如：添加更多的内存，使用多个处理器等。 9、如果数据量很大，需要将其分区吗？ 分区的主要优点是便于数据管理，但如果将数据的表和索引进行相似的分 区，则分区还可以提高查询性能。
估算代价示例： • 全表扫描：代价为B；若选择条件为码=值，则平均代价为B/2； • 索引扫描：若选择条件为码=值，则代价为L+1；若选择条件为非码属性=值， 则代价为L+S；若比较的不是相等的条件(如>),假设估计有一半的元组满足 条件，则代价为L+Y/2+B/2； • 嵌套循环连接：令Br、Bs分别为外表和内表的块数，连接使用内存K块， 分配K-1块给外表，则代价为Br+(Br/K-1)Bs。
基于代价的查询物理优化
问题：你还记得“代价”是指什么吗？ 统计信息： • 对每个基表，该表元组数N、元组长度、占用块数B等等； • 对其表的列，该列不同值的个数、选择率(=该值元组数/N)、最大值、最小 值、该列是否建了索引，是哪种索引等等； • 对索引，如B+树索引，该索引的层数L、不同索引值的个数、索引的选择 基数S(即有S个元组具有某个索引值)、索引的叶结点数Y等等
SQL执行效率低分析
SQL执行效率低的原因很多，可能是由运行SQL Server的网络或计算机相 关的性能问题引起的，也可能是物理数据库设计问题引起的等等。 1、是与组件（而不是与SQL）相关的性能问题吗？例如，是网络性能低的问 题吗？有其他可能引起或造成性能降低的组件吗？ Windows 系统监视器可用于监视相关组件的性能。 2、如果效率问题与查询相关，那么涉及到的是哪个或哪组查询语句？ 使用SQL Server Profiler来帮助找出运行慢的SQL语句。 3、在找出运行慢的查询后，可使用以下选项，进一步分析查询的性能： SET SHOWPLAN_ALL ON 描述SQL Server查询优化器选择的数据检索方法。 SET STATISTICS PROFILE ON 显示每个查询执行后的结果集，并显示查询执行的概要信息。
E2 E1
, Bn
F1
F2
E2 )
, An
A ,A
1
,
( B1 , B2 ,
( E )) A1 , A2 ,
, An
2
(E)
F 1 ( F 2 ( E )) F 1 F 2 ( E ) F ( A , A , , A ( E )) A , A ,
1 2 n 1 2 1
cno ' 2 ' sc
问题：哪个方案执行效率高？为什么？
关系代数等价变换
E1 E2 E2 E1 E1 ( E1
F
E2 E2 E2 )
, An
F
E1 ( E2
注意：有些等价是有条件的。
( E1 E2 ) E3 E1 ( E2 E3 )
F1
2
F2
查询优化
目的：提高查询的执行速度。 目标：查询执行的总代价尽可能小。 总代价 = IO代价 + CPU代价 + 内存代价 + 通信代价 例如：select sname from student,sc where student.sno = sc.sno and cno =‘2’
Q1: sname student .sno sc.sno cno ' 2 ' student sc Q1: sname cno ' 2 ' student Q1: sname student sc
( F ( E ))
F ( E1 E2 ) F ( E1 ) F ( E2 ) F ( E1 A ,A
1 2
E2 ) F1 ( E1 )
, An , B1 , B2 , , Bn
F ( E2 )
2
,
( E1 E2 ) A1 , A2 ,
, An
创建索引的一般性准则
• 一个表若建有大量索引会影响更新语句的性能，因为在表中更改数据时， 所有索引都须进行适当的调整； • 避免对经常更新的表建立过多的索引，并且索引列要尽可能少； • 使用多个索引可以提高更新少而数据量大的查询性能，因为查询优化器有 更多的索引可供选择，从而可以确定最快的访问方法； • 对小表进行索引可能不会产生优化效果。小表的索引可能从来不用，但仍 必须在表中的数据更改时进行维护； • 为经常用于查询中的谓词和联接条件的所在列创建非聚集索引； • 涵盖索引可以提高查询性能。例如，对某一表（其中对列a、列b和列c创建 了组合索引）的列a和列b查询，则仅从该索引本身就可以检索到数据，因 此，减少了总体磁盘I/O； • 对于聚集索引，保持较短的索引键长度； • 如果索引包含多个列，则应考虑列的顺序。WHERE子句中参与比较运算或 者参与连接的列应该放在最前面，其他列从最不重复的列到最重复的列；
sc student 查询树
cno ' 2 '
sc
基于启发式规则的查询物理优化
1、选择操作的启发式规则 • 对于小关系使用全表顺序扫描，即使有索引； • 对于选择条件为主码的等值查询，选择主码索引检索； • 对于选择条件为非主码的值比较查询，并且选择列上有索引，则要估算结 果的元组数目，若数目较少，则在索引上检索，否则在全表上检索； • 对于用and连接的查询条件，若有涉及属性的组合索引，则优先采用组合索 引检索；若只是某些属性上有索引，可采用选择操作的三种实现方法之一 进行，否则采用全表检索； • 对于用or连接的查询条件，一般使用全表顺序扫描。 2、连接操作的启发式规则 • 若两个表都已按连接属性排序，则选用排序合并法； • 若一个表在连接属性上有索引，则可选用索引连接法； • 若上面2条都不适用，其中一个表较小，则可选用散列法； • 最后可选用嵌套循环法，并选择较小的表作为外层。
索引统计信息
作用：SQL Server允许创建有关列中值分布情况统计信息。查询优化器使用这 些统计信息并通过估计使用索引评估查询的开销来确定最佳查询计划。 知识要点： • CREATE STATISTICS语句可用于手工创建统计信息； • 创建统计信息后，数据库引擎对列值进行排序； • 根据这些列值（最多200个，按间隔分隔开）创建一个“直方图”； • 直方图指定有多少行精确匹配每个间隔值，有多少行在间隔范围内，以及 间隔中值的密度大小或重复值的发生率； • 对字符类型的列创建“字符串摘要”信息，使LIKE条件可以更准确地估计 结果集大小，并不断优化查询计划； • 如果列样本摘要的大小超过了数据库引擎可以维护的范围，则不对字符串 摘要信息进行维护； • 创建索引时，查询优化器自动存储有关索引列的统计信息。另外，当数据 库AUTO_CREATE_STATISTICS选项设臵为ON（默认值）时，数据库引 擎自动为没有索引的列创建统计信息。
sname
select sname from student,sc where student.sno = sc.sno and cno =‘2’
sname
student .sno sc.sno
优化 student ×
student .sno sc.sno
cno ' 2 '
SQL执行效率低分析（续）
SET STATISTICS IO ON 报告与语句中引用的每个表的扫描数、逻辑读取数（在高速缓存中访问 的页数）和物理读取数（访问磁盘的次数）的相关信息。 SET STATISTICS TIME ON 显示分析、编译和执行查询所需的时间（毫秒）。 上述SET语句的使用方法：先执行SET语句，然后执行要分析的SQL语 句。 将ON改为OFF将关闭该功能。 4、是否已经使用了统计信息优化查询？ SQL Server自动在索引列上创建对列内值的分布情况的统计信息。也可以 手动（使用create statistics语句）或自动（将auto_create_statistics数据库选项 设臵为true）在非索引列上创建统计信息。查询处理器可以利用这些统计信息 来确定最佳的查询评估策略，以提高查询性能。
第九章 关系查询处理和查询优化
查询语句
查询处理过程
词法分析 语法分析 语义分析 符号名转换 数据库 数据字典 安全性检查 完整性检查 查询树 代数优化 物理优化 查询优化 查询分析
本章内容对DBA很重要。
查询检查
执行策略描述 代码生成
查询计划的执 行代码 查询执行
查询操作的实现
1、选择操作： • 简单的全表扫描：顺序扫描，逐一检查每个元组； • 索引（散列）扫描：选择条件有索引，可按索引扫描，用指针取元组； • 对形如“条件1 and 条件2”选择的处理： ① 分别检索满足条件1的元组指针和满足条件2的元组指针，然后求两指针 集合的交集，按交集指针取元组； ② 先检索满足条件1的元组指针集合，再在该结果集合中检索满足条件2的 元组； ③ 全表扫描，直接检索出满足条件的元组。 2、连接操作： • 嵌套循环法：对外层的每一个元组，检索内层的每一个元组； • 排序合并法：对连接属性排序，然后进行归并； • 索引连接法：若连接属性有索引，则直接进行归并。若没有则建立； • 散列连接法：将连接属性散列到一个桶中，再进行连接。
索引统计信息（续）
• 随着列中数据发生变化，索引和列的统计信息可能会过时，从而导致查询 优化器选择的查询处理方法不是最佳的； • 当AUTO_UPDATE_STATISTICS数据库选项设臵为ON（默认值）时，查 询优化器会在表中的数据发生变化时自动定期更新这些统计信息。常规情 况是：在大约有20%的数据行发生变化时更新统计信息； • 从磁盘读取一个数据页后，该数据页上的所有行都被用来更新统计信息。 对于小于8MB的表，则始终进行完整扫描来收集统计信息； • 有时统计采样无法获得表数据的精确特征。可使用UPDATE STATISTICS 语句的SAMPLE子句和FULLSCAN子句，前者用来指定采样的行数百分比 或采样的行数，后者指定扫描表中的所有数据来收集统计信息； • 使用UPDATE STATISTICS语句的NORECOMPUTE子句针对特定列或索 引禁用统计信息自动生成功能； • CREATE STATISTICS语句对特定表或视图列创建统计信息； • UPDATE STATISTICS语句手动更新统计信息； • DBCC SHOW_STATISTICS语句查看表的统计信息； • DROP STATISTICS语句删除列的统计信息。
一个现实的问题
问题：你知道SQL Server是怎么执行下面语句的吗？ select sname,cname,grade from student,course,sc where student.sno = sc.sno and o = o
在查询分析器中点击“显示估计的查询计划”按钮，即可看到其执行方案。 问题：知道了又能怎样呢？ 如果SQL语句执行速度很慢，用户对应用程序失去等待耐心，以至愤怒。 这个问题不严重吗？ 问题：你知道效率低的原因是什么造成的吗？知道原因后你又怎么解决呢？
( E1 ) B1 , B2 , ( E2 )
, Bn
1 2
E2 ) F1 ( E1 )
, An
F ( E2 )
2
,
( E1
E2 ) A1 , A2 ,
, An
( E1 )
A ,A
1
2,
, An
查询的启发式优化
• • • • 选择运算尽可能先做 把投影运算和选择运算同时进行 把投影同其前或其后的双目运算结合起来同时进行 把某些选择同在它前面要执行的笛卡尔积结合成一个连接运算（尤其是等 值连接运算） • 找出公共子表达式并将其中间结果临时保留
SQL执行效率低分析（续）
5、查询统计是最新的吗？统计信息是自动更新的吗？ 最新的统计信息不取决于日期或时间数据。如果尚未执行更新操作，则查 询统计信息仍是最新的。统计信息可设臵为自动更新。
6、有合适的索引吗？添加一个或多个索引会不会提高查询性能？
7、数据分布在多个磁盘驱动器上会不会提高查询性能？若是磁盘阵列，是不 是数据在磁盘上的分布不合理？ 8、是否为查询优化器提供了优化复杂查询的最有利条件？ 如：添加更多的内存，使用多个处理器等。 9、如果数据量很大，需要将其分区吗？ 分区的主要优点是便于数据管理，但如果将数据的表和索引进行相似的分 区，则分区还可以提高查询性能。
估算代价示例： • 全表扫描：代价为B；若选择条件为码=值，则平均代价为B/2； • 索引扫描：若选择条件为码=值，则代价为L+1；若选择条件为非码属性=值， 则代价为L+S；若比较的不是相等的条件(如>),假设估计有一半的元组满足 条件，则代价为L+Y/2+B/2； • 嵌套循环连接：令Br、Bs分别为外表和内表的块数，连接使用内存K块， 分配K-1块给外表，则代价为Br+(Br/K-1)Bs。
基于代价的查询物理优化
问题：你还记得“代价”是指什么吗？ 统计信息： • 对每个基表，该表元组数N、元组长度、占用块数B等等； • 对其表的列，该列不同值的个数、选择率(=该值元组数/N)、最大值、最小 值、该列是否建了索引，是哪种索引等等； • 对索引，如B+树索引，该索引的层数L、不同索引值的个数、索引的选择 基数S(即有S个元组具有某个索引值)、索引的叶结点数Y等等
SQL执行效率低分析
SQL执行效率低的原因很多，可能是由运行SQL Server的网络或计算机相 关的性能问题引起的，也可能是物理数据库设计问题引起的等等。 1、是与组件（而不是与SQL）相关的性能问题吗？例如，是网络性能低的问 题吗？有其他可能引起或造成性能降低的组件吗？ Windows 系统监视器可用于监视相关组件的性能。 2、如果效率问题与查询相关，那么涉及到的是哪个或哪组查询语句？ 使用SQL Server Profiler来帮助找出运行慢的SQL语句。 3、在找出运行慢的查询后，可使用以下选项，进一步分析查询的性能： SET SHOWPLAN_ALL ON 描述SQL Server查询优化器选择的数据检索方法。 SET STATISTICS PROFILE ON 显示每个查询执行后的结果集，并显示查询执行的概要信息。
E2 E1
, Bn
F1
F2
E2 )
, An
A ,A
1
,
( B1 , B2 ,
( E )) A1 , A2 ,
, An
2
(E)
F 1 ( F 2 ( E )) F 1 F 2 ( E ) F ( A , A , , A ( E )) A , A ,
1 2 n 1 2 1
cno ' 2 ' sc
问题：哪个方案执行效率高？为什么？
关系代数等价变换
E1 E2 E2 E1 E1 ( E1
F
E2 E2 E2 )
, An
F
E1 ( E2
注意：有些等价是有条件的。
( E1 E2 ) E3 E1 ( E2 E3 )
F1
2
F2
查询优化
目的：提高查询的执行速度。 目标：查询执行的总代价尽可能小。 总代价 = IO代价 + CPU代价 + 内存代价 + 通信代价 例如：select sname from student,sc where student.sno = sc.sno and cno =‘2’
Q1: sname student .sno sc.sno cno ' 2 ' student sc Q1: sname cno ' 2 ' student Q1: sname student sc
( F ( E ))
F ( E1 E2 ) F ( E1 ) F ( E2 ) F ( E1 A ,A
1 2
E2 ) F1 ( E1 )
, An , B1 , B2 , , Bn
F ( E2 )
2
,
( E1 E2 ) A1 , A2 ,
, An
创建索引的一般性准则
• 一个表若建有大量索引会影响更新语句的性能，因为在表中更改数据时， 所有索引都须进行适当的调整； • 避免对经常更新的表建立过多的索引，并且索引列要尽可能少； • 使用多个索引可以提高更新少而数据量大的查询性能，因为查询优化器有 更多的索引可供选择，从而可以确定最快的访问方法； • 对小表进行索引可能不会产生优化效果。小表的索引可能从来不用，但仍 必须在表中的数据更改时进行维护； • 为经常用于查询中的谓词和联接条件的所在列创建非聚集索引； • 涵盖索引可以提高查询性能。例如，对某一表（其中对列a、列b和列c创建 了组合索引）的列a和列b查询，则仅从该索引本身就可以检索到数据，因 此，减少了总体磁盘I/O； • 对于聚集索引，保持较短的索引键长度； • 如果索引包含多个列，则应考虑列的顺序。WHERE子句中参与比较运算或 者参与连接的列应该放在最前面，其他列从最不重复的列到最重复的列；