@burguy83

Как ускорить инсерты в базу sql?

Имеем xml c 1,5 млн записями. Весит этот файл около 3гб. Я его разбираю DOMdocument и сразу заливаю в базу в цикле.
Схема кода такая.
foreach ($leiLEIobj as $leiLEIs)
{
            foreach ($leiLEIobj as $leiLEIs)
            {
               $LEI = $leiLEIs->nodeValue;
               $arResult[$LEI][$leiLEIs->nodeName] = $LEI; 
                //$LEIs[$row->nodeName] = $row->nodeValue;
..... много много всякий форейчев
           
            } 
$qwery = "INSERT INTO `leis`($fields) VALUES ($values)
}


Получается я записываю в базу каждую итерацию (ПРОЧИТАЛ-ЗАПИСАЛ). Всего получается 1,5 млн массивов с 30 полями в каждом. База пустая и по началу он льёт достаточно быстро по 1300 ежесекундно но потом приближаясь к 100.000 записи начинает резкое падение до 30 вставок. А потом вообще падает до 1-й. Сервак приличный. И весь этот процесс, чтоб залить в базу занимает несколько дней. Таблица у меня майасин, стоит одно индексное поле с праймери id.

Мне нужны варианты ускорения инсертов. Есть те, которые я пытался применить но не помогли.
Например, не подходит лоад ин файл т.к. в csv записываются криво данные, в плане съезжают значения в другие поля так как в хмл документе в каждом массиве различное кол-во данных.

Слышал про пакетную вставку, но не знаю как реализовать. Какие есть мысли. Спасибо.
  • Вопрос задан
  • 187 просмотров
Пригласить эксперта
Ответы на вопрос 4
DarkRaven
@DarkRaven
разработка программного обеспечения
Лучше всего делать пакетную вставку или же конвертировать xml в соответствующий sql (с теми же пакетными вставками) и выполнить его через
mysql -u username -p password database_name < /path/to/your/file.sql

Обратите внимание, на -p password, между ними, если мне не изменяет память, нет пробела.

Для варианта с SQL формируйте вот такое:
INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);


Только не три строки, а по 100-500 - думаю подберете соотношение.

Примерный код формирования, основанный на вашем примере:
<?php
$batchSize = 1000;
$counter = 0;
$valuesBatch = array();

foreach ($leiLEIobj as $leiLEIs)
{
    foreach ($leiLEIobj as $leiLEIs)
    {
        $LEI = $leiLEIs->nodeValue;
        $arResult[$LEI][$leiLEIs->nodeName] = $LEI; 
        //$LEIs[$row->nodeName] = $row->nodeValue;
    }

    $valuesBatch[] = "($values)";
    $counter ++;
    
    if ($counter==$batchSize)
    {
        $qwery = strtr(
            "INSERT INTO `leis`($fields) VALUES ($values) :text",
            array(
                ':text' => implode(",\r\n", $valuesBatch)
            )
        );

        // Выполнить запрос или записать его в общую переменную-накопитель
        $counter = 0;
        $valuesBatch = array();
    }
}


P.S. csv так же можно сформировать нужным образом из XML, дополнив значениями по умолчанию те, которых в xml-строке нет, а дальше что-то подобное вот этому:
LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);
Ответ написан
@res2001
Developer, ex-admin
Укажите какую СУБД используете.

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

Кроме того обычно СУБД поддерживают операции bulk insert (пакетная вставка), возможно для этого придется конвертировать файл в формат, который понимает СУБД. У MySQL вроде такого нет, в этом случае остается только вариант, предложенный Дмитрий
Ответ написан
@zhaar
В MSSQL Bulk Insert работает быстрее обычного insert into ... values (), поэтому может есть смысл преобразовывать xml в обычный текстовый файл и его уже импортить в базу?
Ответ написан
Комментировать
@ponaehal
Меня смущает постепенное падение производительности.
По моим, ничем не подтвержденным ощущениям, причина кроется в механизме поддержки транзакционности используемой вами базы данных.
Вы пытаетесь вставить кучу записей в одной транзакции, механизмы поддержки отката транзакций (на случай выполнения команды rollback или падения сессии) требуют значительных ресурсов БД. Примерно на 100.000, этих ресурсов хватает, а затем начинается какой то трэш (нужно читать в зависимости от БД).
Что бы я посоветовал для начала:
1. Средствами администрирования БД, посмотреть на расходование ресурсов (понять на что расходуется).
2. Если мои догадки подтвердятся то вам вряд ли поможет пакетная вставка (хотя в разных БД она может быть реализована по разному).
3. Попробуйте вставить commit через каждые 50.000 записей. Тем самым Вы снимите нагрузку с механизмов поддержки отката транзакций.
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы