MySQL & dotnet: эффективно обновлять каждую строку
Я приступил к чему-то, что, на мой взгляд, было бы простым: последовательно (строка за строкой) читать, вычислять некоторые значения и обновлять одну и ту же строку, прежде чем перейти к следующей для всей таблицы.
Контекст: одна плоская таблица, 26 миллионов записей, составной ПК (4 числовых значения). Размер физического стола 1,3 ГБ. Порядок обработки записей не имеет значения. Это будет сделано только один раз в обозримом будущем. Вычисление это слишком сложно, чтобы сделать в SQL (для меня, по крайней мере:-)
Каков рекомендуемый, эффективный способ сделать это?
Что я пробовал: используя datareader в ADO.NET (который больше не имеет старого доброго набора результатов VB6, который был бы намного проще). Объединяя это с оператором обновления (Statement.ExecuteNonQuery) в каждом reader.Read() Цикл был хитрым, поскольку ADO.NET не нравится это в том же соединении. Поэтому мне пришлось открыть 2 соединения. (Запрос на обновление использует составной PK в предложении WHERE, который потенциально должен быть быстрым, но все же кажется мне неэффективным, поскольку курсор уже находится на записи, которую я собираюсь обновить.)
Такой подход работает, но не с читателем, основанным на SELECT * FROM MyTable запрос. Я должен был использовать LIMIT читать куски по несколько тысяч строк за раз, чтобы избежать ошибок тайм-аута. Из ранних экспериментов я оценил, что процесс займет 26 часов для 26 миллионов записей. Я настроил его так, чтобы он работал всю ночь, когда я вернулся, время ожидания снова истекло где-то на треть. После перезапуска я обнаружил, что предложение LIMIT замедляет SELECT запрос, как только смещение становится больше. Мои новые оценки для оставшихся 65% превышают еще 20 часов, возможно, дольше по мере увеличения смещения LIMIT.
Должен быть лучший способ!?
(Я также попробовал EF, который был изящен, но рассчитан на время:-)
2 ответа
После вышеупомянутого обсуждения с Эриком и дальнейших экспериментов, это мой вывод из саги:
- Реляционные базы данных действительно не подходят для последовательной обработки, и любые такие процессы будут снижать производительность при выполнении на реляционных СУБД.
- На некотором этапе в истории баз данных платформы, такие как VB6, предлагали инструменты, такие как "Набор записей", который включал "основанный на курсоре" обход таблиц, чтение и обновление записей по мере продвижения. Они работали на поддерживаемых провайдерах, таких как ODBC и OLE (и СУБД, прикрепленные к ним). Набор записей выглядел очень заманчиво для этой работы, но больше не доступен в ADO.NET (по состоянию на 2013 г.)
- Таблицы малого и среднего размера простят ошибки проектирования.
- ОС кэширует целые таблицы данных и, таким образом, маскирует неэффективность БД при работе с таблицами малого и среднего размера.
- Как только размеры таблицы (и / или количество строк) увеличиваются, система начинает работать и кажется, что она работает ненормально. Вероятно, раньше он работал плохо, но вы не заметите этого из-за вышеупомянутых моментов.
- Мой метод использования SELECT...LIMIT (для извлечения блоков из 1000 строк) пришел к полной остановке примерно на 75%, хотя таблица из 26 миллионов строк; т.е. каждый SELECT из 1000 строк теперь занимает минуты для завершения.
- Я попробовал эмулировать основанный на курсоре набор записей, основанный на http://www.codeproject.com/Articles/8435/Simulating-Recordsets-with-ADO-NET только чтобы выяснить, что MySQL не поддерживает UPDATE курсоров и поддерживает только курсоры внутри хранимых процедур, которые побеждали цель, так как мои вычисления должны были выполняться вне СУБД. (Это может хорошо работать для SQL Server)
- (Поскольку моя таблица состояла из составного ключа из 4 частей), я закончил тем, что создал один искусственный ключ / индекс автоинкремента, как предложил Эрик, чтобы я мог просматривать записи, используя вычисленные диапазоны (например, 0-999, 1000-1999 и т. Д.) который, в отличие от использования LIMIT, был одинаково быстрым в начале и в конце обхода таблицы. Создание поля AutoIncrement плюс индекс / ключ (в пределах одной команды) заняло у MySQL чуть меньше 1 часа (для 26+ миллионов записей при 150 байтах на запись) на медленном 2-ядерном нетбуке Atom.
- В описанной выше конфигурации полный обход занял около 9 часов для 26+ миллионов записей, что соответствует моей первоначальной оценке, когда LIMIT был рассчитан в начале процесса.
Надеюсь, что это может помочь любому в подобной ситуации. Комментарии очень ценятся.
Обновление базы данных небольшими партиями (около 1000 записей), как правило, является хорошим подходом, поскольку позволяет избежать блокировки строк (или страниц) слишком долго и избежать тайм-аутов. Эта часть подхода великолепна.
Вы можете улучшить производительность LIMIT для больших начальных значений. Есть разные подходы. Лучшее, что я нашел на сегодняшний день, это не использование LIMIT, а выбор диапазонов первичных ключей.