MySQL 与 PostgreSQL 对比全攻略

一、快速概览

MySQL 和 PostgreSQL 是开源关系型数据库的两大标杆。一句话区分：

MySQL：简单、快、生态成熟，互联网公司的默认选择
PostgreSQL：功能完备、SQL 标准合规、可扩展性强，被称为”开源 Oracle”

维度	MySQL	PostgreSQL
诞生	1995，瑞典 MySQL AB	1996，UC Berkeley
当前归属	Oracle	PostgreSQL 全球开发组
开源协议	GPL	PostgreSQL License（类 MIT）
核心定位	简单高效的 Web 数据库	功能完备的企业级数据库
典型用户	Facebook、GitHub、YouTube	Apple、Instagram、Spotify
最新版本（2026）	9.x	18.x

二、架构与核心差异

1. 存储引擎

这是 MySQL 和 PG 最底层的差异。

MySQL 采用可插拔存储引擎架构，不同引擎底层实现完全不同：

-- 查看支持的引擎
SHOW ENGINES;

-- 建表时指定引擎
CREATE TABLE t (id INT) ENGINE=InnoDB;

引擎	特点	适用场景
InnoDB（默认）	支持事务、行锁、MVCC	99% 的场景
MyISAM	不支持事务，表锁，读快	只读日志表
Memory	数据存内存，重启丢失	临时表、缓存

PostgreSQL 只有一个统一存储引擎，所有功能（事务、MVCC、各种索引）原生内置，不需要用户选择引擎，降低心智负担。

一句话：MySQL 给你选择自由但也带来选错的可能；PG 只有一个引擎，开箱即用所有高级功能。

2. MVCC 实现

多版本并发控制（MVCC）是两者处理并发的核心机制，但实现方式截然不同：

维度	MySQL（InnoDB）	PostgreSQL
旧版本存储	undo log（回滚段）	数据页内保留多版本
事务 ID	递增 trx_id	递增 xid
清理机制	purge 线程异步清理	VACUUM 标记回收
回滚开销	重放 undo log，慢	直接标记事务无效，快
胀风险	长事务导致 undo log 膨胀	长事务导致表膨胀，需 VACUUM

PG 的 VACUUM 是新手最容易踩的坑：

-- 手动 VACUUM
VACUUM ANALYZE table_name;

-- 但实际生产环境都配 autovacuum 自动清理

MySQL 通过独立的 purge 线程在后台自动清理，对 DBA 更友好。

3. SQL 标准合规度

在 SQL 标准支持上，PG 明显领先：

特性	MySQL	PostgreSQL
CTE（WITH 子句）	✅ 8.0+	✅（最早支持）
窗口函数	✅ 8.0+	✅
CHECK 约束	⚠️ 8.0.16+ 才生效	✅ 从一开始
递归 CTE	✅	✅
INTERSECT / EXCEPT	✅	✅
FULL OUTER JOIN	❌	✅
物化视图	❌	✅
表继承	❌	✅
自定义类型	❌	✅
CHECK OPTION 视图	✅	✅

实际影响最大的三个差异：

-- 1. FULL OUTER JOIN，MySQL 只能模拟
-- PostgreSQL：
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;

-- MySQL 替代写法：
SELECT * FROM a LEFT JOIN b ON a.id = b.id
UNION ALL
SELECT * FROM a RIGHT JOIN b ON a.id = b.id WHERE a.id IS NULL;

-- 2. CHECK 约束（MySQL 8.0.16 之前直接忽略）
-- PostgreSQL 从一开始就严格执行
ALTER TABLE users ADD CHECK (age >= 0);

-- 3. 物化视图（MySQL 没有，PG 原生支持）
CREATE MATERIALIZED VIEW mv_sales AS
SELECT date, SUM(amount) FROM orders GROUP BY date;

4. 数据类型

PG 的类型系统远比 MySQL 丰富：

