MySQL和PostgreSQL的比较

  • 时间:
  • 浏览:0
  • 来源:大发3D_大发3D官方

MySQL

MySQL分商业版和社区版,社区版为GPL许可,允许免费使用,因此不可能 我需要 分类分类整理你的代码,让他选折 开源,不可能 闭源,不可能 选折 闭源则需要向ORACLE支付费用。

Domains

在标准类型上施加约束,长度限制的领域类型 使用例:

MySQL多引擎的缺点

真是这人项PostgreSQL也支持,但PostgreSQL不允许Drop被视图引用的列和修改列定义。 对其它类型的表修改,视图仍然有效。

User defined datatypes

注:MySQL也支持FDW,但目前必须mysql的FDW,完整版都是主流。

MySQL显然更加流行,有观点认为,PostgreSQL之所远不如MySQL流行,是某些因素同去作用的结果。

SELECT .. FOR UPDATE NOWAIT 

查询并锁住行用于将来的更新,当必须获得锁时报错而完整版都是停留。

不可能 Drop被视图引用的列,PostgreSQL会报错并提示使用DROP ... CASCADE。

Use target table in sub-queries 

在1个DML中允许使用目标表多次。 使用例:删除表蕴藏重复值的行

但PostgreSQL凭借其丰富的形状和日益提升的性能,用户量正呈现出快速上升的势头。因此在PostgreSQL接受程度较高的日本市场,PostgreSQL的用户量和MySQL相当,甚至超过。

PostgreSQL的MVCC实现土法子的缺点

MySQL的二级索引中存储的行位置完整版都是Innodb表中的物理位置却说Innodb表中的主键值。却说 MySQL通过二级索引查找记录需要执行两次索引查找。因此不可能 主键太长,会太满占用二级索引的存储空间,却说 某些场景下,放弃自然主键而采用额外的自增字段作为主键效果会更好。

Writeable CTEs

common table expression可以 使用1个或多个DML

MySQL早期的定位是轻量级数据库,虽因此来做了却说 增强,比如事务支持,存储过程等,但和其它常见的关系数据库比起来SQL形状的支持仍比较弱,目前宽泛的SQL 99的子集。 PostgreSQL的定位是高级的对象关系数据库,从一开使对SQL标准的支持比较全面,目前支持大要素的SQL:2011形状。

Descending Index 

指定索引的排序顺序。对多列分别按不同顺序排序的场景有点有用。

PostgreSQL 

有独立的轻量级连接池pgbouncer,已有小量用于生产的案例。

PostgreSQL和其它常见数据库(比如Oracle)不一样的地方在于,它必须redo log(PostgreSQL中称之为WAL)没办法 undo log。数据趋于稳定变更时,PostgreSQL通过记录在堆表的每个行版本的事务id,识别被更新或删除的旧数据。不可能 旧数据就在保趋于稳定堆表中,需要undo log就可以 支持事务的回滚。 PostgreSQL的MVCC实现土法子的优点

参考PostgreSQL官方手册17.6. Upgrading a PostgreSQL Cluster

Tuple updates 

使用例:

http://db-engines.com/en/ranking/

使用例: create type address as ( zip_code varchar(5), city varchar(1000), street varchar(1000) );

MySQL是线程模型,PostgreSQL是线程模型。

SELECT without a FROM clause 

不带where条件时,MySQL是支持的。

PostgreSQL 

有查询缓存插件,使用案例似乎太满见。

http://db-engines.com/en/ranking_trend/system/Oracle;MySQL;Microsoft%20SQL%20Server;Mongodb;PostgreSQL

Clustered index

PostgreSQL真是不支持集蔟索引,但提供了cluster命令要能达到类似于的效果。

PostgreSQL 

索引类型支持btree,hash,gist,sp-gist,gin,brin。已实现的索引算法包括R树,四叉树,基数树等。索引是可扩展的,支持要素索引,支持降序索引。

Read consistency during DML operations 

在1个表的DML中,所有读应该读到该DML日后的值。下面的例子是交换1个列的值。

PostgreSQL

PostsgreSQL是类BSD许可,允许用户以任何目的免费使用。

MySQL

MySQL由Oracle主导其开发,开发过程不须透明Oracle开发团队以外的人可通过MySQL Bug系统提交Patch,Oracle开发团队会吸收或改写Patch。另外,代码贡献者需先回应 Oracle Contribution Agreement (OCA)才允许提交Patch。

PostgreSQL流克隆qq的优点

PostgreSQL中存储元数据的系统表和普通表的存储格式完整版相同,这使得PostgreSQL很容易扩展,因此PostgreSQL的DDL操作也支持事务。

