Какой самый эффективный способ вставить тысячи записей в таблицу (MySQL, Python, Django)

У меня есть таблица базы данных с уникальным строковым полем и парой целочисленных полей. Строковое поле обычно имеет длину 10-100 символов.

Примерно раз в минуту у меня возникает следующий сценарий: я получаю список из 2-10 тысяч кортежей, соответствующих структуре записи таблицы, например.

[("hello", 3, 4), ("cat", 5, 3), ...]

Мне нужно вставить все эти кортежи в таблицу (предположим, я проверил, что ни одна из этих строк не появляется в базе данных). Для пояснения, я использую InnoDB, и у меня есть автоинкрементный первичный ключ для этой таблицы, строка не является PK.

Мой код в настоящее время выполняет итерацию по этому списку, для каждого кортежа создает объект модуля Python с соответствующими значениями и вызывает «.save()», что-то вроде этого:

@transaction.commit_on_success
def save_data_elements(input_list):
    for (s, i1, i2) in input_list:
        entry = DataElement(string=s, number1=i1, number2=i2)
        entry.save()

Этот код в настоящее время является одним из узких мест в моей системе, поэтому я ищу способы его оптимизации.

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

Есть ли у вас какие-либо предложения по оптимизации такого процесса?

Спасибо


python mysql django sql insert
person Roee Adler    schedule 11.05.2009    source источник
comment
Хороший вопрос! Итак, лучшими ответами являются создание текстового файла или создание SQL-запроса посредством конкатенации строк? Это немного не устраивает!   -  person JAL    schedule 29.09.2010

Ответы (8)


arrow_upward
12
arrow_downward

Вы можете записать строки в файл в формате "field1", "field2", .. и затем использовать LOAD DATA для их загрузки

data = '\n'.join(','.join('"%s"' % field for field in row) for row in data)
f= open('data.txt', 'w')
f.write(data)
f.close()

Затем выполните это:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Справочник

person Nadia Alramli    schedule 11.05.2009
comment
Это должно быть LOAD DATA LOCAL INFILE, если код не выполняется на сервере базы данных. - person staticsan; 12.05.2009
comment
Кроме того, отключите индексы перед загрузкой, а затем включите их после (потребуется некоторое время для создания индекса). Не смотрел, помогает ли это и со вставками Django. - person pufferfish; 15.07.2009

arrow_upward
12
arrow_downward

В частности, для MySQL самым быстрым способом загрузки данных является использование LOAD DATA INFILE., поэтому, если бы вы могли преобразовать данные в ожидаемый формат, это, вероятно, был бы самый быстрый способ поместить их в таблицу.

person Chad Birch    schedule 11.05.2009
comment
Единственная потенциальная проблема — переопределение метода save(). Если вы сделаете это, вам придется дважды подумать о своем дизайне. - person S.Lott; 12.05.2009
comment
@S.Lott: что вы подразумеваете под переопределением save ()? Вы имеете в виду, переопределяю ли я метод .save() в классе модуля, чтобы при сохранении кода выполнялись задачи предварительной/постобработки, которые будут потеряны в файле данных загрузки? Если так - это не так, я не переопределяю .save(). В противном случае, пожалуйста, уточните... Спасибо. - person Roee Adler; 12.05.2009

arrow_upward
4
arrow_downward

Если вы не LOAD DATA INFILE, как упоминается в некоторых других предложениях, две вещи, которые вы можете сделать, чтобы ускорить вставку:

  1. Используйте подготовленные операторы - это сокращает накладные расходы на синтаксический анализ SQL для каждой вставки.
  2. Делайте все свои вставки в одной транзакции - для этого потребуется использовать механизм БД, поддерживающий транзакции (например, InnoDB).
