Удалять ли данные из бд?

Question

[Антон Рейтаровский]

Здравствуйте. Не раз замечал что люди не удаляют данные из а просто создают поле del в котором помечают или удалена запись, для чего так делают и как лучше делать?

Answer 1

[Rsa97]

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

Answer 2

[romy4]

Если данные не моноширинные (числа и строки одинаковой длинны), то удаляя их получаете сильно дефрагментированную базу. Получится, что данных мало, а база большущего размера. Чтобы это избежать, необходимо делать её оптимизацию, а это очень и очень затратно и даже недопустимо на нагруженных базах. Поэтому, решением стаёт поле del. А ещё хитрости в виде партиций и т.д.

Comments

[Дмитрий Энтелис]

а еще имея поле del - любое удаление можно достаточно безболезненно откатить, что в живых проектах весьма полезно

[romy4]

Дмитрий Энтелис: мы делали бэкап удалённых записей перед их удалением из таблицы

[AlikDex]

с приходом ссд проблема дефрагментации ушла на второй план.

[romy4]

AlikDex: я здесь не имею в виду дефрагментацию диска — это отдельный вопрос не по теме

[tommy87]

romy4: тут наверно речь о том, что SSD позволяет проводить дефрагментацию индекса боле быстро, при том, что это не блокирующая операция и может выполняться в фоне

[romy4]

tommy87: дефрагментацию индекса?))

[Saboteur]

"@AlikDex: с приходом ссд проблема дефрагментации ушла на второй план. "
Это тут причем? База данных это не файловая система.

[AlikDex]

Saboteur: а что это? Это в первую очередь файловая система. Т.е. базы не в параллельной вселенной хранятся, а на дисках. Даже если правильно прописаны индексы, считывание полей, которые не входят в индекс происходит с диска. И вот тут проблема у нас, если база на харде с блинами.

[Saboteur]

Другими словами, при каждом удалении строки из базы (или пометке ее как удаленной), вы перестраиваете индексы?

Если же нет, то нужно прочитать все записи, пропуская "удаленные", а значит они как минимум считываются.

Дефрагментация файловой системы - объединение фрагментов.
Оптимизация базы - физическое удаление данных, возможно перестройка индексов.

[AlikDex]

Saboteur: Индексы перестраиваются постоянно и так. При любом апдейте, если поле содержится в индексе, оно будет перестроено. Тоже касается и инсертов. Другое дело что при удалении записи с таблицы, в пустое место может записаться что-нибудь, и это создаст ту самую фрагментацию(при инсертах кстати тоже самое происходит, но это менее хреново, т.к. чтение все равно будет боле-менее линейным). Что критично для считывающей головки харда. Но ссдшник лишен этой проблемы, поэтому заморочек быть не должно.

[romy4]

AlikDex: часто при этом остаются пустые блоки (если вы знаете как устроена ISAM, innodb) и база начинает непомерно пухнуть

[АртемЪ]

AlikDex: И сама база и индексы перестраиваются не всегда.
Это крайне затратная операция по времени.
Полная перестройка индекса или самой базы это длительный процесс, и если делать его при каждом изменении, то работа замедлиться на несколько порядков.

[romy4]

АртемЪ: при удалении, изменении или вставке индекс перестраивается автоматически. Кроме случая, когда явно он отключается, но в таком случае он не используется больше в запросах. После повторного включения индекса, он автоматически перестраивается. Вы не можете сказать индексу "не перестраивайся"

[АртемЪ]

romy4: Для чего? При любом изменении в БД индекс приводится в актуальное состояние, т.е добавляется или помечается на удаление информация.
Но он не перестраивается.

[romy4]

АртемЪ: никогда не поздно узнать что-то новое. вот ответ на стек оверфлоу /questions/812512/faster-way-to-delete-matching-rows

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

Для MyISAM таблиц есть такая штука DELETE QUICK (наверное, про неё выше шла речь — не перестраивать индекс):
The QUICK modifier affects whether index leaves are merged for delete operations. DELETE QUICK is most useful for applications where index values for deleted rows are replaced by similar index values from rows inserted later. In this case, the holes left by deleted values are reused.

DELETE QUICK is not useful when deleted values lead to underfilled index blocks spanning a range of index values for which new inserts occur again. In this case, use of QUICK can lead to wasted space in the index that remains unreclaimed. Here is an example of such a scenario:
Create a table that contains an indexed AUTO_INCREMENT column.
Insert many rows into the table. Each insert results in an index value that is added to the high end of the index.
Delete a block of rows at the low end of the column range using DELETE QUICK.

Что означает, будем получать неправильную выборку, если будем обращаться при поиске по индексу по удалённым значениям.

[АртемЪ]

romy4: Ну смотрите - индекс размером в 10гб. Вы удаляете одно значение в начале индекса.
Для перестройки индекса надо записать 10гб это минута времени.
Т.е после любой операции - ждем минуту на перестройку индекса?

[romy4]

АртемЪ: на сколько я понимаю, при удалении where a = b в индексе помечаются записи как удалённые, а при удалении where a >= b and a <= c индекс перестраивается. в первом случае он мерджится, во втором перестраивается, потому что сбивается сортировка.

Answer 3

[Андрей]

Моя позиция, которая меня не раз выручала - если позволяют размеры - то лучше хранить всё. Я даже делал в отдельную таблицу запись кто удалил данные(потому что обычно автоматом пишется user_id того кто изменил данные, а если удалил - то и записи не оставалось кто именно удалил). Пару раз ставил на место особо хитро*опых, которые думали что если данные удалить, то потом никто и не узнает кто послал на удаление.

Answer 4

[Вячеслав Беляев]

я рекомендую добавить поле del и в нем уже отмечать. На этом варианте работает много сервисов, те же соц сети когда можно удалить запись и восстановить

Answer 5

[Михаил]

ну как минимум, если данные не удалять, то база перестанет влазить в память:) вопрос может быт тут, только в размерах и сроке.

или может не хватить вычислительных возможностей базы, тогда вопрос в размерах/количестве/качестве ресурсов.

но рост данных можно и существенно замедлить поделив его скорость на кол-во баз-узлов, представляющих исходную базу.

Comments

[tommy_13]

от БД зависит. Например, в SQLite удаляя записи, БД не уменьшает размер

Answer 6

[Максим Тимофеев]

Ответ на вопрос можно разделить на 2 части:
1. Понадобится ли эта запись в будущем? Например удалять пользователя нет смысла, он может вернуться.
2. Может ли это повлиять на seo или на работу сайта. Например удалив страницу - теряете сео, можно изъять ссылки на нее с сайта, но при этом оставив доступ по прямой ссылке. Некоторые вещи имеет смысл хранить для статистики.

Но имейте в виду, что чаще это лень. Например удаление категории форума, требует удаление всех форумов внутри и наверняка еще кучи связанных данных. И проще не удалять, чем прописать все зависимости - это лень.

Поэтому ответ удалять или нет зависит от конкретного случая. Иногда надо удалять иногда стоит оставить. А вопрос разрастающейся базы может решить архивирование.

Answer 7

[Miron2]

удалять против придать статус "удаленных" зависит от контекста. Если строка не нужна, то конечно удалять.

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

иногда происходит так что данные принимаются в накопитель, с целью максимизации скорости записи индивидуальных или небольших групп строк, после чего включается линия команд обрабатывающий значительный массив данных, чтобы максимизировать возможности субд, с целью обработки данных, и как результат операции удаляются / добавляются строки в конечной таблице для оперативной подачи высоко - интеллектуальных данных. так приходится строить архитектуру, когда субд дорогая, например Azure Parallel Data Warehouse, а её пиковая производительность для пользователя требует плоских полностью денормализованых строк, выбираемых по единому, стандартному ключу. эти сложности оправдываются когда стоимость хранлища и процессора отступает на второй план перед необходимостью одновременного доступa к огромным массивам данных

в последнем случае, представьте себе завод, где допустим сначала шлют в субд строку "кран под номером один, учесть для трубы номер 2..." через час на стенде кран лопается во время испытаний. кран меняют, в базуи шлют "удалить зпись кран №1". В этом случае статус "удалить" несет нагрузку сродни команды.