Блог

Главная - Вопросы по программированию - Каков наиболее оптимальный способ массовой вставки и обновления данных с помощью laravel

Каков наиболее оптимальный способ массовой вставки и обновления данных с помощью laravel

#php #mysql #laravel #eloquent

Вопрос:

У меня есть команда laravel, которая выполняет поиск данных из базы данных SQL Server и вставляет каждую строку (или обновляет ее, если она уже существует) в базу данных MySQL.

Это код в том виде, в каком он работает в настоящее время:

Во — первых, поиск строк из SQL Server:

  $etiquetas = DB::connection('delisur_picking')
            ->table('deli_eti_correl')
            ->where('correl', '>', $lastLabel)
            ->orderBy('correl', 'asc')
            ->get()
            ->chunk(100);

А затем вставки и/или обновления:

 $i = 0;
$numins=0;
$numupd=0;
$tot=0;
$locations=DB::connection("mysql")->table("locations")->select("id", "code")->get();
$alocations=array();
foreach($locations as $l){
    $alocations[$l->id]=$l->code;
}
foreach ($etiquetas as $chunk) {
    $i = $i   1;
    Log::info('Cargando '.$i.' chunks de '.count($etiquetas));
    $arrlabelI=array();
    $arrlabelU=array();
    foreach ($chunk as $etiqueta) {
        $tot  ;
        $label=array();
        $labelExists=DB::connection("mysql")->table("labels")->where("correlative", $etiqueta->correl)->exists();
        $locationid=array_search($etiqueta->origen, $alocations);
        $label["cod_prod"]=$etiqueta->cod_prod;
        $label["nom_prod1"]=trim($etiqueta->nom_prod1);
        $label["nom_prod2"]=trim($etiqueta->nom_prod2);
        $label["q_caja"]=$etiqueta->q_caja;
        $label["fec_elab"]= $etiqueta->fec_elab;
        $label["lote"]= $etiqueta->lote;
        $label["fec_sys"]= $etiqueta->fec_sys;
        $label["actual_location"]= trim($etiqueta->origen) == '' ? null : ($locationid!==false ? $locationid : null);
        if($labelExists){
            $arrlabelU[$etiqueta->correl]=$label;
            Log::info("Correlativo marcado para actualizacion: $etiqueta->correl");                
        }else{
            $label["correlative"]=$etiqueta->correl;
            $arrlabelI[]=$label;
            Log::info("Correlativo marcado para insercion: $etiqueta->correl");
        }
    }

    DB::connection('mysql')->table("labels_alt")->insert($arrlabelI);
    $numins =count($arrlabelI);
    foreach($arrlabelU as $idx => $row){
        DB::connection('mysql')->table("labels")->where("correlative", $idx)->update($row);
        $numupd  ;
    }
}

Таблица меток определяется следующим образом:

 CREATE TABLE `labels` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `correlative` BIGINT(20) NULL DEFAULT NULL,
    `cod_prod` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `nom_prod1` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `nom_prod2` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `q_caja` BIGINT(20) NULL DEFAULT NULL,
    `fec_elab` DATETIME NULL DEFAULT NULL,
    `lote` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `fec_sys` DATETIME NULL DEFAULT NULL,
    `actual_location` VARCHAR(191) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
)

Для каждого фрагмента требуется 1/2 секунды, чтобы заполнить массивы $alabelsI и $alabelsU (учитывая количество строк на фрагмент, что составляет приблизительно 50 секунд) и полсекунды, чтобы вставить данные в $alabelsI

Вопросы:

  1. Есть ли более быстрый способ заполнения массивов?
  2. Из — за ограничений клиента мы не можем изменять существующие таблицы в базе данных MySQL. Как следствие, я не могу определить корреляционный столбец как уникальный ключ, который требуется в MySQL для использования функции Laravel upsert . Если я не могу использовать upsert , есть ли более оптимальный способ, чем приведенный выше код, для вставки или обновления данных?

Заранее спасибо

Комментарии:

1. Пожалуйста, предоставьте сгенерированный SQL для INSERT ввода или вывода.

2. «мы не можем изменить существующие таблицы в базе данных MySQL», Если это означает, что вы даже не можете добавить INDEX , то вам следует поискать другую работу.

3. Мы не можем, потому что клиент этого не позволяет. Вот почему я написал «из-за ограничений клиента». Мы знаем, как это сделать. Кроме того, сгенерированный SQL слишком длинный, потому что представляет собой вставку до 100 строк, и для этого мы используем красноречивый.

