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

Виды индексов в SQL

Индексы — это структуры данных, которые ускоряют поиск в таблице. Без них БД ищет записи методом полного сканирования (O(n)). Индекс снижает время до O(log n) или лучше.

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

Уникальный идентификатор записи, автоматически индексируется:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    email VARCHAR(255)
);

-- Поиск по первичному ключу — очень быстро
SELECT * FROM users WHERE id = 5;  -- O(log n)

-- Нельзя вставить дублирующийся id
INSERT INTO users (id, name) VALUES (1, 'Alice');  -- OK
INSERT INTO users (id, name) VALUES (1, 'Bob');    -- ERROR (duplicate)

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

• ✅ Автоматически уникален и not null
• ✅ Очень быстрые поиски
• ✅ Используется для связей (foreign keys)
• ✅ Обычно B-Tree структура

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

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

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

-- Эквивалентно:
CREATE UNIQUE INDEX idx_email ON users(email);

-- Можно вставить несколько NULL
INSERT INTO users (id, email) VALUES (1, NULL);  -- OK
INSERT INTO users (id, email) VALUES (2, NULL);  -- OK

-- Но один email только один раз
INSERT INTO users (id, email) VALUES (1, 'john@example.com');  -- OK
INSERT INTO users (id, email) VALUES (2, 'john@example.com');  -- ERROR

Используется для: Email, username, SKU товаров

3. Single Column Index (Индекс на один столбец)

Индекс для быстрого поиска по одному полю:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    status VARCHAR(50),
    created_at TIMESTAMP
);

-- Создать индекс
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- Теперь эти запросы быстрые
SELECT * FROM orders WHERE customer_id = 10;  -- O(log n)
SELECT * FROM orders WHERE status = 'pending'; -- O(log n)
SELECT * FROM orders WHERE created_at > '2024-01-01'; -- O(log n)

Когда создавать:

• Столбец часто используется в WHERE
• Столбец используется в JOIN условиях
• Столбец используется в ORDER BY

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

Индекс на несколько столбцов для сложных запросов:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    status VARCHAR(50),
    created_at DATE
);

-- Составной индекс (порядок важен!)
CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- Этот запрос использует индекс
SELECT * FROM orders 
WHERE customer_id = 5 AND status = 'completed';

-- Этот тоже (первый столбец индекса)
SELECT * FROM orders WHERE customer_id = 5;

-- Этот НЕ использует индекс (не первый столбец)
SELECT * FROM orders WHERE status = 'completed';

-- Лучше создать для этого отдельный индекс
CREATE INDEX idx_status ON orders(status);

Правило: Индекс (a, b, c) работает для:

• WHERE a = ...
• WHERE a = ... AND b = ...
• WHERE a = ... AND b = ... AND c = ...

НО НЕ для:

• WHERE b = ...
• WHERE c = ...
• WHERE b = ... AND c = ...

5. Full-Text Index (Полнотекстовый индекс)

Для быстрого поиска по текстовым содержимым:

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

-- Создать полнотекстовый индекс
CREATE FULLTEXT INDEX idx_article_search ON articles(title, content);

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

-- С режимом BOOLEAN
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('+python -javascript' IN BOOLEAN MODE);
-- Найти "python" но исключить "javascript"

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

• Поиск в больших текстовых полях
• Блоги, статьи, документы
• Лучше чем LIKE для текста

6. Prefix Index (Индекс на префикс)

Индекс на первые N символов (экономит место):

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

-- Индекс на первые 10 символов email
CREATE INDEX idx_email_prefix ON users(email(10));

-- Работает для этого:
SELECT * FROM users WHERE email = 'john@example.com';

-- Может быть менее эффективно для LIKE
SELECT * FROM users WHERE email LIKE 'john%';

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

• Очень длинные строки
• Когда нужно сэкономить место на диске
• Например, длинные URL

7. Spatial Index (Пространственный индекс)

Для географических и геометрических данных:

CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    coordinates POINT NOT NULL,
    SPATIAL INDEX idx_coordinates (coordinates)
);

-- Найти все точки в радиусе
SELECT * FROM locations
WHERE ST_Distance_Sphere(
    coordinates,
    POINT(55.7558, 37.6173)
) < 1000;  -- в метрах

-- Найти точки внутри многоугольника
SELECT * FROM locations
WHERE ST_Contains(
    POLYGON((...)...),
    coordinates
);

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

• Картографические приложения
• Поиск ближайшего местоположения
• Проверка пересечений геометрических фигур

8. Hash Index (Хеш-индекс)

Для быстрого поиска по точному совпадению:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    INDEX idx_email_hash (email) USING HASH  -- только MySQL
);

-- Очень быстро для точного поиска
SELECT * FROM users WHERE email = 'john@example.com';  -- O(1)

-- НО не работает для LIKE или диапазонов
SELECT * FROM users WHERE email LIKE 'john%';  -- медленно
SELECT * FROM users WHERE email > 'a@example.com';  -- медленно

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

• Очень быстро для точного совпадения
• Меньше памяти чем B-Tree

Недостатки:

• Не подходит для LIKE, <, >, BETWEEN
• Не поддерживается во всех БД (например, PostgreSQL)

9. B-Tree Index (Стандартный индекс)

Самый распространённый, используется по умолчанию:

            [50]
          /      \\
       [30]        [70]
      /    \\      /    \\
   [10] [40] [60] [80]

CREATE INDEX idx_name ON users(name);
-- Автоматически B-Tree в PostgreSQL и MySQL

-- Быстро для:
WHERE name = 'Alice';              -- O(log n)
WHERE name > 'Alice';              -- O(log n) + scan
WHERE name LIKE 'Alice%';          -- O(log n) + scan
WHERE name IN ('Alice', 'Bob');    -- O(log n) per item
ORDER BY name;                      -- может использовать индекс

10. Covering Index (Покрывающий индекс)

Индекс содержит ВСЕ данные для ответа на запрос:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP
);

-- Покрывающий индекс
CREATE INDEX idx_covering ON orders(customer_id, amount, created_at);

-- Этот запрос получит ALL данные из индекса (не читая таблицу)
SELECT customer_id, amount, created_at 
FROM orders 
WHERE customer_id = 10;

-- БД не нужно доступ к основной таблице!

Преимущество: Очень быстро, так как данные уже в индексе

11. Partial Index (Условный индекс)

Индекс только на некоторые строки:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    status VARCHAR(50),
    deleted_at TIMESTAMP NULL
);

-- Индекс только на активных пользователей (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) 
WHERE deleted_at IS NULL;

-- Запрос использует индекс
SELECT * FROM users WHERE email = 'john@example.com' AND deleted_at IS NULL;

-- Экономит место, так как не индексирует удалённых пользователей

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

• Soft delete (логическое удаление)
• Фильтрация по статусу
• Когда индексируется только 10% данных

Сравнение индексов

Практические рекомендации

-- 1. Индекс на часто используемые столбцы в WHERE
CREATE INDEX idx_user_email ON users(email);

-- 2. Составной индекс для частых комбинаций
CREATE INDEX idx_order_customer_date ON orders(customer_id, created_at DESC);

-- 3. Covering index для часто выбираемых полей
CREATE INDEX idx_product_search ON products(category_id, price, name);

-- 4. Partial index для soft-delete
CREATE INDEX idx_active_posts ON posts(created_at) WHERE deleted_at IS NULL;

-- 5. Full-text для поиска в текстах
CREATE FULLTEXT INDEX idx_search ON articles(title, content);

Когда НЕ нужен индекс

• Маленькая таблица (< 1000 записей)
• Столбец с низкой селективностью (много одинаковых значений)
• Столбец редко используется в WHERE
• Таблица часто обновляется (UPDATE/DELETE)

Вывод: Правильно выбранные индексы могут ускорить запросы в 100+ раз. Но неправильные индексы замедляют запись и займут место!