德哥 posgresql视频教程笔记

第二章：数据库的物理，逻辑，进程结构以及表系统函数

1.逻辑分层：（1）Cluster（2）Database（3）Schema（4）Table,Index,View,Function,Sequence（5）Rows,Columns

2.物理分层：（1）每一个Table,Toast,Index,Sequence对应一个或多个datafile；(2)Archives对应Wals归档日志文件。

3.进程：

（1）postmaster:监听

（2）bakend process:写Wal buffer，Shared buffer

（3）bgwriter:写datafile

（4）Wal writer:把Wal buffer写入Xlogs

（5）Archiver:归档文件

第三章：相关sql

1.布尔值：true,false,unknow

2.全文检索类型：tsvector:分词 tsquery:搜索的关键字。

3.数组不限长度，维度。

4.复合类型：创建：create type 更新：row()

5.record：弹性数据类型

6.函数的三种状态：（1）Immutable（2）Stable（3）Volatile

7.with语法：将某个查询作为子表。

第四章：索引

1.索引实现方式

（1）Btree：支持=，>,>=,<，<=

（2）Hash：只支持=

（3）Gin:支持多值列（如：数组，全文检索类型）如：包含操作符等

（4）Gist:多列索引。索引框架。<<严格在左，>>严格在右

2.索引的作用（是否走索引与几个与索引有关的选择器有关）

（1）排序

（2）带条件的查询，更新，插入

（3）加速join

（4）加速外键更新和删除

（5）排他约束

3.特殊索引类型

（1）多列索引

（2）.部分值索引：只对部分值建立索引，数据不平衡时适合建立的索引

（3）函数索引/表达式索引：状态必须是stable或immuable

4.全文检索

第五章：执行计划

1.union要去重，所以要排序。

第六章：连接池

1.适合长连接，不适合短连接

2.为什么要用连接池？（1）因为postgresql是进程模式，短连接会带来性能问题。（2）缓解最大连接数限制。（3）挡住非法请求。（4）负载均衡

3.pgbouncer连接池：会话模式，事务模式，语句模式。处理短连接比posgresql更好。

4.高速缓存:（1）数据库本身的shared buffer，大小有限，默认128M（2）本地高速缓存pgfincore，数据库本身用的多（3）异地高速缓存pgmemacache，应用程序用的多

第七章 数据库的备份和还原

一.物理备份和还原

1.热备份：数据库在启动时（可进行MDL操作）备份。

2.备份：（1）pg_base_backup : 通过流复制协议备份 (2)pg_start_backup

3.还原：pg_create_restore_point

4.原理：data change -> WALS -> archive ->基础备份，归档日志

二.逻辑备份和还原

1.pg_dump:

(1)二进制文件：压缩，可以用pg_restore精细化还原（指定还原表）

(2)文本：大库不推荐

2.pg_dump——all:

文本：一般备份全局元数据对象：如：用户密码，数据库，表空间。

3.COPY

SQL子集或表的备份，表的还原

第八章 数据库的流复制

流复制：把整个数据库的变更通过xlog复制到 stand by 节点，使stand by节点和主节点保持一致。不写xlog的操作没办法复制（如：hash索引）。表级复制可解决。

第九章：数据库触发器复制：不适合大数据量

基于触发器的复制：

quato_literal:自动转义

第十章：物化视图：对数据实时性要求不高的场景

1.存储是单独的，数据已经取过来。

2.增量刷新是join后刷新变更部分数据。

第十一，二，三，四，五章：表级复制

longdiste3:

第十六，七章：本地分区&异地分区

1.无分区表的语法，通过继承管理分区表。

2.分区的好处

（1）通过分区全表扫描效率高于通过索引全表扫描。

（2）可以把不常用的分区放在便宜的物理存储。

（3）单独vacuum分区。

3.本地分区分类

(1)范围分区

(2)哈希分区：hashtext()函数必须一致

(3)list分区：key值分布均匀

4.要继承索引,主键，约束等：like p1 including all

5.支持多继承（多对多）

6.异地分区：shared nothing分区 所有节点存储的数据

 7.apps -1> PL/Proxy -2> db   PLsql:路由，负载均衡，函数原子性，防止直连数据库，减少交互。1是短连接，建议加连接池。2是长连接。代理和非代理模式。

第十八章：事务和并发

1.一条记录的多个版本实现并发控制。

2.脏读：读取了未提交的事务。

3.master支持串行事务隔离级别；hotstandby不支持，只支持可重复读，read comiited.

第十九章：统计信息

1.pg_statas_cellector

2.事务中获取统计信息只获取一次