运用SQL执行计划助力审计数据分析，本文主要内容关键词为：助力论文,计划论文,数据论文,SQL论文，此文献不代表本站观点，内容供学术参考，文章仅供参考阅读下载。
审计数据分析人员习惯运用SQL查询的方式来进行数据分析，从而找出可能存在的审计疑点或审计线索。由于SQL执行过程的透明性，审计人员很难掌控SQL语句执行的快慢，特别是对于一些复杂的关联查询，其执行时间更是难以预估，这就容易导致审计数据分析时间难以控制，制约审计任务的顺利开展。本文通过执行计划工具来分析影响SQL语句执行速度的典型操作，并提出相应的优化方法，从而协助审计数据分析人员写出高效优质的SQL查询语句，提高审计数据的分析效率。 什么是执行计划 执行计划可视为完成一项审计数据分析任务的具体步骤，它由数据库的查询优化器生成，并由数据库管理系统自动调用。当审计人员编写完一条SQL语句，并将其由客户端发送到数据库所在的服务器时，数据库的查询优化器负责对该SQL语句的语法、语义及权限进行检查，从而验证语句的书写是否规范，所表达的语义是否正确，所引用的属性是否合法。当上述信息准确无误时，查询优化器将锁定分析过程中所需的对象，并基于元数据信息、硬件信息和数据库配置信息等计算出完成该语句所需的基本步骤，即所谓的执行计划。一个完整的执行计划通常由一组相关联的操作组成，如数据的获取、查询条件的使用、中间结果的生成、最终结果的返回等，这些操作彼此之间相互配合，共同完成用户的查询请求。对于每条SQL语句，查询优化器通常会生成一组执行计划，并为每个执行计划计算一个执行成本，执行成本越低说明执行该SQL语句所需的时间越短，最终查询优化器会运用成本最低的执行计划来执行该SQL语句。 如何查看SQL执行计划 目前，主流数据库都提供多种方式来查看执行计划。以SQL Server为例，其为用户提供了命令行和图形化两种查看执行计划的途径。其中，命令行方式利用Transact-SQL的SET语句，并配合SHOWPLAN_ALL选项将执行计划以记录集的形式展示，每条记录代表执行该语句所要完成的操作。SHOWPLAN_ALL选项有“开启”和“关闭”两种状态，当该选项处于开启状态时，其后的SQL语句将不被执行，查询处理器仅仅返回与该语句相关的执行计划和所需的资源信息；当SHOWPLAN_ALL处于关闭状态时，SQL Server将执行语句，而并不生成执行计划。 图形化方式则由SQL Server自带的图形化工具生成执行计划，每个操作由比较有代表性的图标进行表示。对于任意一条SQL语句，用户可通过点击SQL Server Management Studio工具栏上的“显示估计的执行计划”图标，生成针对该语句的执行计划。 SQL Server以树形结构展示执行计划，树中每个节点带有两类信息，分别为SQL执行所需的操作以及该操作在整个执行计划中的开销百分比，所有操作的总开销为100%。节点间由从右向左的箭头连接，该箭头既表示执行计划是从右往左执行，也表示节点间的数据按从右到左的方向流动，箭头宽度表示节点间的数据传输量。 执行计划中的操作可以分为三类：数据访问操作、聚合操作和连接操作。数据访问操作是指由直接访问数据表或者通过访问索引而引发的操作，该类操作是执行计划中的最基本操作，负责收集与用户查询相关的数据。常见的数据访问操作包括表扫描、索引扫描、索引查找等。表扫描需要遍历整个表来查找所有匹配的记录行，效率最差；索引扫描是先根据索引在数据表中过滤出中间结果集，再从中查找所有匹配的记录行，显然索引扫描要比表扫描效率高；而索引查找是根据索引来定位记录的存放位置，然后取得记录，因此相比前两者，索引查找具有更快的速度。聚合操作主要指由聚合函数（如SUM、COUNT、MIN、MAX等函数）引发的操作，该操作用于对中间结果进行统计分析，典型的聚合操作包括流聚合、计算标量、散列聚合、排序等。连接操作是指由多表连接而引发的操作，该操作多用于联合查询，即查询所涉及的属性涉及多个数据表，典型的连接操作包括循环嵌套连接、合并连接、哈希连接等。 如何利用执行计划改善数据分析效率 由于执行计划体现了SQL执行的基本步骤，因而可通过对执行计划的分析来查找影响SQL执行速度的关键因素，从而做到有针对性地分析和优化。通常在查看执行计划时，我们应重点关注开销百分比较大的操作，这些操作在执行阶段也将耗费较多时间，因此可将其视为分析和优化的重点。下面给出一些容易成为SQL语句性能瓶颈的典型操作以及相应的优化方法。 1.表扫描。由于表扫描是以逐行检索的方式查找数据，因此表的数据量越大，扫描整个表所花费的时间越长，SQL的执行速度越慢。为了尽量减少表扫描操作的产生，可考虑从以下两方面人手进行优化：在所查询的表上建立索引，减少查询所涉及的属性。在查询的表上建立索引主要是利用查询优化器自身特点，即在所查询的表上包含索引且查询条件含有索引属性时，查询优化器更倾向于使用索引查找来提高数据检索效率；而减少查询所涉及的属性则是为了避免由于检索的属性过多而迫使优化器给出表扫描优于索引扫描的结论。 2.索引查找和表扫描。当查询结果仅仅是由索引属性构成时，优化器只需通过索引找出满足条件的数据即可，而无须对数据进行扫描。然而当某一个或某几个非索引属性被引用时，优化器往往需要在查找索引的同时，还需要对表中的数据进行扫描。当满足条件的数据项较多时，大量的数据扫描显然会造成查询效率的降低。为了尽量减少数据扫描，当发现执行计划同时包含索引查找和表扫描时，可考虑对已创建的索引进行调整，并将常用的属性包含在索引中，这样在查询时就可以通过索引查找来减少需要读取的数据量。 3.索引扫描。当创建的索引为复合索引且查询条件没有引用索引的首属性时，优化器通常会选择用索引扫描来代替索引查找。索引扫描与表扫描方式类似，区别在于，索引扫描的对象是所有索引叶子页，而表扫描的对象是所有数据页。由于数据量较大时，所创建的索引也会较大，索引包含的叶子页也会比较多，因此遍历所需的时间也会越长。当发现执行计划出现索引扫描时，我们应检查所编写的语句是否正确使用了索引，并且应保证索引的首字段出现在查询条件当中，从而使优化器选择索引查找的方式检索数据。 4.合并连接。当查询语句涉及多个表的属性时，表间的连接操作将不可避免。常用的连接方式主要有以下三种：循环嵌套连接、合并连接和哈希连接。其中合并连接较为耗时，如果用于连接的属性不存在索引，那么连接过程中优化器将需要对属性进行排序，并在排序后的属性上进行连接。排序操作不仅耗费大量的CPU和内存空间，而且在排序内存不足时，还将导致排序结果的溢出。为了避免该现象的产生，当发现执行计划的连接操作耗时过多时，可选择为连接的属性建立索引，从而避免连接过程中排序操作的产生。