Что такое индексы в базе данных? Почему они могут не сработать?

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

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

Структура данных для быстрого поиска (обычно B-tree).

CREATE INDEX idx_users_email ON users(email);

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

B-tree (по умолчанию)

CREATE INDEX idx_name ON users(name);
-- Для: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%'

Hash

CREATE INDEX idx_hash ON users USING hash(id);
-- Только для: =

GIN (для массивов, JSONB)

CREATE INDEX idx_tags ON posts USING gin(tags);

GiST (геометрия, полнотекстовый поиск)

CREATE INDEX idx_location ON places USING gist(location);

Составные индексы:

CREATE INDEX idx_composite ON orders(customer_id, created_at);

-- Работает для:
WHERE customer_id = 1
WHERE customer_id = 1 AND created_at > '2024-01-01'

-- НЕ работает для:
WHERE created_at > '2024-01-01'  -- Первый столбец не указан

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

1. Функции над столбцом:

-- НЕ использует индекс на created_at
WHERE YEAR(created_at) = 2024

-- Использует:
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

2. Неявное преобразование типов:

-- user_id это INT, но передаём строку
WHERE user_id = '123'  -- Может не использовать индекс

3. LIKE с wildcard в начале:

WHERE name LIKE '%smith'  -- Не использует B-tree
WHERE name LIKE 'smith%'  -- Использует

4. OR условия:

WHERE email = 'a@mail.com' OR phone = '123'
-- Может требовать отдельные индексы + Index Merge

5. Низкая селективность:

-- Индекс на gender (M/F) бесполезен
WHERE gender = 'M'  -- ~50% таблицы
-- Full scan может быть быстрее

6. NULL значения:

WHERE column IS NULL  -- Зависит от СУБД

7. Неактуальная статистика:

ANALYZE users;  -- Обновить статистику

EXPLAIN для анализа:

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@mail.com';

-- Смотрим:
-- Index Scan vs Seq Scan
-- Actual rows vs estimated
-- Execution time

Best Practices:

1. Индексируйте столбцы в WHERE, JOIN, ORDER BY
2. Составные индексы — частые столбцы первыми
3. Не переиндексируйте — замедляет INSERT/UPDATE
4. Регулярно анализируйте EXPLAIN