MySQL vs PostgreSQL 与生产实践

10.1 MySQL vs PostgreSQL 详细对比

特性	MySQL 8.0	PostgreSQL 16
诞生年份	1995	1996（源自 1986 POSTGRES）
开源协议	GPL（企业版商业）	PostgreSQL License（完全自由）
事务	InnoDB 引擎支持，MyISAM 不支持	全面支持，DDL 也可在事务中
默认隔离级别	REPEATABLE READ	READ COMMITTED
JSON 支持	支持（5.7+），功能较简单	原生 JSON + JSONB（二进制，可建索引）
全文搜索	内置 FULLTEXT 索引，功能有限	内置 tsvector/tsquery，多语言分词
数组类型	不支持	原生支持（可存储数组并索引）
窗口函数	8.0+ 支持	从很早版本支持，更完整
CTE 递归	8.0+ 支持	完全支持
物化视图	不原生支持	原生支持 + CONCURRENTLY 刷新
外键	InnoDB 支持	完整支持，更严格检查
读写性能	读写均衡，简单查询极快	复杂查询优化器更强
并发	MVCC，适合高并发读	MVCC，复杂并发场景表现更好
扩展性	分库分表工具成熟（ShardingSphere）	逻辑复制、Citus 水平扩展
存储引擎	多引擎（InnoDB/MyISAM/Memory）	单引擎，但可扩展插件
社区活跃度	极高，国内文档丰富	高，国际社区更活跃

10.2 何时选 MySQL，何时选 PostgreSQL

10.3 备份与恢复

MySQL 备份

# mysqldump：逻辑备份（导出 SQL 语句）
mysqldump -u root -p blog_db > blog_db_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_dbs_backup.sql

# 只备份表结构（不含数据）
mysqldump -u root -p --no-data blog_db > blog_db_schema.sql

# 压缩备份（节省空间）
mysqldump -u root -p blog_db | gzip > blog_db_$(date +%Y%m%d).sql.gz

# 恢复数据库
mysql -u root -p blog_db < blog_db_backup.sql

PostgreSQL 备份

# pg_dump：逻辑备份（纯文本 SQL 格式）
pg_dump -U postgres blog_db > blog_db_backup.sql

# pg_dump：自定义格式（支持并行恢复，更推荐）
pg_dump -U postgres -Fc blog_db > blog_db_backup.dump

# 并行恢复（-j 4 表示 4 个并行进程，恢复更快）
pg_restore -U postgres -d blog_db -j 4 blog_db_backup.dump

# 备份所有数据库（包括角色、表空间配置）
pg_dumpall -U postgres > all_dbs_backup.sql

# 恢复纯文本格式
psql -U postgres blog_db < blog_db_backup.sql

10.4 用户权限管理

SQL-- MySQL：创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword123!';
-- @'%' 表示允许从任意主机连接；@'localhost' 表示只允许本机

-- 授予最小权限（最小权限原则！）
GRANT SELECT, INSERT, UPDATE, DELETE
  ON blog_db.*
  TO 'app_user'@'%';

-- 只读用户（适合报表、BI 工具）
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'ReadOnlyPass!';
GRANT SELECT ON blog_db.* TO 'readonly_user'@'%';

-- 刷新权限（MySQL 可能需要）
FLUSH PRIVILEGES;

-- 撤销权限
REVOKE INSERT, UPDATE, DELETE ON blog_db.* FROM 'app_user'@'%';

-- 删除用户
DROP USER 'app_user'@'%';

-- PostgreSQL：创建角色和用户
CREATE ROLE app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;

CREATE USER app_user WITH PASSWORD 'StrongPassword123!';
GRANT app_role TO app_user;

10.5 连接池

每次创建数据库连接都需要 TCP 握手、认证等过程，耗时约 5-20ms。高并发时频繁创建/销毁连接会成为性能瓶颈。连接池预先创建一组连接，复用它们，避免重复开销。

