PostgreSQL——特性详解

CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zipcode TEXT
);

CREATE TABLE company (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location address
);

数组类型

允许在一个字段中存储同一类型的多个值。

CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

JSON和JSONB数据类型

允许存储和查询JSON格式的数据。JSONB是二进制格式，支持更高效的查询和索引。

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content JSONB
);

枚举类型

枚举类型允许用户定义一组可选的值，这在需要限制字段值范围的情况下非常有用。

CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');

范围类型

用于表示范围（如日期范围、数值范围等），适用于需要存储区间数据的场景。

CREATE TABLE booking (
    id SERIAL PRIMARY KEY,
    daterange DATERANGE
);

自定义类型和操作符

用户可以自己定义新的数据类型和操作符。

下述代码定义了一个复数类型（complex），一个用于相加两个复数的函数（add_complex），以及一个使用该函数的加法操作符（+）。

CREATE TYPE complex AS (
    real DOUBLE PRECISION,
    imaginary DOUBLE PRECISION
);

CREATE FUNCTION add_complex(a complex, b complex) RETURNS complex AS $$
BEGIN
    RETURN (a.real + b.real, a.imaginary + b.imaginary);
END;
$$ LANGUAGE plpgsql;

CREATE OPERATOR + (
    FUNCTION = add_complex,
    LEFTARG = complex,
    RIGHTARG = complex
);

标准SQL支持：

完全支持SQL标准（SQL:2011），包括复杂查询、子查询、视图、触发器、外键等。

SQL:2011

SQL:2011是SQL标准的一个版本，由国际标准化组织（ISO）发布。SQL（Structured Query Language，结构化查询语言）是关系型数据库管理系统（RDBMS）中用于查询和管理数据的语言。SQL:2011是SQL标准的第七次主要修订，它引入了许多新特性和扩展，旨在提高SQL的功能和灵活性。

SQL/Fwk（Framework）	提供了SQL标准的总体框架和定义。
SQL/Foundation	定义了SQL的基本功能，包括数据定义、数据操纵、数据控制等。
SQL/CLI（Call-Level Interface）	定义了如何通过编程语言（如C）调用SQL操作的接口。
SQL/PSM（Persistent Stored Modules）	提供了定义和使用存储过程和函数的标准。
SQL/Bindings	定义了SQL与其他编程语言（如Java、C）的绑定和集成。
SQL/OLB（Object Language Bindings）	提供了SQL与面向对象编程语言的集成。
SQL/JRT（Java Routines and Types）	定义了在SQL中使用Java例程和类型的标准。
SQL/MED（Management of External Data）	提供了管理和查询外部数据（如外部文件、Web服务）的标准。
SQL/Schemata	定义了SQL架构的元数据标准。
SQL/XML	提供了在SQL中处理XML数据的标准。

复杂查询

复杂查询（Complex Queries）指的是在SQL中涉及多个表、复杂条件、子查询、聚合操作以及窗口函数等的查询。它们通常用于从数据库中提取有意义的信息，处理复杂的业务逻辑，以及进行高级的数据分析。

联接（JOIN）：

联接操作用于从多个表中获取数据。常见的联接类型包括内联接（INNER JOIN）、联接（LEFT JOIN）、右联接（RIGHT JOIN）和全外联接（FULL OUTER JOIN）。

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

子查询（Subqueries）：

子查询是嵌套在另一个查询中的查询。它们可以用于在SELECT、FROM、WHERE、HAVING等子句中。

SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

聚合函数（Aggregate Functions）：

聚合函数用于计算一组值并返回单个值，如SUM、AVG、COUNT、MAX、MIN等。通常与GROUP BY子句一起使用。

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

窗口函数（Window Functions）：

窗口函数在结果集中执行计算，并保留详细信息。常见的窗口函数包括ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG等。

SELECT name, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

复杂条件：

使用复杂的布尔逻辑和条件表达式来筛选数据。这些条件可以包括AND、OR、NOT、LIKE、IN、BETWEEN等。

SELECT name
FROM employees
WHERE (salary > 50000 AND department_id = 3) OR (hire_date > '2020-01-01');

联合查询（UNION）：

