PostgreSQL 高级特性与 MySQL 深度对比：2026 年数据库选型指南

💡 前言：很多开发者在选型时会纠结 PostgreSQL 和 MySQL 到底用哪个。今天从高级特性出发，深度对比两款数据库，帮你做出最适合的选择。

一、先说结论

维度	PostgreSQL	MySQL
定位	功能全面的企业级数据库	轻量级高性能 Web 数据库
SQL 标准	高度遵循（99%+)	部分遵循
扩展性	强大（支持自定义类型、操作符）	一般
并发性能	适合复杂查询	简单查询更快
JSON 支持	原生 JSONB，高性能	支持但性能一般
生态	云厂商支持增加	非常成熟

快速选择建议：
– 传统业务系统、微服务、简单 CRUD → MySQL
– 数据分析、GIS、AI 场景、复杂业务逻辑 → PostgreSQL
– 云原生 Serverless、流量波动大 → 两者都有云托管版本

二、核心差异对比表

特性	PostgreSQL	MySQL
事务支持	ACID 完整支持	支持（InnoDB 引擎）
隔离级别	4 种 + SERIALIZABLE	4 种
MVCC	真正 MVCC	近似 MVCC
索引类型	7+ 种	5 种
分区表	原生声明式分区	支持（MySQL 5.7+）
JSON	JSONB（原生二进制）	JSON（文本存储）
全文搜索	内置，支持中文	需配置
GIS	PostGIS（专业级）	基础支持
数组类型	原生支持	不支持
自定义类型	完全支持	有限
存储过程	多语言（Python/JS/Java）	SQL/PL
物化视图	支持	不支持
窗口函数	完整支持	MySQL 8.0+ 支持
CTE/递归	完整支持	MySQL 8.0+ 支持
复制	流复制、逻辑复制	主从复制、Group Replication
分片	Citus 扩展	MySQL Fabric（已停止维护）

三、JSON 处理对比

JSON 是现代应用的核心数据格式，两者的处理能力差距明显。

3.1 存储与查询

-- PostgreSQL：使用 JSONB（二进制格式，索引友好）
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    items JSONB,  -- 二进制存储，可建索引
    created_at TIMESTAMP
);

-- JSONB 索引
CREATE INDEX idx_orders_items ON orders USING GIN (items);

-- 插入数据
INSERT INTO orders (customer_id, items) VALUES (
    1001,
    '{"product": "iPhone", "qty": 2, "price": 8999}'::JSONB
);

-- 高级查询：查询数组中包含特定值
INSERT INTO orders (customer_id, items) VALUES (
    1002,
    '{"tags": ["热门", "促销", "限量"], "sku": "IP15PRO"}'::JSONB
);

-- 查找包含"促销"标签的订单（MySQL 无法高效实现）
SELECT * FROM orders 
WHERE items @> '{"tags": ["促销"]}';

-- 查找价格大于 5000 的订单
SELECT * FROM orders 
WHERE (items->>'price')::INT > 5000;

-- 提取特定字段（自动类型转换）
SELECT 
    items->>'product' as product,
    items->>'price' as price,  -- 仍是文本
    (items->>'price')::INT * 2 as double_price  -- 转为数字计算
FROM orders;

-- MySQL：JSON 存储为文本
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    items JSON,
    created_at TIMESTAMP
);

-- 插入数据
INSERT INTO orders (customer_id, items) VALUES (
    1001,
    JSON_OBJECT('product', 'iPhone', 'qty', 2, 'price', 8999)
);

-- 查询价格大于 5000 的订单
SELECT * FROM orders 
WHERE JSON_EXTRACT(items, '$.price') > 5000;

-- 简写语法
SELECT * FROM orders 
WHERE items->>'$.price' > 5000;

3.2 JSON 查询性能对比

-- PostgreSQL JSONB 优势查询
-- 查询特定路径（利用索引）
EXPLAIN SELECT * FROM orders 
WHERE items @> '{"product": "iPhone"}';

-- 包含查询（GIN 索引加速）
SELECT * FROM orders 
WHERE items ? 'tags';

-- 数组元素查询
SELECT * FROM orders 
WHERE items->'tags' ? '促销';

性能实测：

操作	PostgreSQL JSONB	MySQL JSON
简单读取	1x	1.1x
条件过滤	1x	3-5x（无索引）
包含查询	1x（GIN索引）	10x+（全表）
聚合统计	1x	5x

四、高级 SQL 特性对比

4.1 窗口函数（Window Functions）

窗口函数是数据分析的核心，MySQL 8.0 后才完整支持。

-- 场景：计算每个部门的工资排名和部门内平均工资

-- PostgreSQL
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
    SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sum
FROM employees;

-- MySQL 8.0+ 语法相同
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    AVG(salary) OVER (PARTITION BY department) as dept_avg_salary
FROM employees;

窗口函数类型对比：

函数类型	PostgreSQL	MySQL 8.0+
RANK/DENSE_RANK/ROW_NUMBER	✅	✅
LAG/LEAD	✅	✅
FIRST_VALUE/LAST_VALUE	✅	✅
SUM/AVG/COUNT OVER	✅	✅
NTILE	✅	✅
CUME_DIST	✅	✅

4.2 CTE（公共表表达式）

CTE 让复杂查询更易读，两者都支持。

-- 递归 CTE：查询组织架构
WITH RECURSIVE org_tree AS (
    -- 起始：CEO
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归：下属
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

-- 多层 CTE 简化复杂查询
WITH 
    monthly_sales AS (
        SELECT DATE_TRUNC('month', created_at) as month, SUM(amount) as total
        FROM orders
        GROUP BY DATE_TRUNC('month', created_at)
    ),
    avg_sales AS (
        SELECT AVG(total) as avg FROM monthly_sales
    )
SELECT 
    month, 
    total,
    avg,
    total - avg as diff
FROM monthly_sales, avg_sales
ORDER BY month;

4.3 物化视图（Materialized View）

物化视图将查询结果存储为物理表，适合大数据量聚合分析。

-- PostgreSQL 物化视图
CREATE MATERIALIZED VIEW monthly_report AS
SELECT 
    DATE_TRUNC('month', created_at) as month,
    category,
    COUNT(*) as order_count,
    SUM(amount) as total_amount,
    AVG(amount) as avg_amount
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY DATE_TRUNC('month', created_at), category
WITH DATA;

-- 创建索引加速查询
CREATE INDEX idx_monthly_report ON monthly_report (month, category);

-- 刷新数据（手动或定时）
REFRESH MATERIALIZED VIEW monthly_report;

-- 异步刷新（不阻塞查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_report;

-- 查询物化视图（像普通表一样）
SELECT * FROM monthly_report WHERE month >= '2026-01-01';

-- MySQL 没有原生物化视图，需使用定时任务模拟

4.4 数组类型

PostgreSQL 原生支持数组，MySQL 不支持。

-- PostgreSQL：存储用户标签
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests TEXT[],  -- 数组类型
    phone_numbers TEXT[]
);

INSERT INTO users (name, interests, phone_numbers) VALUES (
    '张三',
    ARRAY['技术', '旅游', '摄影'],
    ARRAY['13800138000', '13900139000']
);

-- 查询包含特定兴趣的用户
SELECT * FROM users WHERE '技术' = ANY(interests);

-- 数组包含查询
SELECT * FROM users WHERE interests @> ARRAY['技术', '旅游'];

-- 展开数组（UNNEST）
SELECT id, name, unnest(interests) as interest FROM users;

-- MySQL 需使用 JSON 模拟
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    interests JSON
);

INSERT INTO users (name, interests) VALUES (
    '张三',
    '["技术", "旅游", "摄影"]'
);

五、索引类型对比

索引类型	PostgreSQL	MySQL
B-Tree	✅（默认）	✅（默认）
Hash	✅	✅（MEMORY引擎）
GiST	✅	❌
GIN	✅	❌
SP-GiST	✅	❌
BRIN	✅	❌
表达式索引	✅	部分
部分索引	✅	✅
全文索引	✅（强大）	✅（基础）

5.1 GIN 索引（PostgreSQL 独家）

GIN 索引是 JSONB 和数组的最佳拍档。

-- 为 JSONB 字段创建 GIN 索引
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- 性能对比：查询 JSONB 中包含特定键值
-- 无索引：Full Seq Scan
-- 有 GIN 索引：Index Only Scan

-- 数组 GIN 索引
CREATE INDEX idx_user_interests ON users USING GIN (interests);

-- 查询性能
EXPLAIN SELECT * FROM users WHERE interests @> ARRAY['技术'];
-- -> Bitmap Index Scan on idx_user_interests (cost=...)

5.2 BRIN 索引（PostgreSQL 独家）

BRIN 索引专为时序数据设计，存储空间极小。

-- 时序数据：按插入顺序自然有序
CREATE TABLE sensor_logs (
    id BIGSERIAL,
    sensor_id INT,
    reading JSONB,
    recorded_at TIMESTAMP NOT NULL
);

-- BRIN 索引：假设数据按时间顺序插入
CREATE INDEX idx_sensor_logs_time ON sensor_logs USING BRIN (recorded_at);

-- 对时序数据，BRIN 索引效率极高
-- 存储空间仅为 B-Tree 的 1%
SELECT pg_size_pretty(pg_relation_size('idx_sensor_logs_time'));

-- 对随机插入的数据，BRIN 效果差

5.3 表达式索引

-- PostgreSQL：为函数结果建索引
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- MySQL：支持但语法略有不同
CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));

六、分区表对比

6.1 PostgreSQL 声明式分区

-- PostgreSQL：原生声明式分区
CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INT,
    total_amount DECIMAL(10,2),
    created_at DATE NOT NULL
) PARTITION BY RANGE (created_at);

