面试题: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/1796238https://blog.csdn.net/jiangheng0535/article/details/17217223https://cloud.tencent.com/developer/article/1576728https://cloud.tencent.com/developer/article/1576728https://www.zhihu.com/question/20010554