PostgreSQL学习笔记(下)

PostgreSQL学习笔记(下)

8、索引

8.1 创建索引

使用CREATE INDEX语句在已经存在的表中创建索引，基本语法结构为：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table name(col_name[length],...) [ASC | DESC]

CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication DATE NOT NULL
);

8.1.1 创建普通索引

CREATE INDEX bknameidx ON book(bookname);

8.1.2 创建唯一索引

CREATE UNIQUE INDEX uniqididx ON book(bookid);

8.1.3 创建单列索引

CREATE INDEX bkcmtidx ON book(comment);

8.1.4 创建组合索引

CREATE INDEX bkauandinfoidx ON book(authors,info);

8.1.5 创建Hash索引

# 语法
CREATE INDEX name ON table USING hash(column);

CREATE INDEX name ON book USING hash(authors);

8.2 重命名索引

ALTER INDEX public.bkauandinfoidx RENAME TO bkauandinfoidx_alter;

8.3 删除索引

# 语法
DROP INDEX index_name;

DROP INDEX bkauandinfoidx_alter;

9、视图

9.1 创建视图

9.1.1 创建视图的语法形式

创建视图使用 CREATE VIEW 语句，基本语法格式如下：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name[{column_list}]
AS SELECT_statement
[WITH [CASCADED|LOCAL] CHECK OPTION]

其中，CREATE表示创建新的视图；REPLACE表示替换已经创建的视图；ALGORITHM表示视图选择的算

法；view_name表示视图的名称；column_list表示属性列；SELECT statement 表示SELECT语句；

WITH[CASCADED|LOCAL]CHECK OPTION 表示视图在更新时，保证在视图的权限范围之内。

ALGORITHM的取值有3个，分别是 UNDEFINED | MERGE | TEMPTABLE。其中，UNDEFINED 表示 PostgreSQL 将

自动选择算法；MERGE 表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部

分；TEMPTABLE 将视图的结果存入临时表，然后用临时表来执行语句。

CASCADED与LOCAL 为可选参数，其中，CASCADED 为默认值，表示更新视图时要满足所有相关视图和表的条

件；LOCAL表示更新视图时，满足该视图本身定义的条件即可。

该语句要求针对视图的 CREATE VIEW权限，以及针对由 SELECT语句选择的每一列上的某些权限。对于在 SELECT

语句中其他地方使用的列，必须具有 SELECT 权限。如果还有OR REPLACE 子句，则必须在视图上具有DROP权

限。

9.2.2 在单表上创建视图

CREATE TABLE t(quantity INT,price INT);
INSERT INTO t VALUES(3,50);

CREATE VIEW view_t AS SELECT quantity,price,quantity*price FROM t;

SELECT * FROM view_t;

quantity|price|?column?|
--------+-----+--------+
       3|   50|     150|

CREATE VIEW view_t2(gty,price,total) AS SELECT quantity,price,quantity
*price FROM t;

SELECT * FROM view_t2;

gty|price|total|
---+-----+-----+
  3|   50|  150|

9.2.3 在多表上创建视图

CREATE TABLE student
(
s_id INT,
name VARCHAR(40)
);

CREATE TABLE stu_info
(
s_id INT,
glasS VARCHAR(40),
addr VARCHAR(90)
);

INSERT INTO student VALUES(1,'wanglinl'),(2,'gaoli'),(3,'zhanghai');
INSERT INTO stu_info VALUES(1,'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong');

CREATE VIEW stu_glass(id,name,glass) AS SELECT student.s_id,student.name,stu_info.glass
FROM student,stu_info WHERE student.s_id=stu_info.s_id;

SELECT * FROM stu_glass;

id|name    |glass |
--+--------+------+
 1|wanglinl|wuban |
 2|gaoli   |liuban|
 3|zhanghai|qiban |

9.2 查看视图

SELECT * FROM information_schema.views;

table_catalog|table_schema      |table_name                           |view_definition                                                                                                                                                                                                                                                |check_option|is_updatable|is_insertable_into|is_trigger_updatable|is_trigger_deletable|is_trigger_insertable_into|
-------------+------------------+-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+------------------+--------------------+--------------------+--------------------------+
sample_db    |pg_catalog        |pg_shadow                            | SELECT pg_authid.rolname AS usename,¶    pg_authid.oid AS usesysid,¶    pg_authid.rolcreatedb AS usecreatedb,¶    pg_authid.rolsuper AS usesuper,¶    pg_authid.rolreplication AS userepl,¶    pg_authid.rolbypassrls AS usebypassrls,¶    pg_authid.rolpasswo|NONE        |NO          |NO                |NO                  |NO                  |NO                        |
sample_db    |pg_catalog        |pg_roles                             | SELECT pg_authid.rolname,¶    pg_authid.rolsuper,¶    pg_authid.rolinherit,¶    pg_authid.rolcreaterole,¶    pg_authid.rolcreatedb,¶    pg_authid.rolcanlogin,¶    pg_authid.rolreplication,¶    pg_authid.rolconnlimit,¶    '********'::text AS rolpassword,¶|NONE        |NO          |NO                |NO                  |NO                  |NO                        |

9.3 删除视图

# 语法
DROP VIEW[IF EXISTS]
view_name [,view_name]...
[RESTRICT|CASCADE]

DROP VIEW IF EXISTS stu_glass;

10、触发器

10.1 什么是触发器和触发器函数

一个触发器是一种声明，告诉数据库应该在执行特定操作时执行特定的函数。触发器的执不需要使用CALL 语句来

调用，也不需要手工启动，只要当一个预定义的事件发生时，PostgreSQL就会自动调用它。

触发器可以定义在一个INSERT、UPDATE 或 DELETE 命令之前或者之后执行。如果定义INSERT之前，表明在数据

库插入之前，先调用触发器，再执行触发器函数。

所谓触发器函数，是指一个没有参数并且返回 trigger 类型的函数。在创建触发器之前，首需要创建触发器函数。

创建触发器函数的基本语法格式如下：

CREATE FUNCTION fun_name() RETURNS trigger AS $fun_name$
	BEGIN
		函数执行代码;
	END;
$fun_name$ LANGUAGE plpgsql;

其中，fun_name 为触发器函数的名称。

触发器函数创建完成后，可以使用 CREATE TRIGGER 创建触发器。触发器通常还分为BEFORE触发器和 AFTER触

发器。BEFORE触发器通常在语句开始做任何事情之前触发，AFTER触发器则在语句结束时触发。同一个触发器函

数可以用于多个触发器。

10.2 创建触发器

