На что смотришь при создании индекса?

Ответ на вопрос о критериях создания индекса

На что смотришь при создании индекса?

При создании индекса нужно анализировать несколько ключевых факторов. Это критическая оптимизация БД, требующая понимания как бизнес-логики, так и технических характеристик.

1. Частота использования столбца в WHERE

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

-- Часто выполняемые запросы
SELECT * FROM users WHERE email = ?;  -- Индекс на email
SELECT * FROM orders WHERE status = ?;  -- Индекс на status
SELECT * FROM logs WHERE timestamp > ?;  -- Индекс на timestamp

-- Плохо: индекс на редко используемый столбец
SELECT * FROM users WHERE nickname = ?;  -- Если nickname редко ищем

2. Кардинальность столбца (Cardinality)

Определение: количество уникальных значений в столбце.

-- ВЫСОКАЯ кардинальность (много уникальных) - ХОРОШО для индекса
SELECT COUNT(DISTINCT id) as cardinality FROM users;  -- Например: 1 000 000
SELECT COUNT(DISTINCT email) as cardinality FROM users;  -- Например: 1 000 000

-- НИЗКАЯ кардинальность (мало уникальных) - ПЛОХО для индекса
SELECT COUNT(DISTINCT gender) as cardinality FROM users;  -- Например: 2 (M/F)
SELECT COUNT(DISTINCT is_active) as cardinality FROM users;  -- Например: 2 (true/false)

-- Проверка эффективности индекса
SELECT 
    column_name,
    COUNT(DISTINCT column_name) as cardinality,
    COUNT(*) as total_rows,
    ROUND(100.0 * COUNT(DISTINCT column_name) / COUNT(*), 2) as selectivity_percent
FROM users
GROUP BY column_name
ORDER BY cardinality DESC;

Пороги:

• > 95% уникальных → отличная кандидатура
• 50-95% уникальных → хорошая кандидатура
• < 10% уникальных → плохая кандидатура (может быть индекс, но не как основной)

3. Размер таблицы (table size)

Правило: индексируй в больших таблицах (> 10 000 строк).

-- Проверка размера таблицы
SELECT 
    table_name,
    COUNT(*) as row_count,
    ROUND(pg_total_relation_size(table_name::regclass) / 1024 / 1024, 2) as size_mb
FROM information_schema.tables
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY COUNT(*) DESC;

Рекомендации:

• < 1 000 строк → индекс бесполезен
• 1 000 - 10 000 → может быть полезен

• 10 000 → индекс почти всегда стоит

• 1 000 000 → индекс КРИТИЧЕН

4. Количество JOIN'ов

Правило: индексируй foreign key столбцы.

-- ВСЕГДА индексируй FK
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- Плохой запрос без индекса (медленный JOIN)
SELECT u.*, o.* 
FROM users u
JOIN orders o ON u.id = o.user_id  -- Нужен индекс на orders.user_id
WHERE u.id = 123;

-- Хороший запрос с индексом (быстрый)
-- (индекс на foreign key)

5. ORDER BY и GROUP BY

Правило: индексируй столбцы в ORDER BY и GROUP BY.

-- Частый сортировочный запрос
SELECT * FROM posts 
ORDER BY created_at DESC  -- Нужен индекс на created_at
LIMIT 10;

-- Индекс для этого
CREATE INDEX idx_posts_created_at DESC ON posts(created_at DESC);

-- Группировка
SELECT category_id, COUNT(*) 
FROM products
GROUP BY category_id;  -- Нужен индекс на category_id

CREATE INDEX idx_products_category_id ON products(category_id);

6. Selectively (составные индексы)

Правило: если два столбца часто используются вместе в WHERE.

-- Частый запрос
SELECT * FROM orders 
WHERE user_id = ? AND status = ?;  -- Оба в WHERE

-- Обычные индексы (неоптимально)
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- ЛУЧШЕ: составной индекс
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Порядок имеет значение!
-- Лучше: (user_id, status) если user_id более selective
-- Худше: (status, user_id) если status имеет низкую кардинальность

