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

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

1，事务原理

事务（transaction）：
是用户定义的一组数据库操作，要么全做要么全不做，失败即回滚。
事务是恢复和并发控制的基本单元。

保存点（savePoint）
在一个大的事务中，可以把操作过程分成几个部分，第一个部分执行成功后可以建一个保存点，若后面的部分执行失败，则回滚到此保存点，而不必回滚整个事务。

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

回退（rollback）：
撤销sql执行过程。事务管理可以管理insert、update、delete语句；不能回退create、drop操作。

RDBMS（Relational Database Management System，关系数据库管理系统）
是指包括相互联系的逻辑组织和存取这些数据的一套程序 (数据库管理系统软件)。
关系数据库管理系统就是管理关系数据库，并将数据逻辑组织的系统。

1）事务特性（ACID）

1>原子性（Atomic）

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

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

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

3>隔离性（Isolation）

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

读读不存在并发问题；
读写通过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. 同一个事务中，第一次更新之后，其它事务获得锁进行数据的更新，当前事务中的更新操作需要等到其它事务结束或者回滚再进行操作。

为什么使用RC级别而不使用RR级别？
提高并发度并降低死锁概率。

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>原子性保证

事务ID：XID、txid（transaction id）
pg中每个事务开始时，事务管理器都会分配一个唯一id，从3开始递增。
32位无符号整数，取值空间：2^32-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判断事务可见性。

快照（SnapshotData）
维护了以下一些信息：

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

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

每次select获取当前db SnapshotData，判断数据的可见性。
区分元组t_xmin和快照s_xmin，对于当前元组数据：

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（commit log）：
pg记录事务状态。包括以下四种：
transaction_status_in_progress =0x00：表示事务正在进行中
transaction_status_committed =0x01：表示事务已提交
transaction_status_aborted =0x02：表示事务已回滚
transaction_status_sub_committed =0x03：表示子事务已提交

结构：数组，由缓存(SLRU Buffer Pool )中一系列的8K页面组成。
数组下标对应事务txid，数组内容则为事务状态。每个事务状态2bit，一个块8KB可以存储8KB*8/2 = 32K个事务的状态。
当shutdown pg或Checkpoint运行时，CLOG数据会由内存写入pg_clog（pg 10后叫pg_xact）目录中的文件。这些文件被命名为0000,0001，最大256KB。当pg启动时，会加载这些文件用于初始化CLOG。

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

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

b）Hint Bits

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

元组中的 Hint Bits采用延迟更新策略，并不会在事务提交或者回滚时主动更新所有操作过的元组Hint Bits。
等到第一次访问（可能是VACUUM，DML或SELECT）该元组并进行可见性判断时：

• 如果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机制：

官方文档：Routine Vacuuming
Visibility Map中标记了哪些page中是没有dead tuple的，数据量很小可以cache到内存中。这有两个好处：

• 当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;
1
2
3
4
5
6
7
8
9
10
11

5）事务id回卷

1>系统预留事务ID：0 1 2

0 1 2是系统预留ID，这三个ID比任何普通xid都要旧。

• InvalidTransactionId=0 无效的事务ID；表示还未分配事务ID。
• BootstrapTransactionId=1 表示系统表初始化时的事务ID；表示Initdb服务正在初始化系统表。
• FrozenTransactionId=2 冻结的事务ID。

2>回卷

xid一直递增达到2^32为最大值，然后继续从3开始，以前的xid比新的xid大，回卷后无法按xid大小判断数据可见性。

回卷问题导致事务id可见性的判断怎么解决？

1. pg规定，最早和最新两个事务之间年龄差对多为2³¹。==>事务冻结来保证
   即：xid空间虽然有2³²，作为一个环被一分为二，对某个特定的xid，其后2³¹个xid属于未来，均不可见；其前2³¹个xid属于过去，可见。
2. 俩个事务之间id的计算使用2^31取模的方法来进行事务的比较：diff = (int32) (id1 - id2);来判断id1<id2

举例：txid=100的事务，[101,2³¹+100] 均为不可见事务；[2³¹+101, 99] 均为可见事务。
代码解析：

/*
 * TransactionIdPrecedes --- is id1 logically < id2?
 */
bool TransactionIdPrecedes(TransactionId id1, TransactionId id2) // 结果返回一个bool值
{
	int32		diff;
	if (!TransactionIdIsNormal(id1) || !TransactionIdIsNormal(id2)) //若其中一个不是普通id，则其一定较新（较大）
		return (id1 < id2);

	diff = (int32) (id1 - id2);
	return (diff < 0);
}
1
2
3
4
5
6
7
8
9
10
11
12

