正文

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

那么數(shù)據(jù)庫作為日常背鍋選手，有哪些可以衡量性能的指標(biāo)呢？我大致列了以下幾項(xiàng)：

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

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

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

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

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

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

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

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

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

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

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

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

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

其中 PostgreSQL  優(yōu)化器采用了兩種方法：自底向上使用的是動(dòng)態(tài)規(guī)劃，隨機(jī)方法使用的是遺傳算法，由geqo_threshold 參數(shù)控制何時(shí)使用遺傳算法，默認(rèn)是 12。

    else
    {
        ......
        // 如果有自定義join生成算法則使用
        if (join_search_hook)
            return (*join_search_hook) (root, levels_needed, initial_rels);
        // 如果開啟了遺傳算法且join關(guān)系大于閾值(默認(rèn)12)則使用遺傳算法
        else if (enable_geqo && levels_needed >= geqo_threshold)
            return geqo(root, levels_needed, initial_rels);
        else  // 否則，使用動(dòng)態(tài)規(guī)劃算法
            return standard_join_search(root, levels_needed, initial_rels);
    }
}

對(duì)于 OUTER JOIN 來說，JOIN 順序是固定的，所以路徑數(shù)量相對(duì)較少 (只需要考慮不同 JOIN 算法組成的路徑)；然而對(duì)于 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)

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

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

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

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

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

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

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

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

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

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

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

1. Subquery Scan，掃描一個(gè)子查詢
2. Function Scan，處理含有函數(shù)的掃描
3. TableFunc Scan，處理tablefunc 相關(guān)的掃描
4. Values Scan，用于掃描Values 鏈表的掃描
5. ...

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

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

“

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

多表關(guān)聯(lián)的算法包括 NSL / HASH JOIN / MERGE JOIN，HASH JOIN 要關(guān)注批次 "batch" 的問題

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

對(duì)于 JOIN，如果是基于分布鍵的等值連接 (因?yàn)橥瑯拥臄?shù)據(jù)都位于同一個(gè)數(shù)據(jù)節(jié)點(diǎn))，那么每個(gè) segment 可以本地連接，最后通過 Gather Motion 收集結(jié)果即可。

相反，如果不是基于分布鍵的等值連接，那么需要重分布其中一個(gè)表，或重分布兩個(gè)表，或者廣播，因?yàn)槲倚枰臄?shù)據(jù)位于其他節(jié)點(diǎn)上了，需要將數(shù)據(jù)傳輸?shù)街付ü?jié)點(diǎn)進(jìn)行關(guān)聯(lián)。

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

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

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

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

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

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

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

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

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

另外就是擴(kuò)展統(tǒng)計(jì)信息了，Greenplum7 源自 12 的內(nèi)核，所以也支持

由于 Greenplum 是分布式數(shù)據(jù)庫，分布鍵的設(shè)計(jì)至關(guān)重要，分布鍵的設(shè)計(jì)應(yīng)遵循：數(shù)據(jù)均勻分布原則、本地操作原則和負(fù)載均衡原則。無特殊情況，不使用隨機(jī)分布。

比如下面這個(gè)例子，就存在著數(shù)據(jù)傾斜，另外兩個(gè)節(jié)點(diǎn)只能干瞪著另外一個(gè)節(jié)點(diǎn)熱火朝天，所以木桶效應(yīng)的預(yù)防尤為重要，對(duì)于所有需要設(shè)計(jì) shard key 的數(shù)據(jù)庫都是一樣。

關(guān)于聚集，有兩種方式：

1. GroupAggregatede 特點(diǎn)是在進(jìn)行聚合之前先要將數(shù)據(jù)進(jìn)行排序，然后進(jìn)行聚合操作，而且出來的結(jié)果是有序的。
2. HashAggregatede特點(diǎn)是不需要進(jìn)行排序，在組數(shù)值比較小的情況下是比GroupAggregate要快很多，但是需求的內(nèi)存會(huì)比較多。

另外 HashAggregatede 只能進(jìn)行一些簡(jiǎn)單的聚合，像count (distinct …) 這類聚合是做不了的 (針對(duì)原生PostgreSQL 的情況)，大部分情況下 HashAggregatede 的效率都會(huì)比 GroupAggregatede 要好，主要是排序這個(gè)操作比較耗時(shí)，本質(zhì)上 GroupAggregatede 是在用空間 (內(nèi)存) 換時(shí)間，內(nèi)存充足的情況下這種做可以，但是內(nèi)存不足容易 OOM。

另外要尤其注意 sum(bigint) 的行為，會(huì)導(dǎo)致每一條數(shù)據(jù)都要轉(zhuǎn)換，盡量避免！

最后就是鮮為人知的 union all 了，關(guān)聯(lián)的數(shù)據(jù)類型最好保持一致！否則是無法做視圖展開的

/*
 * 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.
 */

可以看到這兩個(gè)查詢的效率天差地別，僅僅是因?yàn)閿?shù)據(jù)類型的原因

小結(jié)

以上便是關(guān)于 SQL 優(yōu)化的一點(diǎn)小心得，希望各位讀者閱讀之后能夠有所收獲。