Есть ли принцип создания индекса

Есть ли принцип создания индекса

Да, создание индексов следует определённым принципам и best practices. Неправильное индексирование может даже замедлить БД вместо ускорения.

Основные принципы индексирования

1. Принцип Selectivity (Избирательность)

Индекс должен выбирать малый процент строк:

-- Плохой индекс: низкая selectivity
CREATE INDEX idx_gender ON users(gender);  -- Только 2 значения (M/F)
-- Индекс выбирает ~50% строк — неэффективен

-- Хороший индекс: высокая selectivity
CREATE INDEX idx_email ON users(email);    -- Уникальное поле
-- Индекс выбирает 1 строку — очень эффективен

Проверить selectivity:

-- PostgreSQL
SELECT 
    (COUNT(DISTINCT email)::FLOAT / COUNT(*)) * 100 AS selectivity_percent
FROM users;
-- > 90% selectivity хорошо
-- < 10% selectivity плохо

-- MySQL
SELECT 
    (COUNT(DISTINCT email) / COUNT(*)) * 100 AS selectivity_percent
FROM users;

2. Принцип Cardinality (Мощность)

Число уникальных значений в колонке:

-- Колонна с высокой cardinality
SELECT COUNT(DISTINCT user_id) FROM orders;     -- 1,000,000 уникальных

-- Колонна с низкой cardinality
SELECT COUNT(DISTINCT status) FROM orders;      -- 3 значения (pending, confirmed, shipped)

Правило:

• High cardinality → индекс поможет ✓
• Low cardinality → индекс может не помочь ✗

3. Принцип Left-Prefix (Левый префикс)

Для составных индексов порядок колонн критичен:

-- Составной индекс
CREATE INDEX idx_user_order ON orders(user_id, created_at);

-- Используется при условиях:
WHERE user_id = 1;                           -- ✓ Использует индекс
WHERE user_id = 1 AND created_at > '2024'   -- ✓ Использует индекс
WHERE user_id = 1 AND created_at = '2024'   -- ✓ Использует индекс

-- НЕ используется при условиях:
WHERE created_at > '2024';                   -- ✗ Не использует индекс
WHERE created_at = '2024' AND user_id = 1;   -- ✗ Не использует полностью

Правило: индекс используется слева направо. Если пропустить первую колонну, остальное игнорируется.

// В JPA / Hibernate
@Entity
@Table(
    indexes = {
        @Index(
            name = "idx_user_order",
            columnList = "user_id,created_at"
        )
    }
)
public class Order {
    @Id
    private Long id;
    
    @Column(name = "user_id")
    private Long userId;  // Первая!
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;  // Вторая
}

4. Принцип WHERE Clause (Условия фильтрации)

Индексируй колонны, используемые в WHERE:

-- Частый запрос
SELECT * FROM users WHERE email = 'test@example.com' AND status = 'active';

-- Оптимальный индекс
CREATE INDEX idx_email_status ON users(email, status);

В Java:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // Этот запрос должен использовать индекс на email и status
    @Query("SELECT u FROM User u WHERE u.email = ?1 AND u.status = ?2")
    Optional<User> findByEmailAndStatus(String email, String status);
}

5. Принцип JOIN Columns (Колонны объединения)

Индексируй колонны в JOIN условиях:

-- Запрос с JOIN
SELECT u.*, o.* 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- Индексы
CREATE INDEX idx_orders_user_id ON orders(user_id);  -- Foreign key
CREATE INDEX idx_users_status ON users(status);      -- WHERE условие

В Hibernate:

@Entity
public class User {
    @Id
    private Long id;
    
    private String status;
    
    @OneToMany(mappedBy = "user")
    private List<Order> orders;
}

@Entity
public class Order {
    @Id
    private Long id;
    
    @ManyToOne
    @JoinColumn(name = "user_id")  // Этот столбец должен быть индексирован!
    private User user;
}

6. Принцип ORDER BY / GROUP BY

Индекс может помочь в сортировке:

-- Запрос с сортировкой
SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;

-- Оптимальный индекс (status в WHERE, created_at в ORDER BY)
CREATE INDEX idx_status_created ON users(status, created_at DESC);

7. Принцип COVERING INDEX (Индекс-покрытие)

Индекс может содержать все нужные колонны, чтобы избежать обращения к таблице:

-- Часто используемый запрос
SELECT id, email, name FROM users WHERE status = 'active' ORDER BY created_at;

-- Индекс-покрытие содержит все нужные колонны
CREATE INDEX idx_status_covering ON users(
    status,              -- WHERE условие
    created_at,          -- ORDER BY
    id, email, name      -- SELECT колонны
);

Результат:

• БД не нужно обращаться к таблице
• Данные полностью в индексе
• Очень быстро (Index-Only Scan)

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

1. Малые таблицы

-- Таблица с 100 строками
CREATE TABLE status (    -- Не нужен индекс
    id INT,
    name VARCHAR(50)
);
-- Full table scan быстрее, чем индекс

2. Низкая selectivity

-- Таблица с миллионами строк
CREATE INDEX bad_idx ON orders(is_deleted);  -- Вредно!
-- Запрос: WHERE is_deleted = FALSE
-- Индекс выбирает 99% строк → лучше full scan

3. Низкая частота обращений

-- Редкий запрос
CREATE INDEX rare_idx ON users(middle_name);
-- Затраты на maintenance > benefit от rare queries

4. UPDATE/INSERT интенсивные операции

-- Таблица с частыми INSERT'ами
CREATE TABLE events (
    id BIGINT PRIMARY KEY,
    data JSON
);
-- Много индексов → медленные INSERT'ы

Процесс создания индекса

Шаг 1: Выявить медленные запросы

// Spring Boot с логированием
spring.jpa.properties.hibernate.generate_statistics=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.stat=DEBUG

Шаг 2: Анализировать EXPLAIN

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- Если type=ALL → full scan (медленно)
-- Если type=ref → использует индекс (быстро)

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

Шаг 3: Создать индекс

CREATE INDEX idx_email ON users(email);

Шаг 4: Проверить улучшение

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Execution time должно снизиться

Best Practices

-- ✓ Правильно
CREATE INDEX idx_users_email ON users(email);
-- Осмысленное имя индекса

-- ✗ Неправильно
CREATE INDEX idx1 ON users(email);
-- Непонятное имя

-- ✓ Правильно
CREATE INDEX idx_orders_user_status_date 
ON orders(user_id, status, created_at DESC);
-- Составной индекс с логичным порядком

-- ✗ Неправильно
CREATE INDEX idx_date ON orders(created_at);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_user_id ON orders(user_id);
-- Много одиночных индексов вместо одного составного

Инструменты для анализа

# MySQL
mysql> SHOW INDEXES FROM orders;
mysql> ANALYZE TABLE orders;

# PostgreSQL
\d+ orders  -- показать индексы
SELECT * FROM pg_stat_user_indexes;

# MongoDB
db.orders.getIndexes()
db.orders.collection.getIndexStats()

Заключение

Индексирование — это наука и искусство. Ключевые принципы:

1. Selectivity → индекс должен выбирать малый процент
2. Cardinality → индекс на уникальные колонны
3. Left-Prefix → порядок колонн важен
4. WHERE/JOIN → индексируй колонны из условий
5. ORDER BY → индекс может помочь в сортировке
6. Covering Index → избегай обращений к таблице
7. Анализируй EXPLAIN → проверяй реальное использование

Помните: индекс — это trade-off между скоростью SELECT и скоростью INSERT/UPDATE.