普通xid比较：diff = (int32) (id1 - id2);
注意：int 32带符号，第一位表示符号位，取值范围为 [-2^(n-1), 2^(n-1)-1]，即[-2³¹,2³¹-1]。
当两个txid（32位）相减 (id1 - id2)>2³¹时，发生回卷，转换位int32时符号位从0变成了1，(int 32) (id1 - id2)是个负数。此时id1<id2==true，id1事务更小更旧，id1对id2是可见的。
举例：id1=2³¹+101，id2=100。id1-id2=2³¹+1，int32转换后这个值是-1，小于0，因此id1的事务反而要旧，id2可见id1，id1不可见id2。
但是如果xid=100确实是很久很久的事务，那么对于2³¹+101这个事务看见，此时上面的判断就是错的。为了避免这种问题，pg必须保证一个数据库中两个有效的事务之间的年龄最多是2³¹（同一个数据库中，存在的最旧和最新两个事务txid相差不得超过2³¹）。这时靠事务冻结来保证。

3>事务冻结（freeze）

当超过2³¹时，就把旧的事务换成一个FrozenTransactionId=2的特殊事务，当正常事务ID与冻结事务ID比较时，会认为正常xid比FrozenTransactionId更新。

a）freeze实现：

1. pg9.4之前freeze方法：
   直接将符合条件的元组的t_xmin设置为2，回收原来的xid。但这样实现的问题是：
   一）当前可见的数据页需要全部扫描，带来大量的IO扫描；
   二）符合条件的元组需要更新xmin，造成大量脏页，带来大量IO
2. pg9.4后对freeze优化：
   不直接修改t_xmin，而是：
   一）只更新元组头结点的t_infomask为HEAP_XMIN_FROZEN，表示该元组已经被冻结过（frozen）；
   二）有些插入操作，也可以直接将记录置为frozen，例如大批量的COPY数据，insert into等；
   三）如果整个page所有记录已经frozen，则在vm文件中标记为FROZEN，冻结清理会跳过该页，减少了IO扫描。

b）freeze优化：

freeze是被动触发的，可以调节pg的一些参数优化freeze，更多时候提倡用户进行主动预测需要freeze的时机，选择合适的时间（比如pg负载较低的时间）主动执行vacuum freeze命令。
目前已经有很多实现好的开源PostgreSQL vacuum freeze监控管理工具，比如flexible-freeze，能够：确定数据库的高峰和低峰期；在数据库低峰期创建一个cron job执行flexible_freeze.py；flexible_freeze.py会自动对具有最老XID的表进行vacuum freeze。

2，SQL执行原理

1）引擎执行SQL过程

1. 客户端连接
   客户端发送一条查询给服务器；
2. 查询缓存
   服务器先检查查询缓存query cache，如果命中了缓存，立即返回缓存结果。
3. 解析器->预处理器->查询优化器
   1）SQL解析parsing
   解析器扫描查询并检查语法。正确则将查询文本转换成解析树。
   2）预处理transition
   3）优化器optimization
   遍历语法树，生成若干执行计划比计算每个算子执行成本；
4. 查询执行引擎
   根据最优执行计划，调用存储引擎的API来执行分布（distribution）查询；
5. 将结果返回给客户端。
6. 关闭连接

2）算子原理

PG目前有19个查询算子。
每个执行计划都是一颗树，执行时自顶向下开始，自底向上计算，最终得到查询结果。

1>Sort

输入：1个结果集；
场景：order by、Unique算子、
原理：

1. sort_mem可以调整：
   如果结果集的大小超过sort_mem，Sort会将输入集分发到已排序工作文件的集合中，然后再次将工作文件重新合并在一起。如果结果集适合sort_mem*1024字节，则使用 QSort 算法在内存中进行排序。

2>Seq Scan（全表扫描）

1. 场景：查询未匹配到合适的索引、order by操作
2. 原理：
   从表头扫描到表尾，每一行数据执行where过滤，将得到的结果放入结果集中。
3. 输出：过滤后的结果集；排序：表顺序。

3>Index Scan（索引扫描）

原理：

1. 如果where针对索引过滤了，会直接限定索引查询范围。
2. 返回结果排序：索引顺序。
3. 并非所有索引都是可扫描的。可以扫描B-Tree、R-Tree和GiST索引类型；哈希索引不能。

4>Unique算子

场景：DISTINCT
输入：已排序的结果集；
输出：消除值重复的行;
原理：Sort->Seq Scan;

5>LIMIT算子

场景：limit、limit offset
输入：1个结果集；
输出：结果集的前n行，直接丢掉剩余的行