类型	MySQL	PostgreSQL
自增主键	`INT AUTO_INCREMENT`	`SERIAL` / `GENERATED AS IDENTITY`
布尔值	`TINYINT(1)` 模拟	原生 `BOOLEAN`
数组	❌（用 JSON 模拟）	`INT[]`, `TEXT[]`
JSON	`JSON`（不校验）	`JSON` + `JSONB`（二进制高效）
UUID	❌	原生 `UUID` 类型
IP 地址	❌	原生 `INET`, `CIDR`
范围	❌	`int4range`, `daterange`
枚举	`ENUM('a','b')`	`CREATE TYPE mood AS ENUM(...)`
全文搜索向量	❌	原生 `tsvector`

JSON 是关键差一点：MySQL 的 JSON 本质是文本存储，每次读取都需解析；PG 的 JSONB 以二进制存储，支持 GIN 索引加速查询。

-- PostgreSQL JSONB 查询示例
-- 建索引
CREATE INDEX ON products USING GIN ((attrs->'tags'));

-- 直接索引加速查询
SELECT * FROM products WHERE attrs->'tags' @> '["phone"]';

5. 索引

索引类型	MySQL	PostgreSQL
B-Tree	✅	✅
Hash	✅（Memory 引擎）	✅
全文索引	✅（InnoDB）	✅（GIN）
空间索引（R-Tree）	✅	✅（GiST）
部分索引	❌	✅
表达式索引	⚠️ 8.0.13+ 函数索引	✅
覆盖索引	✅ Using index	✅ Index Only Scan
BRIN（块范围索引）	❌	✅

PG 的部分索引特别实用：

1
2
3

-- 只对活跃用户建索引，节省空间
CREATE INDEX idx_active_users ON users (email)
    WHERE status = 'active';

6. 事务隔离级别

MySQL 默认 REPEATABLE READ，PG 默认 READ COMMITTED。MySQL 在 REPEATABLE READ 下通过间隙锁一定程度避免了幻读，但对标 SQL 标准并不严格一致。PG 的隔离实现更贴合 SQL 标准定义。

-- MySQL 查看当前隔离级别
SELECT @@transaction_isolation;

-- PostgreSQL 查看
SHOW default_transaction_isolation;

三、性能与并发

1. 连接模型

维度	MySQL	PostgreSQL
连接模型	一个连接 = 一个线程	一个连接 = 一个进程
内存开销	低（共享内存空间）	高（每连接 ~5-10MB）
连接池	ProxySQL、内置连接池	必须用 pgbouncer/pgpool-II
高并发连接	适合大量短连接	需要连接池中间件

PG 的连接开销是实际生产环境常见的坑：如果应用直接用 1000 个连接连 PG，数据库可能直接 OOM。务必前置 pgbouncer。

2. 读写性能

场景	表现
简单读（主键/索引查）	两者差距不大，都很优秀
简单写（单行 INSERT/UPDATE）	MySQL 略快，得益于线程模型和更简单的 MVCC
复杂分析查询	PG 明显更快，优化器更成熟，支持并行查询
大量更新	PG 更新代价更高（需要 VACUUM），MySQL 在这方面通常更快
高并发写入	PG 通过更好的锁机制处理高并发写入更有优势

无绝对优劣，关键看场景。但有一个不争的事实：如果你的应用有长事务混合分析类查询，PG 更稳。

3. 复制与高可用

维度	MySQL	PostgreSQL
异步复制	✅ 内置	✅ 内置
半同步复制	✅	✅（synchronous_commit）
逻辑复制	✅（binlog）	✅（10.0+ 原生）
组复制	✅（Group Replication）	❌（需 Patroni 等工具）
最常用 HA 方案	MGR / Orchestrator	Patroni + etcd

PG 流复制配置示例：

# 主库 postgresql.conf
wal_level = replica
max_wal_senders = 5
archive_mode = on

# 从库
pg_basebackup -h master_host -D /var/lib/postgresql/data -P -R

MySQL 复制配置示例：

# my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

1
2
3

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

4. 连接池

MySQL 因为线程模型开销不大，轻度使用可直接连。PG 则基本离不开连接池：

# pgbouncer.ini — PG 的标配连接池
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction      # session | transaction | statement
max_client_conn = 1000
default_pool_size = 25

transaction 模式最推荐：连接只在事务期间占用，事务提交即归还池。

四、运维与生态

1. 安装

MySQL（Ubuntu）：

1
2
3

sudo apt update && sudo apt install mysql-server -y
sudo systemctl start mysql
sudo mysql_secure_installation  # 安全初始化，强烈建议运行

