oracle 选择正确的数据库：MySQL 与其他所有数据库

Choosing the right database: MySQL vs. Everything else

提问by benmcredmond

It would seem these days that everyone just goes with MySQL because that's just what everyone goes with. I'm working on a web application that will be handling a large quantity of incoming data and am wondering if I should "just go with MySQL" or if I should take a look at other open-source databases or even commercial databases?

现在似乎每个人都只使用 MySQL，因为这正是每个人都使用的。我正在开发一个将处理大量传入数据的 Web 应用程序，我想知道我是否应该“只使用 MySQL”，还是应该查看其他开源数据库甚至商业数据库？

EDIT: Should have mentioned, am looking for optimal performance, integration with ruby + rails running on debian 5 and money is tight although if it will save money in the long run I would consider making an investment into something more expensive.

编辑：应该提到，我正在寻找最佳性能，与在 debian 5 上运行的 ruby​​ + rails 集成并且资金紧张，但如果从长远来看能省钱，我会考虑投资更昂贵的东西。

回答by tpdi

I've posted this before, but I have no reason to change this advice:

我以前发布过这个，但我没有理由改变这个建议：

MySQL is easier to start using.

MySQL 更容易上手。

Nicer UI tools. Faster, if you don't use ACID. More tolerant of invalid data. Autoincrement columns are as easy as typing autoincrement. Permissions aren't as tied to the file systems and OS users. Setting a delimiter is easier than using PG's "dollar sign quoting" when writing a stored proc. In MySQL, you connect to all databases, not just one at a time.

更好的 UI 工具。更快，如果您不使用 ACID。更能容忍无效数据。自动增量列就像键入 autoincrement 一样简单。权限与文件系统和操作系统用户无关。在编写存储过程时，设置分隔符比使用 PG 的“美元符号引用”更容易。在 MySQL 中，您连接到所有数据库，而不仅仅是一次一个。

Postgres (PG) is much more standards compliant, but it's uglier and more complicated, especially from a UI perspective. It used to require manual vacuuming, and actually enforces referential integrity (which is a great thing that can be a pain in the ass). Autoincrement is much more flexible, but requires sequences (which can me masked by using serial), and wait, what's an OID?

Postgres (PG) 更符合标准，但它更丑陋、更复杂，尤其是从 UI 的角度来看。它曾经需要手动清理，实际上强制执行参照完整性（这是一件很棒的事情，但可能会很痛苦）。自动增量更灵活，但需要序列（我可以使用串行屏蔽），等等，什么是 OID？

So if you don't really know or care much about databases, data validity, ACID compliance, etc, but you do care about ease and speed, you tend to go with MySQL.

因此，如果您不太了解或不太关心数据库、数据有效性、ACID 合规性等，但您确实关心易用性和速度，那么您倾向于使用 MySQL。

Too many (not all, but many) "web programmers" know a lot about "web 2.0" or PHP or Java, but don't know much about database theory or practice ("an index? what's that?"). They tend to see a database as just a fancy hashtable or bag of data, and indeed one that's not anywhere as dynamically changeable or forgiving as a hashtable.

太多（不是全部，但很多）“Web 程序员”对“web 2.0”或 PHP 或 Java 了解很多，但对数据库理论或实践知之甚少（“索引？那是什么？”）。他们倾向于将数据库视为只是一个花哨的哈希表或数据包，实际上，它不像哈希表那样具有动态可变性或宽容性。

For these folks, MySQL -- because until 5.0 it wasn't really an RDBMS, and in many ways still is not -- is a godsend. It's "faster" than the competition, and doesn't "waste time" on "esoteric" database stuff a web programmer doesn't want, understand, or see the value of.

对于这些人来说，MySQL——因为在 5.0 之前它并不是真正的 RDBMS，而且在许多方面仍然不是——是天赐之物。它比竞争对手“更快”，并且不会“浪费时间”在 Web 程序员不想要、不了解或不了解其价值的“深奥”数据库内容上。