6>Group

场景：GROUP BY
原理：Sort->Seq Scan

7>Aggregate

场景：聚合函数：AVG(), COUNT(), MAX(), MIN(), STDDEV(), SUM(), and VARIANCE()
原理：

1. 如果没有group by：Aggregate->Seq Scan;
   读取结果集，计算，输出一个值；
2. 如果有group by：Aggregate->Group->Sort->Seq Scan;

8>Append

场景：UNION、表继承
输入：2+个结果集
原理：返回第一个输入集的所有行，然后拼接第二个输入集的所有行。

9>Result

性能影响不大。
场景：

1. 常量查询；

select timeofday();
where 1==1；
1
2

2. 辅助Append算子；

10>Nested Loop（嵌套循环）

1. 场景：inner joins, left outer joins, and unions；
   因为不遍历整个内表，不能处理其它连接：full, right join

SELECT * FROM customers, rentals
WHERE customers.customer_id = rentals.customer_id;
1
2

2. 原理：
   第一个表作为外表，全部加载进内存，挨个轮询遍历；
   第二个表作为内表，搜索（全表扫描或者索引扫描）满足连接条件的行
3. 场景：
   适合两个表的数据量都比较少的情况(最简单的 table join 方式)。
   1）外表为小表，且过滤后的数据量较少。
   2）内表的关联列上有高效索引(主键或者唯一性索引)。

11>Merge Join

1. 输入：2个表，一个外表一个内表；
2. 原理：
   1）两个表按连接字段排序：index scan->sort
   2）两个表分别按行读取，2个指针分别读外表、内表，同上指针指向相同的连接字段；
   由于有序所以只需要同步遍历两张表就可以连接。
3. 场景：inner joins, outer joins, and unions
   对于那些连接列上有索引的表（已排好序）Merge JOIN性能会优于Hash JOIN。

12>Hash算子、 Hash Join算子

两个算子一起工作。

1. 场景： inner joins, left outer joins, and unions
   hash join一般针对那些没有索引或者其中任一个有索引的大表。

--所有等值连接都可以转换为inner joins
SELECT * FROM customers, rentals
WHERE customers.customer_id = rentals.customer_id;
1
2
3

2. 原理：
   1）加载内表（小表）进内存，建立hash索引：散列表；
   表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O 的性能。
   2）遍历外表，每读一行，对连接列计算hash值，匹配对应的散列表，连接。

13>Subquery Scan算子、Subplan算子

Subquery Scan用于union子句；
Subplan用于子选择。
场景：用于内部标记，将输入集中每一行加入结果集。

14>Tid Scan

用于事务中检索元组id（ctid），很少用到。

15>Materialize（物化）

场景：Merge join算子对于内部输入集不可重新定位的情况，认为物化一个子选择比重复选择代价更高。

16>Setop

1. Setop Intersect 算子
   遇到 INTERSECT时使用。
2. Setop Intersect All 算子
   遇到 INTERSECT ALL时使用。
3. Setop Except 算子
   遇到 EXCEPT时使用。
4. Setop Except All 算子
   遇到 EXCEPT ALL时使用。

3）where原理

select的别名是否可以作为where查询条件？
不能，因为执行计划中where在select之前。

4）缓存

shared_buffers所代表的内存区域可以看成是一个以8KB的block为单位的数组，即最小的分配单位是8KB。这正好是一个page的大小，每个page以page内部的元数据(Page Header)互相区分。

这样，当Postgres想要从disk获取(主要是table和index)数据(page)时，他会(根据page的元数据)先搜索shared_buffers，确认该page是否在shared_buffers中，如果存在，则直接命中，返回缓存的数据以避免I/O。
如果不存在，Postgres才会通过I/O访问disk获取数据(显然要比从shared_buffers中获取慢得多)。

1>缓存淘汰

以页为单位，cache满的时候，会淘汰不常用的页。淘汰后的数据则进行刷盘，但是一般数据都是通过WAL+Checkpointer保证修改的数据刷盘，而不用等到cache满了才进行刷盘。

2>如何观察是否走cache

使用explain时，Shared read表示来自disk，Shared hit则是已经在cache中。

3，进程架构模型

启动pg，主进程为Postmaster（pg的bin目录下，是一个指向Postgres的链接）。Postmaster是整个数据库实例的总控进程，负责启动和关闭数据库实例，同时fork出一些与数据库实例相关的辅助进程，并对其进行管理。

每次客户端与数据库建立连接时，pg数据库都会启动一个服务进程来为该连接服务，故而是进程架构模型，而MySQL是线程架构模型。当某个服务进程报错时，Postmaster主进程会自动完成系统恢复，恢复过程中停掉所有的服务进程，然后进行数据的一致性恢复，恢复完成后数据库才能接受新的连接。

1）autovacuum进程

autovacuum 是 postgresql 里非常重要的一个服务端进程，能够自动运行，在一定条件下自动触发对 dead tuples 进行清理并对表进行分析。
在pg中更新、删除行后，数据行并不会马上从数据块中清理掉，而是需要等VACUUM时时清理。为了加快VACUUM速度并降低对系统I/O性能的影响，pg8.4.1之后为每个数据块文件加了一个后缀为“_vm”的文件（可见性映射表文件，VM文件）。这个文件为每个数据块存储了一个标志位，标记数据块中是否存在要清理的tuple。

VACUUM有两种方式：

1. Lazy VACUUM ：使用VM文件，扫描部分数据块。
2. Full VACUUM ：全量扫描数据块。

vacuum相关的配置：

1. 如果开启了autovacuum，当垃圾记录数大于 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor*reltuples ，autovacuum launcher触发普通的vacuum。
   当表的年龄大于vacuum_freeze_table_age，则自动转换成vacuum freeze。
2. 如果开启了autovacuum，当新增记录数大于autovacuum_analyze_threshold + autovacuum_analyze_scale_factor*reltuples，autovacuum launcher触发analyze。
3. 即使没有开启autovacuum，当表的年龄大于autovacuum_freeze_max_age，也会强制触发vacuum freeze。

4，pg物理存储结构：堆表结构

Relation： 表（table）或索引（Index）。

Page：磁盘中的数据块 。

Buffer：内存中的数据块。

1）堆表结构

1. 每个表由若干文件组成
   表大小超过 RELSEG_SIZE 个块就会被切分成多个文件；
   堆表和索引是分开存储的，而mysql是索引组织的数据存储。
   pg的索引存储：索引、tuple地址。
2. 每个文件由若干块(block)构成；
3. 每个数据块存储了多行数据（tuple）。

1>base目录

1. 每个数据库，对应$PGDATA/base/oid下的一个子目录。

#查看各个数据库的oid
testdb=# select oid, datname from pg_database;
  oid  |  datname  
-------+-----------
 13025 | postgres
 16384 | testdb
     1 | template1
 13024 | template0
(4 rows)
1
2
3
4
5
6
7
8
9

2. 每张表，对应$PGDATA/base/{dboid}/{relfilenode}
   1）relfilenode一般情况下和和tboid一致，但有些情况下也会变化，如TRUNCATE、REINDEX、CLUSTER以及某些形式的ALTER TABLE。
   2）在$PGDATA/base/{dboid}中通常会包含三种文件：

--查询表所在磁盘的位置
select pg_relation_filepath('表名')
-- out
base/16384/50947

--查看表可见的block
testdb=# SELECT * FROM pg_visibility('testtb2');
 blkno | all_visible | all_frozen | pd_all_visible 
-------+-------------+------------+----------------
     0 | t           | f          | t
(1 row)
1
2
3
4
5
6
7
8
9
10
11

2）数据块（页）

数据块（页）大小为 BLCKSZ，默认 8K，最大为32K。

块的结构：

1. 块头(Page Header) ：
   a. 块的checksum值；
   b. 空闲空间的起始位置和结束位置；
   c. 特殊数据的起始位置；
   d. 其它信息。
2. 行指针：向后顺序排列。是一个32bit的数字，具体结构：
   a. 行内容偏移量，15bit；能表示的最大偏移量是2¹⁵，因此pg中块最大为32kb
   b. 指针的标记，2bit；
   c. 行内容的长度，15bit。
3. 空闲空间：行数据指针和行数据内容之间的空间。
4. 行内容：从块尾向前反向排列。

-- 查询表页数、行数
SELECT relpages as 页个数, reltuples as 行个数 FROM pg_class WHERE relname = '表名'

-- 查询占空间大小(排名前20) - 待考证
SELECT table_schema || '.' || table_name                                                         AS table_full_name,
       pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
limit 20;
1
2
3
4
5
6
7
8
9

3）Tuple（数据行、元组）

pg中元组由三部分组成——元组头结点、空值位图、用户数据。

HeapTupleHeaderData结构：Database Page Layout，pg提供了pageinspect插件，可查看指定表对应的page header内容。
相关字段说明：

5，锁机制

使用多版本并发控制（MVCC）比锁定模型（mysql的锁机制）的优势：读锁和写锁不再互斥。

1）pg常见锁

Postgres 有 3 种锁机制：表级锁，行级锁和建议性锁。

