SQL 性能第 2 篇：查询分析和访问路径制定

在 SQL 性能概述的第一部分中，我们研究了关系优化及其影响因素。在今天的文章中，我们将注意力转向查询分析以及 SQL 转换为可执行代码的方式。希望对大家在 SQL 性能优化方面有所帮助。

在 SQL 性能概述的第一部分中，我们研究了关系优化及其影响因素。在今天的文章中，我们将注意力转向查询分析以及 SQL 转换为可执行代码的方式。

从上层看，优化过程包括四个步骤：

1.接收并验证 SQL 语句。

2.分析环境，优化满足 SQL 语句的方法。

3.创建机器可读的指令来执行优化的 SQL。

4.执行这些指令或将它们存储起来以备将来执行。

需要做的第一件事是验证 SQL 是否写对了。这并不意味着它会做你希望它做的事情，只是它符合所需的语法。将对 SQL 进行分析和检查。如果遇到任何错误，进程将停止，你必须修改 SQL，直到它正确为止。在验证 SQL 语法之后，下一步是检查语义，例如数据类型、引用约束、检查约束、视图和触发器。

这个过程的第二步是最有趣的。优化器如何决定如何执行可以按其方式发送的大量 SQL 语句？此查询分析步骤扫描 SQL 以确定其总体复杂性。SQL 语句的表达式是决定优化器选择的访问路径的一个重要因素。查询的复杂性、谓词的数量和类型、函数的存在以及排序子句的存在都将进入优化器计算的估计成本中。

SQL 语句越复杂，查询分析就必须做越多的工作来理解 SQL 语句。在查询分析期间，优化器分析 SQL 语句和数据库系统的各个方面，例如

• 需要哪些数据库中的哪些表

• 是否需要将任何视图分解为基础表

• 是否需要表连接或子选择

• 是否需要 UNION、EXCEPT 或 INTERSECT

• 可以使用哪些索引(如果有的话)

• 必须满足多少谓词(WHERE 子句)

• 必须执行哪些函数

• SQL 是否使用 OR 或 AND

• DBMS 如何处理 SQL 语句的每个组件

• 为 SQL 语句中的表使用的数据缓存分配了多少内存

• 如果查询需要排序，有多少内存可用于排序

换句话说，查询分析将 SQL 语句分解为必须执行的离散任务，以返回查询结果。

现代关系优化器是基于成本的，这意味着优化过程总是试图为每个查询制定一个降低总体成本的访问路径。为了实现这一点，优化器应用查询成本公式来评估和权衡每个潜在访问路径的多个因素：这些因素包括 CPU 成本、I/O 操作、系统编目中的统计信息以及实际的 SQL 语句代码。

优化器可以重写查询，将其转换为等效的、但更容易编译和优化的版本。谓词下推和转换可能在此时发生。然后优化 SQL。将审查和分析多条访问路径，以选择成本最低的选项。最后一步是创建实际的可执行代码。

1 访问路径

关系优化器有许多创建 SQL 访问路径的选项。在较高的层次上，有访问单个表中的数据的方法，也有组合两个表中的数据的方法。可以将这些方法组合成一系列访问方法，为 SQL 语句创建总体访问路径。

对于单表访问，可以使用扫描或索引检索数据。在优化器确定每个谓词可用的索引之后，它将决定是使用单个索引、多个索引还是根本不使用索引。

大家很容易说索引访问将优于扫描访问，但事实并非总是如此。优化器必须评估必须访问的数据量以及查询的性质。例如，如果你正在创建一个包含表中每一行的报告，那么使用索引可能比使用扫描读取所有数据要慢。

表扫描是最简单的数据访问形式。表扫描是通过读取表的每一行来执行的。根据 DBMS 的不同，可能存在另一种扫描类型，称为表空间扫描。表空间扫描读取表空间中的每个页面，表空间可能包含多个表。显然，表空间扫描将比表扫描运行得慢，因为可能会产生额外的 I/O 读取不适用的数据。

另一种扫描形式是分区扫描。如果 DBMS 能够确定要访问的数据存在于多分区表（或表空间）的某些分区中，那么它可以将扫描到的数据限制到适当的分区。分区扫描应该优于表扫描或表空间扫描，因为所需的 I/O 数量减少了。

