关系型与非关系型数据库的差异：MySQL vs MongoDB

译文

每当面对选择数据库时，选用关系型（SQL）还是非关系型（NoSQL）数据库是最大的决定之一。虽然两者都是可行的选项，但用户在决策前必须牢记它们之间的重大差异。

在这里我们分析并讨论关系型和非关系型数据库的两个代表，也就是 MySQL 和 MongoDB 最重要的区别。

宏观上的差异

语言

想象一个小镇——我们叫它 A 镇，这里每个人都说相同的语言。所有东西都围绕这种语言建立，每种形式的交流都使用它。简而言之，它是居民们理解并与周围的世界互动的唯一途径。改变语言将会对每个人都造成混乱和破坏。

另一个镇子，B 镇，那里的每家人都说不同的语言，每个人都通过不同的途径与世界互动，没有“普遍的”世界观或组织机构。一个人的与众不同对其他人根本没有影响。

这个例子有助于说明关系型数据库和非关系型数据库的根本区别之一，这个区别使两者截然不同。解释一下：

关系型数据库使用结构化的查询语句（SQL）来定义和操控数据。一方面，这极富掌控力：SQL 是最多功能和广泛使用的语言之一，它是一个保险的选项，尤其适用于复杂查询。另一方面，它有限制性。SQL 要求使用预先设计好的模式来定义数据结构，然后才能使用。此外，所有的数据都要遵循相同的结构。这需要大量的前期准备工作，并且，就像 A 镇一样，结构的改变可能对整个系统造成破坏。

非关系型数据库，相反，具有非结构化数据的动态模式，而且数据库以多种方式存储：可以是面向字段的、面向文档的、基于图形的或者以键值对的形式存储。这种灵活性意味着：

• 无需预先定义结构就能创建文档
• 每个文档都能有独特的结构
• 数据库与数据库之间语法不同
• 想怎么插入就怎么插入

扩展性

大多数情况下，关系型数据库都是纵向扩展的，你可以通过堆 CPU、内存或者 SSD 来增加单服务器的负载能力。而非关系型数据库是横向扩展的。这意味着你可以通过分库或者增加更多的服务器来处理更多的流量。这就像是向同一幢楼增加更多的楼层和在邻里增加更多的楼房。而后者可以变成更强大，所以非关系型数据库是大型或者不断变化的数据集的首选。

结构

关系型数据库基于表，而非关系型数据库基于文档、键值对、图数据库或者多字段存储。这使得关系型数据库成为需要多事务的应用程序（例如会计系统）或者为关系型数据结构搭建的系统的更好的选择。

关系型数据库包括 MySQL、Oracle、PostgreSQL 和 微软的 SQL Server。非关系型数据库有 MongoDB、BigTable、Redis 等。

SQL vs NoSQL: MySQL vs MongoDB

现在我们已经知道了两种数据库之间的结构差异，接下来探索下两者的功能差异，以 MySQL 和 MongoDB 为例。

MySQL，关系型数据库

下面是 MySQL 的优势：

• 成熟： MySQL 是非常成熟的数据库，坐拥庞大的社区，经过广泛的测试证明相当稳定。
• 兼容性： MySQL 在主流平台都可用，包括 Linux，Windows，Mac，BSD 和 Solaris；支持各种编程语言，Node.js，Ruby，C#，C++，Java，Perl，Python 和 PHP。
• 代价小： 开源免费
• 冗余： MySQL 数据库可做多节点冗余来负载均衡，提升应用程序的可用性和扩展性。
• 分片： 大多数关系型数据库不可以分片，但是 MySQL 可以，省时省力。

MongoDB，非关系型数据库

下面是 MongoDB 的优势：

• 动态模式： 如上所述，你能够灵活地更改数据模式而无需修改任何先有数据。
• 扩展性： MongoDB 横向扩展，有助于负载均衡并轻松扩展业务。
• 易于管理： 不需要数据库管理员。开发者和管理员都可以使用，对用户相当友好。
• 速度： 查询简单性能高。
• 灵活性： 可以在 MongoDB 中插入新的字段或域而不影响现有数据或者性能。

你的业务选择哪种数据库？

对于任何受益于预定义结构和模式的业务来说，比如会计系统或仓储监控系统这样需要多事务的应用程序，或在老式的系统上运行的应用程序，MySQL 更好。

相反，MongoDB 对那些业务飞速发展或者数据库没有明确结构的公司来说是个不错的选择。进一步说，如果你没法定义数据库结构，如果你没法规范数据模式，如果你的数据模型一直在变，移动应用程序、实时分析、内容管理系统常常是这种情况，强烈建议选择 MongoDB。

原文

