Postgresql与MySQL的区别

最新推荐文章于 2024-07-18 14:40:43 发布

weixin_34112208

最新推荐文章于 2024-07-18 14:40:43 发布

阅读量309

点赞数

文章标签：数据库 json python

原文链接：https://juejin.im/post/5c8f60b66fb9a070e46229bd

版权

在学习具体的Postgresql用法之前，我们来思考一下Postgresql与目前最常用的关系型数据库MySQL的区别在哪里，主要集中在两者的底层结构、使用场景和特别之处。

1.前言

当我们去选择项目使用的数据库的时候，应该要考虑该项目的应用场景、数据规模等因素。每一种数据库都有它特定的应用场景，比如我们现在要讨论的Postgresql和MySQL数据库，这两种的数据库的应用场景就有所不同，下面我们就它们的相同点和不同点进行讨论。

2.Postgresql vs MySQL

此表来源于postgresqltutorial，详细区别了两者的不同点。

特性	Postgresql	MySQL
描述	The world’s most advanced open source database	The world’s most popular open source database
发展	PostgreSQL is an open source project	MySQL is an open-source product
实现语言	C	C、C++
图形化工具	PgAdmin	MySQL Workbench
ACID	Yes	Yes
存储引擎	Single storage engine	Multiple storage engines e.g., InnoDB and MyISAM
全文检索	Yes	Yes
Drop a temporary table（删除一个临时表）	No `TEMP` or `TEMPORARY` keyword in `DROP TABLE` statement（随着数据库的连接的断开而被删除）	MySQL supports the `TEMP` or `TEMPORARY`keyword in the `DROP TABLE` statement that allows you to remove the temporary table only.（需要手动删除）
`DROP TABLE`（删除表）	Support `CASCADE` option to drop table’s dependent objects e.g., tables, views, etc.,（级联操作：也就是更新、删除父表，将会同步更新、删除子表；而反过来则不变）	Does not support `CASCADE` option
`TRUNCATE TABLE`（删除表）	PostgreSQL `TRUNCATE TABLE`supports more features like `CASCADE`, `RESTART IDENTITY`, `CONTINUE IDENTITY`, transaction-safe, etc.（对于移除表中的数据，delete是可以的，但是对于一个大表，truncate是更加有效的方式，因为truncate删除表中所有行的时候不需要扫表整个表）	MySQL `TRUNCATE TABLE` does not support `CASCADE` and transaction safe i.e,. once data is deleted, it cannot be rolled back.（永久性删除，不可以撤销）
自动增加列	`SERIAL`	`AUTO_INCREMENT`
解析功能	Yes	No
Data types	Support many advanced types such as array, hstore, and user-defined type.	SQL-standard types
Unsigned integer	No	Yes
Boolean type	Yes	Use `TINYINT(1)` internally for Boolean
IP address data type	Yes	No
设置列默认值	Support both constant and function call	Must be a constant or `CURRENT_TIMESTAMP` for `TIMESTAMP` or `DATETIME` columns
CTE（Common Table Expressions）	Yes	No
`EXPLAIN`output	More detailed	Less detailed
Materialized views（物化视图）	Yes（Postgresql将视图概念扩展到下一个级别，允许视图在物理上存储数据，我们将这些视图称为物化视图，物化视图会缓存复杂的查询结果，然后允许定期刷新此结果）	No
CHECK constraint（检查约束）	Yes	No (MySQL ignores the CHECK constraint)
Table inheritance（表继承）	Yes	No
Programming languages for stored procedures	Ruby, Perl, Python, TCL, PL/pgSQL, SQL, JavaScript, etc.	SQL:2003 syntax for stored procedures
`FULL OUTER JOIN`（全外连接）	Yes	No
`INTERSECT`	Yes（Postgresql的INTERSECT运算符将两个或多个SELECT语句的结果集合并到一个结果集中）	No
`EXCEPT`	Yes（Except运算符通过比较两个或多个quires的结果集来返回行，此返回行存在于第一查询子句而不存在第二查询子句中）	No
Partial indexes（部分索引）	Yes	No
Bitmap indexes（位图索引）	Yes	No
Expression indexes（表达式索引）	Yes	NO
Covering indexes（覆盖索引）	Yes (since version 9.2)例子1、例子2	Yes. MySQL supports covering indexes that allow data to be retrieved by scanning the index alone without touching the table data. This is advantageous in case of large tables with millions of rows.
Common table expression (CTE)	Yes	Yes. (since version 8.0, MySQL has supported CTE)
Triggers（触发器）	Support triggers that can fire on most types of command, except for ones affecting the database globally e.g., roles and tablespaces.	Limited to some commands
Partitioning（分区）	RANGE, LIST	RANGE, LIST, HASH, KEY, and composite partitioning using a combination of RANGE or LIST with HASH or KEY subpartitions
Task Schedule（任务定时）	pgAgent	Scheduled event
Connection Scalability（连接规模）	Each new connection is an OS process（进程）	Each new connection is an OS thread（线程）
SQL compliant（SQL兼容性）	PostgreSQL is largely SQL compliant.	MySQL is partially SQL compliant. For example, it does not support check constraint.
Best suited	PostgreSQL performance is utilized when executing complex queries.	MySQL performs well in OLAP& OLTP systems when only read speeds are needed.
Support for JSON	Support JSON and other NoSQL features like native XML support. It also allows indexing JSON data for faster access.	MySQL has a JSON data type support but does not support any other NoSQL feature.
Default values	The default values can be changed at the system level only	The default values can be overwritten at the session level and the statement level
B-tree Indexes	B-tree indexes merged at runtime to evaluate are dynamically converted predicates.	Two or more B-tree indexes can be used when it is appropriate.
Object statistics	Very good object statistics	Fairly good object statistics

对于上面有部分疑惑的点进行详细分析：

1、Full outer join全外连接

作用：Postgresql full outer join返回来自两个参与表的所有行，如果他们在相对的表上没有匹配，则使用null填充。full outer join组合了左外链接和右外连接的结果，并返回连接子句两侧表中的所有行（匹配或者不匹配）行。

3.Disadvantages of using MySQL（MySQL的缺点）

Transactions related to system catalog are not ACID compliant（与系统目录相关的事务不符合ACID）
Some time A server crash can corrupt the system catalog（有时服务器崩溃可能会破坏系统目录）
No pluggable authentication module preventing centrally managed account（没有可插入的身份验证模块阻止集中管理账户）
No support for roles so it is difficult in maintaining privileges for many users（不支持角色，因此很难为许多用户维护权限）
Stored procedures are not cacheable（存储过程不可缓存）
Tables used for the procedure or trigger are always pre-locked（用于程序或触发器的表始终是预先锁定的）

4.Disadvantages of using PostgreSQL（Postgresql的缺点）

The current external solutions require a high learning curve（当前的外部解决方案需要很高的学习曲线）
No upgrade facility for major releases（没有主要版本的升级工具）
The data need to be exported or replicated to the new version（需要将数据导出或复制到新版本）
Double storage is needed during the upgrade process（升级过程中需要双重存储）
indexes cannot be used to directly return the results of a query（索引不能用于直接返回查询结果）
Query execution plans are not cached（不缓存查询执行计划）
Bulk loading operations may become CPU bound（批量加载操作可能会受CPU限制）
Sparse Independent Software Vendor support（稀疏独立软件供应商支持）

5.What is Better?

After comparing both we can say that MySQL has done a great job of improving itself to keep relevant, but on the other side for PostgreSQL, you don't need any licensing. It also offers table inheritance, rules systems, custom data types, and database events. So, it certainly edges above MySQL.