如何有效的使用索引和表结构来处理SQL语句

夏末蝉未鸣01

已于 2024-09-04 17:20:18 修改

阅读量779

点赞数 28

分类专栏：数据库文章标签：数据库

于 2024-06-26 11:16:18 首次发布

本文链接：https://blog.csdn.net/m0_62410482/article/details/139441204

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SQL处理的一些概念

谓词

优化器及访问路径

索引片、匹配索引扫描、匹配列、索引过滤和过滤列

使用统计信息和所需FETCH调用的次数来指导优化器的行为

帮助优化器（统计信息）

帮助优化器（FETCH调用的次数）

过滤因子、选择器和基数的概念及其对索引设计的影响

SQL处理的一些概念

早期，大部分SQL语句的处理过程依赖于所使用的不同的关系型DBMS。它们之间有许多相似点，也有许多差别。

谓词

WHERE子句由一个或多个谓词（搜索参数）组成。

谓词表达式是索引设计的主要入手点。如果一个索引能够满足SELECT查询语句的所有谓词表达式，那么优化器就很有可能建立起一个高效的访问路径。

优化器及访问路径

关系型数据库的一大优势就是，用户无须关心数据的访问方式。其访问路径由DBMS的一个组件，即优化器来决定的。虽然不同的关系型系统的优化器各不相同，但它们都是在系统收集的统计信息的基础上，尽可能以最高效的方式访问数据。优化器是SQL处理过程的核心，也是本书讨论的重点。

在SQL语句能够被真正执行之前，优化器必须首先确定如何访问数据。这包括：应该使用哪一个索引，索引的访问方式如何，是否要使用辅助式随机读，等等。

索引片、匹配索引扫描、匹配列、索引过滤和过滤列

索引片及匹配列

如上图，索引的一个窄的片段将会被顺序扫描，其上索引行的值在100至110之间，相应的表行将通过同步读从表中读取，除非该页已经在缓冲池中。所以访问路径的成本很大程度上取决于索引片的厚度，即谓词表达式确定的值域范围。索引片越厚，需要顺序扫描的索引页就越多，需要处理的索引记录就越多，而最大开销还是来自于增加的对表的同步读操作，每次表页读取需要10ms。

并不是所有的关系数据库系统都使用索引片这种描述方式，不同的产品有自己的术语。但本书认为索引片是独立于数据库产品的更具描述性的术语。另一种使用较为广泛的描述索引片的方法是定义匹配列的数量。在上面的例子中，仅仅只有一个匹配的列，其值域范围从100到110。这个匹配列实际上定义了索引片的大小。如果WHERE子句中有第二个匹配列，而这个列也在索引上，从而使得这两个列能够一同定义一个更窄的索引片，那么我们将有两个匹配谓词。这样不仅显著减少了索引的处理量，更减少了对表进行同步读的次数。

索引过滤及过滤列

有时候，列可能存在于WHERE子句中，也存在于索引中，但这个列却不能参与索引片的定义（这其中的原因非常复杂，本书将持续讨论这个问题）。不过这些列仍然能够减少回表同步读的次数，仍扮演着重要的角色。我们称这些列为过滤列，有些关系型数据库系统也确实是这样命名的，因为这些列也确实起到了过滤作用。通过这些索引的过滤列能够避免对表行的访问。

1、在WHERE子句中，该列是否至少拥有一个足够简单的谓词与之对应：

（1）如果有，那么这个列就是匹配列；

（2）如果没有，那么剩余的索引列都是非匹配列；

（3）如果该谓词是一个范围谓词，那么剩余的索引列都是非匹配列。

2、对于最后一个匹配列之后的索引列，如果拥有一个足够简单的谓词与之对应，那么该页为过滤页。

优化器何时确定访问路径

现阶段，我们必须理解的优化器相关的问题就是图3.3所描述的内容。很明显在每次SQL语句执行时都进行一次访问路径（数据访问的方式）选择要比仅做一次消耗更多的资源，所以在应用程序开发的过程中，基于成本的优化器进行访问路径选择的成本不容忽视。

不太明显但很重要的一点，在每次SQL语句执行时进行访问路径选择，这种方式可能会为优化器提供一个选择更好的访问路径的机会，因为这种情况下我们使用的是确定的值而不是绑定的变量。

监控优化器

当发现一个慢SQL的时候，通常首先被怀疑的对象是优化器，可能是优化器选择了错误的访问路径。关系型DBMS通常都提供一个工具，用于解释优化器决定使用某个访问路径的原因，这一工具被称为EXPLAIN、SHOW PLAN或者EXPLAIN PLAN。

使用统计信息和所需FETCH调用的次数来指导优化器的行为

帮助优化器（统计信息）

如果优化器进行成本估算所使用的统计信息不完整，那么优化器就很容易做出错误的决定，可能是手机统计信息时所使用的选项太有限，也可能是统计信息已经过时。这些统计信息通常是在需要时由专门的程序收集的，比如DB2 for z/OS中的RUNSTATS程序。

正常情况下，默认收集的统计信息包括了基础的信息，如每张表的记录数和表页数、叶子页数、每个索引的聚簇率、某些列或者列组的不同值个数（被称为基数），以及某些列的最大值、最小值（或者第二大值、第二小值）等。其他可选的统计信息的选项能够提供更多的关于列的值分布情况，比如最常见的N个值连同其对应的表行数量。许多数据库产品（比如Oracle、SQL Server和DB2 for LUW）还支持以直方图的形式采集列值分布（即在用户所定义的值域范围内表行所占的比例N%）。

帮助优化器（FETCH调用的次数）

基于成本的优化器在评估不同访问路径的成本时会FETCH所有满足条件的记录——除非有特别说明。如果我们不需要所有的记录集合，那么可以设置仅FETCH前n行。

过滤因子、选择器和基数的概念及其对索引设计的影响

过滤因子

过滤因子描述了谓词的选择性，即表中满足谓词条件的记录行数所占的比例，它主要依赖于列值的分布情况。

平均过滤因子：如CITY列的平均过滤因子为1/不同CITY的个数；

特定值的过滤因子：如CITY='HELSINKI'的特定值过滤因子=CITY为'HELSINKI'的行数/总行数；

不过这两者之间的不同点对索引的设计和访问路径的选择至关重要。

在评估一个索引是否合适时，最差情况下的过滤因子比平均过滤因子更重要，因为最差情况与最差输入相关，即在该输入条件下，基于特定索引的查询将消耗最长的时间。

组合谓词的过滤因子

如果组合谓词的列之间飞相关，那么组合谓词的过滤因子为单个谓词的过滤因子的乘积。

在设计索引结构的时候，需要将组合谓词看做一个整体来评估过滤因子，而不能仅仅基于零相关进行评估。幸运的是，最坏情况下的过滤因子评估通常比较简单。

优化器在评估可选访问路径的成本时，必须先评估过滤因子，过去的算法远比现在简单，那时候错误的组合谓词的过滤因子评估是导致不合适的访问路径选择的最主要原因之一。现在许多的优化器已经有了可选项，用于计算或者采样索引列组合的基数（不同值的数量）。

过滤因子对于索引设计的影响

需要扫描的索引片的大小对访问路径的性能至关重要。在当前的硬件条件下，索引片大小的最重要的量度就是需要扫描的索引记录数，即匹配组合谓词的过滤因子与总行数的乘积。按照定义，匹配谓词能够参与定义索引片的大小。通常索引行的记录数等于表的记录数，但也有例外。

结果物化及其影响

物化结果集

物化结果集的意思就是执行必要的数据库访问来构建结果集。在最好的情况下，这只需要简单的从数据库缓冲池向应用程序返回一条记录。在最坏的情况下，DBMS需要发起大量的磁盘读取。

当一个SELECT语句只查询出一条记录时，优化器必须在SELECT请求被执行时就物化结果记录。而当结果集可能有多条记录而需要使用游标时，有两种可供选择的方式。

（1）DBMS在OPEN CURSOR时物化整个结果集（或者至少在第一次FETCH的时候）；

（2）每次FETCH物化一条记录。

游标回顾

一次FETCH调用按照DECLARE CURSOR语句的定义向应用程序返回结果集的一条记录。如果DECLARE CURSOR包含了绑定变量，那么在执行时，应用程序会在OPEN CURSOR调用之前，将实际的值传入绑定变量。如果应用程序期望使用同一个游标获得多个结果集，那么首先需要执行一次CLOSE CURSOR调用，然后向绑定变量传入新的值，再重新执行一次OPEN CURSOR调用来打开游标。

在一般的事务隔离级别下，CLOSE CURSOR调用会释放最后一次FETCH操作所持有的锁。如果应用程序没有调用CLOSE CURSOR或者使用了更高的事务隔离级别，那么数据库系统会在提交的时刻释放持有的锁。

一些查询工具可以根据SELECT语句生成DECLARE CURSOR、OPEN CURSOR、一个FETCH循环和CLOSE CURSOR共4个步骤。

为了避免不必要的工作，数据库管理系统总是尽可能晚的进行结果集物化。如果DBMS先物化结果集，那么在调用FETCH的时候，系统将从临时表中检索记录，而在数据库更新的时候，并不会更新临时表中存放的结果集。

DECLARE LASTINV CURSOR FOR
SELECT INO, IDATE, IEUR
FROM INVOICE
WHERE CNO=:CNO
ORDER BY INO DESC
WE WANT 1 ROW PLEASE
OPEN CURSOR LASTINV
    FETCH CURSOR LASTINV
CLOSE CURSOR LASTINV

这条查询语句将总是返回一条记录。应用程序只需要执行一次FETCH调用即可，因此，应用程序所需要的是获取一条记录的最有效的访问路径（不同DBMS的具体语法各有不同）。

尽管只是请求一条记录，但DBMS究竟读取了哪些数据？

1、是读取了CURSOR所定义的整个结果集？

2、还是只读取了满足条件的那一条记录？

从性能角度，这可能是一个非常重要的问题。