MySQL 

自带查询缓存,依赖的表更新时自动失效。每次读查询缓存都需要获取锁,却说 场景下反而拖累了性能。适用场景有限,多建议禁用。

Deferred constraints

定义约束在提交阶段而完整版都是语句执行进行检查。对于外键约束,这可以 使插入删除语句不须考虑执行的顺序。

MySQL

索引类型支持Btree,hash,空间索引,全文检索。自动hash索引(某些场合性能不升反降)。Change Buffering优化索引更新。

Dynamic SQL in triggers 

在触发器函数中使用动态SQL

MySQL的数据库名直接对应于文件系统的目录名,表名直接对应于文件系统的文件名。这是因为MySQL的数据库名和表名支持的字符以及算不算大小写敏感都依赖于文件系统。 MySQL的表定义存储在有点的.frm文件中,DDL操作不支持事务。 MySQL的所有Innodb表数据可以 存储在单个.idb文件中,也可以 每个表1个.idb文件(通过参数innodb_file_per_table控制),即使每个表1个.idb文件,同1个表的数据和索引完整版都是这人个文件里。 MySQL的Innodb表的存储格式是Btree索引组织表,每个表需要有主键,不可能 创建表时没办法 指定主键,MySQL会创建1个结构主键。索引组织表的优点是按主键查询快,但数据插入时不可能 主键完整版都是递增的,会是因为Btree树小量分裂影响性能。

Use a temporary twice in a single query 

MySQL中在单个查询中使用临时表2次会报错

RETURNING clause in a programming language 

RETURNING子句可以 返回DML的结果

升级土法子

主版本升级(形状增强)

客户端到数据库的连接通常使用连接池,因此数据库的最大连接数通常会被限制在10000以内,这可以 大大缓解线程创建删除和切打上去本高的问题报告 图片。却说 PostgreSQL的线程模型更占优势。(需要注意的是,不可能 PostgreSQL的线程模型,在Windows上的性能受到了一定的影响。)

User defined operators 

创建用户自定义操作符(主要为自定义数据类型)。

MySQL 

企业版支持线程,社区版不支持。但Percona Server 5.6以上也支持线程,可以 做到语句级的连接复用。

参考MySQL官方手册2.11.1 Upgrading MySQL

小版本升级(BugFix)

替换新版本数据库软件后重启PostgreSQL服务

MySQL 

1.explain不易理解,trace信息过细。 

2.统计信息过高 (必须总记录数和索引列的唯一值数,尚未GA的MySQL8.0支持直方图) 

3.只支持nest loop join 

4.复杂性查询性能不佳

Triggers on views 

在视图上创建触发器

PostgreSQL 

1.explain易于理解(树状显示,cost,实际执行时间,buffer和IO使用) 

2.统计信息丰富(直方图分布统计,频繁值统计,顺序性等) 

3.支持nest loop,merged sort和hash join 

4.复杂性查询表现优异

下面是MySQL和PostgreSQL的SQL形状支持差异的说明

PostgreSQL可以 通过CASCADE联级TRUNCATE。

Views with derived tables 

视图蕴藏有派生表。MySQL中总出 这人情況会报错

MySQL不支持跨发布系列升级,新旧版本跨太满个发布系列时,需要依次升级。比如不可能 想从5.5升级到5.7,需要先升级到5.6再升级到5.7。

MySQL的版本系列又分为开发版和General Availability (GA)版,必须GA版适合用于生产环境。

Query variables

需要定义存储过程,直接在单个语句中定义变量。仅SQL Server和MySQL支持这人形状。 

使用例:

主版本发布大概1年1次,维护周期为5年,小版本升级大概1个月1次。最近的哪几个主版本发布时间如下

MySQL在WEB类业务上非常流行,尤其在互联网行业几乎是标配。完整版都是用于其它业务的案例。 可参考:http://www.mysql.com/customers/

PostgreSQL对长度大于127字节的字段值采用被称为TOAST (The Oversized-Attribute Storage Technique)的技术进行存储。即将哪些地方地方大字段切分为若干个chunk存储在这人堆表对应的toast表中,每个chunk占一行,最大10000字节,原始表中仅仅存储1个指针。因此PostgreSQL不可能 会对长度大于127字节的数据自动进行压缩。得益于TOAST技术,PostgreSQL要能很好的解决蕴藏大字段的表。

Statement level triggers 

每个语句触发的触发器。MySQL只支持记录级别的触发器。

Check constraints

使用例:不允许薪水为负数

