Индексация базы данных в Django

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

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

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

Содержимое

Что такое индексация базы данных?

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

Что такое индекс базы данных?

Индекс базы данных - это структура данных, используемая для повышения скорости операций поиска данных в таблице базы данных за счет дополнительного места для хранения и потенциального влияния на производительность операций записи (например, INSERT, UPDATE, и DELETE).

Как работает индексация?

Индекс работает так же, как и указатель в книге. Чтобы найти нужную информацию, вместо того чтобы листать каждую страницу, вы можете использовать индекс для быстрого поиска нужной вам информации. Аналогично, в базе данных индекс хранит указатели на строки в таблице. Когда выполняется запрос, компонент database engine сначала проверяет индекс, чтобы быстро найти данные.

Типы индексов

  1. Первичный индекс: Этот индекс автоматически создается для столбцов первичного ключа в базе данных.
  2. Уникальный индекс: Когда столбец определен с помощью атрибута unique, создается уникальный индекс, который гарантирует, что все значения в столбце различны.
  3. Составной индекс: Этот вид индекса включает в себя несколько столбцов.
  4. Полнотекстовый индекс: Оптимизирован для поиска больших текстовых данных.
  5. Кластеризованный индекс: Определяет физический порядок данных в таблице. Таблица может иметь только один кластеризованный индекс.
  6. Некластеризованный индекс: Содержит указатель на фактические данные, что позволяет использовать несколько некластеризованных индексов для каждой таблицы.

Преимущества и недостатки

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

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

  1. Более быстрый поиск данных: Индексы значительно сокращают время, необходимое для поиска определенных строк в таблице, особенно для больших наборов данных. Это особенно полезно для запросов, содержащих предложения WHERE, JOIN, и ORDER BY.
  2. Улучшена производительность запросов: Запросы, которые часто фильтруют или сортируют данные, выигрывают от индексов, поскольку они позволяют базе данных не просматривать всю таблицу целиком.
  3. Поддерживает уникальные ограничения: Индексы также помогают обеспечить уникальность столбцов, гарантируя, что в них не будут вставлены повторяющиеся значения, как это показано в случае с первичными ключами и ограничениями UNIQUE.
  4. Оптимизированный полнотекстовый поиск: Специализированные индексы, такие как полнотекстовые индексы, значительно ускоряют поиск в больших текстовых полях, особенно в таких базах данных, как MySQL и Postgres.
  5. Лучшая масштабируемость: По мере увеличения объема данных в базе данных правильная индексация помогает поддерживать производительность, обеспечивая эффективность запросов даже при работе с миллионами строк.

Недостатки

  1. Повышенные требования к хранилищу: Индексы занимают дополнительное место на диске, поскольку индекс создается в дополнение к таблицам и столбцам базы данных. Для больших таблиц с несколькими индексами это может привести к значительным затратам на хранение.
  2. Более медленные операции записи: Вставки, обновления и удаления выполняются медленнее, поскольку база данных должна обновлять индексы в дополнение к самим данным.
  3. Затраты на обслуживание: Индексы необходимо поддерживать в актуальном состоянии по мере изменения данных в таблице, что увеличивает вычислительные затраты.
  4. Не всегда эффективно: Если запрос не использует индексированные столбцы, индексирование не поможет. Например, использование функций или вычислений для индексированных полей может привести к обходу индекса. Оптимизатор запросов к базе данных также может решить, что полное сканирование более эффективно, чем использование индексации, которая сводит на нет причину индексации базы данных.
  5. Риск чрезмерной индексации: Добавление слишком большого количества индексов может снизить общую производительность, поскольку база данных тратит больше времени на поддержание индексов, чем на выполнение запросов.

Когда использовать индексацию

  1. Индексируйте часто запрашиваемые поля или столбцы, используемые в WHERE, JOIN, или ORDER BY.
  2. Избегайте индексации полей, которые редко используются в запросах.
  3. Соблюдайте баланс между производительностью чтения и записи в зависимости от потребностей вашего приложения.