创建一个触发器的语法如下：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW EXECUTE PROCEDURE 触发器函数;

其中，“触发器名”参数用于指定触发器的名字；BEFORE 和 AFTER 指定触发器执行的时间。如果选择BEFORE，表

明在触发事件之前执行触发语句；选择AFTER，则表明在触发事件之后执行触发语句；“触发事件”参数是指触发的

条件，包括 INSERT、UPDATE 和 DELETE；“表名”参数指触发事件操作的表的名称；FOR EACH ROW 表示任何一

条记录上的操作满足触发事件都会触发触发器；“触发器函数”参数是指触发器被触发后执行的函数。

# 创建一个触发器,使得每次该表有新数据insert时,其中的一个时间字段uptime自动变更为当前时间。
CREATE TABLE timedb(uid INTEGER,gid INTEGER,uptime timestamp with time zone);

# 创建一个触发器函数
CREATE FUNCTION func_timedb() RETURNS trigger AS $func_timedb$
	BEGIN
		If (TG_OP = 'UPDATE') THEN
			If NEW.uptime=OLD.uptime Then
				return null;
			END IF;
		END IF;
		update timedb set uptime= NOW() where uid = NEW.uid and gid = NEW.gid;
		return null;
	END;
$func_timedb$ LANGUAGE plpgsql;

# 创建触发器
CREATE TRIGGER timedb_updateTime AFTER INSERT ON timedb
FOR EACH ROW EXECUTE PROCEDURE func_timedb();

# 插入数据
INSERT INTO timedb VALUES(1,3);

select * fromtimedb;

uid|gid|uptime                       |
---+---+-----------------------------+
  1|  3|2022-09-12 18:49:55.734 +0800|

10.3 查看和修改触发器

ALTER TRIGGER timedb_updateTime ON timedb  RENAME TO timedb_updateTime_alter;

10.4 触发器的使用

CREATE TABLE account
(
id int,
name char(20)
);

CREATE FUNCTION account_stam() RETURNS trigger AS $account_stam$
	BEGIN
		IF NEW.name IS NULL THEN
			RAISE EXCEPTION 'name 字段不能为空值';
		END IF;
	END;
$account_stam$ LANGUAGE plpgsql;

CREATE TRIGGER account_stamp BEFORE INSERT ON account
FOR EACH ROW EXECUTE PROCEDURE account_stam();

INSERT INTO account VALUES(10);

Error occurred during SQL query execution

原因：
 SQL 错误 [P0001]: 错误: name 字段不能为空值
  在位置：在RAISE的第4行的PL/pgSQL函数account_stam()

10.5 删除触发器

# 语法
DROP TRIGGER trigger_name ON schema_name

DROP TRIGGER timedb_updateTime_alter ON timedb;

10.6 触发器使用案例

下面是创建触发器的实例，每更新一次 studentl 表的数据，都要更新 studentl_stats 表对应的字段。

student1表主要用来存储学生的分数表；student1_stats 表主要用来存储专业状态表，存储哪个专业有多少学

生报名。

CREATE TABLE student1
(
sutnum varchar(30) NOT NULL,
major varchar(20) NOT NULL,
score int NOT NULL
);

CREATE TABLE student1_stats
(
major varchar(20) NOT NULL,
total_score integer NOT NULL,
total_students integer NOT NULL
);

CREATE OR REPLACE FUNCTION fun_student_major() RETURNS trigger AS $BODY$ DECLARE rec record;
	BEGIN
		DELETE FROM student1_stats; --将统计表里面的旧数据清空
		FOR rec IN(SELECT major,sum(score) as total_score,count(*) as total_students FROM student1 GROUP BY major) LOOP
		INSERT INTO student1_stats VALUES(rec.major,rec.total_score,rec.total_students);
		END LOOP;
		return NEW;
	END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE

CREATE TRIGGER fun_student_major AFTER INSERT OR UPDATE OR DELETE ON student1
FOR EACH ROW EXECUTE PROCEDURE fun_student_major();

INSERT INTO student1 VALUES('10010','英语',90),('10011','数学',86),
('10012','物理',70),('10013','语文',95);

SELECT * FROM student1_stats;

major|total_score|total_students|
-----+-----------+--------------+
语文   |         95|             1|
英语   |         90|             1|
物理   |         70|             1|
数学   |         86|             1|

UPDATE student1 SET score=89 WHERE major='语文';

SELECT * FROM student1_stats;

major|total_score|total_students|
-----+-----------+--------------+
语文   |         89|             1|
英语   |         90|             1|
物理   |         70|             1|
数学   |         86|             1|

DELETE FROM student1 WHERE major = '物理';

SELECT * FROM student1_stats;

major|total_score|total_students|
-----+-----------+--------------+
语文   |         89|             1|
英语   |         90|             1|
数学   |         86|             1|

11、事务处理与并发控制

11.1 事务介绍

事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性，称为原子性(Atomic)、一致

性(Consistent)、隔离性(Isolated)和持久性(Durable)属性，简称ACID属性，只有这样才能成为一个事务。

• 原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

• 一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的

  修改，以保持所有数据的完整性，事务结束时，所有的内部数据结构都必须是正确的。

• 隔离性：由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态，

  要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数

  据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事

  务执行的状态相同。

• 持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

11.1.1 事务块管理的常用语句

在 PostgreSQL中，一个事务是通过把 SQL 命令用 BEGIN和COMMIT 命令包围实现的。语法格式如下：

BEGIN;
SQL语句1;
...
COMMIT;

事务块是指包围在BEGIN 和 COMMIT 之间的语句。在PostgreSQL9中，常用的事务快管理语句的含义如下。

• START TRANSACTION：此命令表示开始一个新的事务块。

• BEGIN：此命令表示初始化一个事务块。在 BEGIN 命令后的语句都将在一个事务里面执行，直到出现

  COMMIT或ROLLBACK，此命令和 START TRANSACTION是等价的。

• COMMIT：此命令表示提交事务。

• ROLLBACK：此命令表示事务失败时执行回滚操作。

• SET TRANSACTION：设置当前事务的特性，对后面的事务没有影响。

BEGIN和COMMIT同时使用，用来标识事务的开始和结束。

11.1.2 事务的应用案例

SELECT * FROM person;

id|name|age|info|
--+----+---+----+

BEGIN;
INSERT INTO person VALUES(1003,'路飞',80,'10456354');
INSERT INTO person VALUES(1004,'张露',85,'56423424');
INSERT INTO person VALUES(1005,'魏波',70,'41242774');
ROLLBACK TRANSACTION;
COMMIT;