1>表锁

表锁由内置的 SQL 命令获得的，同时可以通过锁命令来明确获取。

2>行锁

行级锁不影响对数据的查询，它们只阻塞对同一行的写入。
行锁容易发生死锁。==》 pg自动侦测死锁条件，然后退出其中一个事务从而允许其它事务完成来解决死锁问题。

9.1版本的行锁主要分为两类，后续版本有基于此的扩充，但是大差不差，都是功能的细分。

3>页面级别的共享/排他锁

用于控制共享缓冲池中表页面的读/写。这些锁在抓取或者更新一行后马上被释放。不需要过多关注

2）谓词锁定（pg不支持）

谓词锁定：
禁止当前事务插入另一个并发事务的where条件，直到其它任务提交

--隐患：当前事务的select中的where条件与另一个并发事务的insert内容相同，导致并发事务：A和B的执行顺序不同结果不同。

--事务1
SELECT ... WHERE class = 1;
Insert class = 10 ...
--事务1
SELECT ... WHERE class = 10;
Insert class = 1 ...
1
2
3
4
5
6
7
8

如何处理这种情况？

1. insert select 合成一条sql
   将业务逻辑融入了sql中，扩展性差。
2. 加锁
3. CAS

3）使用

1>死锁的查询与释放

--查死锁
select pid from pg_locks where relation= (
	select oid from pg_class where relname='表名'
);
--释放锁
--方式1：
select pg_cancel_backend('上面查询到的pid');
--方式2：
select pg_terminate_backend('上面查询到的pid');
1
2
3
4
5
6
7
8
9
10

4）服务器参数说明

通过show 参数 sql语句可以查看当前设置值，修改后需要重启计算机才可以生效。

6, 数据库存储原理

1）硬盘

1. 寻址慢（ms级）
2. 带宽（G/MBPS）

2）内存

1. 寻址快（ns级）
2. 带宽 = 带宽×总线宽度÷8(单位换算) 很快

3）4k对齐

1. 硬盘中文件保存的基本单元是扇区，不管文件大小，都要占用一个扇区的空间。
2. 机械硬盘（Hard Disk Drive，HDD）一个扇区是512字节，固态硬盘（SSD）一个扇区是4K字节。
3. 微软操作系统常用的NTFS格式，默认扇区大小512字节，并且规定前63个扇区是保留的。
   如果“4K不对齐”，那么从第63个扇区结束，往后的每一个簇都会跨越两个物理单元，占据前一个单元的一部分和后一个单元的一部分。
   而“4K对齐”主要是将硬盘的逻辑扇区从第64个扇区开始对齐，即操作系统从第64个扇区开始读写数据，这样就跨过了63扇区的特性，解决了每一个簇跨两个物理单元读写的问题。
4. SSD的最小扇区为4k（1页），写入时不允许像机械硬盘一样覆盖写入，而是擦除写入。如果4k不对齐，数据跨页，那么每次写操作都会写入2个不同的扇区，需要写入两次，造成性能下降。4对齐保证了操作系统的逻辑单元与ssd硬件的闪存页对应起来，写入4k数据只需要写一次即可。
5. 4k对齐提高硬盘工作效率、提高硬盘工作寿命。
6. 4k对齐本质就是扇区对齐，随着扇区变大，会有8k对齐、16k对齐…

4）数据库查询优化

1. 随着文件变大，速度查询变慢？-> 访问硬盘受到IO瓶颈影响。
2. dataPage：数据库合并文件到页（每一页4k），每次读取一页数据只需要1次IO（多个文件只需要一次IO）。->查找一个文件还是要经过很多次IO读入内存进行筛选，速度依然很慢。
3. 建立索引：4k存储模型，指向很多dataPage。也存储在磁盘中。
4. 关系数据库行存储：必须给出每个列的类型->字节宽度确定了，空数据填充->行宽度确定->增删改不需要移动数据，直接复写即可。
5. B+Tree：只要查询命中索引，那么直接命中叶子结点，确定dataPage范围。->减少磁盘寻址时间
6. 如果数据库表很大，性能会变低？
   如果有索引，增删改慢。
   查询单条、少量数据依然会很快。
   并发查询大量数据时，轮流读取dataPage到内存，此时收到磁盘带宽影响速度。
7. 数据在磁盘和内存中大小不一样。
   因为磁盘中没有指针，数据会比内存中膨胀（同一个数据要写很多遍）。

0，参考资料

PgSQL · 特性分析 · MVCC机制浅析
PgSQL · 特性分析 · 事务ID回卷问题
PgSQL · 引擎特性 · PostgreSQL Hint Bits 简介