全部產品
Search

搜尋本產品

    AnalyticDB：查詢效能最佳化指導

    文件中心

    AnalyticDB：查詢效能最佳化指導

    更新時間：Feb 05, 2024

    本文介紹在不同操作環境下使用雲原生資料倉儲AnalyticDB PostgreSQL版時的一些具體建議。選擇合適的操作實踐將有效地協助您提高AnalyticDB PostgreSQL的效能。

    收集統計資訊

    AnalyticDB PostgreSQL的最佳化器在進行查詢最佳化時，會根據統計資訊進行查詢代價估算和最佳化。如果參與查詢的表沒有收集過統計資訊或統計資訊過舊，系統將按照預設值或老舊的統計資訊進行最佳化，往往無法產生最優執行計畫。所以，建議在大批量資料載入完成，或者有較多資料（超過20%）更新後，進行統計資訊收集。

    採用ANALYZE命令收集統計資訊時，可以對所有表收集、對某個表的所有列收集或對錶的指定列收集。對於大部分使用者，建議採用對所有表收集或對錶的所有列收集的方式。如果想對統計資訊收集環節做精細化控制，可以採用對錶的指定列收集方式，針對關聯（JOIN）的條件列、過濾條件列、有索引的列進行統計資訊收集。

    樣本

    • 收集所有表的統計資訊樣本（推薦資料大批量入庫後使用）： 
      ANALYZE;
    • 收集表t的所有列的統計資訊樣本（推薦某個表插入/更新/刪除較多資料後使用）： 
      ANALYZE t;
    • 收集表t的a列的統計資訊樣本： 
      ANALYZE t(a);

    兩種最佳化器的選擇

    AnalyticDB PostgreSQL有兩個SQL最佳化器可供選擇，兩個最佳化器在不同的情境下，各有優勢。

    • Legacy最佳化器

      Legacy最佳化器的SQL最佳化耗時較短，適合高並發的簡單查詢情境（3表以內關聯），或者高並發的資料寫入或更新情境（INSERT/UPDATE/DELTE）。

    • ORCA最佳化器

      ORCA最佳化器為面向複雜SQL語句的最佳化器，會遍曆更多執行路徑，制定最優執行計畫，但SQL最佳化過程相對耗時稍長。建議對複雜查詢（3表以上關聯為主的情境）為主的 ETL情境和報表情境採用ORCA最佳化器。此外，ORCA最佳化器具有相互關聯的子查詢的解關聯最佳化及動態分區裁剪最佳化等能力，含有相互關聯的子查詢的語句及含有帶參數化過濾條件的分區表的語句建議使用ORCA最佳化器。

    Session會話級設定方式：

    -- 使用Legacy最佳化器
set optimizer = off;
-- 使用ORCA最佳化器
set optimizer = on;

    查看當前的最佳化器的方式：

    show optimizer;
-- 值為on：表示當前最佳化器為ORCA最佳化器
-- 值為off：表示當前最佳化器為Legacy最佳化器
    說明
    • AnalyticDB PostgreSQL 4.3版的預設最佳化器為Legacy最佳化器，AnalyticDB PostgreSQL 6.0版的預設最佳化器為ORCA最佳化器。
    • 執行個體層級設定請聯絡支援人員進行修改。

    使用索引加速查詢

    當查詢中有等值過濾條件或範圍過濾條件，且過濾後資料量較少時，可考慮在條件列上建立索引，提升資料掃描的速度。AnalyticDB PostgreSQL目前支援3種索引：

    • BTree：適用於唯一值較多的資料列，列上有過濾條件或Join條件，或為排序列。
    • Bitmap：適用於唯一值較少的資料列，查詢中該列上有多個過濾條件。
    • Gist：適用於地理位置、範圍資料類型、映像特徵值資料、幾何類資料等。

    樣本

    無索引時，帶條件的表資料擷取採用全表掃描再進行過濾的方式：

    postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=16)
         Filter: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

    使用如下的語句在t表的b列上建立BTree索引：

    postgres=# CREATE INDEX i_t_b ON t USING btree (b);
CREATE INDEX

    有索引時，帶條件的表資料擷取採用索引方式：

    postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.00 rows=1 width=16)
   ->  Index Scan using i_t_b on t  (cost=0.00..2.00 rows=1 width=16)
         Index Cond: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

    查看執行計畫

    執行計畫是資料庫運行SQL的步驟，相當於演算法。查看查詢的執行計畫有助於分析查詢的執行過程，分析慢SQL的瓶頸點，協助我們明確最佳化方向。可以通過在查詢前加explain關鍵字，查看查詢的執行計畫，此時只顯示查詢的執行計畫，而不會執行該語句。也可以在查詢前加explain analyze關鍵字，其會運行該語句，收集查詢時真實的執行資訊，並在查詢計劃上顯示出來。

    • explain樣本：

      postgres=# EXPLAIN SELECT a, b FROM t;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
 Optimizer status: legacy query optimizer
(3 rows)

    • explain analyze樣本：

      postgres=# EXPLAIN ANALYZE SELECT a, b FROM t;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   Rows out:  100 rows at destination with 2.728 ms to first row, 2.838 ms to end, start offset by 0.418 ms.
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
         Rows out:  Avg 33.3 rows x 3 workers.  Max 37 rows (seg2) with 0.088 ms to first row, 0.107 ms to end, start offset by 2.887 ms.
 Slice statistics:
   (slice0)    Executor memory: 131K bytes.
   (slice1)    Executor memory: 163K bytes avg x 3 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 3.739 ms
(11 rows)

    執行計畫由一系列的運算元及其資訊按照執行邏輯順序有機組合在一起，並按此以流水線方式執行，進行資料處理。

    運算元種類：

    • 資料掃描運算元：Seq Scan、Table Scan、Index Scan、Bitmap Scan等。
    • 串連運算元：Hash Join、Nested Loop、Merge Join
    • 聚集運算元：Hash Aggregate、Group Aggregate
    • 分布式運算元：Redistribute Motion、Broadcast Motion、Gather Motion
    • 其他運算元：Hash、Sort、Limit、Append等
    postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.b = t2.b;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.b = t2.b
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.b
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.b
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

    在上述執行計畫中，進行了如下的計算過程：

    1. Table Scan運算元對錶t1和t2進行表掃描操作。
    2. Redistribute Motion運算元分別按照t1.b和t2.b的Hash值對t1表和t2表進行了資料重分布（Redistribute Motion），將資料重新分配到了各個節點上，以便於進行Join計算。
    3. Hash運算元在t2表上建了一個用於Join的Hash表。
    4. Hash Join運算元對兩個表的資料做了Join計算。
    5. Gather Motion運算元將計算結果傳輸到前端總控節點，進而傳輸到用戶端。

    執行計畫的整體結構如上所述，具體執行計畫會隨查詢語句的不同發生變化。

    消除分布式（Motion）運算元提升效能

    在進行串連或聚集操作時，AnalyticDB PostgreSQL會根據資料分布情況添加分布式運算元，對資料進行重分布（Redistribute Motion）或廣播（Broadcast Motion）。分布式運算元會佔用大量的網路資源。如果能夠通過建表和商務邏輯進行分布式運算元的規避，則能夠提升資料庫查詢效能。

    基本原理

    如果表定義時設定的分布鍵與商務邏輯並不匹配，則需對分布鍵進行調整，儘可能減少查詢中的分布式運算元。

    樣本

    SELECT * FROM t1, t2 WHERE t1.a=t2.a;

    其中，t1表的分布鍵為t1.a。

    • 如果t2表的分布列是t2.b，會出現t2表的重分布。 
      postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(10 rows)
    • 如果t2表的分布列是t2.a，則無需重分布就可以直接Join。 
      postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

    最佳化關聯（JOIN）列的資料類型

    Join的條件列資料類型應一致，避免因隱式/顯式資料類型轉換帶來資料需要重分布的問題。

    • 顯式資料類型轉換

      顯式資料類型轉換是指在SQL語句中，對Join條件列的資料類型進行強制類型轉換。比如表t的a列是int類型，但是在Join條件中將其轉換為numeric類型（即有t.a::numeric的轉換）。

      資料進行類型轉換後，其hash函數/hash值會發生變化，SQL語句中應盡量避免在Join條件列上進行類型轉換。

      下面例子表明在資料類型轉換後，會導致資料的重分布：

      --無資料類型轉換
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

