Какие знаешь способы добавления индекса?

Способы добавления индекса в базу данных

Индекс — это структура данных, которая ускоряет поиск записей в таблице. Вот основные способы добавления индексов:

1. SQL команда CREATE INDEX

Простейший способ — прямая SQL команда:

-- Создание обычного индекса
CREATE INDEX idx_users_email ON users(email);

-- Создание уникального индекса
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Составной индекс (несколько столбцов)
CREATE INDEX idx_users_lastname_firstname ON users(last_name, first_name);

-- Индекс на части колонки
CREATE INDEX idx_users_email_prefix ON users(email(10));

2. Миграции (Goose для проекта)

В проекте используется Goose, поэтому индексы добавляются через SQL миграции:

# Создай миграцию
goose -dir migrations mysql create add_user_email_index

# Содержимое: migrations/NNNN_add_user_email_index.sql

-- +goose Up
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

-- +goose Down
DROP INDEX idx_users_email ON users;
DROP INDEX idx_orders_user_id ON orders;
DROP INDEX idx_orders_created_at ON orders;

3. ORM аннотации (SQLAlchemy / JPA)

В Python/SQLAlchemy можно определить индексы через модель:

from sqlalchemy import Column, String, Index, DateTime
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255), nullable=False)
    username = Column(String(100), nullable=False)
    created_at = Column(DateTime(timezone=True), nullable=False)
    
    # Индекс через __table_args__
    __table_args__ = (
        Index("idx_users_email", email),
        Index("idx_users_username", username, unique=True),
        Index("idx_users_created_at", created_at.desc()),
    )

Или в Java/Hibernate/JPA:

import javax.persistence.*;
import org.hibernate.annotations.Index;

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_users_email", columnList = "email"),
    @Index(name = "idx_users_username", columnList = "username", unique = true),
    @Index(name = "idx_users_created_at", columnList = "created_at")
})
public class User {
    @Id
    private Long id;
    
    @Column(name = "email", nullable = false)
    private String email;
    
    @Column(name = "username", nullable = false)
    private String username;
}

4. ALTER TABLE

Добавление индекса к существующей таблице:

ALTER TABLE users ADD INDEX idx_users_email (email);
ALTER TABLE users ADD UNIQUE INDEX idx_users_username (username);
ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id);

5. FULLTEXT индексы

Для полнотекстового поиска:

CREATE FULLTEXT INDEX idx_posts_content ON posts(title, content);

-- Поиск
SELECT * FROM posts WHERE MATCH(title, content) AGAINST(java IN BOOLEAN MODE);

6. GiST / GIN индексы (PostgreSQL)

Для специальных типов данных (массивы, JSON, геоданные):

-- JSON индекс
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- Полнотекстовый поиск
CREATE INDEX idx_posts_tsvector ON posts USING GIN(to_tsvector(russian, content));

7. Partial индексы

Индекс только для части данных:

-- Индекс только для активных пользователей
CREATE INDEX idx_users_active_email ON users(email) WHERE is_active = true;

-- Индекс только для заказов последних 30 дней
CREATE INDEX idx_recent_orders ON orders(user_id, created_at) 
WHERE created_at > NOW() - INTERVAL 30 DAY;

8. Descending индексы

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

CREATE INDEX idx_posts_created_desc ON posts(created_at DESC);

-- Теперь этот запрос быстрый
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

9. Include индексы (Covering Index)

Индекс содержит доп. столбцы для избежания доступа к таблице:

-- MySQL 8.0+
CREATE INDEX idx_users_email_include 
ON users(email) INCLUDE (id, username, created_at);

-- Запрос может быть обслужен полностью из индекса
SELECT username, created_at FROM users WHERE email = test@example.com;

Примеры индексов для типичных сценариев

Поиск по email

CREATE UNIQUE INDEX idx_users_email ON users(email);

Сортировка по дате

CREATE INDEX idx_posts_created ON posts(created_at DESC);

Foreign Key связь

CREATE INDEX idx_orders_user_id ON orders(user_id);

Фильтр + Сортировка

CREATE INDEX idx_posts_user_created 
ON posts(user_id, created_at DESC);

-- Быстро находит посты пользователя, отсортированные по дате
SELECT * FROM posts 
WHERE user_id = 5 
ORDER BY created_at DESC;

Поиск по диапазону

CREATE INDEX idx_orders_date_range ON orders(created_at, status);

-- Быстро находит заказы в диапазоне дат
SELECT * FROM orders 
WHERE created_at BETWEEN 2025-01-01 AND 2025-12-31 
AND status = completed;

Проверка существующих индексов

-- MySQL
SHOW INDEX FROM users;

-- PostgreSQL
\d users
SELECT * FROM pg_indexes WHERE tablename = users;

-- SQLite
PRAGMA index_list(users);

Удаление индекса

-- MySQL
DROP INDEX idx_users_email ON users;

-- PostgreSQL / SQLite
DROP INDEX idx_users_email;

Оптимизация индексов

-- Перестроение индекса
REPAIR TABLE users;
REBUILD INDEX idx_users_email;

-- Анализ таблицы
ANALYZE TABLE users;

-- Проверка индексов
CHECK TABLE users;

Лучшие практики

1. Создавай индексы для часто фильтруемых полей

   • WHERE email = ?
   • WHERE user_id = ?
   • WHERE created_at BETWEEN ? AND ?

2. Не переусложняй — слишком много индексов замедляет INSERT/UPDATE

3. Порядок имеет значение

   -- Хорошо: фильтр, затем сортировка
   CREATE INDEX idx ON table(filter_col, sort_col DESC);
   

4. Используй EXPLAIN для проверки

   EXPLAIN SELECT * FROM posts WHERE user_id = 5 ORDER BY created_at DESC;
   

5. Избегай индексов на columns с низкой selectivity

   • Индекс на is_active (только 2 значения) малополезен

6. Добавляй индексы через миграции (не в коде ORM)

Итог

Главное — добавляй индексы через миграции Goose и согласуй с моделью ORM. Правильные индексы могут улучшить производительность в 1000 раз, но неправильные замедлят систему.