Архивирование данных MySQL, бросающих проблему ограничения памяти
У меня есть несколько таблиц. как таблица1, таблица2, таблица3 и т. д.
Что требуется:
1. получить конкретную строку из таблицы1. (например: id = 203)
2. получить все значения, связанные с идентификатором 203, из таблицы 2 (например: 1,2,3,4,5,6,7 …. 500)
3. снова извлеките все значения идентификаторов из шага 2 из таблицы 3, таблицы 4 и т. Д., Которые имеют отношение внешнего ключа к таблице 2. (миллионы строк)
4. Создайте операторы вставки для всех трех шагов из результата.
5. Вставьте запросы шага 4 в соответствующие таблицы в архивированной базе данных с теми же именами таблиц. то есть, короче говоря, архивирование некоторой части данных в архивную БД.
Как у меня дела:
Для каждой таблицы каждый раз, когда получаются строки, создается оператор вставки и сохранение в определенных массивах для каждой таблицы. После получения всех значений до шага 3, создания оператора вставки и сохранения в массиве. Затем запускаются циклы для каждого отдельного массива и выполняются эти запросы в заархивированной БД. После успешного выполнения запросов удаляются все извлеченные строки из основной базы данных, а затем фиксируется транзакция.
Результат:
Пока что вышеупомянутый подход очень хорошо работал с небольшими БД размером около 10-20 МБ данных.
вопрос:
Для большего количества строк (скажем, более 5 ГБ) php выдает ошибку исчерпания памяти при получении строк и, следовательно, не работает в Production. Даже я увеличил лимит памяти до 3gb. Я не хочу увеличивать это больше.
Альтернативное решение, о котором я думаю, состоит в том, чтобы вместо использования массивов для хранения запросов хранить эти запросы в файлах, а затем внутренне использовать команду infile для выполнения запросов к архивированию БД.
Подскажите пожалуйста как добиться вышеуказанного вопроса? после перемещения в архивную БД возникают требования для возврата в основную БД с аналогичным функционалом.
Решение
Есть два ключа для обработки больших наборов результатов.
Первый заключается в потоковой передаче результирующего набора строка за строкой. Если вы не укажете это явно, API-интерфейсы php для MySQL немедленно попытаются прочитать весь набор результатов с сервера MySQL в память клиента, а затем перемещаться по этой строке за строкой. Если ваш набор результатов содержит десятки или сотни тысяч строк, это может привести к нехватке php памяти.
Если вы используете mysql_ интерфейс, использование mysql_unbuffered_query(). Вы не должны использовать этот интерфейс, хотя. Это устарело, потому что, ну, это отстой.
Если вы используете mysqli_ интерфейс, вызов mysqli_real_query() вместо mysqli_query(), Тогда позвони mysqli_use_result() начать поиск набора результатов. Затем вы можете получить каждый ряд с помощью один из fetch() варианты. Не забудьте использовать mysqli_free_result() закрыть набор результатов, когда вы извлекли все его строки. mysqli_ имеет объектно-ориентированные методы; Вы также можете использовать их.
PDO имеет аналогичный способ потоковой передачи наборов результатов с сервера на клиент.
Второй ключ к обработке больших наборов результатов — использовать второе соединение с вашим сервером MySQL для выполнения INSERT а также UPDATE операции, поэтому вам не нужно накапливать их в памяти. То же самое происходит, если вы решили записать информацию в файл в файловой системе: записать ее по очереди, чтобы вам не приходилось хранить ее в оперативной памяти.
Хитрость заключается в том, чтобы обрабатывать один или несколько рядов одновременно, а не десятки тысяч.
Надо сказать: многие люди предпочитают использовать программы командной строки, написанные на языке с ограниченным числом, таком как Java, C # или PERL, для такого рода обслуживания базы данных.
Другие решения
Других решений пока нет …