Laravel的“soft_delete”需要索引MySQL？ [英] Does Laravel's "soft_delete" need index on MySQL?

问题描述

如果我在laravel 4.2（数据库是mysql）中使用软删除，每个雄辩的构建查询都有 WHERE deleted_at IS NULL 。 deleted_at 没有索引。

• 大桌子会慢吗？ （或者IS NULL优化没有索引）


• 我应该在soft_delete上添加索引吗？那么，Laravel的soft_delete在MySQL上是否需要索引？
  
  

< >解决方案

列 deleted_at 不是一个很好的索引候选者。我会尝试比评论更好地解释：索引只有当他们的基数相对较高时才有用。基数是描述数据集中的索引唯一性的数字。这意味着它的总记录数除以总唯一记录。

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

但是，诸如deleted_at之类的列没有这样的值。 Laravel对deleted_at执行的操作是检查是否为空。这意味着它有两个可能的值。包含两个值的列具有非常低的基数，随着记录数量的增加而减少。

您可以对这样的列进行索引，但不会有任何帮助。将会发生什么事情，它可以减缓事情并占用空间。

TL; DR：不，您不必索引该列，索引将不会对表现有利的影响。

If i'm using soft delete in laravel 4.2 (database is mysql), every eloquent builded query has WHERE deleted_at IS NULL. There are no index on deleted_at.

• Will it be slow on big tables ? (or maybe IS NULL is optimized without index)
• Should I add index on soft_delete ?

So, does Laravel's "soft_delete" need index on MySQL ?

解决方案

The column deleted_at is not a good index candidate. I'll try to explain better compared to the comment: indexes are useful only when their cardinality is relatively high. Cardinality is a number that describes index uniqueness in the data-set. That means it's total number of records divided by total unique records.

For example, the cardinality of primary key is 1. Every record contains unique value for primary key. 1 is also, the highest number. You can consider it as a "100%".

But, a column such as deleted_at doesn't have such a value. What Laravel does with deleted_at is check whether it is or isn't null. That means it has two possible values. Columns that contain two values have extremely low cardinality which decreases as number of records goes up.

You can index such a column, but it won't be of any help. What will happen is that it could slow things down and take up space.

TL;DR: no, you don't have to index that column, index will have no beneficial impact on performance.

这篇关于Laravel的“soft_delete”需要索引MySQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！