通常，优化器会选择扫描数据，原因如下之一：

• 使用索引无法满足查询，可能是因为没有索引可用、谓词与索引不匹配，或者谓词妨碍索引的使用。

• 表中的行符合条件的百分比很高。在这种情况下，使用索引可能效率较低，因为无论如何都需要读取大多数数据行。

• 具有匹配谓词的索引具有较低的集群比率，并且仅对少量数据有效。

• 表太小，使用索引实际上是有害的。对于小表，向表访问添加索引访问可能会导致额外的 I/O，而不是更少的 I/O。

为了帮助扫描的性能，优化器可以调用数据预取。数据预取会导致 DBMS 在请求数据页之前，按顺序将数据页读入数据缓存。从本质上说，数据预取是一种读前机制——当数据扫描开始请求数据时，它已经存在于内存中。Prefetch 对于扫描特别有用，但是对于任何类型的顺序数据访问都是实用的。你应该了解特定 DBMS 如何以及为什么预取数据。

2 索引存取

大多数的访问应该使用索引，这使我们可以选择扫描或索引访问。优化器必须首先发现是否存在索引。在编写 SQL 来访问列之前，不必定义索引—你可以查询数据库所知道的任何表的任何列。

此外，必须在 SQL 语句中的可索引谓词中引用至少一个索引列。DBMS 不能为每个 WHERE 子句使用索引。您必须了解谓词可以使用哪些类型的索引来确保为数据库应用程序中的查询创建适当的索引。每个数据库管理系统都有一个不同的列表，其中列出了什么是可索引的，什么是不可索引的。此外，可索引的内容往往会随着每个 DBMS 的版本而变化。

优化器可以选择以许多不同的方式使用索引。第一个也是最简单的索引访问类型是直接索引查找。为了使 DBMS 能够执行直接索引查找，必须为索引中的每一列提供值。为了执行直接索引查找，DBMS 将谓词中请求的值与索引根页中存储的值进行比较。基于这种比较，DBMS 将把索引遍历到下一个页面集。如果存在中间的非叶页，则读取适当的非叶页，并比较该值以确定要访问哪个叶页。阅读适当的页；索引页包含指向符合条件的行实际数据的指针。基于页索引条目中的指针，DBMS 读取适当的表数据页。

但是，假设 SQL 语句中没有提供索引的所有列。不能选择直接索引查找，因为 DBMS 不能匹配完整的索引键。相反，可以选择索引扫描。当一个索引扫描被调用时，索引的页被依次读取。

索引扫描有两种基本类型：匹配索引扫描和不匹配索引扫描。匹配的索引扫描有时称为绝对定位。匹配的索引扫描从索引的根页开始，以与直接索引查找相同的方式向下工作到叶页。但是，由于索引的完整键不可用，DBMS 必须扫描索引的页，查找可用的值，直到检索到所有匹配的值。

要使用匹配的索引扫描，必须在索引键中指定高阶列;即索引 DDL 中指定的第一列。高阶列为 DBMS 从根页面到适当的叶页面遍历索引结构提供了起点。

请考虑在查询中不指定高阶列的后果。DBMS 可以部署不匹配的索引扫描，有时称为相对定位。当由于索引键中的第一列未指定而无法确定起始点时，DBMS 不能使用索引树结构。但是，它可以扫描索引页。不匹配的索引扫描从索引中的第一个页开始，然后应用可用的谓词顺序扫描后续的页。

不匹配的索引扫描可能比表或表空间扫描更有效，特别是如果必须访问的数据页是按集群顺序进行的。此外，请记住索引页(或块)包含的条目比表页多，因为索引“行”比表行短，从而使索引页 I/O 比扫描表页更有效。

总结

在本篇中，我们从较高的层次上研究了查询分析和访问路径公式，了解了查询分析的组件和单表访问方法。但还有更多的东西需要学习。在下一期文章中，我们将研究关系优化可以使用的多表访问方法。希望对大家在 SQL 性能优化方面有所帮助。

本文转载自公众号 360 云计算（ID：hulktalk）。

原文链接：

https://mp.weixin.qq.com/s/1PYqRuk85BsJbqdzBgIBgg