PostgreSQL（Ubuntu）：

sudo apt update && sudo apt install postgresql -y
sudo systemctl start postgresql
# PG 默认创建一个名为 postgres 的超级用户
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_password';"

2. 配置文件

维度	MySQL	PostgreSQL
主配置路径	`/etc/mysql/mysql.conf.d/`	`/etc/postgresql/<ver>/main/`
配置文件	`mysqld.cnf`	`postgresql.conf`
访问控制	`mysql.user` 表	`pg_hba.conf`
重载配置	`SET GLOBAL` 在线修改	`SELECT pg_reload_conf()`

PG 的 pg_hba.conf 控制谁从哪连，格式很清晰：

1
2
3

# TYPE  DATABASE  USER  ADDRESS      METHOD
host    all       all   127.0.0.1/32 md5
host    all       all   10.0.0.0/8   scram-sha-256

3. 备份

方式	MySQL	PostgreSQL
逻辑备份	`mysqldump`	`pg_dump` / `pg_dumpall`
物理备份	XtraBackup（Percona）	`pg_basebackup`
时间点恢复	binlog 回放	WAL 归档回放

常用备份命令：

# MySQL 逻辑备份
mysqldump -u root -p --all-databases --single-transaction > backup.sql

# PostgreSQL 逻辑备份
pg_dump -U postgres --clean --if-exists dbname > backup.sql

# PostgreSQL 物理备份（全量）
pg_basebackup -U postgres -D /backup/ -Ft -z -P

4. GUI 工具

工具	MySQL	PostgreSQL
官方/最流行	MySQL Workbench	pgAdmin
通用 IDE	DBeaver（推荐）、DataGrip（JetBrains）	同左
Web 管理	phpMyAdmin、Adminer	Adminer、pgweb
命令行	mysql CLI	psql

psql 远比 mysql CLI 强大：

# psql 连接后常用快捷键
\d           # 列出所有表
\d table     # 查看表结构
\x           # 扩展显示模式（宽表友好）
\e           # 打开编辑器写 SQL
\i file.sql  # 执行 SQL 文件
\timing      # 显示查询耗时

5. 监控

-- MySQL：查看慢查询
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- PostgreSQL：查看正在运行的查询
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- PostgreSQL：查看表相关统计
SELECT * FROM pg_stat_user_tables ORDER BY n_tup_ins DESC;

6. 用户与权限

-- MySQL 建用户
CREATE USER 'app'@'%' IDENTIFIED BY 'password';
GRANT ALL ON mydb.* TO 'app'@'%';
FLUSH PRIVILEGES;

-- PostgreSQL 建用户
CREATE ROLE app WITH LOGIN PASSWORD 'password';
GRANT ALL ON DATABASE mydb TO app;
-- 注意：PG 还需单独授权 schema 内对象
GRANT ALL ON ALL TABLES IN SCHEMA public TO app;

MySQL 的权限控制更直观——跟 IP 绑定，Web 开发者上手快。PG 需要同时改 pg_hba.conf 和 SQL 授权两处，初学者容易漏掉。

五、选型建议

场景	推荐	理由
小型 Web 应用 / 博客	MySQL	简单够用，生态成熟，成本低
高并发短连接	MySQL	线程模型轻量，连接池不必须
CMS / 电商	看栈	LAMP/WP → MySQL；Django/Rails → PG
数据分析 / BI 查询	PostgreSQL	优化器强，并行查询，窗口函数成熟
GIS / 地理空间	PostgreSQL	PostGIS 扩展是行业标准
全文搜索	两者一般	不如上 Elasticsearch，轻量场景 PG 的 tsvector 可用
数据仓库 / 复杂逻辑	PostgreSQL	CTE、物化视图、表继承、自定义函数
金融 / 高一致性	PostgreSQL	SQL 标准合规、更严格的约束检查
微服务 / 多语言项目	PostgreSQL	丰富的数据类型、JSONB、多语言生态
团队 DBA 有限	MySQL	MySQL 运维门槛低，PG 的 VACUUM 和连接池需要学习
要求最高开源兼容	PostgreSQL	PG License 比 GPL 更宽松