Индексация в Django

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

Создание индексов в Django

Django может создавать несколько типов индексов базы данных, либо автоматически, либо с помощью явной настройки в ваших моделях. В Django вы можете определять индексы, используя db_index для одного столбца индекса, а также параметр indexes в мета-классе модели.

Типы индексов в Django

Тип индекса Создано автоматически? Пример использования
Primary Key Index Yes Первичный ключ по умолчанию
Foreign Key Index Yes Отношения внешнего ключа
Unique Index Yes unique=True на полях
Single-column Index No (explicit) db_index or Meta.indexes
Composite Index No (explicit) Meta.indexes с несколькими полями
Unique Composite Index Yes (unique_together) unique_together или UniqueConstraint
Full-Text Index No (explicit, Postgres only) GinIndex
Partial Index No (explicit, Postgres only) Индекс с условием

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

Примерный проект

Чтобы лучше понять концепцию индексации баз данных в Django, мы создали образец приложения Django - системы управления продуктами. Приложение используется для управления товарами в магазине, позволяя пользователям эффективно фильтровать, сортировать и выполнять поиск.

Система управления продукцией включает в себя следующие функции:

  1. Добавляйте товары и управляйте ими, используя такие сведения, как название, категория, цена и дата создания.
  2. Фильтруйте товары по категориям, чтобы находить товары в определенных группах.
  3. Сортируйте товары по цене, чтобы помочь пользователям найти самые дешевые или самые дорогие товары.
  4. Используйте индексы для повышения производительности запросов при выполнении операций фильтрации и сортировки.

Чтобы продолжить, клонируйте репозиторий и запустите проект:

$ git clone https://github.com/testdrivenio/django-db-indexes.git
$ cd django-db-indexes
$ docker compose up -d --build

Чтобы упростить работу, наш проект использует Docker для запуска приложения Django, а также Postgres. Интересно, как был разработан этот проект? Ознакомьтесь со статьей О настройке Django с помощью Postgres, Gunicorn и Nginx.

Модели Django

Обратите внимание на модели, которые мы определили в product/models.py:

  1. ProductWithoutIndex: Для этой модели не определены индексы.
  2. ProductWithSingleIndex: Эта модель имеет три индекса по одному столбцу через db_index=True в столбцах name, category и price.
  3. ProductWithCompositeIndex: Эта модель имеет составной индекс в столбцах category и price.

Чтобы просмотреть индексы, перейдите в psql:

$ docker compose exec api python manage.py dbshell

Продукт без индексной модели

# \d product_without_index

Вы можете увидеть индекс первичного ключа по умолчанию:

                              Table "public.product_without_index"
   Column   |           Type           | Collation | Nullable |             Default
------------+--------------------------+-----------+----------+----------------------------------
 id         | bigint                   |           | not null | generated by default as identity
 name       | character varying(100)   |           | not null |
 category   | character varying(50)    |           | not null |
 price      | integer                  |           | not null |
 created_at | timestamp with time zone |           | not null |
Indexes:
    "product_without_index_pkey" PRIMARY KEY, btree (id)

Продукт с Единой индексной моделью

# \d product_with_single_index

Результаты:

                            Table "public.product_with_single_index"
   Column   |           Type           | Collation | Nullable |             Default
------------+--------------------------+-----------+----------+----------------------------------
 id         | bigint                   |           | not null | generated by default as identity
 name       | character varying(100)   |           | not null |
 category   | character varying(50)    |           | not null |
 price      | integer                  |           | not null |
 created_at | timestamp with time zone |           | not null |
Indexes:
    "product_with_single_index_pkey" PRIMARY KEY, btree (id)
    "product_with_single_index_category_715a00f3" btree (category)
    "product_with_single_index_category_715a00f3_like" btree (category varchar_pattern_ops)
    "product_with_single_index_name_d6fb2180" btree (name)
    "product_with_single_index_name_d6fb2180_like" btree (name varchar_pattern_ops)
    "product_with_single_index_price_5a707788" btree (price)

