2021年8月3日 星期二

Postgres SQL 執行效能調教 - Explain 執行計劃與多欄位 Index 使用

在使用資料庫的時候都會面對到 SQL 執行速度不如預期的問題，有時候可能單純的 SQL 語法卻要花上好幾秒才能完成查詢，下面是一般常見幾種可能影響查詢速度的因素，此處主要會針對前兩者進行討論。

1. 正確的建立 Index

2. 對 Table 進行 Analyze 更新統計值

3. SQL 語法 優化



在各個不同資料庫調教 SQL 的方式基本大同小異，而在 PGSQL 中使用到 Explain 語法來查看執行計畫，下面會透過觀察執行計畫來觀察不同 Index 狀況對執行計畫的影響。

1. 多欄位Index使用

建立多欄位 Index 時需要注意，欄位的順序對於 Index 的使用是有差的，假設此處建立了一個 Index 依序包含 a, b, c 欄位，但在執行查詢的時候下的條件判斷沒有使用到首欄位，例如 select * from b = 500 因為需要先掃過整個 a 欄位才能掃 b 欄位，執行計畫可能會判斷使用 Index 成本較高，因此偏好使用 Seq Scan，在正確使用上必須要單獨幫 b 欄位另外補一個 Index 才能加速查詢。

下面建立一張測試表並產生測試資料來供後續測試使用，並且為此測試表建立 Index 欄位順序為 a, b, c

create table test_index (a int, b int, c int);
create index on test_index (a, b, c);
insert into test_index select generate_series(1, 10000000), generate_series(1, 10000000), generate_series(1, 10000000);

下面分別以 a 與 b 為條件做篩選，可以觀察到用 a 欄位作條件判斷有正確使用到 Index 但是使用到 b 欄位則沒有使用 Index。

edb=# explain select * from test_index where a = 5000;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Only Scan using test_index_a_b_c_idx on test_index  (cost=0.43..8.45 rows=1 width=12)
   Index Cond: (a = 5000)
(2 rows)
edb=# explain select * from test_index where b = 5000;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Gather  (cost=1000.00..107139.34 rows=1 width=12)
   Workers Planned: 2
   ->  Parallel Seq Scan on test_index  (cost=0.00..106139.24 rows=1 width=12)
         Filter: (b = 5000)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)
edb=#

此外需要了解到，使用 Index 不必然為最佳解，有時候儘管有建立 Index 的資料庫選擇不使用有可能是正常的，因為資料分布狀況的關係幾乎要全表掃，因此直接使用 Seq Scan 反而會比多去檢查 Index 來的有效率。

本處測試資料有 10000000 筆資料，下面可以看到兩種不同的情境下，同樣使用到 a 欄位，但兩者實際掃的資料量有差異，因此選擇了不同方法來處理。
edb=# explain select * from test_index where a > 500;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Seq Scan on test_index  (cost=0.00..179057.19 rows=9999673 width=12)
   Filter: (a > 500)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(5 rows)

edb=# explain select * from test_index where a < 500;
                                           QUERY PLAN
------------------------------------------------------------------
 Index Only Scan using test_index_a_b_c_idx on test_index  (cost=0.43..893.83 rows=501 width=12)
   Index Cond: (a < 500)
(2 rows)
edb=#

2. Analyze Table

在效能調教上統計值有著巨大的影響，如果執行計畫產生器對於資料分布的狀況不了解，無法正確判讀可能的查詢結果，有可能會造成應該使用 Index 的地方卻選擇了 Seq Scan 導致執行查詢結果較慢。

以下產生測試資料

create table test_a (id text , str text, primary key(id, str));
create table test_b (id text primary key, str text);
insert into test_a select generate_series(1, 10000), 'test_a';
insert into test_b select generate_series(1, 10000), 'test_b';
update test_b SET str = 'test_c' where id = 5000;

下面針對 join SQL 進行執行計畫的測試，由於 where 條件判斷預期查詢結果應該只有一筆資料，期望可以透過 Index Scan 來進行快速的查詢，但實際上發現都使用 Seq Scan。

analyze select * from test_a left outer join test_b on test_a.id = test_b.id where test_b.str = 'test_c';

                                                   QUERY PLAN
----------------------------------------------------------------------------------------------
 Hash Join  (cost=115.80..231.92 rows=24 width=128) (actual time=6.963..9.652 rows=1 loops=1)
   Hash Cond: (test_a.id = test_b.id)
   ->  Seq Scan on test_a  (cost=0.00..103.40 rows=4840 width=64) (actual time=0.023..2.779 ro
ws=10000 loops=1)
   ->  Hash  (cost=115.50..115.50 rows=24 width=64) (actual time=4.141..4.143 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on test_b  (cost=0.00..115.50 rows=24 width=64) (actual time=4.135..4.13
6 rows=1 loops=1)
               Filter: (str = 'test_c'::text)
               Rows Removed by Filter: 9999
 Planning Time: 0.131 ms
 Execution Time: 9.675 ms
(10 rows)
接下來針對兩張使用到的表格進行 analyze 後再次查看執行計畫，此時可以看到 test_a 表正確的使用到了我們所預期使用的 Index，
edb=# analyze test_a;
ANALYZE
edb=# analyze test_b;
ANALYZE
edb=# explain analyze select * from test_a left outer join test_b on test_a.id = test_b.id where test_b.str = 'test_c';
                                                        QUERY PLAN

----------------------------------------------------------------------------------------------
-----------------------------
 Nested Loop  (cost=0.29..188.30 rows=1 width=22) (actual time=3.402..3.404 rows=1 loops=1)
   ->  Seq Scan on test_b  (cost=0.00..180.00 rows=1 width=11) (actual time=3.381..3.382 rows=
1 loops=1)
         Filter: (str = 'test_c'::text)
         Rows Removed by Filter: 9999
   ->  Index Scan using test_a_pkey on test_a  (cost=0.29..8.30 rows=1 width=11) (actual time=
0.015..0.015 rows=1 loops=1)
         Index Cond: (id = test_b.id)
 Planning Time: 0.395 ms
 Execution Time: 3.425 ms
(8 rows)

Time: 4.279 ms
edb=#

藉由上面的測試，我們可以觀察到了解到 Index 以及統計值 Analyze 對執行計畫的影響，在調教 SQL 的時候，除了調整 SQL 語法以外，別忘了優先檢查相關 Index 的使用以及收集統計值。

on
標籤： ,