本文來自： http://www.023DNS.com/Database_mssql/5974.html

海晏網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián),海晏網(wǎng)站設(shè)計制作，有大型網(wǎng)站制作公司豐富經(jīng)驗。已為海晏成百上千提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設(shè)公司要多少錢，請找那個售后服務(wù)好的海晏做網(wǎng)站的公司定做！

PostgreSQL優(yōu)化器是基于成本的 (CBO) , (當(dāng)然, 如果開啟了GEQO的話, 在關(guān)聯(lián)表數(shù)量超過一定閾值后, 會采用GEQO, 這主要是由于在關(guān)聯(lián)表太多的情況下, 窮舉法可能帶來巨大的PLAN開銷, 所以GEQO輸出的執(zhí)行計劃不一定是最優(yōu)的)

本文要談的和GEQO沒什么關(guān)系, 主要是與CBO相關(guān).

當(dāng)PostgreSQL使用CBO時, 就一定能每次都輸出最優(yōu)的執(zhí)行計劃嗎?

1. 首選我們看看CBO考察了哪些因素, 它是如何計算成本的?

成本和掃描方式, 關(guān)聯(lián)方式, 操作符, 成本因子, 數(shù)據(jù)集等都有關(guān), 具體的計算方法可參考如下代碼:

src/backend/optimizer/path/costsize.c

我們這里簡單的列舉一下, 哪些因素會影響成本計算的結(jié)果, 具體算法見costsize.c :

-- 表有多少條記錄, 影響全表掃描的 CPU處理記錄的COST.

-- 表有多少個數(shù)據(jù)塊, 影響掃描數(shù)據(jù)塊的成本; 例如全表掃描, 索引掃描, 都需要掃描數(shù)據(jù)塊.

-- 成本因子, 影響成本的計算結(jié)果; 例如連續(xù)或隨機(jī)掃描單個數(shù)據(jù)塊的成本因子, CPU從HEAP塊處理一條記錄的成本因子, 從INDEX塊處理一條索引記錄的成本因子, 執(zhí)行一個操作符或函數(shù)的成本因子.

-- 數(shù)據(jù)存儲物理順序和索引順序的離散度, 影響索引掃描的計算成本.

-- 內(nèi)存大小, 影響索引掃描的計算成本.

-- 列統(tǒng)計信息(列寬, 空值比例, 唯一值比例, 高頻值及其比例, bucket, 物理順序和索引順序的離散度, 數(shù)組的話還有數(shù)組的統(tǒng)計信息, 等), 影響選擇性, 即結(jié)果集行數(shù), 最終影響索引掃描的計算成本.

-- 創(chuàng)建函數(shù)或操作符時設(shè)置的成本.

2. 然后我們看看哪些因素CBO沒有考慮進(jìn)去, 還有哪些因素CBO考慮進(jìn)去了, 但是可能會隨時發(fā)生變化的.

PostgreSQL是否能動態(tài)的跟上這些變化?

2.1 PostgreSQL開啟自動analyze, 可以適時更新的因素如下 :

-- 表有多少數(shù)據(jù)塊, 記錄數(shù), 更新pg_class.relpages, pg_class.reltuples

-- 列統(tǒng)計信息, 數(shù)據(jù)存儲物理順序和索引順序的離散度, 更新pg_statistic

2.2 靜態(tài)配置因素 :

-- 實際可用作緩存的內(nèi)存, 因為數(shù)據(jù)庫所在的操作系統(tǒng)中可能還運行了其他程序, 可用作緩存的內(nèi)存可能會發(fā)生變化. 即使沒有運行其他程序, 當(dāng)數(shù)據(jù)庫會話中有大量使用了work_mem時, 也會造成可用做緩存的內(nèi)存發(fā)生變化.

-- 創(chuàng)建函數(shù)或操作符時設(shè)置的成本, 當(dāng)函數(shù)因為內(nèi)部SQL或處理邏輯等變化, 可能導(dǎo)致函數(shù)本身的處理時間發(fā)生變化.

2.3 未考慮的因素 :

-- 塊設(shè)備的的預(yù)讀, 一般情況下一次讀取時, 會預(yù)讀128KB的數(shù)據(jù).

# blockdev --getra /dev/sda

256

這又有什么影響呢? 如果你要讀取的數(shù)據(jù)在連續(xù)的128KB數(shù)據(jù)塊中, 那么只需要一次塊設(shè)備的IO. 對于數(shù)據(jù)庫來說, 掃描數(shù)據(jù)時掃多少個數(shù)據(jù)塊可不管這個, 都會計算成本, 因此對于不同的塊設(shè)備預(yù)讀配置, 或者對于不同的塊設(shè)備(如機(jī)械盤和SSD), 掃描成本可能不一樣. PostgreSQL塊設(shè)備的性能反映在成本計算方面, 就是seq_page_cost, random_page_cost.

這兩個參數(shù)可以針對表空間設(shè)置, 也就是說, 對于不同的表空間, 可以設(shè)置不同的值, 比如我們有在SSD建立的表空間, 也有在普通機(jī)械盤上創(chuàng)建的表空間, 當(dāng)然需要設(shè)置不同的seq_page_cost, random_page_cost值.

但是對于預(yù)讀來說, 如果發(fā)生了變更, 對實際的性能會有細(xì)微的影響, 一般應(yīng)該不會一天到晚變更塊設(shè)備的read ahead吧.

2.4 generic plan cache, 即執(zhí)行計劃緩存.

PostgreSQL 通過choose_custom_plan選擇重新規(guī)劃執(zhí)行計劃還是使用緩存的執(zhí)行計劃, 當(dāng)cached plan成本大于custom的平均成本時, 會選擇custom plan , 所以當(dāng)統(tǒng)計信息正確的情況下, 可以及時發(fā)現(xiàn)緩存執(zhí)行計劃的問題并及時規(guī)劃新的執(zhí)行計劃.

詳情請見 : src/backend/utils/cache/plancache.c

2.5 采樣精度參數(shù)default_statistics_target , 影響bucket個數(shù), 采樣的精度.

經(jīng)過一番分析, PostgreSQL使用了CBO, 就一定能"每次"都輸出最優(yōu)的執(zhí)行計劃嗎?

1. 首選要確保人為設(shè)置成本因子準(zhǔn)確, 另外還需要打開自動analyze(適時更新 列統(tǒng)計信息, 塊, 離散度等),

2. 影響成本的因素還有一些是靜態(tài)配置的 : 比如可用作BUFFER的內(nèi)存, 函數(shù)的成本.

3. 還有沒考慮的: 預(yù)讀 (甚微).

在大多數(shù)情況下, 如果我們設(shè)置了合理的配置,那么 很少需要使用hint的. 除了以上2,3提到的兩點.

同時hint也存在比較嚴(yán)重的弊端, 如果將hint寫在程序代碼中, 一旦需要變更執(zhí)行計劃, 還需要改程序代碼, 不靈活.

當(dāng)然, 我們不排除另一種用HINT的出發(fā)點, 比如調(diào)試. 我就想看看不同執(zhí)行計劃下執(zhí)行效率是否和想象的一樣.

(我們也可以使用開關(guān)來控制執(zhí)行計劃, 但是有HINT不是更直接一點嘛)

從長遠(yuǎn)來看, 如果僅僅從性能角度來說, 不斷地改進(jìn)數(shù)據(jù)庫本身的優(yōu)化器是比較靠譜的. 但是對于例如調(diào)試這樣的需求, 有HINT更方便也是對的.

進(jìn)入主題, 大多數(shù)Oracle用戶在接觸到PostgreSQL后, 會問PG有沒有SQL hint?

為了讓數(shù)據(jù)庫按照用戶的想法輸出執(zhí)行計劃, 一般來說PostgreSQL提供了一些開關(guān), 比如關(guān)閉全表掃描, 讓它去走索引.

關(guān)閉索引掃描, 讓它去走bitmap或全表掃描, 關(guān)閉嵌套循環(huán), 讓他去走h(yuǎn)ash join或merge join等.

但是僅僅有這些開關(guān), 還不是非常的好用, 那么到底有沒有直接點的HINT呢?

有一個插件可以解決你的問題,：pg_hint_plan.

pg_hint_plan利用PostgreSQL 開放的hook接口, 所以不需要改PG代碼就實現(xiàn)了注入HINT的功能.

/*

* Module load callbacks

*/

void

_PG_init(void)

{

...

}

由于不同PostgreSQL 版本, plan部分的代碼可能會不一致, 所以pg_hint_plan也是分版本發(fā)布的源碼.

比如我要在PostgreSQL 9.4.1中測試一下這個工具.

接下來測試一下 :

安裝

# wget http://iij.dl.sourceforge.jp/pghintplan/62456/pg_hint_plan94-1.1.3.tar.gz

# tar -zxvf pg_hint_plan94-1.1.3.tar.gz

# cd pg_hint_plan94-1.1.3

[root@db-172-16-3-150 pg_hint_plan94-1.1.3]# export PATH=/opt/pgsql/bin:$PATH

[root@db-172-16-3-150 pg_hint_plan94-1.1.3]# which psql

/opt/pgsql/bin/psql

[root@db-172-16-3-150 pg_hint_plan94-1.1.3]# psql -V

psql (PostgreSQL) 9.4.1

# gmake clean

# gmake

# gmake install

[root@db-172-16-3-150 pg_hint_plan94-1.1.3]# ll -rt /opt/pgsql/lib|tail -n 1

-rwxr-xr-x 1 root root 78K Feb 18 09:31 pg_hint_plan.so

[root@db-172-16-3-150 pg_hint_plan94-1.1.3]# su - postgres

$ vi $PGDATA/postgresql.conf

shared_preload_libraries = 'pg_hint_plan'

pg_hint_plan.enable_hint = on

pg_hint_plan.debug_print = on

pg_hint_plan.message_level = log

$ pg_ctl restart -m fast

postgres@db-172-16-3-150-> psql

psql (9.4.1)

Type "help" for help.

postgres=# create extension pg_hint_plan;

CREATE EXTENSION

用法舉例說明 :

postgres=# create table a(id int primary key, info text, crt_time timestamp);

CREATE TABLE

postgres=# create table b(id int primary key, info text, crt_time timestamp);

CREATE TABLE

postgres=# insert into a select generate_series(1,100000), 'a_'||md5(random()::text), clock_timestamp();

INSERT 0 100000

postgres=# insert into b select generate_series(1,100000), 'b_'||md5(random()::text), clock_timestamp();

INSERT 0 100000

postgres=# analyze a;

ANALYZE

postgres=# analyze b;

ANALYZE

postgres=# explain select a.*,b.* from a,b where a.id=b.id and a.id<10;< p="">

QUERY PLAN

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

Nested Loop (cost=0.58..83.35 rows=9 width=94)

-> Index Scan using a_pkey on a (cost=0.29..8.45 rows=9 width=47)

Index Cond: (id < 10)

-> Index Scan using b_pkey on b (cost=0.29..8.31 rows=1 width=47)

Index Cond: (id = a.id)

(5 rows)

在沒有pg_hint_plan時, 我們需要使用開關(guān)來改變PostgreSQL的執(zhí)行計劃

postgres=# set enable_nestloop=off;

SET

postgres=# explain select a.*,b.* from a,b where a.id=b.id and a.id<10;< p="">

QUERY PLAN

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

Hash Join (cost=8.56..1616.65 rows=9 width=94)

Hash Cond: (b.id = a.id)

-> Seq Scan on b (cost=0.00..1233.00 rows=100000 width=47)

-> Hash (cost=8.45..8.45 rows=9 width=47)

-> Index Scan using a_pkey on a (cost=0.29..8.45 rows=9 width=47)

Index Cond: (id < 10)

(6 rows)

postgres=# set enable_nestloop=on;

SET

postgres=# explain select a.*,b.* from a,b where a.id=b.id and a.id<10;< p="">

QUERY PLAN

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

Nested Loop (cost=0.58..83.35 rows=9 width=94)

-> Index Scan using a_pkey on a (cost=0.29..8.45 rows=9 width=47)

Index Cond: (id < 10)

-> Index Scan using b_pkey on b (cost=0.29..8.31 rows=1 width=47)

Index Cond: (id = a.id)

(5 rows)

使用pg_hint_plan來改變PostgreSQL的執(zhí)行計劃，如下所示 :

postgres=# /*+

HashJoin(a b)

SeqScan(b)

*/ explain select a.*,b.* from a,b where a.id=b.id and a.id<10;< p="">

QUERY PLAN

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

Hash Join (cost=8.56..1616.65 rows=9 width=94)

Hash Cond: (b.id = a.id)

-> Seq Scan on b (cost=0.00..1233.00 rows=100000 width=47)

-> Hash (cost=8.45..8.45 rows=9 width=47)

-> Index Scan using a_pkey on a (cost=0.29..8.45 rows=9 width=47)

Index Cond: (id < 10)

(6 rows)

postgres=# /*+ SeqScan(a) */ explain select * from a where id<10;< p="">

QUERY PLAN

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

Seq Scan on a (cost=0.00..1483.00 rows=10 width=47)

Filter: (id < 10)

(2 rows)

postgres=# /*+ BitmapScan(a) */ explain select * from a where id<10;< p="">

QUERY PLAN

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

Bitmap Heap Scan on a (cost=4.36..35.17 rows=9 width=47)

Recheck Cond: (id < 10)

-> Bitmap Index Scan on a_pkey (cost=0.00..4.36 rows=9 width=0)

Index Cond: (id < 10)

(4 rows)

目前pg_hint_plan支持的HINT

http://pghintplan.sourceforge.jp/hint_list.html

The available hints are listed below.

[參考]

1. http://pghintplan.sourceforge.jp/pg_hint_plan-en.html

2. http://pghintplan.sourceforge.jp/pg_hint_plan.html

3. http://pghintplan.sourceforge.jp/hint_list.html

4. http://pghintplan.sourceforge.jp/

5. src/backend/optimizer/path/costsize.c

6. src/backend/utils/cache/plancache.c

本文標(biāo)題：PostgreSQLSQLHINT的使用說明
標(biāo)題網(wǎng)址：http://www.rwnh.cn/article34/jiedpe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供小程序開發(fā)、標(biāo)簽優(yōu)化、電子商務(wù)、面包屑導(dǎo)航、用戶體驗、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)