SQL高级特性

Word count: 4.4k   |   Reading time≈ 16 min

视图(VIEW)

视图（View）是基于 SQL 查询结果创建的虚拟表，本身不存储数据，而是保存一条 SQL 查询定义。

  • 当访问视图时，数据库会执行定义视图的查询，并返回结果。
  • 用途：
    • 简化复杂 SQL 查询，提高可读性。
    • 对用户隐藏表结构，起到安全隔离作用。
    • 可用于权限控制，限制用户访问数据的范围。
  • 示例：
    1
    2
    3
    4
    CREATE VIEW high_salary AS
    SELECT name, salary
    FROM employees
    WHERE salary > 10000;
    使用时：
    1
    SELECT * FROM high_salary;

事务（Transaction）

事务是所有数据库系统的基础概念。事务最重要的一点是它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作。步骤之间的中间状态对于其他并发事务是不可见的，并且如果有某些错误发生导致事务不能完成，则其中任何一个步骤都不会对数据库造成影响。

事务与 ACID

  • 原子性（Atomicity）：事务内的所有操作要么全部成功（COMMIT），要么全部撤销（ROLLBACK）。
  • 一致性（Consistency）：事务结束时，数据库从一个一致状态转到另一个一致状态（例如外键、约束未被破坏）。
  • 隔离性（Isolation）：并发事务之间互不干扰；一个事务在提交前的中间修改对其他事务不可见，避免脏读、不可重复读、幻读等问题。
  • 持久性（Durability）：一旦事务提交，修改被持久保存（WAL 日志保证）。

基本事务控制语句（PostgreSQL）

1
2
3
4
5
6
7
8
9
10
11
12
-- 显式事务块
BEGIN;           -- 或 START TRANSACTION;
  -- 若干 DML/DDL/查询
COMMIT;          -- 提交

-- 如果要撤销
ROLLBACK;

-- 保存点
SAVEPOINT sp1;
ROLLBACK TO sp1;
RELEASE SAVEPOINT sp1;

自动提交（Autocommit）

  • PostgreSQL 默认 自动提交 模式：如果不显式使用 BEGIN，数据库会把每条独立语句当作一个隐式事务执行（成功则立即提交）。

隔离级别与并发现象

PostgreSQL 支持标准的隔离级别（但实际 READ UNCOMMITTED 被视作 READ COMMITTED）：

  1. READ COMMITTED（默认）

    • 每个语句只能看到其他事务已提交的更改。
    • 可能发生“不可重复读”（同一事务内相同查询得到不同结果）。

  2. REPEATABLE READ

    • 事务范围内的相同查询返回一致结果（解决不可重复读）；仍可能出现幻读

  3. SERIALIZABLE

    • 最严格的隔离级别，保证结果等价于某种串行执行（可能导致事务冲突并回滚）。

注：PostgreSQL 的具体并发现象与隔离级别实现细节相对复杂，建议在生产场景选择合适隔离级别并通过测试验证。

锁 的机制和管理

在 PostgreSQL 中，锁（Lock）是用来控制多个事务对同一资源并发访问的同步机制。当多个事务同时访问数据库的同一份数据时，锁用于防止数据不一致、保持隔离性。锁是实现隔离性的核心机制，用于协调多个事务对共享资源（数据页、行、表等）的访问。

锁的分类

锁类型 作用对象 使用场景 特征
表级锁（Table-level Lock） 整个表 DDL / DML 语句 粒度大，持有时间短
行级锁（Row-level Lock） 单行记录 SELECT ... FOR UPDATE、UPDATE、DELETE 精细化控制，MVCC 兼容
页级锁（Page-level Lock） 数据页（8KB） VACUUM、索引维护 系统内部使用
事务锁（Transaction-level Lock） 事务ID（XID） 防止提交/回滚冲突 内部依赖
轻量级锁（Lightweight Lock, LWLock） 内部共享结构 缓冲区、WAL 管理 非用户可见
自旋锁（SpinLock） CPU 级同步 临界区保护 极短期锁，用于内核代码

表级锁

表级锁及兼容性：

锁模式 常见触发操作 与其他锁兼容性（✅兼容 ❌冲突）
AccessShareLock SELECT ✅ 大多数锁（除Exclusive）
RowShareLock SELECT ... FOR UPDATE/SHARE ❌ Exclusive
RowExclusiveLock INSERT、UPDATE、DELETE ❌ 共享类锁
ShareUpdateExclusiveLock VACUUM、ANALYZE、CREATE INDEX CONCURRENTLY ⚠️ 限制中等
ShareLock CREATE INDEX ❌ 与更新锁冲突
ShareRowExclusiveLock CREATE TRIGGER ❌ 几乎所有写锁
ExclusiveLock ALTER TABLE ❌ 除 AccessShareLock 外均冲突
AccessExclusiveLock DROP TABLE、TRUNCATE、REINDEX ❌ 与所有锁冲突

