前言

这两天在团队内部分享了一篇 《SQL优化方法论与实战》，在此也简单整理成文字稿分享给各位。

正文

首先为什么要进行优化？说得直白点，无外乎是为了在现有资源情况下，不付出额外的成本，提升体验，又曰——降本增效。

那么数据库作为日常背锅选手，有哪些可以衡量性能的指标呢？我大致列了以下几项：

1. 流量：每秒查询数量QPS，每秒事务数量TPS
2. 延迟：查询平均响应时间 Query RT，事务平均响应时间Xact RT
3. 饱和度：机器负载，CPU使用率，磁盘IO带宽饱和度，网卡IO带宽饱和度，内存
4. 错误数：数据库客户端连接排队，应用报错数量
5. 缓存命中率：多少hit，多少miss，发生了多少次实际IO，发生了多少次换页

比如应用告警报错阈值是 10 ms，如果某个时间段报错数量急剧增加，这个时候可能数据库的状态就不太正常了，其次数据库的缓存命中率其实也可以从侧面反映出数据库的状态，大量 cache miss，性能注定好不到哪里去。

而延迟作为集中式数据库的关键性黄金指标，延迟至关重要，假如我在某个商品界面上发起下单请求，等了许久才弹出一个付款界面，那么我会转身就走，购买欲望瞬间降至冰点，延迟直接关系到用户体验。

那么作为 DBA 的我们，对于延迟也要有个大概的"尺度"，比如稍微差一点的盘，寻道时间在 3 ~ 10 ms 左右，毫秒级别，L1 / L2 CPU 缓存则在纳秒级别，内存访问的话则是在 100 纳秒的级别。那如果现在有个 redis ，延迟为 100 ms，你说慢不慢？当然是慢的抠脚。

烂 SQL 的危害如果真要一一列举出来，可能到天黑都说不完，烂 SQL 往往是导致数据库性能衰减的元凶，性能问题源于 SQL，之外可能源于并发 (居多) 或数据库和操作系统自身维护性操作 (vacuum / freeze) 等等。

因此获取现场就变得尤为重要，但 PostgreSQL 一直恼于没有原生好用成熟的 AWR 工具，所以得借助一些第三方工具，此处我也简单整理了一下常用工具和插件，比如类似于 cursor sharing 的 pg_shared_plans，执行计划固化 sr_plan / pg_plan_guarentee 等，pg_stat_statements 肯定得装上，基于 pg_stat_statements 实现丐版 AWR 也可以，关于这点可以抄作业 👉🏻 Using pg_stat_statements to Optimize Queries

SQL 从客户端发起，到数据库执行，再到接收，中间的每一环节都至关重要，比如网络带宽直接就决定了数据库的吞吐量，这里要提一句的是，和 fetchsize 类似的是 FETCH_COUNT，也是为了防止客户端 OOM，当客户端向数据库发送请求时，如果结果集很大，可能会把客户端的内存打爆，悠着点儿。

SQL 的逻辑顺序不多说了，关于物理执行顺序需要说明一下。

当一条查询进来之后，会经过Parser → Analyzer → Rewriter → Planner → executor 这一系列步骤，生成各种各样的"树"。若是 DDL 语句，无需进行优化，到 utility 模块处理，对于 DML 则需要按照完整的流程。(最近我正在看 "Journey of a DML query"，后续也会分享给各位)。

对于数据库来说，传入的 SQL 语句不过是一串"文本"，PostgreSQL 并不知晓也不理解这一串文本是什么意思，因此我们需要告诉数据库该如何理解这一串文本，之后 SQL 语句就会被转化为内部结构，即语法解析树，再经过优化的处理，最终转化为执行器可以高效执行的计划树。

而优化器作为数据库的大脑，优化器的好坏直接决定了一个数据库的"上限"，决定了一个数据库面对复杂语句的处理能力。说白了，逻辑优化就是尽量对查询进行等价或者推倒变换，以达到更有效率的执行计划。因为 SQL 是声明式语言，我们只是指定了需要返回什么结果，而没有指定它该怎么做。

