PostgreSQL知识点总结

一、基本使用

1. 交互式终端psql

• 连接至数据库： psql -h <ip地址> -p <端口号>
• \d：查看所有表
• \d + 表名：查看表结构
• \timing：显示SQL语句执行时间

2. 表空间的使用

如果需要把不同的表放在不同的存储介质或不同的文件系统下，可以使用表空间

`CREATE TABLESPACE tablespace_name LOCATION 'directory'
[WITH (tablespace_option = value)]`

在创建数据库、表时可以指定表空间，以便将对象存储在特定的表空间上

表空间参数 tablespace_option ：如果在比 I/O子系统更快或更慢的磁盘上存储表空间，则配置项会变得很有意义

• seq_page_cost：在磁盘上顺序扫描获取一个页面的代价，默认1.0
• random_page_cost：在磁盘上随机扫描获取一个页面的代价，默认4.0
• effective_io_concurrency：可以执行并发异步磁盘 I/O 的数量

3. 创建数据库

CREATE DATABASE name

[ENCODING[=]encoding]
[TABLESPACE[=]tablespace_name]
[CONNECTION LIMIT[=]connlimit]

ENCODING：编码
TABLESPACE：表空间

4. 数据库表约束

创建数据库：

CREATE [[GLOBAL|LOCAL]{TEMPORARY|TEMP}|UNLOGGED] TABLE
[IF NOT EXIST] table_name([
{ column_name data_type [COLLATE collation][column_constraint
[...]]
|table_constraint
|LIKE source_table [like_option...]}
])
[INHERITS(parent_table[,...])]
[PARTITION BY{RANGE|LIST}({column_name|(expression)}
[COLLATE collation][opclass][,...])]
[WITH(storage_parameter[=value][,...])|WITH OIDS|WITHOUT OIDS]
[ON COMMIT{PERSERVE ROWS|DELETE ROWS|DROP}]
[TABLESPACE tablespace_name]

字段约束：

• 非空约束： NOT NULL
• 唯一约束： UNIQUE
• 主键约束： PRIMARY KEY
• 默认约束： DEFAULT
• 外键约束：deptid int not null REFERENCES department(deptid)
• Check约束：createtime timestamp CHECK(createtime > ‘1970-01-01- 00:00:00’)

表约束：

• 外键约束：FOREIGN KEY (b,c) REFERENCE tb_test01 (b1,c1)
• Check约束：CHECK(createtime < modifytime)

删除数据库表：

删除表时如果该表被其他表引用（外键），直接删除会报错，需添加 CASCADE 连带删除子表的外键约束 ： DROP TABLE employee CASCADE

二、数据类型和运算符

1. 数据类型

• 整型：smallint、integer、bigint

• 任意精度：numeric、decimal
  decimal(6,2)：表示总共位数6位，小数部分位数2位。如果插入的数字小数位数超过2，则自动进行四舍五入

• 浮点：real 6位精度、 double precision 15位精度、float(n) 指定精度。浮点类型会造成精度丢失，需要准确计算应该使用numeric类型。浮点类型特殊值（Infinity、-Infinity、NaN）

• 序列：smallserial、serial、bigserial 可用作自增主键

• 货币：money 可以接受 ‘￥10000.00’货币格式，货币符号由数据库 lc_monetary 参数决定

• 字符串：char(n)、varchar(n)、text 。n表示最多存储n个字符，text无长度限制

• 二进制：bytea

• 日期时间：

  • timestamp [with time zone]:8位日期时间
  • date:4位日期
  • time[with time zone]:时间
  • interval [fields]：时间间隔

  系统内部所有与时区相关的日期时间存的都是UTC时间，可以使用timezone参数指定本地时间

• 布尔：true、false、unknown

• 位串：bit(n) 、 bit varying(n)。n表示存储位的长度

• 枚举：CREATE TYPE week AS ENUM (‘Mon’、‘Tue’、‘Wed’)

• 几何类型：point、line、lseg、box、path、polygon、cricle

• JSON类型：

  • json：存储文本的精确复制。处理函数执行时需要解析
  • jsonb：存储为分解好的二进制格式，函数处理不需要解析效率高，且支持索引（建议使用）
    SELECT ‘{“bar”:bar, "balance:7.7"}’::jsonb;

• 范围类型：int4range、int8range、numrange、tsrange、tstzrange、daterange
  INSERT INTO range1 VALUES('[2017-01-01 07:00:00,2017-01-01 08:00:00]')

• 数组类型：

  • 定义数组类型 pay_by_month float[] 或 pay_by_month float array[]
  • 插入数组 INSERT INTO payment VALUES ‘{10000.23, 12030.2, 10000}’或
    INSERT INTO payment VALUES ARRAY[10000.23, 12030.2, 10000]
  • 查询数组时要注意PostgreSQL数组元素的下标从1开始
  • ANY、ALL运算：SELECT * FROM payment WHERE 10000 = ANY(pay_by_month);

2. 运算符

• 字符串运算符：
  • ||：字符串拼接 SELECT a||b FROM test_char;
  • LIKE：字符串匹配
  • SIMILAR TO：字符串匹配，使用正则表达式匹配
• 时间运算符：+、-、*
  • date’2001-09-28’ + inteval ‘1 hour’ -> timestamp ‘2001-09-28 01:00:00’
  • date’2001-09-28’ + time ‘03:00’ -> timestamp ‘2001-09-28 03:00:00’
• 布尔运算符： is [not] distinct from 、 is [not] null
• 几何运算符：平移、旋转、缩放…
  ​

三、表查询

1. 普通查询

