Оптимизация запросов в DjangoORM: когда и как использовать Raw SQL

• Введение в тему оптимизации запросов в Django ORM

• Какие инструменты существуют в Django для работы с сырыми SQL запросами

• Преимущества и недостатки использования сырых SQL запросов

• Недостатки выполнения пользовательского SQL

Введение в тему оптимизации запросов в Django ORM

В документации Django предлагаются следующие методы оптимизации:

1. Индексация

2. Кеширование

3. Уменьшение количества запросов

Индексация

Ускоряет выполнение запросов за счет быстрого поиска записей по индексу.

Индексы — это специальная структура данных, сопоставляющая значения в одном или нескольких столбцах таблицы с соответствующими местоположениями на физическом накопителе, что позволяет базе данных быстро находить строки по конкретному запросу без необходимости сканирования всей таблицы. В Django можно создавать индексы как вручную, так и автоматически.

Кеширование

Сохраняет результаты выполнения запросов, благодаря чему можно быстро получать их при последующих запросах.

Django предоставляет несколько встроенных механизмов кэширования, таких как cache_page и cache_control. На проекте закупок данная оптимизация не применяется, так как кеширование происходит на уровне базы данных.

*сache_page кэширует результаты представления на определенное время, чтобы при последующих запросах к этому представлению они могли быть возвращены из кэша, не выполняя запрос к базе данных.
*cache_control позволяет управлять кэшированием на уровне HTTP-заголовков.

Уменьшение количества запросов

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

Такой подход упрощает работу со связанными объектами, но так же может привести к проблеме N + 1, когда для каждой связанной сущности генерируется дополнительный запрос в базу.

Метод select_related() предназначен для оптимизации запросов, когда мы работаем со связями ForeignKey и OneToOneField. Вместо отдельного запроса для получения связанных данных при каждом обращении, select_related() делает все в одном запросе. Это позволяет избежать так называемой проблемы ‘N+1’, ускоряя доступ к связанным данным.
Для загрузки данных этот метод использует JOIN, в случае если в основной таблице записей много и они ссылаются на одни и те же данные в связанной таблице, в результирующей таблице данные будут повторяться, что может наоборот негативно повлиять на производительность.

В отличие от select_related, prefetch_related загружает связанные объекты отдельным запросом для каждого поля переданного в качестве параметра, кеширует их в памяти, и производит связывание объектов внутри python. Это позволяет избежать множества лишних запросов и помогает бороться с проблемой ‘N+1’.

Такой подход позволяет загружать объекты для ManyToMany полей и записи которые ссылаются на нашу таблицу через ForeignKey поле используя related_name.

hb=Province.objects.prefetch_related ('city_set').get (name__iexact=u"Hubei Province")  for city in hb.city_set.all():   city.name

Запускаемые SQL-запросы:

SELECT Optimize_province.id, Optimize_province.name FROM Optimize_province WHERE Optimize_province.name LIKE Hubei Province;  SELECT Optimize_city.id, Optimize_city.name, Optimize_city.province_id FROM Optimize_city WHERE Optimize_city.province_id IN (1);

Методы values() и values_list() служат для выборки только тех полей из модели, которые переданы в качестве параметров. values() возвращает результаты в виде словарей, а values_list() — в виде кортежей. Это позволяет уменьшить объем данных, получаемых из базы и тем самым ускорить обработку.

Метод annotate() позволяет добавить новое вычисляемое поле к каждой записи, а aggregate() вычисляет агрегатные значения, например сумму, среднее или количество записей для всего набора данных. Это позволяет избежать дополнительных запросов и выполнить вычисления на уровне БД, что значительно эффективнее.

Методы bulk_create() и bulk_update() служат для массового создания и массового обновления записей соответственно. Использование этих методов позволяет отправить в базу данных только один запрос для создания или обновления большого количества записей одной модели. Это значительно ускоряет процесс массового добавления или изменения данных.