PostgreSQL中的数据库对应于文件系统的目录,表对应于文件系统的文件,但目录名和文件名完整版都是PostgreSQL结构的id号,不趋于稳定非法字符和大小写的问题报告 图片。 PostgreSQL的每个表的数据和每个索引都存储在单独的文件中,因此文件操过1GB时,每个GB再拆分为1个单独的文件。

PostgreSQL的版本号由3组数字组成,比如9.5.1。其中前1个数字构成了主版本号,第两个数字代表小版本号。小版本发布通常却说Bugfix,因此不须改变结构存储格式因此老要与日后的相同主版本的发布版本兼容。

Substring 

基于正则表达式抽取要素字符串

PostgreSQL

PostgreSQL必须1个开源主分支,代码托管在git.postgresql.org。另外,有某些公司发布了基于PostgreSQL增强的企业版产品,比如EnterpriseDB公司的EDB? Postgres Advanced Server,富士通的Fujitsu Enterprise Postgres等。哪些地方地方产品完整版都是闭源的商业产品,不属于开源软件的范畴。

Delete triggers fired by cascading deletes 

不可能 ON DELETE CASCADE的外键是因为的级联删除也触发删除触发器

MySQL的版本号由3组数字组成,比如5.7.1,每个数字的含义如下

PostgreSQL在各个行业均有应用案例。国内的知名用户包括:阿里巴巴,腾讯, 平安,移动,华为, 中兴,去哪儿, 斯凯, 云游, 顺丰,高德,探探等。某些用户看中了PostgreSQL的稳定可靠,复杂性查询和GIS解决能力,有点是GiS,从某些实际用户的选型和反馈来看,PostgreSQL+PostGiS被认为是目前最有优势的开源GiS数据库方案。 可参考:http://www.postgresql.org/about/users/

Filtered aggregates 

使用例:

Table functions 

PostgreSQL中函数的返回值可以 是表 使用例:生成序列的函数

PostgreSQL

由来自世界不同公司的开发者组成的PostgreSQL社区主导PostgreSQL的开发,PostgreSQL社区的组织架构参考Contributor Profiles。开发过程完整版开发,开发版的代码完整版公开。任何人随时可以 通过pgsql-hackers邮件列表提交Patch。Patch不可能 会被commiter直接提交到源码树,却说可能 被要求加入到Commitfests以接受Review。通过Commitfests可以 了解当前开发版蕴藏有的每个形状以及它们的进度。任何人不仅可以 提交Patch,甚至还可以 review Commitfests上的Patch。提交PostgreSQL Patch的完整版请参考Submitting a Patch

另外发现,MySQL创建外键有个BUG,下面的语句表面上成功了,但实际上并没办法 创建FK。

MySQL支持而PostgreSQL不支持的形状有两个

对以上数据,不可能 采用row by row的约束检查,下面的delete操作将失败,但在语句的最后评估则会成功。 delete from fk_test where id in (2,3,4);

Index on expression 

创建基于表达式或函数的索引。 使用例:

Function overloading 

创建多个函数名相同但参数不同的函数。

更有意义的例子请参考:http://www.sql-workbench.net/comparison/recursive_queries.html

Check constraints using custom functions 

使用定制函数实施Check约束 使用例:

正确的返回值是2,1。其它数据库的结果完整版都是正确的唯独MySQL返回2,2。

Index using a custom function 

使用定制函数创建索引

MySQL的SQL层和存储层分离,支持多种存储引擎,类似于InnoDB, MyISAM, NDB。PostgreSQL和绝大多数数据库一样是单存储引擎,因此PostgreSQL可以 通过FDW支持其它的储存形式,比如csv,mysql,hadoop等。

Statement based constraint evaluation 

基于语句的约束评估 使用例:

真是MySQL通过Group Commit的优化土法子可以 在高并发时大大减少了刷盘的次数,但双1的配置对性能的影响仍然趋于稳定。却说 不少生产系统并未设置双1,冒了主备数据不一致的风险。

Add table column at specific position

增加列到指定的位置而完整版都是打上去到最后,这会影响"select *"输出的列顺序。但MySQL实现土法子需要拷贝表,因此修改大表的定义很耗资源。

Partial index 

允许索引表的子集而完整版都是整个表 使用例:在所有活动项目中项目名需要唯一

Built-in scheduler

PostgreSQL的pgagent插件可以 达到相同的效果,因此PostgreSQL提供了章 45. 后台工作线程机制,可以 比较容易的定制调度任务。

OVERLAPS 检查时间间隔的重叠

以上翻译自MySQL的官方手册2.1.1 Which MySQL Version and Distribution to Install。

DDL Triggers 

PostgreSQL支持下列时点的触发器

MySQL

