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

Типы индексов в базах данных

Индексы — это структуры данных, которые ускоряют поиск данных в таблице. Они работают как оглавление в книге: вместо чтения всех страниц, мы сразу переходим на нужную.

1. PRIMARY KEY (Первичный индекс)

Назначение: уникальная идентификация записи

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

Характеристики:

• ✅ Уникален — одна запись на одно значение
• ✅ Не может быть NULL — всегда должно быть значение
• ✅ Автоматически создаёт B-Tree индекс
• ✅ Может быть составной (из нескольких полей)

-- Составной первичный ключ
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

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

Назначение: гарантировать уникальность значений, но не идентификацию

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

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

Особенности:

• ✅ Может быть несколько на таблицу
• ✅ Может содержать NULL (обычно несколько NULL допускаются)
• ✅ Ускоряет поиск по уникальным полям

3. INDEX (Обычный индекс / B-Tree)

Назначение: ускорение поиска по колонке

CREATE INDEX idx_name ON users(name);

-- Составной индекс (несколько колонок)
CREATE INDEX idx_name_email ON users(name, email);

Когда использовать:

• Поиск по колонке в WHERE
• Условия LIKE (если левая часть)
• Сортировка ORDER BY
• Условия сравнения (=, <, >, <=, >=, BETWEEN)

-- Быстрый поиск благодаря индексу
SELECT * FROM users WHERE name = Alice;

4. FOREIGN KEY (Индекс внешнего ключа)

Назначение: связь между таблицами и обеспечение целостности данных

CREATE TABLE posts (
    id INT PRIMARY KEY,
    user_id INT,
    title VARCHAR(200),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Характеристики:

• ✅ Обеспечивает referential integrity (ссылочную целостность)
• ✅ Автоматически создаёт индекс на user_id
• ✅ Позволяет каскадное удаление/обновление

-- С каскадными действиями
FOREIGN KEY (user_id) REFERENCES users(id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE

5. FULLTEXT (Полнотекстовый индекс)

Назначение: быстрый поиск по тексту

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (content)
);

-- Поиск
SELECT * FROM articles 
WHERE MATCH(content) AGAINST(python IN BOOLEAN MODE);

Особенности:

• ✅ Работает только в MySQL/MariaDB
• ✅ Ускоряет полнотекстовый поиск
• ✅ Игнорирует стоп-слова (the, a, и т.д.)
• ✅ Поддерживает булевы операторы (+, -, ", etc.)

6. SPATIAL (Пространственный индекс)

Назначение: работа с географическими координатами и геометрическими фигурами

CREATE TABLE locations (
    id INT PRIMARY KEY,
    place_name VARCHAR(100),
    coordinates POINT,
    SPATIAL INDEX sp_coords (coordinates)
);

-- Поиск по расстоянию
SELECT * FROM locations 
WHERE ST_Distance_Sphere(coordinates, POINT(55.75, 37.62)) < 1000;

Используется для:

• Поиск по GPS координатам
• Поиск близких объектов
• Геолокация

7. HASH индекс

Назначение: очень быстрый поиск по точному совпадению

CREATE INDEX idx_hash ON users USING HASH (id);

Характеристики:

• ✅ Превосходит B-Tree только для точного поиска (=)
• ❌ Не работает для диапазонных поиск (>, <, BETWEEN)
• ❌ Не поддерживает ORDER BY
• ❌ В памяти только (Memory engine в MySQL)

Сравнение:

-- B-Tree индекс (универсален)
SELECT * FROM users WHERE age > 25 AND age < 35;

-- HASH индекс (только для =)
SELECT * FROM users WHERE id = 5;

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

Назначение: оптимизация запросов с несколькими условиями

CREATE INDEX idx_user_date ON posts(user_id, created_at);

-- Индекс эффективен для этого запроса
SELECT * FROM posts 
WHERE user_id = 1 AND created_at > 2024-01-01;

Правило левого префикса (Left-most prefix rule):

-- Индекс (user_id, created_at) работает для:
WHERE user_id = 1  ✅
WHERE user_id = 1 AND created_at = 2024-01-01  ✅

-- Но НЕ работает для:
WHERE created_at = 2024-01-01  ❌ (пропущен user_id)

9. Частичный индекс (Partial Index)

Назначение: индекс только для подмножества строк

-- PostgreSQL
CREATE INDEX idx_active_users ON users(name) 
WHERE is_active = true;

-- MySQL
CREATE INDEX idx_published ON posts(id) 
WHERE status = published;

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

• ✅ Меньше размер индекса
• ✅ Быстрее обновления
• ✅ Меньше IO операций

10. Индексированные представления (Indexed View)

Назначение: предварительно вычисленные результаты запроса

-- SQL Server
CREATE VIEW vw_user_stats AS
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id;

CREATE UNIQUE CLUSTERED INDEX idx_user_stats 
ON vw_user_stats(user_id);

Практический пример: оптимизация таблицы

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(100),
    age INT,
    created_at TIMESTAMP,
    is_active BOOLEAN,
    
    -- Обычные индексы для частых поисков
    INDEX idx_name (name),
    INDEX idx_created_at (created_at),
    
    -- Составной индекс для популярного запроса
    INDEX idx_age_active (age, is_active),
    
    -- Частичный индекс для активных пользователей
    INDEX idx_active_users (name) 
    WHERE is_active = true,
    
    -- Внешний ключ
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

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

-- ❌ Индекс будет неэффективен или бесполезен:
- Колонки с низкой избирательностью (много одинаковых значений)
- Колонки с NULL значениями (для HASH индекса)
- Очень маленькие таблицы (< 1000 строк)
- Колонки, которые часто обновляются
- Функции в WHERE (например: WHERE YEAR(date) = 2024)

Best Practices

1. Профилируй перед оптимизацией — используй EXPLAIN для анализа запросов
2. Не переусложняй — 3-4 индекса на таблицу, как правило, достаточно
3. Мониторь размер — индексы занимают место и замедляют INSERT/UPDATE
4. Регулярно анализируй — удаляй неиспользуемые индексы
5. Составные индексы — порядок колонок важен

Правильное использование индексов — критично для производительности базы данных.