行级锁

锁模式 语句来源 含义
FOR UPDATE SELECT ... FOR UPDATE 阻止他人修改/删除该行
FOR NO KEY UPDATE UPDATE（未更改主键时） 允许其他事务 FOR KEY SHARE
FOR SHARE SELECT ... FOR SHARE 允许并发读，阻止修改
FOR KEY SHARE SELECT ... FOR KEY SHARE 最弱行锁，允许更新非主键字段

轻量级锁与自旋锁

锁类型 用途 粒度 特征
轻量级锁LWLock 保护共享结构，如缓冲区链表、WAL缓冲区 中等 支持共享/独占模式
自旋锁SpinLock 极短代码段的同步 微观 忙等，不可睡眠

锁的实现机制以及流程

PostgreSQL 使用一套统一的 Lock Manager（锁管理器）实现所有表级、行级及事务锁。
所有锁信息存放于 共享内存（Shared Memory）；
锁的主要结构：

  • LOCKTAG：锁对象标识（如 relation、tuple、transaction）；
  • LOCK：实际锁结构；
  • PROCLOCK：事务与锁对象的对应关系。

锁请求流程：

1
2
3
4
5
事务请求锁 →
Lock Manager 检查兼容性 →
若冲突则进入等待队列 →
被唤醒后获得锁 →
操作完成后释放锁

可以通过pg_locks查看当前锁的情况：

1
2
3
SELECT pid, locktype, mode, granted, relation::regclass AS table, virtualtransaction
FROM pg_locks
WHERE NOT granted IS NULL;

MVCC(多版本并发控制)

简单说，它让多个事务可以同时访问同一张表的数据，
而又互不干扰、互不锁表。

传统的并发控制方式是加锁（Locking）：
当一个事务正在修改某行数据时，其他事务必须等待,并发性能差。

而 MVCC 不加锁读取数据：

  • 读操作读取“历史快照（snapshot）”，不阻塞写；
  • 写操作创建“新版本（new version）”，不阻塞读。

✅ 核心思想：数据库中的每一行数据都有多个版本（versions），不同事务可以看到不同版本的数据。

核心机制：

在 PostgreSQL 中，每一行数据（tuple）都包含以下两个隐藏字段：

字段 含义
xmin 插入该行的事务 ID
xmax 删除（或更新）该行的事务 ID

当一个事务读取数据时，它根据自己的“快照（snapshot）”决定：

  • 哪些版本是可见的；
  • 哪些版本是对自己不可见的（未提交或已删除）。

PostgreSQL 的 UPDATE 实际上是 “删除旧行 + 插入新行”；

MVCC 的实现方式总结:

操作 动作 说明
SELECT 读取可见版本 不加锁，只看快照
UPDATE 创建一个新版本（新行） 原版本打上 xmax，表示“过期”
DELETE 设置 xmax 逻辑删除，不立即物理删除
VACUUM 清理旧版本 回收空间，防止膨胀

示例：多版本快照

  1. 事务 T1 执行：

    1
    INSERT INTO users VALUES (1, 'Alice');

    系统会：
    为这行记录生成一个新的版本,设置 xmin = T1, xmax = null（未被删除）；

  2. 事务T2 执行：

    1
    2
    3
    UPDATE users
    SET name='newAlice'
    WHERE id=1;

    系统会：更新不是原地修改，而是复制出一行新版本（新 tuple）；
    旧版本的 xmax 被设为当前事务 ID；
    新版本的 xmin 被设为当前事务 ID。

版本 内容 xmin xmax
v1 Alice 10 (T1) 20(T2)
v2 newAlice 20(T2) null
  1. DELETE
    删除操作只是在记录上标记 xmax = 当前事务ID，不立即移除数据。
    只有当没有事务再需要旧版本时，VACUUM 才会真正清理物理空间。

可见性判断

一个事务在查询时，系统会根据当前事务的快照（snapshot）来判断哪些版本可见。
快照记录：当前事务 ID，当前已提交事务 ID 集合，当前未提交事务 ID 集合。

可见性判断是“快照”对“版本”的比较。
✅ “快照认为这个版本已经存在（被提交），并且还没被删除” → 可见
❌ “快照认为这个版本还没生成，或已经被删除” → 不可见

所以“是否可见”是针对当前事务(的快照)视角而言的。

判断规则：

