среда, 7 октября 2015 г.

Удаление значительного числа строк из большой таблицы MySQL

Если у вас большая таблица (скажем, 10+ млн.строк), то удаление значительной части строк (скажем, 20+%) из нее может стать проблемой.


  1. Во-первых, эта операция будет занимать длительное время и некоторые операции на выборку/модификацию данных в эту таблицу все это время нельзя будет произвести.
  2. Во-вторых, удаление строк не освободит место на диске.


Опишу алгоритм, который я несколько раз использовал, когда мне нужно было провести подобную операцию:
  1. создать клон таблицы:

    create table <new_table> like <old_table>
  2. запомнить максимальный id в старой таблице:

    select max(id) from <old_table>
    Записываем на лист бумаги ;-)
  3. переставить id (автоинкрементный) в новой таблице на это max(id) + запас (скажем +2 миллиона):

    ALTER TABLE <new_table> AUTO_INCREMENT=<old_value>+2000000

    Запас нужно выбирать исходя из того, сколько записей может быть вставлено за то время, пока вы делаете операцию. Помножив оценку на 10 - будет в самый раз.
  4. скопировать данные, которые мы хотим оставить из старой таблицы в новую (ниже опишу еще пару трюков)

    insert into <new_table> select * from <old_table> where ...
  5. переименовать обе таблицы:

    RENAME TABLE <old_table> TO <very_old_table>, <new_table> TO <old_table>

    Да, можно делать переименование двух таблиц за одну операцию.
  6. проверить, что максимальные ID в обеих таблицах совпадают. Если не совпадают, докопировать недостающие записи из старой таблицы в новую:

    insert into <new_table> select * from <old_table> where ... and id > <max(id)>

    <max(id)> берем из второго шага.
  7. удалить старую таблицу

    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> left join <good_table> on <old_table>.id=<bad_ids>.id where <bad_ids>.id is NULL

Чтобы JOIN'илось быстро, следует по колонке id в таблице good/bad_ids построить индекс.