Laravel的“soft_delete”在MySQL上需要索引吗？

Question

如果我在 Laravel 4.2 中使用软删除（数据库为 mysql），每个Eloquent查询都有“WHERE deleted_at IS NULL”。deleted_at 上没有索引。

• 在大表上会很慢吗？（或者 IS NULL 在不需要索引的情况下进行了优化）
• 我应该在 deleted_at 列上添加索引吗？

所以，Laravel的“soft_delete”deleted_at列在MySQL中是否需要索引？

说明： Laravel 在deleted_at列中存储时间戳，以表示记录何时进行软删除，而不是布尔值。

- rap-2-h

---

它不需要索引。如果它是空的，它就没有被删除。如果它有一个值，它就被删除了。这意味着它有两个可能的值才能工作。具有两个可用值的列不是好的索引候选项 - 它们的基数趋近于0。因此，deleted_at不需要索引。 - N.B.

好的，谢谢。我以为几乎每个where子句都需要建立索引。如果您愿意，可以将您的评论发布为答案。 - rap-2-h

Answer 1

deleted_at列不适合作为索引候选列。相比于评论，我将尝试更好地解释：仅当其基数相对较高时，索引才有用。 基数是描述数据集中索引唯一性的数字。这意味着它是总记录数除以总唯一记录数得到的数量。

例如，主键的基数为1。每个记录都包含唯一的主键值。 1也是最高的数字。你可以将其视为“100%”。

但是，像deleted_at这样的列没有这样的价值。 Laravel对deleted_at的处理方式是检查它是否为空。 这意味着它只有两个可能的值。 包含两个值的列的基数极低，并随着记录数的增加而降低。

你可以为这样的列创建索引，但这并没有帮助。实际上，它可能会减缓速度并占用空间。

简而言之：不需要为该列创建索引，索引对性能没有任何有益影响。

- N.B.

---

这是否意味着索引布尔列没有意义，还是我的理解有误？ - Zane

@Cabloo - 你没有误解，任何具有少量唯一值的列都不是一个好的索引候选列，即使你有一个包含值为0和1的 tinyint 类型的列。 - N.B.

根据我阅读的资料（参考链接），这不是关于唯一值的数量，而是与值的分布有关。因此，如果50%的记录为真，则不会影响查询时间。但如果5%的记录为真，则会降低查询时间。换句话说，这与“选择性”有关。为了使索引有帮助，对该索引的搜索在数据集中必须相对较少。 - Zane

@Cabloo提到的表只有400万行。我观察到拥有5000万行的表在没有索引的情况下也能很好地执行。他们只提到一个布尔列使用索引来提高性能的情况是不足以证明的。想象一下一个拥有1亿条记录的表，只有一条记录包含'真'值。因此，像WHERE x = true这样的查询将会很快，并且你可能会得出在布尔列上创建索引很棒的结论。那么对于WHERE x = false呢？2除以x，其中x > 0且 < 无限大告诉你，随着数据增长，你将浪费空间。 - N.B.

如果deleted_at是一个有很多不同值且很少为空的日期时间类型，对其进行索引将是一个好选择。 - Rick James

Answer 2

我不知道为什么@N.B.上面的评论有这么多赞，但在我的环境中，我发现这完全不正确。

我在一些关键表中的deleted_at时间戳上添加了索引，并享受到了一些查询时间从32秒降到不到5.4毫秒的好处。这确实取决于你的应用程序的性质。

在我的情况下，我有3个带软删除的表，一些简单的连接（都有索引），但由于Laravel默认处理软删除的方式，我的查询遇到了困难。

我强烈建议索引这些列，以免您的应用程序在记录数量增加时出现问题。

- danrichards

---

两个示例都有问题——你没有展示索引有哪些，尤其是用于连接的索引——这些不会在 deleted_at 上。而且 GROUP BY 因素也很重要。请展示 SHOW CREATE TABLE 和 EXPLAIN SELECT ... 两者。 - Rick James

丹，你读了答案并试图理解吗？看起来你和Rick James都不知道B树是如何工作的。此外，在运行查询之前，你是否清除了所有缓存？你确定innodb_buffer_pool包含数据吗？你在这里做出了完全错误的假设 - 第一个查询，即32秒的查询，在缓冲池中没有任何数据。一旦执行，它就会填充它。你的第二个查询现在使用内存中的数据。你错误地认为这是因为索引。 - Mjh

