草庐IT

postgresql和mysql之间比较

Chsavvy 2023-04-10 原文

一、事务隔离之间的比较

事务隔离级别postgresqlmysql
读未提交无法读脏数据有
读已提交快照实现快照实现
可重复读有，无幻读，发生冲突时，牺牲其中一个事务已实现，有幻读，悲观锁，因为Gap Lock问题，存在性能问题
可串行化已实现，通过SSI实现，乐观锁，性能比较好悲观锁，S2PL，性能不好，实用性比较差

二、持久化之间的比较

持久化技术postgresqlmysql
事务的持久化WAL日志binlog和innodb的redo log
页断裂问题full_page_writesdouble write
检查块的一致性checksumchecksum,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;

三、复制之间的比较

postgresqlmysql
支持物理复制和逻辑复制仅支持逻辑复制
物理复制延迟小逻辑复制延时大
大更新对复制延迟影响小大更新对复制延迟的影响很大，很容易导致复制延迟
物理复制主备之间数据绝对一致逻辑复制可能出现准备数据不一致情况
支持一主多从，支持联级复制，不支持双主架构支持一主多从，支持联级复制，支持双主架构

四、查询相关功能的比较

postgresqlmysql
成熟的基于代价的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-loopsort-merge joinhash join
优化器提示use_nluse_mergeuse_hash
使用条件任何连接主要用于不等价连接：
<,>,>=,<=,不包括不等号<>		仅用于等价连接
消耗资源CPU、磁盘I/0内存、临时空间内存、临时空间
优点当有高选择性能索引或进行限制性搜索时，效率比较高，能够快速返回第一次的搜索结果当缺乏索引或者索引条件模糊时，排序合并连接比较嵌套循环有效当缺乏索引或索引条件模糊时，哈希连接比嵌套循环有效。适用于数据量较大的场景
缺点当索引丢失或查询条件限制不够时，效率很低所有的表都需要排序，它为最优化吞吐量而设置，并且在结果没有全表找到前不返回数据为建立哈希表，需要大量内存，第一次结果返回速度很慢

五、表功能之间的比较

postgresqlmysql
除了支持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可以回滚，支持原子性DDLDDL不支持回滚，mysql8.0之前多个ddl不能原子执行
支持窗口函数mysql8.0之前不支持窗口函数

六、视图和安全之间的比较

postgresqlmysql
支持物化视图不支持物化视图
支持临时表支持临时表
支持主键，外键，唯一键，检查，非约束，还支持exclusion constraints支持主键，外键，唯一键，检查，非约束
支持两阶段提交支持两阶段提交
认证方式丰富，信任/口令/PAM/LDAP/Kerberos/基于ident基本支持密码认证
可以使用pgcrypto库中的函数对列进行加密/解密；可以通过ssl连接实现网络加密可以在表级制定密码来对数据进行加密；可以使用aes_encrypt和aes_decrypt函数对列数据进行加密和解密；可以通过ssl连接实现网络加密
使用explain命令查看查询的解释计划，结果很直观，也很详细使用explain命令查看查询解释计划，但结果不直观，不详细
postgresql完成遵从ACIDmysql只有innodb等少量存储引擎遵从ACID

什么是物化视图

物化视图：不同于一般视图，它会将视图内容物理保存在数据库中，是关系型数据库中很重要的一个功能。

-- 创建物化视图
create materialized view vw_name as ...

-- 刷新物化视图
refresh materialized view vw_name;

七、postgresql和mysql的优势比较

1）postgresql相对于mysql的优势：

  1. 在SQL的标准实现上要比mysql完善，而且功能实现比较严谨
  2. 存储过程的功能支持要比mysql好，具备本地缓存执行计划的能力
  3. 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强
  4. pg主表采用堆表存放，mysql采用索引组织表，能够支持比mysql更大的数据量
  5. PG的主备复制属于物理复制，相对于mysql基于的binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小

2）mysql相对于postgresql的优势：

  1. innodb的基于回滚段实现的MVCC机制，相对pg新老数据存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发vacuum，会带来的多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。
  2. mysql采用索引组织表，这种存储方式非常适合基于主键匹配的查询，删改操作，但是对表结构设置存在约束
  3. mysql优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作
  4. MySQL分区表的实现要优于pg的基于继承表的分区实现，主要体现在分区个数的达到上千万后的处理性能差异的较大
  5. mysql的存储引擎插件化机制，使得它的应用场景更加广泛

文档来源：

  1. 版本比较：https://blog.csdn.net/qq_43303221/article/details/89338142
  2. 优势比较：https://www.cnblogs.com/sbj-dawn/p/8053549.html
  3. postgresql连接方式：https://blog.csdn.net/zhousenshan/article/details/78463029
postgresqlmysqlltgtquot

有关postgresql和mysql之间比较的更多相关文章

  1. ruby-on-rails - Rails 应用程序之间的通信 - 2

    我构建了两个需要相互通信和发送文件的Rails应用程序。例如，一个Rails应用程序会发送请求以查看其他应用程序数据库中的表。然后另一个应用程序将呈现该表的json并将其发回。我还希望一个应用程序将存储在其公共(public)目录中的文本文件发送到另一个应用程序的公共(public)目录。我从来没有做过这样的事情，所以我什至不知道从哪里开始。任何帮助，将不胜感激。谢谢! 最佳答案 无论Rails是什么，几乎所有Web应用程序都有您的要求，大多数现代Web应用程序都需要相互通信。但是有一个小小的理解需要你坚持下去，网站不应直接访问彼此

  2. ruby - Ruby 的 Hash 在比较键时使用哪种相等性测试？ - 2

    我有一个围绕一些对象的包装类，我想将这些对象用作散列中的键。包装对象和解包装对象应映射到相同的键。一个简单的例子是这样的:classAattr_reader:xdefinitialize(inner)@inner=innerenddefx;@inner.x;enddef==(other)@inner.x==other.xendenda=A.new(o)#oisjustanyobjectthatallowso.xb=A.new(o)h={a=>5}ph[a]#5ph[b]#nil,shouldbe5ph[o]#nil,shouldbe5我试过==、===、eq?并散列所有无济于事。

  3. ruby - ＃之间？ Cooper 的 *Beginning Ruby* 中的错误或异常 - 2

    在Cooper的书BeginningRuby中，第166页有一个我无法重现的示例。classSongincludeComparableattr_accessor:lengthdef(other)@lengthother.lengthenddefinitialize(song_name,length)@song_name=song_name@length=lengthendenda=Song.new('Rockaroundtheclock',143)b=Song.new('BohemianRhapsody',544)c=Song.new('MinuteWaltz',60)a.betwee

  4. ruby-on-rails - `a ||= b` 和 `a = b if a.nil 之间的区别？ - 2

    我正在检查一个Rails项目。在ERubyHTML模板页面上，我看到了这样几行:我不明白为什么不这样写:在这种情况下，||=和ifnil?有什么区别？ 最佳答案 在这种特殊情况下没有区别，但可能是出于习惯。每当我看到nil?被使用时，它几乎总是使用不当。在Ruby中，很少有东西在逻辑上是假的，只有文字false和nil是。这意味着像if(!x.nil?)这样的代码几乎总是更好地表示为if(x)除非期望x可能是文字false。我会将其切换为||=false，因为它具有相同的结果，但这在很大程度上取决于偏好。唯一的缺点是赋值会在每次运行

  5. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  6. [工业相机] 分辨率、精度和公差之间的关系 - 2

    📢博客主页：https://blog.csdn.net/weixin_43197380📢欢迎点赞👍收藏⭐留言📝如有错误敬请指正！📢本文由Loewen丶原创，首发于CSDN，转载注明出处🙉📢现在的付出，都会是一种沉淀，只为让你成为更好的人✨文章预览：一.分辨率（Resolution）1、工业相机的分辨率是如何定义的？2、工业相机的分辨率是如何选择的？二.精度（Accuracy）1、像素精度（PixelAccuracy）2、定位精度和重复定位精度（RepeatPrecision）三.公差（Tolerance）四.课后作业（Post-ClassExercises）视觉行业的初学者，甚至是做了1~2年

  7. ruby-on-rails - 如何让 datamapper 与 postgresql 数据库一起工作？ - 2

    我已经找到了几个使用datamapper的示例，并且能够让它们正常工作。不过，所有这些示例都是针对sqlite数据库的。我正在尝试将数据映射器与postgresql一起使用。我将datamapper中的调用从sqlite3更改为postgres，并且我已经安装了dm-postgres-adapter。但它仍然不起作用。我还需要做什么？ 最佳答案 与SQLite不同，PostgreSQL不将数据库存储在单个文件中。在你拥有createdyourdatabase之后，尝试这样的事情:DataMapper.setup:default,{:

  8. ruby - 无法理解 `puts{}.class` 和 `puts({}.class)` 之间的区别 - 2

    由于匿名block和散列block看起来大致相同。我正在玩它。我做了一些严肃的观察，如下所示:{}.class#=>Hash好的，这很酷。空block被视为Hash。print{}.class#=>NilClassputs{}.class#=>NilClass为什么上面的代码和NilClass一样，下面的代码又显示了Hash？puts({}.class)#Hash#=>nilprint({}.class)#Hash=>nil谁能帮我理解上面发生了什么？我完全不同意@Lindydancer的观点你如何解释下面几行:print{}.class#NilClassprint[].class#A

  9. ruby - 是否有用于复杂比较的漂亮语法？ - 2

    方法应返回-1,0或1分别表示“小于”、“等于”和“大于”。对于某些类型的可排序对象，通常将排序顺序基于多个属性。以下是可行的，但我认为它看起来很笨拙:classLeagueStatsattr_accessor:points,:goal_diffdefinitializepts,gd@points=pts@goal_diff=gdenddefothercompare_pts=pointsother.pointsreturncompare_ptsunlesscompare_pts==0goal_diffother.goal_diffendend尝试一下:[LeagueStats.new(

  10. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

随机推荐