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

Что такое индексы в БД?

1.0 Junior🔥 291 комментариев
#Базы данных и SQL

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

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

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

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

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

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

Без индекса (Full Table Scan):        С индексом (B-tree):

Поиск id=1000:                       Поиск id=1000:
Читаем каждую строку:                Быстрый поиск по структуре B-tree:
┌──────┐                              ┌────────┐
│ id 1 │ ❌                            │ 500    │  (разделяет данные)
│ id 2 │ ❌                            /        \
│ id 3 │ ❌                           /          \
...                                  ┌──────┐  ┌──────┐
│id1000│ ✅ Found!                  │1-500 │  │500+ │
                                     │      │  │      │
                                     └──────┘  └──────┘
                                              ↓
                                         Найдено за O(log n)

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

1. Primary Key (Первичный ключ)

Уникально идентифицирует каждую строку:

CREATE TABLE users (
    id INT PRIMARY KEY,     -- Индекс по умолчанию
    name VARCHAR(255)
);

-- В SQL:
ALTER TABLE users ADD PRIMARY KEY (id);

-- Характеристики:
-- - Уникальный (не может быть дублей)
-- - НЕ может быть NULL
-- - Только один на таблицу
-- - Автоматически создает индекс

2. Unique Index (Уникальный индекс)

Гарантирует уникальность значений:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,   -- Уникальный индекс
    phone VARCHAR(20) UNIQUE
);

-- Или:
CREATE UNIQUE INDEX idx_email ON users(email);

-- Отличие от PRIMARY KEY:
-- - Может быть NULL (и может быть несколько NULL)
-- - Может быть несколько на таблицу

3. Regular Index (Обычный индекс)

Ускоряет поиск, но не требует уникальности:

CREATE TABLE posts (
    id INT PRIMARY KEY,
    user_id INT,
    title VARCHAR(255),
    created_at TIMESTAMP
);

-- Создаем индексы для часто используемых колонок
CREATE INDEX idx_user_id ON posts(user_id);
CREATE INDEX idx_created_at ON posts(created_at);

-- Использование:
SELECT * FROM posts WHERE user_id = 1;     -- Быстро (есть индекс)
SELECT * FROM posts WHERE title LIKE '%php%';  -- Медленно (нет индекса)

4. Composite/Multi-column Index (Составной индекс)

Индекс по нескольким колонкам:

-- Для часто используемого условия
CREATE INDEX idx_user_status ON posts(user_id, status);

-- Быстро (покрывающий индекс)
SELECT * FROM posts WHERE user_id = 1 AND status = 'published';

-- Медленно (неправильный порядок)
SELECT * FROM posts WHERE status = 'published';
-- Индекс начинается с user_id, а мы ищем по status

5. Full-Text Index

Для полнотекстового поиска:

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content LONGTEXT,
    FULLTEXT INDEX idx_search (title, content)
);

-- Использование
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('php' IN BOOLEAN MODE);

-- Намного быстрее, чем LIKE '%php%'

6. Spatial Index (PostgreSQL, MySQL)

Для геопространственных данных:

CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    coordinates POINT,
    SPATIAL INDEX idx_location (coordinates)  -- PostgreSQL
);

-- Поиск ближайших мест
SELECT * FROM locations 
WHERE ST_Distance(coordinates, POINT(-74.0, 40.7)) < 1000;

Когда создавать индексы?

// WHERE условия - ОБЯЗАТЕЛЬНО индексируй
SELECT * FROM users WHERE id = 1;          // ✅ Primary key index
SELECT * FROM users WHERE email = '...';   // ✅ Unique index
SELECT * FROM posts WHERE user_id = 1;     // ✅ Foreign key index

// JOIN условия - ОБЯЗАТЕЛЬНО индексируй
SELECT * FROM posts 
JOIN users ON posts.user_id = users.id;    // ✅ Index on both

// ORDER BY и LIMIT - индексируй если нужна скорость
SELECT * FROM posts 
ORDER BY created_at DESC 
LIMIT 10;                                    // ✅ Index on created_at

// GROUP BY - индексируй если часто используется
SELECT user_id, COUNT(*) 
FROM posts 
GROUP BY user_id;                           // ✅ Index on user_id

// LIKE с wildcard в начале - индекс НЕ поможет
SELECT * FROM posts 
WHERE title LIKE '%php%';                   // ❌ Full-text нужен

