使用基数较低的索引是否有意义？

Question

据我理解，如果在一个只包含少量不同值的列上设置索引，你不会得到太多好处。

我有一列保存布尔值（实际上是小整数，但我将其用作标志），并且这个列在我大部分查询的WHERE子句中都被使用。

在一个理论上的“平均”情况下，一半的记录值将是1，另一半是0。

因此，在这种情况下，数据库引擎可以避免全表扫描，但仍需要读取很多行（总行数/2）。

那么，我应该将这个列设为索引吗？

我使用的是Mysql 5，但我更感兴趣的是关于为什么对一个已知基数较低的列进行索引或不进行索引的一般性原则。

- Juan Pablo Califano

Answer 1

即使在低基数字段上，索引也可以有所帮助，如果:

1. 当其中一个可能值与其他值相比非常少见，并且您搜索它时。

   例如，色盲女性非常少，因此这个查询：

   SELECT  *
   FROM    color_blind_people
   WHERE   gender = 'F'
   

   在这种情况下，gender 上建立索引可能会带来最大的收益。

2. 当值倾向于按表顺序分组时：

   SELECT  *
   FROM    records_from_2008
   WHERE   year = 2010
   LIMIT 1
   

   尽管这里只有 3 个不同的年份，但是由于较早年份的记录很可能是先添加的，如果没有索引，就需要扫描很多记录才能返回第一个 2010 记录。

3. 当你需要进行 ORDER BY / LIMIT 操作时：

   SELECT  *
   FROM    people
   ORDER BY
           gender, id
   LIMIT 1
   

   如果没有索引，就需要进行文件排序filesort。尽管由于有LIMIT所以它在一定程度上进行了优化，但仍然需要完全扫描表。

4. 当索引覆盖了查询中使用的所有字段：

CREATE INDEX (low_cardinality_record, value)

SELECT  SUM(value)
FROM    mytable
WHERE   low_cardinality_record = 3

5. 当需要使用DISTINCT时：

   SELECT  DISTINCT color
   FROM    tshirts
   

   MySQL将使用INDEX FOR GROUP-BY，如果颜色较少，则即使有数百万条记录，此查询也会立即完成。

   这是一个场景的示例，在低基数字段上的索引比高基数字段上的索引更有效率。

   请注意，如果DML性能不是问题，那么创建索引是安全的。

   如果优化器认为索引效率低下，那么该索引将不被使用。

- Quassnoi

Answer 2

将布尔字段包含在复合索引中可能是值得的。例如，如果您有一个大型消息表格，通常需要按日期排序，但还有一个布尔已删除字段，因此经常这样查询：

SELECT ... FROM Messages WHERE Deleted = 0 AND Date BETWEEN @start AND @end

在Deleted和Date字段上创建一个复合索引，可以让你受益匪浅。

- Vince Bowdren

---

谢谢。也许我应该对复合索引进行一些研究（我只知道它们的存在，但实际上并没有多少使用）。我正在以与您的示例代码非常相似的方式使用此列（尽管有连接和其他内容，但 WHERE 子句始终具有用于标记软删除的标志）。 - Juan Pablo Califano

进一步讨论为什么复合索引非常适合这种情况，以及为什么布尔值应该放在第一位：https://dev59.com/6FUL5IYBdhLWcg3whIO- - Rick James

@RickJames 这只适用于低基数等值索引和高基数范围索引。通常，高基数键部分应该放在前面 - 之所以在 bool 和 range 中交换是因为否则它不能在复合键中同时使用两个键。 - okdewit

@RickJames，这就是我所说的：在那个讨论中的情况以及Vince Bowdren的例子中，(low, high)排序是有意义的，因为MySQL不能使用(high, low)排序。然而，当你为具有多个等式比较的查询添加复合索引（where x =而不是where x between）时，通常期望(high, low)表现更好。 - okdewit

@okdewit - INDEX(continent, date) 和 WHERE continent='Europe' AND date BETWEEN... 只需要触及与之相关的行（在索引的BTree中）。也就是说，无论日期范围如何，都不需要“丢弃”任何内容。 - Rick James

Answer 3

当一半的记录值为1，另一半为0时，在该列上创建索引是没有意义的。查询优化器很可能不会使用它。

通常情况下，您有一个小的“活动”记录集和一个越来越大的“非活动”记录集。例如，在错误跟踪系统中，您关心活动错误，几乎不看已完成和存档的错误。对于这种情况，技巧是使用“dateInactivated”列来存储记录被停用/删除的时间戳。正如名称所示，在记录处于活动状态时，该值为NULL，但一旦停用，则写入系统日期时间。因此，在该列上创建的索引最终具有高选择性，因为“已删除”记录数增加，每个记录将具有唯一（严格来说不是）的值。查询将具有：

"... AND dateInactivated is NULL ..." 

作为谓词和索引的一部分，它将拉取你关心的恰当行集。

- user1454926

Answer 4

通常我会进行一个简单的“有索引”与“没有索引”的测试。根据我的经验，对于使用 ORDER BY 索引列的查询，大部分性能都可以得到提升。如果该列上存在排序，那么索引很可能会有所帮助。

- Zepplock

---

谢谢你的回答。在这种情况下，我没有按那一列排序。它只是用于标记记录为启用/禁用。我基本上将其用于软删除。这就是为什么我必须在大多数查询的WHERE子句中使用它的原因。 - Juan Pablo Califano

Answer 5

在我看来，这个标记的用途有限。我假设在大多数情况下，您在查询中使用的其他标准可能会更有帮助。

如果达到50％，我可能会进行一些基准测试，看看是否有很大的差别。

- Joe