情况 是否可见 解释
tuple 的 xmin 未提交 ❌ 不可见 事务还没提交当然不可见
tuple 的 xmin 已提交，且 xmax 为空 ✅ 可见 xmax为null说明版本是最新的
tuple 的 xmin 已提交，但 xmax 已提交（并早于当前事务） ❌ 不可见 xmax已经提交说明该版本已经过时了
tuple 的 xmax 是未提交事务 ✅（快照隔离下仍可见） xmax还未提交相当于xmax保持初始值即null

MVCC 与事务隔离级别的关系

隔离级别 MVCC 的表现
READ COMMITTED 每次查询都获得新的快照（看到已提交的新数据）
REPEATABLE READ 整个事务期间使用固定的快照（避免不可重复读）
SERIALIZABLE 在 MVCC 基础上加额外检测，确保结果等价于串行执行

死元组（dead tuple）

每当你修改或删除一行数据时，旧版本不会被立即覆盖或删除，而是被保留，并打上“过期”标记。这就产生了死元组，此外还有一中情况：即事务回滚操作也会产生死元组(新版本已经插入，但事务回滚后它对所有事务都不可见。即使更新失败也会产生)

这些不可见版本也是 dead tuple。

PostgreSQL 采用 非阻塞式 MVCC，允许读写并发。当一行数据(元组)被更新或删除时，如果立即将该元组删除，其他仍在运行的事务可能仍需访问旧版本。因此，旧版本要等到没有任何活跃事务需要它后，才能被回收。

继承(INHERITS)

在 PostgreSQL 中，继承（table inheritance） 是一个比较独特的特性，它允许一个表继承另一个表的列定义和约束，有点类似于面向对象编程中的“类继承”。

基本概念

  • 父表（parent table）：定义了基本的字段和约束。

  • 子表（child table）：继承父表的列和约束，同时可以定义自己的新列和约束。

这样，子表天然拥有父表的所有列，并且你可以在查询父表时自动查询到子表的数据（除非显式限制）。

继承示例

1
2
3
4
5
6
7
8
9
10
11
-- 父表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    salary NUMERIC(10,2)
);

-- 子表，继承父表
CREATE TABLE managers (
    department TEXT
) INHERITS (employees);

解释：

  • managers 继承了 employees 的 id, name, salary 三个字段。
  • managers 自己还定义了 department 列。

继承的查询特性

查询特性

当你查询父表时，默认会包括其所有子表的数据：
SELECT * FROM employees;
结果会包含 employees 表和 managers 表的数据。

如果只想查父表的数据，可以使用 ONLY：
SELECT * FROM ONLY employees;
这样只会返回父表自身的数据。

继承的局限性

虽然继承听起来很强大，但在实际生产中 PostgreSQL 的继承特性有不少限制：

  1. 约束不会自动传播

  2. 父表上的约束（比如 PRIMARY KEY、UNIQUE）不会自动应用到子表。
    如果你希望唯一性跨父表和子表生效，需要手动建约束或触发器。

  3. 索引不继承

  4. 父表的索引不会应用到子表，需要单独创建。

  5. 外键不支持继承

  6. 不能直接把外键建在父表上并自动作用于子表。

  7. SQL 标准兼容性差
    SQL 标准中没有这种表继承机制，因此它是 PostgreSQL 的“特有扩展”。

索引(INDEX)

索引（Index）是数据库中一种用于加快数据检索速度的数据结构。
有索引，数据库可直接跳转到匹配的页（page），而不用全表扫描挨个对比。极大提升查询效率。当然索引也有代价，建立和维护索引需要额外空间和写入开销。

一旦一个索引被创建，就不再需要进一步的干预：系统会在表更新时自动更新索引，而且会在它觉得使用索引比顺序扫描表效率更高时使用索引。但我们可能需要定期地运行ANALYZE命令来更新统计信息以便查询规划器能做出正确的决定。

创建和删除

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建一个单列索引
CREATE INDEX test1_id_index ON test1 (id);
-- 创建一个组合索引
CREATE INDEX test1_id_name_index ON test1 (id,name);
-- 创建唯一索引(不允许任何重复的值插入到表中)
CREATE UNIQUE INDEX test1_unique_id_index ON test1 (id);
--创建一个局部索引(在表的子集上构建的索引)
CREATE INDEX test1_id_index 
ON test1 (id)
WHERE condition;
-- 删除该索引
DROP INDEX test1_id_index

索引的类型

PostgreSQL支持的索引类型包括：B+树（默认）、HASH、GiST、GIN、BRIN等。

B+树

底层是一种多路平衡树（balanced tree），数据按键（key）有序排列，所有叶子节点深度一致（平衡性保证查找效率），并且每个节点存储一组有序键值与指针。

