PostgreSQL技術大講堂 - 第31講：SQL調優技巧

PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色許可權、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

第31講：SQL調優技巧

第31講預告：10月28日(週六)19:30-20:30，釘釘群直播，群號：35822460

內容1 : SQL調優正規化

內容2 : 多表查詢調優技巧

內容3 : 多表查詢應用案例

開發正規化一

· 不要輕易把欄位嵌入到表示式

在sal列上有索引，但是條件語句中把sal列放在了表示式當中，導致索引被壓抑，因為索引裡面儲存的是sal列的值，而不是sal加上100以後的值。

testdb=# explain select * from emp2 where sal + 100 = 2000;

QUERY PLAN

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

Gather (cost=1000.00..7796.60 rows=2294 width=36)

Workers Planned: 2

-> Parallel Seq Scan on emp2 (cost=0.00..6567.20 rows=956 width=36)

Filter: ((sal + 100) = 2000)

(4 rows)

· 改寫成

透過等式等換，把sal列從表示式中剝離出來，就會用到索引。

testdb=# explain select * from emp2 where sal = 2000 - 100;

QUERY PLAN

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

Index Scan using emp2_sal_ind on emp2 (cost=0.42..8.44 rows=1 width=36)

Index Cond: (sal = 1900)

(2 rows)

開發正規化二

· 不要輕易把欄位嵌入到函式中

在hiredate列上有索引，但是條件語句中把該列放在了函式當中，導致索引被壓抑，因為索引裡面儲存的是該列的值，而不是函式處理以後的值。

testdb=# explain select * from emp2 where to_char(hiredate,'dd-mm-yyyy')='22-05-2022';

QUERY PLAN

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

Seq Scan on emp2 (cost=0.00..289.32 rows=50 width=62)

Filter: (to_char((hiredate)::timestamp with time zone, 'dd-mm-yyyy'::text) = '22-05-2022'::text)

· 改寫成

透過等式轉換，把列從函式中剝離出來，就會用到索引，比較成本，差別很大。

testdb=# explain select * from emp2 where hiredate=to_date('22-05-2022','dd-mm-yyyy');

QUERY PLAN

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

Index Scan using emp2_hiredate on emp2 (cost=0.29..8.30 rows=1 width=62)

Index Cond: (hiredate = to_date('22-05-2022'::text, 'dd-mm-yyyy'::text))

開發正規化三

· 如果查詢中比較固定查詢某些列，可以基於這幾個列建複合索引，直接查詢索引，避開回表掃描。

create index emp2_empno on emp2 (empno,sal);

testdb=# explain select empno,sal from emp2 where empno=7788;

QUERY PLAN

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

Index Only Scan using emp2_empno on emp2 (cost=0.29..10.09 rows=2 width=8)

Index Cond: (empno = 7788)

多表查詢指導方針

· OLTP應用SQL調優指導方針

-- 驅動表上有很好的條件限制，同時，驅動表上的限制性條件欄位上應該有索引，包括主鍵、v一索引或其它索引、複合索引等。

-- 在每次連線操作之後儘量保證返回記錄數最少，傳遞給下一個連線操作。

-- 根據返回的行的數量對應正確的連線方式。

-- 儘量透過在被驅動表的連線欄位上的索引，訪問被驅動表。

-- 單表掃描應該有效率，如果被驅動表上還有其它限制條件，可以遵循複合索引建立原則，建立合適的複合索引(連線欄位與條件欄位)。

-- 全表掃描也許是合理的，例如若干小表、程式碼表的訪問。

-- 依次類推，順序完成所有表的連線操作。

· 多表連線調優總體思路

>> 如果是OLTP應用，則最佳化的思路是由小到大，即從限制性最強，返回記錄最少的連線開始，依次完成其它表的連線，並在訪問每張表時，合理使用索引，特別是複合索引技術。

>> 如果是OLAP應用，則最佳化思路基本是hash連線加並行處理，表連線順序不是最主要的。

· 多表連線最佳化案例一

testdb=# explain select e.*,d.*

from emp e,dept d

where d.deptno=e.deptno

and e.empno=7499;

QUERY PLAN

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

Nested Loop (cost=0.30..16.36 rows=1 width=192)

-> Index Scan using pk_emp on emp e (cost=0.15..8.17 rows=1 width=98)

