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

第31講：SQL調優技巧

第31講：10月28日(周六)19:30-20:30，往期文檔及視頻，聯系CUUG

內容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調優指導方針

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

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

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

-- 盡量通過在被驅動表的連接字段上的索引，訪問被驅動表。

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

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

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

· 多表連接調優總體思路

>> 如果是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)字段的復合索引。

執行計劃解讀(續)

應該遵循關于復合索引創建時的建議：

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

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

多表查詢應用案例

· 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