mufassall

Шпаргалка · Данные и хранение

Индексы в базе данных

13 мин · Средний · mufassall.dtwin.uz/ru/l/indexes/

← Открыть урок

Проблема

Страница истории заказов тормозит, когда таблица дорастает до 40 миллионов строк. Читаем план запроса, добавляем правильные B-tree индексы и смотрим, во что каждый индекс обходится на записи.

Чему вы научитесь

  • Читать EXPLAIN: Seq Scan против Index Scan
  • Почему порядок колонок в составном индексе решает, каким запросам он поможет
  • Как покрывающий индекс отвечает, не трогая таблицу
  • Почему каждый лишний индекс замедляет каждый INSERT
Итоговая сцена
40%35%

Диаграмма как код

Итоговая схема урока в виде текста: вставьте её в Jira, GitLab, GitHub, Notion или документацию, там Mermaid рисуется сам. Есть варианты для D2 и PlantUML (Confluence, IntelliJ).

Mermaid: схема

flowchart LR
  n_app(["Приложение магазина"])
  n_api["API заказов"]
  n_db[("База заказов")]
  n_report("Ежедневный отчёт")
  n_import("Массовый импорт")
  n_app --> n_api
  n_api --> n_db
  n_report --> n_db
  n_import --> n_db

Mermaid: последовательность запросов

sequenceDiagram
  participant n_api as API заказов
  participant n_db as База заказов
  participant n_report as Ежедневный отчёт
  participant n_import as Массовый импорт
  Note over n_api,n_db: История заказов открывается 2,4 секунды
  n_api->>n_db: SELECT id, status, total FROM orders
  n_db-->>n_api: (20 rows)
  Note over n_api,n_db: EXPLAIN показывает полное сканирование
  n_api->>n_db: EXPLAIN ANALYZE
  n_db-->>n_api: Limit (actual rows=20)
  Note over n_api,n_db: B-tree индекс по customer_id
  n_api->>n_db: CREATE INDEX CONCURRENTLY idx_orders_customer
  n_db-->>n_api: CREATE INDEX
  Note over n_api,n_db: Оптовик с 250 000 заказов
  n_api->>n_db: EXPLAIN ANALYZE
  n_db-->>n_api: Limit (actual rows=20)
  Note over n_api,n_db: Один индекс на две колонки
  n_api->>n_db: EXPLAIN ANALYZE
  n_db-->>n_api: Limit (actual rows=20)
  Note over n_report,n_db: Новый индекс не помогает вчерашнему отчёту
  n_report->>n_db: EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
  n_db-->>n_report: Aggregate
  Note over n_report,n_db: Индекс под запрос отчёта
  n_report->>n_db: EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
  n_db-->>n_report: Aggregate
  Note over n_api,n_db: Покрывающий индекс не трогает таблицу
  n_api->>n_db: EXPLAIN ANALYZE
  n_db-->>n_api: Limit (actual rows=20)
  Note over n_import,n_db: Семь индексов, и импорт ползёт
  n_import->>n_db: INSERT INTO orders (customer_id, created_at, status, total)
  n_db-->>n_import: INSERT 0 500
  Note over n_api,n_db: Удаляем индексы, которые никто не читает
  n_api->>n_db: SELECT indexrelname, idx_scan
  n_db-->>n_api: indexrelname #124; idx_scan

D2

direction: right

n_app: "Приложение магазина" {
  shape: rectangle
}
n_api: "API заказов" {
  shape: rectangle
}
n_db: "База заказов" {
  shape: cylinder
}
n_report: "Ежедневный отчёт" {
  shape: rectangle
  style.multiple: true
}
n_import: "Массовый импорт" {
  shape: rectangle
  style.multiple: true
}

n_app -> n_api
n_api -> n_db
n_report -> n_db
n_import -> n_db

PlantUML: последовательность запросов

@startuml
participant "API заказов" as n_api
database "База заказов" as n_db
participant "Ежедневный отчёт" as n_report
participant "Массовый импорт" as n_import
note over n_api, n_db : История заказов открывается 2,4 секунды
n_api -> n_db : SELECT id, status, total FROM orders
n_db --> n_api : (20 rows)
note over n_api, n_db : EXPLAIN показывает полное сканирование
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_api, n_db : B-tree индекс по customer_id
n_api -> n_db : CREATE INDEX CONCURRENTLY idx_orders_customer
n_db --> n_api : CREATE INDEX
note over n_api, n_db : Оптовик с 250 000 заказов
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_api, n_db : Один индекс на две колонки
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_report, n_db : Новый индекс не помогает вчерашнему отчёту
n_report -> n_db : EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db --> n_report : Aggregate
note over n_report, n_db : Индекс под запрос отчёта
n_report -> n_db : EXPLAIN ANALYZE SELECT count(*), sum(total) FROM orders
n_db --> n_report : Aggregate
note over n_api, n_db : Покрывающий индекс не трогает таблицу
n_api -> n_db : EXPLAIN ANALYZE
n_db --> n_api : Limit (actual rows=20)
note over n_import, n_db : Семь индексов, и импорт ползёт
n_import -> n_db : INSERT INTO orders (customer_id, created_at, status, total)
n_db --> n_import : INSERT 0 500
note over n_api, n_db : Удаляем индексы, которые никто не читает
n_api -> n_db : SELECT indexrelname, idx_scan
n_db --> n_api : indexrelname | idx_scan
@enduml

PlantUML: компоненты

@startuml
left to right direction
agent "Приложение магазина" as n_app
component "API заказов" as n_api
database "База заказов" as n_db
collections "Ежедневный отчёт" as n_report
collections "Массовый импорт" as n_import
n_app --> n_api
n_api --> n_db
n_report --> n_db
n_import --> n_db
@enduml

Итог

Начинайте с плана: EXPLAIN ANALYZE и строка Rows Removed by Filter показывают, что база делает на самом деле.

1) B-tree индекс превращает полный проход в спуск по дереву. 2) В составном индексе важен порядок: сначала колонки фильтра, потом колонка сортировки. 3) INCLUDE делает индекс покрывающим, и таблица не читается. 4) Каждый индекс стоит на каждой записи: смотрите idx_scan и удаляйте то, чем никто не пользуется.

Показатели: в начале и в конце

ПоказательВ началеВ конце
Страница p952 400 мс2 мс
Строк прочитано за запрос40 000 00020
Вставка строки0,3 мс0,5 мс
Индексов на orders13

Шаги

  1. Проблема История заказов открывается 2,4 секунды

    Покупатель открывает «Мои заказы».

  2. Проблема EXPLAIN показывает полное сканирование

    EXPLAIN ANALYZE выполняет запрос и печатает план, который выбрала база.

  3. Решение B-tree индекс по customer_id

    Индекс — отдельная отсортированная структура: B-дерево значений customer_id, каждое указывает на свои строки в таблице.

  4. Проблема Оптовик с 250 000 заказов

    Клиент 7 — перекупщик с 250 000 заказов.

  5. Решение Один индекс на две колонки

    Составной индекс (customer_id, created_at DESC) отсортирован сначала по клиенту, а внутри клиента — по дате.

  6. Проблема Новый индекс не помогает вчерашнему отчёту

    Ежедневный отчёт суммирует все заказы со вчерашнего дня по всем клиентам.

  7. Решение Индекс под запрос отчёта

    Добавляем индекс только по created_at.

  8. Решение Покрывающий индекс не трогает таблицу

    Даже с составным индексом за status и total каждой из 20 строк приходится ходить в таблицу.

  9. Проблема Семь индексов, и импорт ползёт

    За год «на всякий случай» добавили ещё три индекса: по status, total и комментарию к заказу.

  10. Решение Удаляем индексы, которые никто не читает

    База считает, сколько раз использовался каждый индекс.

  11. Итоги Итоги

    Начинайте с плана: EXPLAIN ANALYZE и строка Rows Removed by Filter показывают, что база делает на самом деле.

Термины

Индекс
Отдельная отсортированная структура, обычно B-tree, чтобы база находила строки без полного сканирования таблицы. В составном индексе важен порядок колонок, а каждый индекс замедляет каждую запись.