一、事务隔离之间的比较
| 事务隔离级别 | 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连接类型
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的存储引擎插件化机制，使得它的应用场景更加广泛
1. 数据类型：PostgreSQL 支持更多的数据类型，如范围类型、网络地址类型、数组，JSON（mysql8.0开始支持） 和 XML 数据类型等，而 MySQL 则不支持这些类型。
2. 扩展性：PostgreSQL 具有很高的扩展性，可以使用自定义数据类型、函数和操作符来扩展其功能。而 MySQL 的扩展性较差，需要使用插件或存储过程来实现类似的功能，另外mysql在分区表现更好
3. ACID 兼容性：PostgreSQL 是完全 ACID 兼容的数据库，而 MySQL 只在特定的存储引擎 (如 InnoDB) 中支持 ACID。
4. 多版本并发控制 (MVCC)：PostgreSQL 使用 MVCC 来提供高级事务隔离级别，这意味着多个事务可以同时读取和写入数据库，而不会相互干扰。MySQL 也支持 MVCC，但其实现方式不同。
5. 存储引擎：MySQL 支持多个存储引擎，包括 MyISAM 和 InnoDB 等。每种存储引擎都有自己的特点和优缺点。PostgreSQL 仅支持单个存储引擎。
6. SQL 标准兼容性：PostgreSQL 更加符合 SQL 标准，而 MySQL 在某些方面采用了自己的实现方式。
7. 性能：在一些特定的使用场景下，MySQL 的性能可能更好。但在复杂的查询和高并发的情况下，PostgreSQL 可能表现更好。
数据写入方面，mysql通过写锁来实现真正的并发性，而pg内置了多版本并发控制支持，没有读写锁定，如果进行频繁并发的写入操作，pg表现会更加优异；
数据读取方面，pg会创建一个新的系统进程，为每个连接到数据库的用户分配大量内存，mysql是为多个用户创建一个单一进程，因此，对于主要向用户读取和显示数据的应用程序，mysql要比pg更好一点
8.学习成本：难易层度来说，mysql更适合初学者，学习曲线更短，从头开始构建新的数据库项目所需时间更少，pg则通常需要更加复杂的基础设置和问题排查经验
9.特性层面：Pg提供了一些高级特性，比如物化视图，公共表表达式（CTE,mysql也有），窗口函数（mysql也有），mysql在一些web开发中表现更加优秀。
10.架构对比: MySQL：多线程;PostgreSQL：多进程
选型考虑：
mysql性能总体更加突出，特别在读密集的场景中，而pg在处理复杂查询和写密集操作时候，更有优势，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
相关文章：
————————————————
版权声明：本文为CSDN博主「Chsavvy」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_44375561/article/details/123579313
1136
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