К сожалению, в определенных местах проекта мощности DjangoORM все равно не достаточно. Увеличить производительность можно по средствам сырых SQL запросов. Для этого разработчики Django предлагают следующий инструментарий.

Какие инструменты существуют в Django для работы с сырыми SQL запросами

Метод менеджера raw()

Принимает необработанный SQL-запрос, выполняет его и возвращает экземпляр RawQuerySet, который можно перебирать так же, как обычный QuerySet, но он не обладает всеми теми же возможностями QuerySet. Аннотацию, фильтрацию и упорядочивание необходимо реализовывать в самом SQL запросе , соотвественно, на языке SQL. У метода есть удобное автоматическое сопоставление полей запроса с полями модели. Возможен поиск по индексу. Обладает «отложенными полями модели», когда поля не прописываются в запросе и будут загружаться исключительно по требованию.
Метод поддерживает только SELECT запросы.

Ниже примеры как выглядит вызов метода. Обратите внимание, на второй пример где демонстрируется перебор RawQuerySet и можно заметить в самом запросе аннотацию.

Person.objects.raw(   """     SELECT first AS first_name,            last AS last_name,            bd AS birth_date,            pk AS id,     FROM some_other_table   """ )

people = Person.objects.raw( 'SELECT *, age(birth_date) AS age FROM myapp_person')  for p in people:   print("%s is %s." % (p.first_name, p.age))  # John is 37. # Jane is 42.

В целом этот метод хорошо применим для быстрого получения большого количества данных. Django ожидает, что оператор вернет набор строк из базы данных, ничего при этом не делая.
Для оператора SQL, переданного в .raw(), проверка не выполняется. Если запрос не возвращает строки, это приведет к ошибке.

При необходимости выполнить UPDATE, INSERT или DELETE запросы Django предлагает воспользоваться следующим инструментом.

Выполнение пользовательского SQL напрямую

Выполнение пользовательского SQL напрямую в Django позволяет разработчикам взаимодействовать с базой данных без использования моделей. Это может быть полезно в ситуациях, когда требуется выполнить сложные запросы или операции, которые тяжело описать встроенными функциями Django ORM.

Чтобы использовать соединение с базой данных необходимо вызвать connection.cursor(). Затем вызвать cursor.execute(sql, [params]), чтобы выполнить SQL.и cursor.fetchone(возвращает одну строку из результата запроса) или cursor.fetchall(возвращает все строки из результата запроса).

from django.db import connection  def my_custom_sql(self):   with connection.cursor as cursor:     cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])     cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])     row = cursor.fetchone      return row

