Эффективный способ удаления большого количества записей из большой таблицы с помощью Python

Question

У меня есть большая таблица (около 10 миллионов строк), в которой мне нужно удалить записи, которые старше 10 дней (согласно столбцу created_at). У меня есть скрипт на Python, который я запускаю для этого. created_at - это varchar(255) и имеет такие значения, как, например,1594267202000

import mysql.connector
import sys
from mysql.connector import Error

table = sys.argv[1]
deleteDays = sys.argv[2]

sql_select_query = """SELECT COUNT(*) FROM {} WHERE created_at / 1000 < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %s DAY))""".format(table)
sql_delete_query = """DELETE FROM {} WHERE created_at / 1000 < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %s DAY)) LIMIT 100""".format(table)

try:
    connection = mysql.connector.connect(host=localhost,
                                         database=myDatabase,
                                         user=admin123,
                                         password=password123)
    cursor = connection.cursor()

        
    #initial count of rows before deletion
    cursor.execute(sql_select_query, (deleteDays,))
    records = cursor.fetchone()[0]


    while records >= 1:
        # stuck at following below line and time out happens....
        cursor.execute(sql_delete_query, (deleteDays,))
        connection.commit()
        cursor.execute(sql_select_query, (deleteDays,))
        records = cursor.fetchone()[0]

    #final count of rows after deletion
    cursor.execute(sql_select_query, (deleteDays,))
    records = cursor.fetchone()[0]

    if records == 0:
        print("\nRows deleted")
    else:
        print("\nRows NOT deleted")

except mysql.connector.Error as error:
    print("Failed to delete: {}".format(error))
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

Однако, когда я запускаю этот сценарий, и он запускает ЗАПРОС УДАЛЕНИЯ... он не работает из-за:

Не удалось удалить: 1205 (HY000): Превышено время ожидания блокировки; попробуйте перезапустить транзакцию

Я знаю, что innodb_lock_wait_timeout в настоящее время установлен на 50 секунд, и я могу увеличить его, чтобы преодолеть эту проблему, однако я бы предпочел не трогать тайм-аут и... Может быть, я хочу удалить в основном кусками? Кто-нибудь знает, как я могу это сделать здесь, используя мой код в качестве примера?

Answer 1

created_at не имеет индекса и является varchar(255) - Саффик 11 часов назад

Вот твоя проблема. Двое из них.

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

Хотя сохранение целого числа как varchar будет работать, MySQL преобразует его за вас, это плохая практика; он тратит впустую память, допускает неверные данные и работает медленно.

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

alter table your_table modify column created_at bigint;
create index created_at_idx on your_table(created_at);

Теперь, когда created_at является индексированным bigint, ваш запрос должен использовать индекс, и он должен быть очень быстрым.

---

Обратите внимание, что created_at должен быть datetime который сохраняет время с точностью до микросекунд. Затем вы можете использовать функции даты MySQL без необходимости преобразования.

Но это испортит ваш код, который ожидает миллисекундный номер эпохи, поэтому вы застряли на нем. Имейте это в виду для будущих таблиц.

Для этой таблицы вы можете добавить сгенерированный created_at_datetimeстолбец, чтобы упростить работу с датами. И, конечно, проиндексируйте это.

alter table your_table add column created_at_datetime datetime generated always as (from_unixtime(created_at/1000));
create index created_at_datetime on your_table(created_at_datetime);

Тогда твой where пункт становится намного проще.

WHERE created_at_datetime < DATE_SUB(NOW(), INTERVAL %s DAY)

Answer 2

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

DELETE
FROM yourTable
WHERE created_at / 1000 < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL %s DAY))
LIMIT 100;

Обратите внимание: строго говоря, у вас всегда должен быть ORDER BY пункт при использовании LIMIT. То, что я написал выше, может удалить любые 100 записей, соответствующих критериям удаления.