PG資料庫SQL最佳化小技巧

S QL 最佳化是資料庫最佳化中十分重要的手段，也是見效最快的手段。有時候新增一個索引，修改一條S QL 語句能夠把一個即將崩潰的資料庫系統挽救回來。S QL 最佳化的手段很多，大家比較常用的是新增索引、調整表連線方式、改寫等價S QL 等方法。

實際上POSTGRESQL資料庫的S QL 最佳化技巧大多數是和其他資料庫，比如Oracle是類似的，因為現代資料庫產品的核心技術就是C BO 最佳化器技術。POSTGRESQL在內的多種商用和開源資料庫產品的C BO 最佳化器也大量參考了Oracle的技術，Oracle也不斷的在吸收其他資料庫產品的C BO 技術。雖然在具體技術實現上存在一定的差異，不過S QL 執行的基本技術路線是趨同的。

因此在做POSTGRESQL的S QL 最佳化的時候，儘可以借鑑其他資料庫的S QL 最佳化經驗。今天的這個話題中，我們不會討論通用資料庫技術的一些S QL 最佳化技巧，而是結合POSTGRESQL資料庫的特點來討論一些S QL 最佳化的實用技巧。這些技巧可能很多都是總體性的，並不能直接應用於你的S QL 之中，不過這些技巧如果應用得當，會讓你的POSTGRESQL資料庫的S QL 效能得到極大的提升。

 

技巧一 ： 確保S QL 語句使用了合適的掃描方式。很多做S QL 最佳化的人認為只要表上做了Seq  Scan ，那麼這個執行計劃一定是有問題的，一定要讓S QL 採用Index   Scan才行。這其實也是一個誤區，在某些情況下，Index   Scan並不是最優的，POSTGRESQL的最佳化器會根據兩種掃描方式的成本來確定採用哪種掃描方式。這和表的資料量、掃描返回的元組的比例、儲存系統順序讀與離散讀的效能差異、作業系統的C ACHE 大小等都有關係。

在大多數情況下，最佳化器都可以找到好的掃描方式。不過在一些臨界狀態，Seq   Scan和Index   Scan的成本十分接近，此時很容易出現執行計劃偏差，發現這些偏差，並糾正執行計劃，對於S QL 效能最佳化十分有效。當缺乏適當的索引可用的時候，使用Parallel   Seq  Scan 替代Seq    Scan也是提高S QL 效能的十分重要的手段。

 

技巧二： 確保多表關聯使用了正確的連線方式。使用正確的連線方式，對於表連線的效能來說十分關鍵。如果你透過e xplain 發現某條 SQL 的c ost 很大，那麼首先你應該看看哪個地方存在較大的c ost ，如果c ost 集中在Join上，那麼你應該首先考慮是不是這條S QL 使用了不合理的表連線方式。有時候缺少必要的索引也是導致j oin 效能問題的主因，因此如果你發現了S QL 使用了錯誤的連線方式的時候，不要總是考慮強制指定連線方式，而是先考慮是不是可以透過索引來最佳化連線方式。

Nested   Loop   Join是最為常見的表連線方式，比較適合於外表結果集不大，內表於外表的連線條件有較好的索引的場景；Merge   Join適合於內表與外表結果集大小差異較小，連線鍵存在排序的場景；Hash   Join比較適合於內外表結果集大小存在一定差異，並且外表結果集比較大的場景，這種場景使用Nested   Loop   Join的效果較差。根據上述的場景分析，仔細分析你的S QL 的表連線方式，發現存在錯誤連線方式的執行計劃。一般來說如果表連線順序出現問題，大多數情況是因為表的統計資料出現錯誤，讓最佳化器產生了錯誤的執行計劃，透過更新統計資料大多數情況下可以解決問題。不過如果你的POSTGRESQL資料庫禁用了Hash   Join，也可能會讓最佳化器選擇Nested   Loop   Join或者Merge    Join替代Hash   Join。在某些情況下，如果你能夠確定某種表連線方式肯定是最優的，那麼你也可以透過強制指定的方式來讓S QL 使用某種表連線方式。

 

