为什么MySQL索引中的基数值与列值的不同计数不相等？

Question

不久前，我开始优化mysql数据库的查询。我创建了一些索引，并决定使用“show index from”语法查看它们的参数。我发现有些列的基数与不同值的数量不相等。

所以，我有一个表

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

在这张表中，我有200万行数据（确切地说是200万个独特的主键），500k个独特的item_id，5个独特的status_id和20个独特的type_id。但当我执行…

show index from item_owners

I've got

+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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      |         |               |
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

有人知道为什么会出现这种情况吗？因为我真的不理解为什么即使是主键索引，基数也不等于唯一行数。

- Andrej Soroj

Answer 1

引用手册：

基数(Cardinality)

索引中唯一值的估计数量。通过运行ANALYZE TABLE或myisamchk -a进行更新。 基数是根据存储为整数的统计信息计算的，因此即使对于小表，该值也不一定准确。

- RandomSeed

---

谢谢您的回答。但是每次索引基数错误时运行ANALYZE并不是很方便。 - Andrej Soroj

获取精确基数的目的是什么？就查询优化而言，它允许优化器正确地决定是否值得使用索引（与表扫描相比）。轻微的误差不应该有大的影响，因为在怀疑的情况下，这两种方法可能需要相同的时间。如果您需要精确的数字（例如在您的应用程序中），只需发出像 SELECT COUNT(*) 这样的查询即可。 - RandomSeed

"Cardinality是基于以整数形式存储的统计数据进行计算的"，不确定这如何逻辑上导致即使对于小表来说，该值也不一定是精确的。 - K2xL