Как записать массив PHP в базу данных MySQL?

Question

[gvg1]

Имеется массив массивов $m и таблица БД из трёх (упрощённо) элементов (полей) - mediumint, mediumint и varchar.
Имею 3 рабочих варианта записать массив в БД.
Но все они примерно одинаково медленные.
Как ускориться?
Можно без PDO или без одного из циклов или с циклом другого вида (значения числовых полей не непрерывные, были удаления элементов) или вообще без циклов одним INSERT'ом (здесь смайлик) или ещё как-то.

$pdo=new PDO("mysql:host=$host;dbname=$dbname;charset=$charset",$username,$password);
$insert='INSERT INTO table1 (i1,i2,s1) VALUES (:i1,:i2,:s1)';
$q=$pdo->prepare($insert);
foreach ($m as $key=>$value){
  foreach ($value as $value1){
    $q->bindParam(':i1',$key,PDO::PARAM_INT);
    $q->bindParam(':i2',$value1[0],PDO::PARAM_INT);
    $q->bindParam(':s1',$value1[1],PDO::PARAM_STR);
    $q->execute();
  };
};

$pdo=new PDO("mysql:host=$host;dbname=$dbname;charset=$charset",$username,$password);
foreach ($m as $key=>$value){
  foreach ($value as $value1){
    $insert='INSERT INTO table1 (i1,i2,s1) VALUES ('.$key.','.$value1[0].",'".$value1[1]."')";
    $q=$pdo->prepare($insert);
    $q->execute();
  };
};

$pdo=new PDO("mysql:host=$host;dbname=$dbname;charset=$charset",$username,$password);
foreach ($m as $key=>$value){
  foreach ($value as $value1){
    $insert="INSERT INTO table1 (i1,i2,s1) VALUES ({$key},{$value1[0]},'{$value1[1]}')";
    $q=$pdo->prepare($insert);
    $q->execute();
  };
};

UPD:
Наткнулся на вариант: записать массив в текстовый файл и командой LOAD DATA INFILE загрузить оный в базу. Может будет быстрее.

Comments

[Руслан Федосеев]

что такое медленные? сколько записей? насколько часто операция повторяется?

[Дмитрий]

На самом деле биндить не обязательно, можно пилить прям так. Главное чтоб неименованных плэйсхолдеров хватало

[gvg1]

Руслан Федосеев,
Увеличивал max_execution_time.
Дошёл до 3600 сек.
За 1 час записывается около 50 тыс. записей, а их в массиве пока что около 1,2 млн и есть тенденция к увеличению.
Причём массив формируется за 20 сек.
Вся оперативная память сейчас используется впритык.
Поэтому хочу исключить формирование массива из техпроцесса, а как, если простое переписывание может занять сутки.
Операция выполняется по запросу, типа отчёт.

[gvg1]

Дмитрий,
Хоть биндить, хоть прям так, разница несущественная.
Нужно что-то кардинальное.
Вот, поступило предложение от Slava Rozhnev

[Ипатьев]

Если вы серьезно думаете, что 2 и 3 - это "разные варианты", то должен вас разочаровать - это один и тот же вариант. Разницы между ними нет вообще никакой.

[Adamos]

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

[Дмитрий]

gvg1, вам проще файл запилить, и дамп заливать

[gvg1]