SELECT * FROM person;

id|name|age|info|
--+----+---+----+

11.2 PostgreSQL的并发控制

PostgreSQL 支持多用户共享同一数据库，但是，当多个用户对同一个数据库进行修改时，会产生并发问题，所以

并发控制的目标就是保证所有的会话可以高效地访问，同时还需要维护数据的完整性。

数据库中数据的并发操作经常发生，而对数据的并发操作会带来下面一些问题：脏读、幻读、不可重复性读取、丢

失更新。

1、脏读

当一个事务读取的记录是另一个事务的一部分时，如果第一个事务正常完成，就没有什么问题，如果此时另一个事

务尚未完成，就产生了脏读。

2、幻读

当某一数据行执行 INSERT 或 DELETE 操作，而该数据行恰好属于某个事务正在读取的范围时，就会发生幻读现

象。

3、不可重复性读取

如果一个事务不止一次的读取相同的记录，但在两次读取中间有另一个事务刚好修改了数据，则两次读取的数据将

出现差异，此时就发生了不可重复性读取。

PostgreSQL 为开发者提供了丰富的对数据并发访问进行管理的工具。PostgreSQL 利用多版本控制(MVCC)来维护

数据的一致性。这就意味着当检索数据时，每个事务看到的都只是一小段时间之前的数据快照(一个数据库版本)，

而不是数据的当前状态。这样，如果对每个数据库会话进行事务隔离，就可以避免一个事务看到其他并发事务的更

新而导致不一致的数据。

在PostgreSQL中，定义了4个事务隔离级别：

隔离级别	脏读	幻读	不可重复性读取
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	可能	不可能
可串行读	不可能	不可能	不可能

在PostgreSQL中，可以请求4种可能的事务隔离级别中的任意一种。但是在内部，实际上只有两种独立的隔离级

别，分别对应读已提交和可串行化。如果选择了读未提交的级别，实际上你用的是读已提交，在选择可重复读级别

的时候，实际上用的是可串行化，所以实际的隔离级别可能比选择的更严格。这是SQL 标准允许的：4 种隔离级别

只定义了哪种现象不能发生，但是没有定义哪种现象一定发生。PostgreSQL 只提供两种隔离级别的原因是，这是

把标准的隔离级别与多版本并发控制架构映射相关的唯一合理方法。

PostgreSQL 中的两种级别的隔离如下。

1、读已提交

读已提交是 PostgreSQL 里的默认隔离级别。当一个事务运行在这个隔离级别时，一个SELECT查询只能看到查询

开始之前已提交的数据，而无法看到未提交的数据或者在查询执行期间其他事务已提交的数据。

如果两个事务在对同一组数据进行更新操作，第二个事务需要等待第一个事务提交或者更新回滚。如果第一个事务

进行提交，系统将重新计算查询条件，如果符合条件，则第二个事务继续进行更新操作；如果第一个事务进行更新

回滚，那么它的作用将被忽略，而第二个事务将继续更新最初发现的行。

2、可串行化

可串行化级别提供最严格的事务隔离。这个级别模拟串行的事务执行,就好像事务将被一个接着一个串行(而不是并

行)地执行。不过，使用这个级别的应用必须准备在串行化失败的时候重新启动事务。

如果两个事务在对同一组数据进行更新操作，串行化事务将等待第一个正在更新的事务提交或者回滚。如果第一个

事务提交了，那么串行化事务将回滚，从头开始重新进行整个事务；如果第一个事务回滚，那么它的影响将被忽

略，这个可串行化的事务就可以在该元组上进行其更新操作。

11.3 锁机制

PostgreSQL 的多版本控制(MVCC)并不能解决所有的并发控制情况，所以还需要使用传统数据库中的锁机制来保证

事务的并发。使用锁可以解决用户存取数据的这个问题，从而保证数据库的完整性和一致性。

11.3.1 锁的类型

PostgreSQL中提供了3种锁模式，分别为SpinLock、LWLook和 RegularLock。

1、SpinLock(自旋锁)

SpinLock 使用互斥信号,与操作系统和硬件环境联系比较密切。SpinLock 锁封锁的时间很短，没有等待队列和死

锁检测机制。另外事务结束时，不能自动释放SpinLock锁。

2、LWLock(轻量级锁)

LWLock 主要提供对共享存储器的数据结构的互斥访问。特点主要是有等待队列和无死锁检测。另外事务结束时，

可以自动释放LWLock锁。LWLock锁分为排他模式和共享模式。

• 排他模式—用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时对同一资源进行多重更

  新。

• 共享模式—用于读取数据操作,允许多个事务读取相同的数据,但不允许其他事务修改

当前数据，如SELECT语句。当多个事务读取一个资源时，资源上存在共享锁,任何其他事务都不能修改数据，除非

将事务隔离级别设置为可重复读或者更高的级别，或者在事务生存周期内用锁定提示对共享锁进行保留，那么，一

旦数据完成读取,资源上的共享锁立即得以释放。

3、RegularLock(常规锁)

RegularLock 为一般数据库事务管理中所指的锁。主要特点为有等待队列、有死锁检测和能自动释放锁。

RegularLock 支持的锁的模式有8种,按排他级别从低到高分别如下。

• ACCESS SHARE(访问共享锁)：查询命令(SELECT)将会在它查询的表上获取访问共享锁。一般地,任何一个对表

  上的只读查询操作都将获取这种类型的锁。此模式的锁和ACCESS EXCLUSIVE(访问排他锁)是冲突的。

• ROW SHARE(行共享锁)：使用 SELECT FOR UPDATE或SELECT FOR SHARE语句将获得行共享锁。另外此锁和

  EXCLUSIVE(排他锁)和ACCESS EXCLUSIVE(访问排他锁)是冲突的。

• ROW EXCLUSIVE(行排他锁)：使用 UPDATE、 DELETE或INSERT命令会在目标表上获得行排他锁，并且在其

  他被引用的表上加上ACCESS SHARE锁。一般的，更改表数据的命令都将在这张表上获得 ROW EXCLUSIVE

  锁。另外此锁和 SHARE(共享锁)、SHARE ROW EXCLUSIVE(共享行排他锁)、EXCLUSIVE(排他锁)和ACCESS

  EXCLUSIVE(访问排他锁)是冲突的。

• SHARE UPDATE EXCLUSIVE(共享更新排他锁)：使用 VACUUM(不带 FULL 选项)ANALYZE或CREATE INDEX

  CONCURRENTLY语句时使用共享更新排他锁。

