PostgreSQL 禁用全表掃描的實現

PostgreSQL可以通過一些設置來禁用全表掃描(FULL SCAN/Seq Scan)

註意:

設置此功能後不是完全避免全表掃描,而是隻要有不通過全表掃描能得出結果的就不走全表掃描。

如果什麼路都不通,那肯定得全表掃描,不然怎麼獲取數據。

而且並不是不走全表掃描性能就一定好。

下面展示下這個功能:

查詢表結構:

highgo=# \d test
    Table test
 Column |    Type    | Modifiers 
-------------+--------------------------------+-----------
 G   | character varying(50)   | 
 A   | character varying(12)   | 
 M   | timestamp(0) without time zone | 
 W   | character varying(5)   | 
Indexes:
 "s__x0" btree ("G", "A", "M", "W")

先檢查視圖:

highgo=# select * from pg_db_role_setting ;
 setdatabase | setrole | setconfig 
-------------+---------+-----------
(0 rows)

查詢執行計劃:

highgo=# explain select "G","Z" from test where "G"='PG';
         QUERY PLAN         
------------------------------------------------------------------------------
 Seq Scan on test (cost=0.00..3.11 rows=1 width=72)
 Filter: (("G")::text = '7e'::text)
(2 rows)

對用戶進行限制:

highgo=# alter role xyh set enable_seqscan =off;
ALTER ROLE
 
highgo=# select * from pg_db_role_setting ;
 setdatabase | setrole |  setconfig  
-------------+---------+----------------------
   0 | 26171 | {enable_seqscan=off}

再次查詢執行計劃:

highgo=# explain select "G","Z" from test where "G"='7e';
         QUERY PLAN         
------------------------------------------------------------------------------
 Index Scan using "s__x0" on test (cost=0.14..8.15 rows=1 width=72)
 Index Cond: (("G")::text = '7e'::text)
(2 rows)

補充:psql 會引起全表掃描的10種sql語句

1、模糊查詢效率很低:

原因:like本身效率就比較低,應該盡量避免查詢條件使用like;對於like ‘%…%’(全模糊)這樣的條件,是無法使用索引的,全表掃描自然效率很低;另外,由於匹配算法的關系,模糊查詢的字段長度越大,模糊查詢效率越低。

解決辦法:首先盡量避免模糊查詢,如果因為業務需要一定要使用模糊查詢,則至少保證不要使用全模糊查詢,對於右模糊查詢,即like ‘…%’,是會使用索引的;左模糊like

‘%…’無法直接使用索引,但可以利用reverse + function index 的形式,變化成 like ‘…%’;全模糊是無法優化的,一定要的話考慮用搜索引擎。出於降低數據庫服務器的負載考慮,盡可能地減少數據庫模糊查詢。

2、查詢條件中含有is null的select語句執行慢

原因:Oracle 9i中,查詢字段is null時單索引失效,引起全表掃描。

解決方法:SQL語法中使用NULL會有很多麻煩,最好索引列都是NOT NULL的;對於is null,可以建立組合索引,nvl(字段,0),對表和索引analyse後,is null查詢時可以重新啟用索引查找,但是效率還不是值得肯定;is not null 時永遠不會使用索引。一般數據量大的表不要用is null查詢。

3、查詢條件中使用瞭不等於操作符(<>、!=)的select語句執行慢

原因:SQL中,不等於操作符會限制索引,引起全表掃描,即使比較的字段上有索引

解決方法:通過把不等於操作符改成or,可以使用索引,避免全表掃描。例如,把column<>’aaa’,改成column<‘aaa’ or column>’aaa’,就可以使用索引瞭。

4、使用組合索引

如果查詢條件中沒有前導列,那麼索引不起作用,會引起全表掃描;但是從Oracle9i開始,引入瞭索引跳躍式掃描的特性,可以允許優化器使用組合索引,即便索引的前導列沒有出現在WHERE子句中。

例如:

create index skip1 on emp5(job,empno); 

全索引掃描

select count(*) from emp5 where empno=7900; 

索引跳躍式掃描

select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900; 

前一種是全表掃描,後一種則會使用組合索引。

5、or語句使用不當會引起全表掃描

原因:where子句中比較的兩個條件,一個有索引,一個沒索引,使用or則會引起全表掃描。例如:where A=:1 or B=:2,A上有索引,B上沒索引,則比較B=:2時會重新開始全表掃描。

6、組合索引

排序時應按照組合索引中各列的順序進行排序,即使索引中隻有一個列是要排序的,否則排序性能會比較差。

例如:

create index skip1 on emp5(job,empno,date); 
select job,empno from emp5 where job='manager'and empno='10' order by job,empno,date desc; 

實際上隻是查詢出符合job=’manager’and empno=’10’條件的記錄並按date降序排列,但是寫成order by date desc性能較差。

7、Update 語句

如果隻更改1、2個字段,不要Update全部字段,否則頻繁調用會引起明顯的性能消耗,同時帶來大量日志。

8、對於多張大數據量

(這裡幾百條就算大瞭)的表JOIN,要先分頁再JOIN,否則邏輯讀會很高,性能很差。

9、select count(*) from table;

這樣不帶任何條件的count會引起全表掃描,並且沒有任何業務意義,是一定要杜絕的。

10、sql的where條件要綁定變量

比如where column=:1,不要寫成where column=‘aaa’,這樣會導致每次執行時都會重新分析,浪費CPU和內存資源。

以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方,望不吝賜教。