数据库系统原理与设计实验教程 第4版 课件 第8章 数据库查询执行计划

第1页第8章数据库查询执行计划数据库系统原理实验教程第4版第2页8.1相关知识8.1.1SQL优化器的优化过程8.1.2执行计划

8.1.3执行计划展示方式8.1.4SQLServer所使用的逻辑和物理运算符8.2实验十六执行计划8.2.1实验目的与要求8.2.2实验案例8.2.3实验内容目录8.1相关知识SQLServer使用基于代价的查询优化器自动优化查询操作。

对于优化器，输入是一条查询语句，输出是一个执行策略。该执行策略是执行这个查询所需要的一系列步骤，数据库的执行代价体现在这个优化算法上。

查询优化经过了3个阶段：查询分析、索引选择、合并选择。第3页8.1.1SQL优化器的优化过程首先对查询的每条子句进行语法分析判定是否能够使用该子句限制查询必须扫描的数据量，该子句可用作索引中的一个查找参数其次在对查询进行语法分析，找出全部查找参数后，查询优化器判定在查找参数上是否存在索引，并决定索引的有效性最后优化器得出一个查询执行计划查询优化器估算执行该计划的开销8.1.2执行计划在SSMS中，可显示执行计划。从查询菜单选择“显示执行计划”，则执行计划会在结果面板窗口以图形形式显示出来。执行计划中的逻辑运算符和物理运算符描述了一个查询或更新是如何被执行的。物理运算符说明了用于处理一条语句例如，扫描一个聚集索引，所使用的是物理实现算法。执行一条查询或更新语句的每一步都包括一个物理运算。逻辑运算符说明了用于处理一条语句例如，执行一个总计，所使用的是关系代数操作。并非每条查询或更新所需要的所有步骤都包含逻辑运算符。8.1.2执行计划SQLServer查询分析器的特点(1)物理运算符用于运算，例如哈希连接或嵌套循环。(2)逻辑运算符匹配物理运算符。如果逻辑运算符与物理运算符不同，它将被列在物理运算符后面。(3)估算行计数运算，输出行的数目。(4)估算行大小，即估算每行输出的大小。(5)估算I/O开销，即估算全部I/O活动的开销。(6)估算CPU开销，即估算全部CPU活动的开销。(7)估算在查询期间执行行的数目，运算执行的次数。(8)估算查询优化器执行某一查询操作的开销，包括该操作的开销在整个查询开销中所占的百分比。(9)估算查询优化器执行某一查询操作以及同一子树中先前操作的全部开销。(10)参数查询使用的判定和参数。8.1.3执行计划展示方式查看执行计划，在SQLServer2005版本以上，系统提供了三种展示方式：图像方式、文本方式和XML方式。1．图像方式[例8.1]：查询单价高于3000元的商品编号、商品名称、订货数量和订货单价。

SELECT

a.productNo,productName,quantity,priceFROMProducta,

OrderDetailbWHERE

a.productNo=b.productNo

ANDprice>3000ORDER

BY

productName

查询所产生的预估执行计划，将其分成不同的运算符进行组合，从右侧的聚集索引扫描（indexscan）到最左侧的结果输出（select）。注意：图中箭头的方向指向的是数据的流向，箭头线的粗细表示了数据量的大小

在图形化执行计划中，每一个不同的运算符都有自身的属性值，把鼠标移至运算符图标上查看2．文本方式文本方式采用竖线（|）标示子运算符和当前运算的子父关系，数据流方向都是从子运算符流向父运算符的

在SSMS中需要用语句开启，开启的方式有两种：只开启|关闭执行计划，不包括详细的评估值。执行命令：SETSHOWPLAN_TEXTON|OFF开启|关闭所有的执行计划明细，包括各个属性的评估值。执行命令：SETSHOWPLAN_ALLON|OFF[例8.2]：查找订购总金额在5000元以上的客户编号、客户名称和订购总金额。SELECT

a.customerNo

,customerName,sum(orderSum)

订购总金额FROMCustomera,OrderMasterbWHERE

a.customerNo=b.customerNoGROUP

BY

a.customerNo

,customerNameHAVING

sum(orderSum)>=5000执行过程是从最里面的运算符开始执行，数据流方向也是依次从子运算符流向父运算符3．XML方式XML方式结合文本和图形方式的优点，利用SQLServer2019中的XML的数据类型和内置XQuery功能进行查询。此方式尤其对与超大型的查询计划查看非常的方便。执行命令：SETSTATISTICSXMLON|OFF开启|关闭[例8.3]：查询单价高于3000元的商品编号、商品名称、订货数量和订货单价SELECT

a.productNo,productName,quantity,priceFROMProducta,

OrderDetailbWHERE

a.productNo=b.productNo

ANDprice>3000ORDER

BY

productName单击输出XML文件点击XML链接地址，再右击鼠标，出现XML方式展现了非常详细的查询计划信息，简单分析如下：