这些查询是可疑的--它们测试任何变量是否为NULL。使用EXISTS并摆脱GROUP BY可以更有效地完成此操作。 - Rick James

@Mjh - 核心问题在于优化器是否会使用BTree组织的索引。其次，如果必须加载整个索引（或表），那么对于数百万条缓存记录进行查找，5秒钟可能是合理的。 - Rick James

我删除了索引，原本查询需要2.7毫秒，然后重新运行了多次。我得到了167秒、142秒和151秒的结果。innodb_buffer_pool不是空的，我认为有一些优化，因为有轻微的增加？在这个查询中，有3个表上的deleted_at IS NULL检查，如果我删除IS NULL检查的where子句，则查询在没有deleted_at索引的情况下以27.5毫秒的速度运行。因此，很明显这些索引在条件中带来了巨大的好处。我不知道添加太多的deleted_at索引会有什么后果，但在这种情况下，我认为我不在意。 - danrichards

Answer 3

简短回答：可能。

详细回答：

如果deleted_at中有非常少的不同值，MySQL将不会使用INDEX(deleted_at)。

如果deleted_at中有许多不同的非空日期，则MySQL将使用INDEX(deleted_at)。

到目前为止，大部分讨论都没有考虑到这个单列索引的基数。

注意：这与类似于is_deleted的2个值标志不同。在这种情况下，对于单列索引是无用的。

更多讨论（从MySQL的角度）

https://laravel.com/docs/5.2/eloquent#soft-deleting中提到

现在，当您在模型上调用delete方法时，deleted_at列将设置为当前日期和时间。并且，在查询使用软删除的模型时，软删除模型将自动从所有查询结果中排除。

据此，我假设这是表定义中发生的事情：

deleted_at  DATETIME  NULL  -- (or TIMESTAMP NULL)

而且这个值被初始化（显式或隐式）为NULL。

情况1：有很多新的行，但没有“删除”：所有deleted_at的值都是NULL。在这种情况下，优化器会避开INDEX（deleted_at）因为它没有帮助。实际上，使用索引会浪费更多时间去遍历整个索引和数据。忽略索引并假定所有行都是可能被SELECTed的候选行，这样更便宜。

情况2：少数行（其中之一）已被删除：现在deleted_at有多个值。尽管Laravel只关心IS NULL与IS NOT NULL，但MySQL将其视为一个多值列。但是，由于测试是用于IS NULL，而大多数行仍然是NULL，因此优化器的反应与情况1相同。

情况3：被软删除的行比仍处于活动状态的行要多得多：现在索引突然变得有用了，因为表中只有小部分IS NULL。

情况2和情况3之间没有确切的分界线。20%是一个方便的经验法则。

现在，从执行的角度来看。

INDEX（deleted_at）用于deleted_at IS NULL：

1. 钻取索引BTree以获取第一个具有NULL的行。
2. 扫描直到IS NULL失败为止。
3. 对于每个匹配的行，到达数据B树以获取该行。

INDEX（deleted_at）未使用：

1. 扫描数据BTree（或使用其他索引）
2. 对于每个data行，请检查deleted_at IS NULL，否则过滤掉该行。

复合索引：

拥有以deleted_at开头的“复合”（多列）索引可能非常有益。例如：

INDEX(deleted_at, foo)

WHERE deleted_at IS NULL
  AND foo BETWEEN 111 AND 222

无论表中有多少百分比的内容被删除，都很可能有效地使用索引。

1. 钻取到第一行具有NULL和foo >= 111的索引BTree。
2. 扫描直到IS NULL或foo <= 222失败。
3. 对于每个匹配的行，进入数据 BTree 获取该行。

请注意，在INDEX中，NULL非常类似于任何其他单个值。 （并且NULLs存储在其他值之前。）

- Rick James

---

@Mjh - MySQL 优化的效果取决于 MySQL 明显看到的基数，而不是 Laravel 给出的“意图”。如果 deleted_at 实际上具有 NULL 或大量不同的 TIMESTAMPs，那么 MySQL 就无法将其视为布尔值。相反，它（错误地）假定不同值的数量均匀分布。 - Rick James