當(dāng)前位置:首頁(yè) >  站長(zhǎng) >  數(shù)據(jù)庫(kù) >  正文

PostgreSQL 禁用全表掃描的實(shí)現(xiàn)

 2021-05-24 17:06  來(lái)源: 腳本之家   我來(lái)投稿 撤稿糾錯(cuò)

  域名預(yù)訂/競(jìng)價(jià),好“米”不錯(cuò)過(guò)

這篇文章主要介紹了PostgreSQL 禁用全表掃描的實(shí)現(xiàn)操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧。

PostgreSQL可以通過(guò)一些設(shè)置來(lái)禁用全表掃描(FULL SCAN/Seq Scan)

注意:

設(shè)置此功能后不是完全避免全表掃描,而是只要有不通過(guò)全表掃描能得出結(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)

 

對(duì)用戶進(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 會(huì)引起全表掃描的10種sql語(yǔ)句

1、模糊查詢效率很低:

原因:like本身效率就比較低,應(yīng)該盡量避免查詢條件使用like;對(duì)于like ‘%...%'(全模糊)這樣的條件,是無(wú)法使用索引的,全表掃描自然效率很低;另外,由于匹配算法的關(guān)系,模糊查詢的字段長(zhǎng)度越大,模糊查詢效率越低。

解決辦法:首先盡量避免模糊查詢,如果因?yàn)闃I(yè)務(wù)需要一定要使用模糊查詢,則至少保證不要使用全模糊查詢,對(duì)于右模糊查詢,即like ‘…%',是會(huì)使用索引的;左模糊like

‘%...'無(wú)法直接使用索引,但可以利用reverse + function index 的形式,變化成 like ‘…%';全模糊是無(wú)法優(yōu)化的,一定要的話考慮用搜索引擎。出于降低數(shù)據(jù)庫(kù)服務(wù)器的負(fù)載考慮,盡可能地減少數(shù)據(jù)庫(kù)模糊查詢。

2、查詢條件中含有is null的select語(yǔ)句執(zhí)行慢

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

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

3、查詢條件中使用了不等于操作符(<>、!=)的select語(yǔ)句執(zhí)行慢

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

解決方法:通過(guò)把不等于操作符改成or,可以使用索引,避免全表掃描。例如,把column<>'aaa',改成column<'aaa' or column>'aaa',就可以使用索引了。

4、使用組合索引

如果查詢條件中沒(méi)有前導(dǎo)列,那么索引不起作用,會(huì)引起全表掃描;但是從Oracle9i開(kāi)始,引入了索引跳躍式掃描的特性,可以允許優(yōu)化器使用組合索引,即便索引的前導(dǎo)列沒(méi)有出現(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;

前一種是全表掃描,后一種則會(huì)使用組合索引。

5、or語(yǔ)句使用不當(dāng)會(huì)引起全表掃描

原因:where子句中比較的兩個(gè)條件,一個(gè)有索引,一個(gè)沒(méi)索引,使用or則會(huì)引起全表掃描。例如:where A=:1 or B=:2,A上有索引,B上沒(méi)索引,則比較B=:2時(shí)會(huì)重新開(kāi)始全表掃描。

6、組合索引

排序時(shí)應(yīng)按照組合索引中各列的順序進(jìn)行排序,即使索引中只有一個(gè)列是要排序的,否則排序性能會(huì)比較差。

例如:

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 語(yǔ)句

如果只更改1、2個(gè)字段,不要Update全部字段,否則頻繁調(diào)用會(huì)引起明顯的性能消耗,同時(shí)帶來(lái)大量日志。

8、對(duì)于多張大數(shù)據(jù)量

(這里幾百條就算大了)的表JOIN,要先分頁(yè)再JOIN,否則邏輯讀會(huì)很高,性能很差。

9、select count(*) from table;

這樣不帶任何條件的count會(huì)引起全表掃描,并且沒(méi)有任何業(yè)務(wù)意義,是一定要杜絕的。

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

比如where column=:1,不要寫成where column=‘aaa',這樣會(huì)導(dǎo)致每次執(zhí)行時(shí)都會(huì)重新分析,浪費(fèi)CPU和內(nèi)存資源。

文章來(lái)源:腳本之家

來(lái)源地址:https://www.jb51.net/article/204880.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道,分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處,共同探討創(chuàng)業(yè)新機(jī)遇!

相關(guān)文章

熱門排行

信息推薦