sql与nosql_SQL与NoSQL：差异

最新推荐文章于 2024-09-24 08:39:48 发布

culh2177

最新推荐文章于 2024-09-24 08:39:48 发布

阅读量272

点赞数

文章标签：数据库大数据 python mysql java

原文链接：https://www.sitepoint.com/sql-vs-nosql-differences/

版权

sql与nosql

SQL与NoSQL的圣战 (The SQL vs NoSQL Holy War)

Before we go further, let’s dispel a number of myths …

在继续之前，让我们消除一些神话……

MYTH: NoSQL supersedes SQL That would be like saying boats were superseded by cars because they’re a newer technology. SQL and NoSQL do the same thing: store data. They take different approaches, which may help or hinder your project. Despite feeling newer and grabbing recent headlines, NoSQL is not a replacement for SQL — it’s an alternative.

误区：NoSQL取代SQL这就像说船被汽车取代，因为它们是一种较新的技术。 SQL和NoSQL做相同的事情：存储数据。他们采用不同的方法，这可能有助于或阻碍您的项目。尽管感到新奇并吸引了最近的头条新闻，但NoSQL并不是SQL的替代品， 而是一种替代品 。

MYTH: NoSQL is better / worse than SQL Some projects are better suited to using an SQL database. Some are better suited to NoSQL. Some could use either interchangeably. This article could never be a SitePoint Smackdown, because you cannot apply the same blanket assumptions everywhere.

误区：NoSQL比SQL更好/更差一些项目更适合使用SQL数据库。有些更适合NoSQL。有些可以互换使用。本文决不能成为SitePoint的Smackdown，因为您不能在所有地方都采用相同的假设。

MYTH: SQL vs NoSQL is a clear distinction This is not necessarily true. Some SQL databases are adopting NoSQL features and vice versa. The choices are likely to become increasingly blurred, and NewSQL hybrid databases could provide some interesting options in the future.

误区：SQL与NoSQL有明显区别这不一定是正确的。某些SQL数据库采用NoSQL功能，反之亦然。这些选择可能会变得越来越模糊，并且NewSQL混合数据库将来可能会提供一些有趣的选项。

MYTH: the language/framework determines the database We’ve grown accustom to technology stacks, such as —

误区：语言/框架决定了数据库我们已经习惯了技术堆栈，例如-

LAMP: Linux, Apache, MySQL (SQL), PHP
灯：Linux，Apache，MySQL(SQL)，PHP
MEAN: MongoDB (NoSQL), Express, Angular, Node.js
内容：MongoDB(NoSQL)，Express，Angular，Node.js
.NET, IIS and SQL Server
.NET，IIS和SQL Server
Java, Apache and Oracle.
Java，Apache和Oracle。

There are practical, historical and commercial reasons why these stacks evolved — but don’t presume they are rules. You can use a MongoDB NoSQL database in your PHP or .NET project. You can connect to MySQL or SQL Server in Node.js. You may not find as many tutorials and resources, but your requirements should determine the database type — not the language.

这些堆栈的演变有实际，历史和商业原因，但不要以为是规则。您可以在PHP或.NET项目中使用MongoDB NoSQL数据库。您可以在Node.js中连接到MySQL或SQL Server 。您可能找不到太多的教程和资源，但是您的需求应该确定数据库类型， 而不是语言 。

(That said, don’t make life purposely difficult for yourself! Choosing an unusual technology combination or a mix of SQL and NoSQL is possible, but you’ll find it tougher to find support and employ experienced developers.)

(也就是说，不要让生活变得刻意为难！选择一种不寻常的技术组合或SQL和NoSQL的混合是可能的，但是您会发现很难找到支持并雇用有经验的开发人员。)

With that in mind, let’s look at the primary differences …

考虑到这一点，让我们看一下主要区别……

SQL表与NoSQL文档 (SQL Tables vs NoSQL Documents)

SQL databases provide a store of related data tables. For example, if you run an online book store, book information can be added to a table named book:

SQL数据库提供了相关数据表的存储。例如，如果您经营一个在线书店，则可以将书信息添加到名为book的表中：

ISBN	title	author	format	price
9780992461225	JavaScript: Novice to Ninja	Darren Jones	ebook	29.00
9780994182654	Jump Start Git	Shaumik Daityari	ebook	29.00

书号	标题	作者	格式	价钱
9780992461225	JavaScript：忍者新手	达伦·琼斯(Darren Jones)	电子书	29.00
9780994182654	快速启动Git	Shaumik Daityari	电子书	29.00

Every row is a different book record. The design is rigid; you cannot use the same table to store different information or insert a string where a number is expected.

每行都是不同的记录。设计是刚性的；您不能使用同一张表存储不同的信息，也不能在需要数字的地方插入字符串。

NoSQL databases store JSON-like field-value pair documents, e.g.

NoSQL数据库存储类似JSON的字段-值对文档，例如

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  format: "ebook",
  price: 29.00
}

Similar documents can be stored in a collection, which is analogous to an SQL table. However, you can store any data you like in any document; the NoSQL database won’t complain. For example:

可以将类似的文档存储在一个集合中 ，该集合类似于一个SQL表。但是，您可以将所需的任何数据存储在任何文档中。 NoSQL数据库不会抱怨。例如：

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  year: 2014,
  format: "ebook",
  price: 29.00,
  description: "Learn JavaScript from scratch!",
  rating: "5/5",
  review: [
    { name: "A Reader", text: "The best JavaScript book I've ever read." },
    { name: "JS Expert", text: "Recommended to novice and expert developers alike." }
  ]
}

SQL tables create a strict data template, so it’s difficult to make mistakes. NoSQL is more flexible and forgiving, but being able to store any data anywhere can lead to consistency issues.

SQL表创建了严格的数据模板，因此很难出错。 NoSQL更加灵活和宽容，但是能够将任何数据存储在任何地方都可能导致一致性问题。

SQL模式与NoSQL模式无 (SQL Schema vs NoSQL Schemaless)

In an SQL database, it’s impossible to add data until you define tables and field types in what’s referred to as a schema. The schema optionally contains other information, such as —

在SQL数据库中，除非您以所谓的schema定义表和字段类型，否则无法添加数据。模式可以选择包含其他信息，例如-

primary keys — unique identifiers such as the ISBN which apply to a single record
主键 -适用于单个记录的唯一标识符，例如ISBN
indexes — commonly queried fields indexed to aid quick searching
索引 - 索引通常查询的字段以帮助快速搜索
relationships — logical links between data fields
关系 —数据字段之间的逻辑链接
functionality such as triggers and stored procedures.
触发器和存储过程等功能 。

Your data schema must be designed and implemented before any business logic can be developed to manipulate data. It’s possible to make updates later, but large changes can be complicated.

必须先设计和实现数据模式，然后才能开发任何业务逻辑来处理数据。以后可以进行更新，但是大的更改可能很复杂。

In a NoSQL database, data can be added anywhere, at any time. There’s no need to specify a document design or even a collection up-front. For example, in MongoDB the following statement will create a new document in a new book collection if it’s not been previously created:

在NoSQL数据库中，可以随时随地添加数据。无需预先指定文档设计甚至是集合。例如，在MongoDB中，以下语句将在以前未创建的新book集中创建一个新文档：

db.book.insert(
  ISBN: 9780994182654,
  title: "Jump Start Git",
  author: "Shaumik Daityari",
  format: "ebook",
  price: 29.00
);

(MongoDB will automatically add a unique _id value to each document in a collection. You may still want to define indexes, but that can be done later if necessary.)

(MongoDB会自动向集合中的每个文档添加唯一的_id值。您可能仍想定义索引，但是以后可以根据需要进行操作。)

A NoSQL database may be more suited to projects where the initial data requirements are difficult to ascertain. That said, don’t mistake difficulty for laziness: neglecting to design a good data store at project commencement will lead to problems later.

NoSQL数据库可能更适合于难以确定初始数据需求的项目。就是说，不要把懒惰的困难误认为是错误的：在项目开始时忽略设计一个好的数据存储将导致以后的问题。

SQL规范化与NoSQL反规范化 (SQL Normalization vs NoSQL Denormalization)

Presume we want to add publisher information to our book store database. A single publisher could offer more than one title so, in an SQL database, we create a new publisher table:

假设我们想将出版商信息添加到我们的书店数据库中。一个发布者可以提供多个标题，因此，在SQL数据库中，我们创建了一个新的publisher表：

id	name	country	email
SP001	SitePoint	Australia	feedback@sitepoint.com

ID	名称	国家	电子邮件
SP001	SitePoint	澳大利亚	feedback@sitepoint.com

