Как оптимизировать вставку данных в таблицу с 2млн строк?

Question

[Константин Б.]

Есть таблица с на данный момент 2 миллионами товаров. Периодически нужно добавлять новые позиции, при этом данные из таблицы также используем для проверки наличия товара перед вставкой. Проблемы в том что очень часто проверка на наличие совпадает с моментом добавления товара и при перестроении индексов запрос начинает заметно тормозить. Как можно оптимизировать этот сценарий чтобы и индексы были и скорость не падала?

Comments

[Дмитрий]

А там какая то сложная логика при наличии товара? REPLACE или INSERT IGNORE не зайдет?

[Сергей П]

Вопрос нужно конкретизировать:
- какова логика проверки наличия товара?
- если он происходит перед вставкой, как она может совпасть с моментом добавления товара?
- вы делаете это во много потоков, что ли?
- какие индексы есть в таблице?
- какие запросы вы делаете при вставке и проверке?
- какой план генерится для этих запросов?

Без кода не понять в чем там у вас проблема.
2 миллиона строк - это не должно быть проблемой, если есть необходимые индексы.
Порядок вставки (в плане оптимизации) сильно зависит от того, какие и в каком количестве процессы работают с БД одновремено, какие требования и ограничения по консистентности данных, какие размеры пакетов вставляемых данных типичны для рабочего процесса... и т.д.

[mayton2019]

В вопросе мало сведений. Нужно понять сколько вставляем. Сколько времени работает текущий джоб. Нужна ли разделяемя работа (транзакции или батчи) или можно делать эксклюзивно (ночью когда никого нет). Можно на время массовых загрузок отключать индексы. Потом перестраивать. Это в некоторых случаях улучшает характеристики. Можно ли брать MyISAM? Есть ли триггеры?

Если говорить про оптимизацию вообще - то это занятие увлекательное и бесконечное. Нужен критерий когда мы скажем "стоп". Еще оптимизация - это trade off. Сделка. Меняем что-то одно на другое. Как я уже говорил про индексы и ночную работу.

[Константин Б.]

vilinyh, да какой код. Там много всего) Коротко описать льются новые заказы, а менеджеры их обрабатывают. Пока один два менеджера работают то все ок. Как только подключатаются все (около 20) то запрсы становятся медленными, смотрю по query log чуть больше секунды занимает запрос, который в обычное время заниает 14мс

[Константин Б.]

Сергей Паньков, там много челевек работает одновременно идут много выборок и вставок. Выборки по разным таблицам, с джойнами например. И в этот момент запросы даже те что были быстрыми начинают на время замедляться, я думаю как раз из-за индексов

[Константин Б.]

vilinyh, сейчас пока теоризирую больше. Создаю инфраструктуру для логов. Будем смотреть конечно. Базу вынесли отдельно чтобы не было зависимости с ресурсами кода

Answer 1

[d'Ivan]

СУБД может сама проверить наличие записи перед вставкой либо по первичному ключу либо по другому уникальному ключу.
INSERT ... ON DUPLICATE KEY UPDATE - можно указать какие поля обновлять при нахождении. Допустим, обновить дату, наименование и т.д.
Необходимо добавить уникальный индекс, если отсутствует.

при перестроении индексов запрос начинает заметно тормозить

Я не уверен в этом. Как это было замерено?

Comments

[nokimaro]

> ON DUPLICATE KEY UPDATE
увеличивает автоинкрементальный индекс при попытке вставки, даже если она не удалась
то же самое делает INSERT IGNORE

[Slava Rozhnev]

nokimaro, И....?

[nokimaro]

Slava Rozhnev, И ... всё. Комментарий про то что такой нюанс не упомянут, в случае если у нас есть AUTO_INCREMENT ключ.
В сочетании с любовью некоторых использовать int вместо bigint для PRIMARY помноженную на миллионы INSERT можно быстро добраться до 4 294 967 295

[Дмитрий]

nokimaro, ну если они будут гадить даже по миллиону записей в день это займёт 11 лет. Глядишь за 11 лет кто нибудь изобретет uuid

Answer 2

[Сергей П]

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