Mysql 和 Postgresql 对比

宏哥 发布于 2010/12/14 11:18
阅读 33K+
收藏 67

Mysql 使用太广泛了,以至于我不得不将一些应用从mysql 迁移到postgresql, 很多开源软件都是以Mysql 作为数据库标准,并且以Mysql 作为抽象基础的,但是具体使用过程中,发现Mysql 有很多问题,所以都迁移到postgresql上了,转一个Mysql 和Postgresql 对比的文章:

 

PostgreSQL由于是类似Oracle的多进程框架,所以能支持高并发的应用场景,这点与Oracle数据库很像,所以把Oracle DBA转到PostgreSQL数据库
上是比较容易的,毕竟PostgreSQL数据库与Oracle数据库很相似。
同时,PostgreSQL数据库的源代码要比MySQL数据库的源代码更容易读懂,如果团队的C语言能力比较强的知,就能在PostgreSQL数据库上做开发,比方说实现类似greenplum的系统,这样也能与现在的分布式趋势接轨。
 
为了说明PostgreSQL的功能,我下面简要对比一下PostgreSQL数据库与MySQL数据库之间的差异:
我们先借助Jametong翻译的"从Oracle迁移到Mysql之前必须知道的50件事",看一看如何把Oracle转到MySQL中的困难:
50 things to know before migrating Oracle to MySQL
by Baron Schwartz,Translated by Jametong
1. 对子查询的优化表现不佳.
2. 对复杂查询的处理较弱
3. 查询优化器不够成熟
4. 性能优化工具与度量信息不足
5. 审计功能相对较弱
6. 安全功能不成熟,甚至可以说很粗糙.没有用户组与角色的概念,没有回收权限的功能(仅仅可以授予权限).当一个用户从不同的主机/网络以同样地用户名/密码登录之后,可能被当作完全不同的用户来处理.没有类似于Oracle的内置的加密功能.
7. 身份验证功能是完全内置的.不支持LDAP,Active Directory以及其它类似的外部身份验证功能.
8. Mysql Cluster可能与你的想象有较大差异.
9. 存储过程与触发器的功能有限.
10. 垂直扩展性较弱.
11. 不支持MPP(大规模并行处理).
12. 支持SMP(对称多处理器),但是如果每个处理器超过4或8个核(core)时,Mysql的扩展性表现较差.
13. 对于时间、日期、间隔等时间类型没有秒以下级别的存储类型.
14. 可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱.
15. 没有基于回滚(roll-back)的恢复功能,只有前滚(roll-forward)的恢复功能.
16. 不支持快照功能.
17. 不支持数据库链(database link).有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多.
18. 数据完整性检查非常薄弱,即使是基本的完整性约束,也往往不能执行。
19. 优化查询语句执行计划的优化器提示非常少.
20. 只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join).
21. 大部分查询只能使用表上的单一索引;在某些情况下,会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢.
22. 不支持位图索引(bitmap index).每种存储引擎都支持不同类型的索引.大部分存储引擎都支持B-Tree索引.
23. 管理工具较少,功能也不够成熟.
24. 没有成熟能够令人满意的IDE工具与调试程序.可能不得不在文本编辑器中编写存储过程,并且通过往表(调试日志表)中插入记录的方式来做调试.
25. 每个表都可以使用一种不同的存储引擎.
26. 每个存储引擎在行为表现、特性以及功能上都可能有很大差异.
27. 大部分存储引擎都不支持外键.
28. 默认的存储引擎(MyISAM)不支持事务,并且很容易损坏.
29. 最先进最流行的存储引擎InnoDB由Oracle拥有.
30. 有些执行计划只支持特定的存储引擎.特定类型的Count查询,在这种存储引擎中执行很快,在另外一种存储引擎中可能会很慢.
31. 执行计划并不是全局共享的,,仅仅在连接内部是共享的.
32. 全文搜索功能有限, 只适用于非事务性存储引擎. Ditto用于地理信息系统/空间类型和查询.
33. 没有资源控制.一个完全未经授权的用户可以毫不费力地耗尽服务器的所有内存并使其崩溃,或者可以耗尽所有CPU资源.
34. 没有集成商业智能(business intelligence), OLAP **数据集等软件包.
35. 没有与Grid Control类似的工具( http://solutions.mysql.com/go.php?id=1296&t=s )
36. 没有类似于RAC的功能.如果你问”如何使用Mysql来构造RAC”,只能说你问错了问题.
37. 不支持用户自定义类型或域(domain).
38. 每个查询支持的连接的数量最大为61.
39. MySQL支持的SQL语法(ANSI SQL标准)的很小一部分.不支持递归查询、通用表表达式(Oracle的with 语句)或者窗口函数(分析函数).支持部分类似于Merge或者类似特性的SQL语法扩展,不过相对于Oracle来讲功能非常简单.
40. 不支持功能列(基于计算或者表达式的列,Oracle11g 开始支持计算列,以及早期版本就支持虚列(rownum,rowid)).
41. 不支持函数索引,只能在创建基于具体列的索引.
42. 不支持物化视图.
43. 不同的存储引擎之间,统计信息差别很大,并且所有的存储引擎支持的统计信息都只支持简单的基数(cardinality)与一定范围内的记录数(rows-in-a-range). 换句话说,数据分布统计信息是有限的.更新统计信息的机制也不多.
44. 没有内置的负载均衡与故障切换机制.
45. 复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.
46. Cluster并不如想象的那么完美.或许我已经提过这一点,但是这一点值得再说一遍.
47. 数据字典(INFORMATION_SCHEMA)功能很有限,并且访问速度很慢(在繁忙的系统上还很容易发生崩溃).
48. 不支持在线的Alter Table操作.
49. 不支持Sequence.
50. 类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务,并且不能回滚也不能做灾难恢复.Schame被保存在文件系统上,这一点与它使用的存储引擎无关.

PostgreSQL数据库可以解决以上问题中的:
1. 对子查询的优化表现不佳
2. 对复杂查询的处理较弱
3. 查询优化器不够成熟
PostgreSQL完全支持SQL-92标准,对SQL的支持也很全面,可以支持复杂的SQL查询。

4. 性能优化工具与度量信息不足
PostgreSQL提供了执行计划和详细的cost值,可以方便看到SQL的执行效率。

9. 存储过程与触发器的功能有限.
PostgreSQL提供了完善的存储过程和触发器支持。

11. 不支持MPP(大规模并行处理)
而PostgreSQL是类似Oracle数据库的架构,是多进程的架构,而不像MySQL是多线程的架构,所以能支持MPP。

18. 数据完整性检查非常薄弱,即使是基本的完整性约束,也往往不能执行。
PostgreSQL提供完善的数据完整性检查机制,支持外键。

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

21. 大部分查询只能使用表上的单一索引;在某些情况下,会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢.
PostgreSQL数据不存在这个问题,假设表T的两个字段col1的col2上有两个索引,idx_1和idx_2,那么select * from t where col1=:a and col2=:b;查询时,PostgreSQL数据库有可能把这个查询转化为select * from t where col1=:a intersect select * from t where col2=:b,这样两个索引都可以使用上。

25. 每个表都可以使用一种不同的存储引擎.
26. 每个存储引擎在行为表现、特性以及功能上都可能有很大差异.
27. 大部分存储引擎都不支持外键.
28. 默认的存储引擎(MyISAM)不支持事务,并且很容易损坏.
29. 最先进最流行的存储引擎InnoDB由Oracle拥有.
30. 有些执行计划只支持特定的存储引擎.特定类型的Count查询,在这种存储引擎中执行很快,在另外一种存储引擎中可能会很慢.
PostgreSQL只有一种存储引擎,所以不存在上面的情况。而PostgreSQL支持完善的事务。

32. 全文搜索功能有限, 只适用于非事务性存储引擎. Ditto用于地理信息系统/空间类型和查询.
PostgreSQL数据库支持全文搜索,支持更多类型的索引,如B-tree,R-tree, Hash, GiST, GIN,R-tree,GIST,GIN索引可用于空间类型和查询。

37. 不支持用户自定义类型或域(domain).
PostgreSQL支持丰富的类型,同时也支持自定义类型。

39. MySQL支持的SQL语法(ANSI SQL标准)的很小一部分.不支持递归查询、通用表表达式(Oracle的with 语句)或者窗口函数(分析函数).支持部分类似于Merge或者类似特性的SQL语法扩展,不过相对于Oracle来讲功能非常简单.
这些PostgreSQL数据库都支持,如窗口函数。

41. 不支持函数索引,只能在创建基于具体列的索引.
PostgreSQL支持函数索引

49. 不支持Sequence.
PostgreSQL支持sequence

50. 类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务,并且不能回滚也不能做灾难恢复.Schame被保存在文
件系统上,这一点与它使用的存储引擎无关.
PostgreSQL不存在这个问题。
加载中
1
ddatsh
ddatsh

MYSQL MyISAM 发生并发写的情况 它确实是由数据量大点的时候并发引起的吧,是否它就是应该通过 读写分离 去解决

 

另外 子查询 约束 这些 确实是非常基本的 导致了LZ的问题

而许多大站 用MYSQL场合那也是相当的多,这些个问题 他们肯定也碰到,想必肯定是有解决办法的

 

只是我们没有得到更专业的解答?

 

 

1
宏哥
宏哥

引用来自#8楼“dd”的帖子

感谢LZ列出了实际中出现的这些情况

对于你提到的有几个情况,从MYSQL技术上讲,不知是否可以对表使用不用的存储引擎来规避问题
比如 有的引擎执行时 的确要缓存数据文件的,比如Memory存储引擎表的容量设置,又或者中间结果含有TEXT或BLOB列类型字段,则MySQL数据库会把其转换 到MyISAM存储引擎表而存放到磁盘
MyISAM不缓存数据文件,因此这时产生的临时表的性能对于查询会有损失


增加索引,字段 之类 是否可以商量在晚上无业务操作时离线进行而不是在线,或者MYSQL中某种类型的存储引擎提供了比较好的在线定义数据结构?


财务数据处理的这问题,是要多个save point?  而迁移到了Postgresql?还是数据质量问题,程序写的严谨点就可以避免的吗?



刚才也仔细的搜了下Postgresql和MYSQL的对比
里面谈到的各项内容 我还需要深入的了解 实践才行


之前我也是想把手头的非常老的项目切换到 我本机TOMCAT 能运行,也费了很大费
好在本本内存也是4G了,装了ORACLE 玩玩还行

网上是说Postgresql貌似可以直接跑ORACLE的应用


对我来说Postgresql看见名字很久了,一直没装过  又有很多要去查的了 呵呵

有很多业务无法离线操作,比如Global 的系统。而且数据量大的时候,mysql 这个修改,需要一整天甚至更长的时间,这是无法接受的。在Oracle/Postgres,这个是在ms级别完成的。这个是因为mysql 设计的问题。

财务数据处理,因为 进行很多数据迭代计算,更新。做或者没有做,只能选择其一。如果出错,就不发生任何更改,是严格要求的。不能通过程序来保证。比如,更新一条数据,插入更新日志,你如何做到用程序来保证大量这样的操作进行恢复?

Mysql 的innodb事务实现的是,Read隔离级别,并没有数据版本信息的更新,批量迭代,会锁表。导致 多地区,多组织操作的发生互斥,这是非常糟糕的。

1
宏哥
宏哥

引用来自#16楼“dd”的帖子

另外有几方面的观点请教

引用一下

商业上选用BEA、Oracle、IBM等产品,并不是说他们有多优秀,而是抱着买保险的心态去对待的,领导不会关心你是用什么树去存储,他只关心出了故障找谁负责

打项目里数据库的钱是小钱,谁也不会这点小钱去负更多的责任

关于这个背锅服务的问题

LZ 是如何说服BOSS 使用Postgres的呢?就凭ORACLE 太贵?部署 维护 麻烦?

要是真碰到可能是 DB 本身的问题 怎么办?自己负更多的责任? 赔钱?

很多参数不是自动调优,使得很多懒的管理员觉得效率低

说服他们很简单啊,我举例一个 应用场景,告诉他们预计发生的情况。

比如不支持事务,我告诉他们风险在哪里,我不是就职于it公司,公司不关心你用什么技术,

只关心是否能达到目标,所以这个事情就简单了。公司的更大的应用全部是在oracle 上。

我说的财务系统这些,还仍然是外围系统。

BEA/IBM 的中间件,Tuxedo/CICS目前市场上的确没有可以竞争的产品。其实银行,电信这些客户,他们很了解自己的需求,并不简单因为 大公司的保证,基本上是以成功案例最佳实践做参照的。

上面提到Mysql 的问题,不是参数,性能的问题,是mysql 的根本设计(core)的部分无法支持。

Oracle 的数据库产品,在市场也是这个位置,少数几个寡头的确没有办法替代。他们的这些产品,是很优秀的。

Open source在中间件,数据库产品上,差距非常大。

1
宏哥
宏哥

引用来自#25楼“mark35”的帖子

引用来自#22楼“无知的 TonySeek”的帖子

pg真的很强大,不过查询性能真的不如mysql。但这个应该在特定领域不是问题,我想太多请求没有命中缓存而是命中数据库,即使是mysql也不给力的吧。所以如果缓存系统设计的好,pg和mysql的性能差距应该是可以忽略的。

pg没有自带连接池是个很大的乌龙,我之前一直不知道,后来装了pgbouncer,ab压力测试结果简直是翻倍的差距。

我想pg应该更适合数据严谨要求高的“事务”型系统,而mysql更适合类似oschina这样偏向常规查询而对事务严谨不苛刻的系统。

之前在其他论坛见到过  20楼 王树兵前辈 的帖子,很是受教,想不到在oschina又见到了,哈哈。

你所说的查询性能是指 select count(*)吧。如果没有限定条件那么mysql的确非常快,而pgsql是做全表扫描。pgsql解决办法也简单,就是做触发器即时更新表总row到某个表中,然后直接读那个表即可。另外,如果数据量很大并且带条件count,那么pgsql可能反而比mysql快。

Mysql count 是基于缓存的,相当于在查询之前,将对应的结果放在内存当中。

对查询密集的情况是Ok的,但是,如果你发生一次写入,你就会发现Mysql 查询速度非常慢,因为它的这个缓存过期了。

PG要做这样的缓存,在PHP里面,只是几行代码而已,太easy了,数据库不需要去做这个。

这是一个方面,只要发生写入,mysql 速度就非常慢。

第二个方面是,关联查询,在多表之间发生关联查询,Mysql 速度非常慢。除非两个都是唯一主键。

第三个方面,是死锁问题,任何的DDL都会造成锁表,比如在大表上增加一列,或者增加一个index,基本上完蛋。

mysql 需要将所有数据拷贝到一个temp空间,相当于整个表重建

用久了,用大了,就知道mysql 的苦头了。

如果要做统计,绝对Over.

目前基于mysql 的应用,都是基于冗余设计的扁平数据,以单表查询为主,并且是很少并发写的情况。

至于 数据库连接池,不大需要,对于部署的特定应用,在PHP fastcgi模式下,可以维持既定数量的连接,就是长连接模式。

宏哥
宏哥
@mark35 :"PHP缓存PG查询结果", 可以将整个查询语句进行hash,做一个key,置入 memcached,设定失效时间,就搞定了。很少的代码。
mark35
mark35
“PG要做这样的缓存,在PHP里面,只是几行代码而已,太easy了,数据库不需要去做这个。”请教下如何在PHP中实现这个要求?PHP是全初始化,脚本一执行完全部环境变量都销毁。在PHP脚本中缓存的数据如何实现缓存的效果呢。难道是把相关数据写到一个文本文件来实现?
0
ddatsh
ddatsh

转的文章不错 值得参考一下

Mysql 使用太广泛了,以至于我不得不将一些应用从mysql 迁移到postgresql

这个表示不解

0
jackie575
jackie575

LZ的意思可能是说mysql的应用太广泛了,以至于一些不适用的场合也使用到了,所以为了性能以及效率考虑,必须要忍痛迁移数据了?

0
宏哥
宏哥

引用来自#3楼“jackie575”的帖子

LZ的意思可能是说mysql的应用太广泛了,以至于一些不适用的场合也使用到了,所以为了性能以及效率考虑,必须要忍痛迁移数据了?

因为OLTP需要一致性和事务支持,mysql 这方面太弱了.这个应用开始的时候,选择mysql,没有想到有这么多问题。

所以把整个应用迁移到postgresql,不单单是数据。

性能方面,发生并发写的情况,mysql 非常糟糕。

还有一个无法理解的地方,是mysql 在大表建索引以及修改表结构的困难。都是很难理解的。

这些基本的东西都无法完成。

我最早开发是数据是用Oracle ,所以对oracle 很亲切。从某种意义上说,mysql 比较适合扁平数据,很难说是一个RDBMS.

0
ddatsh
ddatsh

我也对ORACLE很亲切

虽然对MYSQL不熟

但我个人表示,实在是我们没有深入,MYSQL 完全是比postgresql强大的

可以看看

《MySQL技术内幕:InnoDB存储引擎》

 

比如 里面提到的 innodb plugin

SSD硬盘时的优化。。。 等等  它100%是可以克服我们所碰到的问题的

mark35
mark35
之端末节的软硬优化无法解决先天的低能。你要说mysql强大,这没什么异议,因为它的确“强大”到占据互联网网站绝大部分的数据库份额。但如果要说“mysql完全是比pgsql强大的”,那真有点蚍蜉撼树了……
0
宏哥
宏哥

谢谢分享,

我是因为在实际应用当中遇到问题,无能为力,才放弃mysql.

其中一个应用场景是,有一个客户数据,我们要对它进行分析,SQL 一跑,就发生锁表,然后观察mysql 进程,发现mysql 在copy table to temp......

对这个表增加索引,表也锁住,增加字段,仍然锁住,后来只能把所有数据 ETL到oracle 里面去操作。

还有一个情况是,财务数据处理,从原始数据到结果需要多步过程,这个过程中间发生数据质量的问题,导致需要重新计算,就是需要回滚,也是么有在mysql上找到办法。

Postgresql 虽然没有Oracle 那么强大,基本上我需要的一些东西都在上面找到了,为了迁移到postgresql, 我花了不少时间吧postgresql 整个都熟悉了一遍,可以说是从0学习了一遍,在大多场景当中,可以替换Oracle

Oracle 的确很好,但是很多场景,价格太巨了。之前,我是专在Oracle 上做pl/sql 开发的。

0
宏哥
宏哥

MyISAM 在查询速度上略有优势,我们有个财务系统用这个引擎,结果每个月都要修复表,因为经常发生并发写的情况

Innodb因为没有数据版本,所以做批量的时候,仍然会发生锁表

这几个方面的问题,都没有找到解决的办法

返回顶部
顶部