-- 创建分区
CREATE TABLE orders_2026_q1 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE orders_2026_q2 PARTITION OF orders
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

CREATE TABLE orders_2026_q3 PARTITION OF orders
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');

CREATE TABLE orders_2026_q4 PARTITION OF orders
    FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');

-- 自动路由到正确分区
INSERT INTO orders (customer_id, total_amount, created_at) VALUES
    (1001, 299.00, '2026-03-15'),  -- 进入 q1 分区
    (1002, 599.00, '2026-06-20');  -- 进入 q2 分区

-- 查看查询计划，确认分区裁剪
EXPLAIN SELECT * FROM orders WHERE created_at = '2026-03-15';
-- -> Seq Scan on orders_2026_q1

6.2 MySQL 分区

-- MySQL：分区语法略有不同
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    customer_id INT,
    total_amount DECIMAL(10,2),
    created_at DATE NOT NULL,
    PRIMARY KEY (id, created_at)  -- 主键必须包含分区键
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 按月分区
CREATE TABLE logs (
    id BIGINT,
    created_at DATETIME
) PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
    PARTITION p2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

七、存储过程与函数

7.1 PostgreSQL 多语言支持

-- PostgreSQL：支持多种语言编写存储过程

-- PL/pgSQL（默认）
CREATE OR REPLACE FUNCTION get_order_summary(p_customer_id INT)
RETURNS TABLE (
    total_orders BIGINT,
    total_amount DECIMAL(12,2),
    avg_amount DECIMAL(10,2)
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        COUNT(*),
        SUM(total_amount),
        AVG(total_amount)
    FROM orders
    WHERE customer_id = p_customer_id;
END;
$$ LANGUAGE plpgsql;

-- PL/Python：使用 Python 处理数据
CREATE FUNCTION filter_outliers(data INT[])
RETURNS INT[] AS $$
    import statistics
    if len(data) < 3:
        return data
    mean = statistics.mean(data)
    stdev = statistics.stdev(data)
    return [x for x in data if abs(x - mean) <= 2 * stdev]
$$ LANGUAGE plpython3u;

SELECT filter_outliers(ARRAY[1, 2, 3, 4, 5, 100]);

-- MySQL：仅支持 SQL 和简化版 PL/SQL
CREATE FUNCTION get_order_count(p_customer_id INT)
RETURNS INT
BEGIN
    DECLARE v_count INT;
    SELECT COUNT(*) INTO v_count FROM orders WHERE customer_id = p_customer_id;
    RETURN v_count;
END;

八、复制与高可用

8.1 PostgreSQL 流复制

-- 主库：创建复制用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret';

-- 主库：修改 postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

-- 主库：配置 pg_hba.conf
host replication replicator 10.0.0.0/24 md5

-- 从库：克隆数据
pg_basebackup -h master_host -U replicator -D /var/lib/postgresql/data -P -Xs

8.2 MySQL 主从复制

-- 主库：创建复制用户
CREATE USER 'replicator'@'%' IDENTIFIED BY 'secret';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

-- 主库：配置 binlog
log_bin = mysql-bin
server_id = 1
binlog_format = ROW

-- 从库：配置
server_id = 2
relay_log = mysql-relay-bin
read_only = ON

九、选型决策树

需要选型？
    │
    ├── JSON 数据复杂？───→ 是 ──→ PostgreSQL（JSONB + GIN）
    │
    ├── GIS/地理查询？───→ 是 ──→ PostgreSQL（PostGIS）
    │
    ├── 超大数据量分析？───→ 是 ──→ PostgreSQL（物化视图 + 分区）
    │
    ├── 高并发简单查询？───→ 是 ──→ MySQL（InnoDB 优化成熟）
    │
    ├── 需要多语言函数？───→ 是 ──→ PostgreSQL
    │
    ├── 已有 MySQL 团队？───→ 是 ──→ MySQL（学习成本）
    │
    └── 其他场景？───→ 根据特性对比选择

十、总结

选择 PostgreSQL 的场景：
– 复杂业务逻辑，需要丰富的数据类型
– JSON 数据量大，查询频繁
– GIS 地理信息系统
– 时序数据、物化视图需求
– 需要强大的扩展性（自定义类型、操作符）
– 数据分析、BI 报表

选择 MySQL 的场景：
– 传统 Web 应用，简单 CRUD 为主
– 团队已有 MySQL 经验
– 生态工具成熟（备份、监控、运维）
– 对性能要求极致（简单查询）
– 互联网产品初创期

混合方案：
– 核心业务数据 → MySQL（稳定、高并发）
– 分析报表、大数据 → PostgreSQL（强大分析能力）
– 通过数据同步工具保持一致性

---

关于作者

长期关注大模型应用落地与云服务器实战，专注技术在企业场景中的落地实践。

个人博客：y1cloud.cn —— 持续更新云计算、AI大模型实战教程，欢迎访问交流。