postgresql和mysql之间比较-创新互联
一、事务隔离之间的比较
| 事务隔离级别 | 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
- 对左表中找到的每一行都要扫描右表一次。容易实现，但是可能非常耗时，但可以同规格右表索引控制。
- 对于被连接的数据子集较少的情况，nested-loop是很好的选择，但是如果右表没有索引一般就不会使用nested-loop。
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的优势：
- 在SQL的标准实现上要比mysql完善，而且功能实现比较严谨
- 存储过程的功能支持要比mysql好，具备本地缓存执行计划的能力
- 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强
- pg主表采用堆表存放，mysql采用索引组织表，能够支持比mysql更大的数据量
- PG的主备复制属于物理复制，相对于mysql基于的binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小
2）mysql相对于postgresql的优势：
- innodb的基于回滚段实现的MVCC机制，相对pg新老数据存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发vacuum，会带来的多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。
- mysql采用索引组织表，这种存储方式非常适合基于主键匹配的查询，删改操作，但是对表结构设置存在约束
- mysql优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作
- MySQL分区表的实现要优于pg的基于继承表的分区实现，主要体现在分区个数的达到上千万后的处理性能差异的较大
- mysql的存储引擎插件化机制，使得它的应用场景更加广泛
文档来源：
- 版本比较：https://blog.csdn.net/qq_43303221/article/details/89338142
- 优势比较：https://www.cnblogs.com/sbj-dawn/p/8053549.html
- postgresql连接方式：https://blog.csdn.net/zhousenshan/article/details/78463029
你是否还在寻找稳定的海外服务器提供商？创新互联www.cdcxhl.cn海外机房具备T级流量清洗系统配攻击溯源，准确流量调度确保服务器高可用性，企业级服务器适合批量采购，新人活动首月15元起，快前往官网查看详情吧
当前题目：postgresql和mysql之间比较-创新互联
文章URL：http://myzitong.com/article/dpscpi.html