【每日分享】2022.08.22 碎片整理（一）

主要用于记录一些零碎的小知识，会不断更新

1. PageHeader中的pd_lsn：称为PageLSN，确定和记录了最后更改此页的xlog记录的LSN，把数据也和WAL日志相关联，用于恢复数据时校验日志文件和数据文件的一致性

2. 64位的LSN保存为两个32位的值：高位为xlogid，低位记录偏移量

3. index-only scan仅索引扫描：指仅扫描索引，也就是说SQL仅根据索引就能获得所需检索的数据，而不需要通过索引回表查询数据。在执行计划中“Heap Fetches”通常出现在index-only scan中

4. 多表关联：多表关联能用使用并行并不是指夺标关联本身使用并行，而是指多表关联设计的表数据检索时能够使用并行处理

   • Nested loop：实际上是一个嵌套循环，伪代码如下：

   

   • Merge join：首先将两个表进行排序，之后进行关联字段匹配
   • Hash join：当关联字段没有索引情况下两表关联通常会进行Hash join

5. 不可重复读(Non-repeatable read)和幻读(Phantom Read)区别：

   • 定义：
     • 不可重复读：一个事务重新读取之前读取过的数据，发现该数据已经被另一个事务（在初始读之后提交）修改，两次查询的结果不一致。
     • 幻读：指一个事务的两次查询的结果集记录数不一致。
   • 作用的对象：
     • 不可重复读：同一行的数据，重复读取到获取不同的值（前后读取的数据不一致）
     • 幻读：获取到不同的结果集（前后读取的记录数量不一致）
   • 操作：
     • 不可重复读：主要受到其他事务对数据的UPDATE操作
     • 幻读：主要受到其他事务INSERT和DELETE操作的影响

6. 事务隔离级别相关设置

   PostgreSQL 默认的事务隔离级别是 Read Committed

   • 全局的事务隔离级别 - default_transaction_isolation

     • 查看

       • pg_settings

         postgres=# SELECT name, setting  FROM pg_settings WHERE name ='default_transaction_isolation' ;
                      name              |    setting
         -------------------------------+----------------
          default_transaction_isolation | read committed
         (1 row)
         

       • current_setting()

         postgres=# select current_setting('default_transaction_isolation');
          current_setting
         -----------------
          read committed
         (1 row)
         

     • 修改 - reload 重新加载配置参数后生效

       • postgresql.conf文件的 default_transaction_isolation 参数
       • ALTER SYSTEM 命令

   • 当前会话的事务隔离级别 - transaction_isolation

     • 查看

       • SHOW

         postgres=# show transaction_isolation ;
          transaction_isolation
         -----------------------
          read committed
         (1 row)
         

       • current_setting()

         postgres=# select current_setting('transaction_isolation');
          current_setting
         -----------------
          read committed
         (1 row)
         

     • 修改

       • SET SESSION

         postgres=# set session characteristics as transaction isolation level READ UNCOMMITTED ;
         SET
         postgres=# show transaction_isolation ;
          transaction_isolation
         -----------------------
          read uncommitted
         (1 row)
         

   • 当前事务的事务隔离级别

     • 设置

       在启动事务的同时设置事务隔离级别

       • START

         start transaction isolation level READ UNCOMMITTED ;
         

       • BEGIN

         begin isolation level READ UNCOMMITTED ;
         

7. 并发控制

   • 基于锁的并发控制(Lock Based Concurrency Control) - 悲观机制

     • 排他锁(Exclusive locks , X 锁)

       被加锁的对象只能被持有锁的事务读取和修改，其他事务无法在该对象上加 他锁，也不能读取和修改该对象

     • 共享锁(Share locks, S 锁)

       被加锁的对象可以被持锁事务读取，但是不能被修改，其他事务也可以在上 面再加共享锁

   • 基于多版本的并发控制(Multi-Version Concurrency Control) - 乐观机制

8. json和jsonb区别

   • 存储格式

     • json - 文本
     • jsonb - 二进制，存储时会删除空格以及重复的键值对(保留后插入的键值);

   • 处理效率

     • 检索：json < jsonb

       json检索数据时需重新解析；而jsonb以二进制形式存储已解析好的数据，检索时不需重新解析

     • 写入：json > jsonb

       json 类型存储的内容和输入数据一样

9. buffer和cache

   • 缓冲(buffer)：写入数据时，先把数据放入缓冲区，再一起向硬盘写入；
   • 缓存(cache)：读取硬盘数据时，数据保存在内存的缓存中

   缓存释放：echo 1 > / proc/sys/vm/drop_caches

10. EXPLAIN

    PostgreSQL中可以使用EXPLAIN命令来查看执行计划。EXPLAIN ANALYZE命令使语句会被执行。

    在分析 INSERT、UPDATE、DELETE、CREATE TABLE AS 或者EXECUTE命令的查询计划时，应该在一个事务中执行，得到查询计划后对事务进行回滚

    阅读查询计划的简单原则：从下往上看，从右往左看。