Как лучше оптимизировать работу с БД MySQL для 500к записей?

Question

Нужно как-то оптимизировать работу с БД. Собственно, как лучше это сделать?

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

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

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

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

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

А в этом случае как правильно поступить?

Answer 1

Для эффективной работы с БД в данном случае можно использовать транзакции и индексы.

Добавить новые записи из списка висящего в памяти можно с помощью оператора INSERT IGNORE. В этом случае, если запись уже есть в таблице, она не будет добавлена, а игнорирована. Также можно добавлять записи пакетами, чтобы уменьшить нагрузку на БД.
Чтобы изменить поле not_found на 1 в записях, которые не содержатся в списке в памяти, можно использовать оператор UPDATE с условием NOT IN, используя подзапрос с номерами записей из списка в памяти.

Также рекомендуется создать индекс на поле point_id в таблице table, чтобы ускорить поиск и снизить нагрузку на БД.

Примерный код для добавления новых записей из списка висящего в памяти:

START TRANSACTION;
INSERT IGNORE INTO table (point_id, not_found)
SELECT point_id, 0 FROM memory_list
WHERE point_id NOT IN (SELECT point_id FROM table)
COMMIT;

Примерный код для изменения поля not_found на 1 в записях, которые не содержатся в списке в памяти:

START TRANSACTION;
UPDATE table SET not_found = 1 
WHERE point_id NOT IN (SELECT point_id FROM memory_list)
COMMIT;

Надеюсь, это поможет оптимизировать работу с БД.

Answer 2

Подходящий способ, думаю, сильно зависит от того, как много записей из тех которые 500К в памяти, нужно будет добавить. Если новых записей меньше, например 50%, то лучше сравнение сделать в памяти. Выбрать из базы упорядоченный массив (Recordset) table (point_id,not_found) и сравнить с массивом в памяти. Лучше если массив в памяти отсортирован по point_id. Тогда сравнение можно сделать за 1 проход, быстро. Добавлять только измененные и отсутствующие через UPDATE/INSERT по одной записи.

Чем меньше записей из памяти нужно добавить в таблицу, тем интересней будет сравнение в памяти, а не в СУБД.
Индекс по point_id поможет. Выборка Select * from [table] order by point_id будет быстрой.

БЛОГ НА HUSL

Как лучше оптимизировать работу с БД MySQL для 500к записей?

Ответы (2 шт):