Заметки для построения эффективных Django-ORM запросов в нагруженных проектах

Написано, т.к. возник очередной холивар в комментариях на тему SQL vs ORM в High-Load Project (HL)

Преамбула

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

• Никогда нельзя реализовать HL-проект на основе только одной манипуляции с ORM
• Никогда не складывайте сложные вещи на плечи БД. Она нужна Вам чтобы хранить инфу, а не считать факториалы!
• Если вы не можете реализовать интересующую Вас идею простыми средствами ORM — не используйте ORM для прямого решения задачи. И тем более не лезте в более низкий уровень, костыли сломаете. Найдите более элегантное решение.
• Извините за издевательски-юмористический тон статьи. По другому скучно 🙂
• Вся информация взята по мотивам Django версии 1.3.4
• Будте проще!

И-и-и да, в статье будут показаны ошибки понимания ORM, с которыми я столкнулся за три с лишним года работы с Django.

Не понятый ORM

Начну с классической ошибки, которая меня преследовала довольно долго. В части верований в племя уругвайских мартышек. Я очень сильно верил во всемогучесть Django ORM, а именно в

Klass.objects.all() 

например:

all_result = Klass.objects.all() result_one = all_result.filter(condition_field=1) result_two = all_result.filter(condition_field=2)

В моих мечтах размышление шло следующим образом:

• Я выбрал все что мне интересно, одинм запросом на первой строке.
• Во второй строке у меня уже не будет запроса, а будет работа с полученным результатом по первому условию.
• В третей строке у меня так же не будет запроса к БД, а я по результатам первого запроса буду иметь интересующий меня вывод со вторым условием.

Вы, наверное, уже догадываетесь, что волшебных мартышек не существует и в данном случае мы имеем три запроса. Но, я Вас огорчу. В данном случае мы все же имеем два запроса, а если быть еще точнее — то ни одного запроса нет по результатам работы данного скрипта (но в дальнейшем мы конечно так не будем изголяться). Почему, спросите Вы?
Объясняю по порядку. Докажем что в данном коде три запроса:

• Первая строка, при вычислениях, аналог
  

  select * from table; 

• Вторая строка, при вычислениях, аналог
  

  select * from table where condition_field=1; 

• Третяя строка, при вычислениях, аналог
  

  select * from table where condition_field=2; 

Ура! Мы доказали что у нас есть три запроса. Но главное фраза — «при вычислениях». По сути, мы переходим ко второй части — доказательство что у нас всего два запроса.
Для данной задачки нам поможет следующее понимание ORM (в 2х предложениях):

• Пока мы ничего не вычислили — мы только формируем запрос, средствами ORM. Как только начали вычислять — вычисляем по полученному сформированному запросу.

Итак, в первой строке мы обозначили переменную all_result с интересующим нас запросом — выбрать все.
Во второй и третей строке, мы уточняем наш запрос на выборку доп. условиями. Ну и следовательно получили 2 запроса. Что и следовало доказать
Внимательные читатели (зачем вы еще раз взглянули в предыдущие абзацы?) уже должны были догадаться, что никаких запросов то мы и не сделали. А во второй и третьей строке мы так же просто сформировали интересующий нас запрос, но к базе так с ним и не обратились.
Так что занимались мы ерундой. И вычисления начнутся, например, с первой строки нижестоящего кода:

for result in result_one:   print result.id 

Не всегда нужные функции и обоснованные выборки

Попробуем поиграться с шаблонами, и любимой некоторыми функцией __unicode__().
Вы знаете — классная функция! В любом месте, в любое время и при любых обстоятельствах мы можем получить интересующее нас название. Супер! И супер до тех пора, пока у нас в выводе не появится ForeignKey. Как только появится, считай все пропало.
Рассмотрим небольшой пример. Есть у нас новости одной строкой. Есть регионы к которым привязаны эти новости:

class RegionSite(models.Model):     name = models.CharField(verbose_name="название", max_length=200,)      def __unicode__(self):         return "%s" % self.name   class News(models.Model):     region = models.ForeignKey(RegionSite, verbose_name="регион")     date = models.DateField(verbose_name="дата", blank=True, null=True, )     name = models.CharField(verbose_name="название", max_length=255)      def __unicode__(self):         return "%s (%s)" % (self.name, self.region) 

Нам нужно вывести 10 последних новостей, с названием, как у нас определено в News.__unicode__()
Расчехляем рукава, и пишем:

news = News.objects.all().order_by("-date")[:10] 

В шаблоне:

{% for n in news %} {{ n }} {% endfor %} 

И вот тут мы вырыли себе яму. Если это не новости или их не 10 — а 10 тыс, то будьте готовы к тому, что вы получите 10 000 запросов + 1. А все из-за грязнокровки ForeignKey.
Пример лишних 10 тыс запросов (и скажите спасибо что у нас мелкая модель — так бы выбирались все поля и значения модели, будь то 10 или 50 полей):

SELECT `seo_regionsite`.`id`, `seo_regionsite`.`name` FROM `seo_regionsite` WHERE `seo_regionsite`.`id` = 1  SELECT `seo_regionsite`.`id`, `seo_regionsite`.`name` FROM `seo_regionsite` WHERE `seo_regionsite`.`id` = 1  SELECT `seo_regionsite`.`id`, `seo_regionsite`.`name` FROM `seo_regionsite` WHERE `seo_regionsite`.`id` = 2 SELECT `seo_regionsite`.`id`, `seo_regionsite`.`name` FROM `seo_regionsite` WHERE `seo_regionsite`.`id` = 1  -- итп 

Почему так происходит? Все до генитальности просто. Каждый раз, когда вы получаете название новости, у нас происходит запрос к RegionSite, чтобы вернуть его __unicode__() значение, и подставить в скобки для вывода названия региона новости.
Аналогично нехорошая ситуация начинается когда мы, например, в шаблоне средствами ORM пытаемся добраться до нужного нам значения, например:

{{ subgroup.group.megagroup.name }} 

Вы не поверите какой жесткий запрос может там быть 🙂 Я уж и не говорю о том, что таких выборок у Вас в шаблоне может быть десятки!
Нас так просто не возьмешь — всхлипнули мы и воспользовались следующей отличной возможностью ORM — .values().
Наша строчка кода магическо-клавиатурным способом превращается в:

news = News.objects.all().values("name", "region__name").order_by("-date")[:10] 

А шаблон:

{% for n in news %} {{ n.name }} ({{ n.region__name }}) {% endfor %} 

Обратите внимание на двойное подчеркивание. Оно нам в скором времени пригодится. (Для тех кто не в курсе — двойное подчеркивание, как бы связь между моделями, если говорить грубо)
Такими нехитрыми манипуляциями мы избавились от 10 тыс запросов и оставили лишь один. Кстати да, он получится с JOIN’ом и с выбранными нами полями!

SELECT `news_news`.`name`, `seo_regionsite`.`name` FROM `news_news` INNER JOIN `seo_regionsite` ON (`news_news`.`region_id` = `seo_regionsite`.`id`) LIMIT 10  

Мы до безумства рады! Ведь только что мы стали ORM-оптимизаторами:) Фиг то там! Скажу Вам я:) Данная оптимизация — оптимизация до тех пор пока у нас не 10 тыс новостей. Но мы можем еще быстрее!
Для этого забъем на наши предрассудки по количеству запросов и в срочном порядке увеличиваем количество запросов в 2 раза! А именно, займемся подготовкой данных:

regions = RegionSite.objects.all().values("id", "name") region_info = {} for region in regions:   region_info[region["id"]] = region["name"]  news = News.objects.all().values("name", "region_id").order_by("-date")[:10] for n in news:   n["name"] = "%s (%s)" % (n["name"], region_info[n["region_id"]]) 

И дальше вывод в шаблоне нашей свежезаведенной переменной:

{% for n in news %} {{ n.name }} {% endfor %} 

Да, понимаю… Данными строками мы нарушили концепцию MVT. Но это лишь пример, который можно легко переделать в строки, не нарушающие, стандарты MVT.
Что же мы сделали?

1. Мы подготовили данные по регионам и занесли инфо о них в словарь:
   

   SELECT `seo_regionsite`.`id`, `seo_regionsite`.`name` FROM `seo_regionsite` 

2. Выбрали из новостей все что нас интересует + обратите внимание на одинарное подчеркивание.
   

   SELECT `news_news`.`name`, `news_news`.`region_id` FROM `news_news` LIMIT 10  

   Именно одинарным подчеркиванием мы выбрали прямое значение связки в базе.

3. Связали средствами питона две модели.

Поверьте, на одинарных ForeignKey Вы прироста в скорости почти не заметите (особенное если выбираемых полей мало). Однако, если Ваша модель имеет связь через фориджн более чем с одной моделью — вот тут и начинается праздник данного решения.
Продолжим изголяться над двойным и одинарным подчеркиванием.
Рассмотрим до банальности простой пример:

item.group_id vs. item.group.id 

Не только при построении запросов, но и при обработке результатов можно напороться на данную особенность.
Пример:

for n in News.objects.all():     print n.region_id  

Запрос будет всего один — при выборке новостей
Пример 2:

for n in News.objects.all():     print n.region.id 

Запросов будет 10 тыс + 1, т.к. в каждой итерации у нас будет свой запрос на id. Он будет аналогичен:

SELECT `seo_regionsite`.`id`, `seo_regionsite`.`name` FROM `seo_regionsite` WHERE `seo_regionsite`.`id` = 1  

Вот такая вот разница из-за одного знака.
Многие продвинутые джанговоды сейчас тыкают пальцем в куклу Вуду с моим кодом. И при этом задают мне вопрос — ты чего за пургу творишь с подготовкой данных, и где values_list(«id», flat=True) ?
Рассмотрим замечательный пример, показывающий необходимость в аккуратности работы с value_list:

regions_id = RegionSite.objects.filter(id__lte=10).values_list("id", flat=True) for n in News.objects.filter(region__id__in=regions_id):     print n.region_id 

Данными строками кода мы:

1. Подготавливаем список интересующих нас id-шников регионов по какому-то абстрактному условию.
2. Получившийся результат вставляем в наш новостной запрос и получаем:
   

   SELECT `news_news`.`id`, `news_news`.`region_id`, `news_news`.`date`, `news_news`.`name` FROM `news_news` WHERE `news_news`.`region_id` IN (SELECT U0.`id` FROM `seo_regionsite` U0 WHERE U0.`id` <= 10 )  

Запрос в запросе! Уууух, обожаю 🙂 Особенно выбирать 10 тыс новостей при вложенном селекте с IN (10 тыс айдишников)
Вы конечно же понимаете чем это грозит? 🙂 Если нет — то поймите — ничем, совершенно ни чем хорошим!
Решение данного вопроса так же до гениальности проста. Вспомним начало нашей статьи — никакой запрос не появляется без вычисления переменной. И сделаем ремарку, например, на второй строке кода:

for n in News.objects.filter(region__id__in=list(regions_id)): 

И таким решением мы получим 2 простых запроса. Без вложений.
У вас еще не захватило дух от падл, припасенных для нас ORM? Тогда капнем еще глубже. Рассмотрим код:

regions_id = list(News.objects.all().values_list("region_id", flat=True)) print RegionSite.objects.filter(id__in=regions_id) 

Данными двумя строками мы выбираем список регионов, по котором у нас есть новости. Все в этом коде замечательно, за исключением одного момента, а именно получившегося запроса:

SELECT `seo_regionsite`.`id`, `seo_regionsite`.`name` FROM `seo_regionsite` WHERE `seo_regionsite`.`id` IN (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9) LIMIT 21 

Ахаха, ORM, прекрати! Что ты делаешь!
Мало того что он из всех новостей (у меня в примере их 256, вроде) он выбрал id регионов и просто их подставил, так он еще взял откуда-то limit 21. Про лимит все просто — так устроен print большого количества значений массива (я другого оправдания не нашел), а вот со значениями тут явно засада.
Решение, как и в предыдущем примере, просто:

print RegionSite.objects.filter(id__in=set(regions_id)).values("id", "name") 

Убрав лишние элементы через set() мы получили вполне адекватный запрос, как и ожидали:

SELECT `seo_regionsite`.`id`, `seo_regionsite`.`name` FROM `seo_regionsite` WHERE `seo_regionsite`.`id` IN (1, 2, 3, 4, 9) LIMIT 21 

Все рады, все довольны.
Пораскинув немного глазами по исторически написанному коду, выделю еще одну закономерность о которой Вы должны знать. И опять пример кода:

region = RegionSite.objects.get(id=1) t = datetime.datetime.now() for i in range(1000):     list(News.objects.filter(region__in=[region]).values("id")[:10])     # list(News.objects.filter(region__id__in=[region.id]).values("id")[:10])     # list(News.objects.filter(region__in=[1]).values("id")[:10])     # list(News.objects.filter(region__id__in=[1]).values("id")[:10]) print datetime.datetime.now() - t 

Каждая из строк итерации была последовательно включена (чтобы работала только одна). Итого мы можем получить следующие приближенные цифры:

• 1 строка — 6.362800 сек
• 2 строка — 6.073090 сек
• 3 строка — 6.431563 сек
• 4 строка — 6.126252 сек

