何时选择 Oracle 而不是 MySQL？

When to choose Oracle over MySQL?

提问by Sabeen Malik

I am a MySQL fan , however i want to know in which situations choosing Oracle over MySQL seems like the way to go .. like what would be the indicators which would make you say .. "I need Oracle for this project"

我是 MySQL 的粉丝，但是我想知道在哪种情况下选择 Oracle 而不是 MySQL 似乎是要走的路..就像是什么指标会让你说..“我需要 Oracle 来做这个项目”

Update: As another fellow SOer pointed out, dont limit your answers to Oracle ... if you know of something better , please point that out too.

更新：正如另一位 SOer 同事指出的那样，不要将您的答案限制在 Oracle 上……如果您知道更好的东西，也请指出。

采纳答案by Svetlozar Angelov

There are certain things that bother me with Mysql

Mysql 有一些困扰我的事情

I have to choose between enforcing contraints and transactions vs Fulltext Index (InnoDb vs MyISAM). This is really the problem number 1 for me (enforcing contraints and transactions is what makes dbs cool, but you need the fulltext search too...)

我必须在强制执行约束和事务与全文索引（InnoDb 与 MyISAM）之间做出选择。这对我来说真的是第 1 个问题（强制执行约束和事务是使 dbs 很酷的原因，但您也需要全文搜索......）

• It is not easy to "simulate" transactions in client code.
• If you don't enforce the constraints it is really easy to get inconsistent state of the db
• Without Fulltext Search you might get crazy with OR X LIKE %y%
• You have to create BEFORE UPDATE TRIGGER with RAISE ERROR for CHECK CONSTRAINT
• Mysql has bad performace when data becomes too large(I mean really large).
• Mysql creates poor execution plans
• Mysql has problems with more than 3 joins(let's better say multiple joins).

• 在客户端代码中“模拟”事务并不容易。
• 如果您不强制执行约束，则很容易获得数据库的不一致状态
• 如果没有全文搜索，您可能会对 OR X LIKE %y% 感到抓狂
• 您必须使用 RAISE ERROR 创建 BEFORE UPDATE TRIGGER for CHECK CONSTRAINT
• 当数据变得太大（我的意思是非常大）时，Mysql 的性能很差。
• Mysql 创建糟糕的执行计划
• Mysql 有超过 3 个连接的问题（最好说多个连接）。

Oracle is the solution for all these problems, it is a full DBMS (transactions, CHECK contraints, a lot of options for views, fulltext search and much more.. ) but after all it is a matter of money.

Oracle 是所有这些问题的解决方案，它是一个完整的 DBMS（事务、CHECK 约束、许多视图选项、全文搜索等等），但毕竟这是一个金钱问题。

回答by Marius Burz

I'm an Oracle guy myself, yet sometimes I find it hard to argue its use over PostgeSQL or even MySQL.
The short story is that there are companies/projects out there that manage huge amounts of data using some open source RDBMS.

我自己是一个 Oracle 人员，但有时我发现很难在 PostgeSQL 甚至 MySQL 上争论它的使用。
简而言之，有些公司/项目使用一些开源 RDBMS 来管理大量数据。

If you are to come down to the features, that's another thing, but how can you argue against the success of those companies? True, they use a lot of boxes for achieving this, but it's still much cheaper. I doubt that most companies that use Oracle nowadays really need it, but there are certainly companies that need Oracle.

如果您要归结为功能，那是另一回事，但是您怎么能反对这些公司的成功呢？确实，他们使用了很多盒子来实现这一点，但它仍然便宜得多。我怀疑现在大多数使用 Oracle 的公司是否真的需要它，但肯定有一些公司需要 Oracle。

A nice quote:

一个不错的报价：

"Move cpu-intensive work moved out of the database layer to applications applications layer: referential integrity, joins, sorting done in the application layer! Reasoning: app servers are cheap, databases are the bottleneck."

“将 CPU 密集型工作从数据库层转移到应用程序层：引用完整性、连接、排序在应用程序层完成！推理：应用程序服务器便宜，数据库是瓶颈。”

See herefor lots of real world practice. And yes, they use Oracle.

见这里的许多现实世界的实践。是的，他们使用 Oracle。

And yes, I still love Oracle, it's what a DBMS done right should look like, but that doesn't mean its place is everywhere, at least not at the price it sells for.

是的，我仍然喜欢 Oracle，这是一个正确的 DBMS 应该是什么样子，但这并不意味着它无处不在，至少不是它的售价。

回答by Steve De Caux

Maybe it's worth considering postgresql, which is also free (BSD instead of gpl).

也许值得考虑 postgresql，它也是免费的（BSD 而不是 gpl）。

For a nice review of choices, have a look at Wikipedia using "Comparison of relational database management systems".

要对选择进行很好的回顾，请使用“关系数据库管理系统的比较”查看维基百科。

回答by guigui42

Analytics ... do you need any other reasons ? ;)