We can then add a publisher_id field to our book table, which references records by publisher.id:

然后，我们可以将一个publisher_id字段添加到我们的book表中，该字段按publisher.id引用记录：

ISBN	title	author	format	price	publisher_id
9780992461225	JavaScript: Novice to Ninja	Darren Jones	ebook	29.00	SP001
9780994182654	Jump Start Git	Shaumik Daityari	ebook	29.00	SP001

书号	标题	作者	格式	价钱	Publisher_id
9780992461225	JavaScript：忍者新手	达伦·琼斯(Darren Jones)	电子书	29.00	SP001
9780994182654	快速启动Git	Shaumik Daityari	电子书	29.00	SP001

This minimizes data redundancy; we’re not repeating the publisher information for every book — only the reference to it. This technique is known as normalization, and has practical benefits. We can update a single publisher without changing book data.

这样可以最大程度地减少数据冗余；我们不会为每本书重复出版商信息，而只是重复参考。该技术被称为规范化，具有实际的好处。我们可以更新单个出版商，而无需更改book数据。

We can use normalization techniques in NoSQL. Documents in the book collection —

我们可以在NoSQL中使用规范化技术。文件在book收集-

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  format: "ebook",
  price: 29.00,
  publisher_id: "SP001"
}

— reference a document in a publisher collection:

—引用publisher集合中的文档：

{
  id: "SP001"
  name: "SitePoint",
  country: "Australia",
  email: "feedback@sitepoint.com"
}

However, this is not always practical, for reasons that will become evident below. We may opt to denormalize our document and repeat publisher information for every book:

然而，由于下面将变得显而易见的原因，这并不总是可行的。我们可能会选择对文档进行非规范化处理，并对每本书重复发布者信息：

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  format: "ebook",
  price: 29.00,
  publisher: {
    name: "SitePoint",
    country: "Australia",
    email: "feedback@sitepoint.com"
  }
}

This leads to faster queries, but updating the publisher information in multiple records will be significantly slower.

这将导致更快的查询，但是更新多个记录中的发布者信息将明显变慢。

SQL关系联接vs NoSQL (SQL Relational JOIN vs NoSQL)

SQL queries offer a powerful JOIN clause. We can obtain related data in multiple tables using a single SQL statement. For example:

SQL查询提供了强大的JOIN子句。我们可以使用一个SQL语句在多个表中获取相关数据。例如：

SELECT book.title, book.author, publisher.name
FROM book
LEFT JOIN book.publisher_id ON publisher.id;

This returns all book titles, authors and associated publisher names (presuming one has been set).

这将返回所有书名，作者和相关的出版商名称(假定已设置一个)。

NoSQL has no equivalent of JOIN, and this can shock those with SQL experience. If we used normalized collections as described above, we would need to fetch all book documents, retrieve all associated publisher documents, and manually link the two in our program logic. This is one reason denormalization is often essential.

NoSQL没有等效的JOIN，这会使具有SQL经验的人震惊。如果我们如上所述使用归一化的馆藏，则需要获取所有book文档，检索所有关联的publisher文档，并在程序逻辑中手动链接两者。这是非正规化经常必不可少的原因之一。

SQL与NoSQL数据完整性 (SQL vs NoSQL Data Integrity)

Most SQL databases allow you to enforce data integrity rules using foreign key constraints (unless you’re still using the older, defunct MyISAM storage engine in MySQL). Our book store could —

大多数SQL数据库允许您使用外键约束来强制执行数据完整性规则(除非您仍在MySQL中使用旧的，已失效的MyISAM存储引擎)。我们的书店可以-

ensure all books have a valid publisher_id code that matches one entry in the publisher table, and
确保所有书籍都有有效的publisher_id代码，该代码与publisher表中的一项匹配，并且
not permit publishers to be removed if one or more books are assigned to them.
如果分配了一本或多本图书，则不允许删除出版商。

The schema enforces these rules for the database to follow. It’s impossible for developers or users to add, edit or remove records, which could result in invalid data or orphan records.

该模式强制这些规则供数据库遵循。开发人员或用户无法添加，编辑或删除记录，这可能导致无效的数据或孤立的记录。

The same data integrity options are not available in NoSQL databases; you can store what you want regardless of any other documents. Ideally, a single document will be the sole source of all information about an item.

相同的数据完整性选项在NoSQL数据库中不可用。您可以存储所需的内容，而不管其他任何文档。理想情况下，单个文档将是有关项目的所有信息的唯一来源。

SQL与NoSQL事务 (SQL vs NoSQL Transactions)

In SQL databases, two or more updates can be executed in a transaction — an all-or-nothing wrapper that guarantees success or failure. For example, presume our book store contained order and stock tables. When a book is ordered, we add a record to the order table and decrement the stock count in the stock table. If we execute those two updates individually, one could succeed and the other fail — thus leaving our figures out of sync. Placing the same updates within a transaction ensures either both succeed or both fail.

在SQL数据库中，可以在一个事务中执行两个或多个更新-保证成功或失败的全有或全无包装。例如，假设我们的书店包含order和stock表。订购书籍后，我们将记录添加到order表中，并减少stock表中的库存数量。如果我们分别执行这两个更新，那么一个更新可能成功而另一个失败—因此，我们的数据不同步。在事务中放置相同的更新可确保成功或失败。

In a NoSQL database, modification of a single document is atomic. In other words, if you’re updating three values within a document, either all three are updated successfully or it remains unchanged. However, there’s no transaction equivalent for updates to multiple documents. There are transaction-like options, but, at the time of writing, these must be manually processed in your code.

在NoSQL数据库中，单个文档的修改是原子的。换句话说，如果您要更新文档中的三个值，则这三个值都将成功更新或保持不变。但是，没有等效于更新多个文档的事务。有类似事务的选项，但是在编写本文时，必须在代码中手动处理这些选项。

SQL vs NoSQL CRUD语法 (SQL vs NoSQL CRUD Syntax)

Creating, reading updating and deleting data is the basis of all database systems. In essence —

创建，读取更新和删除数据是所有数据库系统的基础。在本质上 -

SQL is a lightweight declarative language. It’s deceptively powerful, and has become an international standard, although most systems implement subtly different syntaxes.
SQL是一种轻量级的声明性语言。尽管大多数系统实现的语法略有不同，但它具有看似强大的功能，并已成为国际标准。
NoSQL databases use JavaScripty-looking queries with JSON-like arguments! Basic operations are simple, but nested JSON can become increasingly convoluted for more complex queries.
NoSQL数据库使用带有类JSON参数JavaScripty查询！基本操作很简单，但是对于更复杂的查询，嵌套JSON可能变得越来越复杂。

A quick comparison:

快速比较：

SQL	NoSQL
insert a new book record
INSERT INTO book ( `ISBN`, `title`, `author` ) VALUES ( '9780992461256', 'Full Stack JavaScript', 'Colin Ihrig & Adam Bretz' );
update a book record
`UPDATE book SET price = 19.99 WHERE ISBN = '9780992461256'`
return all book titles over $10
`SELECT title FROM book WHERE price > 10;`	The second JSON object is known as a projection: it sets which fields are returned (`_id` is returned by default so it needs to be unset).
count the number of SitePoint books
`SELECT COUNT(1) FROM book WHERE publisher_id = 'SP001';`	This presumes denormalized documents are used.
return the number of book format types
SELECT format, COUNT(1) AS `total` FROM book GROUP BY format;	This is known as aggregation: a new set of documents is computed from an original set.
delete all SitePoint books
`DELETE FROM book WHERE publisher_id = 'SP001';` Alternatively, it’s possible to delete the `publisher` record and have this cascade to associated `book` records if foreign keys are specified appropriately.

SQL	NoSQL
插入新书记录
INSERT INTO book ( `ISBN`, `title`, `author` ) VALUES ( '9780992461256', 'Full Stack JavaScript', 'Colin Ihrig & Adam Bretz' );
更新帐簿记录
`UPDATE book SET price = 19.99 WHERE ISBN = '9780992461256'`
返回所有超过$ 10的书名
`SELECT title FROM book WHERE price > 10;`	第二个JSON对象称为投影：它设置返回哪些字段(默认情况下返回`_id` ，因此需要取消设置)。
计算SitePoint图书的数量
`SELECT COUNT(1) FROM book WHERE publisher_id = 'SP001';`	假定使用了非规范化文档。
返回书籍格式类型的数量
SELECT format, COUNT(1) AS `total` FROM book GROUP BY format;	这称为聚合：从原始集中计算出一组新的文档。
删除所有SitePoint图书
`DELETE FROM book WHERE publisher_id = 'SP001';` 另外，如果适当地指定了外键，则可以删除`publisher`记录并将其级联到相关的`book`记录中。

