Как максимально быстро добавить огромное количество записей в БД без дублей?

Question

[Vadik7777]

Всем привет!

Как максимально быстро добавить огромное количество записей в БД без дублей?

Пробовал так:

foreach($numbers as $num){
   $exist = Model::where('num', $num)->exists();
   if (!exist) {
      $new = new Model();
      $new->num = $num;
      $new->save();
   }
}

Это очень долгий процесс, даже для 1000 записей, а что если будет несколько сотен тысяч записей, сразу повесится)

Подскажите каким образом можно вставить быстро и без дублей?

Comments

[Shavadrius]

Если у вас этот столбец является идентификатором и не должен повторяться - можете это обозначить в схеме таблицы в БД, а потом просто вставлять без проверки. Ошибки ловить, но ничего с ними не делать...
Можете еще предварительно от дублей почистить сам массив $numbers.

[Vadik7777]

Shavadrius, столбец не является идентификатором, елсиб являлся, то да.

[mrFlyer]

Vadik7777, Ему не обязательно быть идентификатором, можно сделать его уникальным значением и даже уникальным в связке с каким то еще столбцом. Но мне кажется это не совсем верное решение по нагрузке на железо будет.

Answer 1

[mrFlyer]

Много записей это сколько? Вы просто каждый раз делаете запрос к базе для проверки. Если переложить проверку поиска дубля на пхп, то проблема решится.

Получите все записи разом из бд, в пхп найдите несуществующие записи и через DB::insert проведите вставку.

Что то типа такого:

$models = Model::select("id","num")->get();
        foreach($numbers as $num){
            if (!$models->where("num",$num)->first()) {
                \DB::table("table_name")->insert(["num"=>$num]);
            }
        }

Ну и как вариант вытащить все записи из списка numbers и сравнить каких не хватает:

$models = Model::select("id","num")->whereIn("num",$numbers)->get();
        foreach($numbers as $num){
            if (!$models->where("num",$num)->first()) {
                \DB::table("table_name")->insert(["num"=>$num]);
            }
        }

Comments

[Vadik7777]

У Вас 1 вариант, а потянет ли он вытащить сразу миллион записей или больше? Допустим база растет каждый день по миллиону записей, получается на 10 день нужно будет проверить 10 миллионов записей быстро, и добавить новый миллион записей без дублей.

[Vadik7777]

mrFlyer, 800К., на своем примере, который выше пробовал на 1К записей, обработка долгая получилась
ну и в базе порядка 15миллионов записей. все 15 лямов прокручивать, даже не знаю.

[mrFlyer]

Vadik7777, ну тогда второй вариант. если в numbers тоже миллионы записей, тогда вытаскивать частями по 100т например(в зависимости от возможностей железа) и сравнивать.

Проблема лежит в кол-ве обращений к базе. Чем меньше обращений, тем будет шустрее.

[Vadik7777]

mrFlyer, все равно получается это даже не секунды на обработку, а минуты и даже часы, блин(

[mrFlyer]

Vadik7777, ну ставьте железо серьезное и будет быстрее.

Можно отказаться от моделей, вытаскивайте записи в виде массива. Вам по сути не нужно создавать объект модели и тратить на него ресурсы.

$dataArray = \DB::table('table_name')->select('id','num')->get();

[Vadik7777]

mrFlyer, спасибо за подсказки!

[Ivan Kudinov]

mrFlyer, insert по одной строке в цикле существенно нагрузит БД. Лучше отправлять пачками - будет меньше обращений к БД.

Про транзакции забывать не стоит и помнить про конкурентность.

Answer 2

[Ivan Kudinov]

Как вариант: отфильтровать `$numbers` на стороне PHP и вставить пачкой.

// избавляемся от возможных дубликатов
$numbers = collect($numbers)->unique();
// pluck('num') вернет нам список значений атрибута num, а не список моделей
$existingNumbers = Model::query()->whereIn('num', $numbers->toArray())->pluck('num');
// с помощью diff получаем элементы, которых нет в $existingNumbers
$newNumbers = $existingNumbers->diff($numbers)->mapWithKeys(function ($value, $key) {
    // ['one', 'two'] => [['num' => 'one'], ['num' => 'two']]
    return [$key => ['num' => $value]];
});

Model::query()->getConnection()->transaction(function () use ($newNumbers) {
    Model::query()->insert($newNumbers->toArray()); 
});

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

// избавляемся от возможных дубликатов
$newNumbers = collect($numbers)->unique();
Model::query()->toBase()->whereIn('num', $numbers->toArray())
      ->chunk(500000, function ($existingNumbers) use (&$newNumbers) {
           // с помощью diff получаем элементы, которых нет в $existingNumbers
          $newNumbers = $newNumbers->diff($existingNumbers);
      });

// ['one', 'two'] => [['num' => 'one'], ['num' => 'two']]
$newNumbers = $newNumbers->mapWithKeys(function ($value, $key) {
    return [$key => ['num' => $value]];
});

Model::query()->getConnection()->transaction(function () use ($newNumbers) {
    Model::query()->insert($newNumbers->toArray()); 
});

Также можно воспользоваться ON CONFLICT, если СУБД поддерживает подобное. Например, у SQLite - ON CONFLICT DO, у MySQL - INSERT IGNORE. Это позволит избавиться от выгрузки данных из БД, что уменьшит потребление памяти приложением и сократит количество запросов.
Laravel имеет для этого метод Query::insertOrIgnore(), который будет глушить все ошибки от некоторых БД, но для некоторых не поддерживается. Поддерживаемые БД: MySQL, SQLite, PostreSQL.
Необходимо, чтобы на атрибут `num` в БД стоял constraint unique, иначе БД просто вставит дубликат.

$numbers = collect($numbers)->unique()->mapWithKeys(function ($value, $key) {
    return [$key => ['num' => $value]];
});

Model::query()->getConnection()->transaction(function () use ($numbers) {
    Model::query()->insertOrIgnore($numbers->toArray());
});

Индексы и explain изучить не помешает. Размер чанка подобрать по возможностям железа.
Индекс на num значительно ускорит выборку, но скорость вставки снизится.
Так же отказ от ORM (Eloquent), объектов и использование голого SQL с PDO ускорит работу.
На большие данные и нагрузки нужно мощное железо. Может потребоваться масштабирование и т.д.