狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

前言

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

正文

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

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

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

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

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

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

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

因此獲取現(xiàn)場就變得尤為重要，但 PostgreSQL 一直惱于沒有原生好用成熟的 AWR 工具，所以得借助一些第三方工具，此處我也簡單整理了一下常用工具和插件，比如類似于 cursor sharing 的 pg_shared_plans，執(zhí)行計劃固化 sr_plan / pg_plan_guarentee 等，pg_stat_statements 肯定得裝上，基于 pg_stat_statements 實現(xiàn)丐版 AWR 也可以，關(guā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，當客戶端向數(shù)據(jù)庫發(fā)送請求時，如果結(jié)果集很大，可能會把客戶端的內(nèi)存打爆，悠著點兒。

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

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

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

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

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

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

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

    else
    {
        ......
        // 如果有自定義join生成算法則使用
        if (join_search_hook)
            return (*join_search_hook) (root, levels_needed, initial_rels);
        // 如果開啟了遺傳算法且join關(guān)系大于閾值(默認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 順序是固定的，所以路徑數(shù)量相對較少 (只需要考慮不同 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í)行計劃樹生成以下三種形式，包括左深樹、右深樹和緊密型樹。不同的連接順序，會生成不同大小的中間關(guān)系，對應(yīng) CPU 和 IO 消耗不同。

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

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

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

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

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

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

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

1. QD (Query Dispatcher、查詢調(diào)度器)：Master 節(jié)點上負責處理用戶查詢請求的進程稱為 QD (PostgreSQL 中稱之為 Backend 進程)。QD 收到用戶發(fā)來的 SQL 請求后，進行解析、重寫和優(yōu)化，將優(yōu)化后的并行計劃分發(fā)給每個 segment 進行執(zhí)行，并將最終結(jié)果返回給用戶。此外還負責整個 SQL 語句涉及到的所有的 QE 進程間的通訊控制和協(xié)調(diào)，譬如某個 QE 執(zhí)行時出現(xiàn)錯誤時，QD 負責收集錯誤詳細信息，并取消所有其他 QEs；如果 LIMIT n 語句已經(jīng)滿足，則中止所有 QE 的執(zhí)行等。QD 的入口是 exec_simple_query()。
2. QE (Query executor、查詢執(zhí)行器)：Segment 上負責執(zhí)行 QD 分發(fā)來的查詢?nèi)蝿?wù)的進程稱為QE。Segment 實例運行的也是一個 PostgreSQL，所以對于 QE 而言，QD 是一個 PostgreSQL 的客戶端，它們之間通過 PostgreSQL 標準的 libpq 協(xié)議進行通訊。對于 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 順序掃描，并把結(jié)果數(shù)據(jù)重分布發(fā)送給第二組 Gang；第二組 Gang 在 2 個 segments 上分別對表 students 順序掃描，與第一組Gang 發(fā)送到本 segment 的 classes 數(shù)據(jù)進行哈希關(guān)聯(lián)，并將最終結(jié)果發(fā)送給 Master。

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

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

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

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

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

“

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

另外要尤其注意 sum(bigint) 的行為，會導致每一條數(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.
 */

可以看到這兩個查詢的效率天差地別，僅僅是因為數(shù)據(jù)類型的原因

小結(jié)

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