调优要点

避免不必要的数据类型转换

需要注意的是，尽量避免潜在的数据类型转换。在生产中遇到的有一半以上的性能问题均出自于数据类型的隐式转换。

如将数值型数据作为条件与字符型数据比较，数据库会自动将字符型用 to_number() 函数进行转换，从而导致全表扫描。

避免对谓词查询列的操作

任何对谓词查询列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数（支持函数索引的除外）。

由于 where 子句中对列的任何操作结果都是在 SQL 运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引（即使该列上有索引）；如果这些结果在查询编译时就能得到，那么就可以被 SQL 优化器优化，使用索引，避免表扫描，提升速度。

使用基于函数的索引

任何对谓词查询列的操作都可能导致全表扫描，包含计算表达式、数据库函数等。当查询条件需要对列进行函数计算，可以使用数据库的函数索引特性优化查询效率。

多列索引前缀列优先原则

B 树索引（或 GiST 索引）可以覆盖多个列。当第一列中的每个值在第二列上有许多值时，通常会创建多列索引。例如，您可能希望创建一个索引，涵盖了 rental_date 和 tape_id 两列。数据库可以使用多列索引进行查询或排序。创建多列索引时，列的命名顺序很重要。当您查询以关键列为前缀列（或按前缀列排序）时，可以使用多列索引。

增加查询的范围限制

某些场景增加查询的范围限制，避免全范围的搜索，特别适用于日期时间类型的数据。 同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在 Where 子句中加上 “AND 列名 < MAX（最大值）”。

尽量避免使用 union

我们都知道 sql 语句使用 union 关键字后，可以获取排重后的数据。而如果使用 union all 关键字，可以获取所有数据，包含重复的数据。但是排重的过程需要遍历、排序和比较，它更耗时，更消耗 cpu 资源。所以如果能用 union all 的时候，尽量不用 union。

In-list、or 使用规范

在 AntDB 数据库中，SQL 优化器能够较好的处理查询条件中的 in、or 子句：

In：在 in-list 数据集合小于 10000 的时候，处理效率可以接受；当超过 10000，使用 in 子句就不如使用临时表来存放集合数据执行速度来的快；

Or：在使用 or 条件的时候，一定要注意将 or 查询条件使用括号括起来。

Bitmap Scan- 位图索引扫描

虽然索引扫描在高相关性下有效，但当相关性下降到扫描模式比顺序模式更随机且页面抓取数增加时，索引扫描就会不足。有一个解决方案是预先收集所有元组 ID，按页码排序，然后使用它们扫描表。这就是位图扫描的工作原理。它可用于任何具有位图扫描属性的访问方法。

AntDB 能够同时使用多个索引。特别是使用 or 语句，如下示例：

合理使用 in、exists

合理使用 in、exists 规则如下：

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in，反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用 exists。

Like 查询匹配

因为 like 参数使用的非常频繁，因此如果能够对 like 子句使用索引，将很高的提高查询的效率。

在其他数据库中使用 like 前端匹配查询可以有效利用该字段的索引，但是两端匹配的时候则不行；AntDB 数据库中我们可以使用 gin 类型所以提升 like 查询的效率。

只获取所要的结果集列

我们都知道索引能够提升查询速度。有时候，索引不仅仅能够用于定位表中的数据。某些查询可能只需要访问索引的数据，就能够获取所需要的结果，而不需要再次访问表中的数据。这种访问数据的方法叫做 Index-Only 扫描。所以我们的查询需要遵循最小获取结果集列的原则，非必要的列不要出现在语句中。

Index Only Scan- 仅索引扫描（索引覆盖扫描）

当索引包含处理查询所需的所有数据时，我们将其称为查询的覆盖索引。通过使用覆盖索引，访问方法可以直接从索引中检索数据，而无需单表扫描。这称为仅索引扫描，可以由任何具有可返回属性的访问方法使用。

多表关联的 SQL 语句优化方法

多表关联的 SQL 语句关联执行计划包含如下几种：

• 嵌套循环-nested loop
• 散列连接-hash join
• 合并连接-merge join

某些关系型数据库版本甚至不支持 hash join。

	Nested Loop	Hash Join	Merge Join
算法	外部驱动表每扫描一行，内部被驱动表扫描匹配的记录	内部表构建 hash 值链表，扫描外部表并根据 hash 值匹配记录	对所有对象排序并合并行
有帮助的索引	关联的内部表查询列有索引	无	内外部表关联列均有索引
好的使用策略	外部表（驱动表）较小	Hash table 大小和 work_mem 参数大小匹配	所有表都较大

嵌套循环 -nested loop

嵌套循环算法基于两个循环：外循环和内循环。外部循环(驱动表)搜索第一个（外部）集合的所有行。对于每个这样的行，内部循环（被驱动表）在第二个（内部）集中搜索匹配的行。在发现满足连接条件的配对后，节点立即将其返回，然后继续扫描。