StmtSimple：描述了T-SQL的执行文本，详细分析了该语句的类型，以及各个属性的评估值。

StatementSetOptions：描述该语句的各种属性值的Set值

QueryPlan：是详细的执行计划，包括执行计划的并行的线程数、编译时间、内存占有量等

OutputList：输出参数列表，在中间部分是具体的不同的执行运算符的信息了，且包括详细的预估值等Sort：排序属性8.1.4SQLServer所使用的逻辑和物理运算符教材中的表8-2列出了SQLServer所使用的部分逻辑和物理运算符。在SQL查询分析器中从右到左、从上到下读取图形执行计划输出显示所分析的批处理内的每个查询包括每个查询的成本占批处理总成本的百分比树结构内的每个节点都用一个图标表示，指定用于执行部分查询或语句的逻辑运算符和物理运算符。每个节点都与一个父节点相关。具有相同父节点的节点都绘制在相同的列内。用箭头将每个节点连接到其父节点。[例8.4]统计客户数量SELECTcount(*)FROMCustomer此查询生成4个运算符，每一个运算符会有两个属性影响其执行的效率1）内存消耗

所有运算符都需要一定的内存来完成。当一条T-SQL语句编译生成查询计划后，SQLServer会选择最优的查询计划去固定内存，目的是为了再次执行时不需重新申请内存，加快执行速度。但有些运算符需额外的内存来存储行数据，所需内存量和处理的数据行数成正比。出现如下情况则会导致内存不能申请到，影响执行性能：(1)如果服务器上正在执行其它的类似的内存消耗巨大的查询，导致系统内存剩余不足的时候，当前的查询就得延迟进行，直接影响性能。(2)当并发量过大的的情况下，多个查询竞争有限的内存资源，服务器会适当的控制并发和减少吞吐量来维护性能，这样也会影响性能(3)如果申请到的可用内存很少，SQLServer通常使用Tempdb临时库进行操作，这个过程会很慢，如耗尽Tempdb磁盘空间则以失败结束。比较消耗内存的运算符主要有分类、哈希连接以及哈希聚合等连接操作

2）阻断运算和非阻断运算阻断和非阻断的区别：运算符是否在输入数据时能直接输出结果数据。(1)当一个运算符在接收输入的同时生成输出行，这种运算符是非阻断式的。如常用的

SelectTop...操作。(2)当一个运算符所产生的输出结果需要等待所有的数据输入的时，这个操作运算就是阻断运算的。如Count(*)操作需等待所有的数据行输入才能计算出。提示：并不是所有的阻断式操作都要消耗内存，如Count(*)就为阻断式，但它不消耗内存。我们要尽量使用非阻断式操作来代替阻断式操作，这样才能更好的提高相应时间，如使用EXISTS子查询来判断，比用HAVINGcount(*)>0的速度要理想的多。逻辑运算符和物理运算符详见教材案例详见教材8.2实验十六执行计划 8.2.1实验目的与要求(1)掌握SQL查询语句的执行过程。(2)熟练使用“显示执行计划”功能，查看并分析SQL语句的执行过程。(3)能够运用执行计划的结果对SQL语句进行优化。8.2.2实验案例1．实验环境(1)启动SQLServerManagementStudio。(2)选择要操作的数据库，如订单数据库OrderDB。(3)在查询窗口输入一条SQL语句。(4)单击“显示估计的执行计划”按钮单击“显示估计的执行计划”按钮8.2.2实验案例2．实验案例[例8.7]查看执行计划和所花费的成本。SELECTproductNameROMOrderDetailb,ProductaWHEREb.productNo=a.productNo将鼠标放在某个物理或逻辑运算符中，如右图所示。“消息”框显示：8.2实验十六执行计划 8.2.2实验案例2．实验案例[例8.8]查找订购了“华为手环B3”的商品的客户编号、客户名称、订单编号、订货数量和订货金额，并按客户编号排序输出。SELECTa.customerNo,customerName,b.orderNo,quantity,quantity*pricetotalFROMCustomera,OrderMasterb,OrderDetailc,ProductdWHEREa.customerNo=b.customerNoANDb.orderNo=c.orderNoANDductNo=ductNoANDproductName='华为手环B3'ORDERBYa.customerNo注意：你运行的结果可能与本教材的结果不一致，其原因与本机的软硬件环境有关。8.2实验十六执行计划 8.2.2实验案例2．实验案例[例8.9]查询销售金额最大的客户名称和总货款。SELECTa.customerNo,customerName,sum(orderSum)FROMcustomera,OrderMasterbWHEREa.customerNo=b.customerNoGROUPBYa.customerNo,customerNameHAVINGsum(orderSum)=(SELECTmax(sumOrder)FROM(SELECTcustomerNo,sum(orderSum)ASsumOrderFROMOrderMasterGROUPBYcustomerNo)c