7. Стоимость обслуживания индекса

Каждый индекс имеет ЦЕНУ:

-- Индекс ускоряет SELECT, но замедляет INSERT/UPDATE/DELETE

-- Медленный INSERT (много индексов)
INSERT INTO users (id, email, name, phone, address, created_at)
VALUES (1, 'user@example.com', 'John', '555-1234', '123 Main St', now());
-- БД обновляет индексы на каждом столбце

-- Плохо: индекс на редко используемый столбец
CREATE INDEX idx_users_phone ON users(phone);  -- Если phone редко ищем

-- Хорошо: индекс на часто ищемый столбец
CREATE INDEX idx_users_email ON users(email);  -- email часто ищем

8. EXPLAIN и Query Plan

Всегда проверяй EXPLAIN перед созданием индекса:

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE email = 'user@example.com';

-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE email = 'user@example.com';

-- Искать:
-- - Seq Scan (плохо, нужен индекс)
-- - Index Scan (хорошо)
-- - Full Index Scan (если можно улучшить)
-- - Execution time

9. Nullability

Правило: индексы в БД обычно исключают NULL.

-- Проверка NULL значений
SELECT COUNT(*) as null_count 
FROM users 
WHERE email IS NULL;  -- Если много NULL, индекс менее эффективен

-- Если NULL часто - может быть better индекс
CREATE INDEX idx_users_email_not_null ON users(email) 
WHERE email IS NOT NULL;  -- Partial index

10. UPDATE frequency (частота обновлений)

Правило: на редко обновляемые столбцы индексируй смелее.

// Java пример: анализ обновлений
public class IndexAnalyzer {
    public boolean shouldIndex(String columnName, long tableSize, 
                               long updateFrequencyPerSecond, 
                               double selectivityPercent) {
        // Условия для создания индекса
        boolean isLargeTable = tableSize > 10_000;
        boolean isSelective = selectivityPercent > 50;
        boolean isNotFrequentlyUpdated = updateFrequencyPerSecond < 100;
        
        return isLargeTable && isSelective && isNotFrequentlyUpdated;
    }
}

Чеклист при создании индекса

✅ ДА, создавай индекс если:

1. Столбец часто используется в WHERE
2. Высокая кардинальность (> 50% уникальных)
3. Таблица большая (> 10 000 строк)
4. Foreign key
5. Используется в ORDER BY или GROUP BY
6. Редко обновляется
7. Таблица растёт (будет иметь миллионы строк)

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

1. Низкая кардинальность (< 10% уникальных)
2. Столбец редко используется в WHERE
3. Таблица маленькая (< 1 000 строк)
4. Столбец часто обновляется
5. SELECT медленнее, чем INSERT/UPDATE с индексом
6. Уже есть лучший составной индекс

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

-- Таблица users
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100),
    status VARCHAR(20),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

-- Индексы:
CREATE INDEX idx_users_email ON users(email);  -- ✅ Высокая кардинальность, часто ищем
CREATE INDEX idx_users_username ON users(username);  -- ✅ Высокая кардинальность
CREATE INDEX idx_users_created_at ON users(created_at DESC);  -- ✅ ORDER BY
CREATE INDEX idx_users_status_created ON users(status, created_at);  -- ✅ Составной
-- НЕ индексируем status отдельно - низкая кардинальность

Резюме

При создании индекса смотри на:

1. Частота использования в WHERE
2. Кардинальность (> 50% уникальных)
3. Размер таблицы (> 10 000 строк)
4. Foreign keys - индексируй всегда
5. ORDER BY, GROUP BY - индексируй столбцы
6. Составные индексы - если 2+ столбца вместе
7. EXPLAIN - проверяй перед созданием
8. Стоимость INSERT/UPDATE/DELETE
9. Selectivity - эффективность фильтрации
10. Частота обновлений - редко обновляемые лучше

Golden rule: Индекс хорош, если ускоряет SELECT больше, чем замедляет INSERT/UPDATE/DELETE.