[WITH [RECURSIVE] with_query [,...]]         //大型查询中使用辅助语句，常用于复杂查询和递归查询
SELECT [ALL|DISTINCT[ON(expression [,...])]]
[*|expression[[AS] output_name][,...]]
[FROM from_item [,...]]
[WHERE condition]
[GROUP BY grouping_element[,...]]
[HAVING condition[,...]]
[WINDOW window_name AS(window_definition)[,...]]
[{UNION|INTERSECT|EXCEPT}[ALL|DISTINCT]select]
[ORDER BY expression [ASC|DESC][NULLS {FIRST|LAST}][,...]]
[LIMIT{count|ALL}]
[OFFSET start[ROW|ROWS]]
[FETCH{FIRST|NEXT}[COUNT]{ROW|ROWS} ONLY]
[FOR{UPDATE|NO KEY UPDATE|SHARE|KEY SHARE}
[OF table_name[,...]][NOWAIT|SKIP LOCKED][...]]

• DISTINCT ON(expression[,…])：根据表达式中的字段进行分组，分组后仅返回每组的第一行（如果不指定ORDER BY，返回的第一条记录是不确定的）

// 根据 insuranceid和sex字段进行分组，返回每组sex最小的一行数据（包含 insuranceid, empid, insurant 字段）
SELECT DISTINCT ON (insuranceid, sex) insuranceid, empid, insurant FROM emp_order_insurance ORDER BY sex;

• ORDER BY：根据字段排序
  • NULLS {FIRST|LAST}：NULL值排序后放前面还是后面
• LIMIT：分页

翻页不能解决因为结果集过大导致的性能问题，因为翻页也是先排序再分页的，被OFFSET子句忽略的行仍然需要在服务器内部计算；

解决方案：

1. 根据业务实际需求，看能否替换为下一页，上一页的功能，特别在ios, android端，完全的分页是不常见的。 这里可以把limit, offset，替换为>辅助索引（即搜索条件）id的方式。该id再调用时，需要返回给前端。
2. 利用索引覆盖
   select column_name from table_name where id in (select id from table where second_index = xxx limit 10 offset 10000) 先在子查询中，查找数据对应的数据库唯一id值，因为主键在辅助索引上就有，所以不用回归到聚簇索引的磁盘去拉取。再通过这些已经被limit出来的10个主键id，去查询聚簇索引。这样只会十次随机io。 在业务确实需要用分页的情况下，使用该方案可以大幅度提高性能。通常能满足性能要求。

• EXISTS/NOT EXISTS：如果子查询至少返回一行，则返回true（子查询只运行到能判断它是否可以返回至少一行为止，而不用等到全部结束。结果只取决于是否返回行，而不取决于这些行的内容，所以子查询的输出列表通常是无关紧要的）

// 购买了保险号为1001的所有员工信息
SELECT empid,empname
FROM employee a
WHERE EXISTS(SELECT 1 FROM emp_order_insurance b WHERE a.empid = b.empid and b.insuranceid = 1001);

EXISTS里表用于和外表连接的字段一定要建立索引。因为EXISTS子查询需要的工作内存较少，优化器匹配到1条索引后即刻返回，所以会选择使用索引，性能非常好！

IN 和 EXISTS的比较：
使用in ,sql语句是先执行子查询，也就是先查询b表，在查a表，
而使用exists是先查主表a ,再查字表b;
对于主表数据较多时，我们使用in速度比exist更快，反之，从表b较大时，使用exist速度更快

• ANY/ALL：WHERE empid > ANY(SELECT empid FROM emp_order_insurance);

2. 模糊匹配查询

• LIKE/ILIKE：ILIKE会忽略大小写
• SIMILAR TO：字符串匹配，使用正则表达式匹配

模糊查询性能提升：

• 带前缀的模糊索引，如’南%'，可以使用B-tree索引进行优化
• 带后缀的模糊索引，如’%南’，可以使用reverse()反转函数建立函数索引优化
• 不带前缀和后缀的模糊查询，可以使用 pg_trgm的 gin 索引进行优化

3. 查询运算

• COUNT：
  • COUNT（*）：不忽略NULL值
  • COUNT（字段名）：忽略NULL值
  • COUNT(DISTINCT column) ：计算非重复结果的数目
• 分组集：

//分别按照deptid、sex和全部进行分组
SELECT deptid,sex,COUNT(*)
FROM employee
GROUP BY GROUPING SETS (deptid, sex, ());

ROLLUP 和 CUBE可以简化分组集的写法

• 集合运算：UNION [ALL]、 INTERSECT[ALL]、EXCEPT[ALL] ，加ALL则不会过滤重复的行

4. 窗口函数

窗口函数对表中一系列与当前行有某种关联的行执行跨行计算，这与聚集函数类似。但窗口函数不会使多行聚合成一个单独的输出行，这与聚集函数不同，在查询中输出的每一行仍然保持独立

function_name ([expression[,expression...]]) OVER (window_definition)

window_definition的格式：

PARTITION BY expression[,...]]
[ORDER BY expression[ASC|DESC]]

PARTITION BY子句将查询的行进行分组，窗口函数会对各个分组作独立的处理

// 查询各部门的员工在所属部门中的工资排名
SELECT empid,salary, deptid, row_number()over(partition by deptid order by salary desc)rnum
FROM employee
ORDEr BY deptid,rnum

empid|salary|deptid|rnum
10001|100000|100001|1
10002|80000 |100001|2
10003|60000 |100001|3
10004|100000|100002|1
10005|70000 |100002|2

常用的窗口函数：

