Если у вас большая таблица (скажем, 10+ млн.строк), то удаление значительной части строк (скажем, 20+%) из нее может стать проблемой.
Опишу алгоритм, который я несколько раз использовал, когда мне нужно было провести подобную операцию:
Для плохих:
insert into <new_table> select * from <old_table> left join <good_table> on <old_table>.id=<bad_ids>.id where <bad_ids>.id is NULL
- Во-первых, эта операция будет занимать длительное время и некоторые операции на выборку/модификацию данных в эту таблицу все это время нельзя будет произвести.
- Во-вторых, удаление строк не освободит место на диске.
Опишу алгоритм, который я несколько раз использовал, когда мне нужно было провести подобную операцию:
- создать клон таблицы:
create table <new_table> like <old_table> - запомнить максимальный id в старой таблице:
select max(id) from <old_table>
Записываем на лист бумаги ;-) - переставить id (автоинкрементный) в новой таблице на это max(id) + запас (скажем +2 миллиона):
ALTER TABLE <new_table> AUTO_INCREMENT=<old_value>+2000000
Запас нужно выбирать исходя из того, сколько записей может быть вставлено за то время, пока вы делаете операцию. Помножив оценку на 10 - будет в самый раз. - скопировать данные, которые мы хотим оставить из старой таблицы в новую (ниже опишу еще пару трюков)
insert into <new_table> select * from <old_table> where ... - переименовать обе таблицы:
RENAME TABLE <old_table> TO <very_old_table>, <new_table> TO <old_table>
Да, можно делать переименование двух таблиц за одну операцию. - проверить, что максимальные ID в обеих таблицах совпадают. Если не совпадают, докопировать недостающие записи из старой таблицы в новую:
insert into <new_table> select * from <old_table> where ... and id > <max(id)>
<max(id)> берем из второго шага. - удалить старую таблицу
drop table <very_old_table>
Обещал пару трюков к шагу 4:
Что делать, если условие по определению хороших или плохих строк очень сложное? Можно отдельными запросами сохранить хорошие или плохие ID в отдельную таблицу (с одним полем ID). А потом при-JOIN-ить эту таблицу к нашему селекту.
Для хороших:
insert into <new_table> select * from <old_table> join <good_ids> on <old_table>.id=<good_ids>.id
insert into <new_table> select * from <old_table> join <good_ids> on <old_table>.id=<good_ids>.id
Для плохих:
insert into <new_table> select * from <old_table> left join <good_table> on <old_table>.id=<bad_ids>.id where <bad_ids>.id is NULL
Чтобы JOIN'илось быстро, следует по колонке id в таблице good/bad_ids построить индекс.