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

Question

在mysql优化中，这两个参数有什么用？

Answer 1

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

如果英文可以，请看这里

Answer 2

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/