Главная

Конкатенация SQL INSERT запросов

2013-05-01 в 23:06, admin, рубрики: mysql, php, sql, Песочница, метки: mysql, PHP, sql

Здравствуйте жители Хабрахабра.

Возможно эта идея не нова (скорее всего так и есть) и я очередной велосипедист, но все же я хотел бы показать вам то что пришло мне в голову. Надеюсь этот метод кому-нибудь поможет, так же ожидаю советов от разработчиков, кто сталкивался с этим или каких-либо других вариантов. Поехали?

Теория

Я заметил что запросы INSERT могут быть такого вида:

INSERT INTO table VALUES (NULL,1,2),(NULL,2,3),(NULL,4,5);

Сразу в голову пришла идея. Если у нас большое количество данных, а выполнять запросы вставки в базу данных в цикле может занять долго времени (т.к. количество запросов может быть достаточно большим), то почему бы не соединить строки и вместо назначенных 2000 запросов делать допустим 200 а то и 50? Для сравнения нам нужно реализовать 2 метода, один из них будет выполнять запросы каждую итерацию, второй соединив строки допустим каждые 50 раз. Замерять будем время выполнения скрипта, каждой итерации (среднее арифметическое) и общее время добавления в базу данных.

Практика

От теории перейдем к практике (заранее извиняюсь, у меня все по английски, почему-то не люблю делать комментарии на русском, об именах переменных я даже не заикаюсь). Два метода генерируют случайные данные допустим какие-то точки X и Y, и потом заносят их в базу. Оба скрипта отличаются лишь методом добавления в базу.

Заголовок файла (в обоих случаях он одинаков)

<?php
set_time_limit(0);

// Полное время скрипта
$whole_script_start = microtime(TRUE);

$dbh = NULL;

// Соединяемся с базой данных
try
{
    $dbh = new PDO('mysql:host=localhost;dbname=test','test','testpass');
}
catch(PDOException $e)
{
    echo 'Error! ' . $e->getMessage() . '<br />';
    exit();
}

// Массивы с данными
// Оба массивы одинаковы
$x_data = array();
$y_data = array();

// Заполняем массивы случайными данными
for($i = 0; $i < 10000; $i++)
{
    $x_data[$i] = rand(0,500);
    $y_data[$i] = rand(0,500);
}

// Массив хранящий время
// Выполнения итерации(й)
$time = array();

Первый метод (выполнение запроса каждую итерацию)

// -----------------------------------

$time_loop = microtime(TRUE);
$insert_count = 0;

for($i = 0; $i < count($x_data); $i++)
{
    $start = microtime(TRUE);

    // Вставка в БД и подсчёт вставленных
    // записей ( в данном случае 1 запись на 1 итерацию )
    $insert_count += $dbh->exec('INSERT INTO points VALUES(NULL,'.$x_data[$i].','.$y_data[$i].');');
    
    $time[$i] = (microtime(TRUE) - $start);
}

$time_loop = (microtime(TRUE) - $time_loop);
$average = 0;

for($i = 0; $i < count($time); $i++)
{
  $average += $time[$i];
}

// Среднее арифметическое
$average /= count($time);
?>
<table border="1">
  <tr>
    <td><b>SQL запросы</b></td>
    <td><b>Количество вставок</b></td>
    <td><b>Ср. время на каждую итерацию</b></td>
    <td><b>Время вставки в БД</b></td>
    <td><b>Время выполнения скрипта</b></td>
  </tr>
  <tr>
    <td><?php echo count($time); ?></td>
    <td><?php echo $insert_count; ?></td>
    <td><?php echo $average; ?></td>
    <td><?php echo $time_loop; ?></td>
    <td><?php echo (microtime(TRUE) - $whole_script_start); ?></td>
  </tr>
</table>

Второй метод (конкатенация запросов)

// Начало SQL запроса
$start_sql = 'INSERT INTO points VALUES';

// Конкатенируемая часть SQL
// запроса которая содержит
// данные из массивов
$end_sql = '';

// Некая константа, определяющая
// какое количество конкатенаций
// будет произведено перед выполнением
// SQL запроса
$concat_const = 150;

$time_loop = microtime(TRUE);
$insert_count = 0;

for($i = 0; $i < count($x_data); $i++)
{
    $start = microtime(TRUE);
    
    // Если кол-во итераций кратно
    // нашей константе, то выполняется запрос
    if((($i + 1) % $concat_const) == 0)
    {
        // В начале строки $end_sql
        // есть знак ',' , который необходимо убрать
        $insert_count += $dbh->exec($start_sql.substr($end_sql,1).';');
        $end_sql = ''; // Убираем из строки уже вставленные значения
    }

    // Конкатенация строки
    $end_sql .= ',(NULL,'.$x_data[$i].','.$y_data[$i].')';

    $end = microtime(TRUE);
    
    $time[$i] = ($end - $start);
}

// Вставляем те записи которые могли
// "недойти" до вставки в базу данных
if(!empty($end_sql)) 
{
    $insert_count += $dbh->exec($start_sql.substr($end_sql,1).';');
}

$time_loop = (microtime(TRUE) - $time_loop);

$average = 0;

for($i = 0; $i < count($time); $i++)
{
  $average += $time[$i];
}

// Среднее арифметическое
$average /= count($time);
?>
<table border="1">
  <tr>
    <td><b>SQL запросы</b></td>
    <td><b>Количество вставок</b></td>
    <td><b>Ср. время на каждую итерацию</b></td>
    <td><b>Время вставки в БД</b></td>
    <td><b>Время выполнения скрипта</b></td>
  </tr>
  <tr>
    <td><?php echo ceil(count($time) / $concat_const); ?></td>
    <td><?php echo $insert_count; ?></td>
    <td><?php echo $average; ?></td>
    <td><?php echo $time_loop; ?></td>
    <td><?php echo (microtime(TRUE) - $whole_script_start); ?></td>
  </tr>
</table>

Обратите внимание что во втором методе я поставил некую «константу», она равна 150. Я ни делал никаких вычислений, в принципе разработчик сам решает какое значение нужно подставить, предположив какое количество данных будет обрабатываться. Однако я не советую делать это значение огромным (память к сожалению или к счастью не ограничена).

Результаты

Первый метод

SQL запросы	Количество вставок	Ср. время на каждую итерацию	Время вставки в БД	Время выполнения скрипта
10000	10000	0.023993042778969	240.07842707634	240.1159491539

Второй метод (конкатенация)

SQL запросы	Количество вставок	Ср. время на каждую итерацию	Время вставки в БД	Время выполнения скрипта
67	10000	0.00016707143783569	1.7419440746307	1.7831699848175