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个事务隔离级别（由低到高）：
| 读未提交（RU级别、read uncommitted） | 读已提交（read committed） | 可重复读（repeatable read） | 序列化（serializable） | |
|---|---|---|---|---|
| 允许操作 | 允许事务读取未被其他事务提交的变更 | 允许事务读取已经被其他事务提交的变更 | 事务读取数据时，禁止其他事务对这个字段进行更新 | 所有事务都一个接一个地串行执行 |
| 可能存在问题 | 脏读、不可重复读、幻读 | 不可重复读、幻读 | 幻读==》添加间隙锁解决此类问题 | 数据安全。但是添加大量行锁会导致大量超时和锁竞争问题。 |
| 避免问题 | ||||
| 举例 | 事务读到了其他事务未提交的数据。其他事务回滚导致脏读。 | 同一个事务读了两次数据，分别读取了其他事务提交的内容，但是两次结果不一致。这就是不可重复读。 | 事务读了两次数据，不管数据怎么修改，都只读第一次的数据。==》导致幻读：每次select时，mvcc的read view不会变化。但是其他事务做了新增操作，真实的数据和当前的read view不同。 | |
| 数据库 | oracle、pg默认隔离级别 | mysql默认隔离级别 |
pg仅支持2种隔离级别：读已提交（默认）、可串行化。
事务隔离级别的实现：
- 读未提交/读已提交：每个query都会获取最新的快照CurrentSnapshotData
- 重复读：所有的query 获取相同的快照都为第1个query获取的快照FirstXactSnapshot
- 串行化：使用锁系统来实现
1>读已提交隔离级别（默认）
每个命令都是从一个新的快照开始执行的。
- 当前事务看不见其它未提交事务的数据；
- 当前事务可以看到自己未提交的数据；
- 一个事务里两个select，两次select读到的数据可能不是同一个快照。第二次select的时候会看到其它事务已提交的数据。
- 同一个事务中，第一次更新之后，其它事务获得锁进行数据的更新，当前事务中的更新操作需要等到其它事务结束或者回滚再进行操作。
为什么使用RC级别而不使用RR级别？
提高并发度并降低死锁概率。
2>可串行化隔离级别（开销大）
每个命令都是从事务开始时的快照开始执行的。
- 当前事务看不见其它未提交事务的数据；
- 当前事务可以看到自己未提交的数据；
- 只读事务不存在冲突：一个事务里两个select，两次select读到的数据一致。
- 串行化冲突：更新事务与其它更新事务冲突需要重试。
同一个事务中，第一次更新之后，其它事务获得锁进行数据的更新，当前事务中的更新操作需要等到其它事务结束或者回滚再进行操作。如果其它事务回滚，当前事务正常进行；如果其它事务提交，那么当前事务回滚（ERROR: could not serialize access due to concurrent update），需要重试。
3）多版本并发控制（MVCC，Multi-Version Concurrency Control）
MVCC是数据库并发访问时，保证数据一致性的一种方法。实现MVCC的方法有以下两种：
- 写新数据时，把原数据移到一个单独的位置，如回滚段中，其它用户读数据时，从回滚段中把原数据读出来。（Oracle和Mysql数据库中的InnoDB引擎使用这种方法）
- 写新数据时，原数据不删除，而是把新数据插入进来。（pg使用这种方法）==》相当于每个事务看到的都是之前一小段时间的数据快照（某一个数据库版本）。
1>原子性保证
事务ID：XID、txid（transaction id）
pg中每个事务开始时，事务管理器都会分配一个唯一id，从3开始递增。
32位无符号整数，取值空间：2^32-1；如果超过范围从头开始算，称为事务回卷。
pg中表中有以下4个内置表字段，每个tuple的更新时是先del旧的再insert新的tuple。
| 字段 | 说明 | 默认值 | 举例 |
|---|---|---|---|
| xmin | insert tuple 时的 xid | ||
| xmax | del tuple 时的 xid | 0，表示未删除 | |
| cmin | 事务内部 insert 的命令ID | 0，递增 | |
| cmax | 事务内部 del 的命令ID | 0，递增 | |
| ctid | 磁盘上的物理位置，格式：(page,offset) | (0,1)表示0号page的第1个位置。如果xmax=0，表示最新版本；如果xmax！=0，ctid指向更新后的元组，形成了版本链。 |
- insert tuple：
xmin=事务id xmax=0 ctid=(0,1)，指向当前元组 - delete tuple：
xmax=事务id - 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）
维护了以下一些信息：
- TransactionId xmin; // 记录了未提交并活跃的事务最小xid，如果t_xid < xmin则元组数据已提交：可见
- TransactionId xmax; //记录了已提交事务最大xid+1，如果t_xid >= xmax 则元组未提交：不可见
- TransactionId *xip; // 活动事务id列表
对于t_xid在[xmin, xmax)之间数据，需要结合clog日志判断其修改的数据是否可见
每次select获取当前db SnapshotData，判断数据的可见性。
区分元组t_xmin和快照s_xmin，对于当前元组数据：
- t_xmin<s_xmin && t_xmax == 0，元组插入且事务已提交，可见；
- t_xmin<s_xmin && t_xmax !=0 && t_xmax <s_xmin，元组已删除，不可见；
- t_xmin<s_xmin && t_xmax !=0 && t_xmax >s_xmax，元组删除但未提交，可见；
- 其它：需要结合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多版本优势在于：
- 事务回滚可以立即完成；
- 数据可以进行很多更新，不必像Oracle和InnoDB那样需要经常保证回滚段不会被用完，也不会像Oracle数据库那样，经常遇到ORA-1555错误的困扰。
劣势在于：
- 旧数据需要Vaccum清理。
- 旧版本数据的存在降低查询速率，需要扫描更多的数据块。
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;
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可见性的判断怎么解决？
- pg规定，最早和最新两个事务之间年龄差对多为231。==>事务冻结来保证
即：xid空间虽然有232，作为一个环被一分为二，对某个特定的xid，其后231个xid属于未来，均不可见；其前231个xid属于过去，可见。 - 俩个事务之间id的计算使用2^31取模的方法来进行事务的比较：
diff = (int32) (id1 - id2);来判断id1<id2
举例：txid=100的事务，[101,231+100] 均为不可见事务；[231+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);
}
普通xid比较：diff = (int32) (id1 - id2);
注意：int 32带符号，第一位表示符号位，取值范围为 [-2(n-1), 2(n-1)-1]，即[-231,231-1]。
当两个txid（32位）相减 (id1 - id2)>231时，发生回卷，转换位int32时符号位从0变成了1，(int 32) (id1 - id2)是个负数。此时id1<id2==true，id1事务更小更旧，id1对id2是可见的。
举例：id1=231+101，id2=100。id1-id2=231+1，int32转换后这个值是-1，小于0，因此id1的事务反而要旧，id2可见id1，id1不可见id2。
但是如果xid=100确实是很久很久的事务，那么对于231+101这个事务看见，此时上面的判断就是错的。为了避免这种问题，pg必须保证一个数据库中两个有效的事务之间的年龄最多是231（同一个数据库中，存在的最旧和最新两个事务txid相差不得超过231）。这时靠事务冻结来保证。
3>事务冻结（freeze）
当超过231时，就把旧的事务换成一个FrozenTransactionId=2的特殊事务，当正常事务ID与冻结事务ID比较时，会认为正常xid比FrozenTransactionId更新。
a）freeze实现：
- pg9.4之前freeze方法：
直接将符合条件的元组的t_xmin设置为2，回收原来的xid。但这样实现的问题是：
一）当前可见的数据页需要全部扫描，带来大量的IO扫描；
二）符合条件的元组需要更新xmin，造成大量脏页，带来大量IO - 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过程
- 客户端连接
客户端发送一条查询给服务器； - 查询缓存
服务器先检查查询缓存query cache，如果命中了缓存，立即返回缓存结果。 - 解析器->预处理器->查询优化器
1）SQL解析parsing
解析器扫描查询并检查语法。正确则将查询文本转换成解析树。
2）预处理transition
3）优化器optimization
遍历语法树，生成若干执行计划比计算每个算子执行成本； - 查询执行引擎
根据最优执行计划，调用存储引擎的API来执行分布（distribution）查询； - 将结果返回给客户端。
- 关闭连接
2）算子原理
PG目前有19个查询算子。
每个执行计划都是一颗树，执行时自顶向下开始，自底向上计算，最终得到查询结果。
1>Sort
输入：1个结果集；
场景：order by、Unique算子、
原理：
- sort_mem可以调整：
如果结果集的大小超过sort_mem，Sort会将输入集分发到已排序工作文件的集合中，然后再次将工作文件重新合并在一起。如果结果集适合sort_mem*1024字节，则使用 QSort 算法在内存中进行排序。
2>Seq Scan（全表扫描）
- 场景：查询未匹配到合适的索引、order by操作
- 原理：
从表头扫描到表尾，每一行数据执行where过滤，将得到的结果放入结果集中。 - 输出：过滤后的结果集；排序：表顺序。
3>Index Scan（索引扫描）
原理：
- 如果where针对索引过滤了，会直接限定索引查询范围。
- 返回结果排序：索引顺序。
- 并非所有索引都是可扫描的。可以扫描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()
原理：
- 如果没有group by：Aggregate->Seq Scan;
读取结果集，计算，输出一个值； - 如果有group by：Aggregate->Group->Sort->Seq Scan;
8>Append
场景：UNION、表继承
输入：2+个结果集
原理：返回第一个输入集的所有行，然后拼接第二个输入集的所有行。
9>Result
性能影响不大。
场景：
- 常量查询；
select timeofday();
where 1==1；
- 辅助Append算子；
10>Nested Loop（嵌套循环）
- 场景：inner joins, left outer joins, and unions；
因为不遍历整个内表，不能处理其它连接：full, right join
SELECT * FROM customers, rentals
WHERE customers.customer_id = rentals.customer_id;
- 原理：
第一个表作为外表，全部加载进内存，挨个轮询遍历；
第二个表作为内表，搜索（全表扫描或者索引扫描）满足连接条件的行 - 场景：
适合两个表的数据量都比较少的情况(最简单的 table join 方式)。
1）外表为小表，且过滤后的数据量较少。
2）内表的关联列上有高效索引(主键或者唯一性索引)。
11>Merge Join
- 输入：2个表，一个外表一个内表；
- 原理：
1）两个表按连接字段排序：index scan->sort
2）两个表分别按行读取，2个指针分别读外表、内表，同上指针指向相同的连接字段；
由于有序所以只需要同步遍历两张表就可以连接。 - 场景：inner joins, outer joins, and unions
对于那些连接列上有索引的表（已排好序）Merge JOIN性能会优于Hash JOIN。
12>Hash算子、 Hash Join算子
两个算子一起工作。
- 场景： inner joins, left outer joins, and unions
hash join一般针对那些没有索引或者其中任一个有索引的大表。
--所有等值连接都可以转换为inner joins
SELECT * FROM customers, rentals
WHERE customers.customer_id = rentals.customer_id;
- 原理：
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
- Setop Intersect 算子
遇到 INTERSECT时使用。 - Setop Intersect All 算子
遇到 INTERSECT ALL时使用。 - Setop Except 算子
遇到 EXCEPT时使用。 - 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出一些与数据库实例相关的辅助进程，并对其进行管理。
| 辅助进程 | 作用 | 配置 |
|---|---|---|
| Logger | 系统日志 | 参数logging_collect设置为on时启动该辅助进程 |
每次客户端与数据库建立连接时，pg数据库都会启动一个服务进程来为该连接服务，故而是进程架构模型，而MySQL是线程架构模型。当某个服务进程报错时，Postmaster主进程会自动完成系统恢复，恢复过程中停掉所有的服务进程，然后进行数据的一致性恢复，恢复完成后数据库才能接受新的连接。
1）autovacuum进程
autovacuum 是 postgresql 里非常重要的一个服务端进程，能够自动运行，在一定条件下自动触发对 dead tuples 进行清理并对表进行分析。
在pg中更新、删除行后，数据行并不会马上从数据块中清理掉，而是需要等VACUUM时时清理。为了加快VACUUM速度并降低对系统I/O性能的影响，pg8.4.1之后为每个数据块文件加了一个后缀为“_vm”的文件（可见性映射表文件，VM文件）。这个文件为每个数据块存储了一个标志位，标记数据块中是否存在要清理的tuple。
VACUUM有两种方式：
- Lazy VACUUM ：使用VM文件，扫描部分数据块。
- Full VACUUM ：全量扫描数据块。
vacuum相关的配置：
| 参数名 | 说明 | 优化思路 |
|---|---|---|
| autovacuum | 默认为on，表示是否开起autovacuum。当需要冻结xid时，尽管此值为off，PG也会进行vacuum。 | |
| autovacuum_naptime | 下一次vacuum的时间，默认1min | 通过缩短实际，调整回收频率，减少每次回收量，可以减小wal压力 |
| log_autovacuum_min_duration | 向日志打印autovacuum的统计信息（以及资源消耗），大于阈值，输出这次autovacuum触发的事件的统计信息 。 “-1”表示不记录。“0”表示每次都记录。 | |
| autovacuum_max_workers | 最大同时运行的worker数量，不包含launcher本身。 | CPU核多、IO优秀时，当DELETE\UPDATE非常频繁时适量调多点。注意最多可能消耗这么多内存： # autovacuum_max_workers * autovacuum mem(autovacuum_work_mem) |
| autovacuum_vacuum_threshold | 默认50。与autovacuum_vacuum_scale_factor（默认值为20%）配合使用。当update,delete的tuples数量超过autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold时，进行vacuum。 | 改小可以降低vacuum触发条件，提高vacuum频率 |
| autovacuum_analyze_threshold | 默认50。与autovacuum_analyze_scale_factor（默认10%）配合使用。当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold时，进行analyze。 | 改小可以降低vacuum触发条件，提高vacuum频率 |
| autovacuum_freeze_max_age和autovacuum_multixact_freeze_max_age | 前者200 million，后者400 million。离下一次进行xid冻结的最大事务数。 如果表的事务ID年龄大于该值, 即使未开启autovacuum也会强制触发FREEZE，并告警Preventing Transaction ID Wraparound Failures。 | 设置较大值，减少因事务id消耗造成全表扫描的频率。（1000million、1200million） |
| autovacuum_vacuum_cost_delay | 如果为-1，取vacuum_cost_delay值。autovacuum触发的vacuum、freeze、analyze的平滑化调度。 | 设置过大，会导致AUTOVACUUM launcher触发的vacuum耗时过长。特别是大表，耗时会非常长，可能导致膨胀等问题。可以调小一点，0. |
| autovacuum_vacuum_cost_limit | 如果为-1，到vacuum_cost_limit的值，这个值是所有worker的累加值。 | |
| vacuum_freeze_table_age | 当表的年龄大于vacuum_freeze_table_age，则自动转换成vacuum freeze | 调高可以降低vacuum freeze的频率 |
| vacuum_multixact_freeze_table_age | 当表的年龄大于autovacuum_freeze_max_age，也会强制触发vacuum freeze | 调高可以降低vacuum freeze的频率 |
- 如果开启了autovacuum，当垃圾记录数大于 autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor*reltuples ，autovacuum launcher触发普通的vacuum。
当表的年龄大于vacuum_freeze_table_age，则自动转换成vacuum freeze。 - 如果开启了autovacuum，当新增记录数大于autovacuum_analyze_threshold + autovacuum_analyze_scale_factor*reltuples，autovacuum launcher触发analyze。
- 即使没有开启autovacuum，当表的年龄大于autovacuum_freeze_max_age，也会强制触发vacuum freeze。
4，pg物理存储结构：堆表结构
Relation： 表（table）或索引（Index）。
Page：磁盘中的数据块 。
Buffer：内存中的数据块。
1）堆表结构
- 每个表由若干文件组成
表大小超过 RELSEG_SIZE 个块就会被切分成多个文件；
堆表和索引是分开存储的，而mysql是索引组织的数据存储。
pg的索引存储：索引、tuple地址。 - 每个文件由若干块(block)构成；
- 每个数据块存储了多行数据（tuple）。
1>base目录
- 每个数据库，对应
$PGDATA/base/oid下的一个子目录。
#查看各个数据库的oid
testdb=# select oid, datname from pg_database;
oid | datname
-------+-----------
13025 | postgres
16384 | testdb
1 | template1
13024 | template0
(4 rows)
- 每张表，对应
$PGDATA/base/{dboid}/{relfilenode}
1）relfilenode一般情况下和和tboid一致，但有些情况下也会变化，如TRUNCATE、REINDEX、CLUSTER以及某些形式的ALTER TABLE。
2）在$PGDATA/base/{dboid}中通常会包含三种文件：
| 文件类型 | 举例 | 说明 |
|---|---|---|
| 表数据及索引文件 | 16385 | 文件最大1GB，超大文件会划分成1GB大小的段：filenode、filenode.1、filenode.2 |
| 空闲空间映射文件 | 16385_fsm | 每一个堆和索引关系（除了哈希索引）都有一个空闲空间映射（FSM）来保持对关系中可用空间的跟踪。 |
| 可见性映射文件 | 16385_vm | 提供当前所有page与活动事务可见的元组 |
--查询表所在磁盘的位置
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)
2）数据块（页）
数据块（页）大小为 BLCKSZ，默认 8K，最大为32K。
块的结构：
- 块头(Page Header) ：
a. 块的checksum值；
b. 空闲空间的起始位置和结束位置；
c. 特殊数据的起始位置；
d. 其它信息。 - 行指针：向后顺序排列。是一个32bit的数字，具体结构：
a. 行内容偏移量，15bit；能表示的最大偏移量是215，因此pg中块最大为32kb
b. 指针的标记，2bit；
c. 行内容的长度，15bit。 - 空闲空间：行数据指针和行数据内容之间的空间。
- 行内容：从块尾向前反向排列。
-- 查询表页数、行数
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;
3）Tuple（数据行、元组）
pg中元组由三部分组成——元组头结点、空值位图、用户数据。
HeapTupleHeaderData结构：Database Page Layout，pg提供了pageinspect插件，可查看指定表对应的page header内容。
相关字段说明：
| 字段 | 说明 | 备注 |
|---|---|---|
| t_xmin | 保存插入该元组的事务txid（该元组由哪个事务插入） | 对应pgclass内置字段xmin |
| t_xmax | 保存更新或删除该元组的事务txid。若该元组尚未被删除或更新，则t_xmax=0，即invalid | 对应pgclass内置字段xmax |
| t_cid | 保存命令标识（command id,cid），指在该事务中，执行当前命令之前还执行过几条sql命令（从0开始计算） | 对应pgclass内置字段cmin cmax |
| t_ctid | 一个指针，保存指向自身或新元组的元组的标识符（tid） | 对应pgclass内置字段ctid |
| t_xvac | 存储的是VACUUM FULL 命令的事务ID | |
| t_infomask2 | number of attributes, plus various flag bits | |
| t_infomask | 各种标志位，标识元组的属性、状态 | 比如：是否具有空属性、是否具有变长的属性、是否包含外部存储的字段、事务提交状态 |
| t_hoff | offset to user data，行header的长度 |
5，锁机制
使用多版本并发控制（MVCC）比锁定模型（mysql的锁机制）的优势：读锁和写锁不再互斥。
1）pg常见锁
Postgres 有 3 种锁机制：表级锁，行级锁和建议性锁。
1>表锁
表锁由内置的 SQL 命令获得的，同时可以通过锁命令来明确获取。
| 表锁 | 获得情况 | 冲突对象 | 备注 |
|---|---|---|---|
| 访问共享（ACCESS SHARE） | SELECT读表 | ACCESS EXCLUSIVE | |
| 行共享（ROW SHARE | SELECT FOR UPDATE 和 SELECT FOR SHARE 读表 | EXCLUSIVE、 ACCESS EXCLUSIVE | |
| 行独占（ROW EXCLUSIVE） | UPDATE、INSERT 和 DELETE 读表 | SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE | |
| 访问独占（ACCESS EXCLUSIVE） | ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX，CLUSTER 和 VACUUM FULL 命令读表时获得 | 与所有模式冲突(包括其自身) | LOCK 命令的默认模式。保证事务是可以访问该表的唯一事务。 |
| 共享更新独占（SHARE UPDATE EXCLUSIVE） | VACUUM（不含FULL），ANALYZE，CREATE INDEX CONCURRENTLY，和一些 ALTER TABLE 的命令获得该锁 | ||
| 共享（SHARE） | CREATE INDEX 命令在查询中引用的表上获得该锁。 | ||
| 共享行独占（SHARE ROW EXCLUSIVE） | 不被任何命令隐式获取。 | ||
| 排他（EXCLUSIVE） | 这个锁模式在事务获得此锁时只允许读取操作并行。它不能由任何命令隐式获取。 |
2>行锁
行级锁不影响对数据的查询，它们只阻塞对同一行的写入。
行锁容易发生死锁。==》 pg自动侦测死锁条件，然后退出其中一个事务从而允许其它事务完成来解决死锁问题。
9.1版本的行锁主要分为两类，后续版本有基于此的扩充，但是大差不差，都是功能的细分。
| 锁分类 | 获得方式 | 备注 | 版本 |
|---|---|---|---|
| 排他锁 | SELECT FOR UPDATE | 即使没有实际的修改，也会获取到锁 | |
| 共享锁 | SELECT FOR SHARE | 允许其它事务同时获取共享锁，此时，事务的更新、删除或排他锁都不被允许。 |
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 ...
如何处理这种情况？
- insert select 合成一条sql
将业务逻辑融入了sql中，扩展性差。 - 加锁
- 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');
4）服务器参数说明
通过show 参数 sql语句可以查看当前设置值，修改后需要重启计算机才可以生效。
| 参数 | 默认值 | 说明 | 调优 |
|---|---|---|---|
| deadlock_timeout | 1s | 进行死锁检测之前在一个锁上等待的总时间（以毫秒计）。增加这个值就减少了浪费在无用的死锁检测上的时间，但是减慢了报告真正死锁错误的速度。 | 在一个高负载的服务器上，可能需要增大它。这个值的理想设置应该超过通常的事务时间 |
| log_lock_waits | off | 如果一个会话等待某个类型的锁的时间超过deadlock_timeout的值，该参数决定是否在数据库日志中记录这个信息。 | |
| max_locks_per_transaction | 64 | 共享锁表跟踪在 max_locks_per_transaction * ( max_connections + max_prepared_transactions ) 个对象（如表）上的锁。因此，在任何一个时刻，只有不超过这么多个可区分对象能够被锁住，超过这个数量会报错。 | 如果想在一个事务中使用很多不同表的查询（例如查询一个有很多子表的父表），则需要提高这个值。 |
| max_connections | 151 | 表示允许客户端并发连接的最大数量 | 最小值为1，最大值为100000 |
| max_prepared_transactions | 设置可以同时处于prepared状态的事务的最大数目 | 设置为0表示不使用预备事务；否则max_prepared_transactions至少与max_connections一样大，以便每个会话都可以有一个待处理的预备事务。 |
6, 数据库存储原理
1）硬盘
- 寻址慢（ms级）
- 带宽（G/MBPS）
2）内存
- 寻址快（ns级）
- 带宽 = 带宽×总线宽度÷8(单位换算) 很快
3）4k对齐
- 硬盘中文件保存的基本单元是扇区，不管文件大小，都要占用一个扇区的空间。
- 机械硬盘（Hard Disk Drive，HDD）一个扇区是512字节，固态硬盘（SSD）一个扇区是4K字节。
- 微软操作系统常用的NTFS格式，默认扇区大小512字节，并且规定前63个扇区是保留的。
如果“4K不对齐”，那么从第63个扇区结束，往后的每一个簇都会跨越两个物理单元，占据前一个单元的一部分和后一个单元的一部分。
而“4K对齐”主要是将硬盘的逻辑扇区从第64个扇区开始对齐，即操作系统从第64个扇区开始读写数据，这样就跨过了63扇区的特性，解决了每一个簇跨两个物理单元读写的问题。 - SSD的最小扇区为4k（1页），写入时不允许像机械硬盘一样覆盖写入，而是擦除写入。如果4k不对齐，数据跨页，那么每次写操作都会写入2个不同的扇区，需要写入两次，造成性能下降。4对齐保证了操作系统的逻辑单元与ssd硬件的闪存页对应起来，写入4k数据只需要写一次即可。
- 4k对齐提高硬盘工作效率、提高硬盘工作寿命。
- 4k对齐本质就是扇区对齐，随着扇区变大，会有8k对齐、16k对齐…
4）数据库查询优化
- 随着文件变大，速度查询变慢？-> 访问硬盘受到IO瓶颈影响。
- dataPage：数据库合并文件到页（每一页4k），每次读取一页数据只需要1次IO（多个文件只需要一次IO）。->查找一个文件还是要经过很多次IO读入内存进行筛选，速度依然很慢。
- 建立索引：4k存储模型，指向很多dataPage。也存储在磁盘中。
- 关系数据库行存储：必须给出每个列的类型->字节宽度确定了，空数据填充->行宽度确定->增删改不需要移动数据，直接复写即可。
- B+Tree：只要查询命中索引，那么直接命中叶子结点，确定dataPage范围。->减少磁盘寻址时间
- 如果数据库表很大，性能会变低？
如果有索引，增删改慢。
查询单条、少量数据依然会很快。
并发查询大量数据时，轮流读取dataPage到内存，此时收到磁盘带宽影响速度。 - 数据在磁盘和内存中大小不一样。
因为磁盘中没有指针，数据会比内存中膨胀（同一个数据要写很多遍）。
0，参考资料
PgSQL · 特性分析 · MVCC机制浅析
PgSQL · 特性分析 · 事务ID回卷问题
PgSQL · 引擎特性 · PostgreSQL Hint Bits 简介
7793
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