--有強制資料類型轉換
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a::numeric;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a::numeric = t2.a::numeric
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.a::numeric
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a::numeric
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)
    • 隱式資料類型轉換

      隱式資料類型轉換是指在Join條件兩邊資料類型不完全一致，會導致資料庫對其中一列的資料類型進行轉換。

      資料庫對其中一列進行資料類型轉換後，可能會引發未經處理資料類型與新資料類型的Hash函數/Hash值不一致，需要進行重分布。所以，在表設計階段，參與Join的兩個表，Join條件的類型應儘可能統一，避免因為資料類型不同而導致需要額外重分布資料的問題。

      下面的例子中，t1.a為timestamp without time zone類型，t2.a為timestamp with time zone類型，他們的Hash函數不一致，在Join時需要進行重分布後再進行Join。

      postgres=# CREATE TABLE t1 (a timestamp without time zone);
CREATE TABLE
postgres=# CREATE TABLE t2 (a timestamp with time zone);
CREATE TABLE
postgres=#
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.04..0.11 rows=4 width=16)
   ->  Nested Loop  (cost=0.04..0.11 rows=2 width=16)
         Join Filter: t1.a = t2.a
         ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=8)
         ->  Materialize  (cost=0.04..0.07 rows=1 width=8)
               ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.04 rows=1 width=8)
                     ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=8)
(7 rows)

    資料扭曲的檢查和處理

    如果出現查詢異常緩慢，或者資源使用率不均勻的情況，則需要確認是否出現了資料扭曲。

    可以通過如下方式檢查資料是否發生傾斜：檢查某個表在各個節點上的資料分布計數，如果各節點上的資料分布明顯不均勻，則需要對該表的分布鍵進行調整。

    postgres=# SELECT gp_segment_id, count(1) FROM t1 GROUP BY 1 ORDER BY 2 DESC;
 gp_segment_id | count
---------------+-------
             0 | 16415
             2 |    37
             1 |    32
(3 rows)

    如果發生傾斜，建議重新合理定義分布鍵，更改分布鍵有兩種方式：

    • 重建立表：建立表時調整分布鍵。
    • 直接更改表的分布鍵：ALTER TABLE t1 SET DISTRIBUTED BY (b);

    查看正在啟動並執行語句狀態

    資料庫中正在並發執行的語句過多，會導致系統資源不足，查詢執行緩慢。

    通過pg_stat_activity視圖查看資料庫的健全狀態，該視圖將列出系統中所有正在並發執行的語句，可以通過觀察query_start欄位（該查詢的執行開始時間）來判斷某查詢是否有執行時間長度上的異常。

    例如：

    postgres=# SELECT * FROM pg_stat_activity;
 datid | datname  | procpid | sess_id | usesysid |  usename   |          current_query          | waiting |          query_start          |         backend_start         | client_addr | client_port | application_name |          xact_start           | waiting_reason
-------+----------+---------+---------+----------+------------+---------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+------------------+-------------------------------+----------------
 10902 | postgres |   53666 |       7 |       10 | yineng.cyn | select * from pg_stat_activity; | f       | 2019-05-13 20:27:12.058656+08 | 2019-05-13 20:16:14.179612+08 |             |          -1 | psql             | 2019-05-13 20:27:12.058656+08 |
 10902 | postgres |   54158 |       9 |       10 | yineng.cyn | select * from t t1, t t2;       | f       | 2019-05-13 20:26:28.138641+08 | 2019-05-13 20:17:40.368623+08 |             |          -1 | psql             | 2019-05-13 20:26:28.138641+08 |
