Преобразование таблиц SQL Server в MySQL с использованием ZF2 и Doctrine2

Я разработал приложение для одного из моих клиентов. У него уже есть один. Поэтому мне нужно преобразовать его фактическую базу данных (SQL Server) в новую (MySQL).

Некоторые таблицы SQL Server имеют более 10.000.000 записей. Когда я начинаю начинать разработку этого конвертера, я начал с некоторых таблиц с несколькими записями, поэтому я нахожу все записи и сохраняю их в своей новой базе данных MySQL. Я покажу вам код для лучшего понимания (это только пример)

<?php

namespace Converter\Model;

class PostConverter extends AbstractConverter 
{

    public function convert() 
    {
        // this is the default connection, it is a mysql database (new application)
        $em = $this->getEntityManager();
        // this return an alternative connection to the sqlserver database (actual application)
        $emAlternative = $this->getEntityManagerAlternative();

        // instance of Converter\Repository\Post
        $repository = $emAlternative->getRepository('Converter\Entity\Post');

        $posts = $repository->findAll();

        foreach ($posts as $post)
            $post = new Post();
            $post->setTitle($object->getTitle());
            $em->persist($post);
        }  

        $em->flush();
    }
}

Теперь предположим, что таблица Post имеет более 10.000.000 записей. Я не могу просто найти все и перебрать его. Я выйду из ОЗУ. Поэтому я сделал что-то вроде этого.

Класс репозитория:

<?php

namespace Converter\Repository;

class Posts extends \Doctrine\ORM\EntityRepository
{

    public function findPosts($limit, $offset)
    {
        $qb = $this->createQueryBuilder('Post');

        $qb->setMaxResults($limit);
        $qb->setFirstResult($offset);

        return $qb->getQuery->getResult();
    }
}

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

<?php

namespace Converter\Model;

class PostConverter extends AbstractConverter 
{

    public function convert() 
    {
        $em = $this->getEntityManager();
        $emAlternative = $this->getEntityManagerAlternative();

        $repository = $emAlternative->getRepository('Converter\Entity\Post');

        $limit = 1000;

        while ($object = $repository->findPosts($limit, $offset) {
            $post = new Post();
            $post->setTitle($object->getTitle());
            $em->persist($post);

            $offset += $limit;
        }  

        $em->flush();
    }
}

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

Спасибо всем

ИЗМЕНИТЬ

Я не могу просто сбрасывать друг друга. То, что я разместил здесь, является всего лишь примером, в преобразовании мне приходится обрабатывать почти все данные перед вставкой в новую базу данных. Его фактическое применение было разработано в 2005 году. База данных даже не нормализована

Ответы

Ответ 1

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

Инструмент ORM в основном не подходит для массовых вставок, обновлений или удаления. Каждая РСУБД имеет свой собственный, наиболее эффективный способ борьбы с такими операциями, и если приведенные ниже варианты недостаточны для ваших целей, мы рекомендуем вам использовать инструменты для вашей конкретной РСУБД для этих массовых операций.

Вот как я бы справился с этим:

Создайте свою пустую базу данных MySQL с помощью инструментов Doctrine.
Составьте список всех индексов и первичных ключей в базе данных MySQL и оставьте их. Я бы это сделал. Это приведет к удалению накладных расходов постоянных обновлений индекса до завершения переноса данных.
Напишите script, чтобы скопировать данные. Пронумеруйте данные SQL Server пакетами в несколько тысяч и вставьте в MySQL.
- Использовать PDO или собственные библиотеки. Нет доктрины или построителей запросов. Напишите запросы вручную.
- Откройте одно соединение с вашим SQL Server и одно соединение с MySQL. Держите их открытыми на время script.
- Запрос в партиях с использованием LIMIT и первичного ключa > последнего идентификатора. Запрос с использованием OFFSET часто медленнее.
- Подготовьте инструкции за пределами циклов для оптимизации обработки запросов.
- Оберните каждую партию вставок в одну транзакцию, чтобы уменьшить транзакционные издержки.
- "Вручную" проверьте ссылочную целостность, если необходимо. В ваших таблицах еще нет первичных ключей.
- Если у вас много таблиц, отделите свой код на объекты или функции, чтобы локальные переменные могли быть удалены из памяти, и отладку будет легче отлаживать.
- Вы можете периодически звонить gc_collect_cycles(). Если ваш код разбит на объекты, это простой способ держать память под контролем.
Восстановить индексы базы данных и первичные ключи. Бонусные баллы, если они были написаны с самого начала. Следите за любыми первичными ключами, которые не могут быть созданы из-за ошибок с дублирующимися данными.
Тестирование и тестирование перед открытием новой базы данных MySQL для использования в производстве. Вы не хотите писать еще один script для исправления переноса данных позже.

Ответ 2

Если схема в исходной базе данных (MSSQL) и целевой базе данных (MySQL) является точной или похожей, я бы экспортировал записи из одной базы данных, а затем импортировал их в другую, используя только инструменты базы данных. Пример:

В MSSQL для каждой таблицы экспортировать записи в CSV
В MySQL для каждой таблицы импортировать записи из CSV

Вы можете использовать оболочку script, чтобы склеить все это вместе и автоматизировать процесс.

Этот экспорт/импорт будет достаточно быстрым, как это происходит на уровне базы данных. Это также самый быстрый, который вы, возможно, получите.

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

В вашем конкретном примере вы можете увидеть некоторое улучшение производительности, если вы unset($object) в нижней части вашего цикла while, хотя я сомневаюсь, что память является узким местом. (I/O есть.)

Ответ 3

Я пробовал этот подход до и из своего опыта, всегда быстрее использовать собственные средства для сброса и восстановления данных СУБД, а не записи процессов через такую структуру.

Я бы предложил использовать утилиту, такую как bcp (https://msdn.microsoft.com/en-us/library/aa337544.aspx), чтобы выгрузить данные из SQL Server, а затем использовать MySQL LOAD DATA (http://dev.mysql.com/doc/refman/5.7/en/load-data.html) или mysqlimport для переноса данных в MySQL.

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

Ответ 4

Предпочитаете использовать демпинг данных на основе СУБД и восстанавливать инструменты, а не обрабатывать записи через как это. Экспорт базы данных в формате CSV и импорт в mysql.