在此也贴一个关于优化器涉及到的相关参数和系统表，以及核心代码流程，之前有位读者问过我这块：

对于 Greenplum 来说，他既支持传统 PostgreSQL 优化器，也有 ORCA。对于 GPORCA 不支持的特性，GPORCA 会自动回到 Planner。

其中 PostgreSQL  优化器采用了两种方法：自底向上使用的是动态规划，随机方法使用的是遗传算法，由geqo_threshold 参数控制何时使用遗传算法，默认是 12。

    else
    {
        ......
        // 如果有自定义join生成算法则使用
        if (join_search_hook)
            return (*join_search_hook) (root, levels_needed, initial_rels);
        // 如果开启了遗传算法且join关系大于阈值(默认12)则使用遗传算法
        else if (enable_geqo && levels_needed >= geqo_threshold)
            return geqo(root, levels_needed, initial_rels);
        else  // 否则，使用动态规划算法
            return standard_join_search(root, levels_needed, initial_rels);
    }
}

对于 OUTER JOIN 来说，JOIN 顺序是固定的，所以路径数量相对较少 (只需要考虑不同 JOIN 算法组成的路径)；然而对于 INNER JOIN 来说，表之间的 JOIN 顺序是可以不同的，这样就可以由不同的 JOIN 组合、不同的 JOIN 顺序组成非常多的不同路径。如A JOIN B JOIN C，路径有：

• (A⋈B)⋈C ：就有两种排列顺序(A JOIN B) JOIN C 和 C JOIN (A JOIN B)
• (A JOIN C) JOIN B
• A JOIN (C JOIN B)

等等。多表间的连接顺序表示了查询计划树的基本形态。一棵树就是一种查询路径，SQL 的语义可以由多棵这样的树表达，从中选择花费最少的树，就是最优查询计划形成的过程。一棵树包括左深连接树、右深连接树、紧密树。PostgreSQL 优化器主要考虑将执行计划树生成以下三种形式，包括左深树、右深树和紧密型树。不同的连接顺序，会生成不同大小的中间关系，对应 CPU 和 IO 消耗不同。

PostgreSQL 中会尝试多种连接方式存放到 "path" 上，以找出花费最小的路径。

试想一下，如果A ⨝ B ⨝ C ⨝ D，那么有 N! ✕ (N-1)! 这么多种可能的计划 (ABCD, ABDC, ADBC, DABC ...)。人们针对树的形成及其花费代价最少的，提出了诸多算法。树形成过程有以下两种策略：

• 至顶向下。从 SQL 表达式树的树根开始，向下进行，估计每个结点可能的执行方法，计算每种组合的代价，从中挑选最优的。
• 自底向上。从 SQL 表达式树的树叶开始，向上进行，计算每个子表达式的所有实现方法的代价，从中挑选最优的，再和上层 (靠近树根) 的进行连接，周而复始直至树根。

在数据库实现中，多数数据库采取了自底向上的策略。就 PostgreSQL 而言，查询优化可以大体分为四个步骤：

1. 查询树预处理：比如常量简化，函数内联，子链接提升等
2. 扫描/连接优化：为查询语句中扫描和连接部分做计划 (动态规划，遗传算法)
3. 特殊处理：比如 GROUP BY，窗口函数，集合等
4. 计划树后处理：把代价最小的路径转换成计划树，转换为执行器可以执行的计划树

如果看到这样类似的关键字，则代表是 ORCA 优化器，其是基于自顶向下的查询优化器，对于复杂 SQL 性能较好，但是生成执行计划的时间也更久。

让我们看一个实际的例子 (Greenplum 相较于 PostgreSQL 多了一些算子和术语) ：