Index Cond: (empno = 7499)

-> Index Scan using pk_dept on dept d (cost=0.15..8.17 rows=1 width=94)

Index Cond: (deptno = e.deptno)

執行計劃解讀：

1、先按照建立在empno欄位上的索引去emp表查詢empno為7499的員工資訊。

2、再根據7499所在的部門號(deptno)去dept表查詢該部門的詳細資訊，而且dept表的deptno欄位上應該有索引。

3、最後使用巢狀迴圈連線方式處理資料。

建議：

“如果是多表連線sql語句，注意驅動表的連線欄位是否需要建立索引”。

在上例中，被驅動表是dept，dept表的連線欄位是deptno，而emp的deptno欄位是可以不需要建索引的，因為已經根據條件欄位上列訪問驅動表。

· 多表連線最佳化案例二

testdb=# explain select e.*,d.*

from emp e,dept d

where d.deptno=e.deptno

and e.empno=7499

and d.dname='DALLAS';

QUERY PLAN

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

Nested Loop (cost=0.30..20.35 rows=1 width=192)

-> Index Scan using pk_emp on emp e (cost=0.15..8.17 rows=1 width=98)

Index Cond: (empno = 7499)

-> Index Scan using pk_dept on dept d (cost=0.15..8.17 rows=1 width=94)

Index Cond: (deptno = e.deptno)

Filter: ((dname)::text = 'DALLAS'::text)

執行計劃解讀：

1、先按照建立在empno欄位上的索引去emp表查詢empno為7499的員工資訊。

2、再根據7499所在的部門號(deptno)去dept表查詢該部門的詳細資訊。此時dept表還有一個條件欄位loc=‘DALLAS’，因此可考慮按(deptno,loc)複合索引方式去查詢dept表，效率更高，即可建立(deptno,loc)欄位上的複合索引(idx_dept_2)。

3、最後以巢狀迴圈的連線方式處理資料。

建議：

“如果是多表連線sql語句，注意是否可以在被驅動表的連線欄位與該表的其它約束條件欄位上建立複合索引”。索引可以在dept表上建立(deptno與dname)欄位的複合索引。

執行計劃解讀(續)

應該遵循關於複合索引建立時的建議：

“如果單個欄位是主鍵或者v一欄位，或者可選性非常高的欄位，儘管約束條件欄位比較固定，也不一定要建成複合索引，可建成單欄位索引，降低複合索引開銷”。

*而且透過比較發現這種情況建立單列索引比建立複合索引查詢的時候代價要低的多。所以在本例中，不應該建立複合索引。

多表查詢應用案例

· 5張查詢應用案例

SELECT emp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address,emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name

from hr.employees emp,hr.employees mgr,hr.departments d,hr.locations l,hr.jobs j

where l.city='South San Francisco'

and emp.manager_id=mgr.employee_id

and emp.department_id=d.department_id

and d.location_id=l.location_id

and emp.job_id=j.job_id;

· 第一種情況：無索引

在沒有任何索引的情況下檢視其執行計劃 ，由於沒有索引，所以所有掃描方式均為全表掃描，連線方式為hash join。

· 第二種情況：建立單列索引

在locations的city、location_id列上建立索引。

在departments的location_id上建立索引

在departments的department_id上建立主鍵約束

在employees的employee_id上建立主鍵約束

在jobs的job_id上建立主鍵約束。

· 第三種情況：建立複合索引

在locations的city、location_id列上建立複合索引。

在departments的department_id 、location_id上建立複合索引

在employees的employee_id、 department_id、manager_id、job_id上建立複合索引(或者單列索引)

在jobs的job_id上建立主鍵約束。

· 三種執行計劃成本對比

經過分析發現，如果連線方式能夠走巢狀迴圈，那麼其成本比其它連線方式都低，當然我們要提供條件讓最佳化器自動選擇成本最低的連線方式，只要有一張表的訪問方式是索引掃描，那麼連線方式一般會選擇巢狀迴圈。

Employees表的複合索引在執行計劃中起到了作用，或者選擇在連線條件列上( employee_id,department_id,manager_id )建立單列索引。

Departments和locations表的記錄比較少，即使建立了單列或者多列索引，都不會使用索引。

連線順序是L->D->EMP-MGR-J