什么是数据库中的水平分区和垂直分区,有什么区别?[英] What are horizontal and vertical partitions in database and what is the difference?

本文是小编为大家收集整理的关于什么是数据库中的水平分区和垂直分区,有什么区别?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我读了

SELECT是将关系的水平分区分成两组.

PROJECT是关系分为两种关系的垂直分区.

但是,我不明白这意味着什么.你能用外行的话来解释吗?

推荐答案

不是对问题的完整答案,但它回答了问题标题中所要求的内容.因此,水平和垂直数据库分区的一般含义是:

水平分区涉及将不同的行放入不同的表中.也许邮政编码少于50000的客户存储在顾客中,而邮政编码大于或等于50000的客户存储在客户west中.然后,这两个分区表是客户和客户,而与工会的视图可能会在他们两个上创建,以提供所有客户的完整视图.

垂直分区涉及创建较少列的表,并使用其他表来存储其余列.归一化还涉及跨表的列分裂,但是垂直分区超出了这一范围,即使在已经进行了归一化的情况下,分区也可以分区.

.

请参阅更多详细信息在这里.

其他推荐答案

投影在关系中创建属性的子集,因此"垂直分区"

选择在关系中创建一个元组的子集,因此"水平分区"

给出一个表(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

诸如

之类的表达式
PROJECT a, b (SELECT a=1 (r))

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

会"做"

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)

导致

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

其他推荐答案

坏死.
我认为现有的答案太抽象了.

所以我在这里尝试了一个更实用的解释:

划分形成开发人员的观点与性能有关.
更确切地说,这是关于当您表中有大量数据时会发生什么,并且您仍然想快速查询数据.

在这里从Slides href =" https://stackoverflow.com/users/20860/bill-karwin"> Bill Karwin 关于 horizo​​ntal 分区的确切含义是:

可怕

上面是不好的,因为:

问题1

问题2

解决方案:

horizo​​ntal partitonaling

水平分区将表分为多个表. 然后,每个表包含相同数量的列,但行较少.

解决方案

差异:查询性能和简单

 kirk Quote




现在,关于水平和垂直分区之间的差异:

" trabbles"也可以在列中积累. 例子: 列Tribble

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

报价Technet

垂直分区将表格分为多个表 包含更少的列.

两种类型的垂直分区是 归一化和行分裂:

标准化是标准 从表和 将它们放在链接到主表的辅助表中 通过主要钥匙和外国密钥关系.

行分割划分 原始表垂直成列的表格.每个 拆分表中的逻辑行与另一个相同的逻辑行匹配 由唯一键列确定的表 隔断表.例如,使用ID 712加入行 从每个拆分表中重新创建原始行.喜欢水平 分区,垂直分区使查询扫描更少的数据.这个 提高查询性能.例如,一个包含七个的表 通常只引用前四个的列可以 将最后三列分为单独的表中受益. 垂直分区应仔细考虑,因为 分析来自多个分区的数据需要加入的查询 表.

垂直分区也可能影响性能 分区很大.

可以很好地总结它.

正确的归一化

现在在选择与项目上:

so Post 这样:

选择操作:此操作用于从表(关系)中选择指定给定逻辑的行,该行被称为一个 predicate.谓词是用户定义的条件,可以选择行 用户的选择.

项目操作:如果用户有兴趣选择一些属性的值,而不是选择所有属性的所有属性 表(关系),然后应该进行PROJECT操作.

选择是实际的SQL操作(语句),而项目是关系代数中使用的术语.

从您在SO上发布此信息,而不是在Mathoverflow上,我建议您不要阅读关系代数书籍,如果您只想学习用于开发应用程序的SQL.

如果您迫切需要建议A good ( Advanced )SQL,这是一个

SQL Antipatterns:避免数据库编程的陷阱
Bill Karwin
ISBN-13:978-1934356555
ISBN-10:1934356557

那是关于SQL值得阅读的一本书.
我在那里看到的大多数其他有关SQL的书都可以用有关Photoshop书籍的愤世嫉俗的陈述来概括:

关于Photoshop的书籍比实际使用Photoshop的人更多.

本文地址:https://www.itbaoku.cn/post/597512.html

问题描述

I read that

SELECT is a horizontal partition of the relation into two set of tuples.

and

PROJECT is a vertical partition of the relation into two relations.

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

推荐答案

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 partitioning involves 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.

Vertical partitioning involves 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.

其他推荐答案

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

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 

其他推荐答案

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 slides by Bill Karwin about what exactly horizontal partitioning is all about:

Horrible

The above is bad, because:

Problem 1

Problem 2

The solution:

HORIZONTAL PARTITONING

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

solution horizontal

The difference: Query Performance and simplicity

Kirk Quote




Now, on the difference between horizontal and vertical partitioning:

"Tribbles" can also accumulate in columns. Example: Column Tribble

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

To quote 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.

That sums it up nicely.

Proper normalization

Now on SELECT vs. PROJECT:

This SO post describes the difference as such:

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 PROJECT Operation.

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

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.

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

SQL Antipatterns: Avoiding the Pitfalls of Database Programming
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:

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