Postgres 9.1与Mysql 5.6 InnoDB? [英] Postgres 9.1 vs Mysql 5.6 InnoDB?

问题描述

一个简单的问题-对于要求在2012年与ACID兼容的中型/大型数据库，哪个会更好?

Simple question - what would better for a medium/big size database with requirement for compatibility with ACID in 2012.

我已经读完了所有关于MySQL和pgSQL的文章(大部分)，但是大多数这些文章分别与版本4,5.1和7,8有关，并且都过时了(2008,2009).现在已经快到2012年了，所以我想我们可以尝试重新审视这个问题.

I have read it all (well most) about mySQL vs pgSQL but most of those posts relate to version 4,5.1 and 7,8 respectively and are quite dated (2008,2009). Its almost 2012 now so I guess we could try and take a fresh look at the issue.

基本上，我想知道PostgreSQL中是否有任何东西超过了MySQL的易用性，可用性和更大的MySQL开发人员/知识基础.

Basically I would like to know if there is anything in PostgreSQL that out-weights ease of use, availability and larger developer/knowledge base of MySQL.

MySQL的查询优化器仍然很愚蠢吗?在非常复杂的查询上它仍然超级慢吗?

Is MySQL's query optimizer still stupid? Is it still super slow on very complicated queries?

打我！ :)

PS.而且不要将我发送到Google或Wiki.我正在寻找一些特定的要点，而不是概述，我更信任StackOverflow，而不是一些随机页面，上面有聪明人"的光芒.

PS. And don't send me to goggle or wiki. I am looking for few specific points not an overview + I trust StackOverflow more than some random page with 'smart guy' shining his light.

附录

项目规模:假设一个订购系统每个帐户每天大约有10-100个订单/天，几千个帐户，最终每个帐户可以有数百到数千个用户.

Size of the project: Say an ordering system with roughly 10-100 orders/day per account, couple of thousand accounts, eventually, each can have several hundred to several thousand users.

更好的:在不断增长的需求和不断变化的需求方面，它是面向未来的，并且灵活.性能对于降低硬件部门的成本也很重要.熟练劳动力的可用性也是一个因素.

Better at: being future proof and flexible when it comes to growing and changing requirements. Performance is also important as to keep costs low in hardware department. Also availability of skilled workforce would be a factor.

OLTP或OLAP :OLTP

推荐答案

MySQL的查询优化器仍然很愚蠢吗?它仍然超级慢吗 非常复杂的查询?

Is MySQL's query optimizer still stupid? Is it still super slow on very complicated queries?

所有查询优化器有时都是愚蠢的.在大多数情况下，PostgreSQL并不那么愚蠢. PostgreSQL的一些较新的SQL功能(窗口功能，带有查询的递归等)非常强大，但是如果您使用的是愚蠢的ORM，则可能无法使用.

All query optimizers are stupid at times. PostgreSQL's is less stupid in most cases. Some of PostgreSQL's more recent SQL features (windowing functions, recursive WITH queries etc) are very powerful but if you have a dumb ORM they might not be usable.

项目规模:假设一个订购系统大约有10到100个 每个帐户的订单/天数，最终每个帐户有数千个帐户 可以有几百到几千个用户.

Size of the project: Say an ordering system with roughly 10-100 orders/day per account, couple of thousand accounts, eventually, each can have several hundred to several thousand users.

听起来没有那么大-恰好在一个大盒子里.

Doesn't sound that large - well within reach of a big box.

更好的是:在发展中适应未来的需求并保持灵活性 和不断变化的需求.

Better at: being future proof and flexible when it comes to growing and changing requirements.

PostgreSQL拥有强大的开发团队，并拥有广泛的贡献者社区. 发布政策是严格的，仅在修订版中存在错误修正.始终跟踪9.1.x的最新版本以获取错误修正.

PostgreSQL has a strong developer team, with an extended community of contributors. Release policy is strict, with bugfixes-only in the point releases. Always track the latest release of 9.1.x for the bugfixes.

MySQL过去对版本号的态度较为宽松.甲骨文负责可能会改变这种情况.我对各种分叉的政策不熟悉.

MySQL has had a somewhat more relaxed attitude to version numbers in the past. That may change with Oracle being in charge. I'm not familiar with the policies of the various forks.

性能对于降低硬件部门的成本也很重要.

Performance is also important as to keep costs low in hardware department.

如果如此大的项目中硬件是主要组件，我会感到惊讶.

I'd be surprised if hardware turned out to be a major component in a project this size.

熟练劳动力的可用性也将是一个因素.

Also availability of skilled workforce would be a factor.

那是您的关键决定者.如果您有一群经验丰富的Perl + PostgreSQL黑客闲着闲逛，请使用它.如果您的员工知道Lisp和MySQL，请使用它.

That's your key decider. If you've got a team of experienced Perl + PostgreSQL hackers sat around idle, use that. If your people know Lisp and MySQL then use that.

OLTP或OLAP:OLTP

OLTP or OLAP: OLTP

PostgreSQL在OLTP上一直很强大.

PostgreSQL has always been strong on OLTP.

我个人的观点是PostgreSQL邮件列表中充满礼貌，乐于助人，知识渊博的人.您可以直接与拥有Terabyte数据库的用户和建立代码主要部分的黑客联系.支持的质量确实很棒.

My personal viewpoint is that the PostgreSQL mailing list are full of polite, helpful, knowledgeable people. You have direct contact with users with Terabyte databases and hackers who have built major parts of the code. The quality of the support is truly excellent.

这篇关于Postgres 9.1与Mysql 5.6 InnoDB?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！