这是所有连接策略中最简单和最通用的连接策略。

AntDB 按顺序扫描外部表，并为每个结果行扫描内部表以查找匹配行。如果外部表很小，嵌套循环连接特别有效，因为这样内部循环就不会执行得太频繁。它是 OLTP 工作负载中使用的典型连接策略，具有规范化的数据模型，效率很高。如果外部表很大，嵌套循环联接通常效率很低，即使连接的内部表上有索引。除此之外，如果没有连接条件使用“=”运算符，则嵌套循环是唯一可以使用的表连接策略。因此，在没有其他连接策略可以使用的情况下，它可以作为一种后备的策略。

• 嵌套循环连接（NLJ）是最简单的连接算法，其中外部表的每条记录都与内部表的每条纪录相匹配；
• 几乎可以在具有任何类型连接子句的任何数据集上使用。由于该算法扫描内部和外部表的所有元组，因此被认为是成本最高的连接操作；

散列连接 -hash join

首先，AntDB 按顺序扫描内部表并构建哈希表，其中哈希键由使用“=”运算符的所有连接键组成。然后，它按顺序扫描外部表，并探测找到

的每一行的哈希值，以找到匹配的连接键。这有点类似于嵌套循环连接。构建哈希表需要额外的启动工作，但哈希值探测比扫描内部表快得多。

由于我们按顺序扫描这两个表，因此连接条件上的索引对哈希连接没有帮助。如果所涉及的表都不小，但较小表的哈希链表大小不超过 work_mem 参数设置的值，则使用哈希连接最好。因为不然的话 AntDB 会将分几批构建哈希链表并将其存储在临时磁盘文件中，会影响性能。在这种情况下，优化器通常选择不同的连接策略，如合并连接。只有连接条件中的运算符为“=”等值查找，才能在哈希链表中查找值，因此需要至少有一个连接条件中使用等值运算符。

合并连接 -merge join

在合并连接中，AntDB使用“=”运算符选择所有连接条件。然后，它根据连接键对两个表进行排序（这意味着数据类型必须是可排序的）。然后它遍历两个排序列表并找到匹配的条目。

要排序的列上的索引可以加快排序速度，因此两个表上的连接键上的索引可以加快合并连接。然而，除非可以使用仅索引扫描，否则显式排序通常更高效。如果所涉及的表大小对于符合 work_mem 大小的哈希连接来说都太大，优化器通常会选择使用合并连接。这是大表连接查询的最佳策略。与 hash join 一样，只有当至少存在一个带有“=”运算符的连接条件时，合并连接才可用。

• 合并连接是一种算法，其中外部表的每条记录都与内部表的每条记录匹配，直到满足连接子句匹配关系的条件方可；
• 此连接算法仅在两个表连接字段都已排序且连接子句运算符为“=”时使用；

选择合适的连接策略

如果优化器低估了行数，则可能会错误地选择嵌套循环连接。然后，它比预期的更频繁地扫描内部表，这导致了糟糕的查询性能。

如果优化器高估了行数，则可能会错误地选择哈希或合并连接。然后，它必须完全扫描这两个表，这可能比在内部表上具有索引的嵌套循环联接的性能差得多。

综上合适的统计信息对于优化器生成正确的执行计划至关重要。

收集统计信息的方式：

• 手动 vacuum analyze
• 开启自动 autovacuum

标量子查询尽量不用

SQL 允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组；这样的子查询称为标量子查询（scalar subquery）。

标量子查询就是指子查询的结果是“单个值”（一行一列）的查询。

标量子查询语句存在如下问题：主表有多少记录，标量子查询子句就要执行多少次，尤其是在表数据量较大的场景下，执行效率更慢；标量子查询越多，标量子查询子句执行次数就更多；

拆分复杂 SQL，适当使用临时表

在某些场景下需要使用较长的复杂关联查询 SQL 语句获取想要的查询结果集，这无疑会增加执行计划生成的时间（CBO 估算的代价可能也不准确），这个时候我们可以考虑化繁为简、化整为零，通过使用临时表，将复杂 SQL 中间的某一部分结果先生成，再利用临时表中的结果集和其他表进行关联，从而从整体上提升查询的效率；尤其对于使用外部表、视图关联查询的 SQL 尤为有效。

利用并行计算能力

现代 CPU 有大量的核心。多年来，应用程序一直在向数据库并行得发送查询请求。如果有处理多个表记录行的报表查询需求，那么在查询时使用多个 CPU 的能力可以帮助我们更快地执行。

AntDB 中的并行查询允许我们利用许多 CPU 更快地完成查询。影响并行的数据库参数：

• max_parallel_workers：设置系统支持的并行查询进程数
• max_parallel_workers_per_gather：设置允许启用的并行进程的进程数，支持会话级别设置