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

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

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

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

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

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

# Виды индексов в базах данных

Существует множество видов индексов, каждый оптимизирован для определенного типа запросов. Разберем основные и их применение.

1. B-Tree индекс (самый распространенный)

-- B-Tree индекс — сбалансированное дерево
-- Идеален для диапазонных запросов, сортировки
CREATE INDEX idx_email ON users(email) USING BTREE;

-- Хорошо для:
SELECT * FROM users WHERE email = "test@example.com";          -- O(log n)
SELECT * FROM users WHERE email LIKE "test%";                -- O(log n + k)
SELECT * FROM users WHERE age > 18 AND age < 65;            -- O(log n + k)
SELECT * FROM users ORDER BY email;                          -- O(1) сортировка

-- Плохо для:
SELECT * FROM users WHERE MATCH(email) AGAINST("example");  -- Full text не работает

2. Hash индекс

-- Hash индекс — хеш-таблица
-- Супер быстро для точного поиска, но не для диапазонов
CREATE INDEX idx_id ON users(id) USING HASH;

-- Отлично для:
SELECT * FROM users WHERE id = 42;                   -- O(1) ⚡
SELECT * FROM users WHERE user_hash = "abc123";     -- O(1)

-- Плохо для:
SELECT * FROM users WHERE id > 42;                   -- O(n) медленно!
SELECT * FROM users ORDER BY id;                     -- Нет упорядоченности
SELECT * FROM users WHERE id IN (1, 2, 3);           -- O(n)

3. Bitmap индекс

-- Bitmap индекс — для столбцов с низкой selectivity
-- Отлично для Boolean, Status, Gender (мало уникальных значений)
CREATE INDEX idx_status ON users(status) USING BITMAP;

-- Данные: 1 млн пользователей, статус: active/inactive
-- Bitmap требует 2 бита на пользователя вместо индекса B-Tree

-- Отлично для:
SELECT * FROM users WHERE status = "active";        -- Очень быстро
SELECT * FROM users WHERE status = "inactive";      -- Очень быстро
SELECT * FROM users WHERE status IN ("active", "inactive");  -- Быстро
SELECT * FROM users WHERE gender = "M" OR status = "active"; -- Быстро

-- Плохо для:
SELECT * FROM users WHERE email = "test@example.com";  -- Медленнее B-Tree

4. GiST (Generalized Search Tree)

-- GiST — для геопространственных данных
-- Поддерживает любые структуры данных
CREATE INDEX idx_location ON restaurants USING GIST(location);

-- Отлично для географических запросов:
SELECT * FROM restaurants 
WHERE location <@ '((0,0),(1,1))'::box;              -- Точки в прямоугольнике

SELECT * FROM restaurants 
WHERE ST_Distance(location, ST_Point(55.75, 37.62)) < 1000;  -- Расстояние

-- Другие типы с GiST:
CREATE INDEX idx_ip_range ON networks USING GIST(ip_range);  -- IP диапазоны
CREATE INDEX idx_document ON documents USING GIST(document);  -- JSON документы

5. BRIN (Block Range Index)

-- BRIN — для больших таблиц с последовательными данными
-- Очень компактный, отлично для временных рядов
-- Индекс: 100 Mb для таблицы 100 Gb!
CREATE INDEX idx_timestamp ON logs USING BRIN(created_at);

-- 1 млрд записей логов
SELECT COUNT(*) FROM logs WHERE created_at > "2024-01-01";  -- Быстро

-- Отлично для:
-- 1. Таблицы в порядке возрастания (время, ID)
-- 2. Очень большие таблицы (терабайты)
-- 3. Когда важна экономия памяти

-- Плохо для:
SELECT * FROM logs WHERE user_id = 42;  -- Не поможет

6. Полнотекстовый индекс (Full-text search)

-- Полнотекстовый индекс для поиска по тексту
CREATE INDEX idx_fulltext ON articles USING GIN(to_tsvector('english', content));

-- Отлично для поиска слов в тексте:
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database');

-- Вместо медленного LIKE:
SELECT * FROM articles WHERE content LIKE "%database%";  -- Медленно (O(n))

-- С индексом: очень быстро
SELECT * FROM articles WHERE content @@ 'database';

-- Поиск фраз:
SELECT * FROM articles WHERE content @@ '"machine learning"';

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

-- Индекс на несколько столбцов
CREATE INDEX idx_user_email_status ON users(email, status);

-- Отлично работает для:
SELECT * FROM users WHERE email = "test@example.com" AND status = "active";

-- Частично работает (leftmost prefix rule):
SELECT * FROM users WHERE email = "test@example.com";  -- Использует индекс

-- НЕ работает:
SELECT * FROM users WHERE status = "active";  -- Индекс не используется!

-- Правило: первый столбец в индексе должен быть в WHERE!

8. Partial/Filtered индекс

-- Индекс только на часть данных
CREATE INDEX idx_active_users ON users(email) WHERE status = "active";

-- Больше не индексируем неактивных пользователей
-- Индекс: 10% от обычного размера

-- Отлично для:
SELECT * FROM users WHERE email = "test@example.com" AND status = "active";  -- Быстро

-- Плохо для:
SELECT * FROM users WHERE status = "inactive";  -- Индекс не поможет

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

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

-- Также выполняет функции обычного индекса для поиска
SELECT * FROM users WHERE email = "test@example.com";  -- Быстро

-- Но не позволяет дублировать значения:
INSERT INTO users (email) VALUES ("test@example.com");  -- OK
INSERT INTO users (email) VALUES ("test@example.com");  -- ERROR! Дубль

10. Обратный индекс (Inverted Index)

# Используется в поисковых системах (Elasticsearch, Solr)
# Маппирует слова → документы, которые их содержат

# Документ 1: "Python programming language"
# Документ 2: "JavaScript is a programming language"
# Документ 3: "C++ programming"

# Inverted Index:
# Python → [Doc1]
# programming → [Doc1, Doc2, Doc3]
# language → [Doc1, Doc2]
# JavaScript → [Doc2]
# C++ → [Doc3]

# Поиск "programming" находит все 3 документа за O(1)

11. Кластеризованный индекс (Clustered Index)

-- Первичный ключ в большинстве БД — это кластеризованный индекс
-- Данные таблицы физически отсортированы по этому индексу
CREATE TABLE users (
    id INT PRIMARY KEY,  -- Кластеризованный индекс
    name VARCHAR(100)
);

-- Таблица отсортирована по ID на диске:
-- ID 1 → блок 0
-- ID 2 → блок 0
-- ID 3 → блок 1
-- ID 4 → блок 1

-- Поиск по ID очень быстро (соседние данные)
SELECT * FROM users WHERE id = 42;  -- Быстро

-- В таблице может быть только ОДН кластеризованный индекс!

12. Некластеризованный индекс (Non-Clustered Index)

-- Отдельный индекс, указывающий на основные данные
CREATE INDEX idx_email ON users(email);  -- Некластеризованный

-- Индекс содержит:
-- email → указатель на строку в таблице

-- При поиске:
SELECT * FROM users WHERE email = "test@example.com";
-- 1. Найти в индексе email (быстро)
-- 2. Перейти по указателю в таблицу (медленнее)

-- Может быть много некластеризованных индексов

Сравнительная таблица

Тип индекса      | Точный поиск | Диапазон | Сортировка | Размер | Случай использования
------------------------------------------------------------------------------------
B-Tree          | O(log n)     | O(log n) | O(1)       | Большой | 90% случаев
Hash            | O(1)         | O(n)     | Нет        | Средний | PRIMARY KEY
Bitmap          | O(1)         | O(n)     | Нет        | Малый  | Status, Gender
GiST            | Зависит      | Да       | Зависит    | Большой | Geographic data
BRIN            | O(n)         | O(log n) | Да         | Малый  | Большие таблицы
Full-text       | Phrasе       | Фразы    | Нет        | Большой | Text search
Partial         | O(log n)     | O(log n) | O(1)       | Малый  | Часто фильтруемые

Выбор индекса для разных сценариев

# Таблица users: 10 млн строк
class User(models.Model):
    id = models.AutoField()              # PRIMARY KEY — B-Tree кластеризованный
    email = models.EmailField()          # Точный поиск → B-Tree
    status = models.CharField()          # 5 возможных значений → Bitmap
    created_at = models.DateTimeField()  # Временной ряд → BRIN
    bio = models.TextField()             # Full-text search → GIN полнотекстовый
    location = models.PointField()       # Geo запросы → GiST
    
    class Meta:
        indexes = [
            models.Index(fields=['email']),  # B-Tree
            models.Index(fields=['status']),  # Bitmap
            models.Index(fields=['created_at']),  # BRIN
            # Partial index
            models.Index(fields=['email'], condition=Q(status='active')),
        ]

Оптимизация выбора индекса

# Используй EXPLAIN ANALYZE для выбора типа
from django.db import connection
from django.db.models import Q

cursor = connection.cursor()

# Проверим план выполнения
query = "EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';"
cursor.execute(query)
for row in cursor.fetchall():
    print(row)

# Результат покажет, какой индекс используется
# Seq Scan — индекса нет
# Index Scan — индекс используется

Заключение

Основные виды индексов:

  1. B-Tree — универсальный, используется в 90% случаев
  2. Hash — для точного поиска по PRIMARY KEY
  3. Bitmap — для низкой selectivity (Boolean, Status)
  4. BRIN — для огромных таблиц с последовательными данными
  5. GiST — для географических и сложных данных
  6. Full-text — для поиска по тексту
  7. Partial — для часто фильтруемых подмножеств
  8. Составной — для запросов с несколькими условиями

Правило: Профилируй запросы через EXPLAIN ANALYZE и выбирай индекс для конкретного сценария.