4. Такая «пакетная вставка» намного быстрее, чем 100 однорядных вставок. (Я не говорю на языке Laravel, поэтому не могу обратиться к этой стороне вопроса.)

5. insert Функция в Laravel позволяет вставлять одну или несколько строк в одну таблицу. Это позволяет, в моем случае, вставлять до 100 строк одним вызовом вместо того, чтобы делать это с одним вызовом на строку.

Ответ №1:

решенный

Самая быстрая форма, которую мы нашли, чтобы сделать то, что нам нужно, — это:

  1. Создайте новую таблицу (с именем labels_alt), которая является копией меток
  2. Замените цикл foreach выше на следующий. Этот код вставляет все строки в новую таблицу
 foreach ($etiquetas as $chunk) {
    $i = $i   1;
    Log::info('Cargando '.$i.' chunks de '.count($etiquetas));
    $arrlabelI=array();
    foreach ($chunk as $etiqueta) {
        $tot  ;
        $label=array();
        $locationid=array_search($etiqueta->origen, $alocations);
        $label["cod_prod"]=iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->cod_prod);
        $label["nom_prod1"]=iconv('Windows-1252', 'UTF-8//TRANSLIT', trim(iconv('Windows-1252', 'UTF-8//TRANSLIT', "$etiqueta->nom_prod1")));
        $label["nom_prod2"]=iconv('Windows-1252', 'UTF-8//TRANSLIT', trim(iconv('Windows-1252', 'UTF-8//TRANSLIT', "$etiqueta->nom_prod2")));
        $label["q_caja"]=iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->q_caja);
        $label["fec_elab"]= iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->fec_elab);
        $label["lote"]= iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->lote);
        $label["fec_sys"]= iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->fec_sys);
        $label["actual_location"]= trim($etiqueta->origen) == '' ? null : ($locationid!==false ? $locationid : null);
        $label["correlative"]= iconv('Windows-1252', 'UTF-8//TRANSLIT', $etiqueta->correl);
        $arrlabelI[]=$label;
    }

    DB::connection('mysql')->table("labels_alt")->insert($arrlabelI);
    $numins =count($arrlabelI);
}
$pstmt=DB::connection("mysql")->getPDO()->prepare("call insertLabels()");
$pstmt->execute();
  1. Создайте хранимую процедуру в базе данных, которая выполняет вставку и обновление меток:
 CREATE DEFINER=`root`@`localhost` PROCEDURE `insertLabels`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE icorrelative, qcaja BIGINT(20);
    DECLARE codprod, nomprod1, nomprod2, ilote VARCHAR(191);
    DECLARE fecelab, fecsys DATETIME; 
    DECLARE createdat, updatedat TIMESTAMP;
    DECLARE done INT DEFAULT FALSE;
    DECLARE ext_cursor CURSOR FOR 
    SELECT labels_alt.correlative, labels_alt.q_caja, labels_alt.cod_prod, labels_alt.nom_prod1, labels_alt.nom_prod2, labels_alt.lote, labels_alt.fec_elab, labels_alt.fec_sys, labels_alt.created_at 
    FROM labels_alt INNER JOIN labels ON labels_alt.correlative=labels.correlative;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    INSERT INTO labels (correlative, q_caja, cod_prod, nom_prod1, nom_prod2, lote, fec_elab, fec_sys, created_at, updated_at) SELECT correlative, q_caja, cod_prod, nom_prod1, nom_prod2, lote, fec_elab, fec_sys, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP FROM labels_alt a NATURAL LEFT JOIN labels b WHERE b.correlative IS NULL;
    OPEN ext_cursor;
    read_loop: LOOP
        FETCH ext_cursor INTO icorrelative, qcaja, codprod, nomprod1, nomprod2, ilote, fecelab, fecsys, createdat; 
        IF done THEN
            LEAVE read_loop;
        END IF;
        UPDATE labels SET q_caja=qcaja, cod_prod=codprod, nom_prod1=nomprod1, lote=ilote, fec_elab=fecelab, fec_sys=fecsys, created_at=createdat, updated_at=CURRENT_TIMESTAMP WHERE correlative=icorrelative;
    END LOOP;
    CLOSE ext_cursor;
    TRUNCATE labels_a<
END

В двух словах, заключается в том, чтобы максимально сократить выполнение инструкций SQL от Laravel, переместив указанные инструкции в хранимую процедуру.

Метки: