阿里云PostgreSQL_开发实践_1 | 学习笔记

开发者学堂课程【PostgreSQL云上开发实践:阿里云PostgreSQL_开发实践_1】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/528/detail/7104

阿里云PostgreSQL_开发实践_1

开发实践分为两类

1. RDS PostgreSQL/PPAS

2. Hybrid DB for PostgreSQL

内容介绍：

一、PPAS

二、PPAS AWR

三、物化视图

四、分页

五、实时数据清洗、转换

六、数据采样

七、数据加密

八、字段加密

九、约束种类与用法

一、PPAS

· SQL 防火墙

https://github.com/digoal/blog/blob/master/201801/20180116_02.md

· CPU\I0 资源隔离

https://github.com/digoal/blog/blob/master/201801/20180113_01.md

· 索引推荐

https://github.com/digoal/blog/blob/master/201801/20180113_02.md

· AWR

https://github.com/digoal/blog/blob/master/201606/20160628_01.md

· VPD(RLS)

https://www.enterprisedb.com/docs/en/10.0/EPAS_Guide_v10/EDB_Postgres_Advanced_Server_Guide.1.52.html#plD0E04QC0HA

二、PPAS AWR

全面的系统报告: edbreport(beginning_id,ending_id)

数据库报告: stat_db_rpt(beginning_id,ending_id)

指定范围的表级报告:

stat_tables_rpt(beginning_id,ending_id,top_n,scope )

scope=ALL,USER,SYS

指定范围的表级10报告:

statio_tables_rpt(beginning_id,ending_id,top_n,scope)

指定范围的索引级报告:

stat_indexes_rpt(beginning_id,ending_id,top_n,scope)

指定范围的索引级10报告:

statio_indexes_rpt(beginning_id,ending_id,top_n,scope)

三、物化视图

https://www.postgresgl.org/docs/devel/static /sgl-creatematerializedview.html

· 预计算，支持索引。

· CREATE MATERIALIZED VIEW [IF NOT EXISTS ]table_name

·   [(column name[，...])]

·   [WITH( storage_parameter [= value] [,.…. ])]

·  [ TABLESPACE tablespace_name]

·  AS query

·  [WITH [NO] DATA]

·  刷新物化视图

· REFRESH MATERIALIZED VIEW [CONCURRENTLY]name

·  [WITH [NO] DATA]

四、分页

· 每一页都丝般柔滑的方法

-1、使用游标

· declare cur1 cursor for select * from table where xxx

order by xx;

· fetch 10 from cur1;

-2、使用位点，每次取值区间以上一次的最后位点为开始点。

·select * from table where xx>上一次最大点and xxxx order by xx limit ?;

https://github.com/digoal/blog/blob/master/201605/20160506_01.md https://github.com digoal/blog/blob/master/201509/20150919_02.md https://github.com/digoal/blog/blob/master/201402/20140211_01.md https://github.com/digoal/blog/blob/master/201206/20120620_01.md https://github.com/digoal/blog/blob/master/201102/20110216_02.md

五、实时数据清洗、转换

https://github.com/digoal/blog/blob/master/201706/20170619_02.md

· rule

· 创建来源表结构

- postgres=# create table nt(id int,c1 numeric,c2 numeric)

- CREATE TABLE

· 创建目标表结构

- postgres=# create table nt geo (id int,geo geometry);

- CREATE TABLE

· 对来源表创建规则或触发器，例如

- postgres=# create rule r1 as on insert to nt do instead insert into nt geo values (NEW.id, ST MakePoint(NEW.c1NEWc2));

- CREATE RULE

· 使用来源数据结构，将数据插入来源数据表

- postgres=# insert into nt values (1,1,1))

- INSERTO1

· rule

· 源表，JSONB非结构化

- postgres=# create table t1 (id int,info text,jjsonb)

· 目标表，结构化

- postgres=# create table t2(id int,info text,c1int,c2 int,c3 text);

· 在源表创建规则，自动将JSONB非结构化数据，转换为结构化数据插入 

- postgres=# create rule r1 as on insert to t1 do instead insert into t2 values(NEW.ID,NEW.INFO,((NEW.J)>>'c1')::int,((NEW.i)->>'c2')::int,(NEW.i)->>'c3');

- postgres=# insert into t1 values(1,'test' jsonb '{"c1":1,"c2":2,"c3":"text"}'); - postgres=# select*from t1;

- (0 rows)

- postgres=# select*from t2

- id| info |c1|c2|c3

- 1|test | 1|2 | text

- (1 row)

六、数据采样

· 使用采样算法

——行级随机采样( BERNOULLI (百分比))

· select *from test TABLESAMPLE bernoulli(1))

——块级随机采样( SYSTEM (百分比))

· select*from test TABLESAMPLE system(1);

https:/github.com/digoal/blog/blob/master201706/201706_02 02.md

七、数据加密

· pgcrypto

https://www.postgresql.org/docs/10/static/pgcrypto. html

· 加密后的查询加速(等值查询)

· 敏感信息加密  -对称加密

· 密码  -不可逆加密

八、字段加密

· digoal=# create extension pgcrypto;

· 可逆加密

- digoal=# insert into userpwd(userid,pwd)values(1,crypt('this is a pwd source’,gen_salt(‘bf’,10)));

- digoal=# create table userpwd(userid int8 primary key,pwd text);

- CREATE TABLE

· 不可逆加密

https://github.com/digoal/blog/blob/master/201711/20171127_02.md

九、约束种类与用法

· 唯一,unique

· 非空，not null

· check,check(exp);

· 外键

· 排他，(例如,空间不相交地图应用，范围不相交，边界限制。)

https://leithub.com/digoal/blog/blob/master/201712/20171223_02.md

- CREATE TABLE reservation

- ( during tsrange,

- EXCLUDE USING GIST(during WITH &&)

- );

- CREATE EXTENSION btree_gist;

- CREATE TABLE room_reservation

- (room text,

- during tsrange,

- EXCLUDE USING GIST(room WITH =,during WITH &&)

- ）；

 

十、数据去重大法

https://github.com/digoal/blog/blob/master/201706/20170602_01.md

-单列去重

-多列去重

-行去重

-多列混合去重

· 窗口、行号、=any(array())、数组 

 

十一、模糊查询

· 单、双字搜索

- postgres=# create or replace function split_12(text) retuns text[ ] as $$ 

- declare

- res text[ ];

- begin

- select regexp_split_to_array($1,") into res;

- for i in 1..length($1)-1 loop

- res := array_append(res.substring($1.i.2));

- end loops;

- return res;

- end;

- $$ language plpgsal strict immutable;

- CREATE FUNCTION

- postgres-# select split_12("abc你好");

-    split_12

- (a,b,c,你,好,ab,bc,c你,你好)

- (1 row)

- create index idx2 on tbl using gin (split_12(col));

- select* from tbl where split 12(col)@>array[‘单字或双字’];

· 大于2个字

https://github.com/digoal/blog/blob/master/201704/20170426_01.md

- create extension pg_trgm;

-create index idx on tbl using gin (col gin_trgm_ops);

-select*from tbl where col like '%xxx%';

并行计算资源控制

· 单个并行节点并行度

-#max_parallel_workers_per_gather=2

· 全局并行度

-#max_parallel_workers=8

· 并行度算法

https://github.com/digoal/blog/blob/master/201610/20161002_01.md

强制设置并行度

postgres=# set max_parallel_workers_per_gather =32;

SET

postgres=# set parallel_setup_cost =0;

SET

postgres=# set parallel tuple_cost =0;

SET

postgres=# set min_parallel table_scan_size =0 ；

SET

postgres=# set min_parallelindex_scan size =0;

SET

postgres=# alter table a set (parallel workers =32);

ALTER TABLE

postgres=# explain select count(*) from a;

 

QUERY PLAN

Finalize Aggregate(cost=86811.94..86811.95 rows=1width=8)

->Gather (cost=86811.85..86811.86 rows=32 width=8)

Workers Planned: 32

->Partial Aggregate (cost=86811.85..86811.86 rows=1 width=8)

->Parallel Index Only Scan using a_pkey on a(cost=0.43.86030.60rows=312500 width=0

(5 rows)

不设置强制，则按表、索引大小、成本、自动估算并行度

批量DML

https://github.com/digoal/blog/blob/master/201704/20170424_05.md

· 批量插入

-insert into tbl values()(),…();

-copy

· 批量更新

-update tbl from tmp set x=tmp.xwhere tbl.id=tmp.id;

· 批量删除

-delete from tbl using tmp where tmp.id=tbl.id;

9种索引接口的选择

https://github.com/digoal/blog/blob/master/201706/20170627_01.md

· B-Tree

-——等值、区间、排序

· Hash

——等值、LONG STRING

 · GIN

——多值类型、倒排

——多列，任意列组合查询

· GiST

——空间、异构数据(范围)

· SP-GiST

——空间、异构数据

· BRIN

——线性数据、时序数据

· Bloom

——多列、任意列组合，等值查询

· 表达式索引

——搜索条件为表达式时。whereabs(a+b)=?

· 条件索引(定向索引)

——搜索时，强制过滤某些条件时。

where status=active' and col=?。

create index idx on tbl(col) where status='active';

ADHoc查询

· 单索引复合顺序选择

- 驱动列优先选择等值条件列

· 任意字段组合扫描需求，不适合复合索引

- 多个b-tree索引支持 bitmap scan

- GIN

- bloom 

函数稳定性

https://github.com/digoal/blog/blob/master/201212/20121226_01.md

· 稳定性

- volatile，不稳定，每次都会被触发调用。(select*from tbl where id=func();有多少记录，就会被触发多少次调用func().)

- stable，稳定，在事务中只调用一次。

- immutable,超级稳定，执行计划中，直接转换为常量。

· 索引表达式

- 必须是immutable稳定性的函数或操作符

· 使用索引

- 必须是stable以上稳定性的函数或操作符

- select * from tbl where a=now()；

- now()=都是stable以上操作符。

· 绑定变量

- stable，每次execute被调用。

- immutable，prepare时转换为常量，不再被调用。 

并发创建索引

https://www.postgresql.org/docs/devel/static/sql-createindex.html

· Command:cREATEINDEX 并发创建索引

· Description:define a new index 不堵塞DML的方法

· Syntax:

· CREATE [UNIQUE ]INDEX [ CONCURRENTLY ] [[ IF NOT EXISTS]name]ON table name[USING method ]

· ({column_name|(expression)}[COLLATE collation][opclass][ASC|DESC][ NULLS { FIRST|LAST}][,..])

· [WITH(storage_parameter=value [,… ])]

· [ TABLESPACE tablespace_name]

· [ WHERE predicate]