前言

這兩天在團隊內部分享了一篇 《SQL優(yōu)化方法論與實戰(zhàn)》，在此也簡單整理成文字稿分享給各位。

正文

首先為什么要進行優(yōu)化？說得直白點，無外乎是為了在現有資源情況下，不付出額外的成本，提升體驗，又曰——降本增效。

那么數據庫作為日常背鍋選手，有哪些可以衡量性能的指標呢？我大致列了以下幾項：

1. 流量：每秒查詢數量QPS，每秒事務數量TPS
2. 延遲：查詢平均響應時間 Query RT，事務平均響應時間Xact RT
3. 飽和度：機器負載，CPU使用率，磁盤IO帶寬飽和度，網卡IO帶寬飽和度，內存
4. 錯誤數：數據庫客戶端連接排隊，應用報錯數量
5. 緩存命中率：多少hit，多少miss，發(fā)生了多少次實際IO，發(fā)生了多少次換頁

比如應用告警報錯閾值是 10 ms，如果某個時間段報錯數量急劇增加，這個時候可能數據庫的狀態(tài)就不太正常了，其次數據庫的緩存命中率其實也可以從側面反映出數據庫的狀態(tài)，大量 cache miss，性能注定好不到哪里去。

而延遲作為集中式數據庫的關鍵性黃金指標，延遲至關重要，假如我在某個商品界面上發(fā)起下單請求，等了許久才彈出一個付款界面，那么我會轉身就走，購買欲望瞬間降至冰點，延遲直接關系到用戶體驗。

那么作為 DBA 的我們，對于延遲也要有個大概的"尺度"，比如稍微差一點的盤，尋道時間在 3 ~ 10 ms 左右，毫秒級別，L1 / L2 CPU 緩存則在納秒級別，內存訪問的話則是在 100 納秒的級別。那如果現在有個 redis ，延遲為 100 ms，你說慢不慢？當然是慢的摳腳。

爛 SQL 的危害如果真要一一列舉出來，可能到天黑都說不完，爛 SQL 往往是導致數據庫性能衰減的元兇，性能問題源于 SQL，之外可能源于并發(fā) (居多) 或數據庫和操作系統自身維護性操作 (vacuum / freeze) 等等。

因此獲取現場就變得尤為重要，但 PostgreSQL 一直惱于沒有原生好用成熟的 AWR 工具，所以得借助一些第三方工具，此處我也簡單整理了一下常用工具和插件，比如類似于 cursor sharing 的 pg_shared_plans，執(zhí)行計劃固化 sr_plan / pg_plan_guarentee 等，pg_stat_statements 肯定得裝上，基于 pg_stat_statements 實現丐版 AWR 也可以，關于這點可以抄作業(yè) 👉🏻 Using pg_stat_statements to Optimize Queries

SQL 從客戶端發(fā)起，到數據庫執(zhí)行，再到接收，中間的每一環(huán)節(jié)都至關重要，比如網絡帶寬直接就決定了數據庫的吞吐量，這里要提一句的是，和 fetchsize 類似的是 FETCH_COUNT，也是為了防止客戶端 OOM，當客戶端向數據庫發(fā)送請求時，如果結果集很大，可能會把客戶端的內存打爆，悠著點兒。

SQL 的邏輯順序不多說了，關于物理執(zhí)行順序需要說明一下。

當一條查詢進來之后，會經過Parser → Analyzer → Rewriter → Planner → executor 這一系列步驟，生成各種各樣的"樹"。若是 DDL 語句，無需進行優(yōu)化，到 utility 模塊處理，對于 DML 則需要按照完整的流程。(最近我正在看 "Journey of a DML query"，后續(xù)也會分享給各位)。

對于數據庫來說，傳入的 SQL 語句不過是一串"文本"，PostgreSQL 并不知曉也不理解這一串文本是什么意思，因此我們需要告訴數據庫該如何理解這一串文本，之后 SQL 語句就會被轉化為內部結構，即語法解析樹，再經過優(yōu)化的處理，最終轉化為執(zhí)行器可以高效執(zhí)行的計劃樹。

而優(yōu)化器作為數據庫的大腦，優(yōu)化器的好壞直接決定了一個數據庫的"上限"，決定了一個數據庫面對復雜語句的處理能力。說白了，邏輯優(yōu)化就是盡量對查詢進行等價或者推倒變換，以達到更有效率的執(zhí)行計劃。因為 SQL 是聲明式語言，我們只是指定了需要返回什么結果，而沒有指定它該怎么做。

在此也貼一個關于優(yōu)化器涉及到的相關參數和系統表，以及核心代碼流程，之前有位讀者問過我這塊：

對于 Greenplum 來說，他既支持傳統 PostgreSQL 優(yōu)化器，也有 ORCA。對于 GPORCA 不支持的特性，GPORCA 會自動回到 Planner。

