Нужен ли Laravel "soft_delete" индекс для MySQL?

Ответ 1

Столбец deleted_at не является хорошим кандидатом индекса. Я попытаюсь объяснить лучше по сравнению с комментарием: индексы полезны только тогда, когда их мощность относительно высока. Кардинальность - это число, которое описывает уникальность индекса в наборе данных. Это означает, что общее количество записей делится на полные уникальные записи.

Например, мощность первичного ключа равна 1. Каждая запись содержит уникальное значение для первичного ключа. 1 также является самым большим числом. Вы можете считать его "100%".

Но столбец, такой как deleted_at, не имеет такого значения. Что Laravel делает с deleted_at, проверьте, является ли оно или нет. Это означает, что у него есть два возможных значения. Столбцы, содержащие два значения, имеют чрезвычайно низкую мощность, которая уменьшается по мере увеличения количества записей.

Вы можете индексировать такой столбец, но это не поможет. Что произойдет, так это то, что он может замедлить работу и заняться пространством.

TL; DR: нет, вам не нужно индексировать этот столбец, индекс не окажет положительного влияния на производительность.

Ответ 2

Я не знаю, почему вышеупомянутое @NB имеет так много голосов, в моем контексте я нахожу это совершенно неверным.

Я добавил индексы к временным меткам selected_at в некоторых таблицах ключей, и некоторые запросы сократились с 32 до менее 5,4 мс. Это действительно зависит от характера вашего приложения.

В моем сценарии у меня есть 3 таблицы с мягким удалением, несколько простых объединений (все с индексами), но мои запросы пострадали из-за природы по умолчанию обработки мягких удалений в Laravel.

Я настоятельно рекомендовал проиндексировать эти столбцы, чтобы ваше приложение задыхалось при увеличении числа записей.

Ответ 3

Краткий ответ: возможно.

Длинный ответ:

Если в deleted_at очень мало разных значений, MySQL не будет использовать INDEX(deleted_at).

Если в deleted_at нет разных ненулевых дат, MySQL будет использовать INDEX(deleted_at).

Большая часть обсуждения (до сих пор) не смогла принять во внимание мощность этого индекса из одной колонки.

Примечание. Это не то же самое, что флаг с двумя значениями, например is_deleted. Бесполезно иметь одностолбцовый индекс для таких.

Больше обсуждения (с точки зрения MySQL)

https://laravel.com/docs/5.2/eloquent#soft-deleting говорит

Теперь, когда вы вызываете метод delete для модели, в столбце delete_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. Разверните Index BTree для первого ряда с NULL.
2. Сканирование до тех пор, пока не IS NULL ошибка
3. Для каждой подходящей строки дотянитесь до данных BTree, чтобы получить строку.

INDEX(deleted_at) не используется:

1. Сканируйте данные BTree (или используйте другой индекс)
2. Для каждой строки данных проверьте, что deleted_at IS NULL, иначе отфильтруйте эту строку.

Составной указатель:

Может быть очень полезно иметь "составной" (многостолбцовый) индекс, начинающийся с deleted_at. Пример:

INDEX(deleted_at, foo)

WHERE deleted_at IS NULL
  AND foo BETWEEN 111 AND 222

Это очень вероятно для эффективного использования индекса независимо от того, какой процент таблицы deleted_at IS NULL.

1. Разверните Index BTree для первого ряда с NULL и foo >= 111.
2. Сканирование, пока IS NULL или foo <= 222 не удастся.
3. Для каждой подходящей строки дотянитесь до данных BTree, чтобы получить строку.

Обратите внимание, что в INDEX NULL действует очень похоже на любое другое отдельное значение. (И NULLs хранятся перед другими значениями.)