修改一行SQL程式碼 效能提升了100倍

　　在PostgreSQL中修改了一行不明顯的程式碼，把（ANY(ARRAY[...]) 改成 ANY(VALUES(...))），結果查詢時間從20s變為0.2s。最初我們學習使用 EXPLAN ANALYZE來優化程式碼，到後來，Postgres社群也成為我們學習提升的一個好幫手，付出總會有回報，我們的效能也因此得到了極大的提升。

　　事出有因

　　Datadog是專門為IT、開發團隊等提供監控服務的。這周早些時候，我們的許多資料庫所面臨的一個效能問題是在一個較小的表上進行大量的key查詢。這些查詢中的99.9%都是高效靈活的。在極少數例項中，有些數量的效能指標tag查詢是費時的，這些查詢需要花費20s時間。這也就意味著使用者需要在瀏覽器面前花費這麼長的時間來等待圖形編輯器做出響應。即使是0.1%，這樣的使用者體驗也顯然糟透了，對此，我們進行了監測，探究為何速度會這麼慢。

　　查詢與計劃

　　結果令人震驚，罪魁禍首竟然是下面這個簡單的查詢：

SELECT c.key,
       c.x_key,
       c.tags,
       x.name
 FROM context c
 JOIN x
   ON c.x_key = x.key
WHERE c.key = ANY (ARRAY[15368196, -- 11,000 other keys --)])
  AND c.x_key = 1
  AND c.tags @> ARRAY[E'blah'];

　　X表擁有上千行資料，C表擁有1500萬行資料，這兩個表的“key”列都帶有適當的索引主鍵。簡單地說，它就是一個簡單的主鍵查詢。但有趣地是，隨著key列中記錄的增加，例如在11000行時，我們通過新增EXPLAIN (ANALYZE, BUFFERS)字首來檢視key列的值是否與陣列中的值匹配：

Nested Loop  (cost=6923.33..11770.59 rows=1 width=362) (actual time=17128.188..22109.283 rows=10858 loops=1)
  Buffers: shared hit=83494
  ->  Bitmap Heap Scan on context c  (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
        Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_key = 1))
        Filter: (key = ANY ('{15368196,(a lot more keys here)}'::integer[]))
        Buffers: shared hit=50919
        ->  BitmapAnd  (cost=6923.33..6923.33 rows=269 width=0) (actual time=132.910..132.910 rows=0 loops=1)
              Buffers: shared hit=1342
              ->  Bitmap Index Scan on context_tags_idx  (cost=0.00..1149.61 rows=15891 width=0) (actual time=64.614..64.614 rows=264777 loops=1)
                    Index Cond: (tags @> '{blah}'::text[])
                    Buffers: shared hit=401
              ->  Bitmap Index Scan on context_x_id_source_type_id_idx  (cost=0.00..5773.47 rows=268667 width=0) (actual time=54.648..54.648 rows=267659 loops=1)
                    Index Cond: (x_id = 1)
                    Buffers: shared hit=941
  ->  Index Scan using x_pkey on x  (cost=0.00..8.27 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=10858)
        Index Cond: (x.key = 1)
        Buffers: shared hit=32575
Total runtime: 22117.417 ms

　　這次查詢共花費22s，我們可以通過下圖對這22s進行很直觀的瞭解，其中大部分時間花費在Postgres和OS之間，而磁碟I/O則花費非常少的時間。

　　在最低水平，這些查詢看起來就像是這些CPU利用率的峰值。在這裡主要是想證實一個關鍵點：資料庫不會等待磁碟去讀取資料，而是做排序、雜湊和行比較這些事。

　　通過Postgres獲取與峰值最接近的行數。

　　顯然，我們的查詢在大多數情況下都有條不紊的執行著。

　　Postgres的效能問題：點陣圖堆掃描

　　rows_fetched度量與下面的部分計劃是一致的：