其中 PostgreSQL  優(yōu)化器采用了兩種方法：自底向上使用的是動態(tài)規(guī)劃，隨機方法使用的是遺傳算法，由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  // 否則，使用動態(tài)規(guī)劃算法
            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)

等等。多表間的連接順序表示了查詢計劃樹的基本形態(tài)。一棵樹就是一種查詢路徑，SQL 的語義可以由多棵這樣的樹表達，從中選擇花費最少的樹，就是最優(yōu)查詢計劃形成的過程。一棵樹包括左深連接樹、右深連接樹、緊密樹。PostgreSQL 優(yōu)化器主要考慮將執(zhí)行計劃樹生成以下三種形式，包括左深樹、右深樹和緊密型樹。不同的連接順序，會生成不同大小的中間關系，對應 CPU 和 IO 消耗不同。

PostgreSQL 中會嘗試多種連接方式存放到 "path" 上，以找出花費最小的路徑。

試想一下，如果A ⨝ B ⨝ C ⨝ D，那么有 N! ✕ (N-1)! 這么多種可能的計劃 (ABCD, ABDC, ADBC, DABC ...)。人們針對樹的形成及其花費代價最少的，提出了諸多算法。樹形成過程有以下兩種策略：

• 至頂向下。從 SQL 表達式樹的樹根開始，向下進行，估計每個結點可能的執(zhí)行方法，計算每種組合的代價，從中挑選最優(yōu)的。
• 自底向上。從 SQL 表達式樹的樹葉開始，向上進行，計算每個子表達式的所有實現方法的代價，從中挑選最優(yōu)的，再和上層 (靠近樹根) 的進行連接，周而復始直至樹根。

在數據庫實現中，多數數據庫采取了自底向上的策略。就 PostgreSQL 而言，查詢優(yōu)化可以大體分為四個步驟：

1. 查詢樹預處理：比如常量簡化，函數內聯，子鏈接提升等
2. 掃描/連接優(yōu)化：為查詢語句中掃描和連接部分做計劃 (動態(tài)規(guī)劃，遺傳算法)
3. 特殊處理：比如 GROUP BY，窗口函數，集合等
4. 計劃樹后處理：把代價最小的路徑轉換成計劃樹，轉換為執(zhí)行器可以執(zhí)行的計劃樹

如果看到這樣類似的關鍵字，則代表是 ORCA 優(yōu)化器，其是基于自頂向下的查詢優(yōu)化器，對于復雜 SQL 性能較好，但是生成執(zhí)行計劃的時間也更久。

讓我們看一個實際的例子 (Greenplum 相較于 PostgreSQL 多了一些算子和術語) ：

1. QD (Query Dispatcher、查詢調度器)：Master 節(jié)點上負責處理用戶查詢請求的進程稱為 QD (PostgreSQL 中稱之為 Backend 進程)。QD 收到用戶發(fā)來的 SQL 請求后，進行解析、重寫和優(yōu)化，將優(yōu)化后的并行計劃分發(fā)給每個 segment 進行執(zhí)行，并將最終結果返回給用戶。此外還負責整個 SQL 語句涉及到的所有的 QE 進程間的通訊控制和協調，譬如某個 QE 執(zhí)行時出現錯誤時，QD 負責收集錯誤詳細信息，并取消所有其他 QEs；如果 LIMIT n 語句已經滿足，則中止所有 QE 的執(zhí)行等。QD 的入口是 exec_simple_query()。
2. QE (Query executor、查詢執(zhí)行器)：Segment 上負責執(zhí)行 QD 分發(fā)來的查詢任務的進程稱為QE。Segment 實例運行的也是一個 PostgreSQL，所以對于 QE 而言，QD 是一個 PostgreSQL 的客戶端，它們之間通過 PostgreSQL 標準的 libpq 協議進行通訊。對于 QD 而言，QE 是負責執(zhí)行其查詢請求的 PostgreSQL Backend 進程。通常 QE 執(zhí)行整個查詢的一部分 (稱為 Slice)。QE 的入口是 exec_mpp_query()。
3. Slice：為了提高查詢執(zhí)行并行度和效率，Greenplum 把一個完整的分布式查詢計劃從下到上分成多個 Slice，每個 Slice 負責計劃的一部分。劃分 slice 的邊界為 Motion，每遇到 Motion 則一刀將 Motion 切成發(fā)送方和接收方，得到兩顆子樹。每個 slice 由一個 QE 進程處理。
4. Gang：在不同 segments 上執(zhí)行同一個 slice 的所有 QEs 進程稱為 Gang。上例中有兩組Gang，第一組 Gang 負責在 2 個 segments 上分別對表 classes 順序掃描，并把結果數據重分布發(fā)送給第二組 Gang；第二組 Gang 在 2 個 segments 上分別對表 students 順序掃描，與第一組Gang 發(fā)送到本 segment 的 classes 數據進行哈希關聯，并將最終結果發(fā)送給 Master。

這里主要提一下 rows 的預估，各位可以參照我之前寫的執(zhí)行計劃篇章，根據 pg_stats 統計信息計算而來，這也再次說明了統計信息的重要性，不然優(yōu)化器無從下手。

當然還有各種各樣的輔助算子，用于執(zhí)行某些特定操作，比如

1. Subquery Scan，掃描一個子查詢
2. Function Scan，處理含有函數的掃描
3. TableFunc Scan，處理tablefunc 相關的掃描
4. Values Scan，用于掃描Values 鏈表的掃描
5. ...

掃描方式就不多說了，順序掃描 / 索引掃描 / bitmap scan，不過 Greenplum 是支持 bitmap 索引的。

對于向量化計算，各位可能也經常在各大產品 PR 里面聽到，此處推薦閱讀一下 PgSQL · 引擎介紹 · 向量化執(zhí)行引擎簡介

“

過去的 20-30 年計算機硬件能力的持續(xù)發(fā)展，使得計算機的計算能力飛速提升。然后，我們很多的應用卻沒有做到足夠的調整到與硬件能力配套的程度，因此也就不能夠充分的將計算機強大的計算能力轉換為軟件的生產力。這樣的問題在今天的通用數據庫系統中也是一個比較突出的問題，因為這些通用數據庫系統往往都已經有十數年或者幾十年的歷史了，它們也存在著不能夠充分利用現在硬件能力的情況。

多表關聯的算法包括 NSL / HASH JOIN / MERGE JOIN，HASH JOIN 要關注批次 "batch" 的問題

讓我們回到 Greenplum，Greenplum 不同于集中式 PostgreSQL，由多個 segment + master 組成，master 僅僅是存放元信息，做結果的匯總 (Gather)

對于 JOIN，如果是基于分布鍵的等值連接 (因為同樣的數據都位于同一個數據節(jié)點)，那么每個 segment 可以本地連接，最后通過 Gather Motion 收集結果即可。

相反，如果不是基于分布鍵的等值連接，那么需要重分布其中一個表，或重分布兩個表，或者廣播，因為我需要的數據位于其他節(jié)點上了，需要將數據傳輸到指定節(jié)點進行關聯。

比如這個計劃，就很明顯，沒有涉及到重分布 (redistribute)，而第二個由于不是分布鍵，就涉及到了重分布。

對于冗長的 SQL，執(zhí)行計劃可能滿滿一屏幕都看不完，人肉分析費時費力，因此我們需要借助一些工具將執(zhí)行計劃可視化一下，這就是 PEV，一目了然，可以迅速發(fā)現高消耗節(jié)點，著重優(yōu)化這些高消耗節(jié)點，用得較多的是 "大力波"。

現在，讓我們看一下實際的優(yōu)化案例，老生常談的當然是索引失效了，各位就直接看 PPT 吧。

關于分區(qū)裁剪，Greenplum7 里看著無法裁剪 stable 的函數，有環(huán)境的讀者可以測一下，也歡迎讀者告訴下我結果。

內存對齊我也提及過很多次，由于 CPU 取址是按照"模子" 去取的，存在著對齊。由于 Greenplum 存在行存表，AOCO 和 AORO ，此處針對傳統堆表，推薦字段排放順序如下：

1. 分布鍵列
2. 分區(qū)列
3. NOT NULL固定長度的屬性
4. 少量NULL固定長度的屬性
5. 合理排列固定長度的屬性
6. 所有變長列放到右邊
7. 使用專有的數據類型，減少轉換，提高數據存儲效率

一個小小的規(guī)范，可能就讓你從原來需要 40C 資源，降低到了 35C，何樂而不為呢。

另外前面也提到了，SQL 是一種聲明式的語言，what to do，而不是 how to do。對于一條 SQL，數據庫可以有多種方式去執(zhí)行，條條大路通羅馬，比如順序掃描、索引掃描，多表連接的話又有 nestloop、hashjoin、mergejoin 等，需要有一種機制告訴它如何去選擇一條最優(yōu)的方式去生成執(zhí)行計劃，這就是統計信息的作用，知道數據的一個分布情況，比如高頻值，非重復值數量，是否有空值等等。

如果統計信息過舊，那么優(yōu)化器做出的決策可能就不準確，我們可以根據 pg_stat_all_tables.last_analyze和last_autoanalyze 查詢何時做了 analyze ，確保統計信息沒有過舊。

另外就是擴展統計信息了，Greenplum7 源自 12 的內核，所以也支持

由于 Greenplum 是分布式數據庫，分布鍵的設計至關重要，分布鍵的設計應遵循：數據均勻分布原則、本地操作原則和負載均衡原則。無特殊情況，不使用隨機分布。

比如下面這個例子，就存在著數據傾斜，另外兩個節(jié)點只能干瞪著另外一個節(jié)點熱火朝天，所以木桶效應的預防尤為重要，對于所有需要設計 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 優(yōu)化的一點小心得，希望各位讀者閱讀之后能夠有所收獲。