• SHARE(共享锁)：使用CREATE INDEX(不带 CONCURRENTLY选项)语句请求时使用共享锁。

• SHARE ROW EXCLUSIVE(共享行排他锁)：和排他锁类似,但是允许行共享。

• EXCLUSIVE(排他锁)：阻塞行共享和使用 SELECT FOR UPDATE语句时使用排他锁.

• ACCESS EXCLUSIVE(访问排他锁)：使用 ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER或

  VACUUM FULL 语句会获得访问排他锁。在 Lock table 命令中，如果没有声明其他模式,它也是默认模式。

11.3.2 死锁

在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死

锁，此时系统处于死锁状态。

1、死锁的原因

在多用户环境下，死锁的发生是由于两个事务都锁定了不同的资源、而同时又都在申请对方锁定的资源，即一组进

程中的各个进程均占有不会释放的资源，但因互相申请其他进程占用的不会释放的资源而处于一种永久等待的状

态。形成死锁有4个必要条件：

• 请求与保持条件-获取资源的进程可以同时申请新的资源。

• 非剥夺条件-已经分配的资源不能从该进程中剥夺。

• 循环等待条件-多个进程构成环路,并且其中每个进程都在等待相邻进程正占用的资源,

• 互斥条件-资源只能被一个进程使用。

2、可能会造成死锁的资源

每个用户会话可能有一个或多个代表它运行的任务，其中每个任务可能获取或等待获取各种资源。以下类型的资源

可能会造成阻塞，并最终导致死锁。

• 锁：等待获取资源(如对象、页、行、元数据和应用程序)的锁可能导致死锁。例如，事务T1在行rl上有共享锁

  (S锁)并等待获取行r2的排他锁(X锁)，事务T2在行r2上有共享锁(S锁)并等待获取行rl的排他锁(X锁)。这将导致

  一个锁循环，其中，T1和T2都等待对方释放已锁定的资源。

• 工作线程：排队等待可用工作线程的任务可能导致死锁。如果排队等待的任务拥有阻塞所有工作线程的资源，

  则将导致死锁。例如，会话SI启动事务并获取行rl的共享锁(S锁)后，进入睡眠状态。在所有可用工作线程上运

  行的活动会话正尝试获取行rl的排他锁(X锁)，因为会话S1 无法获取工作线程，所以无法提交事务并释放行rl的

  锁，这将导致死锁。

• 内存：当并发请求等待获得内存，而当前的可用内存无法满足其需要时，可能发生死锁。例如，两个并发查询

  (Q1和Q2)作为用户定义函数执行,分别获取10MB和20MB的内存。如果每个查询需要30MB而可用总内存为

  20MB，则Q1和Q2必须等待对方释放内存，这将导致死锁。

• 并行查询执行的相关资源，通常与交换端口关联的处理协调器、发生器或使用者线程至少包含一个不属于并行

  查询的进程时，可能会相互阻塞，从而导致死锁。此外，当并行查询启动执行时，PostgreSQL 将根据当前的

  工作负荷确定并行度或工作线程数。如果系统工作负荷发生意外更改，例如，当新查询开始在服务器中运行或

  系统用完工作线程时，则可能发生死锁。

3、减少死锁的策略

复杂的系统中不可能百分之百地避免死锁，从实际出发为了减少死锁，可以采用以下策略：

• 在所有事务中以相同的次序使用资源。

• 使事务尽可能简短并且在一个批处理中。

• 为死锁超时参数设置一个合理范围，如3-30分钟；超时，则自动放弃本次操作，避免进程挂起。

• 避免在事务内部和用户进行交互，减少资源的锁定时间。

• 使用较低的隔离级别，相比较高的隔离级别能够有效减少持有共享锁的时间，减少锁之间的竞争。

11.4 锁的应用案例

在PostgreSQL中，使用LOCK 命令锁定一个表。具体语法如下：

LOCK [ TABLE ] name [,...][ IN lockmode MODE][ NOWAIT]

其中，name为要锁定的现存表的名称；lockmode 为锁模式，声明这个锁和哪些锁冲突，如果没有声明锁模式，

默认模式为 ACCESS EXCLUSIVE模式；NOWAIT声明LOCK TABLE不去等待任何冲突的锁释放，如果无法不等待获

取所要求的锁,那么事务退出。

命令LOCK TABLE a,b;等效于LOCK TABLE a;LOCK TABLEb;。表是按照LOCK TABLE 命令中声明的顺序一个接

一个上锁的。

BEGIN;
LOCK TABLE person IN SHARE MODE;
SELECT id FROM person WHERE name='Star Wars:Episode I - The Phantom Menace';
-- 如果记录没有返回则ROLLBACK
INSERT INTO person user_comments VALUES (_id_,'GREAT! I was waiting for it for so long!');
COMMIT;

BEGIN;
LOCK TABLE person IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM person_user_comments WHERE id IN (SELECT id FROM films WHERE rating<5);
DELETE FROM person WHERE rating<5;
COMMIT;

12、PostgreSQL用户管理

PostgreSQL 是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定允许的权限。一个数据库

用户可以有一系列属性，这些属性定义它的权限，以及与客户认证系统的交互。本章将向读者介绍 PostgreSQL 用

户管理中的相关知识点，包括：组角色管理、账户管理和权限管理。

12.1 组角色管理

在 PostgreSQL 9中，使用组角色的概念管理数据库访问权限。

12.1.1 创建组角色

一个组角色可以看作是一组数据库用户。组角色可以拥有数据库对象(比如表)，以及可以把这些对象上的权限赋予

其他角色，以控制谁拥有访问哪些对象的权限。

# 语法
CREATE ROLE name;

CREATE ROLE post2;

默认情况下，新建立的数据库总是包含一个预定义的“超级用户”角色，并且默认这个组角色名为postgres。

12.1.2 查看和修改组角色

角色存储在pg_roles 系统表中，用户可以通过查看此表来查看系统中的角色。

SELECT rolname FROM pg_roles;

rolname                  |
-------------------------+
pg_database_owner        |
pg_read_all_data         |
pg_write_all_data        |
pg_monitor               |
pg_read_all_settings     |
pg_read_all_stats        |
pg_stat_scan_tables      |
pg_read_server_files     |
pg_write_server_files    |
pg_execute_server_program|
pg_signal_backend        |
postgres                 |
post2                    |

# 语法
ALTER ROLE 组角色名称 RENAME TO 新的组角色名称;

ALTER ROLE post2 RENAME TO post3;

12.1.3 删除组角色

# 语法
DROP ROLE 组角色的名称;

DROP ROLE Post3;

12.2 角色的各种权限

一个数据库角色可以有一系列属性，这些属性定义它的权限，以及与客户认证系统的交互。

常见的权限如下。

12.2.1 LOGIN(登录)

默认情况下，创建的组角色没有登录权限。只有具有LOGIN权限的组角色才可以用作数据库连接的初始角色名。

一旦组角色拥有了登录权限，即可当作用户名一样来使用。

# 创建具有登录权限的组角色,SQL语法如下
CREATE ROLE name LOGIN;

CREATE ROLE post1 LOGIN;

12.2.2 SUPERUSER(超级用户)

SUPERUSER 拥有对数据库操作的最高权限，可以完成对数据库的所有权限检查。为了保证PostgreSQL 的安全，

建议用户谨慎使用 SUPERUSER，不要轻易创建它，最好使用非超级用户完成用户的大多数工作。

# 创建数据库超级用户,SQL语法如下
CREATE ROLE name SUPERUSER;

CREATE ROLE post2 SUPERUSER;

只有超级用户才有权限创建超级用户。

12.2.3 CREATEDB(创建数据库)

角色要想创建数据库，必须明确给出该权限。当然是除了超级用户以外，因为超级用户已经具有所有的权限。

# 创建具有创建数据库权限的组角色,SQL语法格式如下
CREATE ROLE name CREATEDB;

CREATE ROLE post3 CREATEDB;

12.2.4 CREATEROLE(创建角色)

角色要想创建角色，必须明确给出该权限(除了超级用户以外)。一旦角色具有CREATEROLE权限，即可更改和删除

其他角色，还可以给其他角色赋予或者撤销成员关系。当然，如果想对超级用户进行操作，仅有此权限还不够，还

必须拥有SUPERUSER 权限。

# 创建具有创建角色权限的组角色,SQL语法格式如下
CREATE ROLE name CREATEROLE;

CREATE ROLE post4 CREATEROLE;

12.2.5 口令

在客户认证方法要求与数据库建立连接时，需要口令权限。常见的认证方法包括 password、md5 和 crypt。

# 创建具有口令权限的组角色,SQL语法格式如下
CREATE ROLE name 口令认证方法 具体口令

CREATE ROLE post5 PASSWORD '123456';

12.3 账户管理

在PostgreSQL 中可以管理用户账号，包括创建用户、删除用户、密码管理等内容。PostgreSQL数据库的安全

性，需要通过账户管理来保证。

12.3.1 创建用户

用户是具有登录权限的组角色。

# 创建普通用户的SQL语法如下
CREATE USER name;

# 这和下面语句的作用是等价的
CREATE ROLE name LOGIN

# 创建用户名称为postgre02,并具有创建数据库和创建角色的权限,同时登录密码为'123456789'
CREATE USER postgre02 PASSWORD '123456789' CREATEDB CREATEROLE;

使用下面的语句，可以达到同样的目的：

CREATE ROLE postgre02 LOGIN PASSWORD '123456789' CREATEDB CREATEROLE;

12.3.2 删除用户

要想删除用户，必须拥有CREATEROLE权限。

# 删除用户的SQL语法如下
DROP USER user[,user];

DROP USER postgre02;

12.3.3 修改用户密码

# 语法
ALTER USER 用户名 口令认证方式 新密码;

ALTER USER postgre01 PASSWORD '123123';

12.4 组角色和用户权限管理

权限管理主要是对登录到 PostgreSQL 的用户进行权限验证。所有用户的权限都存储在PostgreSQL 的权限表中。

不合理的权限规划会给 PostgreSQL服务器带来安全隐患。

12.4.1 对组角色授权

# 用户还可以使用 ALTER ROLE 对角色进行授权,语法格式如下
ALTER ROLE 角色名 权限1 权限2 ...;

ALTER ROLE post1 CREATEDB CREATEROLE;

12.4.2 对用户授权

# 用户还可以使用ALTER USER对用户进行授权,语法格式如下
ALTER USER 用户名 权限1 权限2 ...;

ALTER USER postgre01 CREATEDB CREATEROLE;

12.4.3 收回组角色权限

收回组角色权限就是取消已经赋予组角色的某些权限。

ALTER ROLE post1 NOCREATEDB NOCREATEROLE;

12.4.4 收回用户权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。

ALTER USER postgre01 NOCREATEDB NOCREATEROLE;

12.5 数据库权限管理

数据库管理员要对所有用户的权限进行合理规划管理。数据库的操作权限主要包括 SELECT、INSERT、UPDATE、

DELETE、REFERENCES和 TRIGGER等。PostgreSQL 权限系统的主要功能是证实连接到一台给定主机的用户，并

且赋予该用户在数据库上的各种权限。

12.5.1 修改数据库的拥有者

在创建对象的时候,它会被赋予一个拥有者。通常拥有者就是执行创建语句的角色，用户可以根据需要修改数据库

的拥有者。

# 用户还可以使用SQL语句修改数据库的拥有者,语法格式如下
ALTER DATABASE 数据库名称 OWNER TO 拥有者名称;

ALTER DATABASE mytest OWNER TO post1;

12.5.2 增加用户的数据表权限

默认情况下，只有数据库的所有者可以对其中的数据表进行操作。要允许其他用户使用这个数据表，必须赋予相应

的权限。

GRANT 权限 ON 数据表 TO 用户名称;

GRANT UPDATE ON ppol TO postgres;

# 赋予所有角色
GRANT UPDATE ON PPOl TO PUBLIC;
GRANT SELECT,UPDATE,INSERT ON mystudent TO admin02;

# 把适用于该对象的所有权限都赋予用户postgre
GRANT ALL ON ppol TO postgres;

13、数据备份与还原

13.1 使用pg_dump工具备份

pg_dump是PostgreSQL提供的一个非常有用的数据库备份工具，它甚至可以在数据库正在使用的时候进行完整一

致的备份。pg_dump 工具执行时，可以将数据库备份成一个文本文件，该文件中实际上包含了多个CREATE 和

INSERT 语句，使用这些语句可以重新创建表和插入数据。

pg_dump的具体使用语法如下：

pg_dump [option...][dbname]

其中，各个参数的含义如表所示：

