PrepBro
← Назад к вопросам

Как устроены индексы?

2.0 Middle🔥 71 комментариев
#Базы данных и SQL

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI22 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

# Как устроены индексы?

Что такое индекс и зачем он нужен

Индекс — это структура данных в БД, которая ускоряет поиск и выборку данных. Без индекса база данных должна сканировать каждую строку таблицы (Sequential Scan) для поиска нужных данных. Индекс же позволяет быстро найти нужные строки.

1. B-Tree индекс (основной)

Структура B-Tree

Индекс на поле 'age':

                    [30]
                   /    \
              [10,20]   [40,50]
              /    |  \  /  |  \
            [5]  [15] [25][35][45][55]

B-Tree сбалансированное дерево, где:

  • Каждый узел содержит несколько ключей (не только 2 как в двоичном дереве)
  • Все листья на одном уровне (гарантирует O(log N))
  • Каждый узел указывает на строки в таблице

Поиск в B-Tree

Поиск age = 45:

1. Начало в root: [30]
   45 > 30 → идём вправо
   
2. Узел [40, 50]
   40 <= 45 <= 50 → находим здесь
   
3. Лист [45]
   Получаем указатель на строку в таблице
   
Количество операций: O(log N)
Для таблицы с 1 млн строк: ~20 операций вместо 1,000,000

CREATE INDEX в SQL

-- Создание простого индекса
CREATE INDEX idx_age ON users(age);

-- Составной индекс
CREATE INDEX idx_user_email ON users(email, age);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_email ON users(email);

-- Частичный индекс (на подмножество)
CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';

2. Как работает индекс на практике

Без индекса (Full Table Scan)

SELECT * FROM users WHERE age = 30;

-- БД сканирует всю таблицу:
Row 1: age=25 ✗
Row 2: age=30 ✓ FOUND
Row 3: age=45 ✗
Row 4: age=30 ✓ FOUND
... (проверяет все миллионы строк)

-- Время: O(N) - линейное, медленно

С индексом (Index Seek)

CREATE INDEX idx_age ON users(age);
SELECT * FROM users WHERE age = 30;

-- БД использует B-Tree индекс:
1. Navigates B-Tree: 30 находится в диапазоне
2. Gets pointer to rows where age=30
3. Fetches только нужные строки

-- Время: O(log N) - логарифмическое, быстро

3. EXPLAIN PLAN - как проверить индекс

Пример с PostgreSQL

-- Запрос БЕЗ индекса
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';

-- Результат:
Seq Scan on orders (cost=0.00..35000.00 rows=5000)
  Filter: (status = 'pending')
  Planning Time: 0.05ms
  Execution Time: 2500ms ❌ МЕДЛЕННО

-- Создаём индекс
CREATE INDEX idx_order_status ON orders(status);

-- Запрос С индексом
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';

-- Результат:
Index Seek using idx_order_status on orders (cost=0.42..150.00 rows=5000)
  Planning Time: 0.05ms
  Execution Time: 50ms ✓ БЫСТРО (в 50 раз!)

4. Составные (Composite) индексы

Как работает составной индекс

-- Индекс на (email, age)
CREATE INDEX idx_email_age ON users(email, age);

-- Структура индекса (B-Tree)
[alice@example.com, 25] → row 1
[alice@example.com, 30] → row 2
[bob@example.com, 35] → row 3
[charlie@example.com, 40] → row 4

-- Query 1: Использует индекс ✓
SELECT * FROM users WHERE email = 'alice@example.com';

-- Query 2: Использует индекс ✓
SELECT * FROM users WHERE email = 'alice@example.com' AND age = 25;

-- Query 3: НЕ использует индекс ✗ (ищет по второму полю)
SELECT * FROM users WHERE age = 25;
-- Нужен отдельный индекс на age

Правило: Order matters

-- Индекс (a, b, c) работает для:
-- WHERE a = ? ✓
-- WHERE a = ? AND b = ? ✓
-- WHERE a = ? AND b = ? AND c = ? ✓
-- WHERE a = ? AND c = ? (b skipped) ⚠ Partial
-- WHERE b = ? ✗ (не использует индекс)
-- WHERE c = ? ✗ (не использует индекс)

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

1. B-Tree (по умолчанию)

CREATE INDEX idx_name ON users(name);
-- Быстрый поиск по =, <, >, <=, >=, BETWEEN
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

2. Hash индекс

CREATE INDEX idx_email USING HASH ON users(email);
-- Только для equality (=)
SELECT * FROM users WHERE email = 'john@example.com'; -- ✓
SELECT * FROM users WHERE email LIKE 'john%'; -- ✗ Не работает

3. Bitmap индекс

-- Для полей с небольшим количеством уникальных значений
CREATE BITMAP INDEX idx_status ON orders(status);