SQL vs NoSQL性能 (SQL vs NoSQL Performance)

Perhaps the most controversial comparison, NoSQL is regularly quoted as being faster than SQL. This isn’t surprising; NoSQL’s simpler denormalized store allows you to retrieve all information about a specific item in a single request. There’s no need for related JOINs or complex SQL queries.

也许最具争议的比较是，NoSQL通常被引用为比SQL更快。这不足为奇。 NoSQL的更简单的非规范化存储使您可以在单个请求中检索有关特定项目的所有信息。不需要相关的JOIN或复杂SQL查询。

That said, your project design and data requirements will have most impact. A well-designed SQL database will almost certainly perform better than a badly designed NoSQL equivalent and vice versa.

也就是说，您的项目设计和数据要求将产生最大的影响。设计良好SQL数据库几乎可以肯定比设计不良的NoSQL具有更好的性能，反之亦然。

SQL与NoSQL扩展 (SQL vs NoSQL Scaling)

As your data grows, you may find it necessary to distribute the load among multiple servers. This can be tricky for SQL-based systems. How do you allocate related data? Clustering is possibly the simplest option; multiple servers access the same central store — but even this has challenges.

随着数据的增长，您可能发现有必要在多台服务器之间分配负载。对于基于SQL的系统，这可能很棘手。您如何分配相关数据？群集可能是最简单的选择。多个服务器访问同一个中央存储-但这仍然带来了挑战。

NoSQL’s simpler data models can make the process easier, and many have been built with scaling functionality from the start. That is a generalization, so seek expert advice if you encounter this situation.

NoSQL的简单数据模型可以使过程更轻松，并且许多模型从一开始就具有扩展功能。这是一个概括，如果遇到这种情况，请寻求专家的建议。

SQL与NoSQL实用性 (SQL vs NoSQL Practicalities)

Finally, let’s consider security and system problems. The most popular NoSQL databases have been around a few years; they are more likely to exhibit issues than more mature SQL products. Many problems have been reported, but most boil down to a single issue: knowledge.

最后，让我们考虑安全性和系统问题。最受欢迎的NoSQL数据库已经有几年了。与更成熟SQL产品相比，它们更有可能出现问题。很多问题都被报道，但大多归结为一个问题：知识。

Developers and sysadmins have less experience with newer database systems, so mistakes are made. Opting for NoSQL because it feels fresher, or because you want to avoid schema design inevitably, leads to problems later.

开发人员和系统管理员对较新的数据库系统缺乏经验，因此会出错。选择NoSQL是因为它感觉较新，或者因为您不可避免地要避免进行架构设计，这会在以后导致问题。

SQL vs NoSQL总结 (SQL vs NoSQL Summary)

SQL and NoSQL databases do the same thing in different ways. It’s possible choose one option and switch to another later, but a little planning can save time and money.

SQL和NoSQL数据库以不同的方式执行相同的操作。可以选择一个选项，以后再切换到另一个选项，但是稍作计划可以节省时间和金钱。

Projects where SQL is ideal:

最适合使用SQL的项目：

logical related discrete data requirements which can be identified up-front
可以预先确定与逻辑相关的离散数据需求
data integrity is essential
数据完整性至关重要
standards-based proven technology with good developer experience and support.
基于标准的成熟技术，具有良好的开发人员经验和支持。

Projects where NoSQL is ideal:

NoSQL最理想的项目：

unrelated, indeterminate or evolving data requirements
不相关，不确定或不断变化的数据要求
simpler or looser project objectives, able to start coding immediately
更简单或更松散的项目目标，能够立即开始编码
speed and scalability is imperative.
速度和可伸缩性势在必行。

In the case of our book store, an SQL database appears the most practical option — especially when we introduce ecommerce facilities requiring robust transaction support. In the next article, we’ll discuss further project scenarios, and determine whether an SQL or NoSQL database would be the best solution.

就我们的书店而言，SQL数据库似乎是最实用的选择，尤其是当我们引入需要强大交易支持的电子商务设施时。在下一篇文章中，我们将讨论更多的项目方案，并确定SQL还是NoSQL数据库将是最佳解决方案。