For somebody with a database background, on the other hand, MySQL is a minefield: stuff that should work (complicated views, group bys, order bys in group bys) may work or may if you're lucky crash the server, or if you're unlucky just give results with incorrect data.

另一方面，对于具有数据库背景的人来说，MySQL 是一个雷区：应该可以工作的东西（复杂的视图、group bys、group bys 中的 order bys）可能会起作用，或者如果你幸运的话可能会导致服务器崩溃，或者如果你' 不走运只是给出不正确数据的结果。

I've spent days working around some of these things in admittedly complicated by not extraordinarily complex views and group bys.

我已经花了几天的时间来处理其中一些事情，当然不是特别复杂的视图和分组。

And MySQL isn't really faster. If you're using InnoDb tables for ACID (or just because at more than 30 Million rows, MyISAM tables tend to get crappy), yes a straight one-table select is probably faster than in PG. But add in joins, and PG is suddenly significantly faster. (MySQL is especially bad at outer joins.)

而 MySQL 并不是真的更快。如果您将 InnoDb 表用于 ACID（或者仅仅因为超过 3000 万行，MyISAM 表往往会变得糟糕），是的，直接单表选择可能比在 PG 中更快。但是加入连接，PG 突然明显变快了。（MySQL 在外连接方面尤其糟糕。）

In summary: if to you the database is a bag, if you never intend to do data mining or reporting, if you're mostly interested in serving up big hunks of text with few relations or updates -- that is, if you're using a database to power a blog, MySQL is a great choice.

总而言之：如果数据库对您来说是一个包，如果您从不打算进行数据挖掘或报告，如果您最感兴趣的是提供几乎没有关系或更新的大块文本——也就是说，如果您使用数据库来支持博客，MySQL 是一个不错的选择。

But if you're actually managing data, if you understand that data lives longer and is more valuable to a business than front-end programs and middle-tier business rules , if you need the features of a real database, use PG.

但是，如果您实际上是在管理数据，如果您了解数据比前端程序和中间层业务规则寿命更长并且对业务更有价值，如果您需要真实数据库的功能，请使用 PG。

A "web programmer" who has decided all his table structures can be auto-generated by Hibernate (or some other ORM) looks at that and says, "too complicated" and "I bet complicated means more cost and slower speed" and so he goes with MySQL.

一个“网络程序员”决定他的所有表结构都可以由 Hibernate（或其他一些 ORM）自动生成，看着它说，“太复杂了”和“我打赌复杂意味着更多的成本和更慢的速度”，所以他与 MySQL 一起使用。

As I said, PG is far superior, and I hate mucking with MySQL's bizarre bugs, and I think that overall PG performance is probably better than MySQL for any even slightly complicated query.

正如我所说，PG 远胜于其他人，而且我讨厌与 MySQL 的奇怪错误混为一谈，而且我认为对于任何稍微复杂的查询，PG 的整体性能可能比 MySQL 更好。

But MySQL makes things look (deceptively) simple, so you get a lot of people who don't really understand database design figuring that MySQL is a great choice.

但是 MySQL 使事情看起来（看似）简单，因此您会发现很多并不真正了解数据库设计的人认为 MySQL 是一个不错的选择。

Use PG. It's consistent, it's reliable, it's standards-compliant, it's faster on (even moderately) complicated queries, it doesn't completely throw off your schedule with weird bugs.

使用PG。它是一致的、可靠的、符合标准的，在（甚至是中等）复杂的查询上速度更快，它不会因为奇怪的错误而完全打乱你的日程安排。

回答by duffymo

I think PostgreSQLis a very viable alternative to MySQL. It's much more Oracle-like.

我认为PostgreSQL是 MySQL 的一个非常可行的替代方案。它更像 Oracle。

回答by Jordan S. Jones

Personally, I try to avoid MySQL whenever I can for the following reasons:

就我个人而言，出于以下原因，我尽量避免使用 MySQL：

