面试题:PostgreSQL和MySQL区别

这个是面试的高频题,但是感觉大家面试的时候一直回答的不够全面,特地查了很多资料,写了一份总结,希望对大家有所帮助。


PG vs MySQL 的发展历史


MySQL

MySQL声称自己是最流行的开源数据库。LAMP中的M指的就是MySQL。构建在LAMP上的应用都会使用MySQL.

MySQL最初是由MySQL AB开发的,然后在2008年以10亿美金的价格卖给了Sun公司,Sun公司又在2010年被Oracle收购。Oracle收购导致MySQL的出现两个版本:商业版和社区版。对于后者,由于Oracle控制了MySQL的开发,受到了广大使用者的批评。

PostgreSQL

PostgreSQL标榜自己是世界上最先进的开源数据库。最初是1985年在加利福尼亚大学伯克利分校开发的,作为Ingres数据库的后继。PostgreSQL是完全由社区驱动的开源项目。

它提供了单个完整功能的版本,而不像MySQL那样提供了多个不同的社区版、商业版与企业版。PostgreSQL基于自由的BSD/MIT许可,组织可以使用、复制、修改和重新分发代码,只需要提供一个版权声明即可。

下面注意从几个方面分别比较,可能不够全面:

性能

PG在实际开发过程中性能要远优于MySQL
MySQL数据库性能调优选项比较有限,很多索引类型都不支持。写一个高效的SQL语句具有挑战性。对于大规模数据,MySQL也不是个很好的选择。表空间仅支持innodb,并且无法容纳表分区。

PG非常适合任何类型的负载:OLTP,OLAP,数据仓库等。由于支持的索引类型比较多,可以更好的提升性能。PG也有选项采集数据库内存使用,分区表可以放到不同表空间平衡IO。

存储

数据存储是数据库的一个关键能力。PG和MySQL都提供多种选项存储数据。

PG有一个通用的存储特性:表空间能够容纳表、索引、物化视图等物理对象。通过表空间,可以将对象进行分组并存储到不同物理位置,可以提升IO能力。PG12之前版本,不支持可拔插存储,12只支持可拔插架构。

MySQL和PG类似,未来具有表空间特性。他支持可拔插存储引擎。这是MySQL的一个优点。

支持的数据模型

关系型数据库的NoSQL能力能够帮助处理非结构化的数据,例如json、xml、text等。

MySQL的NoSQL能力比较有限。5.7引入了json数据类型,需要很长时间才能变得更加成熟。

PG具有丰富的json能力,未来3年内是需要NoSQL能力的开发者的一个很好的选择。Json和jsonb数据类型,使得PG对json操作更快更有效。同样可以在json数据列上建立B-tree索引和GIN索引。XML和HSTORE数据类型可以处理XML格式以及其他复杂text格式的数据。对空间数据类型的支持,使得PG是一个完整的多模型数据库。

存储过程

MySQL和PG都支持存储过程,但MySQL仅支持标准的SQL语法,而PG支持非常先进的存储过程。PG以带RETURN VOID子句的函数形式完成存储过程。PG支持的语言有很多:Ruby、Perl、Python、TCL、PL/pgSQL、SQL和JavaScript。而MySQL则没有这么多。

总结

  1. PostgreSQL相对于MySQL的优势

    在SQL的标准实现上要比MySQL完善,而且功能实现比较严谨,比较学院化;

    存储过程的功能支持要比MySQL好,具备本地缓存执行计划的能力;

    对表连接支持较完整,优化器的功能较完整,支持的索引类型很多,复杂查询能力较强;

    PG主表采用堆表存放,MySQL采用索引组织表,能够支持比MySQL更大的数据量。

    PG的主备复制属于物理复制,相对于MySQL基于binlog的逻辑复制,数据的一致性更加可靠,复制性能更高,对主机性能的影响也更小。

    MySQL的存储引擎插件化机制,存在锁机制复杂影响并发的问题,而PG不存在。

    MySQL 里需要 utf8mb4 才能显示 emoji , Pg 就没这个问题

    MySQL 的事务隔离级别 repeatable read 并不能阻止常见的并发更新, 得加锁才可以, 但悲观锁会影响性能, 手动实现乐观锁又复杂. 而 Pg 的列里有隐藏的乐观锁 version 字段, 默认的 repeatable read 级别就能保证并发更新的正确性, 并且又有乐观锁的性能.

    PG有 几何类型,大量字典、数组、bitmap 等数据类型,相比之下mysql就差很多,instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。

  2. MySQL相对于PG的优势:

    innodb的基于回滚段实现的MVCC机制,相对PG新老数据一起存放的基于XID的MVCC机制,是占优的。新老数据一起存放,需要定时触 发VACUUM,会带来多余的IO和数据库对象加锁开销,引起数据库整体的并发能力下降。而且VACUUM清理不及时,还可能会引发数据膨胀;

    MySQL采用索引组织表,这种存储方式非常适合基于主键匹配的查询、删改操作,但是对表结构设计存在约束;
    MySQL的优化器较简单,系统表、运算符、数据类型的实现都很精简,非常适合简单的查询操作;

    MySQL分区表的实现要优于PG的基于继承表的分区实现,主要体现在分区个数达到上千上万后的处理性能差异较大。

    MySQL的存储引擎插件化机制,使得它的应用场景更加广泛,比如除了innodb适合事务处理场景外,myisam适合静态数据的查询场景。

    MySQL的复制可以用多级从库,但是在9.2之前,PGSQL不能用从库带从库。

    mysql的innodb引擎,可以充分优化利用系统所有内存,超大内存下PG对内存使用的不那么充分,


参考:

https://cloud.tencent.com/developer/article/1796238
https://blog.csdn.net/jiangheng0535/article/details/17217223
https://cloud.tencent.com/developer/article/1576728
https://cloud.tencent.com/developer/article/1576728
https://www.zhihu.com/question/20010554