在 SQL 里描述数据分布情况的时候，有 Cardinality 和 Selectivity 两个概念，有什么区别？

最新推荐文章于 2025-04-01 10:39:19 发布

转载最新推荐文章于 2025-04-01 10:39:19 发布 · 1.4k 阅读

mysql 同时被 2 个专栏收录

78 篇文章

订阅专栏

database

9 篇文章

订阅专栏

本文详细解释了SQL中的基数和选择性概念，基数是指特定列中的唯一值数量，而选择性则是在讨论数据库索引时使用的一个数值，表示给定列值的多样性与表中总行数的关系。选择性帮助查询优化器决定是否使用索引查找表中的某些行。

What is the difference between cardinality and selectivity?

In SQL, cardinality refers to the number of unique values in particular column. So, cardinality is a numeric value that applies to a specific column inside a table. An example will help clarify.
Example of cardinality

Suppose we have a table called People which has a “Sex” column that has only two possible values of “Male” and “Female”. Then, that “Sex” column would have a cardinality of 2, because there are only two unique values that could possibly appear in that column – Male and Female.
Difference between cardinality and selectivity

In SQL, the term selectivity is used when discussing database indexes. The selectivity of a database index is a numeric value that is calculated using a specific formula. That formula actually uses the cardinality value to calculate the selectivity. This means that the selectivity is calculated using the cardinality – so the terms selectivity and cardinality are very much related to each other. Here is the formula used to calculated selectivity:
Formula to calculate selectivity:

Selectivity of index = cardinality/(number of rows) * 100%

This means that if the People table we were talking about earlier has 10,000 rows, then the selectivity would be 2/10,000, which equals .02%. This is considered to be a very low selectivity.
Why are the selectivity and cardinality used in databases?

The selectivity basically is a measure of how much variety there is in the values of a given table column in relation to the total number of rows in a given table. The cardinality is just part of the formula that is used to calculate the selectivity. Query optimizers use the selectivity to figure out if it is actually worth using an index to find certain rows in a table. A general principle that is followed is that it is best to use an index when the number of rows that need to be selected is small in relation to the total number of rows. That is what the selectivity helps measure. You can also read more about selectivity and cardinality in these links: cardinality and selectivity.

原文地址：http://www.programmerinterview.com/index.php/database-sql/cardinality-versus-selectivity/