Для каждого из столбцов создаются отдельные индексы, где db_index имеет значение True. Для каждого из них создаются индексы как для прямых фильтров, так и для фильтров LIKE.

Продукт С составной индексной моделью

# \d product_with_composite_index

Результаты:

                           Table "public.product_with_composite_index"
   Column   |           Type           | Collation | Nullable |             Default
------------+--------------------------+-----------+----------+----------------------------------
 id         | bigint                   |           | not null | generated by default as identity
 name       | character varying(100)   |           | not null |
 category   | character varying(50)    |           | not null |
 price      | integer                  |           | not null |
 created_at | timestamp with time zone |           | not null |
Indexes:
    "product_with_composite_index_pkey" PRIMARY KEY, btree (id)
    "category_price_idx" btree (category, price)
    "product_wit_name_5adbb5_idx" btree (name)

Составной индекс создается как для category, так и для price. Этот индекс позволяет создавать фильтры, объединяющие категорию и цену вместе, быстрее, чем устанавливать индекс для каждого столбца в отдельности.

Фиктивные данные

Возможности индексации становятся намного более очевидными, когда у вас большой набор данных. Чтобы помочь с этим, мы создали команду управления seed_db. Эта команда создает 500 тысяч записей для каждой категории в каждой таблице.

Запустите команду для заполнения базы данных:

$ docker compose exec api python manage.py seed_db

Это займет некоторое время, так что не стесняйтесь выпить чашечку кофе.

Тестирование

Чтобы увидеть, как работают таблицы и индексы, у нас есть другая команда управления, называемая run_tests. Взгляните на сценарий в product/management/commands/run_tests.py.

Команда принимает несколько аргументов

  1. table_type ( обязательный): Это int, где 1 представляет ProductWithoutIndex, 2 представляет ProductWithSingleIndex, а 3 представляет ProductWithCompositeIndex
  2. category: Должно быть "electronics", "clothing", или "home appliances"
  3. price: Целое число, используемое для фильтрации элементов

Основной запрос

Давайте начнем с простого запроса, чтобы получить все объекты.

Продукт без индексной модели
$ docker compose exec api python manage.py run_tests --table_type=1 --category="electronics"

Результаты:

[{'Execution Time': 41.694,
  'Plan': {'Actual Loops': 1,
           'Actual Rows': 0,
           'Actual Startup Time': 39.795,
           'Actual Total Time': 41.682,
           'Async Capable': False,
           'Node Type': 'Gather',
           'Output': ['id', 'name', 'category', 'price', 'created_at'],
           'Parallel Aware': False,
           'Plan Rows': 1,
           'Plan Width': 35,
           'Plans': [{'Actual Loops': 3,
                      'Actual Rows': 0,
                      'Actual Startup Time': 37.769,
                      'Actual Total Time': 37.77,
                      'Alias': 'product_without_index',
                      'Async Capable': False,
                      'Filter': '((product_without_index.category)::text = '
                                "'electronics'::text)",
                      'Node Type': 'Seq Scan',
                      'Output': ['id',
                                 'name',
                                 'category',
                                 'price',
                                 'created_at'],
                      'Parallel Aware': True,
                      'Parent Relationship': 'Outer',
                      'Plan Rows': 1,
                      'Plan Width': 35,
                      'Relation Name': 'product_without_index',
                      'Rows Removed by Filter': 500000,
                      'Schema': 'public',
                      'Startup Cost': 0.0,
                      'Total Cost': 20241.5,
                      'Workers': [{'Actual Loops': 1,
                                   'Actual Rows': 0,
                                   'Actual Startup Time': 36.902,
                                   'Actual Total Time': 36.903,
                                   'Worker Number': 0},
                                  {'Actual Loops': 1,
                                   'Actual Rows': 0,
                                   'Actual Startup Time': 36.892,
                                   'Actual Total Time': 36.893,
                                   'Worker Number': 1}]}],
           'Single Copy': False,
           'Startup Cost': 1000.0,
           'Total Cost': 21241.6,
           'Workers Launched': 2,
           'Workers Planned': 2},
  'Planning Time': 0.358,
  'Triggers': []}]

Мы видим, что сканирование всего тела было выполнено с Node Type: Seq Scan. Это заняло около 42 мс.

Продукт с Единой индексной моделью
$ docker compose exec api python manage.py run_tests --table_type=2 --category="electronics"

Результаты:

[{'Execution Time': 0.033,
  'Plan': {'Actual Loops': 1,
           'Actual Rows': 0,
           'Actual Startup Time': 0.019,
           'Actual Total Time': 0.02,
           'Alias': 'product_with_single_index',
           'Async Capable': False,
           'Index Cond': '((product_with_single_index.category)::text = '
                         "'electronics'::text)",
           'Index Name': 'product_with_single_index_category_715a00f3',
           'Node Type': 'Index Scan',
           'Output': ['id', 'name', 'category', 'price', 'created_at'],
           'Parallel Aware': False,
           'Plan Rows': 1,
           'Plan Width': 35,
           'Relation Name': 'product_with_single_index',
           'Rows Removed by Index Recheck': 0,
           'Scan Direction': 'Forward',
           'Schema': 'public',
           'Startup Cost': 0.43,
           'Total Cost': 4.45},
  'Planning Time': 0.633,
  'Triggers': []}]

В этом запросе оптимизатор базы данных использует индекс в качестве типа сканирования и использует правильный индекс. Выполнение этого запроса занимает около 0,033 мс, что намного лучше, чем для таблицы без индекса.

Продукт С составной индексной моделью
$ docker compose exec api python manage.py run_tests --table_type=3 --category="electronics"

Результаты:

[{'Execution Time': 0.032,
  'Plan': {'Actual Loops': 1,
           'Actual Rows': 0,
           'Actual Startup Time': 0.02,
           'Actual Total Time': 0.02,
           'Alias': 'product_with_composite_index',
           'Async Capable': False,
           'Index Cond': '((product_with_composite_index.category)::text = '
                         "'electronics'::text)",
           'Index Name': 'category_price_idx',
           'Node Type': 'Index Scan',
           'Output': ['id', 'name', 'category', 'price', 'created_at'],
           'Parallel Aware': False,
           'Plan Rows': 1,
           'Plan Width': 35,
           'Relation Name': 'product_with_composite_index',
           'Rows Removed by Index Recheck': 0,
           'Scan Direction': 'Forward',
           'Schema': 'public',
           'Startup Cost': 0.43,
           'Total Cost': 6.2},
  'Planning Time': 0.293,
  'Triggers': []}]

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

Отфильтрованный запрос

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

Продукт без индексной модели
$ docker compose exec api python manage.py run_tests --table_type=1 --category="electronics" --price=1000

Результаты:

[{'Execution Time': 44.069,
  'Plan': {'Actual Loops': 1,
           'Actual Rows': 0,
           'Actual Startup Time': 42.227,
           'Actual Total Time': 44.056,
           'Async Capable': False,
           'Node Type': 'Gather',
           'Output': ['id', 'name', 'category', 'price', 'created_at'],
           'Parallel Aware': False,
           'Plan Rows': 1,
           'Plan Width': 35,
           'Plans': [{'Actual Loops': 3,
                      'Actual Rows': 0,
                      'Actual Startup Time': 40.342,
                      'Actual Total Time': 40.342,
                      'Alias': 'product_without_index',
                      'Async Capable': False,
                      'Filter': '((product_without_index.price <= 1000) AND '
                                '((product_without_index.category)::text = '
                                "'electronics'::text))",
                      'Node Type': 'Seq Scan',
                      'Output': ['id',
                                 'name',
                                 'category',
                                 'price',
                                 'created_at'],
                      'Parallel Aware': True,
                      'Parent Relationship': 'Outer',
                      'Plan Rows': 1,
                      'Plan Width': 35,
                      'Relation Name': 'product_without_index',
                      'Rows Removed by Filter': 500000,
                      'Schema': 'public',
                      'Startup Cost': 0.0,
                      'Total Cost': 21804.0,
                      'Workers': [{'Actual Loops': 1,
                                   'Actual Rows': 0,
                                   'Actual Startup Time': 39.573,
                                   'Actual Total Time': 39.574,
                                   'Worker Number': 0},
                                  {'Actual Loops': 1,
                                   'Actual Rows': 0,
                                   'Actual Startup Time': 39.567,
                                   'Actual Total Time': 39.567,
                                   'Worker Number': 1}]}],
           'Single Copy': False,
           'Startup Cost': 1000.0,
           'Total Cost': 22804.1,
           'Workers Launched': 2,
           'Workers Planned': 2},
  'Planning Time': 0.26,
  'Triggers': []}]