1. QD (Query Dispatcher、查询调度器)：Master 节点上负责处理用户查询请求的进程称为 QD (PostgreSQL 中称之为 Backend 进程)。QD 收到用户发来的 SQL 请求后，进行解析、重写和优化，将优化后的并行计划分发给每个 segment 进行执行，并将最终结果返回给用户。此外还负责整个 SQL 语句涉及到的所有的 QE 进程间的通讯控制和协调，譬如某个 QE 执行时出现错误时，QD 负责收集错误详细信息，并取消所有其他 QEs；如果 LIMIT n 语句已经满足，则中止所有 QE 的执行等。QD 的入口是 exec_simple_query()。
2. QE (Query executor、查询执行器)：Segment 上负责执行 QD 分发来的查询任务的进程称为QE。Segment 实例运行的也是一个 PostgreSQL，所以对于 QE 而言，QD 是一个 PostgreSQL 的客户端，它们之间通过 PostgreSQL 标准的 libpq 协议进行通讯。对于 QD 而言，QE 是负责执行其查询请求的 PostgreSQL Backend 进程。通常 QE 执行整个查询的一部分 (称为 Slice)。QE 的入口是 exec_mpp_query()。
3. Slice：为了提高查询执行并行度和效率，Greenplum 把一个完整的分布式查询计划从下到上分成多个 Slice，每个 Slice 负责计划的一部分。划分 slice 的边界为 Motion，每遇到 Motion 则一刀将 Motion 切成发送方和接收方，得到两颗子树。每个 slice 由一个 QE 进程处理。
4. Gang：在不同 segments 上执行同一个 slice 的所有 QEs 进程称为 Gang。上例中有两组Gang，第一组 Gang 负责在 2 个 segments 上分别对表 classes 顺序扫描，并把结果数据重分布发送给第二组 Gang；第二组 Gang 在 2 个 segments 上分别对表 students 顺序扫描，与第一组Gang 发送到本 segment 的 classes 数据进行哈希关联，并将最终结果发送给 Master。

这里主要提一下 rows 的预估，各位可以参照我之前写的执行计划篇章，根据 pg_stats 统计信息计算而来，这也再次说明了统计信息的重要性，不然优化器无从下手。

当然还有各种各样的辅助算子，用于执行某些特定操作，比如

1. Subquery Scan，扫描一个子查询
2. Function Scan，处理含有函数的扫描
3. TableFunc Scan，处理tablefunc 相关的扫描
4. Values Scan，用于扫描Values 链表的扫描
5. ...

扫描方式就不多说了，顺序扫描 / 索引扫描 / bitmap scan，不过 Greenplum 是支持 bitmap 索引的。

对于向量化计算，各位可能也经常在各大产品 PR 里面听到，此处推荐阅读一下 PgSQL · 引擎介绍 · 向量化执行引擎简介

“

过去的 20-30 年计算机硬件能力的持续发展，使得计算机的计算能力飞速提升。然后，我们很多的应用却没有做到足够的调整到与硬件能力配套的程度，因此也就不能够充分的将计算机强大的计算能力转换为软件的生产力。这样的问题在今天的通用数据库系统中也是一个比较突出的问题，因为这些通用数据库系统往往都已经有十数年或者几十年的历史了，它们也存在着不能够充分利用现在硬件能力的情况。

多表关联的算法包括 NSL / HASH JOIN / MERGE JOIN，HASH JOIN 要关注批次 "batch" 的问题

让我们回到 Greenplum，Greenplum 不同于集中式 PostgreSQL，由多个 segment + master 组成，master 仅仅是存放元信息，做结果的汇总 (Gather)

对于 JOIN，如果是基于分布键的等值连接 (因为同样的数据都位于同一个数据节点)，那么每个 segment 可以本地连接，最后通过 Gather Motion 收集结果即可。

相反，如果不是基于分布键的等值连接，那么需要重分布其中一个表，或重分布两个表，或者广播，因为我需要的数据位于其他节点上了，需要将数据传输到指定节点进行关联。