选项	含义
dbname	将要备份的数据库名称
-a	只输出数据，不输出模式
-b	在备份中包含大对象
-c	输出在创建数据库创建命令之前先清理该数据库对象的命令
-C	以一条创建该数据库本身并且与这个数据库连接命令开头进行输出
-d	将数据输出为INSERT 命令,这样会导致恢复非常缓慢
-D	把数据备份为带有明确字段名的 INSERT命令
-E	以指定的字符集编码创建备份
-f	把输出发往指定的文件
-F	选择输出的格式
-i	忽略在pg_dump和数据库服务器之间的版本差别
-o	作为数据的一部分，为每个表都输出对象标识(OID)
-O	不把对象的所有权设置为对应源数据库
-s	只输出对象定义，不输出数据
-S	指定关闭触发器时需要用到的超级用户名
-t	只备份出匹配 tabl的表、视图、序列
-v	指定冗余模式
-x	禁止备份访问权限
-Z 0..9	声明在那些支持压缩的格式中使用的压缩级别。目前只有自定义格式支持压缩
-h	指定运行服务器的主机名
-p	指定服务器正在侦听的 TCP 端口
-U	连接的用户名
-W	强制口令提示

下面通过实例来讲述如何使用pg_dump备份数据库。

13.1.1 备份单个数据库中的所有表

# 用例
pg_dump -U postgres -f C:\abc\test_backup test

使用具有超级用户权限(-U选项)的角色运行pg_dump程序，-f选项指定备份文件的路径和文件名称以及备份的数据

为 test。

C:\zsxsoftware\PostgreSQL\bin>pg_dump -U postgres -f E:\pg_backup\sample_db_backup sample_db

13.1.2 备份数据库中指定的数据表

在前面pg_dump工具操作中，还可以指定备份的数据表，其语法格式为：

pg_dump -t 表名称 -t 表名称1...tbname

其中，tbname表示数据库中的表名，多个表名之间用-t空格隔开。

# 用例
pg_dump -U postgres -t tb_emp -t tb_emp2 -f C:\abc\test_backup2 test

C:\zsxsoftware\PostgreSQL\bin>pg_dump -U postgres -t fruits -t orders  -f E:\pg_backup\sample_db_backup1 sample_db

13.1.3 使用pg_dumpall工具备份整个服务器的数据库

pg_dumpall 工具可以存储一个数据库集群里的所有数据库到一个脚本文件。该脚本文件包含可以用于psq 输入

的SQL 命令，从而恢复数据库。

pg_dumpall 通过对数据库集群里的每个数据库调用 pg_dump 实现这个功能。pg_dumpall还可以备份出所有数

据库公用的全局对象，而pg_dump 并不保存这些对象。这些信息包括数据库用户和组以及适用于整个数据库的访

问权限。

因为pg_dumpall 从所有数据库中读取表，所以很可能需要以数据库超级用户的身份连接，这样才能生成完整的备

份。同样，用户也需要超级用户的权限执行保存下来的脚本，这些才能增加用户和组，以及创建数据库。SQL 脚

本将写出到标准输出。

# 用例
pg_dumpall -U postgres-f C:\abc\dball_backup

C:\zsxsoftware\PostgreSQL\bin>pg_dumpall -U postgres -f E:\pg_backup\sample_db_backup2

13.2 数据还原

13.2.1 还原数据库

# 用例
psql -d test -U postgres -f C:\abc\test_backup

使用-d选项规定的数据库对恢复操作没有影响；使用-U选项指定具有超级用户特权的角色；使用-f选项指定备份文

件的目录路径。

C:\zsxsoftware\PostgreSQL\bin>psql -d sample_db1 -U postgres -f E:\pg_backup\sample_db_backup2

13.2.2 使用 pg_restore 快速还原数据库

pg_restore 可以还原由pg_dump创建的备份文件，它将重新生成包括数据在内的所有用户定义类型，函数、

表、索引和操作符的所有必要的命令。

# 用例
pg_restore -d test -U postgres -C C:\abc\test_backup

C:\zsxsoftware\PostgreSQL\bin>pg_dump -Fc -d sample_db -U postgres -f  E:\pg_backup\sample_db_backup.dump

C:\zsxsoftware\PostgreSQL\bin>pg_restore -d sample_db1 -U postgres -C E:\pg_backup\sample_db_backup.dump

使用-d选项指定数据库的名称；使用-U选项指定具有超级用户特权的角色；使用-C选项表明该恢复操作建立一个

与生成备份文件的数据库同名的新数据库，以及备份文件的目录路径。

13.3 数据库迁移

数据库迁移就是把数据从一个系统移动到另一个系统上。数据库迁移有以下几个原因：

• 需要安装新的数据库服务器。

• PostgreSQL 版本更新。

• 数据库管理系的变更(如从 Microsoft SQL Server迁移到 PostgreSQL)。

13.3.1 相同版本的PostgreSQL 数据库之间的迁移

相同版本的PostgreSQL数据库之间的迁移就是指在主版本号相同的PostgreSQL 数据库之间进行数据库移动。迁

移过程其实就是源数据库备份和目标数据库还原过程的组合。

一般情况下，不同主版本的 PostgreSQL之间的内部存储结构是不同的；而不同的子版本之间是相同的，它们通常

使用的都是兼容的存储格式。

如果在兼容的版本之间迁移数据，利用pgAdmin即可快速实现。

13.3.2 不同版本的PostgreSQL 数据库之间的迁移

因为数据库升级等原因，需要将较旧版本PostgreSQL 数据库中的数据迁移到较新版本的数据库中。PostgreSQL

服务器升级时，需要先停止服务，然后卸载老版本，并安装新版本的 PostgreSQL，这种更新方法很简单。如果想

保留老版本中用户访问控制信息，则需要备份老版本 PostgreSQL中的数据库，在新版本PostgreSQL 安装完成之

后，重新读入PostgreSQL 备份文件中的信息。需要使用pg_dump备份数据，然后到新的服务器里恢复它们。

建议使用新版本的pg_dump，以便利用新版本的特性和功能。

# 将 www.abc.com 主机上的PostgreSQL数据库全部迁移到www.bcd.com主机上
# 在 www.abc.com主机上执行的命令如下
pg_dump -U postgres -h www.bcd.com -f C:\abc\testl_backup
# 然后在www.abc.com主机上使用 psql还原数据库文件即可

13.3.3 不同数据库之间的迁移

不同类型的数据库之间的迁移，是指把 PostgreSQL的数据库转移到其他类型的数据库，例如从PostgreSQL 迁移

到ORACLE，从ORACLE迁移到PostgreSQL，从PostgreSQL 迁移到SQLServer等。

迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的关键字可能会

不同，例如，PostgreSQL中的日期字段分为DATE和TIME两种，而ORACLE日期字段只有 DATE。另外数据库厂商

