mysql索引没有基数_关于数据库：MySQL：低基数/选择性列=如何编制索引？

我需要在表(列)中添加索引，并在这篇文章中偶然发现：

多少数据库索引太多？

引用：

话虽如此，您显然可以向表添加很多毫无意义的索引，这些索引什么也做不了。将B-Tree索引添加到具有2个不同值的列中将毫无意义，因为它在查找数据方面不会增加任何内容。列中的值越独特，则从索引中受益越多。"

如果只有两个不同的值，索引真的没有意义吗？给定如下表(MySQL数据库，InnoDB)

进一步的条件：

该数据库包含3亿条记录

状态只能是"启用"和"禁用"

1.5亿条记录的状态为=启用，1.5亿条记录的状态为

stauts =禁用

我的理解是，如果没有状态索引，使用where status=’enabled’进行选择会导致进行3亿条记录的全表扫描吗？

在状态上使用BTREE索引时查询的效率如何？

我应该索引此列吗？

MySQL InnoDB提供什么替代方法(可能是其他索引)，以给定示例中的"基数/选择性非常低"的方式通过" where status =" enabled"子句有效地查找记录？

您描述的索引几乎没有意义。如果您需要选择的行数少于总行数，则最好使用索引。

这样做的原因与数据库访问表的方式有关。可以通过全表扫描评估表，在该表中依次读取和处理每个块。或通过rowid或键查找，其中数据库具有键/行并读取所需的确切行。

如果您使用基于主键或另一个唯一索引的where子句，例如where id = 1，数据库可以使用索引来获取对行数据存储位置的精确引用。这显然比进行全表扫描和处理每个块更有效。

现在回到您的示例，您有一个where status = 'enabled'的where子句，索引将返回1.5亿行，数据库将不得不使用单独的小读取依次读取每一行。而通过全表扫描访问表允许数据库利用更有效的较大读取。

最好只进行全表扫描而不是使用索引。使用mysql，您可以将FORCE INDEX (idx_name)用作查询的一部分，以允许在每个表访问方法之间进行比较。

参考：

http://dev.mysql.com/doc/refman/5.5/zh-CN/how-to-avoid-table-scan.html

我明白您的意思，但通常会有其他限制因素。例如，假设他增加限制10，那么索引更好，不是吗？我想按要求，你是对的

很抱歉，我不同意Mike。添加索引的目的是限制对MySQL的完整记录搜索的数量，从而限制通常是瓶颈的IO。

此索引不是免费的；您需要在插入/更新时为其付费，因为索引现在需要加载索引文件(300M条记录的全文本索引可能不在内存中)，因此必须在索引需要更新时以及在搜索本身中进行支付。因此很可能是您获得了更多的IO，而不是限制了它。

我确实同意这样一种说法，即最好将二进制变量存储为bool或tinyint，因为这会减小行的长度，从而限制磁盘IO，并且数字比较也更快。

如果需要速度，并且很少使用禁用的记录，则可能希望有2个表，一个用于启用记录，一个用于禁用记录，并在状态更改时移动记录。随着复杂性和风险的增加，这当然是我的最后选择。如果您碰巧要去，一定要进行一笔交易。

它只是突然出现在您的脑海中，您可以使用explain语句检查是否实际使用了索引。那应该向您展示MySQL如何优化查询。我真的不知道hoe MySQL是否可以优化查询，但是从postgresql中，我确实知道您应该在数据库上解释与真实数据库大致相同(大小和数据)的查询。因此，如果您在数据库上有一个副本，请在表上创建一个索引，然后查看它是否已实际使用。正如我说的那样，我对此表示怀疑，但我绝对不了解所有内容：)

+1建议使用分区表或2个单独的表。

这是一个很好的讨论。我同意分区，但前提是他不管状态如何都不想记录。如果每个查询都涉及一个状态，那么对我来说分区很有意义。

而且，在我看来，插入的代价被选择的提升所抵消。如果状态变为tinyint，并且您具有经过适当调优的mysql服务器，则经过一段预热时间后，300M记录的索引文件很容易放入密钥缓冲区。

我看到，当仅在状态=禁用的情况下，索引会更糟。谢谢！

非常感谢您的回答！

如果数据以50:50的比例分布，则查询status="enabled"的位置将避免对表进行一半的扫描。

在此类表上建立索引完全取决于数据的分布，即：是否启用了状态的条目为90％，其他为10％。对于status="disabled"的查询，它仅扫描表的10％。

因此在此类列上建立索引取决于数据的分布。

您几乎不需要一次获得全部1.5亿条记录，因此我想"状态"将始终与其他列结合使用。也许使用诸如(状态，全名)的复合索引会更有意义

这个答案没有解决所问的问题。如果他添加了该索引，现在他只想按姓氏进行搜索，那就是表扫描。另外，如果要最后禁用十条记录怎么办？按照要求，他希望"状态已禁用"。将全名添加到索引可能是不必要的开销。

但是只有当您不使用where fullname like %something%作为索引时，它才像双面通配符一样没有用。

不，如果状态在索引中排在首位，则您的全名没有索引。列的顺序很重要。

Jan，您绝对应该为该列编制索引。我不确定引语的上下文，但是您上面所说的一切都是正确的。在该列上没有索引，最肯定的是您要对300M行进行表扫描，这对于该数据可能是最糟糕的事情。 strike>

Jan，正如所问的那样，您的查询仅涉及" where status = enabled"，而没有其他限制因素，因此该列上的索引显然无济于事(高兴的SO社区向我展示了最新情况)。但是，如果存在限制因素，例如"限制10"，则索引可能会有所帮助。另外，请记住，索引也按优化分组使用和按顺序使用。如果您正在执行"按状态从表组中选择count(*)，status"，则索引将很有帮助。

您还应该考虑将状态转换为tinyint，其中0表示禁用，而1表示启用。您浪费了大量的空间来存储该字符串，而tinyint仅需要每行1个字节！

嗯，为什么要投票？我错了吗？如果是这样，我很想知道为什么，因为那意味着我已经错过了有关MySql和索引的基本知识。

@stereofrog：你确定吗？索引存储在密钥缓冲区中，而不存储数据的事实呢？另外，我不确定"来回跳跃"是什么意思。

您好迈克，非常感谢您的回答，这对我很有帮助。

@a的答案是正确的，但是需要指出的是，索引的有用性不仅取决于其基数，还取决于数据的分布以及在数据库上运行的查询。

在OP的情况下，对于具有status='enabled'的150M条记录和具有status='disabled'的150M条记录，索引是不必要的，并且浪费资源。

对于具有status='enabled'的299M条记录和具有status='disabled'的1M条记录，该索引在类型SELECT ... where status='disabled'的查询中很有用(并将使用)。

类型为SELECT ... where status='enabled'的查询仍将与全表扫描一起运行。

我的MySQL数据库中有一个类似的专栏。大约400万行，分布分别为90％1和10％0。

我今天才发现，没有索引，我的查询(where column = 1)实际上运行得明显更快。

我愚蠢地删除了索引。我愚蠢地说，因为我现在怀疑查询(where column = 0)可能仍然从中受益。因此，我应该明确地告诉MySQL在搜索1时忽略索引，而在搜索0时使用索引。也许。