当我在MySQL中添加一个新索引时，为什么索引的基数不会改变？

Question

我已经为我的一个MySQL数据库表添加了FULLTEXT索引，如下所示：

ALTER TABLE members ADD FULLTEXT(about,fname,lname,job_title);

我的问题是，在使用phpmyadmin时，我发现我的新索引的基数(cardinality)只有1。这是否意味着该索引永远不会被使用？

我运行了一个analyze table命令，但似乎没有任何作用。

analyze table members

索引字段的相应类型为varchar(100), varchar(100), text, varchar(200)，使用的引擎是MyISAM，表中约有30,000行，全部都是唯一的。我的MySQL版本是5.0.45。

我做错了什么吗？

- Tom

Answer 1

如果表中只有一行数据，那么索引的基数应该是1。基数指的是唯一值的数量，可以将索引看作是一个基于桶（如哈希）的查找表，而基数就是桶的数量。

具体来说，当你在一组列（a、b、c、d）上建立索引时，数据库会扫描表中的所有行，针对每一行查找这四个列的有序四元组。比如说，如果你的表长这样：

a  b  c  d  e   
-- -- -- -- --  
1  1  1  1  200 
1  1  1  1  300
1  2  1  1  200
1  3  1  1  200

数据库查询的只有这四列（a,b,c,d）：

a  b  c  d  
-- -- -- --
1  1  1  1 
1  2  1  1 
1  3  1  1 

注意到现在只剩下三个唯一的行了吗？这些将成为我们的桶，但我们会回到这点。实际上，在表中每一行都有一个记录ID或行标识符。因此，我们的原始表看起来像这样：

(row id) a  b  c  d  e   
-------- -- -- -- -- --  
00000001 1  1  1  1  200 
00000002 1  1  1  1  300
00000003 1  2  1  1  200
00000004 1  3  1  1  200

所以，当我们仅查看 (a，b，c，d) 的 4 列时，实际上也在查看行 ID：

(row id) a  b  c  d 
-------- -- -- -- --
00000001 1  1  1  1
00000002 1  1  1  1
00000003 1  2  1  1
00000004 1  3  1  1

但我们想按 (a,b,c,d) 进行查找，而不是按行 ID，因此我们会生成类似于以下内容：

(a,b,c,d) (row id)
--------- --------
1,1,1,1   00000001
1,1,1,1   00000002
1,2,1,1   00000003
1,3,1,1   00000004

最后，我们将所有具有相同(a,b,c,d)值的行的行ID分组在一起：

(a,b,c,d) (row id)
--------- ---------------------
1,1,1,1   00000001 and 00000002
1,2,1,1   00000003
1,3,1,1   00000004

看到了吗？(a，b，c，d)的值为(1,1,1,1)、(1,2,1,1)和(1,3,1,1)，它们成为我们查找表中原始表行的键。

实际上，这些都没有真正发生，但它应该能够给你一个关于如何进行“天真”（即直接）实现索引的好主意。

但归根结底，基数只是衡量索引中有多少个唯一行。在我们的示例中，这是查找表中键的数量，即3个。

希望这可以帮助你！

- Shalom Craimer

---

感谢提供的索引信息。解释得非常清楚。鉴于有30000行数据且几乎每个成员都有不同的名称，我的索引的基数应该大于1。 - Tom

谢谢您对索引的解释，非常好，但是您的解释并没有回答上面提出的问题。 - Deepak Yadav

你说得对，我没有明确说明最终的结论：我只是展示了4行数据分为3个桶。我相信你可以想出另一行数据可以被添加到现有的3个桶之一。这将保持桶的数量不变，也意味着索引的基数不变。对此我感到抱歉。 - Shalom Craimer

很好的解释。但是“实际上，这些都不会真正发生”！？那么真正发生了什么...？我感到有点困惑。 - Paul Lo

@PaulLo 嗯，一个天真实现的数据库引擎可能会执行所有详细步骤。但我预计大多数实现会以不同的方式进行 - 更有效地利用RAM和磁盘资源，以提供更低的延迟。 - Shalom Craimer

Answer 2

我不能确定为什么MySQL不计算基数，但我可以猜测。MySQL手册指出：

基数：索引中唯一值数量的估计值。这通过运行ANALYZE TABLE或myisamchk -a进行更新。基数是基于存储为整数的统计信息计算的，因此即使对于小表，该值也不一定是准确的。基数越高，MySQL在执行连接时使用索引的可能性就越大。

FULLTEXT索引仅用于MATCH ... AGAINST（...）查询，这会强制使用索引。如果这些字段上没有FULLTEXT索引，则MATCH ... AGAINST语法无法正常工作。

我的猜测是，之所以不计算基数，是因为它真的不必要。 请注意，尽管未设置基数，但针对索引的搜索仍然有效。

记录一下，ANALYZE TABLE foobar语句似乎正确设置了基数。

- Vegard Larsen