关系型数据库对比与选型笔记
· 阅读需 6 分钟
横向对比 MySQL / PostgreSQL / SQLite 三种主流关系型数据库,并给出按场景的选型决策。 单库详情:MySQL笔记 · PostgreSQL笔记 · SQLite笔记。 本笔记旧稿 / 旧稿 / 旧稿 均旧稿目录已清理,当前以本文为准。
目录
- 一句话定位
- 维度对比表
- 关键差异化能力
- 场景选型决策
- 反向场景(什么时候不选)
- 迁移注意
1. 一句话定位
| 数据库 | 一句话 |
|---|---|
| SQLite | 单文件、嵌入式、零配置的 SQL 引擎,跟随应用程序运行 |
| MySQL | 最流行的通用 OLTP 数据库,生态最广、运维工具最多 |
| PostgreSQL | 功能最强大的开源关系库,强类型 + 丰富类型 + 扩展机制 |
2. 维度对比表
| 维度 | SQLite | MySQL | PostgreSQL |
|---|---|---|---|
| 架构 | 嵌入式(进程内库) | C/S(默认服务端) | C/S(默认服务端) |
| 单文件 | ✅ 一库一文件 | ❌ | ❌ |
| 零配置 | ✅ | ❌(要装服务、初始化) | ❌(要装服务、初始化) |
| 网络访问 | ❌(无内置协议) | ✅ | ✅ |
| 事务 | ACID | ACID(InnoDB) | ACID,MVCC |
| 默认隔离级别 | SERIALIZABLE(事实) | REPEATABLE READ | READ COMMITTED |
| 并发写 | 全局写锁串行化 | 行锁 + MVCC(InnoDB) | 行锁 + MVCC |
| 数据类型 | 5 种动态类型 | 严格标量类型 | 严格 + 丰富(JSONB/数组/范围/枚举/自定义) |
| JSON 支持 | 文本函数(json_extract) | JSON 类型(5.7+) | JSON / JSONB(推荐后者) |
| 索引 | B-tree | B-tree / Hash / FullText | B-tree / Hash / GIN / GiST / BRIN / SP-GiST |
| 全文检索 | FTS5 扩展 | FULLTEXT(InnoDB) | 内置 tsvector + tsquery |
| GIS | ❌ | ✅(基础) | ✅ PostGIS(业界标杆) |
| 扩展机制 | 编译期 / 运行时加载扩展 | 插件 / UDF | Extension(生态庞大) |
| 主从复制 | ❌ | ✅ 成熟(异步/半同步/组复制) | ✅ 流复制 / 逻辑复制 |
| 典型上限 | 几十 GB;并发写 ~ 100 QPS 级 | 几百 GB ~ 数 TB;高并发 OLTP 主流 | 几 TB;复杂查询能力强 |
| 许可证 | Public Domain | GPL(社区版) | PostgreSQL License(类 BSD) |
| 云厂商支持 | 几乎不直供 | ✅ 全部主流 | ✅ 全部主流 |
| ORM 生态 | ✅ | ✅(最广) | ✅ |
| 适合部署 | 单机/端上 | 中大规模 Web | 中大规模 + 复杂业务 |
3. 关键差异化能力
每种数据库都有自己最值得拿出来说的"招牌能力":
3.1 SQLite — 嵌入式 + 单文件
- 零运维:拷贝文件 = 备份;删文件 = 删除数据库
- 跨平台一致:iOS / Android / Windows / macOS / Linux 都自带或一键可用
- 适合:移动 App、桌面工具、CI 临时库、单用户小工具
3.2 MySQL — 生态最成熟
- 复制生态最丰富:异步复制、半同步、组复制、MHA、Orchestrator、读写分离中间件
- 运维工具最多:Percona Toolkit、pt-online-schema-change、gh-ost(在线 DDL)、MySQL Shell
- 云厂商支持最广:RDS / Aurora / TiDB(兼容 MySQL 协议)
- 适合:典型 Web 后端、读写分离架构、需要大量运维和迁移工具
3.3 PostgreSQL — 功能天花板
- 类型系统:JSONB / 数组 / 范围 / 自定义类型 / ENUM,能在不引入 NoSQL 的情况下做混合建模
- 索引选择最多:GIN(JSONB / 数组 / 全文)、GiST(GIS / 范围)、BRIN(大表时间序列)
- 扩展生态:PostGIS(GIS)、pgvector(向量)、pg_trgm(模糊)、timescaledb(时序)
- SQL 标准贴合度:窗口函数、CTE、
MERGE、物化视图、LATERAL JOIN等新特性都很早就支持 - 适合:复杂数据模型、强一致性需求、GIS / AI / 时序场景
4. 场景选型决策
按业务场景给一个默认推荐,再加备选。
4.1 决策流程图
你的数据要不要跨进程/跨机访问?
├── 不要 → 单机/端上工具? ── 是 ──→ ✅ SQLite
│ │
│ └── 否(但服务仍在单进程)
│ ↓
└── 要(多人/多服务访问)→ 业务对 SQL 标准和扩展能力要求高吗?
├── 高(复杂类型、JSON、GIS、AI)→ ✅ PostgreSQL
└── 一般 → 是典型 Web 后端 OLTP?
├── 是 → ✅ MySQL
└── 否 → ✅ PostgreSQL(默认更安全)
4.2 按场景列表
| 场景 | 推荐 | 备选 / 理由 |
|---|---|---|
| 移动 App / 桌面软件本地存储 | SQLite | 单文件、零配置、跨平台 |
| 浏览器端 / Electron / Tauri | SQLite | 跨端一致,WASM 版本可跑在浏览器 |
| 单用户小工具脚本 / CLI | SQLite | 文件即数据库,无需安装服务 |
| 集成测试临时库 | SQLite(memory 或 file) | 起停快,无外部依赖 |
| 典型 Web 后端(CRUD 为主,读多写少) | MySQL | 生态成熟,云厂商 RDS / Aurora 完备 |
| 已有团队 / 项目大量用 MySQL | MySQL | 迁移成本大于收益 |
| 复杂业务模型(金额、约束、JSON 混合、GIS) | PostgreSQL | 强类型、严格约束、扩展丰富 |
| AI / RAG(向量检索) | PostgreSQL + pgvector | 一个库搞定元数据 + 向量 |
| 地理信息系统 | PostgreSQL + PostGIS | 业界事实标准 |
| 时序数据 | PostgreSQL + TimescaleDB | 关系模型 + 时序优化 |
| OLAP / 大数据查询 | (专用数仓) | Doris / ClickHouse / StarRocks |
| 超大规模 OLTP(> 10 TB 单库) | 谨慎评估分库分表 | TiDB / CockroachDB / Aurora |
5. 反向场景(什么时候不选)
5.1 不要选 SQLite
- ❌ 多客户端通过网络同时访问(无服务端)
- ❌ 高并发写入(全局写锁)
- ❌ 单库 > 几十 GB
5.2 不要选 MySQL
- ❌ 业务大量依赖 JSON / 复杂类型 / GIS / 向量检索(PG 更合适)
- ❌ 数据完整性要求极高(PG 的约束、CHECK、外键实现更严格)
- ❌ 需要高级 SQL 特性(
MERGE、递归 CTE 性能、LATERAL JOIN、窗口函数)
5.3 不要选 PostgreSQL
- ❌ 团队完全没接触过、运维人才稀缺(MySQL 运维人手多)
- ❌ 现有系统强依赖 MySQL 特性(如特定 SQL_MODE、utf8mb3 行为)
- ❌ 极简单文件场景(SQLite 装它都嫌重)
6. 迁移注意
| 迁移方向 | 主要差异 |
|---|---|
| MySQL → PostgreSQL | 占位符 %s → %s(一致);AUTO_INCREMENT → SERIAL;TINYINT(1) → BOOLEAN;utf8 → utf8 含义不同;事务默认隔离级别不同 |
| PostgreSQL → MySQL | 去掉 JSONB(用 JSON);慎用 ENUM / 数组 / 自定义类型;CASCADE 行为差异 |
| SQLite → MySQL / PG | 升级动态类型为严格类型;删除 INTEGER PRIMARY KEY 自带 rowid 行为;用真实 AUTO_INCREMENT / SERIAL;WITHOUT ROWID / VACUUM 等特有命令消失 |
| MySQL / PG → SQLite(测试 / 迁移) | 不支持网络协议,需要数据导出导入;并发模型完全不同 |
关联笔记
- MySQL 详情:MySQL笔记
- PostgreSQL 详情:PostgreSQL笔记
- SQLite 详情:SQLite笔记
- 向量数据库:Qdrant笔记
- 数据库设计相关:数据库设计与索引优化笔记
- Demo:
example/db/sql-tools-demo/(三种库 Python 连接示例)
标签:#mysql #postgres #sqlite #mongodb