(2 rows)

    其中，比較關鍵的欄位為：

    • procpid：執行該查詢的Master進程號。
    • usename：執行該查詢的使用者名稱。
    • current_query：查詢文本。
    • waiting：查詢是否處於等待狀態。
    • query_start：查詢執行開始時間。
    • backend_start：執行該查詢的進程啟動時間。
    • xact_start：該查詢所在事務開始時間。
    • waiting_reason：查詢等待的原因。

    此外，可以通過在current_query列添加current_query != '<IDLE>'條件，查看正在啟動並執行SQL的資訊：

    SELECT * FROM pg_stat_activity WHERE current_query != '<IDLE>';

    查看耗時最長的5條語句：

    SELECT current_timestamp - query_start as runtime
, datname
, usename
, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>'
ORDER BY runtime DESC
LIMIT 5;

    判斷當前鎖的狀況

    如果資料庫中的對象被加鎖，並且長時間沒有釋放，則可能導致其他查詢一直處於等鎖狀態，會影響其他查詢的正常執行。通過如下語句查看資料庫中被鎖的表：

    SELECT pgl.locktype AS locktype
, pgl.database AS database
, pgc.relname AS relname
, pgl.relation AS relation
, pgl.transaction AS transaction
, pgl.pid AS pid
, pgl.mode AS mode
, pgl.granted AS granted
, pgsa.current_query AS query
FROM pg_locks pgl
JOIN pg_class pgc ON pgl.relation = pgc.oid
JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid
ORDER BY pgc.relname;

    如果檢查出某個查詢被hang住，其原因為在等鎖，可查看查詢涉及的表被加鎖的情況，如有必要可採用如下方式進行人工幹預：

    • 取消進行中的查詢，如果該pid中無查詢，為IDLE狀態，則執行不起作用。另外取消這個query需要花費一定的時間來做清理和事務的復原。 
      SELECT pg_cancel_backend(pid);
      說明 pg_cancel_backend對pg_stat_activity.current_query為<IDLE>狀態的session不起作用，可以用pg_terminate_backend來清理。
    • 中斷session，如果有未提交事務，也會被復原掉。 
      SELECT pg_terminate_backend(pid);

    使用 Nest Loop JOIN 提升效能

    在執行個體預設狀態下，AnalyticDB PostgreSQL沒有啟用Nested Loop JOIN（嵌套串連）。對於只涉及或返回少部分資料的查詢，效能可能不是最優的。

    例如下面的SQL語句：

    SELECT *
FROM t1 join t2 on t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' and t1.c2 < '230769549'
LIMIT 100;

    其特點是t1和t2表都比較大，t1上的選擇條件（t1.c2 >= '230769548' and t1.c2 < '23432442'）過濾了絕大多數資料記錄，且有LIMIT子句，所以查詢實際上只涉及總資料量中的一小部分。這種情況下，使用Nested Loop串連方式是較優的。

    要使用Nested Loop串連，需要執行一下SET命令，如下所示：

    show enable_nestloop ;
 enable_nestloop
-----------------
 off
SET enable_nestloop = on ;
show enable_nestloop ;
 enable_nestloop
-----------------
 on
explain SELECT * FROM t1 join t2 on t1.c1 = t2.c1 WHERE t1.c2 >= '230769548' and t1.c2 < '23432442' LIMIT 100;
                                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.26..16.31 rows=1 width=18608)
   ->  Nested Loop  (cost=0.26..16.31 rows=1 width=18608)
         ->  Index Scan using t1 on c2  (cost=0.12..8.14 rows=1 width=12026)
               Filter: ((c2 >= '230769548'::bpchar) AND (c2 < '230769549'::bpchar))
         ->  Index Scan using t2 on c1  (cost=0.14..8.15 rows=1 width=6582)
               Index Cond: ((c1)::text = (T1.c1)::text)

    可以發現，t1和t2兩張表是使用Nested Loop串連的，從而獲得了較優的效能。