資料成果postgresql93培訓d

1、PostgreSQL 9.3 培訓Day 2digoal.zhou 2013/12/5課程內容n Day - 2n PostgreSQL 索引介紹n 目標:n 熟悉b-tree , hash , gist , gin , sp-gist幾種索引類型的概念, 以及在什么樣的場景應該使用何種索引n 了解全文檢索的應用n PostgreSQLn 目標:優(yōu)化n 了解explian SQL分析工具的使用, 理解explain 的代價計算原理, 并根據(jù)數(shù)據(jù)庫硬件環(huán)境校準代價因子.n 理解explain 輸出的含義 (如組合行集 , 節(jié)點處理 , 合并連接,n 連接池及數(shù)據(jù)庫高速緩存n 目標:連接 等),

2、并可以結合explain的輸出優(yōu)化SQL.n 以pgbouncer為例, 理解數(shù)據(jù)庫連接池在短連接環(huán)境下的好處, 連接池的幾種模式和使用場景n 本地和異地高速緩存的介紹, 如本地os 層緩存pgfincore, 異地K-V緩存pgmemcached的使用.n 數(shù)據(jù)庫擴展及n 目標:n 了解數(shù)據(jù)庫集群級流, 數(shù)據(jù)庫熱備份, 表級, 數(shù)據(jù)庫在虛擬化環(huán)境下的使用注意事項PostgreSQL 索引介紹n 使用索引的好處n 利用索引進行排序減少CPU開銷帶條件的JOIN操作, 刪除, 更新nn外鍵約束更新和刪除操作唯一值約束, 排他約束nnn 索引帶來的弊端n 索引隨著表的塊的變遷需要更新, 因此會對這

3、類操作帶來一定的性能影響. (塊不變更的情況下觸發(fā)HOT特性, 可以不需要更新索引)n 使用索引的注意事項n 正常創(chuàng)建索引時, 會阻斷除以外的其他操作.n 使用并行CONCURRENTLY 選項后, 可以非常長.同時對表的DML操作, 但是對于頻繁DML的表, 這種創(chuàng)建索引的時間n 某些索引不WAL, 所以如果有利于WAL進行數(shù)據(jù)恢復的情況(如crash recovery, 流, warm standby等), 這類索引在使用前需要重建. (HASH 索引)PostgreSQL 索引介紹n 索引類型n 根據(jù)不同的索引算法, PostgreSQL的算法分為 B-tree, Hash, GiST,

4、 SP-GiST, GINn select amname from pg_am;n 索引應用場景n PostgreSQL不同的索引類別支持的索引操作符也有區(qū)別, 以下為不同的索引類型對應的系統(tǒng)默認的索引策略n Btree, 同時還支持前導模糊n n =n n Hashn=(like % 或 ), 忽略大小寫字符前導模糊(ILIKE % 或* )n Gin, 支持多值列的索引, 例如數(shù)組類型, 全文檢索類型, 例如以下為一維數(shù)組類型對應的GIN索引已實現(xiàn)的n - 被包含array1,2,3 - 包含 array1,2,3 array2n=- 相等array1,2,3 = array1,2,3n

5、&- 相交array1,2,3 & array2策略操作符PostgreSQL 索引介紹n Gist, 并不是單類的索引, 可以認為它是一種索引框架, 支持許多不同的索引策略(operator class),n 例如二維幾何類型的以下操作符支持通過Gist索引- 嚴格在左側, 例如circle (0,0),1) circle (5,0),1)&| &|= &n- 表示左邊的平面體- 表示左邊的平面體- 嚴格在右- 嚴格在下擴展到超過右邊的平面體的右邊. 例如box (0,0),(1,1) & box (0,0),(2,2)nnnn擴展到超出上面擴展到超出下面-nn- 嚴格在上- 包含- 被包含

6、- 相同- 相交nnnnnn除此之外 , gist索引還支持近鄰排序. 例如n SELECT * FROM places ORDER BY location point (101,456) LIMIT 10;另外contrib中也提供了一些gist索引策略.nnPostgreSQL 索引介紹n Sp-Gist, 與gist類似, 也是一種索引框架, 支持基于磁盤n 例如二維的point類型, gist索引支持的操作符如下n n =n n - 在下面, circle (0,0),1) - 在上面, circle (0,5),1) circle (0,0),1)的非平衡數(shù)據(jù)結構, 如四, k-d樹

7、, radix樹.PostgreSQL 索引使用場景舉例n 利用索引進行排序減少CPU開銷, 刪除, 更新帶條件的JOIN操作nnnn外鍵約束更新和刪除操作唯一值約束, 排他約束PostgreSQL 索引使用場景舉例利用索引進行排序減少CPU開銷nnnnnnnnnnnnnnn1.條件就是索引列digoal=# create table test(id int, info text, crt_time timestamp); CREATE TABLEdigoal=# insert into test select generate_series(1,10000), md5(random():te

8、xt),clock_timestamp();INSERT 0 10000digoal=# create index idx_test_1 on test(id); CREATE INDEXdigoal=# explain analyze select * from test where id100 order by id;QUERY PLANIndex Scan using idx_test_1 on test (cost=0.29.162.61 rows=3333 width=44) (actual time=0.036.0.069 rows=99 loops=1) Index Cond:

9、(id Seq Scan on test (cost=0.00.219.00 rows=1 width=45) (actual time=0.016.2.201 rows=1 loops=1) Filter: (info = 620f5eaeaf0d7cf48cd1fa6c410bad49:text)Rows Removed by Filter: 9999Total runtime: 2.273 ms (7 rows)PostgreSQL 索引使用場景舉例利用索引進行排序減少CPU開銷nnnnnnnnnnnnnn2.條件不是索引列digoal=# set enable_seqscan=off;

10、 SETdigoal=# explain analyze select * from test where info=620f5eaeaf0d7cf48cd1fa6c410bad49 order by id;QUERY PLANIndex Scan using idx_test_1 on test (cost=0.29.299.29 rows=1 width=45) (actual time=0.027.3.628 rows=1 loops=1) Filter: (info = 620f5eaeaf0d7cf48cd1fa6c410bad49:text)Rows Removed by Filt

11、er: 9999Total runtime: 3.661 ms(4 rows)這個只是例子, 不一定適合實際應用場景.如果info的選擇性好的話, 在info上面加索引時比較妥當?shù)?PostgreSQL 索引使用場景舉例, 刪除, 更新帶條件的nnnnnnnnnnnnnnnnnnndigoal=# explain analyze select * from test where id=1;QUERY PLANIndex Scan using idx_test_1 on test (cost=0.29.2.30 rows=1 width=45) (actual time=0.014.0.015

12、rows=1 loops=1)Index Cond: (id = 1)Total runtime: 0.039 ms (3 rows)效率:在沒有索引的情況下的set enable_indexscan=off;set enable_bitmapscan=off;digoal=# explain analyze select * from test where id=1;QUERY PLANSeq Scan on test (cost=0.00.219.00 rows=1 width=45) (actual time=0.017.1.744 rows=1 loops=1)Filter: (id

13、= 1)Rows Removed by Filter: 9999 Total runtime: 1.773 ms(4 rows)PostgreSQL 索引使用場景舉例JOIN操作digoal=# create table test1(id int, info text, crt_time timestamp); CREATE TABLEdigoal=# insert into test1 select generate_series(1,10000), md5(random():text),clock_timestamp();INSERT 0 10000test1表沒有索引時digoal=#

14、explain analyze select t1.*,t2.* from test t1 join test1 t2 on (t1.id=t2.id and t2.id=1);QUERY PLANnnnnnnnnnnnnnnnnnNested Loop (cost=0.29.221.31 rows=1 width=90) (actual time=0.028.1.708 rows=1 loops=1)- Index Scan using idx_test_1 on test t1 (cost=0.29.2.30 rows=1 width=45) (actual time=0.015.0.01

15、6 rows=1 loops=1)Index Cond: (id = 1)- Seq Scan on test1 t2 (cost=0.00.219.00 rows=1 width=45) (actual time=0.010.1.686 rows=1 loops=1) Filter: (id = 1)Rows Removed by Filter: 9999Total runtime: 1.768 ms(7 rows)PostgreSQL 索引使用場景舉例JOIN操作digoal=# create index idx_test1_1 on test1(id); CREATE INDEXdigo

16、al=# explain analyze select t1.*,t2.* from test t1 join test1 t2 on (t1.id=t2.id and t2.id=1);QUERY PLANnnnnnnnnnnnnnNested Loop (cost=0.57.4.61 rows=1 width=90) (actual time=0.045.0.046 rows=1 loops=1)- Index Scan using idx_test_1 on test t1 (cost=0.29.2.30 rows=1 width=45) (actual time=0.012.0.012

17、 rows=1 loops=1) Index Cond: (id = 1)- Index Scan using idx_test1_1 on test1 t2 (cost=0.29.2.30 rows=1 width=45) (actual time=0.029.0.030 rows=1 loops=1)Index Cond: (id = 1) Total runtime: 0.089 ms (6 rows)PostgreSQL 索引使用場景舉例JOIN操作MERGE JOIN也能用到索引.digoal=# explain analyze select t1.*,t2.* from test

18、t1 join test1 t2 on (t1.id=t2.id);QUERY PLANnnnnnnnnnnnnnn-Merge Join (cost=0.57.698.57 rows=10000 width=90) (actual time=0.024.14.468 rows=10000 loops=1) Merge Cond: (t1.id = t2.id)- Index Scan using idx_test_1 on test t1 (cost=0.29.274.29 rows=10000 width=45) (actual time=0.010.3.754 rows=10000 lo

19、ops=1)- Index Scan using idx_test1_1 on test1 t2 (cost=0.29.274.29 rows=10000 width=45) (actual time=0.007.3.715 rows=10000 loops=1)Total runtime: 15.429 ms (5 rows)PostgreSQL 索引使用場景舉例JOIN操作在沒有索引的情況下, merge join增加排序開銷.digoal=# explain analyze select t1.*,t2.* from test t1 join test1 t2 on (t1.id=t2.

20、id);QUERY PLANnnnnnnnnnnnnnnnnnMerge Join (cost=1716.77.1916.77 rows=10000 width=90) (actual time=8.220.17.291 rows=10000 loops=1)Merge Cond: (t1.id = t2.id)-Sort (cost=858.39.883.39 rows=10000 width=45) (actual time=4.177.5.211 rows=10000 loops=1) Sort Key: t1.idSort Method: quicksort Memory: 1018k

21、B- Seq Scan on test t1 (cost=0.00.194.00 rows=10000 width=45) (actual time=0.008.1.757 rows=10000 loops=1)Sort (cost=858.39.883.39 rows=10000 width=45) (actual time=4.035.5.300 rows=10000 loops=1) Sort Key: t2.idSort Method: quicksort Memory: 1018kB- Seq Scan on test1 t2 (cost=0.00.194.00 rows=10000

22、 width=45) (actual time=0.006.1.752 rows=10000 loops=1)-Total runtime: 18.420 ms(11 rows)PostgreSQL 索引使用場景舉例外鍵約束更新和刪除操作nn digoal=# create table p(id int primary key, info text, crt_time timestamp);n CREATE TABLEn digoal=# create table f(id int primary key, p_id int references p(id) on delete cascade

23、 on update cascade, info text, crt_time timestamp);n CREATE TABLEn digoal=# insert into p select generate_series(1,10000), md5(random():text), clock_timestamp();n INSERT 0 10000n digoal=# insert into f select generate_series(1,10000), generate_series(1,10000), md5(random():text), clock_timestamp();n

24、 INSERT 0 10000PostgreSQL 索引使用場景舉例外鍵約束更新和刪除操作 在f表的p_id未加索引時, 更新p.iddigoal=# explain (analyze,verbose,costs,buffers,timing) update p set id=0 where id=1;QUERY PLANnnnnnnnnnnnnnnnUpdate on postgres.p (cost=0.29.2.30 rows=1 width=47) (actual time=0.082.0.082 rows=0 loops=1)Buffers: shared hit=8- Index

25、Scan using p_pkey on postgres.p (cost=0.29.2.30 rows=1 width=47) (actual time=0.021.0.022 rows=1 loops=1) Output: 0, info, crt_time, ctidIndex Cond: (p.id = 1)Buffers: shared hit=3Trigger RI_ConstraintTrigger_a_92560 for constraint f_p_id_fkey on p: time=2.630 calls=1 Trigger RI_ConstraintTrigger_c_

26、92562 for constraint f_p_id_fkey on f: time=0.059 calls=1 Total runtime: 2.820 ms(9 rows)PostgreSQL 索引使用場景舉例外鍵約束更新和刪除操作增加f表的p_id列上的索引digoal=# create index idx_f_1 on f(p_id); CREATE INDEXdigoal=# explain (analyze,verbose,costs,buffers,timing) update p set id=1 where id=0;QUERY PLANnnnnnnnnnnnnnnnnnU

27、pdate on postgres.p (cost=0.29.2.30 rows=1 width=47) (actual time=0.067.0.067 rows=0 loops=1) Buffers: shared hit=8- Index Scan using p_pkey on postgres.p (cost=0.29.2.30 rows=1 width=47) (actual time=0.018.0.020 rows=1 loops=1)Output: 1, info, crt_time, ctid Index Cond: (p.id = 0) Buffers: shared h

28、it=3Trigger RI_ConstraintTrigger_a_92560 for constraint f_p_id_fkey on p: time=0.471 calls=1 Trigger RI_ConstraintTrigger_c_92562 for constraint f_p_id_fkey on f: time=0.053 calls=1 Total runtime: 0.636 ms(9 rows)PostgreSQL 索引使用場景舉例索引在排他約束中的使用對排他操作符的要求, 左右操作數(shù)互換對結果沒有影響. 例如x=y, y=x 結果都為true或unknown. 用

29、法舉例digoal=# CREATE TABLE test(id int,geo point,EXCLUDE USING btree (id WITH pg_catalog.=); CREATE TABLEdigoal=# insert into test (id) values (1);INSERT 0 1digoal=# insert into test (id) values (1);nnnnnnnnnnnnnnERROR: 23P01:DETAIL: Key (id)=(1)ing key value violates exclusion constraint test_id_excl

30、s with existing key (id)=(1).SCHEMA NAME: postgresTABLE NAME: testCONSTRAINT NAME: test_id_exclLOCATION: check_exclusion_constraint, execUtils.c:1337PostgreSQL 索引使用場景舉例唯一值約束nn Primary KEYn Unique KEYPostgreSQL 索引使用場景舉例digoal=# CREATE TABLE test(id int,geo point,EXCLUDE USING spGIST (geo WITH pg_cata

31、log.=); CREATE TABLE索引如下:digoal=# d testTable postgres.testColumn | Type| Modifiersnnnnnnnnnnn+id| integer |geo| point|Indexes:test_geo_excl EXCLUDE USING spgist (geo WITH =)PostgreSQL 索引使用場景舉例n CREATE TABLE test(id int,geo circle,EXCLUDE USING GIST (geo WITH pg_catalog.&);n INSERT INTO test values(

32、1,:circle);n INSERT INTO test values(1,:circle);n INSERT INTO test values(1,:circle);n ERROR:n DETAIL:ing key value violates exclusion constraint test_geo_exclKey (geo)=()s with existing key (geo)=().c3c1c2PostgreSQL 索引使用場景舉例digoal=# CREATE TABLE test(id int,geo circle,EXCLUDE USING GIST (geo WITH p

33、g_catalog.&); CREATE TABLEnndigoal=# CREATE TABLE test(id int,geo circle,EXCLUDE USING GIST (geo WITH pg_catalog.=);CREATE TABLEnn以下例子左右操作數(shù)互換后得到的結果不一致, 所以這類操作符不創(chuàng)建排他索引.nnnnnnnnndigoal=# CREATE TABLE test(id int,geo point,EXCLUDE USING spGIST (geo WITH pg_catalog.); ERROR: 42809: operator ); ERROR: 42

34、809: operator (integer,integer) is not commutativeDETAIL: Only commutative operators can be used in exclusion constraints.LOCATION: ComputeIndexAttrs, indexcmds.c:1132?是否使用索引和什么有n 是否使用索引和什么有 ?n 首先是前面提到的Access Method, 然后是使用的operator class, 以及opc中定義的operator或function.n 這些都滿足后, 還要遵循CBO的選擇.n #seq_page_c

35、ost = 1.0n #random_page_cost = 4.0n #cpu_tuple_cost = 0.01n #cpu_index_tuple_cost = 0.005n #cpu_operator_cost = 0.0025n #effective_cache_size = 128MB遵循完CBO的選擇, 還需要符合當前配置的Planner 配置.n#enable_bitmapscan = on #enable_hashagg = on #enable_hashjoin = on #enable_indexscan = on #enable_material = on #enabl

36、e_mergejoin = on #enable_nestloop = on #enable_seqscan = on #enable_sort = on#enable_tidscan = onnnnnnnnnnn多列索引的使用Multicolumn Indexn 多列索引, 使用任何列作為條件, 只要條件中的操作符或函數(shù)能滿足opclass的匹配, 都可以使用索引, 索引被掃描的部分還是全部基本取決于條件中是否有索引的第一列作為條件之一.例子postgres=# create table test (c1 int,c2 int);postgres=# insert into test sel

37、ect 1,generate_series(1,100000);nnnnnnnnnnnnpostgres=# create index idx_test_1 on test(c1,c2); postgres=# analyze test;postgres=# explain select * from test where c2=100;Seq Scan on test (cost=0.00.1693.00 rows=1 width=8) Filter: (c2 = 100)postgres=# set enable_seqscan=off;注意過濾條件不是驅動列.看似不能走索引postgre

38、s=# explain analyze select * from test where c2=100;Index Scan using idx_test_1 on test (cost=0.00.1858.27 rows=1 width=8) (actual time=0.104.7.045 rows=1 loops=1)Index Cond: (c2 = 100)n索引合并Combining Multiple Indexessrc/backend/executor例如nnnfilterBitmapAn d | OrBitmap Heap Scanfilter。Bitmap, TupleAd

39、dr(s)Bitmap Index ScanBitmap,TupleAddr(s)Bitmap IndexScan索引合并Combining Multiple Indexes單列索引的多條件匹配合并nnpostgres=# create table test (id int primary key,info text unique);postgres=# insert into test select generate_series(1,100000),digoal|generate_series(1,100000);postgres=# explain analyze select * fr

40、om test where id=1 or id=1000;Bitmap Heap Scan on test (cost=8.54.16.20 rows=2 width=36) (actual time=0.034.0.036 rows=2 loops=1)Recheck Cond: (id = 1) OR (id = 1000)nnnnn-BitmapOr(cost=8.54.8.54 rows=2 width=0) (actual time=0.023.0.023nrows=0 loops=1)- Bitmap Index Scan on test_pkey (cost=0.00.4.27

41、 rows=1 width=0) (actual time=0.012.0.012 rows=1 loops=1)Index Cond: (id = 1)- Bitmap Index Scan on test_pkey (cost=0.00.4.27 rows=1 width=0) (actual time=0.009.0.009 rows=1 loops=1)Index Cond: (id = 1000)nnnn索引和collate的匹配collection例子nnCREATE TABLE test1c (id integer,content varchar COLLATE x);nnnnC

42、REATE INDEX test1c_content_index ON test1c (content);SELECT * FROM test1c WHERE content constant;nn- 以下SQL不能使用索引test1c_content_indexSELECT * FROM test1c WHERE content constant COLLATE y;nn- 需建立與y COLLATE對應的索引, 以上這條SQL才會走索引.CREATE INDEX test1c_content_y_index ON test1c (content COLLATE y);nn部分值索引part

43、ial index例子- 部分約束-去除common值 id=1, 這個值有10W條, 走索引根本不合適. partial 索引很好的避免了此類情況.nnnnpostgres=# create table test(id int,info text);postgres=# insert into test select 1,digoal|generate_series(1,100000);postgres=# insert into test select generate_series(1,1000),digoal|generate_series(1,1000);nnnpostgres=#

44、create index idx_test_1 on test(id) where id1;npostgres=# explain select * from test where id=1;Seq Scan on test (cost=0.00.1791.00 rows=100000 width=15) Filter: (id = 1)postgres=# explain select * from test where id=100;Index Scan using idx_test_1 on test (cost=0.00.8.27 rows=1 width=15) Index Cond

45、: (id = 100)nnnnnn部分值索引n - 部分索引在非索引列的使用postgres=# explain select * from test where info=digoal and id=1;QUERY PLANnnnSeq Scan on test (cost=0.00.2041.00 rows=1 width=15)Filter: (info = digoal:text) AND (id = 1)nnpostgres=# create index idx_test_2 on test(id) where info=digoal100;npostgres=# explain

46、select * from test where info=digoal100;QUERY PLANnnnIndex Scan using idx_test_2 on test (cost=0.00.8.27 rows=1 width=15)(1 row)nn部分值索引n - 部分索引的好處, 為什么要去除common 值postgres=# drop index idx_test_1; postgres=# drop index idx_test_2;postgres=# explain select * from test where id=1;QUERY PLANnnnnnSeq Sca

47、n on test (cost=0.00.1791.00 rows=100000 width=15)Filter: (id = 1)nn- 為什么會走掃描npostgres=# select id,count(*) from test group by id order by count(*) desc limit 10;id | countnn+n1 | 100001nn120 |887 |681 |111nn函數(shù)和表達式索引函數(shù)索引和表達式索引表達式索引nnpostgres=# explain select * from test where id+1=100;QUERY PLANnnnS

48、eq Scan on test(cost=0.00.2059.86 rows=505 width=15)nFilter: (id + 1) = 100)postgres=# create index idx_test_1 on test(id+1); CREATE INDEXnnnpostgres=# explain select * from test where id+1=100;nQUERY PLANnnBitmap Heap Scan on test (cost=12.18.577.45 rows=505 width=15)Recheck Cond: (id + 1) = 100)-

49、Bitmap Index Scan on idx_test_1 (cost=0.00.12.05 rows=505 width=0) Index Cond: (id + 1) = 100)nnnn函數(shù)和表達式索引n 函數(shù)索引- 以下區(qū)分大小寫的場景無法使走普通的索引.npostgres=# create table test (id int,info text,crt_time timestamp(0); postgres=# insert into test selectgenerate_series(1,100000),digoal|generate_series(1,100000),clock_timestamp();postgres=# create index idx_test_info on te