MySQL分支较多 ,除了Oracle官方的版本,比较流行的还有Percona Server和MariaDB等。

BETWEEN SYMMETRIC 

允许BETWEEN的上下边界是任意顺序

关于SQL形状支持情況的对比,可以 参考http://www.sql-workbench.net/dbms_comparison.html

Dynamic SQL in functions 

在函数中使用动态SQL 使用例:

Recursive Queries 

Common Table Expressions的这人特殊语法,可用于查询继承数据,比如列出一名员工所有的直接和间接上级。 使用例:计算1到1000的和(递归)

MySQL在SQL层通过binlog记录数据变更并基于binlog实现克隆qq和PITR。另外在Innodb存储层有和其它数据库类似于的redo log和undo log以支持事务的ACID。不可能 MySQL有两套日志,为了确保严格的持久性,以及保证1个日志中事务顺序的一致,每次事务提交需要刷3次盘,严重影响性能。这却说所谓的双1问题报告 图片(sync_binlog=1,innodb_flush_log_at_trx_commit=1)

输出结果:

PostgreSQL流克隆qq的缺点

Row constructor 

使用例:

MySQL多引擎的优点

Arrays

Window functions 

SQL10003引入的形状,允许针对每一行计算分组值而整个分组必须1个值。 使用例:计算每我个人 在全公司的薪水排名和在其所在部门的薪水排名

PostgreSQL支持而MySQL不支持的形状有61个,如下。

MySQL不可能 非常流行,国内相关技术书籍和资料非常多。但缺少较新的中文手册,目前可以 找到的最新的MySQL中文手册是MySQL 5.1。

Custom aggregates 

使用SQL自定义聚集函数

ALTER a table used in a view 

修改被视图使用的表定义而需要DROP视图。因此这不可能 会是因为视图失效。

TRUNCATE table with FK 

MySQL不支持TRUNCATE有外键引用的表。

MySQL的目标是每18~21个月发布1个新发布系列的GA版本,最近哪几个发布系列的第1个GA版本的发布时间如下。

升级土法子

* In-place升级: 停机,替换MySQL二进制文件,重启MySQL,运行mysql_upgrade。 

* 逻辑升级:mysqldump导出数据,安装新版MySQL,加载数据到新版MySQL,运行mysql_upgrade。

Common Table Expressions 

SQL标准中的形状。很像子查询,不同的是可以 在1个查询中被使用多次,因此复杂性SQL的书写。 使用例:

Exclusion constraints 

使用例:会议室预约,不允许同一会议的预约时间总出 重叠。

PostgreSQL FDW的优点

Replace 

基于正则表达式替换字符串

MySQL和PostgreSQL是目前主流的1个开源关系数据库,同样完整版都是开源产品,亲们该如保选型呢?MySQL长期以来被认为是更加快速但支持的形状较少;而PostgreSQL则提供了丰富的形状老要被描述为开源版的Oracle。MySQL不可能 不可能 它的快速和易用变得非常流行,但PostgreSQL正得到太满来自Oracel或SQL Server背景的开发人员的追从。 因此却说 假设不可能 变得过时不可能 不正确了,MySQL不可能 加入了却说 高级形状,PostgreSQL也大大提高了它的时延。本文针对最新的MySQL 5.7 and PostgreSQL 9.5进行对比。

MySQL 5.7中可以 使用虚拟列达到相同的效果

PostgreSQL FDW的缺点

TRUNCATE Trigger

binlog克隆qq的优点

MySQL的克隆qq传输的是SQL层的binlog记录,binlog记录的是数据的逻辑变更(SQL语句或基于行的数据变更),属于逻辑克隆qq;PostgreSQL的克隆qq传输的是WAL记录,WAL记录的是数据块的变更,属于物理克隆qq。

PostgreSQL的主版本更新的周期更短,平均一年一次;而MySQL的主版本升级平均2年一次。PostgreSQL小版本升级却说Bugfix不不增加新形状;而MySQL的小版本更新会增加和修改功能;因此,PostgreSQL提供的升级工具支持跨主版本升级,MySQL不可以 。因此PostgreSQL的版本升级风险更低也更加容易实施。

RETURNING clause as a result set 

使用例:

binlog克隆qq的缺点

早年PostgreSQL相关的中文技术资料和书籍过高 ,但目前不可能 比较丰富,因此每年完整版都是新的中文PostgreSQL书籍诞生,PostgreSQL相关的技术交流和分享也很活跃。另外,PostgreSQL官方手册相当精良无论想入门还是深入均可受益,因此PostgreSQL中国用户会还在其官网上提供了中文版的手册。

参考: