Как ускорить импорт из excel в бд mysql?

Question

[Alexis_D]

Доброго времени суток. Для импорта файлов в бд mysql использую библиотеку PHPExcel, в файле excel имеется 90тыс. строк и при импорте в базу на это уходит, примерно, 35-40 мин, как можно ускорить этот процесс?

Comments

[Roman]

Без профайлера и алгоритма можно только гадать.

Answer 1

[Lander]

Экспортировать excel-файл в CSV и через консоль импортировать CSV в БД.

upd. Если нужно только средствами PHP, то можете использовать пакетное добавление. Это когда в одном INSERT перечисляется сразу данные для нескольких строк.

Answer 2

[Андрей Астафьев]

Как я понял ты читаешь excell файл и построчно инсертешь в бд?
Если так то заведи массив в памяти и вставляй в бд не по одной строке, а сразу целой кучей и дела пойдут быстрее

insert into tablename (id,blabla) values(1,'werwer'),(2,'wqewqe'),(3,'qwewe');

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

Comments

[Alexis_D]

начал инсертить вашим первым вариантом, через несколько values, в базу приходят продублированные строки, но добавляет уже быстрей, как сделать так, чтобы не дублировал?

[Андрей Астафьев]

Alexis_D, получается тебе нужно чтобы дубли не инсертились? если такое поле есть в бд оно не должно добавляться? дубли возникают от того что такая запись уже есть в бд или ты вставляешь одни и теже значния. я правильно понял?

[Roman]

Андрей Астафьев, Это гадание на кофейной гуще.
Автор не знает где у него тормозит и методом тыка пытается что то сделать.
Для начала хотя бы циклы чтения и вставки посмотреть. Может там засада.

[Alexis_D]

Андрей Астафьев, у меня есть проверка на наличие одинаковых записей, мне нужно только ускорить запись

if (isset($_POST['import_b'])) {
           include "Classes/PHPExcel/IOFactory.php";
           $file_path = $_FILES['file_path']['tmp_name'];

           $objPHPExcel = PHPExcel_IOFactory::load($file_path);
           foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
           {
             $highestRow = $worksheet->getHighestRow();
             for ($row = 2; $row <= $highestRow; ++ $row)
             {
               $name = $worksheet->getCellByColumnAndRow(0, $row);
               $nameScore = $worksheet->getCellByColumnAndRow(1, $row);
               $organization = $worksheet->getCellByColumnAndRow(2, $row);

               $result_set = mysqli_query($connect, "SELECT `name`, `nameScore` FROM `test_excel` WHERE `name` = '$name' AND `nameScore` = '$nameScore' ");
               $present_in = mysqli_fetch_row($result_set);
               if(isset($present_in["0"]) && $nameScore == $present_in['1']){
                 echo "";
               }
               else{
                 $result = mysqli_query($connect, "INSERT INTO `test_excel`(name, nameScore, organization) VALUES ('$name', '$nameScore', '$organization')") or die('Ошибка чтения записи:'.mysqli_error());
               }
             }
           }
         }

[Андрей Астафьев]

Alexis_D,
можно попробовать этот вариант тут инсертим все без разбору в таблицу import_excel а потом сольем ее в нужную
можно попробовать вариант с DUPLICATE KEY .

//заводим еще одну таблицу в нее будем инсертить файл без разбору

/*
 * CREATE TABLE import_excel(
 *      `name` CHAR(30),
 *      `nameScore` CHAR(30),
 *      `organization` CHAR(30),
 * )
 */


//запишем все данные в новую таблицу
if (isset($_POST['import_b'])) {
    include "Classes/PHPExcel/IOFactory.php";
    $file_path = $_FILES['file_path']['tmp_name'];

    $objPHPExcel = PHPExcel_IOFactory::load($file_path);
    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        $highestRow = $worksheet->getHighestRow();
        $dataExcell = [];//тут будем хранить данные
        for ($row = 2; $row <= $highestRow; ++$row) {
            //при добавлении в массив можешь дополнительно провалидировать trim, strip_tags и прочее если надо
            $dataExcell[] = sprintf(
                    "('%s', '%s', '%s')",
                    $worksheet->getCellByColumnAndRow(0, $row), 
                    $worksheet->getCellByColumnAndRow(1, $row), 
                    $worksheet->getCellByColumnAndRow(2, $row)
                );
        }
    }
    $sql = mysql_query('INSERT INTO test_excel (name, nameScore, organization) VALUES '.implode(', ', $dataExcell));
    unset($dataExcell);
}

//теперь данные мержим в нужную таблицу
$result = mysqli_query($connect, 'INSERT INTO test_excel (name, nameScore, organization) '
                                    . 'SELECT t1.name, t1.nameScore, t1.organization '
                                    . 'FROM import_excel t1, test_excel t2 '
                                    . 'WHERE t1.name <> t2.name '
                                        . 'and t1.nameScore <> t2.nameScore '
                                        . 'and t1.organization <> t2.organization')  
            or die('Ошибка чтения записи:'.mysqli_error());

[Андрей Астафьев]

Роман, лучше конечно все делать через PDO

[Roman]

цикл for можно развернуть

[Андрей Астафьев]

Alexis_D, проблема в том что прежде чем сделать вставку ты еще проверяешь есть ли эта запись в бд
получается 70 тыс. селектов и каждый запрос это установка соединения выполнить запрос отдать результат и тд со всеми вытекающими. единично он там выполняется за милисекунды, но когда ими бомбардируешь то база начинает разруливать, блокировки и прочее ну тоже быстро но факт в том что их много этих запросов от того и быстродействие хромает. мой вариант просто заводим еще одну таблицу и туда без проверок и прочего вставляем вставляем все записи и потом выполняем еще один запрос на инсерт с селектом где исключаем дубли, после этого таблицу можно очистить. чтобы выполнялся быстрее селект можно составной индекс по трем полям которые не должны дублироваться сделать. или уникальный составной индекс, но с уникальными индексами не стоит злоупотреблять, потому каждый такой сильно торомозит вставку в таблицу.

[Андрей Астафьев]

Роман, это как можно пример?)

[Roman]

Андрей Астафьев,

for ($row = 2; $row <= $highestRow; ++$row) {
            //при добавлении в массив можешь дополнительно провалидировать trim, strip_tags и прочее если надо
            $dataExcell[] = sprintf(
                    "('%s', '%s', '%s')",
                    $worksheet->getCellByColumnAndRow(0, $row), 
                    $worksheet->getCellByColumnAndRow(1, $row), 
                    $worksheet->getCellByColumnAndRow(2, $row)
                );
            if(($row+5) <= $highestRow){
            $dataExcell[] = sprintf(
                    "('%s', '%s', '%s')",
                    $worksheet->getCellByColumnAndRow(0, ++$row), 
                    $worksheet->getCellByColumnAndRow(1, $row), 
                    $worksheet->getCellByColumnAndRow(2, $row)
                );
                   ...
                   ...
                   ...
            }
        }

около 30000 операций сравнения в цикле вместо 90000

кода больше но быстрее.

Answer 3

[Zhainar]

Самый быстрый способ