联合查询用于将多个查询的结果合并为一个结果集。UNION会去除重复记录，UNION ALL则不会。

SELECT name FROM employees WHERE department_id = 1
UNION
SELECT name FROM employees WHERE department_id = 2;

CTE（Common Table Expressions）：

公用表表达式用于定义一个临时结果集，可以在后续的SELECT、INSERT、UPDATE或DELETE语句中引用。

以下使用了公用表表达式（Common Table Expression，CTE）来计算每个部门的平均工资，然后将这些结果与部门表进行联接，最终返回每个部门的名称及其对应的平均工资。

WITH department_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT departments.name, department_salary.avg_salary
FROM departments
JOIN department_salary ON departments.id = department_salary.department_id;

复杂查询的优化

由于复杂查询可能涉及大量数据和多表操作，因此优化这些查询以提高性能非常重要。以下是一些常见的优化技术：

索引：创建适当的索引以加速查询。索引可以显著提高SELECT语句的性能，但需要平衡索引的数量和维护成本。
分区：将大型表分区，以减少查询扫描的数据量，从而提高查询性能。
查询重写：通过重写查询，消除不必要的嵌套和联接，简化查询逻辑。
统计信息：定期更新统计信息，以帮助查询优化器做出更好的决策。
缓存：使用缓存技术（如查询结果缓存）减少重复查询的开销。

触发器与可更新视图

简单视图可以直接更新，但如果视图定义中包含了无法直接更新的结构，可以通过创建触发器（trigger）来实现可更新视图。

假设我们需要一个视图来显示员工的名称和部门名称，但我们希望能够更新员工的信息。这时，我们需要使用触发器。

//首先，创建基础表：
CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary NUMERIC,
    department_id INTEGER REFERENCES departments(id)
);


//插入一些示例数据：
INSERT INTO departments (name) VALUES ('Sales'), ('Engineering'), ('Marketing');

INSERT INTO employees (name, salary, department_id) VALUES
('Alice', 60000, 1),
('Bob', 70000, 2),
('Charlie', 80000, 3);


//创建视图
CREATE VIEW employee_details AS
SELECT e.id, e.name, e.salary, d.name AS department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;


//定义触发器函数
CREATE FUNCTION update_employee_details() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        UPDATE employees
        SET name = NEW.name, salary = NEW.salary, department_id = (SELECT id FROM departments WHERE name = NEW.department_name)
        WHERE id = OLD.id;
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO employees (name, salary, department_id)
        VALUES (NEW.name, NEW.salary, (SELECT id FROM departments WHERE name = NEW.department_name));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM employees WHERE id = OLD.id;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;   //指定触发器函数的语言为PL/pgSQL，这是PostgreSQL的一种过程化语言。


//创建触发器
CREATE TRIGGER employee_details_trigger
INSTEAD OF INSERT OR UPDATE OR DELETE ON employee_details
FOR EACH ROW EXECUTE FUNCTION update_employee_details();

现在，我们可以通过视图employee_details来进行插入、更新和删除操作。

//插入数据
INSERT INTO employee_details (name, salary, department_name)
VALUES ('David', 90000, 'Sales');


//更新数据
UPDATE employee_details
SET salary = 95000, department_name = 'Engineering'
WHERE name = 'David';

//删除数据
DELETE FROM employee_details
WHERE name = 'David';

数据完整性：

提供丰富的数据完整性约束，如主键、外键、唯一性约束、非空约束、检查约束等。

数据库的数据完整性是指确保数据库中的数据准确、可靠和一致的机制和规则。数据完整性确保了数据库中的数据在插入、更新和删除操作中保持一致性和准确性，避免了数据异常和错误。数据完整性可以通过以下几种主要类型来实现：

实体完整性（Entity Integrity）

定义：实体完整性确保每个表中的每一行都有一个唯一的标识符，这通常是通过主键实现的。主键列的值必须唯一，并且不能为NULL。

实现方式：

主键约束：指定表中一个或多个列作为主键，主键列的值必须唯一且不能为空。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary NUMERIC
);

参照完整性（Referential Integrity）

定义：参照完整性确保数据库中的外键值必须在关联表的主键列中存在。这避免了“悬挂”或“孤立”的外键值，即外键指向不存在的记录。

