Несколько вопросов по вопросу работы с большими данными , MySQL (версия пока не известна, где-то в районе 5.6)

[Majestio]

Всем привет!

Собственно, возникли вопросы по оптимизации. Ибо делать поэлементно - не наш метод...

Исходные данные:

* Есть таблица в БД пусть `table`, в ней есть в записях поле biginteger `point_id` уникальное, и поле `not_found`. Остальные поля не важны.
* В памяти висит список порядка из 500 000 номеров (а точнее записей, в которых есть номер).

Нужно организовать две операции:

1) В таблицу `table` добавить все записи из списка в памяти, но только тех, которых еще там нет - по полю `point_id`
2) В таблице `table` изменить поле `not_found` на 1 во всех записях, которые отсутствуют в списке висящем в памяти, по полю `point_id`

Смущает количество 500 000. Были бы десятки или сотни, можно было бы как-то поиграться с IN, NOT IN.

Какие варианты? Может как-то со временными таблицами поиграться?

[Akina]

WHERE IN и WHERE NOT IN - это самый медленный вариант связывания/антисвязывания. Тут разве что умный сервер сообразитЮ что по сути перед ним тупой JOIN.

Цитата Majestio @ 23.05.23, 16:10

Какие варианты? Может как-то со временными таблицами поиграться?

Зависит от СУБД. И даже от точной версии. Ну и от статистики данных.

Например, первая задача в MySQL прекрасно решается вульгарным INSERT IGNORE. Вторая же лучше всего сработает через индексированную временную таблицу и, в зависимости от количества записей в основной таблице, либо LEFT JOIN WHERE IS NULL, либо WHERE NOT EXISTS.

[Majestio]

Akina, благодарю!!! Ты как всегда даешь супер ответы

[Majestio]

Akina, нужен еще твой совет.

Вот какая ситуация. Заказчик требует детальных логов на каждую сессию. Что вставлено, что изменено, что удалено. С "пакетными" изменениями напрямую я это не могу получить, верно? Пока на ум взбрело два подхода:

1. Делаю "снимок" таблицы во временную по полям point_id и not_fount. Выполняю преобразования по твоему совету выше. Потом сравниваю временную таблицу и реальную и нахожу изменения для логов.
2. Вытаскиваю в память всю таблицу (только поля point_id и not_fount). По ним уже формирую INSERT без IGNORE в памяти порциями, ну и UPDATE как-нибудь, либо порциями, либо формированием временной таблицы и запросом. Ну а логи сразу формируются по результатам сравнений.

Детали

Таблица в БД больше чем 2 500 000 записей не планируется расширятся, заказчик мамой клянется
Движок БД, уточнил - MariaDB v.10.4.25
Доступ к БД будет гарантировано монопольный

Какие соображения?

[Akina]

Цитата Majestio @ 24.05.23, 06:00

Заказчик требует детальных логов на каждую сессию. Что вставлено, что изменено, что удалено.

Непонятно, на каком уровне нужно логирование. Таблиц на уровне SQL-сервера? или объектов на уровне приложения?

Цитата Majestio @ 24.05.23, 06:00

С "пакетными" изменениями напрямую я это не могу получить, верно?

Если речь о логировании уровня таблиц, причём логировать надо только фактические изменения - то не вижу проблемы от слова "совсем".

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

Цитата Majestio @ 24.05.23, 06:00

Делаю "снимок" таблицы во временную по полям point_id и not_fount. Выполняю преобразования по твоему совету выше. Потом сравниваю временную таблицу и реальную и нахожу изменения для логов.

Вот тебе делать нечего... есть же версионка. Temporal Tables+

[Majestio]

Спасибо, буду разбираться. Триггеры - интересно. Возможно и временных таблиц не понадобится тогда.