前言
最近接手了新项目，数据库是PG，跟之前的MySQL很像，但还是有点差别，主要从索引和优化的角度来对比学习。
MySQL&PostgreSQL对比
索引原理
快速搜索
- 哈希：算法复杂度O(1)，高效的等值查找，缺点是不支持范围查询，还有哈希冲突问题需要解决
- 二分法：可以将O(n)时间复杂度降低到O(log n)，二叉树作为索引存在很多问题，比如树的高度太高，数据容易出现不平衡
B-tree和B+tree
B-tree
B+tree
索引类型
| 索引类型 | MySQL | PostgreSQL |
|---|---|---|
| B-tree | 默认索引 | 默认索引 |
| Hash | InnoDB不支持 | 支持 |
| Full-text | 支持 | 支持，更强大 |
索引对比
- Mysql: InnoDB是B+tree（聚集索引+非聚集索引）
| 主索引 | 辅助索引 |
|---|---|
 |
 |
- Pg: B+tree的变形（非聚集索引）
使用场景差别
主键
- MySQL：主键最好不要太长，会出现在所有的二级索引中（因此我们用自增）
- PostgreSQL：没有这个问题
查询操作
| MySQL | PostgreSQL |
|---|---|
 |
 |
联合索引和范围查找
- MySQL：当左侧索引进行范围查找时，剩余的索引不会生效
- PostgreSQL：剩余索引会生效
原因：优化器的不同（TODO 为什么）
插入/更新操作
- MySQL：保证存储顺序，在原有数据上更新
- PostgreSQL：在桶里新增数据，保留原始数据，后续可用在事务的回滚上
优化器
优化器的区别
MySQL 优化器
- 简单高效：MySQL 的优化器设计注重简单性和效率，目标是在大多数情况下提供良好的性能，而不需要过于复杂的优化策略。
- 基于规则和成本：MySQL 的优化器结合了基于规则的优化和基于成本的优化。基于规则的优化主要用于处理常见的查询模式，而基于成本的优化则用于评估不同执行计划的成本。
- 分阶段优化：MySQL 的优化过程分为多个阶段，包括逻辑转换、优化准备、基于成本优化和执行计划改进。
PostgreSQL 优化器
- 高度灵活：PostgreSQL 的优化器设计更加灵活和复杂，能够处理更广泛的查询模式和优化需求。
- 基于成本：PostgreSQL 的优化器主要基于成本模型，通过详细的统计信息和复杂的算法来评估不同执行计划的成本，选择最优的执行计划。
- 动态规划和遗传算法：PostgreSQL 优化器使用动态规划和遗传算法来生成和选择最优的执行计划，特别是在处理多表连接时。
SQL优化
索引使用
建议不超过5个索引
哪些列应该加索引
- 在where语句中的
- order by / group by / distinct中出现的
- join on中出现的
联合索引的顺序
- 区分度最高：重复的少
- 字段长度小的放左侧
- 频繁用的放在左侧
索引失效的场景
- like ‘%123’
- not in（用left join和not exists优化）
- null值判断
- 数据类型隐式转换
- 使用or
实战
分析SQL执行计划
1 | explain "your sql" |