Привет, хабр! В данной статье хочу больше чем обычно рассказать про оптимизацию запросов к базе данных в Django. Расскажу о том, как правильно не только запрашивать, но и создавать или обновлять записи в базе. В статье будет много примеров, генерируемый SQL, типы данных, индексы и выводы планировщика запросов.
Данная статья подходит как для начинающих, так и для достаточно опытных разработчиков на django, а также для всех, кто еще не интересовался, что происходит под капотом ORM.
Я видел достаточно много статей на тему оптимизации запросов в django, но почему-то, чаще всего в них рассматривалось буквально пару методов, хорошо, если в них упоминались хотя бы какие-то индексы, или подробно рассматривался какой-то метод. В общем, я не смог беглым поиском найти статью, которая рассказывает обо всем, что важно знать, когда берешься за оптимизацию и решил написать подробное руководство на эту тему.
Тут не будет оптимизаций, которые не касаются взаимодействия с ORM по типу использования functools.cached_property или любого другого кэширования, а также не будут рассмотрены подзапросы, оконные функции, агрегационные функции, группировка и прочие специфические возможности баз данных, так как их рассмотрение выходит далеко за рамки статьи, а возможно и книги.
Понимаю, что материал, мягко говоря, объемный, но вы можете пробежаться по разделам "Итоги главы" или самому оглавлению, чтобы понять, есть ли для вас вообще смысл читать тот или иной раздел.
Оглавление
Минимальная оптимизация
Начнем, пожалуй, с того минимума, который должны знать все разработчики, использующие django orm, но сперва подготовим все необходимое для запуска примеров.
Я создал чистый проект django и в нем добавил 3 модуля minimum для текущей главы, а также middle и advanced для следующих глав.
В примере используется Django 5.2.2 и PostgreSQL 17.5.
Зависимости, которые я использую в примерах
asgiref==3.8.1
asttokens==3.0.0
decorator==5.2.1
Django==5.2.2
django-stubs==5.2.0
django-stubs-ext==5.2.0
executing==2.2.0
factory_boy==3.3.3
Faker==37.3.0
ipython==9.2.0
ipython_pygments_lexers==1.1.1
jedi==0.19.2
matplotlib-inline==0.1.7
parso==0.8.4
pexpect==4.9.0
prompt_toolkit==3.0.51
psycopg2-binary==2.9.10
ptyprocess==0.7.0
pure_eval==0.2.3
Pygments==2.19.1
Pympler==1.1
sqlparse==0.5.3
stack-data==0.6.3
traitlets==5.14.3
types-PyYAML==6.0.12.20250516
typing_extensions==4.14.0
tzdata==2025.2
wcwidth==0.2.13Утилиты для вывода sql, размера объектов, и плана планировщика
import sqlparse
from django.db import connection
from django.db import reset_queries
from django.db.models import QuerySet
from pympler.asizeof import asizeof
# Напишем пару утилит для дальнейшего тестирования
def get_size(obj):
# Возвращает читаемое значение размера объекта
size_bytes = asizeof(obj)
size_kb = size_bytes / 1024
size_mb = size_kb / 1024
return f"{size_mb:.2f} MB, {size_kb:.2f} KB"
# Контекстный менеджер, который будет выводить
# SQL запросы, совершенные во время выполнения
# кода в его контексте
class CheckQueries:
_count_only: bool
_time_only: bool
# На входе принимаем параметры, управляющие выводом
def __init__(self, count_only=False, time_only=False):
# На входе очищаем список запросов
reset_queries()
self._count_only = count_only
self._time_only = time_only
# На выходе очищаем список запросов
def __enter__(self, count_only=False):
reset_queries()
def __exit__(self, exc_type, exc_val, exc_tb):
if self._count_only:
print(f"Количество запросов: {len(connection.queries)}")
return
# Проходимся по всем совершенным запросам и печатаем
# информацию о них
for number, query in enumerate(connection.queries):
if self._time_only:
print(f"Время выполнения: {query['time']} s")
continue
print(f"SQL запрос {number + 1}")
# Выводим отформатированный sql
print(sqlparse.format(
query['sql'],
reindent=True,
keyword_case='upper'
))
print(f"Время выполнения: {query['time']} s")
print("-" * 20)
# Функция, для вывода плана планировщика и фактического времени
# выполнения запроса
def explain(queryset: QuerySet):
print(queryset.explain(analyze=True))
Теперь, накидаем пару тестовых моделей django.
from django.db import models
# Модель, содержащая только текстовые поля
class Bar(models.Model):
short_field = models.CharField(max_length=64)
long_field = models.TextField(max_length=4096)
...
# Модель, содержащая как текстовые поля, так и первичный ключ таблицы Bar
class Foo(models.Model):
short_field = models.CharField(max_length=64)
long_field = models.TextField(max_length=4096)
...
bar = models.ForeignKey(Bar, on_delete=models.CASCADE)Генерируем тестовые данные. На данном этапе, думаю, нам хватит по 10 000 строчек в каждой из таблиц.
Код для генерации тестовых данных
# Сгенерируем фэйковые данные
from django.db import transaction
from factory.fuzzy import FuzzyText
from minimum.models import Bar, Foo
# Создаем функции, которые будут генерировать рандомные строки
long_text_generator = FuzzyText(length=4096)
short_text_generator = FuzzyText(length=64)
def get_long_text():
return long_text_generator.fuzz()
def get_short_text():
return short_text_generator.fuzz()
# Функция для создания записей в базе данных для модели Bar
def create_bars(count):
# Контейнер для экземпляров класса Bar
bars = []
for _ in range(count):
# Добавляем экземпляр в контейнер
bars.append(Bar(
# Заполняем рандомными строками
short_field=get_short_text(),
long_field=get_long_text(),
))
return Bar.objects.bulk_create(bars, batch_size=1000)
# Функция для создания записей в базе данных для модели Foo
@transaction.atomic
def create_foos(count):
# Здесь логика аналогична create_bars
bars = create_bars(count)
foos = []
for bar in bars:
foos.append(Foo(
# Добавляем значение первичного ключа
bar_id=bar.id,
short_field=get_short_text(),
long_field=get_long_text(),
))
return Foo.objects.bulk_create(foos, batch_size=1000)
# Создадим по 10 000 записей Foo и Bar для наглядности
create_foos(10_000)Наконец, можем посмотреть несколько примеров
Проблема N + 1
Тут хочу плавно подвести к самой проблеме, поэтому ее суть определим в процессе.
from minimum.models import Foo
# Давайте представим, что нам нужна первая запись из таблицы Foo
with CheckQueries():
# Получаем первую запись из таблицы Foo
foo = Foo.objects.first()
# А также мы хотим получить значение какого либо поля foo.bar.*
bar_short_field = foo.bar.short_fieldЗдесь будет выведено 2 SQL запроса
SQL запрос 1 (получение записи из таблицы Foo)
SELECT "minimum_foo"."id",
"minimum_foo"."short_field",
"minimum_foo"."long_field",
"minimum_foo"."bar_id"
FROM "minimum_foo"
ORDER BY "minimum_foo"."id" ASC
LIMIT 1
Время выполнения: 0.000s
--------------------
SQL запрос 2 (получение записи из таблицы Bar)
SELECT "minimum_bar"."id",
"minimum_bar"."short_field",
"minimum_bar"."long_field"
FROM "minimum_bar"
WHERE "minimum_bar"."id" = 20001
LIMIT 21
Время выполнения: 0.000sПо умолчанию django не запрашивает значения из связанных таблиц, и, при обращении к атрибуту, данные для которого еще не были получены, django всякий раз будет делать запрос к базе данных.
При повторном обращении к уже запрошенному атрибуту, новый запрос производиться не будет. На первый взгляд, это поведение может показаться удобным, и периодически его хочется использовать, но, давайте взглянем, чем может быть опасна данная особенность.
# Теперь представим, что нам нужно более одной записи из таблицы Foo
with CheckQueries(count_only=True):
# На данном этапе запрос в базу данных все еще не будет выполнен.
# Чтобы выполнить запрос, QuerySet необходимо оценить.
# Это можно сделать несколькими способами:
# 1) Начать итерироваться по экземпляру QuerySet
# 2) Передать его в вызов list, что по сути то же самое,
# 3) Передать его в вызов bool (но тут есть нюансы).
# В целом, оценить QuerySet можно и иначе,
# но обычно этого более чем достаточно.
queryset = Foo.objects.all()
# На этом этапе QuerySet будет оценен во время вызова
# метода __iter__ класса QuerySet, в то время как срез
# [:1000] только добавит LIMIT к запросу
for foo in queryset[:1000]: # SQL запрос 1
# А также мы хотим получить значение какого либо поля foo.bar.*
bar_short_field = foo.bar.short_field # SQL запрос 2 - 1001
# Количество запросов: 1001Подробнее про оценку QuerySet можно прочитать в документации.
На этот раз мы получили 1001 запрос на 1000 записей, данное поведение обычно и называют "Проблемой N + 1".
Мы единожды запрашиваем записи из таблицы Foo, но для каждой из записей мы делаем еще по одному запросу в базу данных для получения записи Bar.
Чтобы избежать данного поведения, нам необходимо сделать INNER JOIN таблицы Bar. В django это можно сделать с помощью метода QuerySet.select_related.
with CheckQueries():
# Используем select_related, чтобы присоединить к запросу
# данные из таблицы, на которую ссылается поле bar
queryset = Foo.objects.select_related("bar")
for foo in queryset[:1000]: # SQL запрос 1
# Теперь следующая строчка не порождает новые запросы
bar_short_field = foo.bar.short_fieldНа этот раз генерируется только один SQL запрос
SELECT "minimum_foo"."id",
"minimum_foo"."short_field",
"minimum_foo"."long_field",
"minimum_foo"."bar_id",
"minimum_bar"."id",
"minimum_bar"."short_field",
"minimum_bar"."long_field"
FROM "minimum_foo"
INNER JOIN "minimum_bar" ON ("minimum_foo"."bar_id" = "minimum_bar"."id")
LIMIT 1000
Время выполнения: 0.008sПро связи между таблицами можно прочитать тут.
Чаще всего у вас будет куда больше одной подобной связи с отношением M2O или O2O. Если вам нужны данные из связанной такими отношениями таблицы, стоит добавить в вызов select_related название поля с такой связью. Проблема, подобная описанной выше, присутствует и в связях M2M или O2M, но о ее решении мы поговорим позже.
Оптимизация количества запрашиваемых данных
Пока что, нас все еще не устраивает последний SQL запрос, так как мы запрашиваем из базы данных больше столбцов, чем нам действительно необходимо.
У нас есть несколько вариантов того, как мы можем это исправить
Вариант 1 - использование defer (отсроченный запрос поля)
На самом деле, данный метод редко когда может пригодиться на практике, но, такие ситуации все-таки не исключены. Его работа заключается в том, что мы явно указываем, какие поля мы не хотим загружать сразу, но их можно будет дополнительно загрузить при необходимости.
with CheckQueries():
foos = (
Foo.objects
.select_related("bar")
.defer("long_field", "bar__long_field")
)
# Итерируемся
for foo in foos[:1000]: # SQL запрос 1
# А также мы хотим получить значение foo.bar.short_field
bar_short_field = foo.bar.short_field
# На этом этапе у нас все еще будет один SQL запрос
# А теперь обратимся к полям, получение которых мы отложили
foo_10_long_field = foos[10].long_field # SQL запрос 2
foo_10_bar_long_field = foos[10].bar.long_field # SQL запрос 3
Получаем 3 SQL запроса
SQL запрос 1
SELECT "minimum_foo"."id",
"minimum_foo"."short_field",
"minimum_foo"."bar_id",
"minimum_bar"."id",
"minimum_bar"."short_field"
FROM "minimum_foo"
INNER JOIN "minimum_bar" ON ("minimum_foo"."bar_id" = "minimum_bar"."id")
LIMIT 1000
Время выполнения: 0.008s
--------------------
SQL запрос 2
SELECT "minimum_foo"."id",
"minimum_foo"."long_field"
FROM "minimum_foo"
WHERE "minimum_foo"."id" = 11
LIMIT 21
Время выполнения: 0.001s
--------------------
SQL запрос 3
SELECT "minimum_bar"."id",
"minimum_bar"."long_field"
FROM "minimum_bar"
WHERE "minimum_bar"."id" = 20011
LIMIT 21
Время выполнения: 0.000sТут важно, что для каждого экземпляра, при обращении к полю, которое было отложено, мы будем совершать дополнительный запрос к базе данных.
Это может быть выгодно, только если для малого количества записей из всей выборки нужны значения полей, и, в то же время, если получать данные для всей выборки, издержки по памяти и/или времени конвертации объектов базы данных в объекты python выше чем время запроса полей для части записей из выборки.
Вот полная документация по данному методу.
Вариант 2 - использование only (явное указание необходимых полей)
with CheckQueries():
queryset = Foo.objects.select_related("bar").only("bar__short_field")
for foo in queryset[:1000]: # SQL запрос 1
# А также мы хотим получить значение foo.bar.short_field
bar_short_field = foo.bar.short_fieldПолучаем 1 SQL запрос
SELECT "minimum_foo"."id",
"minimum_foo"."bar_id",
"minimum_bar"."id",
"minimum_bar"."short_field"
FROM "minimum_foo"
INNER JOIN "minimum_bar" ON ("minimum_foo"."bar_id" = "minimum_bar"."id")
LIMIT 1000
Время выполнения: 0.001sПоле id при вызове defer и only всегда будет загружено немедленно, так как экземпляру модели django необходимо оставить возможность обновить запись в случае изменения атрибутов.
Поля minimum_foo.bar_id и minimum_bar.id будут загружены, по той же причине, но на случай изменения данных в связанной таблице, доступной по атрибуту bar.
Тут также стоит отметить, что методы defer и only можно комбинировать. Для более подробных разъяснений их совместного поведения, вы можете обратиться к официальной документации.
Вариант 3 - использование values (получение словарей с данными вместо экземпляров моделей django)
Данный вариант будет эффективнее по памяти и скорости, но накладывает ряд ограничений. В комбинации с ним у вас не получится использовать часть методов QuerySet, а также вы не сможете сразу же изменять полученные данные. Тем не менее практически, все потребности чтения записей из таблицы с его помощью можно закрыть.
Помимо этого, при вызове метода values не нужно вызывать select_related, все необходимые таблицы будут присоединены автоматически. Прикрепляю документацию метода для справки.
with CheckQueries():
# При использовании bar__* в методе values, таблица Bar будет
# автоматически присоединена к запросу
queryset = Foo.objects.values("short_field", "bar__short_field")[:1000]
for foo in queryset[:1000]: # SQL запрос 1
# В данном случае foo является словарем, поэтому получение значения
# осуществляется соответственно
short_field = foo["short_field"]
bar_short_field = foo["bar__short_field"]В данном случае получаем следующий SQL
SELECT "minimum_foo"."short_field" AS "short_field",
"minimum_bar"."short_field" AS "bar__short_field"
FROM "minimum_foo"
INNER JOIN "minimum_bar" ON ("minimum_foo"."bar_id" = "minimum_bar"."id")
LIMIT 1000
Время выполнения: 0.001 sЗдесь важно, что мы загружаем только те поля, которые мы запросили, так как более нет необходимости поддерживать возможность обновления значений в базе данных.
Вариант 4 - использование values_list (получение кортежей с данными вместо экземпляров моделей django)
Данный вариант еще более экономный по памяти, так как не содержит ключи словаря, что бывает полезно при работе с большим количеством полей и на значительной выборке.
Метод values_list также не требует вызова select_related. Документация метода для справки.
# При использовании bar__* в методе values, таблица Bar будет
# автоматически присоединена к запросу
queryset = Foo.objects.values_list("short_field", "bar__short_field")
for foo in queryset[:1000]: # SQL запрос 1
# В данном случае foo является кортежем, поэтому получение значения
# осуществляется соответственно
short_field, bar_short_field = fooSQL тут аналогичен вызову values.
Также, полезной особенностью данного метода является возможность вернуть список значений вместо кортежей, если мы запрашиваем одно поле. Данное поведение бывает полезно, если нам нужны, например, только id для одного из следующих запросов.
bar_ids_queryset = Foo.objects.values_list("bar_id", flat=True)[:10]
list(bar_ids_queryset)
# [20001, 20002, 20003, 20004, 20005, 20006, 20007, 20008, 20009, 20010]Сравнение по памяти.
Не отходя далеко от примеров выше, давайте взглянем, а на сколько же большая разница будет в использовании памяти. Для наглядности будем запрашивать все записи.
# ALL
get_size(list(Foo.objects.select_related("bar")))
# '89.65 MB, 91797.45 KB'
# ONLY
get_size(list(
Foo.objects.select_related("bar")
.only("short_field", "bar__short_field")
))
# '10.61 MB, 10859.90 KB'
# VALUES
get_size(list(Foo.objects.values("short_field", "bar__short_field")))
# '3.97 MB, 4062.67 KB'
# VALUES_LIST
get_size(list(Foo.objects.values_list("short_field", "bar__short_field")))
# '2.75 MB, 2812.55 KB'Думаю, тут цифры говорят за себя. Если ваша задача состоит в том, чтобы максимально оптимизировать взаимодействие с базой данных, вам стоит смотреть в сторону values и values_list.
Оптимизация по памяти для больших запросов
На самом деле, у вас есть еще одна возможность ограничить моментальное использование памяти. Для этого можно воспользоваться методом iterator. Данный метод получает записи из базы данных по частям. Это бывает нужно, когда мы запрашиваем значительное количество данных из базы.
with CheckQueries():
queryset = Foo.objects.values("short_field", "bar__short_field")
for foo in queryset.iterator(chunk_size=1000):
...При использовании iterator, обратите внимание на то, что для разных баз данных метод будет работать по-разному. У вас либо будут использоваться серверные курсоры, либо нет. Так, например, с PostgreSQL для использования серверных курсоров, вам необходимо, чтобы значение настройки DISABLE_SERVER_SIDE_CURSORS было равно False. Подробнее об этом можно прочитать в документации.
SQL с серверными курсорами
DECLARE "_django_curs_134432763113728_sync_1" NO SCROLL
CURSOR WITH HOLD
FOR
SELECT "minimum_foo"."short_field" AS "short_field",
"minimum_bar"."short_field" AS "bar__short_field"
FROM "minimum_foo"
INNER JOIN "minimum_bar" ON ("minimum_foo"."bar_id" = "minimum_bar"."id")SQL без серверных курсоров
SELECT "minimum_foo"."short_field" AS "short_field",
"minimum_bar"."short_field" AS "bar__short_field"
FROM "minimum_foo"
INNER JOIN "minimum_bar" ON ("minimum_foo"."bar_id" = "minimum_bar"."id")Также, в последних версиях django итерирование по записям базы данных можно производить асинхронно.
queryset = Foo.objects.values_list("short_field", "bar__short_field")
async for foo in queryset.aiterator(chunk_size=1000):
...Но здесь есть ряд тонкостей и ограничений о которых можно почитать в соответствующем разделе документации.
Прочие методы оптимизации
Давайте рассмотрим еще пару методов, помимо упомянутых выше, использование которых, может помочь немного оптимизировать часть ваших запросов.
Зачастую нам не нужны все данные запроса, а нас интересует только наличие записей или их количество, давайте посмотрим как их можно получить.
Получение количества.
# Если нас действительно интересует только количество
# записей, самым лучшим способом будет использовать
# метод count
with CheckQueries():
count = Foo.objects.count()Получаем следующий SQL
SELECT COUNT(*) AS "__count"
FROM "minimum_foo"
Время выполнения: 0.002 sКогда нам нужно получить и данные и их количество, при использовании count нужно быть осторожным в порядке выполнения действий.
# Вот плохой вариант
with CheckQueries():
queryset = Foo.objects.all()
count = queryset.count() # SQL запрос 1
foos = list(queryset) # SQL запрос 2Получаем 2 SQL запроса
SQL запрос 1
SELECT COUNT(*) AS "__count"
FROM "minimum_foo"
Время выполнения: 0.001 s
--------------------
SQL запрос 2
SELECT "minimum_foo"."id",
"minimum_foo"."short_field",
"minimum_foo"."long_field",
"minimum_foo"."bar_id"
FROM "minimum_foo"
Время выполнения: 0.048 s# А вот приемлемый вариант
with CheckQueries():
queryset = Foo.objects.all()
foos = list(queryset) # SQL запрос 1
count = queryset.count()На этот раз получаем только 1 запрос
SELECT "minimum_foo"."id",
"minimum_foo"."short_field",
"minimum_foo"."long_field",
"minimum_foo"."bar_id"
FROM "minimum_foo"
Время выполнения: 0.035 sНа самом деле, чтобы избегать подобных казусов, в данном случае лучше использовать len, а не метод QuerySet.count, так как в данном случае ошибиться не получится.
# Если мы сначала посчитаем количество записей,
# а потом преобразуем QuerySet в список, все равно
# в базу данных будет отправлен только один запрос
with CheckQueries():
queryset = Foo.objects.all()
count = len(queryset) # SQL запрос 1
# На этом этапе результат выполнения запроса будет
# закэширован в queryset и при повторной оценке
# Queryset данные будут получены из кэша
foos = list(queryset)В случае с вызовом count после оценки QuerySet, вместо запроса в базу данных вызывается len на кэше уже полученного из базы данных результата.
По поводу кэширования результатов в QuerySet, вам также стоит быть осторожными, так как желательно не оставлять в памяти оцененные QuerySet, которые более не используются (метод iterator как раз не записывает результат в кэш). Помимо этого, учтите, что если QuerySet будет как-либо изменен (вызовом одного из методов QuerySet), данные будут повторно запрошены из базы данных при следующей оценке.
Еще одним граничным случаем может быть необходимость в пагинации данных. В данном случае вам всегда выгоднее делать 2 запроса.
with CheckQueries():
total = Foo.objects.count() # SQL запрос 1
page = Foo.objects.values_list("id", flat=True)[10:20]
print(f"Total: {total} PageIds:{list(page)}") # SQL запрос 2
# Total: 10000 PageIds:[11, 12, 13, 14, 15, 16, 17, 18, 19, 20]Получаем следующие 2 SQL запроса
# SQL запрос 1
SELECT COUNT(*) AS "__count"
FROM "minimum_foo"
# Время выполнения: 0.001 s
# --------------------
# SQL запрос 2
SELECT "minimum_foo"."id" AS "id"
FROM "minimum_foo"
LIMIT 10
OFFSET 10
# Время выполнения: 0.000 sПроверка наличия записей.
В целом, применимость почти аналогична применимости метода count. Если нас интересует исключительно наличие записей в базе данных, эффективнее использовать метод exists.
with CheckQueries():
# Проверим наличие записей
exists = Foo.objects.exists()Получаем следующий SQL
SELECT 1 AS "a"
FROM "minimum_foo"
LIMIT 1
Время выполнения: 0.001 sЭто будет быстрее, чем использование метода first или last в виду отсутствия издержек на передачу и преобразование данных.
НО, если вам нужно использовать записи, наличие которых вы проверяли, тогда
использование exists не имеет особого смысла, так как, помимо него, вам придется все равно делать еще один запрос на получение записей.
Получение первой / последней записи.
В качестве небольшой оптимизации стоит отметить, что зачастую вам может понадобиться только первая или последняя запись в таблице базы данных. Для этого стоит обратить внимание на следующие методы: first и last.
with CheckQueries():
# Получаем первую запись
first = Foo.objects.values("id").first() # SQL запрос 1
# Получаем последнюю запись
last = Foo.objects.values("id").last() # SQL запрос 2Получаем следующие 2 запроса
SQL запрос 1
SELECT "minimum_foo"."id" AS "id"
FROM "minimum_foo"
ORDER BY "minimum_foo"."id" ASC
LIMIT 1
Время выполнения: 0.001 s
--------------------
SQL запрос 2
SELECT "minimum_foo"."id" AS "id"
FROM "minimum_foo"
ORDER BY "minimum_foo"."id" DESC
LIMIT 1
Время выполнения: 0.000 sТакже, существуют методы latest и earliest, но, для их использования,
вы должны указать в Meta вашей модели значение для атрибута get_latest_by Подробнее про них можно почитать в документации.
Итоги главы
Зафиналим данную главу следующими выводами:
Всегда стоит использовать
select_relatedдля запроса данных по полям сForeignKeyилиOneToOneField, если только не используемvaluesилиvalues_listЕсли часть данных не нужна, лучше использовать
onlyилиdeferЕсли вы запрашиваете много данных, и вам не нужны методы модели django используйте
valuesилиvalues_list.На большой выборке стоит использовать
iteratorЕсли вам нужно только количество записей, запрашивайте его через
countЕсли вам важно только наличие записей, используйте
existsЕсли вам нужна только первая или последняя запись, используйте
firstилиlastБудьте осторожны с кэшированием
QuerySet
Базовая оптимизация
Для того, чтобы глубже погрузиться в оптимизацию запросов, давайте попробуем добавить новые модели с более сложными связями.
Описание моделей модуля middle
from django.db import models
from django.db.models import IntegerChoices
from django.utils.translation import gettext_lazy as _
# Теперь давайте рассмотрим более сложный пример и
# попробуем организовать хранение адресов компании
# Пока что опустим детали в виде уникальности записей,
# описания Meta.verbose_name и прочего
# Абстрактная модель с определением поля name.
# Будем наследоваться от нее при необходимости.
class NameMixin(models.Model):
name = models.CharField(
verbose_name=_("Название"),
max_length=128
)
class Meta:
abstract = True
# Страна
class Country(NameMixin): ...
# Регион
class Region(NameMixin):
country = models.ForeignKey(
Country,
verbose_name=_("Страна"),
on_delete=models.CASCADE
)
# Город
class City(NameMixin):
region = models.ForeignKey(
Region,
verbose_name=_("Регион"),
on_delete=models.CASCADE
)
# Части адреса
class AddressPart(models.Model):
position = models.IntegerField(verbose_name=_("Позиция"))
key = models.CharField(
verbose_name=_("Ключ"),
max_length=64
)
value = models.CharField(
verbose_name=_("Значение"),
max_length=64
)
# Enum с типами адресов
class AddressType(IntegerChoices):
FACT = 0, _("Фактический")
LEGAL = 1, _("Юридический")
OTHER = 2, _("Другой")
# Адрес
class Address(models.Model):
# На самом деле, у вас есть 3 варианта того, как организовать
# хранение типа адреса. Если у вас всего 2 значения в Enum,
# вы можете обойтись и BooleanField, например так
# is_legal = models.BooleanField(
# verbose_name=_("Юридический адрес"),
# default=False
# )
# Возможно, вы хотите настраивать описание или расширять
# количество типов адресов в административной панели,
# тогда вы можете сделать дополнительную таблицу для
# хранения типов адресов.
# type = models.ForeignKey(
# AddressTypeModel,
# verbose_name=_("Тип адреса"),
# on_delete=models.PROTECT
# )
# Для примера используем третий вариант с использованием Enum
type = models.SmallIntegerField(
verbose_name=_("Тип адреса"),
choices=AddressType.choices
)
# Тут для примера будем использовать M2M связь, но, в
# действительности, здесь лучше использовать
# django.contrib.postgres.fields.ArrayField
# либо models.JSONField, так как эти данные нам нужны только
# в пределах записей таблицы Address, и нужны почти всегда
parts = models.ManyToManyField(
AddressPart,
verbose_name=_("Части адреса")
)
# Здесь ссылаемся на город. Город ссылается на регион.
# Регион ссылается на страну. В целом, если вам нужно
# всегда иметь быстрый доступ к значениям первичных ключей
# соответствующих таблиц, и вас не сильно волнует место
# на диске, вы можете продублировать вложенные связи и здесь
city = models.ForeignKey(
City,
verbose_name=_("Город"),
on_delete=models.CASCADE
)
# Компания
class Company(NameMixin):
addresses = models.ManyToManyField(
Address,
verbose_name=_("Адреса компании")
)
Генерация тестовых данных
# Сгенерируем фэйковые данные
from django.db import transaction
import factory
from factory.django import DjangoModelFactory
from factory.fuzzy import FuzzyText
from middle.models import (
Company, Address, AddressPart,
City, Region, Country, AddressType
)
# Фабрика для создания стран
class CountryFactory(DjangoModelFactory):
name = FuzzyText(length=15)
class Meta:
model = Country
# Фабрика для создания регионов
class RegionFactory(DjangoModelFactory):
name = FuzzyText(length=30)
# Используем уже созданные страны
country = factory.Iterator(Country.objects.all())
class Meta:
model = Region
# Фабрика для создания городов
class CityFactory(DjangoModelFactory):
name = FuzzyText(length=20)
# Используем уже созданные регионы
region = factory.Iterator(Region.objects.all())
class Meta:
model = City
# Фабрика для создания частей адреса
class AddressPartFactory(DjangoModelFactory):
# Создаем с первой по четвертую позиции адресов
position = factory.Iterator([1, 2, 3, 4])
key = factory.Iterator(["ул", "д", "к", "кв"])
value = FuzzyText(length=20)
class Meta:
model = AddressPart
# Фабрика для создания связей между адресом и его частью
class AddressAddressPartFactory(DjangoModelFactory):
addresspart = factory.SubFactory(AddressPartFactory)
class Meta:
model = Address.parts.through
# Фабрика для создания адресов
class AddressFactory(DjangoModelFactory):
type = factory.Iterator(AddressType.values)
# Создаем по 4 части адресов
parts = factory.RelatedFactoryList(
AddressAddressPartFactory,
factory_related_name="address",
size=4
)
# Используем уже созданные города
city = factory.Iterator(City.objects.all())
class Meta:
model = Address
# Фабрика для создания связей между компанией и адресом
class CompanyAddressFactory(DjangoModelFactory):
address = factory.SubFactory(AddressFactory)
class Meta:
model = Company.addresses.through
class CompanyFactory(DjangoModelFactory):
name = FuzzyText(length=30)
# Создаем по 3 адреса для каждой компании
addresses = factory.RelatedFactoryList(
CompanyAddressFactory,
factory_related_name="company",
size=3
)
class Meta:
model = Company
@transaction.atomic
def create_test_data():
# Создадим 20 стран
CountryFactory.create_batch(20)
# Создадим 100 регионов
RegionFactory.create_batch(100)
# Создадим 1000 городов
CityFactory.create_batch(1000)
# Создадим 10000 компаний с 3 адресами в каждой
CompanyFactory.create_batch(10000)
create_test_data()Оптимизация для M2M и O2M связей
Давайте продолжим уже начатую тему оптимизации загрузки данных связанных таблиц.
Импорты для этой главы
from pprint import pprint
from django.db.models import Prefetch, QuerySet, F, Value
from django.db.models.functions import Concat
from middle.models import (
Company, AddressType, Address,
AddressPart, Country, City
)
from minimum.scripts.utils import CheckQueries# Теперь у нас есть куда большая вложенность, а также поля с отношением M2M.
# Давайте посмотрим, что происходит на примере одной записи
with CheckQueries():
# Получаем первую запись из таблицы компаний
company = Company.objects.first() # SQL запрос 1
# Представим, что мы хотим получить полный фактический адрес
address = company.addresses.get(type=AddressType.FACT) # SQL запрос 2
city = address.city # SQL запрос 3
region = city.region # SQL запрос 4
country = region.country # SQL запрос 5
# Части адресов нужно отсортировать по позициям
address_parts = address.parts.order_by("-position") # SQL запрос 6
pprint({
"Страна": country.name,
"Регион": region.name,
"Город": city.name,
# Тут добавляем части адресов
**{part.key: part.value for part in address_parts},
})
# {'Город': 'qBPOWMylkgqjzwVHbOKx',
# 'Регион': 'DFFCvrMwZaMHKtCkUGxOzPnKvSQvGs',
# 'Страна': 'hUtCDtXieLKLXox',
# 'ул': 'urAVQxDYxsVxLSkDkUQl'}
# 'кв': 'oMKiAakyCoJJuYEdUkMd',
# 'к': 'emyuXPNEdmGhcnSyMdRc',
# 'д': 'ccPkAGylpvSxIZNbOIbW',
# Ожидаемого результата мы добилисьА теперь давайте взглянем на SQL
SQL запрос 1
SELECT "middle_company"."id",
"middle_company"."name"
FROM "middle_company"
ORDER BY "middle_company"."id" ASC
LIMIT 1
Время выполнения: 0.001 s
--------------------
Тут есть INNER JOIN так как связь
M2M реализована через промежуточную таблицу
SQL запрос 2
SELECT "middle_address"."id",
"middle_address"."type",
"middle_address"."city_id"
FROM "middle_address"
INNER JOIN "middle_company_addresses" ON ("middle_address"."id" = "middle_company_addresses"."address_id")
WHERE ("middle_company_addresses"."company_id" = 1
AND "middle_address"."type" = 0)
LIMIT 21
Время выполнения: 0.000 s
--------------------
SQL запрос 3
SELECT "middle_city"."id",
"middle_city"."name",
"middle_city"."region_id"
FROM "middle_city"
WHERE "middle_city"."id" = 1
LIMIT 21
Время выполнения: 0.000 s
--------------------
SQL запрос 4
SELECT "middle_region"."id",
"middle_region"."name",
"middle_region"."country_id"
FROM "middle_region"
WHERE "middle_region"."id" = 1
LIMIT 21
Время выполнения: 0.000 s
--------------------
SQL запрос 5
SELECT "middle_country"."id",
"middle_country"."name"
FROM "middle_country"
WHERE "middle_country"."id" = 1
LIMIT 21
Время выполнения: 0.000 s
--------------------
SQL запрос 6
SELECT "middle_addresspart"."id",
"middle_addresspart"."position",
"middle_addresspart"."key",
"middle_addresspart"."value"
FROM "middle_addresspart"
INNER JOIN "middle_address_parts" ON ("middle_addresspart"."id" = "middle_address_parts"."addresspart_id")
WHERE "middle_address_parts"."address_id" = 1
ORDER BY "middle_addresspart"."position" ASC
Время выполнения: 0.000 sМы получили 6 запросов в базу данных. Думаю, это уже выглядит внушительно, но давайте посмотрим, сколько будет отправлено запросов, если мы захотим получить данные обо всех адресах для 10 компаний.
# Запросов будет много, поэтому выведем только количество
with CheckQueries(count_only=True):
# Получаем первую запись из таблицы компаний
companies = Company.objects.all()[:10]
for company in companies:
# Итерируемся по адресам
for address in company.addresses.all():
city = address.city
region = city.region
country = region.country
address_parts = list(address.parts.order_by("position"))
# Количество запросов: 131Думаю, 131 запрос это явно много, хотя по факту мы запросили всего лишь адреса 10 компаний.
Конечно, для примера мы выбрали не лучшее решение по части проектирования таблиц базы данных, но очень часто у вас все-таки могут быть подобные связи по различным причинам.
В прошлых разделах мы рассматривали использование метода select_related для явного указания того, что таблицу нужно присоединить заранее, но данный метод работает только со связями M2O и O2O, поэтому тут нам его недостаточно.
Для уменьшения количества запросов, в данном случае необходимо использовать метод prefetch_related. Данный метод принимает названия атрибутов связей либо объект Prefetch. Также стоит отметить, что prefetch_related и select_related можно использовать вместе.
Использование этого метода, для каждого из переданных аргументов делает дополнительный запрос в базу данных, и записывает его результат в кэш QuerySet для дальнейшего доступа к значениям уже на уровне экземпляров моделей django.
with CheckQueries():
# Получаем первую запись из таблицы компаний
first_company, second_company = (
Company.objects.prefetch_related("addresses")[:2]
) # SQL запросы 1 и 2
# Попробуем получить адреса первой компании
# Новый запрос в базу данных отправляться не будут,
# так как мы заранее запросили все необходимые адреса.
first_company_addresses = list(first_company.addresses.all())
# Однако, если мы запросим конкретный тип адреса для второй компании,
# запрос в базу данных будет отправлен, так как QuerySet изменился
# и кэш, записанный при вызове prefetch_related более не актуален для
# адресов второй компании
second_company_fact_address = (
second_company.addresses.get(type=AddressType.FACT)
) # SQL запрос 3
# В таких случаях нужно использовать фильтрацию на уровне python
first_company_fact_address = next(filter(
lambda address: address.type == AddressType.FACT,
first_company_addresses
))
# Помимо этого, если мы обратимся к городу любого адреса, будет отправлен
# еще один запрос в базу данных на его получение
# SQL запрос 4
first_company_fact_address_city = first_company_fact_address.city
# Поэтому, зачастую передачи имени атрибута со связью M2M недостаточно
На этот раз мы получаем 4 запроса
SQL запрос 1
SELECT "middle_company"."id",
"middle_company"."name"
FROM "middle_company"
LIMIT 2
Время выполнения: 0.001 s
--------------------
SQL запрос 2
SELECT ("middle_company_addresses"."company_id") AS "_prefetch_related_val_company_id",
"middle_address"."id",
"middle_address"."type",
"middle_address"."city_id"
FROM "middle_address"
INNER JOIN "middle_company_addresses" ON ("middle_address"."id" = "middle_company_addresses"."address_id")
WHERE "middle_company_addresses"."company_id" IN (1,
2)
Время выполнения: 0.001 s
--------------------
SQL запрос 3
SELECT "middle_address"."id",
"middle_address"."type",
"middle_address"."city_id"
FROM "middle_address"
INNER JOIN "middle_company_addresses" ON ("middle_address"."id" = "middle_company_addresses"."address_id")
WHERE ("middle_company_addresses"."company_id" = 2
AND "middle_address"."type" = 0)
LIMIT 21
Время выполнения: 0.001 s
--------------------
SQL запрос 4
SELECT "middle_city"."id",
"middle_city"."name",
"middle_city"."region_id"
FROM "middle_city"
WHERE "middle_city"."id" = 1
LIMIT 21
Время выполнения: 0.000 sТеперь давайте попробуем минимизировать количество запросов в примере, где мы получили 131 запрос.
def test_prefetch(count):
# Получаем первую запись из таблицы компаний
companies = Company.objects.prefetch_related(
# При использовании класса Prefetch, мы можем
# указывать конкретный QuerySet, в котором также
# можем использовать select_related и prefetch_related
Prefetch( # Для этого будет выполнен второй SQL запрос
# Дополнительно запрашиваем адреса для компаний
"addresses",
# Указываем QuerySet для адресов
Address.objects.select_related(
# Присоединяем таблицы из вложенных связей
"city",
"city__region",
"city__region__country",
).prefetch_related(
# Дополнительно запрашиваем части адресов для компаний
Prefetch( # Для этого будет выполнен третий SQL запрос
"parts",
# Также мы можем фильтровать вложенные QuerySet
AddressPart.objects.order_by("-position")
)
)
)
)
# В этот раз не важно по какому количеству компаний мы будем
# итерироваться, мы всегда получим одинаковое количество запросов
for company in companies[:count]: # Запросы 1, 2 и 3 будут выполнены здесь
# Итерируемся по адресам
for address in company.addresses.all():
city = address.city # Получаем город
region = city.region # Получаем регион
country = region.country # Получаем страну
# Дополнительно сортировать части адресов не придется
# так как мы уже сделали это в Prefetch
address_parts = list(address.parts.all())
with CheckQueries():
test_prefetch(2)
На этот раз совершается всего 3 запроса
SQL запрос 1 (Основной)
SELECT "middle_company"."id",
"middle_company"."name"
FROM "middle_company"
LIMIT 2
Время выполнения: 0.000 s
--------------------
SQL запрос 2 (Prefetch для основного запроса)
SELECT ("middle_company_addresses"."company_id") AS "_prefetch_related_val_company_id",
"middle_address"."id",
"middle_address"."type",
"middle_address"."city_id",
"middle_city"."id",
"middle_city"."name",
"middle_city"."region_id",
"middle_region"."id",
"middle_region"."name",
"middle_region"."country_id",
"middle_country"."id",
"middle_country"."name"
FROM "middle_address"
INNER JOIN "middle_company_addresses" ON ("middle_address"."id" = "middle_company_addresses"."address_id")
INNER JOIN "middle_city" ON ("middle_address"."city_id" = "middle_city"."id")
INNER JOIN "middle_region" ON ("middle_city"."region_id" = "middle_region"."id")
INNER JOIN "middle_country" ON ("middle_region"."country_id" = "middle_country"."id")
WHERE "middle_company_addresses"."company_id" IN (1, 2)
Время выполнения: 0.001 s
--------------------
SQL запрос 3 (Prefetch частей адреса вызванный для модели Address)
SELECT ("middle_address_parts"."address_id") AS "_prefetch_related_val_address_id",
"middle_addresspart"."id",
"middle_addresspart"."position",
"middle_addresspart"."key",
"middle_addresspart"."value"
FROM "middle_addresspart"
INNER JOIN "middle_address_parts" ON ("middle_addresspart"."id" = "middle_address_parts"."addresspart_id")
WHERE "middle_address_parts"."address_id" IN (1, 2, 3, 4, 5, 6)
ORDER BY "middle_addresspart"."position" DESC
Время выполнения: 0.000 sДавайте проверим, что при увеличении количества компаний, для которых нужно запросить адреса, количество запросов не изменится.
with CheckQueries(count_only=True):
test_prefetch(2)
# Количество запросов: 3
with CheckQueries(count_only=True):
test_prefetch(10000)
# Количество запросов: 3
# Получаем ожидаемое поведениеДумаю, вы согласитесь, что описание всех аргументов prefetch_related каждый раз для таких внушительных запросов выглядит достаточно громоздким.
Вы можете попробовать написать алгоритм, который будет рекурсивно проходиться, например, по Pydantic модели, которую нужно заполнить данными и по django модели из которой эти данные нужно получить. В результате сопоставления полей и проверки их типов, вы можете получить точный список аргументов, которые нужно передать как в select_related так и в prefetch_related, что значительно ускорит как разработку, так и базовый уровень оптимизации запросов. К сожалению, я не могу предоставить вам рабочий код такого решения по многим причинам, но, я вас уверяю, вы никогда не пожалеете, если потратите время на его написание.
Также, стоит отметить, еще 2 ключевые особенности использования prefetch_related, которые даже можно объединить. Во-первых, мы можем фильтровать QuerySet, в прочем как и вызывать любые его методы, которые возвращают QuerySet, а во-вторых мы можем записать результат выполнения дополнительного запроса в указанную нами переменную.
Далее будет, наверное, не лучший пример использования, но зато он достаточно наглядный.
companies = list(Company.objects.prefetch_related(
# Дополнительно запросим адреса
Prefetch(
"addresses",
# Запросим только фактические адреса
Address.objects.filter(type=AddressType.FACT),
# Запишем их в атрибуты экземпляров django модели
to_attr="fact_addresses"
),
# Еще раз дополнительно запросим адреса,
# но с другим фильтром
Prefetch(
"addresses",
# Запросим только юридические адреса
Address.objects.filter(type=AddressType.LEGAL),
# Запишем их в атрибуты экземпляров django модели
to_attr="legal_addresses"
),
)[:2])
# На этом этапе будет выполнено 3 запроса: основной,
# запрос фактических адресов и запрос юридических адресов
# Далее дополнительных SQL запросов не будет
company = companies[0]
# Присваивание значений новых атрибутов происходит
# уже средствами django, поэтому чего-то принципиально
# нового мы не увидим
# Также важно, что при таком использовании, вместо
# QuerySet мы получим сразу список, поэтому обращаемся
# со значениями соответствующе
fact_address = company.fact_addresses[0]
legal_address = company.legal_addresses[0]
# Выведем то, что мы получили для фактического и юридического
# адресов соответственно
print(AddressType(fact_address.type).label, fact_address)
print(AddressType(legal_address.type).label, legal_address)
# Фактический Address object (1)
# Юридический Address object (2)
# Мы получили ожидаемое поведениеПодводные камни с дублированием записей
Тут важно отметить, что, использование values и values_list в описанных выше случаях, будет более чем затруднено и может легко привести к не ожидаемому поведению. Как справиться со следующей проблемой, вам следует решить самостоятельно, но я обязан обратить на нее внимание.
Так как при использовании values и values_list, мы буквально отказываемся от использования экземпляров моделей django, вместе с этим django больше не заботится о том, чтобы группировать значения M2M и O2M связей и вы будете получать несколько записей с одним и тем же ID.
Конечно, дублирование записей может возникнуть и при использовании моделей django. Например, при сортировке, фильтрации или аннотировании, если эти операции совершаются по полям со связями O2M или M2M. Это поведение связано с особенностями JOIN операций реляционных баз данных.
Давайте попробуем получить id компании вместе со списком id адресов.
with CheckQueries():
pprint(list(Company.objects.filter(pk=1).values("id", "addresses__id")))
# [{'addresses__id': 1, 'id': 1},
# {'addresses__id': 2, 'id': 1},
# {'addresses__id': 3, 'id': 1}]
# Мы получили 3 записи для единственной записи таблицы Company.
# Это произошло, так как каждая запись в таблице Address была
# присоединена к записи в таблицы Company. Так как у компании
# было 3 адреса, мы и получили 3 отдельные записиМы получаем SQL запрос с использованием LEFT OUTER JOIN
SELECT "middle_company"."id" AS "id",
"middle_company_addresses"."address_id" AS "addresses__id"
FROM "middle_company"
LEFT OUTER JOIN "middle_company_addresses" ON ("middle_company"."id" = "middle_company_addresses"."company_id")
WHERE "middle_company"."id" = 1
Время выполнения: 0.000 sЧасто такое поведение бывает не ожидаемым, но вы всегда можете усугубить ситуацию.
На этот раз, давайте попробуем запросить еще и части адреса.
with CheckQueries():
pprint(list(
Company.objects
.filter(pk=1)
.order_by("addresses__id", "addresses__parts__position")
.values(
"id",
"addresses__id",
"addresses__parts__key",
"addresses__parts__value",
)
))Консольный вывод
[{'addresses__id': 1,
'addresses__parts__key': 'ул.',
'addresses__parts__value': 'urAVQxDYxsVxLSkDkUQl',
'id': 1},
{'addresses__id': 1,
'addresses__parts__key': 'д.',
'addresses__parts__value': 'ccPkAGylpvSxIZNbOIbW',
'id': 1},
{'addresses__id': 1,
'addresses__parts__key': 'к.',
'addresses__parts__value': 'emyuXPNEdmGhcnSyMdRc',
'id': 1},
{'addresses__id': 1,
'addresses__parts__key': 'кв',
'addresses__parts__value': 'oMKiAakyCoJJuYEdUkMd',
'id': 1},
{'addresses__id': 2,
'addresses__parts__key': 'ул',
'addresses__parts__value': 'vbSonsrMbVUfSxPHFGLr',
'id': 1},
{'addresses__id': 2,
'addresses__parts__key': 'д',
'addresses__parts__value': 'wQrlJipLDRvfpQxKfgbq',
'id': 1},
{'addresses__id': 2,
'addresses__parts__key': 'к',
'addresses__parts__value': 'VVsKLDTZMgkHrTUOTVKE',
'id': 1},
{'addresses__id': 2,
'addresses__parts__key': 'кв',
'addresses__parts__value': 'vRmlBsmedRiaEqEEwhvT',
'id': 1},
{'addresses__id': 3,
'addresses__parts__key': 'ул',
'addresses__parts__value': 'rEuDkVxAGPXOSTkcrRkr',
'id': 1},
{'addresses__id': 3,
'addresses__parts__key': 'д',
'addresses__parts__value': 'CfiFguBdijkaBYAoYDmi',
'id': 1},
{'addresses__id': 3,
'addresses__parts__key': 'к',
'addresses__parts__value': 'oqrBjrfPUerODqZyHjLp',
'id': 1},
{'addresses__id': 3,
'addresses__parts__key': 'кв',
'addresses__parts__value': 'gPBDAEGAFJGGdOKeeToS',
'id': 1}]На этот раз мы получил целых 12 записей, так как мы буквально присоединили еще одну таблицу. Каждая строка из таблицы AddressPart была присоединена к соответствующей строке таблицы Address. И так как у адреса было 4 части, количество строк и умножилось на соответствующее значение.
А вот SQL, который привел к такому результату
SELECT "middle_company"."id" AS "id",
"middle_company_addresses"."address_id" AS "addresses__id",
"middle_addresspart"."key" AS "addresses__parts__key",
"middle_addresspart"."value" AS "addresses__parts__value"
FROM "middle_company"
LEFT OUTER JOIN "middle_company_addresses" ON ("middle_company"."id" = "middle_company_addresses"."company_id")
LEFT OUTER JOIN "middle_address" ON ("middle_company_addresses"."address_id" = "middle_address"."id")
LEFT OUTER JOIN "middle_address_parts" ON ("middle_address"."id" = "middle_address_parts"."address_id")
LEFT OUTER JOIN "middle_addresspart" ON ("middle_address_parts"."addresspart_id" = "middle_addresspart"."id")
WHERE "middle_company"."id" = 1
ORDER BY 2 ASC,
"middle_addresspart"."position" ASC
Время выполнения: 0.001 sОсобенности запроса на основе данных другой таблицы
Давайте рассмотрим еще одну не очень очевидную особенность.
Зачастую нам нужно найти объекты на основании какого-либо списка ID объектов, связанных с ними. Это действие можно выполнить по-разному.
Давайте посмотрим, как это можно сделать
def get_companies_by_addresses_ids(ids: QuerySet | list[int]) -> QuerySet:
# Функция возвращает QuerySet компаний, которым
# соответствуют переданные id адресов
return (
Company.objects
.filter(addresses__id__in=ids)
# Из-за особенностей реализации реляционных баз данных
# при фильтрации по m2m связи, нам нужно исключить дубли.
# Исключаем дубли по полю ID (возможно не во всех базах данных)
.distinct("id")
)
# Давайте для начала передадим небольшой список адресов
with CheckQueries():
get_companies_by_addresses_ids([1, 2, 3]).count()Получаем следующий SQL
SELECT COUNT(*)
FROM
(SELECT DISTINCT ON ("middle_company"."id") "middle_company"."id" AS "col1",
"middle_company"."name" AS "col2"
FROM "middle_company"
INNER JOIN "middle_company_addresses" ON ("middle_company"."id" = "middle_company_addresses"."company_id")
WHERE "middle_company_addresses"."address_id" IN (1, 2, 3)) subquery
Время выполнения: 0.001 sВ целом, пока нет никаких проблем. Давайте теперь попробуем передать список из большего количества ID.
addresses_ids = list(range(1, 30001))
with CheckQueries():
get_companies_by_addresses_ids(addresses_ids).count()Теперь мы получаем почти тот же самый SQL, вот только сам запрос содержит все значения ID. В данном случае их всего 30 тысяч, но разница уже ощутимая.
SELECT COUNT(*)
FROM (... WHERE "middle_company_addresses"."address_id" IN (1, ..., 30000)) subquery
Время выполнения: 0.046 sА сейчас давайте передадим вместо физического списка ID другой QuerySet, что позволит передать всю работу на сторону базы данных и исключить издержки на формирование, передачу и чтение запроса.
addresses_ids = Address.objects.values_list("id", flat=True)
with CheckQueries():
get_companies_by_addresses_ids(addresses_ids).count()В данном случае получаем другой SQL
SELECT COUNT(*)
FROM
(SELECT DISTINCT ON ("middle_company"."id") "middle_company"."id" AS "col1",
"middle_company"."name" AS "col2"
FROM "middle_company"
INNER JOIN "middle_company_addresses" ON ("middle_company"."id" = "middle_company_addresses"."company_id")
WHERE "middle_company_addresses"."address_id" IN
(SELECT U0."id" AS "id"
FROM "middle_address" U0)) subquery
Время выполнения: 0.006 sТакого рода запросы на небольшой выборке могут выполняться дольше, однако, при увеличении количества запрашиваемых ID показывают более чем значительный прирост в скорости выполнения.
Дополнительно упомяну кейс оптимизации, который также касается исключительно методов QuerySet. У вас почти наверняка когда-нибудь будет необходимость фильтровать или сортировать записи в таблице на основании значения которого нет в таблице, но которое можно рассчитать в момент выполнения запроса. Обычно вы будете использовать методannotate, добавите в нем необходимые аннотации и уже их будете использовать в вызове метода filter.
Так вот, если вас не интересует результат полученной аннотации (он нужен исключительно для фильтрации или сортировки) эффективнее будет использовать метод alias вместо метода annotate. Вот тут можно подробнее почитать об этом методе.
Особенности создания и обновления записей
Оптимизация запроса SELECT это, конечно, важно, но не стоит забывать и о том, что оптимизации стоит не только он.
Для начала рассмотрим методы get_or_create и update_or_create. Чаще всего эти методы используют для удобства, но также они все-таки обладают некоторым эффектом оптимизации, хотя он и будет достаточно специфическим.
Использование get_or_create (получение или создание записи)
Данный метод возвращает запись из базы данных, если она уже в ней присутствует, в противном случае, создает новую запись и возвращает ее.
# Хороший сценарий (запись присутствует)
with CheckQueries():
Country.objects.get_or_create(id=1)Тут мы просто получаем запись из базы
SQL запрос 1
SELECT "middle_country"."id",
"middle_country"."name"
FROM "middle_country"
WHERE "middle_country"."id" = 1
LIMIT 21
Время выполнения: 0.002 s# Плохой сценарий (запись отсутствует)
with CheckQueries():
Country.objects.get_or_create(id=300)Тут все сложнее
SQL запрос 1
SELECT "middle_country"."id",
"middle_country"."name"
FROM "middle_country"
WHERE "middle_country"."id" = 300
LIMIT 21
Время выполнения: 0.000 s
--------------------
SQL запрос 2
BEGIN
Время выполнения: 0.000 s
--------------------
SQL запрос 3
INSERT INTO "middle_country" ("id", "name")
VALUES (300, '') RETURNING "middle_country"."id"
Время выполнения: 0.006 s
--------------------
SQL запрос 4
COMMIT
Время выполнения: 0.003 sПытаемся найти запись (но она отсутствует)
Начинается блок транзакции для вставки новой записи
Осуществляется вставка новой записи
Фиксируем транзакцию
Итого, мы получаем лишний запрос в виде первоначального поиска. А теперь давайте посмотрим на ситуацию с другой стороны.
Представим, что у нас есть миллионы записей в какой-то таблице, записи постоянно пополняются и не удаляются вместе с записями в других таблицах базы данных. Тем не менее в других таблицах есть ссылки на целевую, и, при этом, в целевой таблице могут быть дубли.
В таком случае использование get_or_create поможет в долгосрочной перспективе уменьшить скорость разрастания самой таблицы, что уменьшит занимаемое место на диске и косвенно ускорит поиск.
Использование update_or_create (обновление или создание записи)
Теперь давайте рассмотрим метод update_or_create
Данный метод обновляет запись в базе данных, если она уже в ней присутствует, в противном случае, создает новую запись возвращает ее.
# Запись присутствует
with CheckQueries():
# Значения из defaults будут использованы для обновления или
# создания, остальные аргументы будут участвовать в фильтрации,
# если только они не описаны в сигнатуре функции
Address.objects.update_or_create(id=1, defaults={"city_id": 1})В случае обновления происходит следующее
SQL запрос 1
BEGIN
Время выполнения: 0.000 s
--------------------
SQL запрос 2
SELECT "middle_address"."id",
"middle_address"."type",
"middle_address"."city_id"
FROM "middle_address"
WHERE "middle_address"."id" = 1
LIMIT 21
FOR
UPDATE
Время выполнения: 0.002 s
--------------------
SQL запрос 3
UPDATE "middle_address"
SET "city_id" = 1
WHERE "middle_address"."id" = 1
Время выполнения: 0.000 s
--------------------
SQL запрос 4
COMMIT
Время выполнения: 0.002 sОткрываем транзакцию для обновления
Выбираем данные для обновления
Производим обновление значение поля
city_idФиксируем транзакцию
А теперь давайте посмотрим, что будет если запись в таблице отсутствует.
# Запись отсутствует
with CheckQueries():
Address.objects.update_or_create(
id=30001,
defaults={"city_id": 1, "type": AddressType.FACT}
)На этот раз, все опять сложнее
SQL запрос 1
BEGIN
Время выполнения: 0.000 s
--------------------
SQL запрос 2
SELECT "middle_address"."id",
"middle_address"."type",
"middle_address"."city_id"
FROM "middle_address"
WHERE "middle_address"."id" = 30001
LIMIT 21
FOR
UPDATE
Время выполнения: 0.006 s
--------------------
SQL запрос 3
SAVEPOINT "s136764922921216_x3"
Время выполнения: 0.000 s
--------------------
SQL запрос 4
INSERT INTO "middle_address" ("id", "type", "city_id")
VALUES (30001, 0, 1) RETURNING "middle_address"."id"
Время выполнения: 0.000 s
--------------------
SQL запрос 5
RELEASE SAVEPOINT "s136764922921216_x3"
Время выполнения: 0.000 s
--------------------
SQL запрос 6
COMMIT
Время выполнения: 0.002 sОткрываем транзакцию для обновления
Выбираем данные для обновления (запись отсутствует)
До вставки устанавливаем новую точку сохранения
Осуществляем вставку
Освобождаем точку сохранения
Фиксируем транзакцию
Помимо этого, в случае возникновения провала проверки возможности вставки, сюда же добавится ROLLBACK TO SAVEPOINT, затем django по каким-то своим причинам продублирует запрос 2 и завершит все ROLLBACK
В целом, выглядит это все достаточно громоздко. Как и в случае с get_or_create, данный метод позволяет не создавать новые записи в базе данных, однако его эффективность стоит под большим вопросом, во-первых, потому что он может генерировать куда больше запросов в базу данных, чем хотелось бы, а во-вторых вставка чаще всего происходит быстрее обновления.
Обновление может быть быстрее в случае, если мы изменяем небольшое количество полей (относительно количества столбцов таблицы), а также если мы имеем значительное количество индексов, которым также нужно время на построение.
Поэтому, данный метод стоит использовать с осторожностью.
Использование update (обновление всех найденных записей)
Куда более ощутимый эффект оптимизации возникает при использовании метода update
Вы можете сразу обновить все записи, которые попадают под условия фильтрации, в том числе значения можно обновить на основании вычисленных во время запроса.
with CheckQueries():
# Добавим символ "." к ключам первых трех записей в таблице AddressPart
(
AddressPart.objects
.filter(id__in=[1, 2, 3])
.update(key=Concat(F("key"), Value(".")))
)
Тут все однозначно, удобно и просто - один запрос на обновление
UPDATE "middle_addresspart"
SET "key" = (COALESCE("middle_addresspart"."key", '') || COALESCE('.', ''))
WHERE "middle_addresspart"."id" IN (1, 2, 3)
Время выполнения: 0.008 sНо в данном случае есть ряд ограничений, о которых нужно помнить.
Обновлять можно столбцы только основной таблицы (фильтрация этим не ограничена)
Данный метод не будет работать с агрегационными аннотациями и рядом других методов.
Так как обновление происходит на уровне SQL, метод
saveне будет вызван, а также не будут вызваны сигналыpre_saveиpost_save.
Хочу заострить особое внимание на пункте 3. Если у вас есть какая-то логика в методе save или в сигналах pre_save или post_save, то, скорее всего, использовать данный метод вам не стоит.
Использование bulk методов
В завершении главы, наконец, рассмотрим bulk методы, а именно bulk_create и bulk_update.
Данные 2 метода также работают в обход метода save модели django, поэтому ни код из метода save ни сигналы pre_save и post_save также не будут вызваны.
Использование bulk_create (вставка списка записей)
Метод bulk_create реализует эффективную вставку списка объектов в базу данных.
with CheckQueries():
countries = [
Country(name="Новая страна 1"),
Country(name="Новая страна 2"),
]
countries = Country.objects.bulk_create(countries)Здесь мы просто в рамках транзакции вставляем новые записи в таблицу
SQL запрос 1
BEGIN
Время выполнения: 0.000 s
--------------------
SQL запрос 2
INSERT INTO "middle_country" ("name")
SELECT *
FROM UNNEST((ARRAY['Новая страна 1', 'Новая страна 2'])::varchar(128)[]) RETURNING "middle_country"."id"
Время выполнения: 0.007 s
--------------------
SQL запрос 3
COMMIT
Время выполнения: 0.001 sИспользование bulk_update (обновление списка записей)
На данном этапе countries из прошлого примера является массивом с двумя экземплярами django модели Country, у которых уже есть ID.
Давайте теперь на тех же записях попробуем обновить значения полей с помощью bulk_update. Для вызова данного метода, всегда необходимо указывать набор полей, которые нужно будет обновить.
with CheckQueries():
for country in countries:
# Просто добавим суффикс к имени страны
country.name += ": Обновлен"
country.save()
countries = Country.objects.bulk_update(countries, fields=["name"])Получаем следующий SQL
SQL запрос 1
BEGIN
Время выполнения: 0.000 s
--------------------
SQL запрос 2
UPDATE "middle_country"
SET "name" = (CASE
WHEN ("middle_country"."id" = 21) THEN 'Новый город 1: Обновлен'
WHEN ("middle_country"."id" = 22) THEN 'Новый город 2: Обновлен'
ELSE NULL
END)::varchar(128)
WHERE "middle_country"."id" IN (21, 22)
Время выполнения: 0.002 s
--------------------
SQL запрос 3
COMMIT
Время выполнения: 0.007 sВ целом, тут также никакого криминала. В зависимости от ID, мы выбираем новое значение в колонке для каждой записи.
НО, есть нюанс. При обновлении нескольких полей, для всех записей будут обновлены все поля, вне зависимости от того, реально ли они изменились. Вот пример такого случая.
addresses = list(Address.objects.filter(id__in=[1, 2]))
with CheckQueries():
for address in addresses:
address.type = 1
address.city_id = 2
break
addresses = Address.objects.bulk_update(
addresses, fields=["type", "city_id"]
)И вот как изменится SQL
SQL запрос 1
BEGIN
Время выполнения: 0.000 s
--------------------
SQL запрос 2
UPDATE "middle_address"
SET "type" = (CASE
WHEN ("middle_address"."id" = 1) THEN 1
WHEN ("middle_address"."id" = 2) THEN 1
ELSE NULL
END)::smallint,
"city_id" = (CASE
WHEN ("middle_address"."id" = 1) THEN 2
WHEN ("middle_address"."id" = 2) THEN 2
ELSE NULL
END)::bigint
WHERE "middle_address"."id" IN (1,
2)
Время выполнения: 0.001 s
--------------------
SQL запрос 3
COMMIT
Время выполнения: 0.004 sГлядя на запрос 2, напрашивается вывод, что физический размер запроса имеет тенденцию разрастаться как при увеличении количества полей, так и при увеличении количества обновляемых записей.
Для того, чтобы решить проблему километровых запросов, используйте параметр batch_size, который присутствует в обоих методах. Чаще всего, оптимальным будет указать значение в районе 2 000 - 10 000 записей за раз (никто вам не запрещает указать больше или меньше).
Итоги главы
Зафиналим данную главу следующими выводами:
Если вам нужен доступ к данным из M2M или O2M связей, используйте
prefetch_relatedНе забывайте, что, даже при использовании
prefetch_related, вам все еще может понадобитьсяselect_relatedкак для основной таблицы, так и для запрошенных в методеprefetch_related. Для этого вы можете использовать классPrefetchБудьте осторожны при использовании
valuesиvalues_list, в которых запрашиваются данные из M2M или O2M связей.Вы получите разный SQL, если передадите одним из параметров фильтрации список или другой
QuerySetНе создавайте без необходимости новые записи, иногда стоит использовать
get_or_createиupdate_or_createВы можете обновлять множество записей разом с помощью
update, но методsaveи сигналы не будут вызваныВы можете вставлять в таблицу множество записей разом с помощью
bulk_create, но методsaveи сигналы не будут вызваныВы можете обновлять множество записей в таблице разом с помощью
bulk_update, но методsaveи сигналы не будут вызваны
Продвинутая оптимизация
На этот раз, рассмотрим более фундаментальные вещи, такие как структуры данных и индексы. Так как я использую PostgreSQL, пример будет с оглядкой именно на нее.
Для данной главы было добавлено приложение advanced.
Генерацию тестовых данных я также скрою под спойлером. Определение моделей, на этот раз, будет отображено, так как в нем присутствует ряд важных комментариев и примеров.
Типы данных
Начнем с типов данных. Так как базы данных выделяют разное количество памяти для хранения каждого типа данных, очень важно использовать именно тот тип, который вам действительно необходим.
Вы можете посмотреть маппинг полей django и типов данных в вашей базе по следующему пути django/db/backends/<your_backend>/base.py
# Таблица с основными типами данных
class ModelWithBaseTypes(models.Model):
# Значения от -32768 до 32767
# Данный тип данных стоит использовать для хранения Enum c числовыми
# значениями или если вам просто достаточен указанный диапозон значений
small_integer = models.SmallIntegerField() # smallint (2 bytes)
# Значения от -2147483648 до 2147483647
# Данный тип данных рекомендуется использовать в большинстве случаев
integer = models.IntegerField() # integer (4 bytes)
# Значения от -9223372036854775808 до 9223372036854775807
# Данный тип данных рекомендуется использовать для первичных ключей таблиц,
# в которых может быть огромное количество записей, и в ряде исключительных
# случаев, например, для счетчика просмотров YouTube
big_integer = models.BigIntegerField() # bigint (8 bytes)
# Следующие 3 поля аналогичны, но хранят значения от 0
# smallint (2 bytes)
positive_small_integer = models.PositiveSmallIntegerField()
# integer (4 bytes)
positive_integer = models.PositiveIntegerField()
# bigint (8 bytes)
positive_big_integer = models.PositiveSmallIntegerField()
# Текстовое поле с ограниченной длиной.
# Данный тип данных используется для хранения Enum со строковыми значениями
# А также для всех строковых полей, максимальную длину которых мы можем
# спрогнозировать
# varchar(max_length) (1-4 bytes на символ + 1-4 bytes на длину строки)
small_text = models.CharField(max_length=255)
# Текстовое поле без ограничения по длине
# Тут будьте внимательны, некоторые базы данных все-таки имеют ограничение
# text (1-4 bytes на символ + 1-4 bytes на длину строки)
big_text = models.TextField()
# Когда вам нужно поле с булевым значением, думаю всегда ясно.
# Тем не менее, я постоянно вижу строковые или числовые Enum
# всего с 2 значениями, которые вполне можно переделать во флаг
# Также, значением поля может быть и null, что позволяет вам
# иметь сразу 3 опции и часть Enum с тремя значениями также могут
# быть переосмыслены в nullable BooleanField
bool_field = models.BooleanField() # bool (1 byte)
# Если вам нужна именно дата или дата и время, вам в любом случае
# придется использовать данные поля
date_field = models.DateField() # date (4 bytes)
# Выделяемый размеры как с таймзоной, так и без одинаковы
# timestamp with time zone (8 bytes)
datetime_field = models.DateTimeField()
# Тип данных, который хранит только время.
# На практике он может вам понадобиться не так часто,
# но все же бывает удобен, когда вы, например, храните
# время открытия или закрытия какого либо объекта.
# Также у вас есть возможность добавить таймзону,
# но тогда на значение будет выделено 12 байт, поэтому
# я рекомендую определять таймзону как-то иначе
time_field = models.TimeField() # time (8 bytes)
# Значения продолжительности от -178000000 лет до 178000000 лет
# с точностью до микросекунды
# Данный тип поля достаточно удобен в использовании, но он занимает
# относительно много места, поэтому, если вам нужна продолжительность
# в пределах дней, то в зависимости от необходимой точности, вам будет
# дешевле использовать числовые типы данных и хранить в них секунды или
# микросекунды
duration_field = models.DurationField() # interval (16 bytes)
# Значения до 131072 знаков до запятой и до 16383 знаков после запятой
# При использовании Decimal важно, во-первых, прогнозировать
# максимальное значение, которое вы теоретически хотите мочь
# записать в базу. Во-вторых, определиться с порядком допустимой
# погрешности математических операций, которые вы будете проводить
# над значениями. Чем меньше будут оба значения, тем меньше будет
# выделено места для хранения значений
# numeric(max_digits, decimal_places) (variable)
decimal_field = models.DecimalField(max_digits=15, decimal_places=2)
# Неточный числовой тип, имеющий переменную точностью
# до 15 десятичных знаков
# Если вы планируете проводить какие-либо расчеты, вам почти со 100%
# вероятностью необходимо использовать DecimalField
float_field = models.FloatField() # double precision (8 bytes)
В целом, вам с большой долей вероятности могут понадобиться еще и следующие специфические типы полей, в django. Думаю, вы поймете, что они вам нужны именно тогда, когда они вам действительно понадобятся.
Хочу предостеречь только на счет JSONField и ArrayField. Не стоит писать свою структуру таблицы базы данных с помощью JSONField, и не нужно хранить первичные ключи в ArrayField. Не берусь утверждать, что этого никогда нельзя делать, но данные решения являются как минимум сомнительными, и вы точно должны знать что именно вы делаете, для чего вы это делаете и нельзя ли сделать иначе.
Конечно же, PostgreSQL предоставляет множество других полей, но, если они вам понадобятся, скорее всего вы и так знаете, чего вы хотите.
В любом случае, оставлю ссылки на документацию типов данных:
Введение в индексы
Теперь давайте поближе познакомимся с индексами баз данных.
Я не буду здесь подробно рассказывать о том, что вообще такое индексы, как именно они работают, какие бывают алгоритмы поиска и так далее, так как это материал, уходящий далеко за рамки текущей статьи.
Я обзорно покажу, какие вообще индексы поддерживаются в django по умолчанию, в каких случаях их стоит применять и постараюсь показать, какую пользу и вред они могут принести.
Начнем с предостережений.
Во-первых, вы должны знать, что индексы занимают место на диске, причем некоторые из индексов могут занимать вполне себе сопоставимое с размером таблицы место.
Во-вторых, индексы призваны ускорить поиск нужных вам записей, но они делают обратное для вставки и обновления данных. Чем больше индексов в вашей таблице, тем больше будет занимать создание новых записей и изменение старых, так как помимо изменения таблицы, необходимо проводить изменения и в самих индексах.
Чаще всего, чем больше уникальных значений в колонке той или иной таблицы, тем эффективнее на ней будет показывать себя индекс. Иными словами, на колонки с булевыми значениями или Enums, наличие индекса будет неоправданным.
Поэтому, вам не стоит вешать индексы бездумно.
Также, вы должны знать, что django будет автоматически создавать индексы для полей, реализующих связи с другими таблицами, а также в случае указания уникальности поля или группы полей.
Виды индексов.
Но давайте для начала поговорим о том, какие индексы будут созданы по-умолчанию, а какие можно указать целенаправленно.
Стандартно при указании db_index=True или unique=True в поле модели, django автоматически будет использован B-Tree индекс (бинарное дерево).
Если вы используете PostgreSQL, у вам будет возможность использовать
один из следующих индексов:
django.contrib.postgres.indexes.BTreeIndex (B-Tree)
Документация django, документация PostgreSQL
Бинарное дерево работает в условиях равенства, проверках диапазонов, в целом планировщик запроса может задействовать данный индекс, если индексируемый столбец участвует в сравнении с одним из следующих операторов <, <=, =, >=, >.
django.contrib.postgres.indexes.HashIndex (HASH)
Документация django, документация PostgreSQL
Хэш индекс хранит 32 битный хэш, полученный из значения индексированного столбца, поэтому данный тип индекса будет использован только в сравнение с оператором =, но тут важно, что скорость может быть намного выше чем при использовании других индексов.
django.contrib.postgres.indexes.BrinIndex (BRIN Block Range Index)
Документация django, документация PostgreSQL
Данный индекс является неточным и хранит обобщенные сведения о значениях, находящихся в физически последовательно расположенных блоках таблицы. Наиболее эффективным такой индекс будет для колонок с временными значениями, в случае, если данные значения физически расположены по порядку (например, поле со временем создания записи). Для типов данных, имеющих линейный порядок сортировки, записям в индексе соответствуют минимальные и максимальные значения данных в столбце для каждой зоны блоков. Данный индекс поддерживает запросы со следующими операторами <, <=, =, >=, >.
django.contrib.postgres.indexes.GinIndex (GIN Generalized Inverted Index)
Документация django, документация PostgreSQL
Данный индекс куда сложнее по своей природе, поэтому я оставлю вам ссылку на статью, где подробнее рассказано об его использовании. Если кратко, то его используют для оптимизации полнотекстового поиска, когда вы используете, SearchVector и ищете в значении поля сразу несколько слов в разных местах или фразу. Также данный индекс можно использовать с ArrayField и JSONField.
django.contrib.postgres.indexes.GistIndex (GiST Generalized Search Trees)
Документация django, документация PostgreSQL
GiST используется при индексации полигонов, диапазонов, точек, поддерживает поиск ближайших соседей и полнотекстовый поиск. Обобщая, мы можем сказать, что он используется в основном для эффективного получения сложных типов данных. Вот статья на тему.
django.contrib.postgres.indexes.SpGistIndex (SP-GiST space partitioning generalized search trees)
Документация django, документация PostgreSQL
Наиболее эффективен, для неперекрывающихся геометрий и ускоряет поиск пространственно однородных распределений благодаря пространственному разбиению. На моей практике не было опыта использования данного индекса, поэтому, оставлю ссылку на статью, про него.
django.contrib.postgres.indexes.BloomIndex (Индекс на основе фильтров Блума) Документация django, документация PostgreSQL
Данный индекс является неточным. Используется для проверки того, является ли элемент членом набора. Наиболее полезен, когда таблица имеет много атрибутов, а запросы проверяют произвольные их комбинации. B-Tree быстрее индекса Блума, но B-Tree индексов на таблицу нужно много, а индекс Блума только один. Также из недостатков, индекс блума поддерживает только оператор равенства.
Создание индексов
Теперь давайте попробуем создать индексы для полей новых таблиц.
Начнем с описания таблицы, на которой будем проверять преимущества и недостатки индекса Bloom в сравнении с индексом B-Tree и отсутствием индекса.
from django.contrib.postgres.indexes import (
GinIndex, BrinIndex, BloomIndex,
BTreeIndex, GistIndex, HashIndex
)
# В данной таблице будем сравнивать индексы Bloom и B-Tree
class ModelWithBloomIndex(models.Model):
# Проверим работу без индекса
char_field_1 = models.CharField(max_length=32)
char_field_2 = models.CharField(max_length=32)
integer_field_3 = models.IntegerField()
integer_field_4 = models.IntegerField()
# Проверим работу bloom индекса
char_field_1_bloom = models.CharField(max_length=32)
char_field_2_bloom = models.CharField(max_length=32)
integer_field_3_bloom = models.IntegerField()
integer_field_4_bloom = models.IntegerField()
# Сравним с работой btree на группе полей
char_field_1_btree = models.CharField(max_length=32)
char_field_2_btree = models.CharField(max_length=32)
integer_field_3_btree = models.IntegerField()
integer_field_4_btree = models.IntegerField()
class Meta:
indexes = [
# Добавим Bloom индекс для группы полей
BloomIndex(
name="advanced_multi_field_bloom_idx",
fields=[
"char_field_1_bloom",
"char_field_2_bloom",
"integer_field_3_bloom",
"integer_field_4_bloom"
],
),
# Добавляем стандартный индекс для каждого поля
BTreeIndex(
name="advanced_multi_field_1_btree_idx",
fields=["char_field_1_btree"]
),
BTreeIndex(
name="advanced_multi_field_2_btree_idx",
fields=["char_field_2_btree"]
),
BTreeIndex(
name="advanced_multi_field_3_btree_idx",
fields=["integer_field_3_btree"]
),
BTreeIndex(
name="advanced_multi_field_4_btree_idx",
fields=["integer_field_4_btree"]
),
]
Далее заведем таблицу, на которой проверим автоматическое создание индексов.
# Таблица для проверки автоматического создания индексов
class ModelWithUnique(models.Model):
# Проверим, индекс для уникальности поля
unique_field = models.CharField(unique=True, max_length=255)
# Проверим индекс по умолчанию
default_index_field = models.CharField(db_index=True, max_length=255)
# Проверим, индекс для уникальности комбинации полей
unique_together_field_1 = models.CharField(max_length=255)
unique_together_field_2 = models.CharField(max_length=255)
class Meta:
# Определяем уникальность комбинации полей
constraints = [
models.UniqueConstraint(
name="advanced_unique_together_field_1_2",
fields=["unique_together_field_1", "unique_together_field_2"]
),
]Последней опишем таблицу для сравнения и проверки работы всех остальных, упомянутых выше индексов (за исключением SP-GiST).
class ModelWithIndexes(models.Model):
# Добавим связи с другими таблицами
m2m_field = models.ManyToManyField(ModelWithUnique)
fk_field = models.ForeignKey(ModelWithBloomIndex, on_delete=models.CASCADE)
# Проверим фильтрацию по датам
created_at = models.DateTimeField()
created_at_with_brin_index = models.DateTimeField()
created_at_with_btree_index = models.DateTimeField()
# Проверим полнотекстовый поиск
big_text = models.TextField()
big_text_with_gin = models.TextField()
big_text_with_gist = models.TextField()
# Проверим точное совпадение
small_text = models.CharField(max_length=255)
small_text_btree_index = models.CharField(max_length=255)
small_text_with_hash_index = models.CharField(max_length=255)
# Проверим составной индекс
field_without_composite_index_1 = models.CharField(max_length=255)
field_without_composite_index_2 = models.CharField(max_length=255)
field_with_composite_index_1 = models.CharField(max_length=255)
field_with_composite_index_2 = models.CharField(max_length=255)
class Meta:
# Определяем набор индексов
indexes = [
# Добавляем индексы для дат
BrinIndex(
name="advanced_created_at_brin",
fields=["created_at_with_brin_index"]
),
BTreeIndex(
name="advanced_created_at_btree",
fields=["created_at_with_btree_index"]
),
# Добавляем индексирование векторов на текстовые поля
GinIndex(
SearchVector("big_text_with_gin", config="russian"),
name="advanced_big_text_gin"
),
GistIndex(
SearchVector("big_text_with_gist", config="russian"),
name="advanced_big_text_gist"
),
# Добавляем индекс для точного поиска
HashIndex(
name="advanced_small_text_hash",
fields=["small_text_with_hash_index"]
),
# Добавляем стандартный индекс на текстовое поле
BTreeIndex(
name="advanced_small_text_btree",
fields=["small_text_btree_index"]
),
# Добавляем составной индекс
BTreeIndex(
name="advanced_combined",
fields=[
"field_with_composite_index_1",
"field_with_composite_index_2"
]
),
]Для работы Bloom индекса, вам нужно будет добавить BloomExtension() на первое место в атрибуте operations созданной миграции.
Также оставляю документацию по явному определению индексов.
Создание тестовых данных
import uuid
from datetime import timedelta
from django.utils import timezone
from factory.fuzzy import FuzzyText, FuzzyInteger
from advanced.models import (
ModelWithBloomIndex,
ModelWithUnique,
ModelWithIndexes
)
# Метод для создания записей для ModelWithBloomIndex
def create_model_with_bloom_index(count):
# Определяем генераторы рандомных значений
text_15 = FuzzyText(length=15)
integer = FuzzyInteger(low=0, high=1000000)
instances = []
for _ in range(count):
# Просто заполняем поля рандомными значениями
instances.append(ModelWithBloomIndex(
char_field_1=text_15.fuzz(),
char_field_2=text_15.fuzz(),
integer_field_3=integer.fuzz(),
integer_field_4=integer.fuzz(),
char_field_1_bloom=text_15.fuzz(),
char_field_2_bloom=text_15.fuzz(),
integer_field_3_bloom=integer.fuzz(),
integer_field_4_bloom=integer.fuzz(),
char_field_1_btree=text_15.fuzz(),
char_field_2_btree=text_15.fuzz(),
integer_field_3_btree=integer.fuzz(),
integer_field_4_btree=integer.fuzz(),
))
# Создаем записи пачками по 10 000
ModelWithBloomIndex.objects.bulk_create(
instances, batch_size=10000
)
# Метод для создания записей для ModelWithUnique
def create_model_with_unique(count):
# Создаем генератор uuid
# P.S. в процессе понял, что они создаются достаточно быстро
# поэтому использую их для заполнения строк
uuids = (str(uuid.uuid4()) for _ in range(count * 4))
instances = []
for _ in range(count):
instances.append(ModelWithUnique(
unique_field=next(uuids),
default_index_field=next(uuids),
unique_together_field_1=next(uuids),
unique_together_field_2=next(uuids),
))
# Создаем записи пачками по 10 000
ModelWithUnique.objects.bulk_create(instances, batch_size=10000)
# Метод для создания записей для ModelWithIndexes
def create_model_with_indexes(count):
# Итератор для получения id записи связанной таблицы
fk_ids = iter(
ModelWithBloomIndex.objects
.values_list("id", flat=True)
)
# Генератор для дат
dates = (timezone.now() + timedelta(i) for i in range(count))
# Генератор для uuid
uuids = (str(uuid.uuid4()) for _ in range(count * 10))
instances = []
for _ in range(count):
# Просто заполняем сгенерированными данными
current_date = next(dates)
instances.append(ModelWithIndexes(
fk_field_id=next(fk_ids),
created_at=current_date,
created_at_with_brin_index=current_date,
created_at_with_btree_index=current_date,
big_text=next(uuids) * 5,
big_text_with_gin=next(uuids) * 5,
big_text_with_gist=next(uuids) * 5,
small_text=next(uuids),
small_text_btree_index=next(uuids),
small_text_with_hash_index=next(uuids),
field_with_composite_index_1=next(uuids),
field_with_composite_index_2=next(uuids),
field_without_composite_index_1=next(uuids),
field_without_composite_index_2=next(uuids),
))
# Создаем записи пачками по 10 000
ModelWithIndexes.objects.bulk_create(
instances, batch_size=10000
)
def create_model_with_indexes_m2m():
# Итератор для получения id записей связанной таблицы
m2m_ids = iter(
ModelWithUnique.objects
.values_list("id", flat=True)
)
# Итератор для получения id записей связанной таблицы в
# обратном порядке
m2m_ids_reversed = iter(
ModelWithUnique.objects
.values_list("id", flat=True).reverse()
)
m2m_instances = []
for instance in ModelWithIndexes.objects.all():
m2m_id = next(m2m_ids)
m2m_id_reversed = next(m2m_ids_reversed)
m2m_instances.append(ModelWithIndexes.m2m_field.through(
modelwithindexes_id=instance.id,
modelwithunique_id=m2m_id
))
# Не включаем совпадения, так как django проверит дубли
if m2m_id != m2m_id_reversed:
m2m_instances.append(ModelWithIndexes.m2m_field.through(
modelwithindexes_id=instance.id,
modelwithunique_id=m2m_id_reversed
))
# Создаем записи пачками по 10 000
ModelWithIndexes.m2m_field.through.objects.bulk_create(
m2m_instances, batch_size=10000
)
# Создадим миллион записей для ModelWithBloomIndex
create_model_with_bloom_index(1_000_000)
# Создадим миллион записей для ModelWithUnique
create_model_with_unique(1_000_000)
# Создадим миллион записей для ModelWithIndexes
create_model_with_indexes(1_000_000)
# Создадим m2m связи для записей ModelWithIndexes
create_model_with_indexes_m2m()
Размеры индексов и их количество
Теперь мы можем посмотреть, какие индексы были созданы для каждой таблицы и сколько они занимают места на диске. Для этого вы можете использовать команду \dti+ в терминале psql, она выведет информацию об индексах и таблицах.
Таблица advanced_modelwithbloomindex (ModelWithBloomIndex) 150 MB
Название индекса |
Тип индекса |
Размер |
advanced_modelwithbloomindex_pkey |
btree |
21 MB |
advanced_multi_field_1_btree_idx |
btree |
37 MB |
advanced_multi_field_2_btree_idx |
btree |
37 MB |
advanced_multi_field_3_btree_idx |
btree |
24 MB |
advanced_multi_field_4_btree_idx |
btree |
23 MB |
advanced_multi_field_bloom_idx |
bloom |
15 MB |
Здесь индекс advanced_modelwithbloomindex_pkey был создан автоматически для первичного ключа таблицы. Остальные индексы мы указали явно.
Можем заметить, что advanced_multi_field_bloom_idx занимает меньше места чем остальные индексы, а также для всех 4 полей он нужен в единственном экземпляре, что и является его преимуществом. Хоть и поиск по нему будет осуществляться дольше.
Таблица advanced_modelwithunique (ModelWithUnique) 291 MB
Название индекса |
Тип индекса |
Размер |
advanced_modelwithunique_default_index_field_cb2b39ae |
btree |
118 MB |
advanced_modelwithunique_default_index_field_cb2b39ae_like |
btree |
118 MB |
advanced_modelwithunique_pkey |
btree |
34 MB |
advanced_modelwithunique_unique_field_key |
btree |
119 MB |
advanced_modelwithunique_unique_field_f7af07ff_like |
btree |
119 MB |
advanced_unique_together_field_1_2 |
btree |
206 MB |
Индексы advanced_modelwithunique_default_index_field_cb2b39ae и advanced_modelwithunique_default_index_field_cb2b39ae_like были созданы автоматически для текстового поля default_index_field при указании db_index=True. Имейте в виду, что django создает в таком случае сразу 2 индекса: первый для точного поиска, второй для поиска подстроки по шаблону, используя __contains (LIKE '%pattern%').
Индекс advanced_modelwithunique_pkey создается автоматически для первичного ключа таблицы, как и для любой другой таблицы.
Индексы advanced_modelwithunique_unique_field_key и advanced_modelwithunique_unique_field_f7af07ff_like созданы автоматически для поля unique_field, так как мы указали параметр unique=True.
advanced_unique_together_field_1_2 был создан автоматически для поиска по паре полей, так как мы указали уникальность пары полей unique_together_field_1 и unique_together_field_2.
Таблица advanced_modelwithindexes (ModelWithIndexes) 868 MB
Название индекса |
Тип индекса |
Размер |
advanced_big_text_gin |
gin |
422 MB |
advanced_big_text_gist |
gist |
59 MB |
advanced_combined |
btree |
134 MB |
advanced_created_at_brin |
brin |
64 kB |
advanced_created_at_btree |
btree |
22 MB |
advanced_modelwithindexes_fk_field_id_48ab936e |
btree |
22 MB |
advanced_modelwithindexes_pkey |
btree |
22 MB |
advanced_small_text_btree |
btree |
76 MB |
advanced_small_text_hash |
hash |
37 MB |
Индекс advanced_modelwithindexes_pkey был создан автоматически для первичного ключа таблицы.
Индекс advanced_modelwithindexes_fk_field_id_48ab936e был создан автоматически для первичного ключа таблицы ModelWithBloomIndex, которую мы указали в поле fk_field.
Остальные индексы мы определили явно.
Таблица advanced_modelwithindexes_m2m_field (ModelWithIndexes.m2m_field.through) 50 MB
Промежуточная таблица для m2m связи между ModelWithIndexes и ModelWithUnique, которую мы указали в поле m2m_field.
Название индекса |
Тип индекса |
Размер |
advanced_modelwithindexe_modelwithindexes_id_mode_3fdedf4b_uniq |
btree |
21 MB |
advanced_modelwithindexes__modelwithindexes_id_47db69d5 |
btree |
37 MB |
advanced_modelwithindexes_m2m_field_modelwithunique_id_8139e026 |
btree |
37 MB |
advanced_modelwithindexes_m2m_field_pkey |
btree |
24 MB |
Для данной таблицы все индексы были созданы автоматически.
Индексы advanced_modelwithindexes__modelwithindexes_id_47db69d5 и advanced_modelwithindexes_m2m_field_modelwithunique_id_8139e026 были созданы для первичных ключей таблиц ModelWithIndexes и ModelWithUnique.
Индекс advanced_modelwithindexes_m2m_field_pkey был создан для первичного ключа самой промежуточной таблицы.
Индекс advanced_modelwithindexe_modelwithindexes_id_mode_3fdedf4b_uniq был создан для быстрой проверки уникальности пары первичных ключей.
Сравнение работы индексов
Для того, чтобы понять эффективность работы индексов, давайте посмотрим, как будут планироваться запросы при участии полей с индексами и без них.
Запросы с таблицами ModelWithUnique и ModelWithIndexes.m2m_field.through детально мы не будем рассматривать так как они были нужны для того, чтобы показать, как и какие индексы django создает по умолчанию.
Еще раз акцентирую внимание, по умолчанию, при использовании db_index=True и unique=True django может создавать сразу по 2 индекса и если такое поведение вас не устраивает, вам необходимо указывать индексы явно в Meta вашей модели.
Поиск по многим полям (B-Tree и Bloom).
Начнем с таблицы ModelWithBloomIndex и первым делом посмотрим, как ведет себя поиск по столбцам без индекса.
from datetime import date
from advanced.models import ModelWithBloomIndex
# Здесь и далее сразу будем выводить план планировщика запросов
explain(ModelWithBloomIndex.objects.filter(
char_field_1="uTSdXupYPVOaDtm",
integer_field_4=460098
))
Здесь идет сканирование по самой таблице
Gather (cost=1000.00..26481.10 rows=1 width=128) (actual time=35.628..38.325 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on advanced_modelwithbloomindex (cost=0.00..25481.00 rows=1 width=128) (actual time=25.018..25.019 rows=0 loops=3)
Filter: (((char_field_1)::text = 'uTSdXupYPVOaDtm'::text) AND (integer_field_4 = 460098))
Rows Removed by Filter: 333333
Planning Time: 0.074 ms
Execution Time: 38.358 msТеперь взглянем на поиск по B-Tree индексу.
explain(ModelWithBloomIndex.objects.filter(
char_field_1_btree="LhnDapYoCVXWYCs",
integer_field_4_btree=113736
))Index Scan using advanced_multi_field_1_btree_idx on advanced_modelwithbloomindex (cost=0.42..8.45 rows=1 width=128) (actual time=0.064..0.065 rows=1 loops=1)
Index Cond: ((char_field_1_btree)::text = 'LhnDapYoCVXWYCs'::text)
Filter: (integer_field_4_btree = 113736)
Planning Time: 0.072 ms
Execution Time: 0.076 msСейчас для основания поиска был выбран индекс для поля char_field_2_btree. Мы сразу видим значительный прирост фактической скорости поиска записей. В случае, если бы мы не запрашивали его, был бы выбран другой индекс, и для возможности использования индексов для поиска по разной комбинации полей, нам нужно определить индекс для каждого из них, что может стоить значительного места в на диске.
Наконец взглянем на работу индекса Bloom.
explain(ModelWithBloomIndex.objects.filter(
char_field_1_bloom="MLgdOGMnnHeyZgt",
integer_field_4_bloom=496094,
))Bitmap Heap Scan on advanced_modelwithbloomindex (cost=17848.00..17852.02 rows=1 width=128) (actual time=7.692..7.694 rows=1 loops=1)
Recheck Cond: (((char_field_1_bloom)::text = 'MLgdOGMnnHeyZgt'::text) AND (integer_field_4_bloom = 496094))
Rows Removed by Index Recheck: 38
Heap Blocks: exact=39
-> Bitmap Index Scan on advanced_multi_field_bloom_idx (cost=0.00..17848.00 rows=1 width=0) (actual time=7.428..7.429 rows=39 loops=1)
Index Cond: (((char_field_1_bloom)::text = 'MLgdOGMnnHeyZgt'::text) AND (integer_field_4_bloom = 496094))
Planning Time: 0.057 ms
Execution Time: 7.771 msВ данном случае используется индекс advanced_multi_field_bloom_idx. Так как индекс является неточным, всегда дополнительно будут перепроверяться фактические значения.
В целом, исходя из фактического времени выполнения запроса, мы можем сделать вывод, что индексы дают значительный прирост в скорости поиска.
При использовании Bloom индекса записи ищутся намного дольше, чем при использовании B-Tree, но сам индекс весит меньше и может быть применен сразу ко многим полям.
Если вы ограничены в месте на диске и вас интересует хотя бы минимальное увеличение скорости поиска полных совпадений, Bloom индекс будет хорошим решением.
Поиск по датам (Brin и B-Tree)
Теперь перейдем к ModelWithIndexes.
Первым делом посмотрим, как поиск ведет себя с датами. Сначала без использования индекса.
from advanced.models import ModelWithIndexes
explain(ModelWithIndexes.objects.filter(
created_at__lt=date(2050, 1, 1),
))Здесь сканирование идет по самой таблице
Gather (cost=1000.00..118257.88 rows=9375 width=851) (actual time=2.576..62.615 rows=8970 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on advanced_modelwithindexes (cost=0.00..116320.38 rows=3906 width=851) (actual time=29.265..47.775 rows=2990 loops=3)
Filter: (created_at < '2050-01-01 00:00:00+00'::timestamp with time zone)
Rows Removed by Filter: 330343
Planning Time: 0.077 ms
Execution Time: 63.168 msЗатем посмотрим на работу B-Tree.
explain(ModelWithIndexes.objects.filter(
created_at_with_btree_index__lt=date(2050, 1, 1),
))Index Scan using advanced_created_at_btree on advanced_modelwithindexes (cost=0.42..1318.56 rows=9379 width=851) (actual time=0.016..3.206 rows=8970 loops=1)
Index Cond: (created_at_with_btree_index < '2050-01-01 00:00:00+00'::timestamp with time zone)
Planning Time: 0.212 ms
Execution Time: 3.351 msПри использовании индекса advanced_created_at_btree мы получили более чем ощутимый прирост, но стоит помнить, данный индекс весит 22 MB, хоть это достаточно скромное значение, размер индекса Brin куда меньше.
Далее посмотрим на работу Brin.
explain(ModelWithIndexes.objects.filter(
created_at_with_brin_index__lt=date(2050, 1, 1),
))Bitmap Heap Scan on advanced_modelwithindexes (cost=22.63..30876.16 rows=9375 width=851) (actual time=0.109..5.507 rows=8970 loops=1)
Recheck Cond: (created_at_with_brin_index < '2050-01-01 00:00:00+00'::timestamp with time zone)
Rows Removed by Index Recheck: 310
Heap Blocks: lossy=1032
-> Bitmap Index Scan on advanced_created_at_brin (cost=0.00..20.29 rows=10357 width=0) (actual time=0.102..0.103 rows=10320 loops=1)
Index Cond: (created_at_with_brin_index < '2050-01-01 00:00:00+00'::timestamp with time zone)
Planning Time: 0.122 ms
Execution Time: 5.925 msТак как индекс Brin также как и Bloom является неточным, нам всегда нужно перепроверять и уточнять реальные значения.
Тем не менее, в данном случае, мы получили почти такой же прирост по скорости, как и при использовании B-Tree, особенно в этом впечатляет то, что данный индекс весит всего 64 kB на таблице с 1 000 000 записей.
Полнотекстовый поиск (GIN, GiST и B-Tree)
Теперь давайте глянем на эффективность полнотекстового поиска. Сначала без индекса.
from django.contrib.postgres.search import SearchVector, SearchQuery
explain(
ModelWithIndexes.objects
# Преобразуем текст в вектор для поиска
.alias(search=SearchVector("big_text", config="russian"))
# Попробуем найти значение
.filter(search=SearchQuery("123", config="russian"))
)Здесь мы сканируем саму таблицу
Gather (cost=1000.00..221987.88 rows=5000 width=851) (actual time=4896.181..4900.291 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on advanced_modelwithindexes (cost=0.00..220487.88 rows=2083 width=851) (actual time=4882.752..4882.752 rows=0 loops=3)
Filter: (to_tsvector('russian'::regconfig, COALESCE(big_text, ''::text)) @@ '''123'''::tsquery)
Rows Removed by Filter: 333333
Planning Time: 0.177 ms
Execution Time: 4900.982 msЗатем проверим работу с GIN.
explain(
ModelWithIndexes.objects
# Преобразуем текст в вектор для поиска
.alias(search=SearchVector("big_text_with_gin", config="russian"))
# Попробуем найти значение
.filter(search=SearchQuery("123", config="russian"))
)Bitmap Heap Scan on advanced_modelwithindexes (cost=27.02..1125.84 rows=267 width=851) (actual time=0.040..0.041 rows=0 loops=1)
Recheck Cond: (to_tsvector('russian'::regconfig, COALESCE(big_text_with_gin, ''::text)) @@ '''123'''::tsquery)
-> Bitmap Index Scan on advanced_big_text_gin (cost=0.00..26.95 rows=267 width=0) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (to_tsvector('russian'::regconfig, COALESCE(big_text_with_gin, ''::text)) @@ '''123'''::tsquery)
Planning Time: 0.268 ms
Execution Time: 0.062 msВ данном случае был использован индекс advanced_big_text_gin и мы получили огромный прирост скорости поиска, но давайте сравним этот результат с использованием GiST.
explain(
ModelWithIndexes.objects
# Преобразуем текст в вектор для поиска
.alias(search=SearchVector("big_text_with_gist", config="russian"))
# Попробуем найти значение
.filter(search=SearchQuery("123", config="russian"))
)Index Scan using advanced_big_text_gist on advanced_modelwithindexes (cost=0.29..1084.96 rows=267 width=851) (actual time=29.907..29.908 rows=0 loops=1)
Index Cond: (to_tsvector('russian'::regconfig, COALESCE(big_text_with_gist, ''::text)) @@ '''123'''::tsquery)
Planning Time: 0.147 ms
Execution Time: 29.925 msВ целом, мы также получили неплохой прирост в скорости, но результат куда хуже, чем при использовании GIN. Справедливости ради, нужно сказать и о том, что GiST занимает куда меньше места на диске, но и тут не стоит забывать о том, что, чем больше повторяющихся ключей в индексах, тем меньше будет весить GIN индекс.
Поиск точного совпадения (HASH и B-Tree)
Снова начинаем с поиска без индексов.
explain(ModelWithIndexes.objects.filter(
small_text="47c58638-2847-4dc7-8462-a721e8235516",
))Здесь мы сканируем саму таблицу
Gather (cost=1000.00..117320.48 rows=1 width=851) (actual time=67.964..71.498 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on advanced_modelwithindexes (cost=0.00..116320.38 rows=1 width=851) (actual time=56.045..56.046 rows=0 loops=3)
Filter: ((small_text)::text = '47c58638-2847-4dc7-8462-a721e8235516'::text)
Rows Removed by Filter: 333333
Planning Time: 0.100 ms
Execution Time: 71.880 msДалее взглянем на B-Tree
explain(ModelWithIndexes.objects.filter(
small_text_btree_index="2d9d006c-d77f-4e92-8856-c8835c5b4129",
))Index Scan using advanced_small_text_btree on advanced_modelwithindexes (cost=0.42..8.44 rows=1 width=851) (actual time=0.042..0.043 rows=1 loops=1)
Index Cond: ((small_text_btree_index)::text = '2d9d006c-d77f-4e92-8856-c8835c5b4129'::text)
Planning Time: 0.065 ms
Execution Time: 0.054 msОжидаемо получаем значительный прирост скорости поиска.
Последним рассмотрим работу HASH.
explain(ModelWithIndexes.objects.filter(
small_text_with_hash_index="9e3f41fb-51fd-405d-bae7-0da696c7f7b6",
))Index Scan using advanced_small_text_hash on advanced_modelwithindexes (cost=0.00..8.02 rows=1 width=851) (actual time=0.012..0.013 rows=1 loops=1)
Index Cond: ((small_text_with_hash_index)::text = '9e3f41fb-51fd-405d-bae7-0da696c7f7b6'::text)
Planning Time: 0.063 ms
Execution Time: 0.024 msТут мы получили сопоставимый результат. В среднем, в зависимости от длины строки и заполненности базы данных, при поиске полного совпадения вы должны получать лучшую скорость при использовании HASH индекса, а также данный индекс занимает ощутимо меньше места на диске.
Комбинированный индекс
Наконец рассмотрим работу комбинированного индекса. Сначала попробуем найти запись по 2 полям без индексов.
explain(ModelWithIndexes.objects.filter(
field_without_composite_index_1="733f5adf-f389-4fdf-984e-8970afd6e28b",
field_without_composite_index_2="221d82bf-1d71-43fd-a854-62892d393b23",
))Как и раньше сканируем саму таблицу
Gather (cost=1000.00..118362.15 rows=1 width=851) (actual time=69.031..72.790 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on advanced_modelwithindexes (cost=0.00..117362.05 rows=1 width=851) (actual time=59.619..59.620 rows=0 loops=3)
Filter: (((field_without_composite_index_1)::text = '733f5adf-f389-4fdf-984e-8970afd6e28b'::text) AND ((field_without_composite_index_2)::text = '221d82bf-1d71-43fd-a854-62892d393b23'::text))
Rows Removed by Filter: 333333
Planning Time: 0.060 ms
Execution Time: 73.038 msА теперь будем искать по комбинированному индексу.
explain(ModelWithIndexes.objects.filter(
field_with_composite_index_1="39908556-921f-4670-b225-7a2226f4cd30",
field_with_composite_index_2="1a6d2c41-add4-4993-a67f-0f0a4a7724e2",
))Index Scan using advanced_combined on advanced_modelwithindexes (cost=0.55..8.57 rows=1 width=851) (actual time=0.141..0.145 rows=1 loops=1)
Index Cond: (((field_with_composite_index_1)::text = '39908556-921f-4670-b225-7a2226f4cd30'::text) AND ((field_with_composite_index_2)::text = '1a6d2c41-add4-4993-a67f-0f0a4a7724e2'::text))
Planning Time: 0.232 ms
Execution Time: 0.185 msОжидаемо получаем значительный прирост скорости поиска. С данным индексом стоит быть осторожным, так как он занимает значительное количество места на диске. Самым частым примером использования, наверное, является поиск по ФИО, когда у вас нет общего для них поля в базе данных.
Итоги главы
Зафиналим данную главу следующими выводами:
Для разных типов данных выделяется разное место для хранения (в том числе и в зависимости от указанных вами ограничений), поэтому вы всегда должны использовать только те типы, которые вам необходимы для закрытия той или иной потребности
Нельзя бездумно вешать индексы на все поля, они занимают много места на диске, а также замедляют вставку и изменение данных в таблицах
Django автоматически и так создает достаточно много индексов.
Разные индексы подходят для решения разных типов задач, поэтому выбор индекса также должен быть осмысленным и уместным
Что вам важнее: скорость поиска, занятость места на диске или что-то среднее, решать только вам
Прочие способы оптимизации
Есть еще несколько моментов, о которых я не говорил ранее, но также обязан осветить в контексте данной статьи.
Партицирование таблиц
На самом деле, у вас могут быть и так огромные таблицы, с которыми вы работает постоянно. Иногда даже такие таблицы могут иметь, например, связанную таблицу с ежедневной историей, и тогда у вас получается просто колоссальное количество записей.
Для того чтобы облегчить задачу поиска, в таких случаях стоит использовать партицирование таблиц на основе даты. Буквально каждый день, неделю, месяц или год (в зависимости от объема данных) в вашей базе будет создаваться новая таблица, в которую будут писаться данные за строго отведенный для нее промежуток времени.
Здесь я не буду показывать пример, так как особых тонкостей в нем нет, а оставлю документацию пары библиотек, которые добавляют удобное управление партицированием для django моделей.
django-postgresql-partitioning (До django 5.0)
JOIN по нескольким полям
К сожалению, django не предоставляет почти никакой возможности совершить операцию JOIN по нескольким полям, хотя иногда этой возможности уж очень не хватает.
В целом, вы можете выполнить любой SQL используя метод raw вашего QuerySet, но такой подход является плохой практикой.
Одним из альтернативных решений может быть использование SQLAlchemy, в которой такая возможность есть.
Для того чтобы интегрировать SQLAlchemy в ваши django модели, вы можете использовать библиотеку Aldjemy. К сожалению, я сам достаточно давно пользовался ей в последний раз, а последнее обновление репозитория было около 6 месяцев назад, поэтому не могу гарантировать, что данная библиотека будет работать с последними версиями django.
VACUUM и ANALYZE
Ежедневно в вашей базе данных скапливается мусор в виде "мертвых" кортежей. При обычных операциях Postgres, кортежи, удаленные или устаревшие в результате обновления, физически не удаляются из таблицы. Они сохраняются в ней, пока не будет выполнена команда VACUUM. Поэтому вам необходимо периодически выполнять данную команду на ваших таблицах, особенно, если записи таблицы часто обновляются.
Вы также можете использовать VACUUM ANALYZE. Данная команда, после выполнения очистки, проведет анализ всех указанных таблиц, и обновит статистику, используемую планировщиком для определения наиболее эффективного способа выполнения запроса.
Оставляю вам документацию по этой команде.
Заключение
Понимаю, статья получилась более чем объемной, поэтому, хотя бы в заключении не буду затягивать. Я хотел затронуть как минимум большую часть аспектов, которые могут повлиять на оптимизацию запросов.
Буду очень рад, если вы будете периодически возвращаться к материалу данной статьи как к справочнику со всем, что вам нужно в вопросе оптимизации запросов.
Если вы дочитали эту статью до конца, вы входите в 1% читателей моих статей с тегом "Сложно", а это уже неплохо.
Надеюсь, данный материал будет полезен многим и вы смогли найти что-то новое для себя.
Если вы нашли орфографические, пунктуационные, или логические ошибки, а также неточности в материале статьи, пожалуйста, напишите мне в личные сообщения. Материал объемный, а глаза уже давно замылились, буду очень вам признателен.
Комментарии (2)
Pavel1114
13.06.2025 06:58Спасибо за статью. Сомневаюсь что новички много полезного вынесут - они просто ещё не сталкивались с проблемами, которые тут решаются. А вот мидлам будет полезно.
Пара занудных замечаний
select_related всё же не всегда приводит к INNER JOIN, а только в случае, когда поле имеет null=False. При null=True select_related приведёт к left outer join, что логично и правильно.
Я не сразу понял что вы имеете ввиду под "оценкой" QuerySet. Я понимаю, что все переводчики дают именно такой перевод, но всё же мы не оцениваем QuerySet, а скорее выполняем или исполняем или разрешаем. eval ведь тоже не оценка выражения, а его исполнение.
В статье несколько раз повторяется, но думаю не лишним будет ещё раз подчеркнуть. Все описанные "проблемы" это не недоработки django, а "особенности" реляционных баз данных. ORM это лишь удобный инструмент, позволяющий формировать запросы. Какое то время можно пользоваться им не понимая как работают БД. Но тогда вы будете постоянно удивляться почему плодятся дубли или как то неправильно работает сортировка. Поэтому очень рекомендую разобраться с основами реляционных БД и SQL.
Golgi
Спасибо большое за материал!
Достать до конца мне ещё предстоит, но базовые оптимизации уже улучшили моё понимание происходящего при запросах в Django.
Еще попробую применить прочитанное на Tortoise ORM (использую из-за асинхронности)