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

Что такое индекс?

1.2 Junior🔥 231 комментариев
#Базы данных (SQL)

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

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

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

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

Индекс — это структура данных в СУБД, которая ускоряет поиск и выборку данных из таблицы. Если таблица — это книга, то индекс — это оглавление. Без индекса база данных ищет каждую строку последовательно (full scan), с индексом — находит нужные данные за логарифмическое время.

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

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

Таблица users (1,000,000 записей):
id | name | email | age
---|------|-------|----
1  | John | john@example.com | 30
2  | Jane | jane@example.com | 25
...
1000000 | Bob | bob@example.com | 35

Запрос: SELECT * FROM users WHERE id = 999999

Без индекса: БД проверяет ВСЕ 1,000,000 записей
Время: O(n) — наихудший случай 1,000,000 операций

С индексом (B-tree)

Индекс на id (B-tree структура):
           [500000]
          /        \
     [250000]     [750000]
     /      \    /      \
  [125k]  [375k] [625k] [875k]
   / \     / \    / \    / \
  ...

Запрос: SELECT * FROM users WHERE id = 999999
С индексом: БД использует бинарный поиск
Время: O(log n) ≈ 20 операций для 1,000,000 записей

Ускорение: в 50,000 раз!

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

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

Уникально идентифицирует каждую строку. Автоматически создаётся как индекс.

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

-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

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

Гарантирует уникальность значений в столбце.

-- Создание уникального индекса
CREATE UNIQUE INDEX idx_email ON users(email);

-- Или через constraintконструкцию
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE(email);

-- Теперь этот запрос будет ошибкой:
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
INSERT INTO users (name, email) VALUES ('Jane', 'john@example.com');  -- ERROR

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

Ускоряет поиск, фильтрацию и сортировку по столбцу.

-- Создание обычного индекса
CREATE INDEX idx_name ON users(name);

-- Теперь этот запрос будет быстрым:
SELECT * FROM users WHERE name = 'John';  -- O(log n)

-- Без индекса был бы full scan
SELECT * FROM users WHERE name = 'John';  -- O(n) - медленно!

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

Индекс по нескольким столбцам. Порядок столбцов важен!

-- Создание составного индекса
CREATE INDEX idx_user_city ON users(city, age);

-- Быстрые запросы:
SELECT * FROM users WHERE city = 'Moscow';  -- Использует индекс
SELECT * FROM users WHERE city = 'Moscow' AND age > 30;  -- Использует индекс

-- Не использует индекс (неправильный порядок):
SELECT * FROM users WHERE age > 30;  -- Full scan!

5. Full-Text Index

Для полнотекстового поиска в больших текстовых полях.

-- Создание полнотекстового индекса
CREATE FULLTEXT INDEX idx_content ON articles(title, body);

-- Полнотекстовый поиск (быстрее, чем LIKE)
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('Python' IN BOOLEAN MODE);

-- Вместо медленного:
SELECT * FROM articles WHERE title LIKE '%Python%' OR body LIKE '%Python%';

6. Partial/Conditional Index

Индекс только для подмножества строк (PostgreSQL, SQLite).

-- Создание частичного индекса
CREATE INDEX idx_active_users ON users(name) WHERE active = true;

-- Полезно для больших таблиц, где индексируемое подмножество мало
-- Экономит дисковое пространство и ускоряет INSERT/UPDATE

Структуры данных для индексов

B-Tree (самый распространённый)

Большинство баз данных используют B-Tree:
- PostgreSQL: по умолчанию
- MySQL (InnoDB): по умолчанию
- SQLite: по умолчанию

Преимущества:
✓ Хорошо для range queries (BETWEEN, >, <)
✓ Хорошо сбалансирован
✓ O(log n) для поиска, вставки, удаления

Hash Index

Для точного поиска (equality):
- Быстрее всех для WHERE column = value
- НЕ работает для range queries
- Не поддерживает BETWEEN, >, <

Применение:
- Поиск по точному значению
- Иногда для JOIN операций

GiST (Generalized Search Tree)

Для географических и геометрических данных:
CREATE INDEX idx_location ON places USING GIST(geo_location);

-- Быстрый поиск мест в радиусе 5км:
SELECT * FROM places 
WHERE geo_location <-> point(55.7558, 37.6173) < 5;

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

import sqlite3
import time
from datetime import datetime

# Создание таблицы
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL,
        age INTEGER
    )
''')

# Добавление 100,000 записей
print("Добавление 100,000 записей...")
for i in range(1, 100001):
    cursor.execute(
        'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
        (f'User{i}', f'user{i}@example.com', 20 + (i % 50))
    )
conn.commit()

# Тест 1: Поиск БЕЗ индекса
print("\n=== Поиск БЕЗ индекса ===")
start_time = time.time()
for _ in range(100):
    cursor.execute('SELECT * FROM users WHERE email = ?', ('user50000@example.com',))
    result = cursor.fetchone()
no_index_time = time.time() - start_time
print(f"Время: {no_index_time:.4f} сек")

# Создание индекса
print("\nСоздание индекса на столбец email...")
cursor.execute('CREATE INDEX idx_email ON users(email)')
conn.commit()

# Тест 2: Поиск С индексом
print("\n=== Поиск С индексом ===")
start_time = time.time()
for _ in range(100):
    cursor.execute('SELECT * FROM users WHERE email = ?', ('user50000@example.com',))
    result = cursor.fetchone()
index_time = time.time() - start_time
print(f"Время: {index_time:.4f} сек")

print(f"\nУскорение: {no_index_time / index_time:.1f}x раз")

conn.close()

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

Создавай индекс для:

# 1. Часто используемые WHERE условия
CREATE INDEX idx_status ON orders(status);  # WHERE status = 'completed'

# 2. Сортировка
CREATE INDEX idx_created_at ON posts(created_at DESC);  # ORDER BY created_at DESC

# 3. JOIN условия
CREATE INDEX idx_user_id ON comments(user_id);  # FROM comments WHERE user_id = ?

# 4. Первичные ключи и уникальные поля
CREATE UNIQUE INDEX idx_username ON users(username);

# 5. Большие таблицы (> 10,000 строк)
CREATE INDEX idx_large_table_filter ON large_table(filter_column);

НЕ создавай индекс для:

# 1. Маленькие таблицы (< 1,000 строк)
# Full scan может быть быстрее

# 2. Столбцы с низкой селективностью
CREATE INDEX idx_gender ON users(gender);  # Только M/F — мало пользы

# 3. Часто обновляемые столбцы
CREATE INDEX idx_view_count ON posts(view_count);  # Много UPDATE операций

# 4. LIKE с подстановкой в начале
SELECT * FROM users WHERE name LIKE '%john%';  # Индекс не поможет
SELECT * FROM users WHERE name LIKE 'john%';   # Индекс поможет

Анализ производительности

-- PostgreSQL: EXPLAIN план
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

-- Результат:
-- Index Scan using idx_email on users  (cost=0.29..2.51 rows=1)
--   Index Cond: (email = 'test@example.com')

-- MySQL: EXPLAIN
EXPLAIN
SELECT * FROM users WHERE email = 'test@example.com'\G

-- Результат:
-- *************************** 1. row ***************************
--   possible_keys: idx_email
--   key: idx_email
--   key_len: 50
--   rows: 1

Заключение

Индексы — это мощный инструмент для оптимизации производительности базы данных. Правильное использование индексов может ускорить запросы в тысячи раз. Однако, каждый индекс имеет стоимость: замедление INSERT/UPDATE/DELETE и увеличение дискового пространства. Поэтому индексы следует создавать осознанно, на основе анализа реальных запросов и нагрузки на систему.