Postgres SQL sort 操做性能調優
這篇文章將以實戰的方式結合筆者在項目中真實遇到的狀況來說解。說到SQL，你們可能會遇到一些寫法稍微複雜的寫法。好比SQL中遇到的有聚合函數sum等，也有遇到使用group by / order by的狀況，其實這種狀況下SQL的性能可能不是特別好。至少有兩種大的解決思路：sql
減小SQL的壓力，把SQL實現的邏輯計算相關，用code的方式去解決。即釋放數據庫後端寶貴的計算資源。算是重構SQL，這種方式代價稍微要高些，由於要更改code的實現邏輯，也要規劃好新的SQL的結構。目的就是爲了在SQL中去掉order排序/group分組這類比較耗費資源的部分。數據庫
若是前提是咱們的SQL不能動，那麼要怎麼去優化呢？下面會一步步和你們解釋在postgres中怎麼調優order by SQL語句。後端
step1:app
在Postgres中建立測試表，建立表SQL以下：ide
CREATE TABLE public.sort_test ( id bigint NOT NULL, salary numeric NOT NULL, CONSTRAINT sort_test_pkey PRIMARY KEY (id) ) TABLESPACE pg_default;
step2: 函數
在測試表中插入多條數據，以下SQL,能夠往上表中插入500萬條數據。oop
insert into sort_test select generate_series(1,5000000),generate_series(1,5000000);
step3:
post
評估order by 性能問題，假設要評估select語句以下，SQL不是很複雜，能夠說明問題便可。性能
select * from sort_test order by salary;
step4:測試
分析SQL執行計劃，獲取執行計劃的SQL語句以下：
explain analyze select * from sort_test order by salary;
step5:
執行計劃以下（注：若是上面SQL語句沒有analyze關鍵字，那麼執行計劃就不會有Sort Method詳細信息和actual time的信息。
"Sort (cost=804270.42..816770.42 rows=5000000 width=14) (actual time=2688.920..3797.378 rows=5000000 loops=1)"" Sort Key: salary"" Sort Method: external merge Disk: 122344kB"" -> Seq Scan on sort_test (cost=0.00..77028.00 rows=5000000 width=14) (actual time=0.071..476.958 rows=5000000 loops=1)""Planning Time: 0.193 ms""Execution Time: 4038.509 ms"
從以上的執行計劃能夠看到最小的節點（上面"->"處）的執行時間是0.071 ms,而到上一層Sort，就會發現執行時間就變爲了2688.92 ms （注：這邊是以actual time做爲分析依據，你也能夠以cost時間，都是能夠的。
以上有個關鍵信息以下，這就說明此sql在執行的時候，postgres分配的work_mem的內存大小不夠，只能從disk處抓取數據處理。那麼從內存的角度來優化SQL，就須要增大work_mem參數值，上面說是用了大體122MB disk。而postgres默認的work_mem是 4MB。
Sort Method: external merge Disk: 122344kB
那麼修改work_mem,用下面方法，將work_mem大小設置爲1GB.
SET work_mem = '1GB';
step6:
修改以後，再獲取執行計劃以下：
"Sort (cost=633365.42..645865.42 rows=5000000 width=14) (actual time=1241.768..1526.102 rows=5000000 loops=1)"" Sort Key: salary"" Sort Method: quicksort Memory: 430984kB"" -> Seq Scan on sort_test (cost=0.00..77028.00 rows=5000000 width=14) (actual time=0.046..498.029 rows=5000000 loops=1)""Planning Time: 0.095 ms""Execution Time: 1775.462 ms"
此時，能夠看到關鍵字變爲以下所示，sort操做如今是放在了內存中執行的，用了430MB左右的內存，而後執行時間爲1241.768 ms，還不到原來執行時間的一半，性能仍是有大幅度提高的。
Sort Method: quicksort Memory: 430984kB"
舒適提示：work_mem在調整時，仍是要考慮實際狀況，好比我數據庫跑一些輕量級的sql比較多，那麼設置work_mem值過大的話，反而會影響性能,此時能夠小幅度地調整參數值，須要測試多輪，獲得適合本身產品的最優解。若是都是哪一種OLAP,SQL比較重的狀況下，能夠調整的幅度大一些，如從4MB調整到1GB.
你們也能夠掃描並關注以下公衆號「TimTest」，會有更多性能測試相關內容分享。
