Как определить индекс, если требуется максимальная производительность?

Определение индекса для максимальной производительности в БД

Индексы — это критичный элемент оптимизации базы данных. Правильное их определение напрямую влияет на производительность. Рассмотрю стратегию создания индексов.

1. Основные типы индексов

-- B-Tree индекс (по умолчанию, для большинства случаев)
CREATE INDEX idx_users_email ON users(email);

-- Composite индекс (несколько столбцов)
CREATE INDEX idx_users_city_age ON users(city, age);

-- Unique индекс (гарантирует уникальность)
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Full-text индекс (для поиска текста)
CREATE FULLTEXT INDEX idx_posts_content ON posts(content);

-- Hash индекс (только для точного совпадения)
CREATE INDEX idx_users_id_hash ON users(id) USING HASH;

-- GiST индекс (для геоданных)
CREATE INDEX idx_locations_geo ON locations USING GIST(location);

2. Правило выбора столбцов для индекса

-- ПРАВИЛЬНО: Часто используемые в WHERE условиях
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- ПРАВИЛЬНО: Столбцы с высокой селективностью (много уникальных значений)
CREATE INDEX idx_users_email ON users(email);      -- email уникален
CREATE INDEX idx_users_username ON users(username); -- username уникален

-- НЕПРАВИЛЬНО: Столбцы с низкой селективностью
CREATE INDEX idx_users_active ON users(is_active); -- только true/false

-- НЕПРАВИЛЬНО: Редко используемые столбцы
CREATE INDEX idx_users_notes ON users(notes);      -- редко в WHERE

3. Composite индексы (индексы на несколько столбцов)

Порядок столбцов критичен:

-- Порядок: WHERE условия слева направо
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);

-- Эта таблица показывает, какие запросы будут использовать индекс:
-- ✓ user_id
-- ✓ user_id, status
-- ✓ user_id, status, created_at
-- ✗ status (пропущен user_id в начале)
-- ✗ created_at (пропущены user_id и status)

Примеры:

// Этот запрос будет использовать индекс полностью
// SELECT * FROM orders WHERE user_id = 123 AND status = 'COMPLETED' AND created_at > '2024-01-01';

// Этот запрос будет использовать только user_id и status
// SELECT * FROM orders WHERE user_id = 123 AND status = 'COMPLETED';

// Этот запрос НЕ будет использовать индекс (пропущен user_id)
// SELECT * FROM orders WHERE status = 'COMPLETED' AND created_at > '2024-01-01';

4. Анализ запросов с EXPLAIN

-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'COMPLETED';

-- Смотрим на:
-- type: range, ref, const (хорошо), ALL (плохо)
-- key: какой индекс используется
-- rows: сколько строк сканируется
-- Extra: возможные проблемы

Пример вывода:

id  select_type  table   type   key                        rows    Extra
1   SIMPLE       orders  ref    idx_orders_user_status_date 142   Using index condition

5. Стратегия создания индексов

// Сначала анализируем основные запросы
public interface OrderRepository extends JpaRepository<Order, Long> {
    // Часто используемые запросы
    List<Order> findByUserId(Long userId);        // Нужен индекс на user_id
    List<Order> findByStatus(String status);       // Низкая селективность, может не нужен
    List<Order> findByUserIdAndStatus(Long userId, String status); // Нужен composite индекс
    List<Order> findByCreatedAtBetween(LocalDate start, LocalDate end); // Нужен индекс
}

Миграция:

-- Создаём индексы для основных операций поиска
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

6. Индексы для JOIN операций

-- Для JOIN нужны индексы на столбцы, которые объединяют таблицы
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_products_id ON products(id);

-- Запрос будет использовать эти индексы:
SELECT o.*, oi.*, p.* FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 123;

7. Индексы для SORT и GROUP BY

-- ORDER BY может использовать индекс для сортировки
CREATE INDEX idx_orders_created_at_user_id ON orders(created_at, user_id);

-- Этот запрос не потребует дополнительной сортировки
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC;

-- GROUP BY также может использовать индекс
SELECT status, COUNT(*) FROM orders 
WHERE created_at > '2024-01-01'
GROUP BY status;

8. Что НЕ нужно индексировать

-- Малые таблицы (< 1000 строк)
CREATE INDEX idx_statuses_name ON statuses(name);  -- Бесполезно

-- Столбцы, редко используемые в WHERE
CREATE INDEX idx_users_notes ON users(notes);      -- Редко ищут
CREATE INDEX idx_users_bio ON users(bio);          -- Редко ищут

-- Столбцы с низкой селективностью
CREATE INDEX idx_orders_is_paid ON orders(is_paid); -- Только true/false
CREATE INDEX idx_users_gender ON users(gender);     -- Мало значений

-- Текстовые поля без полнотекстового поиска
CREATE INDEX idx_posts_content ON posts(content);  -- Используй FULLTEXT

9. Monitoring и оптимизация

-- Найти неиспользуемые индексы (MySQL)
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_NAME='orders' AND COUNT_READ=0;

-- Найти медленные запросы (MySQL)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

-- Анализ индексов (PostgreSQL)
SELECT schemaname, tablename, indexname 
FROM pg_indexes 
WHERE tablename='orders';

-- Статистика использования индексов (PostgreSQL)
SELECT schemaname, tablename, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE tablename='orders'
ORDER BY idx_scan DESC;

10. Полная стратегия индексирования

@Entity
@Table(name = "orders", indexes = {
    // Основные WHERE условия
    @Index(name = "idx_user_id", columnList = "user_id"),
    @Index(name = "idx_status", columnList = "status"),
    @Index(name = "idx_created_at", columnList = "created_at"),
    
    // Composite индексы для частых комбинаций
    @Index(name = "idx_user_status", columnList = "user_id,status"),
    @Index(name = "idx_status_date", columnList = "status,created_at"),
    
    // Уникальные индексы
    @Index(name = "idx_order_number_unique", columnList = "order_number", unique = true)
})
public class Order {
    @Id
    private Long id;
    
    @Column(nullable = false)
    private Long userId;
    
    @Column(nullable = false)
    private String status;
    
    @Column(nullable = false)
    private LocalDateTime createdAt;
    
    @Column(unique = true)
    private String orderNumber;
}

Checklist для создания индексов

1. Анализируй медленные запросы — используй EXPLAIN
2. Индексируй столбцы в WHERE — начни отсюда
3. Используй composite индексы — для частых комбинаций
4. Помни про порядок — левый префикс важен
5. Смотри на селективность — уникальные значения = хороший индекс
6. Балансируй reads vs writes — больше индексов = медленнее UPDATE/INSERT
7. Мониторь использование — удаляй ненужные
8. Регулярно ANALYZE — для обновления статистики
9. Тестируй на боевых данных — с реальными объёмами
10. Документируй решения — почему каждый индекс нужен

Правильные индексы могут улучшить производительность в 100+ раз!