Удаление больших объемов данных из Mysql таблиц

Игорь Грегорченко

26.09.2020 03:34

Если вам приходится удалять десятки и сотни тысяч записей из таблиц, вы знаете, что эта работает медленно. Ясно, ведь Mysql в этом случае должен пройтись по каждой записи и удалить её с диска.

В [https://onthe.io/ .io] мы используем (т.н.) оконные таблицы. Это когда вы храните данные в таблице всего за последний час (или другой промежуток). А значит, вы не только много туда пишите, но и много удаляете. В случае, если потеря данных не фатальна, можно использовать MEMORY таблицы. Однако для большинства случаев это, конечно, не подойдет. Ведь терять данные всякий раз при перезагрузке сервера не хочется.

Почему DELETE работает медленно

Прежде всего, нужно понимать, что удаление 10 тыс. записей – это 10 тыс. удалений по одной записи. А каждое удаление – это несколько операций записи изменений (в данных и индексах) на диск.

Кроме этого, прежде чем удалить записи, Mysql должен их сначала выбрать. А в этом случае используются те же правила, что и при выборках. Если индексы настроены плохо, операция DELETE станет еще медленнее.

Установка индексов

Для проверки использования индексов достаточно DELETE заменить на SELECT count(*):
***DELETE*** FROM users WHERE ts < 5
## Выясним, используется ли здесь индекс

**EXPLAIN SELECT count(*)** FROM users WHERE ts < 5
## Замена на SELECT позволит проверить использование индексов

Тогда сможем убедиться, что проблема в индексе:

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | ***key***  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | ***NULL*** | NULL    | NULL | 3224 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

## Стоит установить индекс на колонку ts чтобы ускорить удаление

Использование партиций

Индексы помогут при удалении сравнительно небольших объемов. Однако, если приходится постоянно удалять много (как в нашем случае), стоит посмотреть на [https://dev.mysql.com/doc/refman/5.7/en/partitioning.html партиционирование].

Партиционирование позволяет разбить таблицу на несколько физических блоков

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

Чтобы проверить это на практике, создадим простую таблицу такой структуры:

**tmp**: id | title | datetime

## id, заголовок и дата создания заголовка

Наполним ее тестовыми данными (несколько десятков тысяч записей) и удалим данные:
DELETE FROM tmp WHERE datetime < '2017-05-31 11:00:00'
## Удалим часть данных из таблицы

Запрос выполнился довольно медленно, удалив около 25 тыс. записей:

Query OK, 25750 rows affected (***0.27 sec***)

Убедимся, что проблема не в индексе (мы его создали, но на всякий случай проверим):
EXPLAIN SELECT count(*) FROM tmp WHERE datetime < '2017-05-31 11:00:00';

Увидим, что индекс используется – тут все хорошо:

+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | **key**      | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | tmp   | NULL       | range | datetime      | **datetime** | 5       | NULL | 28395 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+-------+----------+--------------------------+

Проверим также системную переменную, которая показывает количество удаленных записей из всех InnoDB таблиц:
SHOW GLOBAL STATUS LIKE 'Innodb_rows_deleted'G

Variable_name: Innodb_rows_deleted
        Value: 25750

## Увидим количество удаленных строк

Мы проводим эксперимент в изолированной среде, поэтому других удалений тут не происходит.

Выбор схемы партиционирования

Поскольку мы решаем проблему удаления, нам необходимо иметь схему, в которой мы сможем удобно удалять (чистить) целые партиции. Нам необходимо удалять данные за час, поэтому мы создадим HASH партицию на основе часа из поля datetime:
ALTER TABLE tmp PARTITION BY hash( **HOUR(datetime)** ) PARTITIONS 24;
## 24 партиции потому, что 24 часа в сутках

Проверим, как выглядит распределение наших данных по партициям:
SELECT PARTITION_ORDINAL_POSITION, TABLE_ROWS, PARTITION_METHOD

FROM information_schema.PARTITIONS

WHERE TABLE_SCHEMA = ‘test’ AND TABLE_NAME = ‘tmp’;

+----------------------------+------------+------------------+
| PARTITION_ORDINAL_POSITION | TABLE_ROWS | PARTITION_METHOD |
+----------------------------+------------+------------------+
|                          1 |          0 | HASH             |
|                          2 |          0 | HASH             |
|                          3 |          0 | HASH             |
|                          4 |          0 | HASH             |
|                          5 |          0 | HASH             |
|                          6 |          0 | HASH             |
|                          7 |          0 | HASH             |
|                          8 |          0 | HASH             |
|                          9 |          0 | HASH             |
|                         10 |          0 | HASH             |
|                         **11 |      25394** | HASH             |
|                         **12 |      31171** | HASH             |
|                         13 |          0 | HASH             |
|                         14 |          0 | HASH             |
|                         15 |          0 | HASH             |
|                         16 |          0 | HASH             |
|                         17 |          0 | HASH             |
|                         18 |          0 | HASH             |
|                         19 |          0 | HASH             |
|                         20 |          0 | HASH             |
|                         21 |          0 | HASH             |
|                         22 |          0 | HASH             |
|                         23 |          0 | HASH             |
|                         24 |          0 | HASH             |
+----------------------------+------------+------------------+

## номер партиции будет соответствовать часу колонки datetime

Как видим, данные в таблице помещены только в две партиции. Они соответствуют текущему и предыдущему часу. Что нам нужно – это очищать партицию за тот час, который нам уже не нужен. Для этого существует операция TRUNCATE :

ALTER TABLE tmp **TRUNCATE PARTITION p11**
## Эта операция выполнилась за (0.01 sec)

Если мы проверим счетчик удаленных InnoDB записей, увидим там:

Variable_name: Innodb_rows_deleted
        Value: 25750

## Значение не изменилось

Это подтверждает тот факт, что TRUNCATE работает принципиально не так как DELETE. Вместо удаления каждой записи, таблица (или ее партиция) очищается на уровне структуры. Если очень грубо, то Mysql удаляет старый файл данных и создает новый. А эта операция выполняется значительно быстрее построчного удаления.

<h2>TL;DR

Если вам нужно удалять большие объемы данных из Mysql, следуйте двум советам:

Стройте индексы для ускорения выборки при удалении, заменив DELETE FROM на EXPLAIN SELECT count(*) FROM.
Используйте [https://dev.mysql.com/doc/refman/5.7/en/partitioning.html партиционирование] и TRUNCATE PARTITION для эффективного удаления большого количества строк.