海豚 VS 大象 功能对比

PostgreSQL(下面简称PG)最近受关注程度逐渐增高,更多人拿它与MySQL做对比,此文目帮助大家熟悉下两个数据库功能特点大家根据自己业务场景选择到底使用哪个数据库

体系结构

MySQL为多线程架构后台有多个线程处理内部操作例如:刷脏Undo purgecheckpoint等,整体上MySQL分为两层Server/存储引擎。存储引擎层可支持多种,Server层负责接收请求对于每个请求创建新的线程,同时SQL解析查询优化复制都是在Server处理。

PG则是多进程结构能更充分利用多CPU完成并行查询DDL,PG中也有对应后台进程负责处理垃圾回收CheckpointWAL日志。每个新请求连接都会连接到postgres主进程,随后postgres会为每一个新请求fork子进程,对于短连接高并发应用频繁fork进程还是有一定消耗通常需要添加连接池。

  • 事务 MySQL与PG都是完整支持ACID特性,都具有事务日志(Redo/WAL)。PG中并没有Undo概念,更新操作是在原表中插入一条记录,这样会造成表膨胀需要定期做vaccum操作。PG中事务ID最大值是40亿,需要注意表年龄,防止事务ID回卷问题发生。 EDB公司也正在研发具有Undo的新存储引擎zheap: https://github.com/EnterpriseDB/zheap MySQL中为了防止幻读现象使用间隙锁来解决,但PG中则是通过MVCC方式来实现防止幻读现象出现。虽然MySQL也支持MVCC但两种数据库实现方式不同、加锁实现方式也不同,MySQL增加了间隙锁,通常我们使用MySQL都要将隔离级别设置为RC,在RR下不注意很容易造成锁阻塞
  • 表 MySQL Innodb表都是索引组织表,PG中是堆表。对于索引组织表我们需要注意表中最好有自增主键这样插入时为顺序插入每次都是在表最后追加,通常二级索引会比堆表的索引要大因为要存储主键的值,索引组织表优势在于根据聚集索引查询时性能会比较好并且不用回表。
  • 索引类型 MySQL中我们通常使用B-TREE索引,PG中索引的类型比较丰富除了B-TREE索引还有hash索引部分索引GIN索引(全文检索)可满足不同业务需求,例如: %xxx%这种前后都带有%语句PG中也可使用索引提升效率
  • 数据类型 PG中数据类型是比较丰富,例如:网络类型、数组、Range类型 这些类型对开发人员比较方便。
  • 分区表 PG分区上并没有做的很完善,利用的是表继承实现。10之前版本需要手工在表上创建触发器来完成数据的路由。但是对于操作非常频繁的大表在PG中需要考虑分区,应对事务ID回卷的问题。

备份恢复

  • MySQL
    • 逻辑备份:mysqldump、 mysqlpump、mydumper
    • 物理备份:xtrbackup
  • PG
    • 逻辑备份:pgdump
    • 物理备份:pg_basebackup、pg_rman

MySQL与PG都具有逻辑与物理备份工具

  • 操作性:
    • PG中可在recover.conf文件中指定要恢复到哪个时间点,MySQL中需要人工来判断应用哪些binlog
    • 如果只是删除了部分表数据的误操作,MySQL中可使用binlog2sql等工具反解析binlog,这点PG中无法搞定
    • 当然大公司都具有自动化运维平台,不在需要这些恢复手工来操作,对于小公司或者不太熟悉数据库的人员PG在恢复操作上会方便一些
  • 恢复速度:
    • MySQL基于备份恢复后需要追加应用binlog日志到指定时间点,如果binlog日志量比较大可以将日志改名为relay log 利用SQL多线程特点加速恢复
    • PG中WAL是物理日志恢复时需要覆盖整个页

复制

  • MySQL是基于binlog的逻辑复制,5.7之前版本SQL线程是单线程如果主库有大的DDL操作会造成复制延迟,5.7之后增加了并行复制加快了复制速度。同步模式有异步、半同步两种模式,如果对数据可靠性较高可设置为一主多从+半同步,有一个从库返回ACK即可切换时判断哪个从库最靠前提升为主库即可。
  • PG是基于WAL日志的物理复制速度上比较快,即使有大的DDL也影响不大。模式有同步、异步两种,并有synchronous_commit参数可控制同步模式下WAL日志写入磁盘的各种情况。9.6之后PG增加了逻辑复制,但是个更像是逻辑订阅有比较大的限制,例如不支持DDL操作。

高可用

  • MySQL中高可用方案较多: MHAMGRGalera 等, MGRGalera可以保证高可用切换后数据强一致性。
  • PG中可以使用 pgpoolpatroni+ectd,PG中目前没有像MGRGalera这样的架构
  • 在高可用中探测主库好坏是比较重要,但MGR这种架构中相对比较简单了,MGR这种架构也会是未来的主流
  • 当然两种数据库都可以使用一些开源的高可用工具来配合完成,在MySQL主从模式下发生高可用切换后主库数据有一定概率会比备库少一些数据需要重新搭建,PG中提供了pg_rewind工具避免了主库重新搭建

与外围数据库生态建设

PG中具有FDW功能可以访问远程异构数据库,也支持PG写入同步到远程,这点可以方便解决两个数据库之间数据同步问题。 MySQL中可模拟Slave线程连接到主库上解析Binlog日志放入Kafka,通过程序方式在Kafka中消费放入到不同数据库中

分布式方案

分布式方案中我们最关心是对复杂SQL的支持和分布式事务的强一致性,这两点基于MySQL协议的Tidb和修改了PG代码的PGXC做到了,PG中可以基于FDW做分布式方案,好处在于路由节点是完整的数据库支持所有复杂SQL查询,并且不会改动内核代码随着版本升级不会太费心,会跟随新版本数据库所有新功能。

其它

两类数据库都有比较好的工具提供给我们学习研究数据库内部原理,PG、MySQL中有相应工具可以解析WAL日志、解析数据文件、解析索引结构并且源码也都是开放,但PG工具更开放一些,给大家提供一些工具: 解析PG WAL日志:安装PG后自带的pg_waldump 查看PG页结构内容:PG源码目录下contrib/pageinspect 解析PG数据文件:

https://github.com/ChristophBerg/pg_filedump

总结

如何选择两种数据库大家根据自己的业务来定,如果业务场景是单点高并发查询把数据库就当做存储访问来用那么MySQL更适合,但业务复杂尤其是Oracle迁移过来的引用具有很多存储过程触发器函数,有很多业务逻辑是放在数据库中那PG会比较合适

原文发布于微信公众号 - 3306pai(pai3306)

原文发表时间:2018-09-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏文渊之博

T-SQL性能调整(一)--编译和重新编译

概念简介 我们平时所说的查询在SQLServer 中主要有两部分来实现: 编译查询,主要包括了五个环节(缓存查找、分析、代数化、优化、缓存新计划) ?     ...

1889
来自专栏文渊之博

SQL Server中的锁的简单学习

简介     在SQL Server中,每一个查询都会找到最短路径实现自己的目标。如果数据库只接受一个连接一次只执行一个查询。那么查询当然是要多快好省的完成工作...

2745
来自专栏Laoqi's Linux运维专列

MySQL性能调优 – 你必须了解的15个重要变量

2.3K2
来自专栏小L的魔法馆

MySql修改WordPress密码

4026
来自专栏企鹅号快讯

一枚女程序员眼中的mysql,值得收藏

某群聊天内容 什么是数据库? ‍‍数据库(Database)是按照数据结构来组织、存储和管理数据的仓库, 每个数据库都有一个或多个不同的API用于创建,访问,管...

4158
来自专栏c#开发者

Oracle 开放源代码项目

Oracle 开放源代码项目 这是无数个可扩展、使用以及构建于 Oracle 技术的开放源代码项目中的一个简短的示例。如果您有自己喜欢的开放源代码项目未在此处列...

8408
来自专栏数据库

存储过程和触发器的应用

实验案例三:创建视图 方法一:在图形界面下创建视图(以Myschool数据库为例) 创建一个视图,分别来自三个的表的三个列,并重命名列,生成的视图名为stude...

22210
来自专栏杨建荣的学习笔记

巧用flashback database实现灵活的数据切换(r5笔记第9天)

今天是DTCC第二天了,抽空去听了下,因为手头有一些活,听到一半只能赶回公司继续工作。 客户今天有一个需求,因为开发现在在生产环境中遇到了一些困难,需要在测试生...

3475
来自专栏DHUtoBUAA

Python读取SQLite文件数据

  近日在做项目时,意外听说有一种SQLite的数据库,相比自己之前使用的SQL Service甚是轻便,在对数据完整性、并发性要求不高的场景下可以尝试!   ...

5719
来自专栏杨建荣的学习笔记

MySQL 8.0初体验

从决定安装MySQL 8.0到开始行动,也就不到一个小时的时间,一个小时的时间能干些啥呢,来简单体验下8.0,官网上能看到这个丰富的表情包。

1504

扫码关注云+社区

领取腾讯云代金券