-- status может быть: pending, completed, cancelled
-- Bitmap: каждое значение = один бит
-- Очень эффективно для фильтрации

4. BRIN индекс (PostgreSQL)

-- Для очень больших таблиц с отсортированными данными
CREATE INDEX idx_created_at USING BRIN ON orders(created_at);
-- Намного меньше памяти чем B-Tree

5. Full-Text индекс

-- Для текстового поиска
CREATE FULLTEXT INDEX idx_description ON products(description);
SELECT * FROM products WHERE MATCH(description) AGAINST('smartphone');

6. Когда индекс не используется

Проблема 1: Функция на индексируемом столбце

-- ✗ Индекс НЕ используется
SELECT * FROM users WHERE UPPER(name) = 'JOHN';

-- ✓ Индекс используется
SELECT * FROM users WHERE name = 'John';

-- ✓ Решение: функциональный индекс
CREATE INDEX idx_upper_name ON users(UPPER(name));

Проблема 2: Неправильное сравнение типов

-- user_id INT, но сравниваем со строкой
-- ✗ Индекс может не использоваться из-за неявного приведения типов
SELECT * FROM orders WHERE user_id = '123';

-- ✓ Правильно типизировать
SELECT * FROM orders WHERE user_id = 123;

Проблема 3: LIKE с wildcard в начале

-- ✗ Индекс НЕ используется
SELECT * FROM users WHERE name LIKE '%john%';

-- ✓ Индекс используется
SELECT * FROM users WHERE name LIKE 'john%';

-- Решение для полного текстового поиска
CREATE FULLTEXT INDEX idx_name_ft ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('john');

7. Производительность и затраты

Плюсы индексов

✓ Ускорение SELECT запросов (10-1000x)
✓ Ускорение JOIN операций
✓ Ускорение ORDER BY и GROUP BY

Минусы индексов

✗ Замедление INSERT/UPDATE/DELETE
  Нужно обновлять индекс при каждом изменении
✗ Использование дополнительной памяти
  Индекс может занимать 10-30% размера таблицы
✗ Сложность управления
  Нужно выбирать правильные колонки для индексирования

Пример: Затраты на UPDATE

-- Таблица users с 10 индексами
UPDATE users SET age = age + 1 WHERE id = 123;

-- БД должна обновить:
-- 1. Основную таблицу
-- 2. Все 10 индексов
-- Если без индексов UPDATE за 1ms, то с 10 индексами ~10ms

8. Стратегия индексирования

Что индексировать

-- 1. Первичный ключ (PRIMARY KEY)
CREATE INDEX idx_users_id ON users(id);
-- Уже создаётся автоматически

-- 2. Внешние ключи (FOREIGN KEY)
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Ускоряет JOIN'ы

-- 3. Колонки в WHERE условиях
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = ?;

-- 4. Колонки в ORDER BY
CREATE INDEX idx_created_at ON orders(created_at);
SELECT * FROM orders ORDER BY created_at DESC;

-- 5. Составные индексы для частых комбинаций
CREATE INDEX idx_user_email_status ON orders(user_id, email, status);

Что НЕ индексировать

-- 1. Логические колонки
CREATE INDEX idx_is_active ON users(is_active);
-- Плохо: только 2 значения (true/false), бесполезно
-- Mejor: Bitmap индекс если нужен

-- 2. Редко используемые колонки
-- Тратим память впустую

-- 3. Колонки с NULL значениями
-- Индекс может не включать NULL
SELECT * FROM users WHERE phone_number IS NULL;
-- Индекс не поможет

9. Мониторинг индексов

-- PostgreSQL: найти неиспользуемые индексы
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
-- Если idx_scan = 0 → индекс никогда не использовался
-- Можно удалить

-- Удалить неиспользуемый индекс
DROP INDEX idx_name;

10. В Java коде

@Entity
public class User {
    @Id
    @GeneratedValue
    private Long id;
    
    @Column
    @Index(name = "idx_email")
    private String email;
    
    @Column
    @Index(name = "idx_age")
    private int age;
    
    // Составной индекс
    @Table(indexes = {
        @Index(name = "idx_email_age", columnList = "email, age")
    })
}

Итого

Как устроены индексы:

  1. B-Tree структура — сбалансированное дерево для быстрого поиска O(log N)
  2. Листья указывают на строки — позволяет быстро получить данные
  3. Составные индексы — для поиска по нескольким полям
  4. Типы индексов — B-Tree, Hash, Bitmap, BRIN, Full-Text
  5. Затраты на обновление — INSERT/UPDATE/DELETE работают медленнее
  6. Стратегия — индексировать WHERE, ORDER BY, JOIN колонки
  7. Мониторинг — удалять неиспользуемые индексы
Как устроены индексы? | PrepBro