一个简单判断标准：

如果你的应用 CRUD 为主、读多写少、团队对数据库不太熟 → MySQL
如果你的应用有复杂查询、需要数据强一致、或用到地理/数组/JSON 处理 → PostgreSQL

两者都是优秀的数据库，大多数互联网场景下都能胜任。选好之后深挖、坚持比来回切换更重要。

六、命令速查对照

基础操作

操作	MySQL	PostgreSQL
连接	`mysql -u root -p`	`psql -U postgres`
列出数据库	`SHOW DATABASES;`	`\l`
切换数据库	`USE dbname;`	`\c dbname`
列出表	`SHOW TABLES;`	`\dt`
查看表结构	`DESC tablename;`	`\d tablename`
退出	`exit;`	`\q`

数据库操作

操作	MySQL	PostgreSQL
创建数据库	`CREATE DATABASE mydb;`	`CREATE DATABASE mydb;`
删除数据库	`DROP DATABASE mydb;`	`DROP DATABASE mydb;`
导入 SQL 文件	`mysql -u user -p db < file.sql`	`psql -U user -d db -f file.sql`
导出	`mysqldump -u user -p db > dump.sql`	`pg_dump -U user db > dump.sql`

表操作

操作	MySQL	PostgreSQL
创建自增主键	`id INT AUTO_INCREMENT PRIMARY KEY`	`id SERIAL PRIMARY KEY`
添加列	`ALTER TABLE t ADD col INT;`	`ALTER TABLE t ADD col INT;`
删除列	`ALTER TABLE t DROP col;`	`ALTER TABLE t DROP col;`
重命名表	`RENAME TABLE a TO b;`	`ALTER TABLE a RENAME TO b;`
清空表	`TRUNCATE TABLE t;`	`TRUNCATE TABLE t;`
查看建表语句	`SHOW CREATE TABLE t;`	`\d+ t`

索引

操作	MySQL	PostgreSQL
创建索引	`CREATE INDEX idx ON t (col);`	`CREATE INDEX idx ON t (col);`
唯一索引	`CREATE UNIQUE INDEX idx ON t (col);`	`CREATE UNIQUE INDEX idx ON t (col);`
查看索引	`SHOW INDEX FROM t;`	`\di`
删除索引	`DROP INDEX idx ON t;`	`DROP INDEX idx;`

用户与权限

操作	MySQL	PostgreSQL
创建用户	`CREATE USER 'u'@'%' IDENTIFIED BY 'p';`	`CREATE ROLE u LOGIN PASSWORD 'p';`
授权	`GRANT ALL ON db.* TO 'u'@'%';`	`GRANT ALL ON DATABASE db TO u;`
查看用户	`SELECT user,host FROM mysql.user;`	`\du`
修改密码	`ALTER USER 'u'@'%' IDENTIFIED BY 'new';`	`ALTER ROLE u PASSWORD 'new';`
删除用户	`DROP USER 'u'@'%';`	`DROP ROLE u;`

日常管理

操作	MySQL	PostgreSQL
当前版本	`SELECT VERSION();`	`SELECT VERSION();`
运行时配置	`SHOW VARIABLES LIKE '%xxx%';`	`SHOW xxx;` 或 `SELECT current_setting('xxx');`
查看连接数	`SHOW PROCESSLIST;`	`SELECT * FROM pg_stat_activity;`
杀查询	`KILL <id>;`	`SELECT pg_terminate_backend(<pid>);`
慢查询	`SHOW VARIABLES LIKE 'slow%';`	需在 `postgresql.conf` 配置 `log_min_duration_statement`

事务快捷操作

-- 两者通用
START TRANSACTION;  -- 或 BEGIN;
-- ... SQL 语句 ...
COMMIT;
-- 或
ROLLBACK;

总结

	MySQL	PostgreSQL
一句话	简单高效，互联网标配	功能完备，企业级首选
最适合	CRUD Web 应用	复杂查询、数据分析
上手难度	低	中（VACUUM、连接池需额外了解）
SQL 标准	60%	95%
扩展能力	中	高（扩展、自定义类型、函数语言）

两者没有绝对的好坏，选型本质是匹配场景。关键是深入了解你选的那一个，而不是来回切换纠结。