person Sean McSomething    schedule 11.05.2009
comment
@Sean: Спасибо, под подготовленными операторами вы подразумеваете код SQL со многими элементами %s, которые я просто заполняю, предоставляя список строк/чисел? Также, пожалуйста, взгляните на мой код (в теле вопроса) - если я правильно понимаю, я уже использую одну транзакцию с декоратором @transaction.commit_on_success (я использую InnoDB) - person Roee Adler; 12.05.2009
comment
Я не совсем уверен, что происходит за кулисами с Django - я просто исхожу из общего фона использования MySQL, поэтому я не знаю, что это делает в отношении транзакций. Что касается подготовленных операторов, похоже, это деталь реализации ваших объектов DataElement. Подготовленный оператор будет: stmt = Prepare(sqlStatement); stmt.execute(var1, var2..), а не db.execute(sqlStatement, var1, var2...) - это похоже на компиляцию регулярных выражений, а не на их синтаксический анализ каждый раз. - person Sean McSomething; 12.05.2009

arrow_upward
4
arrow_downward

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

person staticsan    schedule 12.05.2009
comment
@staticsan: Как вы думаете, есть ли какие-то практические ограничения для такого заявления? т. е. могу ли я отправить в базу данных один запрос INSERT с 10 тыс. строк текста? - person Roee Adler; 12.05.2009
comment
Единственным реальным ограничением является размер сетевого буфера. По умолчанию это значение было 1 МБ в течение многих лет, но многие люди увеличили его до 16 МБ. Более поздние версии MySQL могут поддерживать даже большие размеры пакетов. - person staticsan; 12.05.2009
comment
Это больше о размере пакета, чем о количестве записей. Когда вы создаете свой буфер вставки, не добавляйте больше, если это приведет к тому, что буфер превысит максимальный размер пакета mysql. Я бы провел сравнительный анализ и посмотрел, где преимущество начинает выравниваться. Вы также можете запросить у сервера MySQL максимальный размер пакета: mysql> select @@max_allowed_packet\G: @@max_allowed_packet: 33554432 - person Will; 30.05.2013

arrow_upward
2
arrow_downward

Независимо от метода вставки вы захотите использовать механизм InnoDB для максимального параллелизма чтения/записи. MyISAM заблокирует всю таблицу на время вставки, тогда как InnoDB (в большинстве случаев) заблокирует только затронутые строки, позволяя выполнять операторы SELECT.

person weevilgenius    schedule 12.05.2009
comment
Спасибо, я добавил уточнение, что использую InnoDB - person Roee Adler; 12.05.2009

arrow_upward
1
arrow_downward

какой формат вы получаете? если это файл, вы можете выполнить массовую загрузку: http://www.classes.cs.uchicago.edu/archive/2005/fall/23500-1/mysql-load.html

person KM.    schedule 11.05.2009

arrow_upward
1
arrow_downward

Это не связано с фактической загрузкой данных в БД, но...

Если вы предоставляете пользователю тип сообщения «Данные загружаются... Загрузка будет выполнена в ближайшее время», вы можете запускать INSERT или LOAD DATA асинхронно в другом потоке.

Просто что-то еще, чтобы рассмотреть.

person NathanD    schedule 12.05.2009
comment
Скорее всего, проблема в том, что сервер настолько занят обработкой этого ввода, что не может обрабатывать другие запросы. - person Tom Leys; 12.05.2009
comment
Я уже выполняю обработку в отдельном потоке (пользователь не ждет, пока это закончится), моя проблема в том, что иногда система слишком занята, поэтому есть вероятность, что очередь заполнится быстрее, чем ее очистка за достаточное время ... - person Roee Adler; 12.05.2009

arrow_upward
1
arrow_downward

Я не знаю точных деталей, но вы можете использовать представление данных в стиле json и использовать его как фикстуры или что-то в этом роде. Я видел что-то подобное на Django Video Workshop Дугласа Наполеоне. Смотрите видео на http://www.linux-magazine.com/online/news/django_video_workshop. и http://www.linux-magazine.com/online/features/django_reloaded_workshop_part_1. Надеюсь, это поможет.

Надеюсь, ты справишься. Я только начал изучать django, поэтому могу просто указать вам на ресурсы.

person roopesh    schedule 12.05.2009