Провёл небольшое исследование заливки массива в базу.
Массив формируется 20 сек и содержит 1,2 млн строк.
В самом начале, когда всё плохо, за 1 час из массива в базу записалось 50 тыс. записей.
Далее сделал некоторые улучшения. Слева - время заливки всего массива, правее - что сделано.
4 мин 07 сек - завернул процесс в транзакцию beginTransaction() - commit()
3 мин 58 сек - +временно отключил индексы ALTER TABLE table1 DISABLE KEYS
2 мин 49 сек - +вместо bindParam использовал сцепление для формирования VALUES, каждая строка отдельно.
Далее начал формировать тем же способом VALUES для различного количества строк.
10 мин 44 сек - 10 тыс. строк
3 мин 45 сек - 5 тыс. строк
2 мин 38 сек - 3 тыс. строк
2 мин 04 сек - 2 тыс. строк
1 мин 22 сек - 1 тыс. строк
1 мин 12 сек - 500 строк
1 мин 07 сек - 200 строк
1 мин 06 сек - 150 строк
1 мин 00 сек - 100 строк
1 мин 08 сек - 80 строк
1 мин 10 сек - 50 строк
График типа параболы ветвями вверх, имеется точка минимума.
Выводы:
1) наибольшее быстродействие в моём частном случае достигается при загрузке массива порциями примерно по 100 строк (можно ещё уточнить).
2) время формирования VALUES в зависимости от количества записей растёт нелинейно. Начиная с нескольких десятков тысяч строк время формирования превышает (и значительно) время заливки соответствующей порции. 50 тыс. я еле дождался, а на 100 тыс. превысило разумные пределы.

Answer 1

[Slava Rozhnev]

Если массив не очень большой - сделайте всё одним запросом:

<?php
$data = [
    ['a' => 1, 'b' => 2, 'c' => 3],
    ['a' => 1, 'b' => 2, 'c' => 3],
    ['a' => 1, 'b' => 2, 'c' => 3],
    ['a' => 1, 'b' => 2, 'c' => 3],
    ['a' => 1, 'b' => 2, 'c' => 3]
];

$query="INSERT INTO t (a, b, c) VALUES " . implode(
    ', ', 
    array_fill(
        0,
        count($data), 
        '(' . implode (', ', array_fill(0, count($data[0]), '?')) . ')'
    )
);

echo $query;

$values = array_reduce(
    $data,
    function($ac, $el) {
        return array_merge($ac, array_values($el));
    },
    []
);

var_export($values);

$q=$pdo->prepare($query);
$q->execute($values);

Здесь вы можете протестировать код

Comments

[gvg1]

У меня в настройках сейчас длина запроса может быть до max_allowed_packet= 32M.
В эти 32М можно запихнуть 300 тыс. VALUES по 100 символов и закачать всю базу за 4 запроса.
Очень перспективно.
Пишут, что с помощью LOAD DATA INFILE из текстового файла это можно сделать ещё быстрее примерно на 30-40%.
Будем проверять.

Answer 2

[Ипатьев]

Ответ на вопрос, который вы почему-то решили вынести в заголовок (при том что, судя по тексту вопроса, вы прекрасно знаете ответ на него) - и который имеет очень мало общего с вашей реальной проблемой - уже дал Slava Rozhnev.

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

Вариант 1, при котором никакой код менять не надо. Отключаем эти проверки устанавливая переменную mysql innodb_flush_log_at_trx_commit=0. Для этого надо обладать правами рута.
Вариант 2: заключить все вставки в транзакцию
Вариант 3: добавлять все записи одним запросом. Это можно сделать либо кодом Slava Rozhnev, либо запросом LOAD DATA INFILE

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

Comments

[gvg1]

Я думал, что в 3-м варианте {} сильно замедляют по сравнению с конкатенцией во 2-м.
Ещё я думал, что VALUES в запросе можно написать пару-тройку штук, а оказывается их общая длина ограничена только переменной max_allowed_packet.
На LOAD DATA INFILE я вышел с этой страницы phpclub.ru/mysql/doc/insert-speed.html

"Вариант 2: заключить все вставки в транзакцию"
Между beginTransaction() и commit()?

[Ипатьев]

Я думал, что в 3-м варианте {} сильно замедляют по сравнению с конкатенцией во 2-м.

Во-первых, разумеется, они ничего не замедляют сами по себе.
Во-вторых, разумеется, все замедление в любом случае происходит в базе данных, а не в построении строки в РНР. Это совершенно несравнимые вещи. РНР вам за этот час насоздаёт миллиард миллиардов строк, в любом варианте. Это в принципе никогда не бывает узким местом. Любые идеи про замедление в данном случае - это нелепые фантазии, про которые надо забыть.

Между beginTransaction() и commit()?

Да