• row_number()：行号
• rank()、dense_rank()：带间隙，不带间隙的当前行排名
• first_value()、last_value()：该分组的第一个值和最后一个值
• nth_value(value, integer)：该分组第几行的值

5. 高级操作

• 归并数据：UPSERT操作，用于解决数据插入过程中发生的冲突

insert into department(deptid, deptname)
values(1001,'xx')
on conflict(deptid)
do update set createtime = now()
[do nothing]

• 批量插入
  • INSERT INTO … SELECT …
  • BEGIN 多条insert END：一个事务里插入多条SQL语句
  • COPY table_name FROM ‘filename’:从外部文件导入数据，性能最好
• 关联更新：通过与其他表关联实现批量更新多条记录

//把a的department更新为b的department
UPDATE department a SET deptname = b.deptnam FROM
upd_dept b WHERE a.deptid = b.deptid;

• 关联删除：

DELETE FROM department a USING test b WHERE  a.deptid = b.deptid;

四、索引

1. 索引分类

• B-tree：默认索引类型。可用于等值查询、范围查询、前缀模糊匹配查询、查询结果集排序
• Hash：只能用于等值查询（如果只需要等值查询，建议使用Hash索引）
• GiST：平衡的树型结构访问方法，可作为基础模板实现任意索引模式。适用于几何类型，范围类型，IP类型，空间类型
• SP-GiSp：实现非平衡的基于硬盘的数据结构，适用于几何类型，范围类型，IP类型，空间类型
• GIN：通用倒排索引，存储的是键值和倒排表,高效全文和Json搜索。GIN允许用户开发自定义访问方法的数据类型索引
  GIN索引会为每个键建立一个B-tree索引，导致更新很慢，可通过参数调整。
• BRIN：块范围（物理上相邻的一组页面，可以指定数据块数）索引，每一个块范围都在索引中存储了一些统计信息。
  执行时先扫描满足查询的块范围，再检查块范围中满足查询的页面返回
  当被索引的列与物理存储的相关性很强，BRIN索引会有很好的效果，如时序数据，在时间或序列字段上创建BRIN索引

2.索引设计原则

1. 在经常用于查询的字段上建索引
2. 在经常用于连接的字段上创建索引
3. 在经常需要根据范围进行查询的列上创建索引
4. 在经常需要排序的列上创建索引
5. 不应该在查询很少使用的列上建索引
6. 对经常更新的表需要避免对其建立过多的索引
7. 不应该在数据量非常小的表上创建索引
8. 不应该在数据取值区分度很小的列上创建索引

3. 创建索引

CREATE [UNIQUE] INDEX [CONCURRENTLY]      # 构建索引时不阻塞写操作
ON table_name[USING method]               # 创建索引类型
({column_name|(expression)})              # 列名或表达式
WITH（storage_parameter = value, ...）     # 索引存储参数
[WHERE predicate]                         # 部分索引

索引存储参数：

• fillfactor：索引填充因子，百分数
• buffering、fastupdate、gin_pending_list_limit、pages-per_range、autosummarize

如：

CREATE INDEX empname_idx ON employee(epmid)             #普通索引
CREATE INDEX empname_idx ON employee(lower(empname));   #表达式索引
CREATE INDEX gin_idx ON employee USING GIN(telephone) WITH (fastupdate = off)   #GIN索引
CREATE INDEX alarm_patial_idx ON alarm_t(level) WHERE level='alarm';  #部分索引
CREATE UNIQUE INDEX test_success_idx ON test_table(test_class, testid) WHERE result; #部分唯一索引可以对表的部分数据进行唯一性约束

五、视图

1. 视图使用场景

• 将复杂查询定义为一个视图，方便后续查询
• 视图可以隐藏一些字段，提升安全性

2. 普通视图

# 普通视图
CREATE VIEW myview AS SELECT * FROM mytab;
#临时视图，Session结束时消失
CREATE TEMP VIEW myview AS SELECT * FROM mytab;
#视图重命名列
CREATE VIEW vw_employee(id, hiredate) AS SELECT empid, begindate FROM hr_employee;

当满足以下3个条件时，视图是自动可更新的：

• 视图的FROM列表只有一项，且必须是一个表或另一个可更新视图
• 视图定义的查询中最外层不包含WITH、DISTINCT、GROUP BY、HAVING、LIMIT、OFFSET
• 视图定义的查询中最外层不包含任何聚集函数、窗口函数、集合返回函数

自动可更新的视图上执行增删改命令会影响到底层引用的表。
如果可更新视图包含WHERE条件，则在该视图上的UPDATE、DELETE操作只会影响WHERE限制的行，可能会导致修改后的行不在视图中可见。同理INSERT命令可以插入不满足WHERE条件的行

CREATE  VIEW vw_employee AS SELECT empid,responsibilities FROM hr_employee WHERE  empid > 2;
# 该更新只会修改hr_employee表中 empid > 2 && responsibilities = 'DBA'的数据
UPDATE vw_employee SET empid = 0 WHERE responsibilities = 'DBA';

CHECK OPTION 可以阻止INSERT、UPDATE命令产生在视图中无法看到的行

# 不允许在视图中插入和产生 empid<=2 的行
CREATE  VIEW vw_employee AS SELECT empid,responsibilities FROM hr_employee WHERE  empid > 2 with CHECK OPTION;

3. 物化视图

物化视图是一个包含查询结果的数据库对象，类似于缓存计算代价昂贵的函数结果

普通视图和物化视图的区别：

