Laravel 的“soft_delete"是否会被删除?需要在 MySQL 上建立索引吗?

2021-12-21 00:00:00 indexing optimization mysql laravel eloquent

如果我在 laravel 4.2 中使用软删除(数据库是 mysql)，每个雄辩的查询都有 WHERE deleted_at IS NULL.deleted_at 上没有索引.

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

  • 在大桌子上会很慢吗?(或者可能 IS NULL 在不需要索引的情况下进行了优化)
  • 我应该在 deleted_at 列上添加索引吗?
  • Will it be slow on big tables? (or maybe IS NULL is optimized without needing an index)
  • Should I add an index on the deleted_at column?

那么，Laravel 的soft_delete"deleted_at 列是否需要在 MySQL 中建立索引?

So, does Laravel's "soft_delete" deleted_at column need an index in MySQL?

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

Clarification: Laravel stores a timestamp in the deleted_at column to denote when a record has been soft deleted, as opposed to a boolean value.

推荐答案

deleted_at 列不是一个好的索引候选.与评论相比，我将尝试更好地解释:索引仅在基数相对较高时才有用.基数是描述数据集中索引唯一性的数字.这意味着它是记录总数除以唯一记录总数.

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.

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

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%".

但是，诸如deleted_at 之类的列没有这样的值.Laravel 对删除的_at 所做的是检查它是否为空.这意味着它有两个可能的值.包含两个值的列具有极低的基数，基数随着记录数的增加而减少.

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:不，您不必为该列建立索引，索引不会对性能产生有益影响.

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

相关文章