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

作者


阿里云推广

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) = 'user@example.com';

-- 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（强大分析能力）
  • 通过数据同步工具保持一致性

    • 关于作者

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

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

    发表评论