比如这个计划，就很明显，没有涉及到重分布 (redistribute)，而第二个由于不是分布键，就涉及到了重分布。

对于冗长的 SQL，执行计划可能满满一屏幕都看不完，人肉分析费时费力，因此我们需要借助一些工具将执行计划可视化一下，这就是 PEV，一目了然，可以迅速发现高消耗节点，着重优化这些高消耗节点，用得较多的是 "大力波"。

现在，让我们看一下实际的优化案例，老生常谈的当然是索引失效了，各位就直接看 PPT 吧。

关于分区裁剪，Greenplum7 里看着无法裁剪 stable 的函数，有环境的读者可以测一下，也欢迎读者告诉下我结果。

内存对齐我也提及过很多次，由于 CPU 取址是按照"模子" 去取的，存在着对齐。由于 Greenplum 存在行存表，AOCO 和 AORO ，此处针对传统堆表，推荐字段排放顺序如下：

1. 分布键列
2. 分区列
3. NOT NULL固定长度的属性
4. 少量NULL固定长度的属性
5. 合理排列固定长度的属性
6. 所有变长列放到右边
7. 使用专有的数据类型，减少转换，提高数据存储效率

一个小小的规范，可能就让你从原来需要 40C 资源，降低到了 35C，何乐而不为呢。

另外前面也提到了，SQL 是一种声明式的语言，what to do，而不是 how to do。对于一条 SQL，数据库可以有多种方式去执行，条条大路通罗马，比如顺序扫描、索引扫描，多表连接的话又有 nestloop、hashjoin、mergejoin 等，需要有一种机制告诉它如何去选择一条最优的方式去生成执行计划，这就是统计信息的作用，知道数据的一个分布情况，比如高频值，非重复值数量，是否有空值等等。

如果统计信息过旧，那么优化器做出的决策可能就不准确，我们可以根据 pg_stat_all_tables.last_analyze和last_autoanalyze 查询何时做了 analyze ，确保统计信息没有过旧。

另外就是扩展统计信息了，Greenplum7 源自 12 的内核，所以也支持

由于 Greenplum 是分布式数据库，分布键的设计至关重要，分布键的设计应遵循：数据均匀分布原则、本地操作原则和负载均衡原则。无特殊情况，不使用随机分布。

比如下面这个例子，就存在着数据倾斜，另外两个节点只能干瞪着另外一个节点热火朝天，所以木桶效应的预防尤为重要，对于所有需要设计 shard key 的数据库都是一样。

关于聚集，有两种方式：

1. GroupAggregatede 特点是在进行聚合之前先要将数据进行排序，然后进行聚合操作，而且出来的结果是有序的。
2. HashAggregatede特点是不需要进行排序，在组数值比较小的情况下是比GroupAggregate要快很多，但是需求的内存会比较多。

另外 HashAggregatede 只能进行一些简单的聚合，像count (distinct …) 这类聚合是做不了的 (针对原生PostgreSQL 的情况)，大部分情况下 HashAggregatede 的效率都会比 GroupAggregatede 要好，主要是排序这个操作比较耗时，本质上 GroupAggregatede 是在用空间 (内存) 换时间，内存充足的情况下这种做可以，但是内存不足容易 OOM。

另外要尤其注意 sum(bigint) 的行为，会导致每一条数据都要转换，尽量避免！

最后就是鲜为人知的 union all 了，关联的数据类型最好保持一致！否则是无法做视图展开的

/*
 * We require all the setops to be union ALL (no mixing) and there can't be
 * any datatype coercions involved, ie, all the leaf queries must emit the
 * same datatypes.
 */

可以看到这两个查询的效率天差地别，仅仅是因为数据类型的原因

小结

以上便是我个人关于 SQL 优化的一点小心得，希望各位读者阅读之后能够有所收获。