database 数据库中的水平分区和垂直分区是什么，有什么区别？

What are horizontal and vertical partitions in database and what is the difference?

提问by John Snowden

I read that

我读到了

SELECTis a horizontal partition of the relation into two set of tuples.

SELECT是将关系水平划分为两组元组。

and

和

PROJECTis a vertical partition of the relation into two relations.

PROJECT是将关系垂直划分为两个关系。

However, I don't understand what that means. Can you explain it in layman's terms?

但是，我不明白这是什么意思。你能通俗的解释一下吗？

回答by Kuldeep Jain

Not a complete answer to the question but it answers what is asked in the question title. So the general meaning of horizontal and vertical database partitioning is:

不是问题的完整答案，但它回答了问题标题中提出的问题。所以横向和纵向数据库分区的一般含义是：

Horizontal partitioninginvolves putting different rows into different tables. Perhaps customers with ZIP codes less than 50000 are stored in CustomersEast, while customers with ZIP codes greater than or equal to 50000 are stored in CustomersWest. The two partition tables are then CustomersEast and CustomersWest, while a view with a union might be created over both of them to provide a complete view of all customers.

水平分区涉及将不同的行放入不同的表中。也许邮政编码小于 50000 的客户存储在 CustomersEast 中，而邮政编码大于或等于 50000 的客户存储在 CustomersWest 中。这两个分区表是 CustomersEast 和 CustomersWest，而可能会在它们两个上创建一个带有联合的视图，以提供所有客户的完整视图。

Vertical partitioninginvolves creating tables with fewer columns and using additional tables to store the remaining columns. Normalization also involves this splitting of columns across tables, but vertical partitioning goes beyond that and partitions columns even when already normalized.

垂直分区涉及创建具有较少列的表并使用额外的表来存储剩余的列。规范化还涉及跨表的列拆分，但垂直分区超出了这一范围，即使已经规范化，也会对列进行分区。

See more details here.

在此处查看更多详细信息。

回答by T I

A projection creates a subset of attributes in a relation hence a "vertical partition"

投影在关系中创建属性子集，因此是“垂直分区”

A selection creates a subset of the tuples in a relation hence a "horizontal partition"

选择在关系中创建元组的子集，因此是“水平分区”

Given a table (r)as

给定一个表(r)作为

a : b : c : d : e
-----------------
1 : 2 : 3 : 4 : 5
1 : 2 : 3 : 4 : 5
2 : 2 : 3 : 4 : 5
2 : 2 : 3 : 4 : 5

An expression such as

一个表达式，例如

PROJECT a, b (SELECT a=1 (r))

-- SELECT a, b FROM r WHERE a=1

Would "do"

会做”

a : b | c : d : e
-----------------
1 : 2 | 3 : 4 : 5
1 : 2 | 3 : 4 : 5
=================    <  -- horizontal partition (by SELECTION)
2 : 2 | 3 : 4 : 5
2 : 2 | 3 : 4 : 5

      ^  -- vertical partition (by PROJECTION)

Resulting in

导致

a : b
------
1 : 2 
1 : 2 

回答by Stefan Steiger

Necromancing.
I think the existing answers are too abstract.

死灵法术。
我认为现有的答案太抽象了。

So here my attempts at a more practical explanation:

所以在这里我尝试更实际的解释：

Partitioning form a developer's point of view is all about performance.
More exactly, it's about what happens when you have large amounts of data in your tables, and you still want to query the data fast.

从开发人员的角度来看，分区是关于性能的。
更确切地说，它是关于当您的表中有大量数据并且您仍然希望快速查询数据时会发生什么。

Here some excerpts from slidesby Bill Karwinabout what exactly horizontalpartitioning is all about:

下面是Bill Karwin关于水平分区的全部内容的幻灯片的一些摘录：

The above is bad, because:

以上是不好的，因为：

The solution:

解决方案：

HORIZONTAL PARTITONING

水平分区

Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows.

水平分区将一个表分成多个表。然后每个表包含相同数量的列，但行数更少。

The difference: Query Performance and simplicity

区别：查询性能和简单性

Now, on the difference between horizontal and vertical partitioning:

现在，关于水平分区和垂直分区之间的区别：

"Tribbles" can also accumulate in columns. Example:

“Tribbles”也可以在列中累积。例子：

The solution to that problem is VERTICAL PARTITIONING
Proper normalization is ONE form of vertical partitioning

该问题的解决方案是垂直分区
正确的归一化是垂直分区的一种形式

To quote technet

引用technet

Vertical partitioning divides a table into multiple tables that contain fewer columns.

The two types of vertical partitioning are normalization and row splitting:

Normalization is the standard database process of removing redundant columns from a table and putting them in secondary tables that are linked to the primary table by primary key and foreign key relationships.

Row splitting divides the original table vertically into tables with fewer columns. Each logical row in a split table matches the same logical row in the other tables as identified by a UNIQUE KEY column that is identical in all of the partitioned tables. For example, joining the row with ID 712 from each split table re-creates the original row. Like horizontal partitioning, vertical partitioning lets queries scan less data. This increases query performance. For example, a table that contains seven columns of which only the first four are generally referenced may benefit from splitting the last three columns into a separate table. Vertical partitioning should be considered carefully, because analyzing data from multiple partitions requires queries that join the tables.

Vertical partitioning also could affect performance if partitions are very large.

垂直分区将一个表划分为包含较少列的多个表。

两种类型的垂直分区是规范化和行拆分：

规范化是从表中删除冗余列并将它们放在通过主键和外键关系链接到主表的辅助表中的标准数据库过程。

行拆分将原始表垂直划分为列数较少的表。拆分表中的每个逻辑行都与其他表中的相同逻辑行相匹配，这些行由在所有分区表中相同的 UNIQUE KEY 列标识。例如，连接每个拆分表中 ID 为 712 的行会重新创建原始行。与水平分区一样，垂直分区允许查询扫描更少的数据。这提高了查询性能。例如，一个包含七列的表，通常只引用前四列可能会受益于将最后三列拆分为单独的表。应仔细考虑垂直分区，因为分析来自多个分区的数据需要连接表的查询。

如果分区非常大，垂直分区也会影响性能。

That sums it up nicely.

这总结得很好。

Now on SELECT vs. PROJECT:

现在关于 SELECT 与 PROJECT：

This SO postdescribes the difference as such:

这个 SO 帖子描述了这样的区别：

Select Operation :This operation is used to select rows from a table (relation) that specifies a given logic, which is called as a predicate. The predicate is a user defined condition to select rows of user's choice.

Project Operation :If the user is interested in selecting the values of a few attributes, rather than selection all attributes of the Table (Relation), then one should go for PROJECTOperation.

选择操作：此操作用于从指定给定逻辑的表（关系）中选择行，称为 predicate. 谓词是用户定义的条件，用于选择用户选择的行。

项目操作：如果用户有兴趣选择几个属性的值，而不是选择表（关系）的所有属性，那么应该选择PROJECT操作。

SELECT is an actual SQL operation (statement), while PROJECT is a term used in relational algebra.

SELECT 是实际的 SQL 操作（语句），而 PROJECT 是关系代数中使用的术语。

Judging from you posting this on SO and not on MathOverflow, I would suggest you don't read relational algebra books if you just want to learn SQL for developing applications.

从你在 SO 上而不是在 MathOverflow 上发帖来看，如果你只是想学习用于开发应用程序的 SQL，我建议你不要阅读关系代数书籍。

If you are in dire need of a recommendation for a goodbook about (advanced) SQL, here is one

如果你是在一个建议的迫切需要良好的约（书高级）SQL，这里是一个

SQL Antipatterns: Avoiding the Pitfalls of Database Programming
Bill Karwin
ISBN-13: 978-1934356555
ISBN-10: 1934356557

SQL 反模式：避免数据库编程的陷阱
Bill Karwin
ISBN-13：978-1934356555
ISBN-10：1934356557

That's the one book about SQL worth reading.
Most other books about SQL that I've seen out there can be summed up by this cynical statement about photoshop books:

这是一本值得一读的关于 SQL 的书。
我在那里看到的大多数其他关于 SQL 的书籍都可以用这个关于 Photoshop 书籍的愤世嫉俗的声明来总结：

There are more books about photoshop than people actually using photoshop.

关于 photoshop 的书籍比实际使用 photoshop 的人还多。

回答by djna

Consider a single table in a database, it has some rows and columns.

考虑数据库中的单个表，它有一些行和列。

There are two ways your could pick data: You could pick some rows, or you could pick some columns (well ok, three ways, you could pick some rows, and within that pick some columns.)

您可以通过两种方式选择数据：您可以选择一些行，或者您可以选择一些列（好吧，三种方式，您可以选择一些行，然后在其中选择一些列。）

You can think of select as picking some rows - that's horizontal (and not picking the rest, hence partitioning)

您可以将选择视为选择一些行 - 这是水平的（而不是选择其余行，因此进行分区）

You can think of project as picking some columns - that's vertical (and not picking the rest)

您可以将项目视为选择一些列 - 这是垂直的（而不是选择其余的）

回答by Krutik

The distinction of horizontal vs vertical comes from the traditional tabular view of a database. A database can be split vertically— storing different tables & columns in a separate database or horizontally— storing rows of a same table in multiple database nodes.

水平与垂直的区别来自数据库的传统表格视图。一个数据库可以垂直拆分——将不同的表和列存储在单独的数据库中，或者水平拆分——将 同一表的行存储在多个数据库节点中。

Horizontal partitioning is often referred as Database Sharding.

水平分区通常称为数据库分片。

# Example of vertical partitioning
fetch_user_data(user_id) -> db[“USER”].fetch(user_id)
fetch_photo(photo_id) ->    db[“PHOTO”].fetch(photo_id)
# Example of horizontal partitioning
fetch_user_data(user_id) -> user_db[user_id % 2].fetch(user_id)

Find more details here: https://medium.com/@jeeyoungk/how-sharding-works-b4dec46b3f6

在此处查找更多详细信息：https: //medium.com/@jeeyoungk/how-sharding-works-b4dec46b3f6