实现方式：

外键约束：确保外键列的值在主表中存在，并且可以定义级联操作（如CASCADE、SET NULL、RESTRICT等）以自动处理相关记录的更新或删除。

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary NUMERIC,
    department_id INTEGER REFERENCES departments(id) ON DELETE SET NULL
);

域完整性（Domain Integrity）

定义：域完整性确保列的数据类型、格式和范围符合预期。这通常涉及对列的定义和约束，确保插入的数据满足预期的格式和范围。

实现方式：

数据类型：定义列的数据类型以确保存储的数据符合预期。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    salary NUMERIC CHECK (salary >= 0)
);

检查约束：使用CHECK约束来限制列的值范围或格式。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    salary NUMERIC CHECK (salary >= 0 AND salary <= 100000)
);

用户定义的完整性（User-Defined Integrity）

定义：用户定义的完整性是由用户根据具体应用需求定义的规则和约束。这些规则通常包括复杂的业务逻辑和条件。

实现方式：

触发器：使用触发器函数在插入、更新或删除操作时执行自定义的逻辑来确保数据完整性。

CREATE FUNCTION validate_salary() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION validate_salary();

并发控制：

并发控制机制是确保多个用户或事务同时访问数据库时，数据的一致性和完整性得以维持的关键技术。PostgreSQL主要通过两种机制来实现并发控制：事务隔离级别和多版本并发控制（MVCC）。

事务隔离级别

READ COMMITTED	防止脏读	可能出现不可重复读	可能出现幻读
REPEATABLE READ	防止脏读	防止不可重复读	可能出现幻读
SERIALIZABLE	防止脏读	防止不可重复读	防止幻读

READ COMMITTED

定义：这是PostgreSQL的默认隔离级别。在READ COMMITTED隔离级别下，事务只能读取其他事务已提交的数据。每次查询都将看到事务开始时已经提交的数据，即使在事务执行期间其他事务对数据进行了更新。

特性：

防止脏读（Dirty Read）：事务不会读取到其他事务未提交的数据。
不防止不可重复读（Non-Repeatable Read）：同一事务的多个查询可能返回不同的结果，尤其在事务执行过程中其他事务提交了更新。

不防止幻读（Phantom Read）：事务在执行过程中，如果其他事务插入了新的数据行，查询结果可能会变化。

BEGIN;
-- 读取数据
SELECT * FROM employees WHERE department_id = 1;
-- 其他事务可以提交更新
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1;
-- 提交事务
COMMIT;

REPEATABLE READ

定义：在REPEATABLE READ隔离级别下，事务在执行过程中将看到一个一致的数据快照，即使其他事务对数据进行了更新或删除。事务中的查询将始终返回相同的数据结果。

特性：

防止脏读（Dirty Read）。
防止不可重复读（Non-Repeatable Read）：事务中的所有查询都将返回一致的结果，尽管在事务执行期间其他事务可能已提交更新。

不防止幻读（Phantom Read）：如果其他事务插入了满足查询条件的新数据行，事务可能会看到不同的结果集。

BEGIN;
-- 读取数据
SELECT * FROM employees WHERE department_id = 1;
-- 其他事务可以提交更新，但不会影响当前事务中的查询结果
-- 提交事务
COMMIT;

SERIALIZABLE

定义：SERIALIZABLE是最高的隔离级别，提供了严格的事务串行化保证。事务在SERIALIZABLE隔离级别下，确保它们的执行效果与按顺序执行所有事务的结果相同。

特性：

防止脏读（Dirty Read）。
防止不可重复读（Non-Repeatable Read）。

防止幻读（Phantom Read）：通过严格的事务串行化协议，确保事务的执行顺序不会导致数据异常。

BEGIN;
-- 读取数据
SELECT * FROM employees WHERE department_id = 1;
-- 事务在提交时会检查是否有其他事务对数据进行了更改，从而确保串行化的隔离
-- 提交事务
COMMIT;

设置事务隔离级别

可以通过SET TRANSACTION ISOLATION LEVEL语句设置事务的隔离级别。
-- 设置事务隔离级别为 REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置事务隔离级别为 SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

多版本并发控制