并没有完全按照 SQL标准来设计数据库系统，导致不同的数据库系统的 SQL 语句有差别。例如，PostgreSQL几乎

完全支持标准SQL语言，而 Microsoft SQL Server 使用的是T-SQL 语言，T-SQL中有些非标准的SQL 语句，因此在

迁移时必须对这些语句进行语句映射处理。

数据库迁移可以使用一些工具，例如 Windows 系统下，可以使用 Ora2pg 实现 Oracle 数据库和 PostgreSQL数

据库之间的迁移。

14、性能优化

14.1 优化查询

查询是数据库中最频繁的操作，查询速度的提高可以有效提高PostgreSQL 数据库的性能。本节将为读者介绍优化

查询的方法。

14.1.1 分析查询语句 EXPLAIN

EXPLAIN主要用于分析一个语句的执行规划。执行规划显示语句引用的表是如何被扫描的，即是简单的顺序扫描，

还是索引扫描，并且如果引用了多个表，采用什么样的连接算法从每个输入的表中取出所需要的记录。

EXPLAIN的基本语法格式如下：

EXPLAIN [ANALYZE] [VERBOSE] statement select_options

其中，ANALYZE 参数表示执行命令并显示实际运行时间；VERBOSE 参数表示规划树完整的内部表现形式，而不

仅仅是一个摘要；statement 参数要查看规划结果的任何 SELECT、INSERT、UPDATE、DELETE、VALUES、

EXECUTE 和 DECLARE 语句之一；select_options 是语句的查询选项，包括FROM和WHERE子句等。

执行该语句，可以分析EXPLAIN 后面的语句的执行情况，并且能够分析出所查询的表的一些特征。

EXPLAIN ANALYZE SELECT * FROM fruits WHERE f_price=3.60;

QUERY PLAN                                                                                       |
-------------------------------------------------------------------------------------------------+
Seq Scan on fruits  (cost=0.00..1.20 rows=1 width=1086) (actual time=0.015..0.017 rows=2 loops=1)|
  Filter: (f_price = 3.60)                                                                       |
  Rows Removed by Filter: 14                                                                     |
Planning Time: 0.052 ms                                                                          |
Execution Time: 0.026 ms                                                                         |

14.1.2 索引对查询速度的影响

PostgreSQL 中提高性能的一个最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并

且加快了查询的速度，因而有着至关重要的影响。使用索引可以快速定位表中的某条记录，从而提高数据库查询的

速度，提高数据库的性能。

如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果

使用索引进行查询，查询语句根据索引快速定位到待查询记录，可以减少查询的记录数，达到提高查询速度的目

的。

# 分析未使用索引时的查询情况
EXPLAIN SELECT * FROM fruits WHERE f_name='apple';

QUERY PLAN                                             |
-------------------------------------------------------+
Seq Scan on fruits  (cost=0.00..1.20 rows=1 width=1086)|
  Filter: (f_name = 'apple'::bpchar)                   |

CREATE INDEX index name ON fruits(f_name);
EXPLAIN ANALYZE SELECT * FROM fruits WHERE f_name='apple';

QUERY PLAN                                                                                       |
-------------------------------------------------------------------------------------------------+
Seq Scan on fruits  (cost=0.00..1.20 rows=1 width=1086) (actual time=0.019..0.026 rows=1 loops=1)|
  Filter: (f_name = 'apple'::bpchar)                                                             |
  Rows Removed by Filter: 15                                                                     |
Planning Time: 0.762 ms                                                                          |
Execution Time: 0.041 ms                                                                         |

14.1.3 优化子查询

在PostgreSQL中，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT 语

句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL 操作。子查询虽然可以使查询语句很

灵活，但执行效率不高。执行子查询时，PostgreSQL 需要为内层查询语句的结果建立一个临时表,然后外层查询语

句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数

据量比较大，这种影响就会随之增大。

在PostgreSQL 中可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如

果查询中使用到索引，性能会更好。连接之所以更有效率，是因为PostgreSQL不需要在内存中创建临时表来完成

查询工作。

14.2 优化数据库结构

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。合理的数据库结构不仅可以使数据库占用

更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的

数据类型是否合理等多方面的内容。

14.2.1 将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表中的数据量

很大时，查询会由于使用频率低的字段的存在而变慢。

create table members
(
id INT NOT NULL PRIMARY KEY,
username VARCHAR(100) NULL,
password VARCHAR(100) NULL,
last_login_time TIMESTAMP NULL
);

create table members_detail
(
member_id INT NOT NULL PRIMARY KEY,
address VARCHAR(50) NULL,
telephone VARCHAR(50) NULL,
description Text NULL
);

将两张表通过外键来进行连接，联合查询进行数据的查询：

SELECT * FROM members LEFT JOIN members_detail ON members.id = members_detail.member_id;

id|username|password|last_login_time|member_id|address|telephone|description|
--+--------+--------+---------------+---------+-------+---------+-----------+

通过这种分解，可以提高表的查询效率。对于字段很多、而有些字段使用不频繁的表，可以通过这种分解的方式来

优化数据库的性能。

14.2.2 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入

到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

该方法的执行过程是：首先，分析经常联合查询表中的字段；然后，使用这些字段建立一个中间表，并将原来联合

查询的表的数据插入到中间表中；最后，可以使用中间表来进行查询了。

已知现在有一个模块需要经常查询出带有会员组名称(name)、会员组备注(remark)、会员用户名(username)等会

员信息。根据这种情况，可以创建一个temp_vip表。temp_vip表中存储用户名(user_name)，会员组名称

(group_name)和会员组备注(group_remark)信息。创建表的语句如下：

CREATE TABLE vip(
Id INT NOT NULL PRIMARY KEY,
username VARCHAR(100) NULL,
password VARCHAR(100) NULL,
groupId int NULL
);

CREATE TABLE vip_group(
Id INT NOT NULL PRIMARY KEY,
name VARCHAR(100) NULL,
remark VARCHAR(100) NULL
);

CREATE TABLE temp_vip(
Id INT NOT NULL PRIMARY KEY,
user_name VARCHAR(100) NULL,
group_name VARCHAR(100) NULL,
group_remark VARCHAR(100) NULL
);

INSERT INTO temp_vip(user_name,group_name,group_remark) SELECT v.username,g.name,g.remark FROM vip as v,vip_group as g WHERE v.groupId=g.Id;

14.2.3 增加冗余字段

设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理

的加入冗余字段可以提高查询速度。

表的规范化程度越高，表与表之间的关系就越多，需要连接查询的情况也就越多。例如，员工的信息存储在staff

