数据库的三种连接算法
1、Nested Loop Join
2层循环连接，外层循环逐行检索内层循环的每一行。最基础的连接方式，表比较小且被驱动表有索引的情况下，效率很高。无索引or表较大时性能急剧下降。
2、Hash Join
分2个阶段：
- 构建：选取表较小者基于连接字段在内存中构建一个哈希表
- 探测：遍历较大表(探测表)，对其每行的连接字段应用同样的哈希函数，快速定位到哈希表中对应位置，找到匹配的行。
特征：适合大表等值连接、无需索引，但需要额外内存来建构哈希表
3、Sort Megre Join
分2个阶段：
- 排序：将2个表各自按照连接字段进行排序
- 合并：像合并2个有序链表一样，同时遍历2个已排序的结果集，一次性找到所有匹配行
特点：当连接条件是非等值连接时，或已预先排序，可能比Hash Join更有效。排序本身可能需要消耗大量资源。
数据类型
- 布尔类型：支持标准boolean类型.
- 值可以是true/false、true/false、yes/no、t/f、0/1。
- 逻辑操作符：AND、OR、NOT
- 数值类型
- smallint 2字节
- int 4字节
- bigint 8字节
- real 4字节浮点，不精确、变精度
- doulbe precision 8字节浮点数，不精确、变精度
- money 8字节，货币类型，保证精度，不通国家精度不一致
- 字符类型
- varchar （character varying）变长，最大1G，存储空间：4 + 实际字符串长度
- char (character) 定长，不足则补空白，最大1G，存储空间也是4 + n
- text 变长，无限制长度。
- 二进制类型：bytea类型，适合存储原始字节数据，例如图片
- 日期与时间类型
- date：日期，4字节
- time：时间，8字节，带时区12字节
- timestamp：带不带时区都8字节。以2000年1月1日0点之前或之后的秒数存储。
- interval ：12字节，时间间隔，用于日期时间加减计算等。
- 时区设置：select timestamp with time zone '2025-11-20 06:30:45 PST'
- 其他类型：枚举、几何、网络地址、数组、符合类型、xml、range、伪类等
(补充)JSON类型
- json：数据原封不动存放至数据库、使用时再解析。保留json串中key的顺序，重复key仅保留最后一个。
- jsonb：存储时将json转换为二进制格式，使用时无需解析，且支持建索引，使用性能更高。不保留多余空格、不保留key顺序和重复key
json类型操作
select '[1,2,3]'::json->2 out:3
select '{"a":1,"b":2}'::json ->>'a' out：1
select '{"a":{"b":{"c":1}}}'::json #>'{a,b}' out：{"c":1}
->和->>的区别：->返回原类型，->>返回转换后的text类型
jsonb '[1,2]' = jsonb '[1,2]' 检测json对象内容一致性
@> 左包含
@< 右包含
? 检测key是否存在
?| 检测数组中是否有任意字符串存在于json的key中
?& 检测数组中是否所有字符串存在于json的key中
jsonb索引
jsonb支持BTree和GIN索引，默认是GIN索引，因为BTree效率相对较低，不关心jsonb内部数据类型，仅简单按整个jsonb大小方式进行排序。
数据类型转换
1、CAST()函数
select cast('5' as int)
2、::双冒号转换
select '10'::int
常用函数
current_date、current_timestamp
当前日期、时间戳
now()
返回当前时间戳（带时区）
extract()、date_part() //二者等效
从日期/时间中抽取子域，返回double precision类型数据。可返回年份/季度/月份/第几个星期/第几天/时/分/秒等。
select extract(dom from now()) //获取当前时间是星期几
复合类型添加、新增用法
create type zyl as ( // 创建复合类型
z int,
y double precision,
l timestamp
)
//新增复合类型新增数据，可单独为某个字段赋值，未标注的默认为NULL
insert into zhouyinglin values(row(10,10.00,now()),'zyl',now())
insert into zhouyinglin (zhou.z,ying,lin) values(100,'zyl3',now())
//查询复合类型，使用括号隔开列名
select * from zhouyinglin where (zhou).z=10
select * from zhouyinglin where (zhouyinglin.zhou).z=10
//修改复合类型
update zhouyinglin set zhou=row(10,10.00,now()) where (zhou).z=10
update zhouyinglin set zhou.z=50.00 where (zhou).z=10
执行计划
查看执行计划：explain analyze verbose select .....
- explain：显示查询计划
- 执行计划节点：计划由多个操作节点组成，例如Seq Scan、Index Scan、Hash Scan、Sort等。
- 树形结构：节点以树的形式组织，数据从叶子结点流向根节点。
- analyze：实际执行该查询，收集每个步骤详细的运行时间、返回的行数等
- Actual Time：该节点实际执行时间（毫秒）
- Rows：该节点实际返回的行数
- Loops：该节点被执行的次数（嵌套循环中可能会执行多次）
- verbose：输出修饰符，展示比explain、analyze更详细的信息
- 输出列表：显示每个节点输出的列名
- 模式限定：表名以schema.table的形式显示
- 表达式计算：如果查询中有表达式，会显示详细求值信息
表逻辑结构
数据库服务>数据库>表/索引>行
模式：数据库的一个命名空间或目录，不同模式下可以有相同的表、函数等对象，而不会冲突。模式提出主要用于方便管理。在PG库中，不能同时访问不同数据库对象，但可以访问不同模式间的对象。类似Mysql中的不同的Database。类似Linkdo开发环境中，将linkdo和linkdoapi放在同一个库的不同模式下。
pg在新建一个新数据库时，会默认创建一个public模式登录数据库后，若没有指定，则默认访问public模式内的对象。
新建模式：create schema xxx
模式授权给用户：GRANT USAGE ON SCHEMA {schema_name} TO {username};
PG表的TOAST技术
TOAST技术主要用于存储大字段的值，由于PG页面大小固定且不允许行跨越多个页面，所以不能直接存储较大的值，转而压缩或者拆分到系统表，即TOAST表。只有变长的数据类型才支持TOAST，变长数据类型中，前4字节（即32bit）成为长度字，长度字的高2bit是标志位，后30bit是长度值，对应1GB。2bit标志位分别对应【压缩标记位】和【是否行外存储】，不论是否压缩、是否行外存储，长度字内的30bit都表示数据的实际尺寸，而不是压缩后的数据长度。
- 压缩标记位：设置后，使用前需要先解压缩
- 行外存储：设置后，30bit长度位后面只是一个指针
修改字段设置
ALTER TABLE blog ALTER content SET STORAGE EXTERNAL;
如果表里有任意字段是支持TOAST的，那么就会自动为该表创建一个关联的TOAST表，行位内容就保存在这个表中。
TOAST策略
- PLAIN：避免压缩、行外存储
- EXTENDED：允许压缩和行外存储，优先压缩、后行外存储
- EXTERNAL：允许行外存储、不允许压缩
- MAIN：允许压缩、不允许行外存储
PostgreSQL11之后，可通过参数toast_tuple_target 来控制TOAST触发时机，单行数据在INSERT或UPDATE时，如果超过指定数值，就会将该行数据改为TOAST存储。
alter table test01 set (toast_tuple_terget=128); //128单位为字节
toast_tuple_terget属于单行优化存储。另外，还可以通过fillfactor、toast.filltactor来控制数据更新时数据块扩展时机。
fillfactor：当前表的填充因子
toast.fillfactor：当前表对应的toast表的填充因子
填充因子取值：10~100，表示数据块在当前页(page)INSERT多少之后就不再继续填充了，仅保留做UPDATE使用。例如设置60，则剩下40只当做UPDATE使用。
补充：PostgreSQL的UPDATE流程
PG在操作UPDATE时，原数据行并不会被覆盖，而是会插入一条新数据行，由于新数据行仍在当前页，因此可以通过Heap-Only Tuple在原行和新行之间建立一个链表，因此UPDATE后不需要重新建索引，索引仍会指向原行，并通过链表找到最新行。
问题：如果UPDATE插入到其他页，则无法使用HOT，这时需要更新表上全部索引，会产生较大的开销。因此，对于更新频繁的表，需要设置一个较小的fillfactor值。
而Linkdo最常更新的linkdo_task_instance表，通过查询系统目录信息并没有查到相关fillfactor配置，而是更加激进的配置：
SELECT reloptions AS table_options
FROM pg_class
WHERE relname = 'your_table_name';
["autovacuum_vacuum_scale_factor=0.02","autocvacuum_analyze_scale_factor=0.01"]
- autovacuum_vacuum_scale_factor=0.02：当表中有 2% 的行被更新或删除时触发 vacuum（默认是 20%，即0.2）
- autovacuum_analyze_scale_factor=0.01：当表中有 1% 的行被更改时触发 analyze（默认是 10%。即0.1）
补充：vacuum和analyze
vacuum是PG库中的空间清理工，用于：清理死元祖、更新可见性地图等，analyze负责查询优化，主要用于收集表的行数、页数，每个列的数值分布、最常见值、更新系统目录。如果没有这些信息，优化器可能会选择很差的查询计划。
[数据变更] → [产生死元组] → [VACUUM清理] → [空间重用]
↓
[统计信息过时] → [ANALYZE更新] → [优化器获得新信息]
临时表
PG库中临时表有2种，一种是单纯的临时表，另一种是半持久化表。
- 临时表：
- 会话级临时表：数据保存在整个会话中
- 事务级临时表：数据保存在整个事务中
- UNLOGGED表：不产生WAL日志、数据库异常则数据丢失，正常重启不会丢失数据。在使用上与其他表没有差异
// 创建临时表如下4种写法效果一致
create TEMPORARY table tmp_t1(id int primary key, note text);
create TEMP table tmp_t1(id int primary key, notes text)
create GLOBAL TEMPORARY table tmp_t1(id int primary key, note text);
create LOCAL TEMPORARY table tmp_t1(id int primary key, note text);
// 创建unlogged表
CREATE UNLOGGED TABLE unlogged01(id int primary key, t text);
约束
检查约束/非空约束/唯一约束/主键约束/外键约束
修改表
增删字段
alter table table_name add column column_name type;
alter table table_name drop column column_nmae;
增删约束
alter table table_name add check(age > 16) //设置检查约束
alter table table_name alter column column_name set not null //设置非空约束
//删除约束前需要知道约束名称是什么
alter table table_name drop constraint constraint_name
//非空约束没有名字，通过如下语法删除
ALTER TABLE student ALTER COLUMN student_name DROP NOT NULL;
修改/删除默认值
// 修改只影响后续INSERT的默认值
alter table table_name alter column column_name set default 15;
// 删除默认值，即将默认值修改为NULL
alter table table_name alter column column_name drop default;
修改字段数据类型
// 只有字段内所有项都可以隐式转换为新类型时，才能修改字段类型，无法隐式转换则会修改失败
alter table table_name alter column column_name type text;
字段/表重命名
alter table table_name rename column age to age_num; //重命名字段
alter table table_name rename to table_name2; //改表明
继承表
pg库的表是允许继承的，且允许多继承，继承表的特性如下：
- 子表插入/更新数据后对父表可见，父表插入/更新数据后对子表不可见
- 如果只想查询父表可使用关键字【only】
- 父表所有检查约束、非空约束都会自动继承给子表，其他约束不会继承
- 子表继承父表后，子表拥有所有父表字段总和，类型相同的字段会被融合，融合字段同样继承约束。
基于继承表的分区表
PG库在版本10之前均通过表继承实现分区表，而10之后提供了DDL语句创建声明式分区表，但原理仍然是表继承。表分区就是逻辑上把一个大表分割成物理上的几块，表继承的优势在于：
- 查询、删除更新等操作性能提升，避免全表扫描
- 访问较少的历史数据可以使用表空间技术移动到便宜的慢速存储介质上
创建分区表步骤
1、创建父表，配置好字段。父表不插入数据、不定义约束。
2、创建子表，继承父表字段且不新增字段，自定定义约束，通过关键字段来控制分区，并为关键字段创建索引。
3、定义一个触发器，将原本插入主表的数据重定向到合适的分区表，
浙公网安备 33010602011771号