1. 普通视图是虚表，实际是对底层表的映射，对它的查询和更新都会被转换成对底层表的操作。物化视图是实体表，查询时是直接查询物化视图中的缓存数据，因此性能较高
2. 物化视图有额外的存储空间开销，普通视图没有
3. 物化视图可以为任意列创建索引，普通视图不行

对底层表的修改不会实时反映到物化视图上，如果需要更新，需要通过REFRESH命令刷新数据

CREATE MATERIALIZED VIEW table_name AS query;  #创建物化视图
REFRESH MATERIALIZED VIEW view_name;           #刷新物化视图

六、触发器

当执行某种特定类型的操作时，会自动触发执行一个特殊函数，这个函数被称为触发器函数。
触发器常用来强化数据库的数据完整性约束或实现某种业务规则

1. 常规触发器

常规触发器依附于某个单独的表、视图或外部表

触发器分为：

• 行级触发器：在每条记录行被修改时会触发一次调用，返回值可以为null
• 语句级触发器：在执行一条SQL语句时会触发一次调用，返回值可以为null，也可以返回一条行记录

根据触发时机，可以分为 BEFORE触发器、AFTER触发器、INSTEAD OF 触发器

触发器函数：触发器函数被用于触发器中，触发器函数必须声明为一个不带参数的函数，返回类型为 trigger（触发器函数使用一个 TriggerData 结构类型变量作为其输入参数）

触发器语法：

CREATE [CONSTRAINT] TRIGGER name {BEFORE|AFTER|INSTEAD OF}{event [OR...]}
ON table_name
[FOR[EACH]{ROW|STATEMENT}]   # 指定行触发器或语句级触发器
[WHEN（condition)]           # 决定触发器函数是否被实际执行的表达式
EXECUTE PROCEDURE function_name(arguments)   # 指定触发器函数

1. 定义触发器函数：

CREATE FUNCTION process_employee_audit_function()
	RETURNS TRIGGER
	LANGUAGE plpgsql
AS $$
	BEGIN
		IF(TG_OP = 'DELETE') THEN
			INSERT INTO employee_audit select 'D',now(),user,old.empanme;   #通过old,new可以引用新旧行值
			RETURN old;
		ELSIF(TG_OP = 'UPDATE') THEN
			INSERT INTO employee_audit select 'U',now(),user,new.empanme;   
			RETURN new;
		ELSIF(TG_OP = 'INSERT') THEN
			INSERT INTO employee_audit select 'I',now(),user,new.empanme;   
			RETURN new;
		END IF;
		RETRUN NULL;
	END;
$$

行级BEFORE触发器函数：

• 返回值为null，则跳过当前操作，不做修改
• 返回一条行记录，则使用该记录行来插入或更新。可以用来修改将要被插入或更新的行。

行级AFTER触发器会忽略所有返回值

2. 创建触发器

CREATE TRIGGER employee_trigger
AFTER INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW
EXECUTE PROCEDURE process_employee_audit_function;    #在对表进行增删改查操作后记录操作日志

数据变更可见性：

• 行级、语句级BEFORE触发器看到的是修改前的旧值
• 行级、语句级AFTER触发器看到的是修改后的新值

2.事件触发器

事件触发器定义在某个数据库上，捕捉DDL事件

事件触发器支持的事件：

• ddl_command_start事件：在DDL命令（CREATE、ALTER、DROP）开始前触发
• ddl_command_end事件：在DDL命令执行完成后（事务提交前）触发
• table_rewrite：在表被ALTER命令的某些动作重写之前发生
• sql_drop：删除一个对象后触发

如果ddl_command_start事件触发器执行失败，则DDL命令不会执行，后续事件触发器不会触发
如果DDL命令执行失败，则不会触发ddl_command_end事件触发器
如果ddl_command_end事件触发器执行失败，则会回滚DDL命令

1. 定义触发器函数

CREATE FUNCTION event_trigger_drops_function()
	RETURNS EVENT TRIGGER
	LANGUAGE plpgsql
AS $$
	DECLARE
		Obj record;
	BEGIN
		INSERT INTO drop_audit    #记录删除操作日志
		SELECT classid,objid,objsubid,object_type,schema_name,object_name,object_identity,tg_tag,now()
		FROM pg_event_trigger_dropped_objects(); #通过pg_event_trigger_dropped_objects()函数可以返回被删除的对象列表
	END;
$$

2. 定义触发器

CREATE EVENT TRIGGER drop_evnet_trigger ON sql_drop WHEN tag in('drop table')
EXECUTE PROCEDURE event_trigger_drops_function();

在执行DROP TABLE 命令时，该触发器会被触发

七、表继承和表分区

1. 表继承

表继承的特性：

1. 父表的所有字段被子表所继承
2. 子表可以有自己的子表，层层继承
3. 一个子表允许从多个父表继承（多继承）

通过表继承可以实现不同层级业务对象的内在继承关系

表继承语法：

CREATE TABLE vehicles(
	name text PRIMARY KEY,
	color  text
)

CREATE TABLE bikes(
	size  float NOT NULL
)INHERITS（vehicles）

执行查询操作时，查询父表会把子表的数据一起查出。如果只想查父表数据，可以在表名前添加 ONLY 关键字。

# 会把子表中红色的车一起查出
SELECT * FROM vehicles where color = 'red';
# 仅查主表
SELECT * FROM ONLY vehicles;

删除数据时，可以通过父表删除子表的数据
插入数据时，插入哪个表数据就在哪个表中，插入父表不会更新子表
更新数据时，可以通过更新父表更新子表的数据，可以通过ONLY关键字仅更新父表

UPDATE ONLY vehicles SET weight=1160 WHERE name='truck002';

