Работа с большим объемом данных node mysql?

Question

[Sratimon1]

Задача поставлена просто, получаем от клиента одним куском 50 000 +- номеров. Проверяем есть ли они в БД и если нету то записываем. БД будет хранить по началу 10 000 000 записей каждая примерно 200 байт. Так же клиент может запросить эти 50 000 номеров. Номер уникален.
Использую node + express база mysql. ОРМ sequelize.
Вопрос в скорости выполнения этих операций. И правильной реализации.
1) Я получаю массив объектов от пользователя далее по этому массиву делаю выборку данных из mySql, нахожу номера которых нету и записываю их в бд.
Есть ли возможность составить такой SQL если нету номера то записать его, после этого вернуть id номеров ? Или есть другие предложения как сделать это быстрее ?
2) Если держать номера в оперативе, это будет быстрее, но как это реализовать ?
Просто получить ответ от бд и записать его в переменную, но при обновлении базы надо будет снова делать выбору по все базе, информация будет часто меняться.
3) Использовать что то кроме mysql. Тогда какие варианты можете предложить ?
В идеале мне надо держать одну таблицу постоянно в оперативе т.к по ней будет основная выборка / запись данных.
4) Ваши варианты. Заказчик не чем не ограничивает. В данный момент есть VPS с i7-6700 и 32gb RAM.
P.s Изучаю web в общем 9 месяцев (была база в программирование С++, C++ qt, С#). Но с БД в таких объемах не работал. В поисковике в основном лайтовые статьи для новичков. Либо серьезные ребята объясняют серьезные вещи и я не вывожу. В общем прошу помочь , кто чем сможет.

Answer 1

[Александр Кузнецов]

Вы не смотрели на 13.2.5.2 INSERT ... ON DUPLICATE KEY UPDATE Syntax ?

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

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

UPD. Забыл написать - можно вставлять пачками, подобным образом:

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Comments

[Sratimon1]

INSERT IGNORE очень сильно помог, спасибо. Время сократилось с 20-60 сек до 100 мили секунд.
Но теперь следующий вопрос. Мне надо получить id этих записей. И сразу проблема с выборкой данных. Каким образом можно получить 50 000 записей из бд ?
WHERE number = 5 OR number = 8 OR number = 123 пробовал вот так но
при 1 000 53.115ms
при 10 000 278.887ms
при 50 000 слишком долго или что то пошло не так (5 минут нету ответа)
Индексы по number сделал.

[Александр Кузнецов]

Sratimon1, как получить ID можно прочитать вот тут - How can I Insert many rows into a MySQL table and .... На словах, innodb гарантирует непрерывное нарастание AutoIncrement, и получив LAST_INSERT_ID и количество строк, можно получить все ID.

INSERT IGNORE очень сильно помог, спасибо

Очень аккуратно с этой штукой, если что-то поменяется в поле, можно свежую запись потерять, оставив старую.

Каким образом можно получить 50 000 записей из бд ?

Это выгрузка? Если нужно просто получить 50000 записей - берете и читаете, лучше всего фоном. Я делал выгрузку ~ 50000 номенклатур из MySQL, но язык PHP. Строил excel. Сделал так - последовательно запускал фоновый процесс N раз, который выгружал следующие 5000 записей и строил по ним Excel. Процесс возвращал путь к файлу. Потом по полученному массиву имен файлов строил соответствующий Zip-архив с файлами и отдавал клиенту.

В вашем случае можно и не бить, запустить процесс выгрузки, тот, когда закончит, куда-то сохранит ссылку на скачивание файла. Как-то так.

[Алексей Лауд]

WHERE number = 5 OR number = 8 OR number = 123

Если нельзя избавиться от индекса по number (например, сделать WHERE person_id=3), то эту конструкцию лучше заменить на
WHERE number IN (5, 8, 123)

Answer 2

[Виталий Инчин ☢]

Возможно, стоит просто присмотреться присмотреться к Elastic'у

Comments

[Sratimon1]

Я знаю про облачные сервисы, но не в этот раз. Ну по крайней мере я надеюсь )))
Это на разбираться в API подключать это все к node переделывать уже настроенную авторизацию с регистрацией.
Лучше скажите как выбрать из бд 50 000 номеров ? ))
кроме WHERE number = 5 OR number = 8 OR number = 123 не чего в голову не пришло.
Индексацию number сделал. Но все ровно долго.
при 1 000 53.115ms
при 10 000 278.887ms отличный результат
при 50 000 не дождался почему то. Тут должно быть (1 - 1.5с )

[Виталий Инчин ☢]

Быстрее только Полнотекстовый поиск https://habr.com/post/40218/

[abbaboka]

Зачем нужен полнотекстовый поиск, если в задаче поиск по полному совпадению?

[Виталий Инчин ☢]

abbaboka, пере-организовать структуру для полнотекстового поиска - пособ, получить скорость более высокую, чем просто прямая выборка по заранее проиндексированым колонкам. На деле это так, хоть и лютый геморрой. Адекватное решение я предложил в ответе.

[abbaboka]

Виталий Инчин ☢,

пере-организовать структуру для полнотекстового поиска - пособ, получить скорость более высокую, чем просто прямая выборка по заранее проиндексированым колонкам

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

Answer 3

[Andrey Shatokhin]

Про INSERT ... ON DUPLICATE KEY UPDATE вам уже сказали. И это правильно.
Если у вас номер телефона в таблице уникальное поле и при этом не изменяемое - я бы рассмотрел вариант сделать его primary key в этой таблице.
Делать выборку по принципу select * from table where phone in (5,8,123)