Снова было выполнено полное сканирование тела. Это заняло приблизительно 44 мс.

Продукт с Единой индексной моделью
$ docker compose exec api python manage.py run_tests --table_type=2 --category="electronics" --price=1000

Результаты:

[{'Execution Time': 0.041,
  'Plan': {'Actual Loops': 1,
           'Actual Rows': 0,
           'Actual Startup Time': 0.011,
           'Actual Total Time': 0.011,
           'Alias': 'product_with_single_index',
           'Async Capable': False,
           'Index Cond': '((product_with_single_index.category)::text = '
                         "'electronics'::text)",
           'Index Name': 'product_with_single_index_category_715a00f3',
           'Node Type': 'Index Scan',
           'Output': ['id', 'name', 'category', 'price', 'created_at'],
           'Parallel Aware': False,
           'Plan Rows': 1,
           'Plan Width': 35,
           'Relation Name': 'product_with_single_index',
           'Rows Removed by Index Recheck': 0,
           'Scan Direction': 'Forward',
           'Schema': 'public',
           'Startup Cost': 0.43,
           'Total Cost': 4.45},
  'Planning Time': 0.353,
  'Triggers': []}]
Продукт С составной индексной моделью
$ docker compose exec api python manage.py run_tests --table_type=3 --category="electronics" --price=1000

Результаты:

[{'Execution Time': 0.036,
  'Plan': {'Actual Loops': 1,
           'Actual Rows': 0,
           'Actual Startup Time': 0.021,
           'Actual Total Time': 0.022,
           'Alias': 'product_with_composite_index',
           'Async Capable': False,
           'Index Cond': '(((product_with_composite_index.category)::text = '
                         "'electronics'::text) AND "
                         '(product_with_composite_index.price <= 1000))',
           'Index Name': 'category_price_idx',
           'Node Type': 'Index Scan',
           'Output': ['id', 'name', 'category', 'price', 'created_at'],
           'Parallel Aware': False,
           'Plan Rows': 1,
           'Plan Width': 35,
           'Relation Name': 'product_with_composite_index',
           'Rows Removed by Index Recheck': 0,
           'Scan Direction': 'Forward',
           'Schema': 'public',
           'Startup Cost': 0.43,
           'Total Cost': 6.2},
  'Planning Time': 0.365,
  'Triggers': []}]

Эти различия, как правило, более заметны в таблицах с большими наборами данных. Чтобы дополнительно проверить это, вы можете увеличить размер пакета команды seed_db, чтобы увидеть, насколько быстрее индексация выполняет фильтрацию и сортировку.

Заключение

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

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

Сочетая практическую реализацию с лучшими практиками, вы можете использовать индексацию для обеспечения более быстрого и понятного взаимодействия с пользователем. Не забудьте проанализировать шаблоны запросов вашего приложения и сбалансировать параметры индексации для получения оптимальных результатов. Обладая этими инструментами и техниками, вы уверенно продвигаетесь по пути создания масштабируемых и высокопроизводительных приложений на Django!

Удачной индексации.

Вернуться на верх