MySQL SQL中基数的定义是什么

What is the definition of cardinality in SQL

提问by Tobias Schultz

My school book Database Systemsdefines cardinality as follows:

我的教科书数据库系统将基数定义如下：

The cardinality of a relation is the number of tuples it contains. By contrast, the number of tuples is called the cardinality of the relation and this changes as tuples are added or deleted. High-cardinality - many tuples, low-cardinality - few tuples.

关系的基数是它包含的元组数。相比之下，元组的数量称为关系的基数，并且随着元组的添加或删除而变化。高基数 - 许多元组，低基数 - 很少的元组。

While the Wikipedia article on Cardinality (SQL statements), defines it as follows:

而维基百科关于Cardinality (SQL statements) 的文章，将其定义如下：

Cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table. The lower the cardinality, the more duplicated elements in a column. There are 3 types of cardinality: high-cardinality, normal-cardinality, and low-cardinality.

基数是指包含在数据库表的特定列（属性）中的数据值的唯一性。基数越低，列中重复的元素越多。有 3 种类型的基数：高基数、正常基数和低基数。

They might both be right, but i can't connect the two definitions as related definitions. A rephrase would be appriciated!

他们可能都是对的，但我无法将这两个定义连接为相关定义。改写将是appriciated！

回答by Edper

They are speaking the same thing and it has to do with tuples (relational algebra) or rows (layman's term).

他们说的是同一件事，它与元组 ( relational algebra) 或行（外行的术语）有关。

When it says high-cardinality are possible values of particular attribute (or field) that are unique and therefore the number of rows or tuples are higher:

当它说高基数是特定属性（或字段）的可能值时，它们是唯一的，因此行数或元组数更高：

Example:

示例：

 StudentID   Lastname Firstname  Gender
 101         Smith    John       M
 102         Jones    James      M
 103         Mayo     Ann        F
 104         Jones    George     M
 105         Smith    Suse       F

As far as as StudentIDthe cardinalityis highbecause it is unique. In this it has five (5) tuples/rows.

至于作为StudentID的cardinality是high，因为它是独一无二的。其中它有五 (5) 个元组/行。

On the other hand Lastnamehas normal cardinality, in particular there are only three (3) unique tuples/rows. Thus it has normal cardinality.

另一方面，姓氏具有正常的基数，特别是只有三 (3) 个唯一的元组/行。因此它有normal cardinality。

And finally Genderhas only two possible unique tuples thus Low Cardinality.

最后Gender只有两个可能的唯一元组Low Cardinality。

You probably confuse Cardinalityhere with Degreeof a relation which has something to do of the number of attributes/fieldsin a relation (or table).

您可能Cardinality在这里混淆了与关系（或表）Degree中的数量有关attributes/fields的关系。

On the other hand the textbook for Databasewhen speaking of Cardinalitynormally has to do with an entity in relation to another entity, that is, the number of possible relations occurences for an entity participating in a given relationship type. Thus for example for a binary relationshipcardinalitycould be either one-to-one, one-to-manyor many-to-many.

另一方面Database，Cardinality通常讲的教科书与一个实体与另一个实体的关系有关，即参与给定关系类型的实体可能出现的关系数。因此，例如 abinary relationshipcardinality可以是one-to-one,one-to-many或many-to-many。

回答by Gordon Linoff

Both definitions are trying to say that cardinality is the "number of rows". The difference is whether the comparison is "in the table" or "in a particular column".

这两个定义都试图说明基数是“行数”。区别在于比较是“在表中”还是“在特定列中”。

The version in your database text book focuses on relational algebra and the structure of tables ("relations" in that lingo).

您的数据库教科书中的版本侧重于关系代数和表的结构（该行话中的“关系”）。

The Wikipedia entry is more practical. It encompasses the textbook definition, assuming the table has a primary key (the cardinality of the primary key is the same as the table). However, it can be applied to, say, a flag column as well. If the flag only takes on two values (0versus 1), then we can say that the cardinality of the column is 2.

维基百科词条更实用。它包含教科书定义，假设表有一个主键（主键的基数与表相同）。但是，它也可以应用于例如标志列。如果标志只有两个值（0vs 1），那么我们可以说列的基数是 2。

This is important for optimizing queries. Cardinality is one component of choosing the best methods for joining, aggregating, and selecting data. In practice, most databases use more information than the cardinality, so-called "statistics" about columns and their values for optimization.

这对于优化查询很重要。基数是选择连接、聚合和选择数据的最佳方法的一个组成部分。在实践中，大多数数据库使用比基数更多的信息，即关于列及其优化值的所谓“统计”。

回答by alexander zak

If we have tables A and B, think of cardinality as the number of rows of table B that would be related to a row from table A. If the tables are PERSON and VEHICLE, and the relation is RODE_ON then the cardinality is high because most persons rode on lots of different vehicles in the past, and most vehicles drove many persons. If the relation is OWNS then the cardinality is low - most persons own one vehicle, some own none, and a vehicle usually has one or two owners, not more.

如果我们有表 A 和 B，将基数视为表 B 中与表 A 中的行相关的行数。如果表是 PERSON 和 VEHICLE，并且关系是 RODE_ON，则基数很高，因为大多数过去人们乘坐许多不同的车辆，大多数车辆都载着许多人。如果关系是 OWNS，那么基数就低——大多数人拥有一辆车，有些人没有，一辆车通常有一个或两个所有者，而不是更多。

Note that cardinality from one side of a relation is not equal to cardinality from the other side. If the tables are PERSON and FINGER, and the relation is BELONG TO, then a person has many fingers, but each finger belongs to only one person.

请注意，关系一侧的基数不等于另一侧的基数。如果表是PERSON和FINGER，关系是BELONG TO，那么一个人有很多根手指，但每个手指只属于一个人。

回答by VAI Jason

There are two concepts

有两个概念

1. Index cardinality
2. Cardinality

1. 索引基数
2. 基数

This I believe is referring to index cardinality, which is VERY different https://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

我认为这是指索引基数，这是非常不同的 https://www.ibm.com/developerworks/data/library/techarticle/dm-1309cardinal/

• Index cardinality is considered the number of unique values in the index
• The term is used to discuss creating indexes, table scans, index access vs table access, how it affects inserts, updates, deletes, storage space

• 索引基数被认为是索引中唯一值的数量
• 该术语用于讨论创建索引、表扫描、索引访问与表访问、它如何影响插入、更新、删除、存储空间

Here is another example, https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)

这是另一个例子， https://en.wikipedia.org/wiki/Cardinality_(SQL_statements)

• In SQL (Structured Query Language), the term cardinality refers to the uniqueness of data values contained in a particular column (attribute) of a database table.
• The lower the cardinality, the more duplicated elements in a column. Thus, a column with the lowest possible cardinality would have the same value for every row. SQL databases use cardinality to help determine the optimal query plan for a given query.

• 在 SQL（结构化查询语言）中，术语基数是指包含在数据库表的特定列（属性）中的数据值的唯一性。
• 基数越低，列中重复的元素越多。因此，具有最低基数的列的每一行都具有相同的值。SQL 数据库使用基数来帮助确定给定查询的最佳查询计划。

Just the word cardinality, I believe focuses on relationships between tablesIn particular, it is not a term used to discuss a single table or uniqueness of data

只是基数这个词，我相信重点是表之间的关系特别是，它不是用来讨论单个表或数据唯一性的术语

IBM documentation (if you search for the word unique, it is not mentioned) https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cog_rlp.10.2.2.doc/c_cog_rlp_rel_cardinality.htmlWhen you interpret cardinality, you must consider the notation that displays at both ends of the relationship. Possible end labels are shown in the following list:

IBM 文档（如果您搜索单词 unique，则不会提及） https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cog_rlp.10.2。 2.doc/c_cog_rlp_rel_cardinality.html解释基数时，必须考虑关系两端显示的符号。可能的结束标签显示在以下列表中：

• 0..1 (zero or one match)
• 1..1 (exactly one match)
• 0..n (zero or more matches)
• 1..n (one or more matches)

• 0..1（零个或一个匹配项）
• 1..1（恰好一场比赛）
• 0..n（零个或多个匹配项）
• 1..n（一个或多个匹配项）

In mathematics, the cardinality of a set is a measure of the "number of elements of the set". (no mention of unique btw) https://en.wikipedia.org/wiki/Cardinality

在数学中，集合的基数是“集合元素数量”的度量。（没有提到独特的顺便说一句） https://en.wikipedia.org/wiki/Cardinality

In database design, the cardinality or fundamental principle of one data aspect with respect to another is a critical feature. The relationship of one to the other must be precise and exact between each other in order to explain how each aspect links together. In the relational model, tables can be related as any of "one-to-many", "many-to-many" "one-to-zero-or-one", etc.. This is said to be the cardinality of a given table in relation to another. https://en.wikipedia.org/wiki/Cardinality_(data_modeling)

在数据库设计中，一个数据方面的基数或基本原则是一个关键特征。一个与另一个之间的关系必须精确而准确，以便解释每个方面如何联系在一起。在关系模型中，表可以关联为“一对多”、“多对多”、“一对零或一”等中的任何一种。这被称为一个给定的表相对于另一个。 https://en.wikipedia.org/wiki/Cardinality_(data_modeling)