分析……您还需要其他理由吗？;)

Analytics are a blessing for all reports and datawharehouses. But then, if you just need a little database for a website, stick with MySQL, otherwise, if you need complicated reporting and performance is an issue, then i believe Oracle is the way to go.

分析是所有报告和数据仓库的福音。但是，如果你只需要一个网站的小数据库，坚持使用 MySQL，否则，如果你需要复杂的报告和性能是一个问题，那么我相信 Oracle 是要走的路。

回答by Ken

I know nothing about Oracle, but since the question was expanded to include, say, Postgres...

我对 Oracle 一无所知，但是由于问题已扩展为包括 Postgres ...

Some things I've personally used or seen used in Postgres that don't really exist in MySQL (AFAICT)!

我个人在 Postgres 中使用或看到的一些东西在 MySQL 中并不存在（AFAICT）！

• transactions and full-text search together

• 交易和全文搜索一起

I know it's popular to use an external FTS since MySQL doesn't have one. Personally, I've had nothing but trouble using separate FTS solutions: if it's possible for the two data sources to get out of sync, I can guarantee that at some point they will. I could use BDB and write my own indices, too, but I don't, because I see no way in which it's better than a built-in index, and a bunch of ways in which it's worse. (OK, in one case I needed a weird custom index, and for that it's nice. If you need a weird custom FTS, then maybe Sphinx is more flexible. But I've never seen a real need for a weird custom FTS, and I'm not even sure that Sphinx is more flexible than Postgres FTS.)

我知道使用外部 FTS 很流行，因为 MySQL 没有。就我个人而言，我在使用单独的 FTS 解决方案时遇到了一些麻烦：如果两个数据源可能会不同步，我可以保证在某个时候它们会不同步。我也可以使用 BDB 并编写我自己的索引，但我不这样做，因为我看不到它比内置索引更好的方法，还有很多比内置索引更糟糕的方法。（好吧，在一种情况下，我需要一个奇怪的自定义索引，这很好。如果你需要一个奇怪的自定义 FTS，那么 Sphinx 可能更灵活。但我从未见过真正需要一个奇怪的自定义 FTS，并且我什至不确定 Sphinx 比 Postgres FTS 更灵活。）

• spatial queries (PostGIS)

• 空间查询 (PostGIS)

I don't know if MySQL has an extension mechanism that allows this, but I'm pretty sure it doesn't have an extension like PostGIS. Let's say you want to query for all coffee shops within 300 meters of a park, and not within 100 meters of a landfill (given that your database has the boundaries of parks, landfills, and coffee shops). It's impressively easy with PostGIS. With MySQL, I think it would probably be a decent amount of work.

我不知道 MySQL 是否有允许这样做的扩展机制，但我很确定它没有像 PostGIS 这样的扩展。假设您要查询公园 300 米范围内的所有咖啡店，而不是垃圾填埋场 100 米范围内的所有咖啡店（假设您的数据库包含公园、垃圾填埋场和咖啡店的边界）。使用 PostGIS 非常容易。使用 MySQL，我认为这可能是一个体面的工作。

• object-relational tables

• 对象关系表

Rails people (hey, I used to be one!), especially, like to use STI and pretend that all subclasses have pretty much the same fields as the superclass. It's OK if you only have a couple subclasses or they're all very similar, but trying to map a class hierarchy to tables can get pretty crazy pretty quick. In Postgres, it's easy: make a new table that inherits from the first one, and adds its fields, just like subclassing in your programming language. A data model that actually matches my data! Not as nice as a real OODB but pretty friggin' close.