По умолчанию Python DB API возвращает результаты без имен полей, в джанго документации предлагается пример написания функции, которая может обрабатывать результат и возвращать dict(в примере ниже это функция «dictfetchall». Другой вариант — использовать collections.namedtuple() из стандартной библиотеки Python. namedtuple — это объект, похожий на кортеж, у которого есть поля, доступные при поиске по атрибутам; он индексируемый и повторяемый.

cursor.execute("SELECT id, parent_id FROM test LIMIT 2") cursor.fetchall # ((54360982, None), (54360880, None))  cursor.execute("SELECT id, parent_id FROM test LIMIT 2") dictfetchall(cursor) # ['parent_id': None, 'id': 54360982}, {'parent_id': None, 'id': 54360880}]  cursor.execute("SELECT id, parent_id FROM test LIMIT 2") results = namedtuplefetchall(cursor) results # [Result(id=54360982, parent_id=None), Result(id=54360880, parent_id=None)] results[0].id # 54360982 results[0][0] # 54360982

Class RawSQL(sql, params, output_field)

Он так же помогает с необработанными выражениями SQL, которые можно встроить напрямую в фильтр или аннотацию. При данном способе мы получаем полноценный QuerySet и открываем возможность выразить сложную фильтрацию для определенного столбца. Класс RawSQL принимает три параметра: sql, params и output_field. Параметр sql представляет собой строку, содержащую SQL-запрос. Параметр params представляет собой список или кортеж, содержащий значения, которые будут подставлены в запрос. Параметр output_field представляет собой тип данных, который будет возвращён запросом.

Примеры демонтрируют простоту использования,как в аннотируемом поле, так и в фильтре.

form django.db.models.expressions import RawSQL  queryset.annotate(val=RawSQL('select col from sometable where othercol = %s', (params,))) queryset.filter(id__in=RawSQL('select id from sometable where col = %s', (params,)))

Extra(select=None,  select_params=None, params=None, where=None, tables=None, order_by=None)

Иногда синтаксис запроса Django сам по себе не может легко выразить сложное предложение WHERE. Для этих крайних случаев Django предоставляет модификатор extra() QuerySet — ловушку для вставки определенных предложений в SQL, генерируемый QuerySet.

• Аргумент select позволяет добавлять дополнительные поля в предложение SELECT. Это должен быть словарь, сопоставляющий имена атрибутов с предложениями SQL ,которые вычисляют значение этого атрибута. Для передачи параметров фрагментам SQL применяется select_params.

• Аргумент params — это список любых дополнительных параметров, которые нужно заменить.

• Where / tables – работают по такому же принципу что и селект, добавляет условия в WHERE/ таблицы в FROM.

• order_by для extra() добавляет упорядочивание в результирующий набор запросов.

*В таких вставках мы можем вписывать атрибуты, о которых мы знаем, что они будут в основном запросе джанго.

Blog.objects.extra(   select={     "entry_count": "SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id"   }, )

SELECT blog_blog.*, (SELECT COUNT(*) FROM blog_entry WHERE blog_entry.blog_id = blog_blog.id) AS entry_count FROM blog_blog;

Entry.objects.extra(select={'is_recent': "pub_date › '2006-01-01' "})

SELECT blog_entry.*, (pub_date > '2006-01-01') AS is_recent FROM blog_entry;

Преимущества и недостатки использования сырых SQL запросов

Преимущества

• Гибкость. Позволяет разработчикам писать сложные запросы, которые могут быть не доступны или сложны для реализации с использованием Django ORM.

• Минимизация количества запросов. В один SQL запрос возможно включить сразу несколько операций, что может уменьшить количество обращений к базе данных и повысить производительность приложения.

• Быстрая работа с большими объёмами данных.Использование raw SQL может быть более эффективным и быстрым, чем ORM, так как Django не нужно тратить время и ресурсы на формирование запроса в бд и обработку данных.

Недостатки

• Отход от принципов ORM. ORM Django предоставляет абстракцию над базой данных, упрощая работу с данными и уменьшая количество кода, необходимого для взаимодействия с базой данных. Использование raw SQL отходит от этих принципов и может привести к потере некоторых преимуществ ORM.

• Сложность отладки и тестирования. Поскольку raw SQL запросы могут быть более сложными и менее абстрактными, они могут затруднить отладку и тестирование вашего кода. Может потребоваться больше усилий для проверки правильности работы ваших запросов.

• Уязвимость кода. Django никак не защищает и не проверяет SQL-запросы, об этом нужно заботиться отдельно, предостерегая код от SQL-инъекций.

  * SQL-инъекция — это один из самых распространённых способов атаки на веб-приложения, использующие базы данных, позволяет злоумышленнику внедрить вредоносный SQL-код в запрос к базе данных.

Заключение

Подведем итоги: сырые SQL запросы это эффективный способ оптимизировать ORM запросы, НО далеко не все случаи подходят под такую оптимизацию. Зачастую это точечно узкие места проекта, где либо слишком сложная бизнес-логика, либо большие объемы данных, работать с которыми на SQL продуктивнее. Django ORM обладает хорошими мощностями для построения достойных SQL запросов, поэтому для использования RawSQL потребуютcя хорошие знания в области баз данных и в частности языка SQL. Прибегать к рассмотренным методам без крайней надобности не рекомендуется дабы не подрывать безопасность кода, не усложнять его читаемость и отладку, а также не отходить абстракций.