约束继承：子表只会继承检查约束和非空约束，不支持继承其他类型约束
控制访问权限继承：子表不会继承访问权限，但是通过查询父表依旧可以查到子表数据。为解决该问题需要使用PostgreSQL的行级安全策略去实现

父表和子表的存储空间是独立的。

2. 表分区

表分区是把逻辑上的大表分裂成多个更小的物理分片，以获得性能的提升。

表分区使用场景：

• 大部分频繁访问的记录集集中在表的某个局部范围，采用分区表使热点数据集中在少数分区，每个分区的索引较小，可以全部载入内存中，从而显著提升查询性能
• 对某个分区的顺序扫描的性能可能优于基于全表索引的随机IO
• 批量导入和删除某个分区的速度很快
• 可以将不常用的分区数据迁移到低速存储设备

适合进行表分区的估算法则：表的大小超过数据库服务器的物理内存

表分区的使用：
表分区分为范围分区和列表分区

1. 创建分区表（被拆分的表，本身不存储任何记录）

CREATE TABLE vehicle(
category  int   NOT NULL,
name       text,
color      text,
weight     float,
area        text,
mededate    date  NOT NULL
)PARTITION BY LIST(category)

2. 创建不同的表空间

CREATE TABLESPACE  bike_tb LOCATION '/pgdata/bikes_space';
CREATE TABLESPACE  cars_tb LOCATION '/pgdata/cars_space';

3. 为分区表创建分区

CREATE TABLE vehicle_bikes
PARTITION OF vehicle
FOR VALUES IN (0)
TABLESPACE bike_tb;

#在分区的基础上继续分区
CREATE TABLE vehicle_cars
PARTITION OF vehicle
FOR VALUES IN (1)
PARTITION BY RANGE(madedate)
TABLESPACE cars_tb;

CREATE TABLE vehicle_cars_before2014
PARTITION OF vehicle_cars
FOR VALUES FROM(MINVALUE) TO ('2013-12-31')
TABLESPACE cars_tb;

• 插入数据时，可以直接将数据记录插入到分区表中，记录会被自动路由到合适的分区中。不需要预先判断每条记录应该插入到哪个分区中
• 查询数据时，查询主表会把分区的数据一起查出
• 更新、删除数据时，分区表自动将更新删除操作路由到对应分区进行
• 不需要某个分区时，可以使用 DROP TABLE 分区名称 来删除整个分区数据，这样可以避免VACUUM空间回收开销

约束排除：检查每个分区的约束条件，试图通过分区条件证明该分区不可能包含任何满足WHERE条件的记录，从而不扫描该分区，提升查询性能。通过 constraint_exclusion 参数控制。建议基于分区表的WHERE条件尽可能带上分区键

注意事项：

• 分区表本身不存任何数据，数据存在各个分区上。因此不允许在分区表中创建索引、主键约束、唯一性约束、外键约束和排他约束。但是可以在各个分区上创建想要的索引或约束。
• 对分区字段的修改不允许导致该记录从一个分区迁移到另一个分区
• 不允许其他表外键引用分区表中的字段
• 不允许在分区表中使用ON CONFLICT子句，因为不存在跨越整个分区体系的唯一性约束

八、管理数据库模式

PostgreSQL数据库集群包含一个或多个数据库，每个数据库包含一个或多个模式（schema），模式包含表，以及其他类型的命名对象。 模式类似于操作系统中的目录

使用模式的原因：

• 允许多个用户使用同一个数据库而互不干扰
• 将数据库对象组织成逻辑组，以便更易管理
• 可将第三方应用程序的对象放入单独的模式中，防止与其他对象的名称发生冲突

模式语法：

# 创建模式
CREATE SCHEMA myschema;
# 为指定用户创建模式
CREATE SCHEMA myschema AUTHORIZATION user_name;

如果要创建或访问模式中的表，需要使用限定名；当操作数据库对象不指定模式时，系统默认将对象放入名为public的模式中

CREATE TABLE myschema.mytable (...);

如果不想操作表时加限定名，需要将模式名设置到当前搜索路径中

# 这样访问myschema的表是就不需要加限定名了
SET search_path TO myschema,public;

九、配置管理

PostgreSQL服务端配置文件是 postgresql.conf，所有PostgreSQL服务端参数都在该配置文件中进行集中管理。该文件通常放在$PGDATA下

1. 修改配置参数

PostgreSQL可以通过修改配置文件（最常见）、SQL命令、Shell命令来修改参数

所有配置文件的参数都可以在pg_settings系统视图中进行查看，视图中的context参数可以查看使参数生效的方式（是否需要重启）

select * from pg_settings where name = 'log_destination';

通过SQL命令设置参数：

• ALTER SYSTEM：改变全局参数值，会在postgresql.auto.conf文件中保存修改的配置项参数

ALTER SYSTEM SET work_mem = '8MB';
# 加载函数使配置生效
select pg_reload_conf();

postgresql.auto.conf的优先级高于postgresql.conf，重启PostgreSQL后依旧以postgresql.auto.conf文件中的值为准
可通过ALTER SYSTEM SET work_mem=default;命令将postgresql.auto.conf中的配置项去掉

• ALTER DATABASE：针对某个数据库设置其参数

ALTER DATABASE name SET parameter {TO|=}{value|DEFAULT}

• ALTER ROLE：针对某个用户或角色设置参数值

2. 连接和安全认证参数

• max_connections：数据库最大并发连接数，默认100
• tcp_keepalives_idle：一个TCP连接空闲多长时间会发送一个keepalive消息，0表示使用系统默认值

3. 缓存和存储参数

对于数据库来说，I/O是最大的性能瓶颈，使用缓存可以降低I/O频率，因此这些参数对性能调优非常重要

• shared_buffers：数据库服务使用共享内存缓冲区的大小。该缓冲区用于缓存数据库的数据块，合理的配置是操作系统内存的25%~40%。并不是越大越好，太大缓存效果不明显
• temp_buffers：每个数据库会话使用的临时缓冲区的最大空间，默认8M。可以在单独的session中对该参数进行设置，尤其是需要访问较大的临时表时，会有显著的性能提升。
• work_mem：工作内存（session级别）。负责内部的sort和hash操作，合适的work_mem大小能够保证这些操作在内存中进行，如果太小则需要频繁的与硬盘进行swap，极大的降低系统性能。需要对系统的监控数据进行分析，或通过explain analyze分析语句，做出最好的选择
• maintenance_work_mem：维护工作内存，针对数据库的维护操作，如索引维护，VACUUM操作

需要注意：
max_connections*work_mem + shared_buffers + temp_buffers + maintenance_work_mem + 操作系统所需内存
不能超过整个内存大小

• temp_file_limit：声明一个会话能用于临时文件的最大硬盘空间。超过该限制则取消该事务，默认没有限制
• huge_pages：是否开启巨页（Linux操作系统支持）。通过使用巨页减少内存页数量，节约页表占用空间，所需地址转换减少，提高内存访问的性能；也可以减轻CPU缓存的压力，提高系统整体性能。默认值为try，服务器启动时尝试使用巨页，使用失败则进行普通内存申请

巨页使用步骤：
1）查看Linux huge page页大小
grep Hugepage /proc/meminfo
HugepageSize： 2048KB
2) 根据shared_buffers计算需要多少huge page。 假设设置了24GB的shared_buffers，则需要 24GB/2MB = 12288
3) 设置 Linux huge page 页数
sysctl -w vm.nr_hugepages=12288
4) 在数据库配置文件中设置 huge_pages = on
5) 启动数据库
6) 查看当前使用了多少 huge page cat /proc/meminfo | grep -i huge

4. WAL日志参数

• fsync：表面数据库将调用fsync确保被更新的数据写入硬盘，默认为on。当数据库数据不是很重要时可设置为off
• wal_buffer：缓存WAL数据共享内存的大小

5. 错误日志参数

• log_destination：日志输出的格式
• log_rotation_age：一个日志文件的最大生命周期，经过一段时间后，创建一个新的日志文件
• log_rotation_size：单个日志文件的大小，超出该大小会创建新的日志文件
• log_min_duration_statement：记录所有执行时间超过该阈值的SQL语句，用于定位慢查询
• log_checkpoints：查看checkpoint的详细信息，定位是否是checkpoint导致系统变慢
• log_lock_waits：记录锁等待信息，排查是否是锁等待导致系统变慢

十、事务

1. 事务隔离级别

PostgreSQL的默认隔离级别为“读已提交”
可通过 show default_transaction_isolation 命令查看隔离级别

2. 事务使用

BEGIN主动开启事务
BEGIN [WORK|TRANSACTION] [transaction_mode[,...]]
...
SAVEPOINT savepoint_name
ROLLBACK [TO savepoint_name]      全部回滚/部分回滚
COMMIT

3. 事务实现原理

1）WAL机制：WAL（Write-Ahead Logging）在对数据进行任何变更之前，先确保对数据库的所有写操作都已经记录到WAL日志中，从而保证事务的更新操作不会丢失

作用：

• 实现事务的持久性、灾难恢复：即使数据库发生重启，也可以通过WAL日志进行恢复
• 提升数据库性能：采用WAL机制后，每次事务提交不必立即把更改的内存数据页持久化到硬盘中，只需把事务对应的WAL日志刷到硬盘即可。脏页会在适当时机再刷写到硬盘中。WAL日志是顺序写的，内存数据页是随机I/O，WAL机制把原本对数据文件的随机I/O转换成WAL日志文件顺序I/O，从而降低了事务提交时等待I/O的时延，提高了数据库的性能

2）MVCC机制：MVCC多版本并发控制，维护每一行记录的不同版本快照，读操作访问行记录的快照。使得每个事务都只看到与该事务启动时的快照视图相一致的版本
使用MVCC主要可以提高数据库访问的并发度，使读操作不阻塞写操作，写操作不阻塞读操作

快照版本是用事务ID来进行标识的，当一个事务启动时，PostgreSQL会为该事务分配一个唯一的事务ID，可以通过 SELECT txid_current() 查看

3）Checkpoint机制：在产生检查点时，所有的内存数据页都会被刷写到硬盘中，并且会在WAL日志中写入一条特殊的检查点记录，以确保在该检查点之前所有信息都已经写到数据文件中了
数据库发生崩溃后，恢复过程会找到最后的检查点记录，然后重做这个检查点之后产生的事务日志，把数据恢复到一致性状态。而检查点之前的WAL日志可以循环利用和删除。

Checkpoint相关参数：

• checkpoint_timeout：用来指定生成 Checkpoint 检查点的最大间隔时间
• checkpoint_completion_target：指定Checkpoint在两次 Checkpoints 间隔时间的 50% 内完成
• max_wal_size：指定最大WAL日志的容量大小

当达到 checkpoint_timeout 超时时间或 WAL日志的 max_wal_size 已经被填满，则会触发Checkpoint

设置的太小会导致频繁的Checkpoint，导致过多的硬盘吞吐。设置的太大会导致Checkpoint检查点出现极大的IO负载，影响数据库正常业务使用。

4）锁：PostgreSQL包含两个级别的锁：表锁和行锁

表锁：

• 最基本的共享锁SHARE和排他锁EXCLUSIVE
• MVVC多版本并发控制引入 ACCESS SHARE 和 ACCESS EXCUSIVE
• 处理表锁和行锁的关系，引入意向锁——意向共享锁、意向排他锁

行锁：

• FOR UPDATE：排他锁、写锁。会导致SELECT命令检索到的行被锁定。
• FOR NO KEY UPDATE：不会阻塞 SELECT FOR KEY SHARE
• FOR SHARE：共享锁、读锁
• FOR KEY SHARE：不会阻塞FOR NO KEY UPDATE

十一、优化SQL语句

1. 查询优化器原理

应用程序将SQL查询语句发送到PostgreSQL服务器后，执行如下操作：

1. 解析器对SQL语句进行语法检查和语义检查，生成查询树交给重写器
2. 重写器根据存储在系统表中的规则修改查询树，先把视图重写为对应的基础表，再把重写后的查询树交给优化器
3. 优化器根据查询树产生执行计划，交给执行器
4. 执行器执行查询计划树并返回查询结果

PostgreSQL主要进行两个阶段的优化：逻辑优化和物理优化

• 逻辑优化：在不改变语义的基础上改变查询树的节点位置和结构，从而提升SQL语句的执行效率
• 物理优化：在查询树上选择最优的查询路径，该查询路径中的物理访问代价最少

2. 逻辑优化

逻辑优化的顺序：

1. 对子查询进行优化
2. 对 WHERE、HAVING、ON等条件表达优化及等价谓词重写、
3. 对外连接进行优化

1）子查询优化：

• 对子查询进行上提（尽可能把子查询上提到父查询中，与父查询合并）
• 把选择出来的少量结果进行表间的连接操作，将表连接的操作数量降到最低，提高查询性能

可以使用EXPLAIN查看是否有SubPlan确定是否进行了子查询优化（有说明没优化）

PostgreSQL对ANY、SOME、EXIST、NOT EXIST基本支持子查询优化，对IN部分支持，不支持ALL和NOT IN
尽量使用支持优化的子查询SQL语句！

2）条件表达式优化及等价谓词重写：基于关系代数中的并、交、差等运算规则，对查询树的条件表达式进行优化
如把 employee.deptid=10 AND employee.deptid=department.deptid 重写为
employee.deptid=10 AND department.deptid =10

3）外连接优化： 把外连接转化为内连接，可以使表的连接顺序更随意，提高查询效率
可以通过EXPLAIN查看是否是 Hash Join 查看是否优化（Hash Left Join 是否优化为 Hash Join）

3. 物理优化

单表查询时会比较各种扫描方式的扫描代价，并选择代价较小的扫描方式作为表的物理查询方式

1）单表查询优化
会比较顺序扫描和索引扫描两种方式的扫描代价，并选择代价较小的路径

2）多表查询优化
多表连接有以下3种连接策略

• 嵌套循环连接（Nested Loop）:对外表进行扫描，找到的每一行在内表进行一次扫描。如果连接键上有索引则可以使用索引扫描，避免全表扫描
• 归并连接（Merge Semi Join）:对内表和外表的关联字段进行排序，然后对两个表进行同步向前扫描，匹配是否满足连接条件。两个表都只需要扫描一次，如果联结键上有索引，则可以避免排序的开销
• Hash连接：先对内表进行扫描创建关联字段的Hash表，然后扫描外表通过Hash表快速定位Hash中的匹配行（Hash连接对于小表联结的查询效率更高）

超过两个表则会先将某两个表连接，再连接其他表节点。为了在一定时间决定出使用一个合适的查询计划，物理优化会根据多表连接的个数（geqo_threshold）来选择是使用动态规划算法还是遗传算法

3）代价估算
总代价 = I/O代价 + CPU代价
减少I/O操作的成本，使查询尽可能在内存中运行，是成本优化的重要手段之一

成本的估算依赖于数据库的统计信息，统计信息是否准确是决定代价准确性的重要条件之一。AUTOVACUUM命令自动更新统计信息，也可以通过ANALYZE命令手动更新统计信息

4. 执行计划

EXPLAIN statement
EXPLAIN ANALYZE statement  //语句会被真实执行

explain输出解读：

QUERY PLAN
 _______________________________________
 Hash Join (cost=1.02..19.76 rows=10 width=12)（actual time=0.079..0.493 rows=11 loops=1）
 	-> Seq Scan on emp_order_insurance(cost=0.00..16.00 rows=1000 width=12)（actual time=0.017..0.493 rows=1000 loops=1）

Planing time:0.294 ms
Execution time:0.537ms

• cost：…前后分别为预计启动开销和总开销
• width：预计输出的行平均字节数
• actual time：实际启动时间和总运行时间
• rows：输出行的估计值
• loops：循环次数

阅读时建议优先读叶子节点，再依次读取父节点

扫描操作

节点	说明
Seq Scan	顺序扫描
Index Scan	索引扫描
Index Only Scan	索引只读扫描（不回表）
BItmap Index Scan	位图索引扫描（利用索引获得满足选择条件的元组的位图，直接读取索引文件，不读取数据文件）
BItmap Heap Scan	位图堆索引扫描 (利用Bitmap Index Scan 得到位图，获取元组，直接在数据文件中读取数据)
Subquery Scan	子查询扫描

位图扫描：一次性取出所有元组指针，并在内存以bitmap的机制进行排序保存，然后按照元组位置的顺序访问表，这样就把对堆数据的随机访问转换成了顺序访问。"bitmap"是进行排序的机制

连接操作

节点	说明
Nest [(type)] Loop	嵌套循环连接
Merge [(type)] Join	归并连接
Hash [(type)] Join	Hash连接

5. SQL优化

1）索引优化

• 创建索引：索引查询、索引覆盖
• 使用特色索引：使用GIN索引、GiST索引、SP-GisT、BRIN索引提高JSON、RANGE、几何空间、时间日期等数据类型的查询性能
• 避免索引失效的SQL写法（表达式、函数）

2）子查询优化

当使用子查询，或SQL语句中含有通用表达式、集合运算、聚集运算、HAVING子句时，可能会影响优化器对子查询语句的优化。在编写此类语句时，要注意通过EXPLAIN观察其对性能的影响

3）UNION ALL 代替 UNION
UNION ALL 不会进行去重，UNION会去重，当确认不会有重复数据或不需要去掉重复数据，建议使用UNION ALL代替UNION

十二、优化数据库服务端性能

衡量数据库性能的指标：

• 吞吐量：QPS、TPS
• 响应时间

影响数据库的硬件因素：CPU、内存、硬盘、网卡

1. 优化配置参数

1）内存资源类参数：见9.3

2）脏页刷写类参数优化

BgWriter进程用于辅助数据库在后台将脏页刷写到硬盘，定期刷写部分缓冲区的页面到硬盘中。
Checkpoint进程会把所有脏页刷写到硬盘中，通过BgWriter预先写出一些脏页，可以减少执行检查点时要进行的I/O操作，使操作系统的I/O负载趋于平稳

避免其他服务在需要读取新的页面到缓冲区时，因为缓冲区已满不得不自己执行刷脏页操作

相关参数：

• bgwriter-delay：指定后台 BgWriter 活动轮次之间的延迟。但当发现缓冲池中没有脏页时，将无视bgwriter-delay 配置进入更长的休眠时间，默认200ms
• bgwriter_lru_maxpages：每一轮次写脏页的最大个数，默认100
• bgwriter_lru_multiplier：下一轮刷写的脏页数，可以通过“最近所需新缓冲区数目的平均值*bgwriter_lru_multiplier”估算，最大不超过bgwriter_lru_maxpages。默认值为2，提供了一些缓冲，以应对需求高峰
• bgwriter_flush_after：如果BgWriter写入超过bgwriter_flush_after字节，则会尝试强制操作系统把这些写操作发送到底层存储器上。这样做可以限制内核页面缓存中脏数据的量，降低 CheckPoint 进程结束时发出 fsync 请求卡住的可能性，降低操作系统在后台大批量写回数据时卡住的可能性

3）空间回收类参数优化
PostgreSQL数据库操作中，不会自动回收已更新的或已删除的行占据的硬盘空间。因此，PostgreSQL会周期性地执行 VACUUM 命令回收硬盘空间，并且还会更新查询优化器使用的数据统计信息

PostgreSQL 默认打开 AUTOVACUUM 功能，相关参数：

• autovacuum_max_workers：同时运行自动清理进程个数最大值，默认为3
• autovacuum_vacuum_threshold：一个表上触发 VACUUM 所需的更新或删除元组的最小数量，默认为50个元组。不同的表可以设置不同的阈值
• autovacuum_vacuum_scale_factor：触发 VACUUM 的表中行数比例值
• autovacuum_freeze_max_age：表中事务最大寿命达到该参数时，会强制执行 VACUUM操作，默认为2亿

触发AUTOVACUUM的两个场景：

• 表上发生变化的行数 >= autovacuum_vacuum_scale_factor * 表上记录数 + autovacuum_vacuum_threshold
• 表上事务的最大寿命达到autovacuum_freeze_max_age

4）WAL日志参数优化

• fsync：WAL是否先写入硬盘，默认为on
• synchronous_commit：事务提交成功并在成功返回前是否需要等待 WAL日志文件刷写到硬盘，默认on
• full_page_write：检查点之后首次修改一个页面，会将该页面的全部内容刷写到WAL日志文件。可以在崩溃恢复时恢复整个完好的页面，为了保证页面的一致性，需要将该参数设置为on
• wal_buffers：存储WAL数据的内存大小，默认为share_buffers的1/32
• wal_writer_delay：刷新WAL日志文件的间隔，默认200ms
• wal_compression：会将全页刷写的数据先压缩再写到WAL日志文件中
• commit_delay、commit_sibling：一个事务发起提交请求时，如果数据库中正在执行的事务数量大于commit_sibling，则会在延迟commit_delay后再发起提交。用于一次提交多个事务，默认为0
• checkpoint_timeout：自动执行WAL CheckPoint 的最长时间间隔，默认300s。
• checkpoint_completion_target：完成Checkpoint所需时间占启动间隔总时间的比例，默认0.5
• checkpoint_flush_after：执行检查点时，如果有checkpoint_flush_after字节被写入，则强制操作系统将其缓存中的脏数据刷写到磁盘
• max_wal_size：WAL日志最大值，日志增大到指定大小后，操作系统会自动进行Checkpoint，默认1G

5）数据库性能视图

• pg_stat_activity：可以显示执行SQL语句的开始时间，事务的开始时间
• pg_stat_database：查看数据库的并发连接数、提交回滚事务数量、读写数据块的总时间、检测到的死锁个数、缓冲区命中数
• pg_stat_all_tables、pg_stat_user_tables：顺序扫描数、索引扫描数、扫描行数、增删改行数、清理时间
• pg_stat_user_indexes：索引视图