Как правильно удалить лишние данные из большой таблицы Mysql?

Question

[protsey]

Доброго времени суток. Есть база данных Mysql Ver 14.14, которая не обслуживается уже давненько и имеет солидный вес, короче говоря, надо чистить. Есть таблица следующей структуры:

+----------+------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------------------+-------+
| insertts | timestamp | NO | | CURRENT_TIMESTAMP | |
| ts | timestamp | NO | | 0000-00-00 00:00:00 | |
| code | int(11) | YES | | NULL | |
| value | int(11) | YES | | NULL | |
| imei | bigint(20) | YES | MUL | NULL | |
+----------+------------+------+-----+---------------------+-------+

Записей в этой таблице аж пять с половиной миллиардов, а весит она более 700 Гб... Удалять надо по столбцу ts, оставить данные за прошедший год, все остальное отсеять. Остаться должно навскидку процентов 30. Была попытка удалять через

delete from `sensors` where `ts` < '2018-01-01 00:00:00' limit 1000;

зациклив его в баше без паузы между запросами. По началу шло вроде резво достаточно, на один запрос до пол секунды уходило, но спустя около 150 миллионов записей запрос началу весить уже под 10 секунд, что заставило задуматься что что то тут не так. Как правильно поступить в данной ситуации? Прошу сильно не пинать, глубокой теории не знаю, но приходится копаться на продакшине рабочего ресурса.
Заранее спасибо!

Answer 1

[Alex]

Похоже, что для поля ts нет индекса. Поэтому происходит скан всей таблицы при поиске данных. Ну, точнее, поскольку LIMIT, то не всей, а только пока находятся соответвующие записи. По мере их удаления приходится сканировать все дальше и дальше - соответственно время и растет.

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

Answer 2

[Дмитрий Шицков]

mysql.rjweb.org/doc.php/deletebig