Как правильнее поступать с ненужными записями в БД — удалять или помечать их флагом "deleted"?

Question

[denisigo]

Удаление ненужных записей можно сделать так:

1. Удалять физически. Периодически делать OPTIMIZE TABLE.
2. Не удалять. Присваивать флаг, например, «deleted», и в дальнейшем не выбирать записи с этим флагом

Как правильнее и оптимальнее сделать, если удаляться записи будут немного, но регулярно?

Answer 1

[Tujh]

2. Не удалять. Присваивать флаг, например, «deleted», и в дальнейшем не выбирать записи с этим флагом

2. не удалять, присваивать флаг, а потом перезаписывать снимая флаг «deleted»

Comments

[denisigo]

оО это что-то новенькое. И насколько такие манипуляции оправданны?

[mark_ablov]

если хранить записи фиксированный длины, то неплохое решение, если нет — то ужасное :)

[Tujh]

Ну да, забыл добавить — только для записей фиксированной длинны, когда много работаешь с однотипными решениями — некоторые моменты кажутся сами собой разумеющимися, но забываешь, что это не очевидно для других :)
Манипуляции удобны тем, что позволяет оптимизировать операции при частом удалении / вставке одинаковых данных. Пометили запись удаленной, а при вставке пробежались — нашли первую помеченную и перезаписали. Нет разрастания файла БД, нет операций удаления записи, меньше риск повреждения, так как при удалении нет необходимости сжимать таблицы.
Есть и минусы, дополнительное поле в каждой записи, увеличение времени вставки, если не хранятся индексы «удаленных» записей, возможно обращение к уже удаленной записи, т.к. её индекс может стать валидным при перезаписи новых данных и т.п. Длинна записей должна быть всегда одна, нельзя так хранить строки переменной длинны.
Ну в-общем как и у любого решения есть свои плюсы и свои минусы.

[Владимир Чернышев]

Ещё минус — нужна блокировка или какая другая синхронизация, чтобы между, грубо говоря, SELECT id FROM table WHERE deleted и UPDATE table… WHERE id=? не начался другой такой SELECT.

Answer 2

[mark_ablov]

зависит от размера БД, конечно.
Если база велика я бы комбинировал флаг + удаление по крону + OPTIMIZE во время простоя ночью.

Comments

[denisigo]

То есть в конечном счёте получается удалять физически? То есть лишний размер таблицы хуже чем фрагментация (или OPTIMIZE TABLE это полностью устраняет)?

[mark_ablov]

суть в том, что OPTIMIZE TABLE резонно использовать только при большом числе изменений. Ну и таблица локается, поэтому лучше использовать её пореже.

[denisigo]

Это понятно конечно же )

Answer 3

[ChemAli]

Физический смысл.

Запсись БД делается, грубо говоря, в файл. Если запись физически из файла удалить, нужно произвести операцию по усечению файла и его перезаписи, что на многих файловых системах и СУБД — ресурсозатратная (и опасная — вдруг сбой?) операция, поэтому ее стараются избегать. Плюс часто при перезаписи (оптимизации) БД становится недоступна совсем или для некоторых операций, что тоже нехорошо. Отсюда пошла практика пометок «удаленными». Решайте сами, исходя из задачи и необходимости.

Встречал как-то альтерантивный вариант: периодический слив БД в дамп без «помеченных» записей и замена им старой БД. Благодаря тому, что чтение дампа происходило без остановки работы, а заливка нового была достаточно быстрой, решение жило и было приемлемым. Побочный продукт — бекапы.

Answer 4

[CrazySquirrel]

Зависит от того, нужны ли Вам этим записи в будущем, если 100% не понадобятся, я бы ставил флаг на удаление, и по крону удалял.

Comments

[denisigo]

Нет, записи 100% не понадобятся. Просто где-то давно читал, что физическое удаление ужасно плохо для индексов и что лучше не удалять.

[pietrovich]

Удаление само по себе не плохо. Плоха фрагментация, к которой приводит удаление. и тут многое от субд/системы хранения зависит. В некоторых ситуациях флажок рулит, в некоторых удаление по расписанию и optimize/shrink/etc, в некоторых проще перелить оставшиеся данные в новую таблицу и заменить старую переименованием.

Answer 5

[Виталий Желтяков]

Всё зависит от типа таблицы, из которой удаляются записи.
Если это MySQL, то делать пометку deleted и удалять потом.
Если innodb, то удалять физически.

Comments

[mark_ablov]

MyISAM, наверно?
с innodb optimize не работает :)

[denisigo]

Вы хотели сказать myisam?

То есть всё равно всё сводится к физическому удалению — сразу или по крону — зависит от размера базы и нагруженности.

[denisigo]

to mark_ablov, OPTIMIZE с innodb не работает??

[Виталий Желтяков]

Да, я имел в виду тип таблиц MyISAM. На работе закрутился.

Физическое удаление из этого типа таблиц (даже, если мы удали 1 строку) снижает скорость работы таблицы, до того момента, как мы не проведём оптимизацию.
Для InnoDB физическое удаление практически не критично.

[mark_ablov]

тьфу, раньше не работала, сейчас глянул в мануал — добавили поддержку.
только, как я понял, это маппинг на ALTER TABLE, которую юзали раньше для оптимизации таблички.

Answer 6

[rPman]

Ответы очевидны.
Помечать флагом — растет база, удалять — понижение производительности (фрагментация, сама операция удаления может оказаться дорогой) и часто усложнение бизнеслогики.

Я бы не рекомендовал удалять данные из сложных баз данных, особенно если это справочники, например выставив в freign key — RESTRICT, чтобы разрешить удаление только для 'свободных' записей. Удаление записей в сложных базах данных, обычно очень сложная операция, обычно перед этим приходится проводить кучу проверок и изменений для связанных данных, поэтому флаг 'deleted' используется как упрощение или даже часть механизма для введения временной составляющей в хранение данных (база данных может являться как средством для хранения текущего состояния, так и для хранения лога изменений данных во времени)

В зависимости от задач:
1. Если удаление происходит сравнительно редко, т.е. если оверхед на размер базы незначителен — то лучше помечать флагом.
2. Если удаления и создания записей очень частые (в результате база не очень растет), то лучше удалять, плюсы от отсутствия фрагментации может оказаться недостаточным, чтобы перебить рост индексов (кстати и кэш в оперативной памяти будет зря занят).
3. Если и скорость и данные критичны, то лучше помечать флагом и удалять по крону, а чтобы минимизировать вероятность задержек на время обслуживания — разделить таблицу на кластеры (можно 'вручную' в классе работы с БД) и очищать каждый кусок отдельно, а разделение сделать с целью отделить часто используемые данные от редко используемых.

Answer 7

[Хуршед Нурматов]

Компромисс. Помечать на удаление. И удалять небольшими порциями по крону самые старые записи.