Как работает индекс в БД?

Как работает индекс в БД?

Индекс в базе данных — это структура данных, которая ускоряет поиск, фильтрацию и сортировку данных. Это один из самых важных инструментов для оптимизации производительности БД.

Основной концепт

Без индекса база данных должна просканировать ВСЕ строки таблицы (full table scan), чтобы найти нужные данные. С индексом она может быстро найти нужные строки.

Без индекса (поиск по name):
Таблица: id | name    | age
          1 | Alice   | 25
          2 | Bob     | 30
          3 | Charlie | 35
          4 | David   | 40

Запрос: SELECT * FROM users WHERE name = 'Bob'
БД сканирует ВСЕ 4 строки, чтобы найти Bob
Время: O(n) — линейное время

С индексом по name:
Индекс: Alice -> 1
        Bob -> 2      (очень быстрый поиск)
        Charlie -> 3
        David -> 4

Запрос: SELECT * FROM users WHERE name = 'Bob'
БД ищет в индексе, находит Bob -> 2, затем берёт row 2
Время: O(log n) или даже O(1) — очень быстро

Структура данных индекса

Наиболее распространённая структура — B-Tree (B дерево):

B-Tree индекс (сбалансированное дерево):

               [30, 50]
              /    |    \
         /         |         \
    [10, 20]   [35, 45]   [60, 70]
    /  |  \    /  |  \   /  |  \
  1   12 22  33 42 52  63 71 81

Особенности B-Tree:

• Всегда сбалансировано (разница глубины максимум 1)
• Узлы содержат множество ключей
• Каждое дерево содержит указатели на строки таблицы
• Очень эффективно для range queries (WHERE age > 25 AND age < 35)

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

-- Одноколоночный индекс
CREATE INDEX idx_users_name ON users(name);

-- Индекс на несколько колонок (composite index)
CREATE INDEX idx_users_name_age ON users(name, age);

-- Уникальный индекс (гарантирует уникальность)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Индекс с WHERE условием (partial index)
CREATE INDEX idx_users_active ON users(id) WHERE status = 'active';

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

1. Primary Key Index

CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- автоматически создаёт индекс
    name VARCHAR(100)
);

Индекс по primary key уникален и очень быстрый для поиска по id.

2. Unique Index

CREATE TABLE users (
    email VARCHAR(255) UNIQUE  -- создаёт уникальный индекс
);

Гарантирует, что значение встречается только один раз.

3. Composite (Multi-column) Index

CREATE INDEX idx_users_city_age ON users(city, age);

Порядок колонок очень важен для производительности.

4. Partial Index

CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';

Индексирует только строки, которые удовлетворяют условию. Экономит место.

5. Full-text Index (для поиска текста)

CREATE INDEX idx_users_bio ON users USING GIN (bio);
SELECT * FROM users WHERE bio @@ to_tsquery('golang');

6. Hash Index (для точных совпадений)

CREATE INDEX idx_users_hash ON users USING HASH(email);

Быстрее для точного поиска (=), но не работает для диапазонов.

Как БД выбирает использовать индекс

Это делает Query Optimizer:

-- Этот запрос использует индекс по email
SELECT * FROM users WHERE email = 'test@example.com';

-- Этот может не использовать индекс (зависит от селективности)
SELECT * FROM users WHERE status = 'active';
-- Если 90% пользователей активны, БД предпочтёт full scan

-- Этот точно использует индекс (очень селективно)
SELECT * FROM users WHERE id = 12345;

Практический пример на GO

import "database/sql"

type User struct {
    ID    int
    Name  string
    Email string
    Age   int
}

// Миграция создания таблицы с индексами
func CreateUsersTable(db *sql.DB) error {
    query := `
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            age INT
        );
        
        CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);
        CREATE INDEX IF NOT EXISTS idx_users_age ON users(age);
        CREATE INDEX IF NOT EXISTS idx_users_name_age ON users(name, age);
    `
    _, err := db.Exec(query)
    return err
}

// Запрос использует индекс по email
func GetUserByEmail(db *sql.DB, email string) (*User, error) {
    var u User
    err := db.QueryRow(
        "SELECT id, name, email, age FROM users WHERE email = $1",
        email,
    ).Scan(&u.ID, &u.Name, &u.Email, &u.Age)
    return &u, err
}

// Запрос может использовать индекс idx_users_name_age
func GetUsersInAgeRange(db *sql.DB, name string, minAge, maxAge int) ([]User, error) {
    var users []User
    rows, err := db.Query(
        "SELECT id, name, email, age FROM users WHERE name = $1 AND age BETWEEN $2 AND $3",
        name, minAge, maxAge,
    )
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    for rows.Next() {
        var u User
        rows.Scan(&u.ID, &u.Name, &u.Email, &u.Age)
        users = append(users, u)
    }
    return users, rows.Err()
}

Когда индекс помогает

Цена индекса

Плюсы:

• Ускоряет SELECT запросы
• Ускоряет WHERE, JOIN, ORDER BY, GROUP BY
• Уникальные индексы предотвращают дубликаты

Минусы:

• Занимает дополнительное место на диске
• INSERT, UPDATE, DELETE медленнее (нужно обновить индекс)
• Слишком много индексов замедляют операции записи
• Нужно периодически поддерживать (VACUUM, ANALYZE)

Анализ индексов

EXPLAIN для просмотра плана выполнения:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Output может быть:
-- Seq Scan on users (cost=0.00..35.50 rows=1)
--   Filter: (email = 'test@example.com')
-- (2 rows)

-- vs с индексом:
-- Index Scan using idx_users_email on users
--   Index Cond: (email = 'test@example.com')

ANALYZE собирает статистику:

ANALYZE users;  -- БД пересчитывает статистику для оптимизации

Правила для хороших индексов

1. Индексируй колонки в WHERE условиях

-- Часто используется в WHERE
CREATE INDEX idx_status ON orders(status);

1. Порядок в composite индексе важен

-- Хорошо если часто query: WHERE name = ? AND age = ?
CREATE INDEX idx_name_age ON users(name, age);

-- Плохо для того же query
CREATE INDEX idx_age_name ON users(age, name);  -- обратный порядок

1. Не переиндексируй

// Плохо — 10 индексов замедляют inserts
CREATE INDEX idx1 ON users(col1);
CREATE INDEX idx2 ON users(col2);
// ...
CREATE INDEX idx10 ON users(col10);

// Лучше — 2-3 хорошо спланированных индекса
CREATE INDEX idx_status_created ON orders(status, created_at);
CREATE INDEX idx_user_id ON orders(user_id);

1. Удаляй неиспользуемые индексы

DROP INDEX IF EXISTS idx_unused_column;

Вывод

Индекс в БД:

• Структура данных (обычно B-Tree) для быстрого поиска
• Огромно ускоряет SELECT, но замедляет INSERT/UPDATE/DELETE
• Требует дополнительное место на диске
• Выбирает использование Query Optimizer на основе селективности
• Критичен для производительности высоконагруженных систем
• Нужно правильно планировать и поддерживать

Мастерское использование индексов — это суперспособность backend разработчика.