在 PostgreSQL 中，索引也是一种表结构，存储在 索引页（index page） 里。
每个索引页包含：

  • Page header：页头元信息（页号、空闲空间指针等）
  • Item pointers：指向表中 元组 的指针（ctid）
  • Key 数据：索引列的值（例如 name, id）
  • 链表指针：指向相邻叶子页（用于范围扫描）

优点是：查找、插入、删除平均 O(log N)，支持范围与排序，适用于几乎所有普通查询
但也有一些缺点：插入过多会导致页分裂（page split），大量更新/删除导致索引膨胀（bloat），不适合极端高写入负载场景（如日志）

查找的流程：
从根页（root page）开始，比较 key 值，确定要下探的分支页，然后递归直到叶子页，再在叶子页找到匹配 key，如果比较结果为 = 时，B+树索引会选择右子节点继续查找,最后返回该 key 指向的数据行（tuple）。

Hash

HASH索引存储一个由索引列计算出的32位的hash code值。因此，Hash索引只能处理简单等值=比较。每当索引列涉及到等值操作符的比较时，查询规划器将会使用Hash索引。

索引选择性（selectivity）和成本模型

PostgreSQL 在执行 SQL 时，如何决定是否使用索引？
——答案关键在于索引选择性（selectivity）与成本估算（cost estimation）。

索引选择性是指某个查询条件筛选出的行数占总行数的比例。

  • 选择性高（接近 1.0） → 命中很多行 → 索引没啥用；
  • 选择性低（接近 0.0） → 命中很少行 → 索引非常有效。

当然了，优化器不可能真的去“执行一遍”看看结果多少行。
它根据 系统统计信息（pg_statistic） 估算的，这些统计是在你执行：

1
2
3
4
ANALYZE tablename;  --ANALYZE 会扫描表中的部分数据行，
--生成统计信息并存入系统表 pg_statistic
或
VACUUM ANALYZE;

时生成的。

PostgreSQL 最终并不是只看 selectivity，而是综合计算“成本”。
每个执行计划都会有：

  • startup_cost：启动开销（准备、扫描第一行）
  • total_cost：读完全部行的总成本（包括 I/O、CPU 等）

优化器根据统计信息与代价参数估算这些值。

索引失效的常见原因

1. 索引选择性低
如果结果集占表的 20% 以上，优化器通常会放弃索引，改用顺序扫描（Seq Scan）。
2. 数据类型不匹配
PostgreSQL 的索引对数据类型非常严格。

1
2
3
4
CREATE INDEX idx_userid ON users(userid INT);

-- 错误写法（字符串转整数）
SELECT * FROM users WHERE userid = '123';

此时 '123' 是字符串常量，优化器不会隐式转换成 int 去匹配索引，因此索引失效。
3. 在列上使用函数或表达式
当你在 WHERE 子句中对列使用函数时，索引无法直接利用。
但是可以创建函数索引。
4. 使用模糊查询LIKE时前缀以%开头
B-tree 索引支持 LIKE 'abc%'，但不支持 LIKE '%abc'。
5. 运算符或表达式不匹配索引类型
例如，B-tree 索引支持 <, <=, =, >=, >，
但不支持 != 或 NOT IN。
6. 统计信息不准确/过旧（ANALYZE 未更新）
优化器依赖 pg_statistic 表的统计信息估算行数,如果表数据更新频繁但没执行 ANALYZE，优化器可能错误地认为索引代价高。
7. 多列复合索引未按前缀顺序使用
假设创建了：

1
CREATE INDEX idx_user_city_age ON users (city, age);

只有满足最左前缀规则的查询能用到索引：
8. 数据量太小或表太小
当表只有几百行时，优化器计算发现：
“全表扫描只需一次磁盘读取”，而索引扫描需要“额外随机IO + 回表查元组”，所以索引也不会被使用。

索引调优技巧

1. 分析查询计划 (EXPLAIN / EXPLAIN ANALYZE)

1
EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'Tom';

查看执行计划，如果是 Seq Scan → 没用索引，如果是 Index Scan / Index Only Scan → 已使用索引
2. 维护统计信息

1
2
-- 先清理再统计，防止统计包含过时数据
VACUUM ANALYZE;

3. 使用函数索引或表达式索引

1
2
3
CREATE INDEX idx_expr_salary ON employees ((salary * 12));
-- 即可支持：
SELECT * FROM employees WHERE salary * 12 > 120000;

4. 使用部分索引
如果查询经常限定某一条件：

1
2
3
4
SELECT * FROM orders WHERE status = 'pending';

可以这样建索引：
CREATE INDEX idx_pending_orders ON orders (status) WHERE status = 'pending';

这样能极大减少索引大小与扫描成本。
5. 避免数据类型不一致

  • Copyrights © 2023-2025 Hexo

请我喝杯咖啡吧~

支付宝
微信