Rails 人（嘿，我曾经是其中之一！），尤其是喜欢使用 STI 并假装所有子类都具有与超类几乎相同的字段。如果您只有几个子类或者它们都非常相似，那也没关系，但是尝试将类层次结构映射到表会很快变得非常疯狂。在 Postgres 中，这很容易：创建一个继承自第一个表的新表，并添加其字段，就像在您的编程语言中子类化一样。一个真正匹配我的数据的数据模型！不如真正的 OODB 好，但非常接近。

• better transactions

• 更好的交易

I know if you stick with InnoDB you get transactions for all data manipulation operations. Postgres also has transactions for all data definitionoperations. Take a common case: I need a migration to add a column, convert data from the old format to the new format, and remove an old column. In Postgres, I just do it all in one transaction, and there's no chance I'll end up with this transaction partially applied. In MySQL, it can do the data conversion step in a transaction, sure, but if it has to rollback, the new column has still been added, so you need to clean it up by hand, or write a more complex transaction to deal with that (and even then it's still not atomic in the db). Repeat every day, and enjoy the pain.

我知道如果您坚持使用 InnoDB，您将获得所有数据操作操作的事务。Postgres 也有所有数据定义操作的事务。举一个常见的案例：我需要迁移添加一列，将数据从旧格式转换为新格式，并删除旧列。在 Postgres 中，我只是在一个事务中完成所有工作，并且我不可能最终会部分应用此事务。在MySQL中，它可以在一个事务中做数据转换的步骤，当然可以，但是如果要回滚，新的列仍然被添加了，所以你需要手工清理它，或者写一个更复杂的事务来处理那个（即使这样它在数据库中仍然不是原子的）。每天重复，享受痛苦。

(The general theme I see here is the ability to say just what I mean, and therefore work at a higher level of abstraction. Want FTS on my data? Then create a FTS index. Want a spatial query? Then write a spatial query. Want to store subclass data? Then make a subclass table. Want a migration that's completely atomic? Then slap a transaction around it and call it a day. Sure, I can implement any of these in MySQL, but then I'm having to think about and implement and maintain that other thing, rather than just writing a single line of SQL. As a professional programmer there's nothing more valuable to me than being able to work at a higher level of abstraction, full stop.)

（我在这里看到的一般主题是能够说出我的意思，因此可以在更高的抽象级别上工作。想要对我的数据进行 FTS？然后创建一个 FTS 索引。想要一个空间查询？然后编写一个空间查询。想要存储子类数据？然后制作一个子类表。想要一个完全原子的迁移？然后围绕它创建一个事务并称它为一天。当然，我可以在 MySQL 中实现这些中的任何一个，但是我不得不考虑关于并实现和维护其他事情，而不仅仅是编写一行 SQL。作为一名专业程序员，对我来说，没有什么比能够在更高的抽象级别工作更有价值的了，句号。）

Now, I'm not sure if I'd say Postgres is generally "better" than MySQL -- there are certainly things that MySQL does much better and so it certainly has its uses -- but these are a few things I absolutely love about it.

现在，我不确定我是否会说 Postgres 通常比 MySQL“更好”——当然 MySQL 在某些方面做得更好，所以它肯定有它的用途——但这些是我非常喜欢的一些事情它。

回答by O. Jones

Oracle offers a highly sophisticated, and complex, scheme for backing up and restoring live data, giving it true non-stop hot backup capability.

Oracle 为备份和恢复实时数据提供了一种高度成熟和复杂的方案，使其具有真正的不间断热备份功能。

It offers lots of analytical and statistical functions built in for aggregating and summarizing data. You can do this stuff with mySQL, but in your app.

它提供了许多内置的分析和统计功能，用于聚合和汇总数据。你可以用 mySQL 做这些事情，但在你的应用程序中。

It has excellent scalability, if you need to run a large transactional system with transactional integrity (commit / rollback). If you're willing to spend a lot of money, it scales out with Real Application Clusters.

如果您需要运行具有事务完整性（提交/回滚）的大型事务系统，它具有出色的可扩展性。如果您愿意花很多钱，可以使用 Real Application Clusters 进行横向扩展。

It offers schemes for handling embedded code (packages, stored procedures, stored functions) that scale up well to large quantities of code and complex schemas.

它提供了处理嵌入式代码（包、存储过程、存储函数）的方案，可以很好地扩展到大量代码和复杂模式。

It does a good job with high transaction rates (tens of thousands per hour), especially when you use bound variable queries (PreparedStatement objects in JDBC). Most importantly, its performance for this sort of thing is predictable.

它在高事务率（每小时数万次）的情况下做得很好，尤其是当您使用绑定变量查询（JDBC 中的 PreparedStatement 对象）时。最重要的是，它在这类事情上的表现是可以预测的。

It's very expensive and requires a krewe of highly trained acolytes to keep it working properly. The good news is there are lots of expert people out there. The place I work spends money with an outfit called Pythian Group to look after our Oracle, which is a good way to go.

它非常昂贵，需要一群训练有素的追随者才能保持正常工作。好消息是那里有很多专家。我工作的地方花钱请了一个叫做Pythian Group的机构来照顾我们的Oracle，这是一个很好的方法。

You can evaluate it with the Express Edition (free, limited to a couple of gigabytes of data).

您可以使用 Express Edition（免费，仅限几 GB 数据）对其进行评估。

If your system can work with Oracle Standard Edition, then use mySQL instead. If your system requires the Enterprise Edition features, then you should also evaluate IBM DB2, as both of these are designed for scaleout.

如果您的系统可以使用 Oracle 标准版，请改用 mySQL。如果您的系统需要 Enterprise Edition 特性，那么您还应该评估 IBM DB2，因为它们都是为横向扩展而设计的。

回答by Scott Swank

There are a variety of things that Oracle does very well, perhaps better than any other rdbms (I don't know DB2 well enough to leave that unqualified). The clustering (RAC) is very, very good. The stored procedure language, PL/SQL, is very solid and actually enjoyable to code in. There is good XML functionality, GIS, full-text, etc.

Oracle 在很多方面都做得很好，也许比任何其他 rdbms 都要好（我对 DB2 的了解不够深入，所以不合格）。集群 (RAC) 非常非常好。存储过程语言 PL/SQL 非常可靠，编写代码实际上很有趣。有很好的 XML 功能、GIS、全文等。

But the absolute deal breaker for me is that the optimizer works well. You give it a query and it returns you a result set in an efficient manner. Occasionally you know some detail that it does not and have to provide a hint accordingly, but that is the rare exception. You issue SELECT, INSERT, UPDATE and DELETE statements and the database works the way it ought to.

但对我来说绝对的交易破坏者是优化器运行良好。你给它一个查询，它以一种有效的方式返回一个结果集。有时您知道一些它不知道的细节并且必须相应地提供提示，但这是罕见的例外。您发出 SELECT、INSERT、UPDATE 和 DELETE 语句，数据库就会按照它应有的方式工作。

回答by David Aldridge

Oracle bring a huge set of functionality with it, some of it optional extras that you pay for but much of it included for free in every edition.

Oracle 带来了大量的功能，其中一些是您付费购买的可选附加功能，但其中大部分功能在每个版本中都是免费的。

http://www.oracle.com/database/product_editions.html

http://www.oracle.com/database/product_editions.html

Flashback query is a free include, and lets you query the database "as of" some time in the recent past.

闪回查询是一个免费的包含，可以让您查询数据库“截至”最近的某个时间。

Expression filters are another good example of a freebee, and the Rules engine is also powerful. http://www.oracle.com/technology/products/database/rules_manager/index.html

表达式过滤器是 freebee 的另一个很好的例子，规则引擎也很强大。http://www.oracle.com/technology/products/database/rules_manager/index.html

So when you make your choice you ought to consider what features you can leverage that will get you functionality that you would otherwise have to develop and support yourself.

因此，当您做出选择时，您应该考虑可以利用哪些功能来获得原本必须自己开发和支持的功能。