表中，部门信息存储在department 表中。通过 staff 表中的 departmen_id 字段与 department 表建立关联关

系。如果要查询一个员工所在部门的名称，必须从 staff 表中查找员工所在部门的编号(department_id)，然后根

据这个编号去 department表中查找部门的名称。如果经常需要进行这个操作时，连接查询会浪费很多时间。因而

可以在 staff 表中增加一个冗余字段department_name，该字段用来存储员工所在部门的名称。这样就不用每次

都进行连接操作了。

冗余字段会导致一些问题。比如，冗余字段的值在一个表中被修改了，就要想办法在其他表中更新该字段，否则就

会使原本一致的数据变得不一致。

分解表、增加中间表和增加冗余字段都浪费了一定的磁盘空间。从数据库性能来看，为了提高查询速度而增加少量

的冗余，大部分情况下是可以接受的。是否通过增加冗余来提高数据库性能,这要根据实际需求综合分析。

14.2.4 优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优

化。

1、删除索引

对于非空表，插入记录时，PostgreSQL 会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会

降低插入记录的速度。为了应对这种情况，可以在插入记录之前删除索引，数据插入完毕后再创建索引。

2、使用批量插入

插入多条记录时，可以使用一条 INSERT 语句一次插入一条记录；也可以使用一条 INSERT语句一次插入多条记

录。一次插入一条记录的 INSERT 语句情形如下：

INSERT INTO fruits VALUES('x1','101','mongo2','5.5');
INSERT INTO fruits VALUES('x2','101','mongo3','5.5');
INSERT INTO fruits VALUES('x3','101','mongo4','5.5');

一次插入多条记录的 INSERT 语句情形如下：

INSERT INTO,fruits VALUES ('x1','101','mongo2','5.5'),('x2','101','mongo3','5.5'),('x3','101','mongo4','5.5');

第二种情形的速度要比第一种情形快。

3、删除外键约束

和删除索引一样，在插入数据之前，删除对外键的约束；数据插入完成之后，再创建外键的约束。

4、禁止自动提交

如果允许每个插入都独立地提交，那么PostgreSQL 会为所增加的每行记录做大量的处理。所以在插入数据之前，

禁止自动事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

5、使用 COPY批量导入

当需要批量导入数据时，如果能用 COPY在一条命令里装载所有记录，就尽量使用COPY 语句。因为 COPY 语句导

入数据的速度要比 INSERT 语句快，另外在大量装载数据的情况下，导致的负荷也少很多。

14.2.5 分析表的统计信息

PostgreSQL 中提供了 ANALYZE 语句收集表内容的统计信息，然后把结果保存在系统表p_statistic 里。

ANALYZE 语句的基本语法如下：

ANALYZE [VERBOSE] [table [(column[,...])]]

其中，VERBOSE 参数主要是为了显示处理过程的信息；table 参数代表要分析的数据表的名称，如果不填写，表

示当前数据库里所有表；column 参数代表要分析的特定字段的名称，如果不填写，表示当前数据表中的所有字

段。

ANALYZE VERBOSE fruits;

正在分析 "public.fruits"
"fruits": 已经扫描了2页的2, 包含16可用的记录和32不能用的记录; 在示例中有16条记录,估算所有记录为16 .

一般情况下，如果用户增加或者更新了大量数据之后，都需要运行ANALYZE语句，从而获取表数据的最新统计，

如果没有统计数据或者统计数据太陈旧,那么规划器可能选择比较差的查询规划，导致表的查询性能大幅度降低。

14.3 优化 PostgreSQL服务器

优化 PostgreSQL服务器主要从两个方面来进行。一方面是从硬件方面来优化；另一方面是从PostgreSQL 服务的

参数来优化。这部分的内容需要较多的全面知识，一般只有专业的数据库管理员才能进行这一类的优化。对于可以

定制参数的操作系统，也可以针对 PostgreSQL 进行操作系统优化。

14.3.1 优化服务器硬件

服务器的硬件性能直接决定着 PostgreSQL 数据库的性能。硬件的性能瓶颈,直接决定PostgreSQL 数据库的运行速

度和效率。针对性能瓶颈,提高硬件配置,可以提高 PostgreSQL 数据库的查询、更新速度。本小节将为读者介绍以

下优化服务器硬件的方法。

• 配置较大的内存。足够大的内存是提高 PostgreSQL 数据库性能的方法之一。内存的速度比磁盘I/O 快得多，

  可以通过增加系统的缓冲区容量，使数据在内存中停留的时间更长，以减少磁盘I/O。

• 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。

• 合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。

• 配置多处理器，PostgreSQL 是多线程的数据库，多处理器可同时执行多个线程。

14.3.2 优化 PostgreSQL 的参数

通过优化PostgreSQL的参数，可以提高资源利用率，从而达到提高PostgreSQL服务器性能的目的。本小节将为读

者介绍这些配置参数。

PostgreSQL 服务的配置参数都在postgresql.conf中。下面对几个比较影响性能的参数进行详细介绍。

• maintenance_work_mem：在装载大量数据时，临时增大maintenance_work_mem 配置变量可以改进性能。

  这个参数也可以帮助加快 CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY 命令的执行速度。

• checkpoint_segments：PostgreSQL 里面装载大量的数据会导致检查点操作比平常更加频繁发生，检查点

  操作时，所有脏数据都必须刷新到磁盘上。通过在大量数据装载时临时增加checkpoint_segments，所要求的

  检查点的数目减少，从而让大量数据装载得更快。

• effective_cache_size：此参数代表 PostgreSQL 能够使用的最大缓存。通过设置此参数，可以提高服务器

  的性能。例如服务器为4GB的内存，可以设置缓冲为3.5GB的大小。

• max connections：通常，max_connections 的目的是防止 max_connections*work_mem超出实际内存的

  大小。比如，如果将work_mem设置为实际内存的4%，则在极端情况下，如果有25个查询都有排序要求，而

  且都使用4%的内存，则会导致数据外溢，系统性能就会大大降低。

• shared_buffers：PostgreSQL 通过 shared buffers 和内核、磁盘打交道，因此该参数应该尽量大，让更多

  的数据缓存在 shared buffers中。通常设置为实际RAM的10%是比较合理的。

• work_mem：PostgreSQL在执行排序操作时，会根据 work_mem 的大小决定是否将一个大的结果集拆分为几

  个和 work_mem差不多大小的临时文件。显然拆分的结果是降低了排序的速度，因此增加work_mem有助于

  提高排序的速度。通常设置为实际RAM的2%~4%比较合理。