一、事务隔离之间的比较
| 事务隔离级别 | postgresql | mysql |
|---|---|---|
| 读未提交 | 无法读脏数据 | 有 |
| 读已提交 | 快照实现 | 快照实现 |
| 可重复读 | 有，无幻读，发生冲突时，牺牲其中一个事务 | 已实现，有幻读，悲观锁，因为Gap Lock问题，存在性能问题 |
| 可串行化 | 已实现，通过SSI实现，乐观锁，性能比较好 | 悲观锁，S2PL，性能不好，实用性比较差 |
二、持久化之间的比较
| 持久化技术 | postgresql | mysql |
|---|---|---|
| 事务的持久化 | WAL日志 | binlog和innodb的redo log |
| 页断裂问题 | full_page_writes | double write |
| 检查块的一致性 | checksum | checksum,db_lock_checking |
| 事务同步提交 | 默认是同步synchronous_commit=on,session级别可以设置，更灵活 | sync_binlog=1,innodb_flush_log_at_trx_commit=1全局参数 |
postgresql中的synchronous_commit
synchronous_commit:同步提交参数，控制事务提交后返回客户端是否成功的策略
选值类型:
on:1)没有开启备库，当wal日志真正刷新到磁盘永久存储后才会返回客户端事务已提交成功；2)开启了同步备库，设置synchronous_standby_names，必须要等事务日志刷新到本地磁盘，并且还要等远程备库页提交到磁盘才能返回客户端已经提交
off:写到缓存中就会向客户端返回提交成功，延迟写入磁盘，延迟时间大为3倍的wal_writer_delay参数。
remote_write:当事务提交时，不仅要把wal刷新到磁盘，还需要等wal日志发送备库操作系统，但不需要等备库刷新到磁盘，因此如果备库此时发生实例终端下会有数据丢失，因为数据还在操作系统上，而如果操作系统故障，则此部分wal日志还没有来的及就会丢失，备库启动后还需要主库读取wal日志
local:当事务提交时，仅写入本地磁盘即可返回客户端事务提交成功，而不管是否同步备库
PS： 如果没有设置同步备库，则on/remote_write/local都是一样的，仅等待事务刷新到本地磁盘
-- session局部设置，异步同步方式
set local synchronous_commit to OFF;三、复制之间的比较
| postgresql | mysql |
|---|---|
| 支持物理复制和逻辑复制 | 仅支持逻辑复制 |
| 物理复制延迟小 | 逻辑复制延时大 |
| 大更新对复制延迟影响小 | 大更新对复制延迟的影响很大，很容易导致复制延迟 |
| 物理复制主备之间数据绝对一致 | 逻辑复制可能出现准备数据不一致情况 |
| 支持一主多从，支持联级复制，不支持双主架构 | 支持一主多从，支持联级复制，支持双主架构 |
四、查询相关功能的比较
| postgresql | mysql |
|---|---|
| 成熟的基于代价的SQL优化器，复杂SQL性能很好 | 查询优化器不够成熟，非完全的基于代价的SQL优化器，对复杂查询的性能较低 |
| 多种表连接类型:nested-loop join,sort-merge join,hash join | 一种表连接类型:nested-loop |
| 非常智能，可以走多个索引 | 大部分查询只能使用表上得单一索引;在某些情况下，会存在使用多个索引的查询，但是查询优化器通常会低估其成本，它们常常比表扫描还有慢 |
| pg11版本之前表增加列基本上是重建表和索引，消耗时间比较长，pg11之后对非空列添加不需要重建 | 表增加列，只是在数据字典中增表定义，不会重建表 |
| 支持在线创建索引，pg10开始支持并行创建索引 | 支持在线创建索引，不支持并发索引 |
| 支持并行查询 | mysql8.0之前不支持并行查询 |
| 支持B-树，哈希，R-树和Gist索引 | B-树，哈希（不同存储引擎） |
postgresql连接类型
1).nested loop join
2).merge join
3).hash join
各连接方式之期的对比：
| 类别 | nested-loop | sort-merge join | hash join |
|---|---|---|---|
| 优化器提示 | use_nl | use_merge | use_hash |
| 使用条件 | 任何连接 | 主要用于不等价连接： <,>,>=,<=,不包括不等号<> | 仅用于等价连接 |
| 消耗资源 | CPU、磁盘I/0 | 内存、临时空间 | 内存、临时空间 |
| 优点 | 当有高选择性能索引或进行限制性搜索时，效率比较高，能够快速返回第一次的搜索结果 | 当缺乏索引或者索引条件模糊时，排序合并连接比较嵌套循环有效 | 当缺乏索引或索引条件模糊时，哈希连接比嵌套循环有效。适用于数据量较大的场景 |
| 缺点 | 当索引丢失或查询条件限制不够时，效率很低 | 所有的表都需要排序，它为最优化吞吐量而设置，并且在结果没有全表找到前不返回数据 | 为建立哈希表，需要大量内存，第一次结果返回速度很慢 |
五、表功能之间的比较
| postgresql | mysql |
|---|---|
| 除了支持pl/pgsql写存储过程，还支持pl/perl,pl/python,pl/tcl;也支持用C语言的写存储过程 | 存储过程与触发器功能有限 |
| 有单独的sequence | 没有单独的sequence,sequence在表主键上 |
| PostGIS插件支持，PostGIS是目前使用最广泛地开源GIS系统 | GIS的支持有限 |
| 通过GIN索引提供了对JSON内部数据的索引，只要创建索引之后，不管存储json的数据格式如何变化，都能利用GIN索引加快查询 | json类型只支持虚拟列方式创建索引，不支持json内部数据的索引，当json内部的列结构不确定时，不能事先创建索引来提高查询性能 |
| 通过FDW框架完善支持外部表功能，可以方便连接其他异构数据源，如mysql,mongodb等 | 外部表功能有限，基本不具有太多实用价值 |
| 堆表，不支持索引组织表 | 索引组织表，不支持堆表 |
| ddl可以回滚，支持原子性DDL | DDL不支持回滚，mysql8.0之前多个ddl不能原子执行 |
| 支持窗口函数 | mysql8.0之前不支持窗口函数 |
六、视图和安全之间的比较
| postgresql | mysql |
|---|---|
| 支持物化视图 | 不支持物化视图 |
| 支持临时表 | 支持临时表 |
| 支持主键，外键，唯一键，检查，非约束，还支持exclusion constraints | 支持主键，外键，唯一键，检查，非约束 |
| 支持两阶段提交 | 支持两阶段提交 |
| 认证方式丰富，信任/口令/PAM/LDAP/Kerberos/基于ident | 基本支持密码认证 |
| 可以使用pgcrypto库中的函数对列进行加密/解密；可以通过ssl连接实现网络加密 | 可以在表级制定密码来对数据进行加密；可以使用aes_encrypt和aes_decrypt函数对列数据进行加密和解密；可以通过ssl连接实现网络加密 |
| 使用explain命令查看查询的解释计划，结果很直观，也很详细 | 使用explain命令查看查询解释计划，但结果不直观，不详细 |
| postgresql完成遵从ACID | mysql只有innodb等少量存储引擎遵从ACID |
什么是物化视图
物化视图：不同于一般视图，它会将视图内容物理保存在数据库中，是关系型数据库中很重要的一个功能。
-- 创建物化视图
create materialized view vw_name as ...
-- 刷新物化视图
refresh materialized view vw_name;七、postgresql和mysql的优势比较
1）postgresql相对于mysql的优势：
2）mysql相对于postgresql的优势：
文档来源：
你是否还在寻找稳定的海外服务器提供商？创新互联www.cdcxhl.cn海外机房具备T级流量清洗系统配攻击溯源，准确流量调度确保服务器高可用性，企业级服务器适合批量采购，新人活动首月15元起，快前往官网查看详情吧