When it comes to choosing a database, one of the biggest decisions is picking a relational (SQL) or non-relational (NoSQL) data structure. While both are viable options, there are certain key differences between the two that users must keep in mind when making a decision.

Here, we break down the most important distinctions and discuss two of the key players in the relational vs non-relational debate: MySQL and MongoDB.

The Big Picture Differences

The Language

Think of a town - we’ll call it Town A - where everyone speaks the same language. All of the businesses are built around it, every form of communication uses it - in short, it’s the only way that the residents understand and interact with the world around them. Changing that language in one place would be confusing and disruptive for everyone.

Now, think of another town, Town B, where every home can speak a different language. Everyone interacts with the world differently, and there’s no “universal” understanding or set organization. If one home is different, it doesn’t affect anyone else at all.

This helps illustrate one of the fundamental differences between SQL relational and NoSQL non-relational databases, and this distinction has big implications. Let’s explain:

SQL databases use structured query language (SQL) for defining and manipulating data. On one hand, this is extremely powerful: SQL is one of the most versatile and widely-used options available, making it a safe choice and especially great for complex queries. On the other hand, it can be restrictive. SQL requires that you use predefined schemas to determine the structure of your data before you work with it. In addition, all of your data must follow the same structure. This can require significant up-front preparation, and, as with Town A, it can mean that a change in the structure would be both difficult and disruptive to your whole system.

A NoSQL database, on the other hand, has dynamic schema for unstructured data, and data is stored in many ways: it can be column-oriented, document-oriented, graph-based or organized as a KeyValue store. This flexibility means that:

• You can create documents without having to first define their structure
• Each document can have its own unique structure
• The syntax can vary from database to database, and
• You can add fields as you go.

The Scalability

In most situations, SQL databases are vertically scalable, which means that you can increase the load on a single server by increasing things like CPU, RAM or SSD. NoSQL databases, on the other hand, are horizontally scalable. This means that you handle more traffic by sharding, or adding more servers in your NoSQL database. It’s like adding more floors to the same building versus adding more buildings to the neighborhood. The latter can ultimately become larger and more powerful, making NoSQL databases the preferred choice for large or ever-changing data sets.

The Structure

SQL databases are table-based, while NoSQL databases are either document-based, key-value pairs, graph databases or wide-column stores. This makes relational SQL databases a better option for applications that require multi-row transactions - such as an accounting system - or for legacy systems that were built for a relational structure.

Some examples of SQL databases include MySQL, Oracle, PostgreSQL, and Microsoft SQL Server. NoSQL database examples include MongoDB, BigTable, Redis, RavenDB Cassandra, HBase, Neo4j and CouchDB.

SQL vs NoSQL: MySQL vs MongoDB

Now that we’ve established the key structural differences between SQL and NoSQL databases, let’s delve into the key functional differences between the two, looking specifically at MySQL and MongoDB as examples.

MySQL: The SQL Relational Database

The following are some MySQL benefits and strengths:

• Maturity: MySQL is an extremely established database, meaning that there’s a huge community, extensive testing and quite a bit of stability.
• Compatibility: MySQL is available for all major platforms, including Linux, Windows, Mac, BSD and Solaris. It also has connectors to languages like Node.js, Ruby, C#, C++, Java, Perl, Python and PHP, meaning that it’s not limited to SQL query language.
• Cost-effective: The database is open source and free.
• Replicable: The MySQL database can be replicated across multiple nodes, meaning that the workload can be reduced and the scalability and availability of the application can be increased.
• Sharding: While sharding cannot be done on most SQL databases, it can be done on MySQL servers. This is both cost-effective and good for business.

MongoDB: The NoSQL Non-Relational Database

The following are some of MongoDB benefits and strengths:

• Dynamic schema: As mentioned, this gives you flexibility to change your data schema without modifying any of your existing data.
• Scalability: MongoDB is horizontally scalable, which helps reduce the workload and scale your business with ease.
• Manageability: The database doesn’t require a database administrator. Since it is fairly user-friendly in this way, it can be used by both developers and administrators.
• Speed: It’s high-performing for simple queries.
• Flexibility: You can add new columns or fields on MongoDB without affecting existing rows or application performance.

So Which Database Is Right For Your Business?

MySQL is a strong choice for any business that will benefit from its pre-defined structure and set schemas. For example, applications that require multi-row transactions - like accounting systems or systems that monitor inventory - or that run on legacy systems will thrive with the MySQL structure.

MongoDB, on the other hand, is a good choice for businesses that have rapid growth or databases with no clear schema definitions. More specifically, if you cannot define a schema for your database, if you find yourself denormalizing data schemas, or if your schema continues to change - as is often the case with mobile apps, real-time analytics, content management systems, etc.- MongoDB can be a strong choice for you.