Как ускорить вставку Mysql с помощью nullif?

я загружаю файл CSV через PHP и вставляю во временную таблицу, это то, что мой загрузочный скрипт PHP и запрос MySQL.

    $i=0;
    while (($data = fgetcsv($source, 1000, ",")) !== FALSE)
        {   
            if($i!=0) {
                $column1=$data[0];
                $column2=$data[1];
                $column3=$data[2];
                $column4=$data[3];
                $column5=$data[4];
                $column6=$data[5];
                $query= "INSERT INTO temp_table(column1,column2,column3,column4,column5,column6,load_datetime)
    VALUES (nullif('$column1',''), nullif('$column2',''), nullif('$column3',''), nullif('$column4',''), nullif('$column5',''), nullif('$column6',''),now())";
                mysql_query($query) or die(mysql_error());
            }
            $i++;
        }

и мой файл CSV имеет 25000 записей, это пример строки из моего CSV

        column1,column2,column3,column4,column5,column6
        ,,,13:57:18,,23:00:19

проблема, с которой я сталкиваюсь, заключается в том, что загрузка CSV занимает более 10 минут, а между ними страница становится пустой. Мои настройки PHP: upload_max_filesize: 100M post_max_size:100M max_execution_time:1000 max_input_time:1000.

это моя деталь таблицы

   CREATE TABLE temp_table (
       id int(11) NOT NULL,
       column1 time DEFAULT NULL,
       column2 time DEFAULT NULL,
       column3 time DEFAULT NULL,
       column4 time DEFAULT NULL,
       column5 time DEFAULT NULL,
       column6 time DEFAULT NULL,
       load_datetime datetime DEFAULT NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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


mysql csv php php-ini nullif
person davidb    schedule 03.10.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Я бы рекомендовал вам прекратить использовать расширение mysql (mysql_*)
и начать использовать mysqli или PDO.

Попробуйте сгруппировать данные нескольких строк в один запрос.

Пример:

$i  = 0;
$i2 = 0;
$sql = "INSERT INTO temp_table(column1,column2,column3,column4,column5,column6,load_datetime) VALUES ";
$values = [];
while (($data = fgetcsv($source, 1000, ",")) !== FALSE) {
    if(++$i == 1)
        continue;
    else {
        $data = array_map(function($item) {
            return $item == '' ? 'NULL' : "'$item'";
        }, $data);
        $values[] = "(" . implode(", ", $data) . ', now()' . ")";
        if (++$i2 == 500) {
            mysql_query ($sql . implode(', ', $values)) or die(mysql_error());
            $values = [];
            $i2 = 0;
        }
    }
}
if (!empty($values)) {
    mysql_query ($sql . implode(', ', $values)) or die(mysql_error());
    $values = [];
}
person Neodan    schedule 03.10.2017
comment
Предыдущий я забыл добавить now() попробуйте сейчас. Если не поможет, попробуйте вывести сгенерированный sql echo $sql . implode(', ', $values). - person Neodan; 03.10.2017
comment
ваше решение заняло менее 2 минут и вставило 25000 записей. Спасибо огромное. - person davidb; 03.10.2017
comment
@davidb 1if(++$i == 1) continue;` пропускает первую строку (-; - person Neodan; 03.10.2017

arrow_upward
0
arrow_downward

Я думаю, что в PHP-скрипте требуется время. Попробуйте приведенный ниже код и дайте мне знать, насколько он улучшился, а также вы можете группировать от 50 до 100 записей за раз.

 while (($data = fgetcsv($source, 1000, ",")) !== FALSE)
{   
if($i!=0)
{
$query= "INSERT INTO temp_table(column1,column2,column3,column4,column5,column6,load_datetime)
VALUES (nullif('$data[0]',''), nullif('$data[1]',''), nullif('$data[2]',''), nullif('$data[3]',''), nullif('$data[4]',''), nullif('$data[5]',''),now())";
mysql_query($query) or die(mysql_error());
}
$i++;
}
person Bhushan    schedule 03.10.2017
comment
спасибо, я попробовал это, это занимает то же время, что и раньше. - person davidb; 03.10.2017
comment
Могу ли я узнать, сколько времени это занимает при непосредственном выполнении в БД так же, как это написано в php-коде? - person Bhushan; 03.10.2017
comment
Также в коде создания таблицы идентификатор должен быть полем автоматического увеличения или иметь значение по умолчанию. - person Bhushan; 03.10.2017
comment
Если выполнение запроса не занимает больше времени, вы можете просмотреть следующие статьи. stackoverflow .com/questions/6245971/ Благодаря этому вы можете узнать, где в PHP-коде требуется больше времени. - person Bhushan; 03.10.2017
comment
поле id в таблице является первичным ключом и установлено как автоинкремент. - person davidb; 03.10.2017
comment
@Jack Связь между PHP и MySQL - самая медленная часть в этой ситуации. Так что для ОП просто нужно уменьшить сумму запроса. - person Neodan; 03.10.2017