mysql - 为什么 mysql 索引中的基数值不等于列值的不同计数

Question

不久前，我开始优化 mysql 数据库的查询。我创建了一些索引并决定使用 show index from 语法查看它们的参数。并看到基数不等于某些列的非重复计数。

所以，我有 table

CREATE TABLE `item_owners` (
  `uid` varchar(255) NOT NULL,
  `version` bigint(20) NOT NULL,
  `type_id` varchar(255) NOT NULL,
  `owner_name` varchar(255) NOT NULL,
  `item_id` varchar(255) NOT NULL,
  `status_id` varchar(255) NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `FK181EADBC7346EE24` (`status_id`),
  KEY `FK181EADBC90094D43` (`type_id`),
  KEY `FK181EADBC499E38CA` (`item_id`),
  CONSTRAINT `FK181EADBC499E38CA` FOREIGN KEY (`item_id`) REFERENCES `items` (`uid`),
  CONSTRAINT `FK181EADBC7346EE24` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`uid`),
  CONSTRAINT `FK181EADBC90094D43` FOREIGN KEY (`type_id`) REFERENCES `types` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

在那个表中我有 2M 行(实际上是 2M uniq PK)、500k uniq item_id、5 uniq status_id 和 20 uniq type_id

但是当我执行

show index from item_owners

我有

+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| item_owners |          0 | PRIMARY            |            1 | uid         | A         |     1893427 |     NULL | NULL   |      | BTREE      |         |               |
| item_owners |          1 | FK181EADBC7346EE24 |            1 | status_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| item_owners |          1 | FK181EADBC90094D43 |            1 | type_id     | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| item_owners |          1 | FK181EADBC499E38CA |            1 | item_id     | A         |      148000 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

然后我决定执行

analyze table item_owners

在这个输出之后是:

+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| item_owners |          0 | PRIMARY            |            1 | uid         | A         |     2005419 |     NULL | NULL   |      | BTREE      |         |               |
| item_owners |          1 | FK181EADBC7346EE24 |            1 | status_id   | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| item_owners |          1 | FK181EADBC90094D43 |            1 | type_id     | A         |          17 |     NULL | NULL   |      | BTREE      |         |               |
| item_owners |          1 | FK181EADBC499E38CA |            1 | item_id     | A         |     2005419 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

有人知道这怎么可能吗？，因为我真的误解了为什么即使对于 PK 索引，基数也不等于 uniq 行数。

Answer 1

引用 the manual :

Cardinality

An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables.