【数据库学习】Postgres(PG)原理及底层实现

大家好，欢迎来到IT知识分享网。

1. pg安装与运维
2. Postgres原理及底层实现
3. 基础语法
4. SQL优化
5. 中文文档

1，事务原理

事务的实现即：RDBMS采取何种技术确保事务的ACID特性？

1）事务特性（ACID）

1>原子性（Atomic）

事务是数据库的逻辑工作单位。要么都做，要么都不做。==》通过MVCC保证

2> 一致性（Consistency）==》最终目的

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏，事务在执行过程中发生错误，会被回滚到事务开始前的状态。

3>隔离性（Isolation）

事务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。==》用锁和MVCC来保证。

4>永久性（Durability）

事务一旦提交，所做修改会永久的保存在数据库中。==》保证了db的可靠性，用WAL日志来实现

其中一致性是事务的最终目的，为了达到一致性需要保证原子性、隔离性、永久性。

那么pg是怎么完成ACID的呢？

2）pg隔离级别

在标准SQL规范中，定义了4个事务隔离级别（由低到高）：

pg仅支持2种隔离级别：读已提交（默认）、可串行化。

事务隔离级别的实现：

1. 读未提交/读已提交：每个query都会获取最新的快照CurrentSnapshotData
2. 重复读：所有的query 获取相同的快照都为第1个query获取的快照FirstXactSnapshot
3. 串行化：使用锁系统来实现

1>读已提交隔离级别（默认）

每个命令都是从一个新的快照开始执行的。

1. 当前事务看不见其它未提交事务的数据；
2. 当前事务可以看到自己未提交的数据；
3. 一个事务里两个select，两次select读到的数据可能不是同一个快照。第二次select的时候会看到其它事务已提交的数据。
4. 同一个事务中，第一次更新之后，其它事务获得锁进行数据的更新，当前事务中的更新操作需要等到其它事务结束或者回滚再进行操作。

2>可串行化隔离级别（开销大）

每个命令都是从事务开始时的快照开始执行的。

1. 当前事务看不见其它未提交事务的数据；
2. 当前事务可以看到自己未提交的数据；
3. 只读事务不存在冲突：一个事务里两个select，两次select读到的数据一致。
4. 串行化冲突：更新事务与其它更新事务冲突需要重试。
   同一个事务中，第一次更新之后，其它事务获得锁进行数据的更新，当前事务中的更新操作需要等到其它事务结束或者回滚再进行操作。如果其它事务回滚，当前事务正常进行；如果其它事务提交，那么当前事务回滚（ERROR: could not serialize access due to concurrent update），需要重试。
   

3）多版本并发控制（MVCC，Multi-Version Concurrency Control）

MVCC是数据库并发访问时，保证数据一致性的一种方法。实现MVCC的方法有以下两种：

1. 写新数据时，把原数据移到一个单独的位置，如回滚段中，其它用户读数据时，从回滚段中把原数据读出来。（Oracle和Mysql数据库中的InnoDB引擎使用这种方法）
2. 写新数据时，原数据不删除，而是把新数据插入进来。（pg使用这种方法）==》相当于每个事务看到的都是之前一小段时间的数据快照（某一个数据库版本）。

1>原子性保证

pg中表中有以下4个内置表字段，每个tuple的更新时是先del旧的再insert新的tuple。

1. insert tuple：xmin=事务id xmax=0 ctid=(0,1)，指向当前元组
2. delete tuple：xmax=事务id
3. update tuple，先delete，再insert： tupleOld的xmax=事务id ctid指向新的元组，tupleNew的xmin=事务id ctid指向当前元组

原子性：通过当前事务id对tuple进行标记，不管是commit还是rollback操作都可以通过xmin和xmax保证事务的原子性。

2>事务隔离性保证

a）不同事务的可见性：xmin xmax

在不同事务中，可以根据xmin和xmax判断事务可见性。

1. TransactionId xmin; // 记录了未提交并活跃的事务最小xid，如果t_xid < xmin则元组数据已提交：可见
2. TransactionId xmax; //记录了已提交事务最大xid+1，如果t_xid >= xmax 则元组未提交：不可见
3. TransactionId *xip; // 活动事务id列表

对于t_xid在[xmin, xmax)之间数据，需要结合clog日志判断其修改的数据是否可见

1. t_xmin<s_xmin && t_xmax == 0，元组插入且事务已提交，可见；
2. t_xmin<s_xmin && t_xmax !=0 && t_xmax <s_xmin，元组已删除，不可见；
3. t_xmin<s_xmin && t_xmax !=0 && t_xmax >s_xmax，元组删除但未提交，可见；
4. 其它：需要结合clog进行判断。

b）同一事务的可见性：cmin cmax

cmin、cmax 用于同一个事务中实现版本可见性判断

3>事务持久性保证

a）clog（commit log）日志

CLOG数据会不断增长，但并非所有数据都是必要的，清理过程也会定期清理掉不再需要的CLOG页面和文件。

pg可以通过调用三个内部函数——TransactionIdIsInProcess、TransactionIdDidCommit和TransactionIdDidAbort，读取CLOG返回所请求事务状态。

b）Hint Bits

判断元组的可见性非常频繁，每次从缓存或者磁盘读取clog信息依然不够高效，引入了Hint Bits概念。t_informask中存储的一些标志位保存了插入/删除该元组的事务的状态。

• 如果Hint Bits已设置，直接读取Hint Bits的值。
• 如果Hint Bits未设置，则调用函数从CLOG中读取事务状态。如果事务状态为COMMITTED或ABORTED，则将Hint Bits设置到元组的t_informask字段。如果事务状态为INPROCESS，由于其状态还未到达终态，无需设置Hint Bits。

4>MVCC的优缺点

pg在事务提交前，只需要访问原来的数据；提交后，系统更新元组的存储标识，直到Vaccum进程回收为止。

相比InnoDB和Oracle，pg多版本优势在于：

1. 事务回滚可以立即完成；
2. 数据可以进行很多更新，不必像Oracle和InnoDB那样需要经常保证回滚段不会被用完，也不会像Oracle数据库那样，经常遇到ORA-1555错误的困扰。

劣势在于：

1. 旧数据需要Vaccum清理。
2. 旧版本数据的存在降低查询速率，需要扫描更多的数据块。

4）表膨胀问题

1>Visibility Map机制：

• 当vacuum时，可以直接跳过这些page
• 进行index-only scan时，可以先检查下Visibility Map。这样减少fetch tuple时的可见性判断，从而减少IO操作，提高性能

2>vacuum（表空间优化、收缩表）

VACUUM寻找不再被别的任何事务任何人看到的行。这些行可能是页的中间几行。

一般pg会有个异步任务自动执行，如果突然有大量数据执行update全表等操作，会让磁盘空间瞬间翻倍，需要手动执行vacuum，但是这个操作会锁表，用的时候慎重。

--加表名指定表 不加表名表示全局处理 vacuum t_lxs; --获取表空间大小 --vacuum允许 pg重用该空间，但是，它不会将该空间返回给操作系统。 SELECT pg_relation_size('t_lxs');--8192 DELETE FROM t_lxs; --如果从表中的某个位置开始，ALL rows are dead，VACUUM可以截断表。 VACUUM t_lxs; SELECT pg_relation_size('t_lxs');--0 --但是大表末尾总有那么几行数据，靠VACUUM几乎很难释放空间。通过使用VACUUM FULL重排数据的磁盘位置，可以解决表膨胀的问题。但是这个操作会直接锁表。一定要在业务低频使用时进行。 VACUUM FULL t_lxs; 

5）事务id回卷