Оптимизация запросов в 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. Прибегать к рассмотренным методам без крайней надобности не рекомендуется дабы не подрывать безопасность кода, не усложнять его читаемость и отладку, а также не отходить абстракций.