1. The defaultstorage engine MyISAM lacks Foreign Keysupport. Innodbdoes, however it does not support Foreign Keys to MyISAM tables for obvious reasons.
2. If I attempt to insert invalid data, MySQL will happily change it for me.
   1. Illegal DateTime, Date, or Timestamp values are convert to "zero": http://dev.mysql.com/doc/refman/5.1/en/datetime.html
   2. Varchar and Char type columns: http://dev.mysql.com/doc/refman/5.1/en/char.html
   3. Numeric Datatypes depending on SQL Strict Mode: http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

1. 该默认存储引擎的MyISAM缺乏外键的支持。Innodb支持，但是由于显而易见的原因，它不支持 MyISAM 表的外键。
2. 如果我尝试插入无效数据，MySQL 会很乐意为我更改它。
   1. 非法的 DateTime、Date 或 Timestamp 值转换为“零”：http: //dev.mysql.com/doc/refman/5.1/en/datetime.html
   2. Varchar 和 Char 类型列：http: //dev.mysql.com/doc/refman/5.1/en/char.html
   3. 取决于 SQL 严格模式的数字数据类型：http: //dev.mysql.com/doc/refman/5.1/en/numeric-types.html

These things may not be of any importance to some people, but when it comes to quality of data, I would rather use something else.

这些东西对某些人来说可能并不重要，但是当涉及到数据质量时，我宁愿使用其他东西。

回答by John Gietzen

Well, there are may differences between the RDBMS of the world. Take a look at http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Fundamental_features

好吧，世界上的 RDBMS 之间可能存在差异。看看http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Fundamental_features

Using this as a guide you should be able to narrow down your choices a lot.

以此为指导，您应该能够大大缩小您的选择范围。

A few things to keep in mind:

需要注意以下几点：

SQL Server 2005 Express is limited to a 4GB file size, but has excellent support int the .NET and Java languages.

SQL Server 2005 Express 的文件大小限制为 4GB，但对 .NET 和 Java 语言具有出色的支持。

MySQL will run on both Windows and Linux, and many languages support it (including .NET and Java) with external libraries.

MySQL 将在 Windows 和 Linux 上运行，并且许多语言（包括 .NET 和 Java）通过外部库支持它。

SQLite is supported by effectively every operating system, and can be distributed as an integrated part of your application.

每个操作系统都有效地支持 SQLite，并且可以作为应用程序的一个集成部分进行分发。

回答by Tim Jarvis

Firebirdthe open sourced and branched version of Borland's Interbase is pretty good, it works happily on most (all?) flavours of Linux and is very performant. I am not a RoR guy so I don't know the details, but I do have a friend in NZ who uses Firebird with all his RoR projects, it definitely works with RoR, and works well.

Firebird是 Borland 的 Interbase 的开源和分支版本非常好，它可以在大多数（所有？）Linux 版本上愉快地运行，并且性能非常好。我不是一个 RoR 人，所以我不知道细节，但我在新西兰有一个朋友，他在他所有的 RoR 项目中使用 Firebird，它绝对适用于 RoR，并且效果很好。

Edit:
Found a link to a Firebird Rails Adapter here

编辑：在此处
找到指向 Firebird Rails 适配器的链接

回答by staticsan

Please be a little wary of the idealistic points-of-view put forth by people who may have axes to grind. MySQL is a capable, robust and scalable solution for many problems, but this is usually not so right out-of-the-box as its defaults are extremely conservative. Like any database product, you will need to spend time tuning its installation for the desired performance. You will also need to spend some time accomodating its limitations, which is also true for any database you choose.

请对可能有轴心的人提出的理想主义观点保持警惕。MySQL 是解决许多问题的强大、健壮和可扩展的解决方案，但这通常不是开箱即用的，因为它的默认值非常保守。与任何数据库产品一样，您需要花时间调整其安装以获得所需的性能。您还需要花一些时间来适应其局限性，这对于您选择的任何数据库也是如此。

MySQL's popularity is partly self-generated: a lot of hosting providers provide it, so a lot of people use it.

MySQL 的流行部分是由自身产生的：很多托管服务提供商都提供它，所以很多人都在使用它。

回答by Micha? Niklas

Like duffymo I also recommend PostgreSQL. It is very nice from a developer perspective: work on many platforms (both unix-based and Windows), has vary stable interfaces to any laguage/environment I worked (Windows, Linux, Delphi, Java, Perl, Python). Stored procedure language: PLPGSQL is also easy and powerful. User support (newsgroups, lists, SO) is nice and helpful.

和 duffymo 一样，我也推荐 PostgreSQL。从开发人员的角度来看，这是非常好的：在许多平台（基于 unix 和 Windows）上工作，对我工作的任何语言/环境（Windows、Linux、Delphi、Java、Perl、Python）都有不同的稳定接口。存储过程语言：PLPGSQL 也简单而强大。用户支持（新闻组、列表、SO）很好而且很有帮助。

回答by ahains

Mysql is great, and mssql is great. I haven't used anything else. I would say if you are completely on the fence, go with the technology stack you are strongest with. I have a good amount of c#, asp.net, and other Microsoft stack experience, so it is pretty natural for me to specialize in mssql. If you are more familiar with *nix, php, etc, you may be more at home sticking to an open source stack. You can certainly mix and match the two stacks, but sticking to one world or the other can avoid some pain for you.

Mysql 很棒，而 mssql 也很棒。我没用过别的。我想说的是，如果您完全处于围栏状态，请选择您最擅长的技术堆栈。我有大量的 c#、asp.net 和其他 Microsoft 堆栈经验，因此专攻 mssql 对我来说很自然。如果您更熟悉 *nix、php 等，那么您可能更喜欢坚持使用开源堆栈。你当然可以混合搭配这两个堆栈，但坚持一个世界或另一个世界可以避免你的痛苦。

回答by Robin Day

"It would seem these days that everyone just goes with MySQL because that's just what everyone goes with." If MySQL is the only thing that people use then why are Oracle and MSSQL still around?

“现在似乎每个人都只使用 MySQL，因为这正是每个人都使用的。” 如果 MySQL 是人们唯一使用的东西，那么为什么 Oracle 和 MSSQL 仍然存在？

The debate as to which database engine to go for can be talked about until the cows come home. I personally have always found one constant in choosing a databse engine. The one you can afford is generally the one you go for.

关于使用哪种数据库引擎的争论可以一直讨论到奶牛回家。我个人在选择数据库引擎时总是发现一个常数。你能买得起的通常就是你想要的。

If you can justify spending XXX on a database then you probably know the reasons already for choosing it.

如果您可以证明在数据库上花费 XXX 是合理的，那么您可能已经知道选择它的原因。

回答by Ciarrra

MySQL is easy to install and it works fine without special settings. With the proper approach MySQL can flexibly adjust to your needs. But there are also some pitfalls: in some cases it may slow down your project, no matter how well you have tuned the DBMS and the data structure.

MySQL 易于安装，无需特殊设置即可正常工作。通过适当的方法，MySQL 可以灵活地适应您的需求。但也存在一些陷阱：在某些情况下，无论您对 DBMS 和数据结构进行了多么出色的调整，它都可能会减慢您的项目速度。

MySQL is for you if:

MySQL 适合您，如果：

• you do not want to delve into DBMS settings;
• you think structurally;
• integration with MySQL is in any programming language, framework, CMS, CMF and so on;
• you need DBMS to manage small structural data (up to 1 or 2 gigabytes).

• 您不想深入研究 DBMS 设置；
• 你从结构上思考；
• 在任何编程语言、框架、CMS、CMF 等中与 MySQL 集成；
• 您需要 DBMS 来管理小型结构数据（最多 1 或 2 GB）。

You mentioned, that you deal with large quantity of incoming data. Probably this guidewould be helpful

您提到，您处理大量传入数据。可能本指南会有所帮助