這篇文章主要介紹了PostgreSQL 禁用全表掃描的實(shí)現(xiàn)操作，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧。
PostgreSQL可以通過一些設(shè)置來禁用全表掃描（FULL SCAN/Seq Scan）
注意：
設(shè)置此功能后不是完全避免全表掃描，而是只要有不通過全表掃描能得出結(jié)果的就不走全表掃描。
如果什么路都不通，那肯定得全表掃描，不然怎么獲取數(shù)據(jù)。
而且并不是不走全表掃描性能就一定好。
下面展示下這個(gè)功能：
查詢表結(jié)構(gòu)：
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)
查詢執(zhí)行計(jì)劃：
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)
對用戶進(jìn)行限制：
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}
再次查詢執(zhí)行計(jì)劃：
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)
補(bǔ)充：psql 會引起全表掃描的10種sql語句
1、模糊查詢效率很低：
原因：like本身效率就比較低，應(yīng)該盡量避免查詢條件使用like；對于like ‘%...%'（全模糊）這樣的條件，是無法使用索引的，全表掃描自然效率很低；另外，由于匹配算法的關(guān)系，模糊查詢的字段長度越大，模糊查詢效率越低。
解決辦法：首先盡量避免模糊查詢，如果因?yàn)闃I(yè)務(wù)需要一定要使用模糊查詢，則至少保證不要使用全模糊查詢，對于右模糊查詢，即like ‘…%'，是會使用索引的；左模糊like
‘%...'無法直接使用索引，但可以利用reverse + function index 的形式，變化成 like ‘…%'；全模糊是無法優(yōu)化的，一定要的話考慮用搜索引擎。出于降低數(shù)據(jù)庫服務(wù)器的負(fù)載考慮，盡可能地減少數(shù)據(jù)庫模糊查詢。
2、查詢條件中含有is null的select語句執(zhí)行慢
原因：Oracle 9i中，查詢字段is null時(shí)單索引失效，引起全表掃描。
解決方法：SQL語法中使用NULL會有很多麻煩，最好索引列都是NOT NULL的；對于is null，可以建立組合索引，nvl(字段,0),對表和索引analyse后，is null查詢時(shí)可以重新啟用索引查找,但是效率還不是值得肯定；is not null 時(shí)永遠(yuǎn)不會使用索引。一般數(shù)據(jù)量大的表不要用is null查詢。
3、查詢條件中使用了不等于操作符（<>、!=）的select語句執(zhí)行慢
原因：SQL中，不等于操作符會限制索引，引起全表掃描，即使比較的字段上有索引
解決方法：通過把不等于操作符改成or，可以使用索引，避免全表掃描。例如，把column<>'aaa'，改成column<'aaa' or column>'aaa'，就可以使用索引了。
4、使用組合索引
如果查詢條件中沒有前導(dǎo)列，那么索引不起作用，會引起全表掃描；但是從Oracle9i開始，引入了索引跳躍式掃描的特性，可以允許優(yōu)化器使用組合索引，即便索引的前導(dǎo)列沒有出現(xiàn)在WHERE子句中。
例如：
1create index skip1 on emp5(job,empno);
全索引掃描
1select count(*) from emp5 where empno=7900;
索引跳躍式掃描
1select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900;
前一種是全表掃描，后一種則會使用組合索引。
5、or語句使用不當(dāng)會引起全表掃描
原因：where子句中比較的兩個(gè)條件，一個(gè)有索引，一個(gè)沒索引，使用or則會引起全表掃描。例如：where A=：1 or B=：2，A上有索引，B上沒索引，則比較B=：2時(shí)會重新開始全表掃描。
6、組合索引
排序時(shí)應(yīng)按照組合索引中各列的順序進(jìn)行排序，即使索引中只有一個(gè)列是要排序的，否則排序性能會比較差。
例如：
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;
實(shí)際上只是查詢出符合job='manager'and empno='10'條件的記錄并按date降序排列，但是寫成order by date desc性能較差。
7、Update 語句
如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調(diào)用會引起明顯的性能消耗，同時(shí)帶來大量日志。
8、對于多張大數(shù)據(jù)量
（這里幾百條就算大了）的表JOIN，要先分頁再JOIN，否則邏輯讀會很高，性能很差。
9、select count(*) from table；
這樣不帶任何條件的count會引起全表掃描，并且沒有任何業(yè)務(wù)意義，是一定要杜絕的。
10、sql的where條件要綁定變量
比如where column=：1，不要寫成where column=‘aaa'，這樣會導(dǎo)致每次執(zhí)行時(shí)都會重新分析，浪費(fèi)CPU和內(nèi)存資源。
文章來源：腳本之家
來源地址：https://www.jb51.net/article/204880.htm
申請創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！
