postgresql和mysql的区别

特性MySQLPostgreSQL

实例通过执行 MySQL 命令（mysqld）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。

通过执行 Postmaster 进程（pg_ctl）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。一台机器上可以启动多个实例。

数据库数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。

数据缓冲区通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。Shared_buffers 缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。

数据库连接客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。

身份验证MySQL 在数据库级管理身份验证。 基本只支持密码认证。PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证

加密可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。可以使用 pgcrypto 库中的函数对列进行加密/解密。可以通过 SSL 连接实现网络加密。

审计可以对 querylog 执行 grep。可以在表上使用 PL/pgSQL 触发器来进行审计。

查询解释使用 EXPLAIN 命令查看查询的解释计划。使用 EXPLAIN 命令查看查询的解释计划。

备份、恢复和日志InnoDB 使用写前（write-ahead）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。需要第三方软件才能支持热备份。在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。

JDBC 驱动程序可以从 参考资料 下载 JDBC 驱动程序。可以从 参考资料 下载 JDBC 驱动程序。

表类型取决于存储引擎。例如，NDB 存储引擎支持分区表，内存引擎支持内存表。支持临时表、常规表以及范围和列表类型的分区表。不支持哈希分区表。 由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。

索引类型取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。支持 B-树、哈希、R-树和 Gist 索引。

约束支持主键、外键、惟一和非空约束。对检查约束进行解析，但是不强制实施。支持主键、外键、惟一、非空和检查约束。

存储过程和用户定义函数支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

触发器支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。

系统配置文件my.confPostgresql.conf

数据库配置my.confPostgresql.conf

客户机连接文件my.confpg_hba.conf

XML 支持有限的 XML 支持。有限的 XML 支持。

数据访问和管理服务器OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片

myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎）

mysql —— 命令行工具

MySQL Administrator —— 客户机 GUI 工具Vacuum —— 回收未使用的空间

Analyze —— 更新查询优化器所使用的统计数据

psql —— 命令行工具

pgAdmin —— 客户机 GUI 工具

并发控制支持表级和行级锁。InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。支持表级和行级锁。支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。使用 SET TRANSACTION 语句在事务级设置隔离级别。使用 SET SESSION 在会话级进行设置。

MySQL相对于PostgreSQL的劣势：

MySQL

PostgreSQL

最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。

BSD协议，没有被大公司垄断。

对复杂查询的处理较弱，查询优化器不够成熟

很强大的查询优化器，支持很复杂的查询处理。

只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。

都支持

MYSQL里面没有限制文件大小的语句，但是事实上你的文件是有大小限制的－－受 *** 作系统的限制，比如32位 *** 作系统单个文件有2G大小的限制。

你无法插入数据，要看具体的情况，除了上面说的文件大小超过2G，主要有一下两种可能：

一是插入的数据在唯一索引或者主键字段上有重复。看看表有那些索引，如果插入数据和以前的有重复当然就会失败，删除以前的数据当然就能插入了。这种情况下，插入数据的时候会报告相应的错误，错误详细信息有说明是哪个字段上的所有有重复。

二是数据表损坏，特别是假如你说你的表根本没有索引的时候，那么损坏的可能性就相当的大。这种情况下数据表可能是只读状态，也甚至可能是根本无法打开，插入失败的时候可能会报告数据表被别的进程占用。处理的方法是用MYSQL的系统工具进行修复，比如MYISAMCHK。

快照读 即： snapshot read ，官方叫法是： Consistent Nonlocking Reads ，即： 一致性非锁定读 ，官方的解释是：

即：

即 快照读 的问题在于：在同一个事务中，能够读取到之前提交的数据。表现为：

字面意思：在事务中，为查询创建的快照，并不适用与 DML 语句。

也就是说：如果事务 A 开始时创建的快照，查询不到数据 col1=1 ，但此时事务 B 刚刚提交 insert col1=1 和 insert col1=1 ，此时如果事务 A 执行， delete col1=1 ，是能将事务 B 生成的数据删除的。

字面意思：即使事务 A 的快照是在事务 B 提交之前创建的，但事务 A 也只有在事务 A 和事务 B 都提交后，才能看到事务 B 新增的数据。

---