Шпаргалка · Данные и хранение
Индексы в базе данных
Проблема
Страница истории заказов тормозит, когда таблица дорастает до 40 миллионов строк. Читаем план запроса, добавляем правильные B-tree индексы и смотрим, во что каждый индекс обходится на записи.
Чему вы научитесь
- Читать
EXPLAIN:Seq ScanпротивIndex Scan - Почему порядок колонок в составном индексе решает, каким запросам он поможет
- Как покрывающий индекс отвечает, не трогая таблицу
- Почему каждый лишний индекс замедляет каждый
INSERT
Диаграмма как код
Итоговая схема урока в виде текста: вставьте её в 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_dbMermaid: последовательность запросов
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_scanD2
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_dbPlantUML: последовательность запросов
@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
@endumlPlantUML: компоненты
@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 и удаляйте то, чем никто не пользуется.
Показатели: в начале и в конце
| Показатель | В начале | В конце |
|---|---|---|
| Страница p95 | 2 400 мс | 2 мс |
| Строк прочитано за запрос | 40 000 000 | 20 |
| Вставка строки | 0,3 мс | 0,5 мс |
| Индексов на orders | 1 | 3 |
Шаги
- Проблема История заказов открывается 2,4 секунды
Покупатель открывает «Мои заказы».
- Проблема EXPLAIN показывает полное сканирование
EXPLAIN ANALYZEвыполняет запрос и печатает план, который выбрала база. - Решение B-tree индекс по customer_id
Индекс — отдельная отсортированная структура: B-дерево значений
customer_id, каждое указывает на свои строки в таблице. - Проблема Оптовик с 250 000 заказов
Клиент
7— перекупщик с 250 000 заказов. - Решение Один индекс на две колонки
Составной индекс
(customer_id, created_at DESC)отсортирован сначала по клиенту, а внутри клиента — по дате. - Проблема Новый индекс не помогает вчерашнему отчёту
Ежедневный отчёт суммирует все заказы со вчерашнего дня по всем клиентам.
- Решение Индекс под запрос отчёта
Добавляем индекс только по
created_at. - Решение Покрывающий индекс не трогает таблицу
Даже с составным индексом за
statusиtotalкаждой из 20 строк приходится ходить в таблицу. - Проблема Семь индексов, и импорт ползёт
За год «на всякий случай» добавили ещё три индекса: по
status,totalи комментарию к заказу. - Решение Удаляем индексы, которые никто не читает
База считает, сколько раз использовался каждый индекс.
- Итоги Итоги
Начинайте с плана:
EXPLAIN ANALYZEи строкаRows Removed by Filterпоказывают, что база делает на самом деле.
Термины
- Индекс
- Отдельная отсортированная структура, обычно B-tree, чтобы база находила строки без полного сканирования таблицы. В составном индексе важен порядок колонок, а каждый индекс замедляет каждую запись.