为什么当我添加新索引时，MySQL 中索引的基数保持不变？
我已将 FULLTEXT 索引添加到我的 MySQL 数据库表之一，如下所示：
ALTER TABLE members ADD FULLTEXT(about,fname,lname,job_title);
问题是使用 phpmyadmin 我可以看到新索引的基数仅为 1。 这是否意味着该索引永远不会被使用？
我已经运行了分析表命令，但它似乎没有做任何事情。
analyze table members
索引字段的类型分别为varchar(100)、varchar(100)、text、varchar(200)，使用的引擎是MyISAM，表有大约30,000行，都是唯一的。 我的MySQL版本是5.0.45。
难道我做错了什么？
I have added a FULLTEXT index to one of my MySQL database tables as follows:
ALTER TABLE members ADD FULLTEXT(about,fname,lname,job_title);
The problem is that using phpmyadmin I can see the cardinality of my new index is only 1. Does this mean the index will never be used?
I have run a analyze table command but it didn't seem to do anything.
analyze table members
The respective types of the index fields are varchar(100), varchar(100), text, varchar(200) and the engine used is MyISAM and the table has about 30,000 rows, all unique. My MySQL version is 5.0.45.
Am I doing something wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果表中只有 1 行，那么索引的基数当然应该为 1。 它只是计算唯一值的数量。
如果您将索引视为基于存储桶（如哈希）的查找表，那么基数就是存储桶的数量。
它的工作原理如下：当您在一组列
(a,b,c,d)上构建索引时，数据库会遍历表中的所有行，查看有序的四元组每行 4 列。 假设您的表如下所示：那么数据库查看的只是 4 列（a、b、c、d）：
看到只剩下 3 个唯一的行了吗？ 这些将成为我们的桶，但我们会回到这个话题。 实际上，表中的每一行还有一个记录 ID 或行标识符。 所以我们的原始表看起来像这样：
因此，当我们只查看 (a,b,c,d) 的 4 列时，我们实际上也在查看行 id：
但我们想要通过 (a,b ,c,d) 而不是按行 id，所以我们生成如下所示的结果：
最后，我们将具有相同 (a,b,c,d) 值的行的所有行 id 分组在一起：
看到了吗？ (a,b,c,d) 的值，即 (1,1,1,1) (1,2,1,1) 和 (1,3,1,1) 已成为我们查找表的键到原始表的行中。
实际上，这一切都没有真正发生，但它应该让您了解如何完成索引的“简单”（即直接）实现。
但底线是：基数只是衡量索引中有多少唯一行。 在我们的示例中，这是查找表中键的数量，即 3。
希望有帮助！
If you only have 1 row in the table, the cardinality for the index should be 1, of course. It's just counting the number of unique values.
If you think of an index as a lookup-table based on buckets (like a hash), then the cardinality is the number of buckets.
Here's how it works: When you build an index over a set of columns
(a,b,c,d), then the database goes over all the rows in the table, looking at the ordered quadruplets of those 4 columns, for each row. Let's say your table looks like this:So what the database looks at is just the 4 columns (a,b,c,d):
See that there are only 3 unique rows left? Those will become our buckets, but we'll get back to that. In reality, there's also a record id, or row identifier for each row in the table. So our original table looks like this:
So when we look at only the 4 columns of (a,b,c,d), we're really looking also at the row id:
But we want to do lookup by (a,b,c,d) and not by row id, so we produce something like this:
And finally, we group all the row ids of rows that have identicle (a,b,c,d) values together:
See that? The values of (a,b,c,d), which are (1,1,1,1) (1,2,1,1) and (1,3,1,1) have become keys for our lookup table into the rows of the original table.
Actually, none of this really happens, but it should give you a good idea on how a "naive" (i.e. straight-forward) implementation of an index might be done.
But the bottom line is this: cardinality just measures how many unique rows there are in an index. And in our example that was the number of keys in our lookup table, which was 3.
Hope that helps!
我无法明确回答为什么 MySQL 不计算基数，但我可以猜测。 MySQL 手册指出：
FULLTEXT 索引仅在 MATCH ... AGAINST (...) 查询中使用，这会强制使用索引。 如果这些字段上没有 FULLTEXT 索引，则 MATCH ... AGAINST 语法不起作用。
我的猜测是，没有计算基数，因为它确实没有必要。
请注意，即使未设置基数，也会对索引进行搜索。
根据记录，ANALYZE TABLE foobar 语句似乎正确设置了基数。
I cannot answer definitely why MySQL does not compute the cardinality, but I can guess. The MySQL manual states:
FULLTEXT indices are only used in MATCH ... AGAINST (...) queries, which forces the index to be used. The MATCH ... AGAINST syntax does not work if there is not a FULLTEXT index on those fields.
My guess is that the cardinality isn't computed because it really isn't necessary.
Note that searches against the index work even though the cardinality isn't set.
For the record, an ANALYZE TABLE foobar statement seems to set the cardinality correctly.