多版本并发控制（MVCC, Multi-Version Concurrency Control）是一种并发控制机制，用于处理多用户同时访问和修改数据库时的并发问题。MVCC 允许多个事务并发执行，同时保持数据的一致性和隔离性。

MVCC 通过创建数据的多个版本来管理事务并发，确保每个事务看到的数据是一致的快照。这样，事务的操作不会直接影响其他事务的视图，从而避免了数据的竞争和锁定问题。

核心特点

每个事务的快照：每个事务在开始时会看到数据库的一个一致性快照，这个快照反映了事务开始时数据库的状态。事务在执行期间不会看到其他事务对数据的未提交更改。
版本管理：每个数据行在 PostgreSQL 中都有两个隐藏的系统列，xmin 和 xmax，用来管理数据的版本。
- xmin：记录创建该行数据的事务ID。
- xmax：记录删除该行数据的事务ID（如果存在）。
事务 ID（XID）：PostgreSQL 使用事务 ID 来标识每个事务，每个事务都有一个唯一的 ID。
不可见的行：对于一个事务来说，只有在事务开始时已经提交的数据对它可见。其他事务在其执行期间的修改（即使已经提交）也不会影响到这个事务的视图。

MVCC 的工作原理

事务的视图

当一个事务开始时，它会看到数据库在其开始时的状态。即使其他事务在该事务执行期间修改了数据，这些修改对当前事务是不可见的。

-- 事务A
BEGIN;
SELECT * FROM employees; -- 看到的是事务A开始时的快照

-- 事务B
BEGIN;
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1;
COMMIT;

-- 事务A
SELECT * FROM employees; -- 仍然看到事务A开始时的快照，事务B的更改对它不可见
COMMIT;

数据版本管理

PostgreSQL 在每行数据中存储了 xmin 和 xmax 列，用于跟踪数据的创建和删除：

xmin：表示创建数据行的事务 ID。
xmax：表示删除数据行的事务 ID（如果存在）。

这些系统列帮助 PostgreSQL 确定某行数据在某个事务中的可见性。

扩展性：

支持多种扩展功能，如自定义数据类型、函数、操作符、索引方法等。用户还可以通过插件扩展数据库的功能。

上面讲过，回忆一下。

事务管理：

完整支持ACID特性，确保事务的原子性、一致性、隔离性和持久性。

原子性（Atomicity）

实现机制：

事务日志（Write-Ahead Logging, WAL）：PostgreSQL 使用 WAL 技术来确保事务的原子性。WAL 记录了所有对数据库的更改操作，即使在系统崩溃的情况下，事务日志可以帮助恢复数据库到一致的状态。每个事务在提交时，其所有更改都会先写入 WAL 文件，然后再写入实际的数据文件。如果事务未提交，WAL 记录可以帮助撤销未提交的更改，确保原子性。
回滚机制：如果事务执行中出现错误或故障，PostgreSQL 会回滚事务，撤销所有已执行的操作，以确保事务的原子性。

一致性（Consistency）

实现机制：

约束和触发器：PostgreSQL 支持各种数据完整性约束（如主键、外键、唯一性约束、检查约束）和触发器，用于确保数据库状态的一致性。约束保证数据符合预定义的规则，触发器可以在数据修改时自动执行逻辑检查。
事务一致性：在事务提交时，PostgreSQL 确保数据库的一致性状态。如果事务执行违反了数据库的完整性约束，则事务会被回滚，从而维护数据库的一致性。

隔离性（Isolation）

实现机制：

事务隔离级别：PostgreSQL 支持四种事务隔离级别（READ COMMITTED、REPEATABLE READ、SERIALIZABLE、READ UNCOMMITTED），控制事务在并发环境下对数据的可见性。用户可以通过设置隔离级别来确保事务的隔离性。
锁机制：PostgreSQL 使用多种锁（行级锁、表级锁等）来管理并发事务，确保事务在执行时不会互相干扰。

持久性（Durability）

实现机制：

事务日志（WAL）：持久性主要通过 WAL 实现。WAL 确保了即使在系统崩溃的情况下，已提交事务的更改仍然可以恢复。提交的事务会被记录到 WAL 中，这些日志用于在系统重启时恢复数据库状态。
数据页写入：在事务提交后，WAL 记录会被刷新到磁盘，然后数据库的实际数据页也会被写入磁盘，确保数据持久化。

索引类型：

支持多种索引类型，如B树、哈希、GiST、SP-GiST、GIN和BRIN等，以适应不同的查询需求。

B-tree	默认且广泛适用的索引类型。
Hash	专用于等值查找。
GiST	通用索引，适合处理空间数据和其他特殊数据类型。
GIN	适合处理多值字段，如数组和全文搜索。
BRIN	针对大规模数据表的范围查询优化。
SP-GiST	适合空间数据的分区索引。
Expression Indexes	基于计算结果的索引。
Partial Indexes	只索引满足特定条件的部分数据。

B-tree 索引

定义：B-tree（Balanced Tree）索引是 PostgreSQL 的默认索引类型。它适用于大多数常见的查询，包括等值比较和范围查询。

特性：

支持等值查找（=）、范围查找（<、>）、排序操作等。

适合大多数数据类型，包括整数、浮点数、日期等。

-- 创建一个 B-tree 索引
CREATE INDEX idx_employee_name ON employees (name);

Hash 索引

定义：Hash 索引是用于等值查找的索引类型，适合处理只进行等值比较的查询。

特性：

仅支持等值查找（=）。
不支持范围查询。

相对较少使用，因为 B-tree 索引提供了更多功能和更好的性能。

-- 创建一个 Hash 索引
CREATE INDEX idx_employee_id_hash ON employees USING HASH (employee_id);

GiST 索引

定义：GiST（Generalized Search Tree）索引是一种通用的索引结构，可以用于支持不同的查询类型，如空间查询、全文搜索等。

特性：

支持多种不同的数据类型和查询，例如几何数据、全文搜索、网络地址等。

灵活且可扩展，但可能需要额外的配置和调整。

-- 创建一个 GiST 索引
CREATE INDEX idx_gist_geom ON spatial_data USING GIST (geom);

GIN 索引

定义：GIN（Generalized Inverted Index）索引是一种专门用于处理包含多个值的数据类型，如数组、全文搜索等的索引。

特性：

适合处理多值字段，如数组、JSONB、全文搜索。

支持高效的包含查询和键值查询。

-- 创建一个 GIN 索引
CREATE INDEX idx_employee_tags ON employees USING GIN (tags);

BRIN 索引

定义：BRIN（Block Range INdexes）索引适用于非常大的表，特别是那些按时间或其他顺序存储的数据。

特性：

针对大规模数据表进行高效的范围查询。

占用的空间较少，适合用于按范围查询的列。

-- 创建一个 BRIN 索引
CREATE INDEX idx_brin_timestamp ON logs USING BRIN (timestamp);

SP-GiST 索引

定义：SP-GiST（Space-partitioned Generalized Search Tree）索引用于支持空间分区的数据类型，如多维数据。

特性：

适用于特殊的空间数据类型，例如三维数据。

支持高效的空间查询。

-- 创建一个 SP-GiST 索引
CREATE INDEX idx_spgist_location ON spatial_data USING SPGIST (location);

Expression Indexes

定义：表达式索引基于列的计算结果而不是列的原始值。这对于基于计算结果的查询很有用。

特性：

支持基于表达式的查询，如对列进行函数计算。

-- 创建一个表达式索引
CREATE INDEX idx_lower_email ON users ((lower(email)));

Partial Indexes

定义：部分索引只为满足特定条件的行创建索引。这可以减少索引的大小并提高查询性能。

特性：

只索引表中符合条件的部分数据。

适用于条件查询的优化。

-- 创建一个部分索引
CREATE INDEX idx_active_employees ON employees (name) WHERE status = 'active';

复制和高可用性：

支持流复制、逻辑复制、同步复制和异步复制，确保数据的高可用性和灾难恢复能力。

流复制	物理复制，实时同步主数据库和备用数据库的数据。
逻辑复制	逻辑复制，允许选择性地复制数据到多个订阅者数据库。
同步复制	确保主数据库和备用数据库在事务提交时同步更新，提供强一致性。
异步复制	主数据库在事务提交后立即返回，备用数据库异步接收 WAL 日志，提供更高的性能。