// IN список - может использовать индекс
SELECT * FROM posts 
WHERE user_id IN (1, 2, 3);                // ✅ Index helpful

Trade-offs индексов

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

✅ Значительно ускоряет SELECT (10-1000x)
✅ Ускоряет JOINs
✅ Помогает GROUP BY и ORDER BY
✅ Помогает WHERE условиям

Недостатки

❌ Замедляет INSERT (нужно обновить индекс)
❌ Замедляет UPDATE
❌ Замедляет DELETE
❌ Занимает дополнительное место на диске (часто 20-30% от таблицы)
❌ Нужно поддерживать (заново индексировать)

Создание и управление индексами

SQL

-- Создание
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_multi ON posts(user_id, status);

-- Удаление
DROP INDEX idx_name;

-- Просмотр индексов
SHOW INDEXES FROM users;
SHOW INDEX FROM users;  -- MySQL
SELECT * FROM information_schema.statistics WHERE table_name='users';

-- Перестроение (оптимизация)
OPTIMIZE TABLE users;  -- MySQL
REINDEX TABLE users;   -- PostgreSQL

-- Отключение (для больших INSERT)
DISABLE KEYS;  -- MySQL
-- Много INSERT
ENABLE KEYS;

Laravel

// Миграция
Schema::create('posts', function (Blueprint $table) {
    $table->id();                           // Primary key
    $table->foreignId('user_id')->index();  // Index
    $table->string('title')->unique();      // Unique index
    $table->string('slug')->unique();       // Unique index
    $table->timestamp('created_at')->index();  // Index
    
    // Составной индекс
    $table->index(['user_id', 'status']);
});

// Raw index
DB::statement('CREATE FULLTEXT INDEX idx_search ON posts(title, content)');

Оптимизация запросов

-- 1. EXPLAIN показывает план выполнения
EXPLAIN SELECT * FROM posts WHERE user_id = 1;

Вывод:
id | select_type | table | type | key         | key_len | rows | Extra
1  | SIMPLE      | posts | ref  | idx_user_id | 4       | 1234 | NULL
                                ↑ Используется индекс!

-- 2. Плохой план (Full scan)
EXPLAIN SELECT * FROM posts WHERE title = 'PHP';

Вывод:
id | select_type | table | type  | key  | rows
1  | SIMPLE      | posts | ALL   | NULL | 50000  ← FULL SCAN!
                              ↑ Нет индекса - сканирует все 50000 строк!

-- 3. Добавляем индекс
CREATE INDEX idx_title ON posts(title);

-- Теперь быстро:
EXPLAIN SELECT * FROM posts WHERE title = 'PHP';
key: idx_title ✅

Практические советы

1. Индексируй Foreign Keys

ALTER TABLE posts ADD INDEX(user_id);  -- Foreign key
ALTER TABLE posts ADD INDEX(category_id);

2. Правильный порядок в составном индексе

-- Часто используется: WHERE user_id = 1 AND status = 'published'
CREATE INDEX idx_multi ON posts(user_id, status);
-- Правильно! user_id первый, потом status

-- Это НЕ поможет для WHERE status = 'published'
-- Индекс начинается с user_id!

-- Для обоих условий используй отдельные индексы
CREATE INDEX idx_user ON posts(user_id);
CREATE INDEX idx_status ON posts(status);

3. Не создавай слишком много индексов

❌ ПЛОХО: 20+ индексов
✅ ХОРОШО: 3-5 стратегических индексов

Прежде чем создавать индекс:
1. Профилируй запросы (EXPLAIN)
2. Проверь, используется ли текущий индекс
3. Удали неиспользуемые индексы

4. Мониторь размер индексов

-- PostgreSQL
SELECT tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_indexes
JOIN pg_class ON pg_class.relname = indexname
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL
SHOW TABLE STATUS WHERE Name='posts';

Итог

Индексы - это критический инструмент оптимизации:

📊 PRIMARY KEY       - Всегда есть, уникален
🔍 Regular INDEX     - Для WHERE условий
✨ UNIQUE INDEX      - Для email, slug
🔗 COMPOSITE INDEX   - Для multi-column queries
📝 FULLTEXT INDEX    - Для поиска текста
🗺️ SPATIAL INDEX     - Для геоданных

Правило большого пальца:
Эсли запрос медленный → добавь индекс на WHERE колонку
Что такое индексы в БД? | PrepBro