Расхождения минимальные, но видимые. Предпочтительные 2 и 4 варианты (я в основном пользуюсь 4м). Основная потеря времени — это то, как быстро мы создадим запрос. Тривиально, но показательно, я считаю. Каждый читатель сделает выводы самостоятельно.
И завершим мы статью страшным словом — транзакция.
Частный случай:

• У вас InnoDB
• Вам нужно обновить данные в таблице, в которую клиенты не пишут, а лишь читают (например список товаров)

Делается обновление/вставку на раз-два

1. Подготавливаем 2 словаря — на вставку данных и на обновление данных
2. Каждый из словарей кидаем в свою функцию
3. PROFIT!

Пример реальной функции обновления:

@transaction.commit_manually def update_region_price(item_prices):     """     Обновляем одним коммитом базу     """      from idea.catalog.models import CatalogItemInfo      try:         for ip in item_prices:             CatalogItemInfo.objects.filter(                 item__id=ip["item_id"], region__id=ip["region_id"]             ).update(                 kost=ip["kost"],                 price=ip["price"],                 excharge=ip["excharge"],                 zakup_price=ip["zakup_price"],                 real_zakup_price=ip["real_zakup_price"],                 vendor=ip["vendor"],                 srok=ip["srok"],                 bonus=ip["bonus"],                 rate=ip["rate"],                 liquidity_factor=ip["liquidity_factor"],                 fixed=ip["fixed"],             )      except Exception, e:         print e         transaction.rollback()         return False     else:         transaction.commit()         return True 

Пример реальной функции добавления:

@transaction.commit_manually def insert_region_price(item_prices):     """     Добавляем одним коммитом базу     """      from idea.catalog.models import CatalogItemInfo      try:         for ip in item_prices:             CatalogItemInfo.objects.create(**ip)      except Exception, e:         print e         transaction.rollback()         return False     else:         transaction.commit()         return True 

Зная эти моменты, можно строить эффективные приложения с использованием Django ORM, и не влезать в SQL код.

Ответы на вопросы:

Раз уж пошла такая пляска, то напишите, когда стоит использовать ORM, а когда не стоит. (с) lvo
Считаю что ОРМ стоит использовать всегда, когда оно просто. Не стоит складывать на плечи ORM, а уж тем более базы запросы типа:

User.objects.values('username', 'email').annotate(cnt=Count('id')).filter(cnt__gt=1).order_by('-cnt') 

Тем более на HL-продакшн. Заведите для себя отдельный системный сервачок, в котором так изголяйтесь.
Если у Вас нет возможности писать простыми «ORM-запросами», то измените алгоритм решения задачи.
Для примера, у клиента в ИМ есть фильтрация по характеристикам, с использованием регулярок. Крутая гибкая штука, до тех пор пока посетителей сайта не стало очень много. Сменил подход, вместо стандартного Клиент-ORM-База-ORM-Клиент, переписал на Клиент-MongoDB-Питон-Клиент. Данные в MongoDB формируются по средствам ORM на системном сервере. Как было сказано раньше — HL нельзя достигнуть путем одних манипуляций с ORM

Интересно, почему именно Django. Какие преимущества дает этот фреймворк (и его ОРМ) по сравнению с другими фреймворками / технологиями. (с) anjensan
Исторически. Питон начал изучать вместе с Django. И знания в технологии его использования довожу до максимума. Сейчас в параллельном изучении Pyramid. Сравнить я пока могу только с PHP, и их фреймворками, цмс-ками. Наверное скажу общую фразу — я неэффективно тратил свое время, когда писал на PHP.
Сейчас могу назвать пару серьезных недочетов в Django 1.3.4:

1. Постоянное соединение/разъединение с базой (в старших версиях подправлено)
2. Скорость работы template-процессора. По тестам, найденных в сети, она достаточна мала. Нужно менять 🙂

А вообще, есть один классный прием, как увеличить скорость работы генерации template-процессора.
Никогда не передавайте переменные в шаблон через locals() — при объемных функциях и промежуточных переменных — Вы получите молчаливого медленно шевелящегося умирающего монстра 🙂

Что это за программист такой которому сложно запрос на SQL написать? (с) andreynikishaev
Программист, который ценит свое время на программном коде, а не на средстве взаимодействия между База-Код обработки данных. SQL знать нужно — очень часто работаю напрямую с консолью базы. Но в коде — ORM. ORM легче и быстрее подвергается изменениям, либо дополнением. А так же, если пишешь обоснованно-легкими запросами, легко читать и понимать.

Извините, все! (Бла-бла… жду замечаний, предложений, вопросов, пожеланий)