Buffers: shared hit=83494
  ->  Bitmap Heap Scan on context c  (cost=6923.33..11762.31 rows=1 width=329) (actual time=17128.121..22031.783 rows=10858 loops=1)
        Recheck Cond: ((tags @> '{blah}'::text[]) AND (x_key = 1))
        Filter: (key = ANY ('{15368196,(a lot more keys here)}'::integer[]))
        Buffers: shared hit=50919

　　Postgres使用點陣圖堆掃描（ Bitmap Heap Scan）來讀取C表資料。當關鍵字的數量較少時，它可以在記憶體中非常高效地使用索引構建點陣圖。如果點陣圖太大，查詢優化器會改變其查詢資料的方式。在我們這個案例中，需要檢查大量的關鍵字，所以它使用了非常相似的方法來檢查候選行並且單獨檢查與x_key和tag相匹配的每一行。而所有的這些“在記憶體中載入”和“檢查每一行”都需要花費大量的時間。

　　幸運的是，我們的表有30%都是裝載在RAM中，所以在從磁碟上檢查行的時候，它不會表現的太糟糕。但在效能上，它仍然存在非常明顯的影響。查詢過於簡單，這是一個非常簡單的key查詢，所以沒有顯而易見的資料庫或應用重構，它很難找到一些簡單的方式來解決這個問題。最後，我們使用 PGSQL-Performance郵件向社群求助。

　　解決方案

　　開源幫了我們，經驗豐富的且程式碼貢獻量非常多的Tom Lane讓我們試試這個：

SELECT c.key,
       c.x_key,
       c.tags,
       x.name
 FROM context c
 JOIN x
   ON c.x_key = x.key
WHERE c.key = ANY (VALUES (15368196), -- 11,000 other keys --)
  AND c.x_key = 1
  AND c.tags @> ARRAY[E'blah'];

　　你能發現有啥不同之處嗎？把ARRAY換成了VALUES。

　　我們使用ARRAY[...]列舉出所有的關鍵字來進行查詢，但卻欺騙了查詢優化器。Values(...)讓優化器充分使用關鍵字索引。僅僅是一行程式碼的改變，並且沒有產生任何語義的改變。

　　下面是新查詢語句的寫法，差別就在於第三和第十四行。

Nested Loop  (cost=168.22..2116.29 rows=148 width=362) (actual time=22.134..256.531 rows=10858 loops=1)
  Buffers: shared hit=44967
  ->  Index Scan using x_pkey on x  (cost=0.00..8.27 rows=1 width=37) (actual time=0.071..0.073 rows=1 loops=1)
        Index Cond: (id = 1)
        Buffers: shared hit=4
  ->  Nested Loop  (cost=168.22..2106.54 rows=148 width=329) (actual time=22.060..242.406 rows=10858 loops=1)
        Buffers: shared hit=44963
        ->  HashAggregate  (cost=168.22..170.22 rows=200 width=4) (actual time=21.529..32.820 rows=11215 loops=1)
              ->  Values Scan on "*VALUES*"  (cost=0.00..140.19 rows=11215 width=4) (actual time=0.005..9.527 rows=11215 loops=1)
        ->  Index Scan using context_pkey on context c  (cost=0.00..9.67 rows=1 width=329) (actual time=0.015..0.016 rows=1 loops=11215)
              Index Cond: (c.key = "*VALUES*".column1)
              Filter: ((c.tags @> '{blah}'::text[]) AND (c.x_id = 1))
              Buffers: shared hit=44963
Total runtime: 263.639 ms

　　從22000ms到200ms，僅僅修改了一行程式碼，速度提升了100倍還多。

　　產品裡新的查詢

　　部署後的程式碼：

　　資料庫看起來更美觀

　　Postgres慢查詢將一去不復返了。但有誰願意因為這個0.1%的倒黴蛋再去折磨呢？我們使用Datadog來驗證修改是否正確，它能夠做出即時驗證。如果你想檢視Postgres查詢速度的各種影響， 不妨試試Datadog吧。

　　英文來源： Datadog