连接池	适用	模式	说明
PgBouncer	PostgreSQL	独立代理进程	最流行的 PostgreSQL 连接池，支持 session/transaction/statement 三种模式
ProxySQL	MySQL	代理服务器	除了连接池，还支持读写分离、查询路由
应用层连接池	所有	内嵌在应用	如 HikariCP（Java）、node-postgres pool（Node.js）、SQLAlchemy pool（Python）

PgBouncer 配置示例

# pgbouncer.ini 核心配置
[databases]
blog_db = host=127.0.0.1 port=5432 dbname=blog_db

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432           # 应用连接 PgBouncer 的端口
pool_mode = transaction      # 事务模式：每次事务复用连接（推荐）
max_client_conn = 1000       # 允许的最大客户端连接数
default_pool_size = 20       # 每个数据库的连接池大小（真实到 PG 的连接数）
min_pool_size = 5

10.6 读写分离

读写分离是提高数据库承载能力的经典架构：主库（Master）处理写操作，从库（Replica）处理读操作。

从库延迟问题及应对

• 写后读一致性问题：用户刚写入，立刻读，可能读到旧数据（延迟未同步）
• 解决方案1：写操作完成后，读操作在主库执行（牺牲部分读扩展性）
• 解决方案2：使用 Session 粘滞：某用户最近有写操作，则该用户的读也走主库
• 解决方案3：监控复制延迟，延迟超阈值时自动降级所有读到主库

10.7 分库分表

当单表数据量超过 5000 万行，或单库 TPS 达到瓶颈时，需要考虑分库分表。

10.8 常见性能问题与解决方案清单

问题现象	可能原因	解决方案
某查询突然变慢	数据量增长、统计信息过期、锁等待	EXPLAIN 分析、ANALYZE TABLE 更新统计、查看锁等待
全表扫描（type=ALL）	缺少索引、索引失效	添加合适索引，检查 WHERE 条件是否破坏索引
CPU 飙升	慢查询堆积、大量排序/聚合	慢查询日志找出耗时 SQL，添加索引，优化 ORDER BY
磁盘 I/O 高	buffer pool 太小、大量随机 I/O	增大 innodb_buffer_pool_size（建议内存的 70%）
连接数耗尽	连接泄漏、并发连接太多	使用连接池、检查未关闭连接的代码
死锁频繁	事务加锁顺序不一致	统一加锁顺序、缩短事务、降低隔离级别
主从延迟大	大事务、DDL 操作、从库配置弱	拆分大事务、从库配置与主库一致、启用并行复制
表空间持续增长	数据不断增加、碎片化	归档历史数据、定期 OPTIMIZE TABLE、分区表
大表 ALTER 超时	ALTER TABLE 锁表	MySQL 用 pt-online-schema-change 在线 DDL；PostgreSQL 用 pg_repack

生产环境关键配置参数

# MySQL InnoDB 关键参数（my.cnf）
innodb_buffer_pool_size = 8G        # 缓冲池：建议物理内存的 60-80%
innodb_log_file_size = 512M         # Redo Log 文件大小，影响写入性能
innodb_flush_log_at_trx_commit = 1  # 1=每次提交刷盘（最安全）
sync_binlog = 1                     # 每次提交同步 binlog（最安全）
max_connections = 500               # 最大连接数（配合连接池使用）
slow_query_log = ON
long_query_time = 1                 # 超过 1 秒记入慢查询日志

# PostgreSQL 关键参数（postgresql.conf）
shared_buffers = 4GB                # 共享缓冲区：建议内存的 25%
work_mem = 64MB                     # 单个排序/哈希操作的内存（每连接）
max_connections = 200               # 配合 PgBouncer 使用
checkpoint_completion_target = 0.9  # 平滑 checkpoint，减少 I/O 峰值
wal_buffers = 64MB                  # WAL 写入缓冲
log_min_duration_statement = 1000   # 慢查询日志阈值（毫秒）

课程总结