技巧三 ： 對於複雜的多表查詢，確保表連線的順序是最優的。必要時可透過 SQL 語法來固定表連線的順序，從而避免最佳化器產生錯誤的執行計劃。實際上，在很多情況下，軟體開發人員是知道資料之間的連線順序與關係的，而運維人員要知道這一切需要花費更多的時間。對於一些複雜的容易出現執行計劃錯誤的S QL 語句，可以使用顯式Join語句替代隱式關聯語句。並可以在會話級禁止S QL rewri te，從而確保執行計劃是按照業務邏輯去做關聯的。這種S QL 編寫方式也存在一定的風險，如果業務資料發生了較大的變化，那麼可能你所預先設定的連線順序是錯誤的，會引發新的效能問題。

 

技巧四 ： 儘可能不要編寫 select * from  這樣的語句。你需要查詢哪些欄位，就寫清除哪些欄位。這對於減少I O 以及在某些時候最佳化器可以選擇效能更好的 Index only scan 操作有幫助。

 

技巧五 ： 分頁查詢的效能。分頁查詢是應用系統中最為常用的，POSTGRESQL資料庫對於分頁查詢有很好的支援，採用下面的語法就可以輕鬆的寫出分頁查詢語句。

s elect … from … order by …  limit <n> offset <m>;

比如下面的一個語句：

select * from test_outer order by o_entry_d limit 50 offset 1000;

透過e xplain 我們來看看這條語句的成本：

=# explain (analyze)select * from test_outer order by o_entry_d limit 50 offset 1000;

                                                                    QUERY PLAN                                                                     

-------------------------------------------------------------------------------

 Limit  (cost=434939.44..434945.27 rows=50 width=36) (actual time=515.714..515.725 rows=50 loops=1)

   ->  Gather Merge  (cost=434822.76..1627087.20 rows=10218696 width=36) (actual time=515.529..516.793 rows=1050 loops=1)

         Workers Planned: 2

         Workers Launched: 2

         ->  Sort  (cost=433822.74..446596.11 rows=5109348 width=36) (actual time=511.974..512.016 rows=957 loops=3)

               Sort Key: o_entry_d

               Sort Method: top-N heapsort  Memory: 179kB

               Worker 0:  Sort Method: top-N heapsort  Memory: 188kB

               Worker 1:  Sort Method: top-N heapsort  Memory: 191kB

               ->  Parallel Seq Scan on test_outer  (cost=0.00..151884.48 rows=5109348 width=36) (actual time=0.003..227.158 rows=4087478 loops=3)

 Planning Time: 0.062 ms

 Execution Time: 516.873 ms

(12 rows)

在一個有1億2千多萬條記錄的表中做一個分頁查詢，從第1000條記錄開始讀取50條記錄，耗時516.873毫秒，執行的效率還是不錯的。下面我們再來看看，如果從第100完條記錄開始查詢50條的執行效果：

=# explain (analyze)select * from test_outer order by o_entry_d limit 50 offset 1000000;

                                                                    QUERY PLAN                                                                     

---------------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=998528.95..998534.78 rows=50 width=36) (actual time=1676.440..1676.451 rows=50 loops=1)

   ->  Gather Merge  (cost=881854.14..2074118.58 rows=10218696 width=36) (actual time=1465.918..1672.129 rows=1000050 loops=1)

         Workers Planned: 2

         Workers Launched: 2

         ->  Sort  (cost=880854.11..893627.48 rows=5109348 width=36) (actual time=1460.427..1491.615 rows=333850 loops=3)

               Sort Key: o_entry_d

               Sort Method: external merge  Disk: 197344kB

               Worker 0:  Sort Method: external merge  Disk: 197136kB

               Worker 1:  Sort Method: external merge  Disk: 193568kB

               ->  Parallel Seq Scan on test_outer  (cost=0.00..151884.48 rows=5109348 width=36) (actual time=0.003..222.965 rows=4087478 loops=3)

 Planning Time: 0.066 ms

 Execution Time: 1721.528 ms

(12 rows                                                                   

                                                                     

可以看到這條語句執行了1721.528毫秒，比上一條語句慢了不少。這也是分頁查詢語句的特點，讀取靠後的記錄所需要的時間比讀取考前的記錄要花費更多的時間。同樣的測試，如果o ffset  為1億，則執行時間變成了6秒多。對此我們如何去最佳化呢？

 

如果我們要讀取最後的n條資料，那麼如果使用降序排序，然後再掃描，效果要好得多。使用o rder by  o_entry_d   desc   limit 50 offset 0 這樣的語法，執行時間又變成500多毫秒了。關於分頁查詢的這個小技巧大家一定要記住。

 

技巧六 ： 分合理使用並行掃描。目前我們使用的X 86 伺服器的C PU 執行緒數很多，C PU 資源, IO 能力都大大提升了。因此當系統資源不存在瓶頸的前提下，並行查詢可以解決一些我們在透過其他最佳化方式無法解決的問題。

 

技巧七 ： 合理使用POSTGRESQL豐富的索引來進行S QL 最佳化。POSTGRESQL的索引十分豐富，針對一些特殊的S QL 語句，POSTGRESQL可以使用某些特殊的索引來進行最佳化。比如使用部分索引來減少索引維護的成本；使用覆蓋索引將Index   Scan最佳化為Index   Only   Scan，從而提升S QL 執行效率。在POSTGRESQL資料庫中甚至可以為某種特殊的應用場景建立特殊的索引來解決系統的效能問題。因此在POSTGRESQL的S QL 最佳化工作中，一定要認真研究使用適當的索引型別來最佳化某個比較難以解決的問題，這種工作思路是其他資料庫中所沒有的，對於解決複雜的S QL 效能問題十分有效。

 

技巧八 ： 確保統計資料為最新的。 POSTGRESQL 的C BO 最佳化器十分依賴於表的統計資料的準確性，如果你經常發現某條S QL 的執行計劃會出現錯誤，那麼首先你需要分析一下表上的統計數是否準確。POSTGRESQL資料庫雖然預設是開啟A UTOVACUUM 的，在A UTOVACUUM 操作中，會根據表資料的變化情況，自動進行表分析操作。不過有時候系統級的定義對於某些表來說不一定有效，同時因為一些其他的因素，有可能某張表並沒有及時的執行表分析操作。

=# select relname,last_analyze,last_autoanalyze frompg_stat_all_tables where last_autoanalyze is not null;

 relname  | last_analyze |       last_autoanalyze        

----------+--------------+-------------------------------

 pdrdb    |              | 2021-09-30 08:10:35.457991+08

 pdrsql   |              | 2021-09-30 07:10:33.380828+08

 pdrtable |              | 2021-09-30 08:10:35.521596+08

(3 rows)

如上面的查詢，我們可以透過pg_stat _all_tables 檢視去檢視某張表最後a utoanalyze 的時間，如果該時間過舊，而這張表的資料變化十分頻繁，那麼可能表分析資料是導致執行計劃錯誤的原因。

 

技巧九 ： 採用分割槽表。分割槽表往往是最佳化大資料量業務的利器，分割槽表可以讓每個表分割槽的資料量得到有效的控制，從而減少S eq  Scan 的成本，也可以降低表維護的成本。對於部分S QL 語句的效能最佳化也有一定的效果。根據業務的特點，選擇合適的表分割槽類別，可以有效的提高某些S QL 的效能。比如 HASH  分割槽可以有效地避免表資料的熱塊衝突，提升大併發量寫入的效能。與業務有關的時間範圍分割槽可以讓業務經常訪問的最新的分割槽的資料總量得到控制，從而提升業務系統S QL 的效能。同時按照時間的範圍分割槽還可以讓資料歸檔變得簡單。在設計表結構的時候，根據業務特點，對大表設計合理的表分割槽，對於應用系統長期的高效能執行十分關鍵。

 

技巧十 ： 定期歸檔歷史資料。幾乎所有的D BA 與開發人員都知道，資料量大了，效能就不容易保障了。確保應用系統的資料量不隨著系統上線的時間呈線性上升，是確保系統執行數年後仍能高效能執行的十分關鍵的工作。透過定期歸檔歷史資料可以實現這個目標。不過要想實現定期歸檔歷史資料，必須在系統設計建設階段就做好設計與規劃。應用系統可以正確的從歷史資料庫讀取歷史資料。

透過資料清理和資料歸檔的實施，可以提高資料庫效能，應用可以更加迅捷地為客戶提供優質服務，從而提高企業信譽、提升企業的核心競爭力。

Ø  內部業務利益

ü  提高系統效能

ü  更加充分地利用現有資源

ü  增強系統穩定性

ü  適應業務部門新的需求

Ø  降低運營成本

Ø  減少生產資料庫的規模

ü  縮短備份恢復時間

任何一個資料都有一定的生命週期，從資料產生（輸入或者被採集）到使用到過期。所不同的是資料的儲存週期不同，資料的儲存方式不同。一般來說，根據資料生命週期和保管方式不同，可以分為幾大類：

l  永 久線上資料：這類資料不需要歸檔，從系統上線開始一直線上。這類資料的特點是資料量不大（一般從幾十K到幾百M不等），資料較為靜態，變更量不大（比如引數資料）

l  週期性線上資料：這類資料往往在某個週期性事件中被採集往往具有很強的時間週期特性（比如月度週期），這些資料在採集後被集中處理，處理形成彙總或者帳務資料。資料處理後，在一定週期內還需要被查詢，一定時間週期後查詢量逐漸減少

l  工作流性質的資料：具有一定的實效性，根據流程的狀態判斷是否需要繼續一級線上，歸檔不僅僅取決於時間，還取決於某些條件

l  一級彙總資料：根據明細彙總的資料，可以在明細資料歸檔或者刪除後提供統計查詢。一般線上1年或者數年

l  二級彙總資料：在一級彙總資料基礎上彙總的資料，資料量較小，可以長期保留

l  臨時資料：臨時使用，使用後應該馬上清理的資料。不過在往往清理力度不足，導致長期線上

每個資料在其生命週期內，其儲存的方式也有多種形態， 根據其訪問的便利性，在系統建設階段可以進行生產庫、查詢庫、歷史庫、歸檔庫等的設計。比如某套生產系統，可以分為多個資料庫來存訪各種資料：

u  生產庫線上存放，當前資料：儲存在生產庫的當前資料表中

u  生產庫線上存放，歷史資料：儲存在生產庫的歷時資料表中

u  歷史庫線上存放：從生產庫中轉移到歷史庫，並在生產庫中刪除該資料

u  離線歸檔存放：存放在離線介質中（比如物件儲存）

 

 

技巧十一 ： 透過讀寫分離分離系統負載。如果POSTGRESQL資料庫系統的負載很高，伺服器資源存在不足的現象，而POSTGRESQL資料庫又沒有類似Oracle  RAC 的橫向擴充套件能力，如何解決這個問題呢？採用讀寫分離叢集來擴充套件POSTGRESQL的併發處理能力是一種常用的方法。對於絕大多數業務系統來說，資料庫伺服器的讀寫比例為8：2甚至9：1，大部分的系統負載只讀的S ELECT 語句產生的，因此如果能夠在系統設計之初就為讀寫分離做好設計。使用POSTGRESQL POOL 、P ATRONI 等技術構建POSTGRESQL資料庫的讀寫分離叢集，就可以大大提升POSTGRESQL資料庫的橫向擴充套件能力，讓POSTGRESQL資料庫在大型系統中發揮巨大的作用。