На какие действия влияет индекс

На какие действия влияют индексы БД

Короткий ответ

Индексы в основном влияют на операции ЧТЕНИЯ (SELECT), но могут замедлить ЗАПИСЬ (INSERT/UPDATE/DELETE) из-за необходимости обновления индекса.

Детально: Как работают индексы

Индекс — это отсортированная структура данных (обычно B-tree), которая позволяет быстро найти нужные строки без сканирования всей таблицы.

-- БЕЗ индекса
CREATE TABLE users (
    id BIGINT,
    email VARCHAR(255),
    name VARCHAR(100)
);

SELECT * FROM users WHERE email = 'john@example.com';
-- Обыскивает ВСЕ строки (Full Table Scan) — O(n)

-- С индексом
CREATE INDEX idx_users_email ON users(email);

SELECT * FROM users WHERE email = 'john@example.com';
-- Использует индекс для быстрого поиска — O(log n)

1. SELECT (чтение) — УЛУЧШАЕТСЯ

Индекс ускоряет SELECT операции:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_products_category_price ON products(category, price);

-- Все эти селекты будут БЫСТРЕЕ
SELECT * FROM users WHERE email = 'john@example.com'; -- ✅ Быстро
SELECT * FROM orders WHERE user_id = 123; -- ✅ Быстро
SELECT * FROM products WHERE category = 'Electronics' AND price > 100; -- ✅ Быстро

Сравнение производительности:

2. INSERT (вставка) — ЗАМЕДЛЯЕТСЯ

INSERT становится медленнее, потому что нужно обновить индексы:

-- БЕЗ индексов
INSERT INTO users (id, email, name) VALUES (1, 'john@example.com', 'John');
-- Быстро: просто добавить строку в таблицу

-- С индексами
INSERT INTO users (id, email, name) VALUES (1, 'john@example.com', 'John');
-- Медленнее: нужно обновить индекс по email, индекс по name, и т.д.

Сценарий: Вставка 1 миллиона записей

-- Таблица БЕЗ индексов
CREATE TABLE users_no_index (id INT, email VARCHAR(255), name VARCHAR(100));
INSERT INTO users_no_index VALUES (1, 'user1@example.com', 'User 1');
-- ... 1,000,000 раз
-- Время: 5 секунд

-- Таблица С индексами
CREATE TABLE users_indexed (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    name VARCHAR(100)
);
CREATE INDEX idx_name ON users_indexed(name);
INSERT INTO users_indexed VALUES (1, 'user1@example.com', 'User 1');
-- ... 1,000,000 раз
-- Время: 15 секунд (3x медленнее!)

3. UPDATE (обновление) — ЗАМЕДЛЯЕТСЯ

UPDATE замедляется, если обновляется индексируемое поле:

-- Обновление БЕЗ влияния на индекс (быстро)
UPDATE users SET phone = '+1234567890' WHERE id = 123;
-- id — PRIMARY KEY, уже в индексе
-- phone — не индексирован, индекс не обновляется

-- Обновление ИНДЕКСИРУЕМОГО поля (медленнее)
UPDATE users SET email = 'newemail@example.com' WHERE id = 123;
-- email индексирован, нужно обновить индекс

-- Обновление НЕСКОЛЬКИХ индексируемых полей (еще медленнее)
UPDATE users SET email = 'newemail@example.com', name = 'New Name' WHERE id = 123;
-- Обновлены оба индекса

Сценарий производительности UPDATE:

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    balance DECIMAL(10, 2),
    last_updated TIMESTAMP
);
CREATE INDEX idx_email ON accounts(email);

-- Быстро (не трогаем индексы)
UPDATE accounts SET balance = 1000 WHERE id = 1; -- O(log n)

-- Медленнее (обновляем индекс email)
UPDATE accounts SET email = 'new@example.com' WHERE id = 1; -- O(log n + log m)

4. DELETE (удаление) — ЗАМЕДЛЯЕТСЯ

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

-- БЕЗ индексов
DELETE FROM orders WHERE id = 123;
-- Просто удалить строку из таблицы

-- С индексами
DELETE FROM orders WHERE id = 123;
-- PRIMARY KEY — в индексе (быстро найтись)
-- Но потом нужно удалить из индекса по user_id
-- И удалить из индекса по created_at
-- И т.д.

Сценарий: Удаление 100,000 записей

-- Таблица БЕЗ индексов
DELETE FROM temp_logs WHERE date < '2024-01-01';
-- Время: 1 сек (full scan)

-- Таблица С индексами
CREATE INDEX idx_date ON temp_logs(date);
DELETE FROM temp_logs WHERE date < '2024-01-01';
-- Время: 3 сек (находим через индекс, но потом удаляем из всех индексов)

5. JOIN (соединение) — УЛУЧШАЕТСЯ

Индексы ускоряют JOIN операции:

-- БЕЗ индекса
SELECT u.name, o.order_id 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'john@example.com';
-- Медленно: full scan обеих таблиц и затем join

-- С индексами
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

SELECT u.name, o.order_id 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'john@example.com';
-- Быстро: индекс находит пользователя, индекс находит заказы

6. Aggregations (агрегации) — УЛУЧШАЮТСЯ

Индексы помогают GROUP BY, COUNT, SUM:

-- БЕЗ индекса (медленно)
SELECT category, COUNT(*), SUM(price)
FROM products
GROUP BY category;
-- Full table scan

-- С индексом (быстрее)
CREATE INDEX idx_category ON products(category);
SELECT category, COUNT(*), SUM(price)
FROM products
GROUP BY category;
-- Индекс отсортирован по category, GROUP BY быстрее

7. ORDER BY (сортировка) — УЛУЧШАЕТСЯ

Индекс может избежать сортировки:

-- БЕЗ индекса (медленно, нужна сортировка)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Сканирует ВСЕ rows и сортирует — O(n log n)

-- С индексом (быстро)
CREATE INDEX idx_created_at ON orders(created_at DESC);
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Индекс уже отсортирован, берет только 10 — O(log n)

Матрица влияния индексов

Пример: Real-world сценарий

// JPA Entity
@Entity
@Table(name = "posts")
public class Post {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id; // PRIMARY KEY — индекс автоматически
    
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User author; // ← Нужен индекс для быстрого JOIN
    
    @Column(name = "created_at")
    private LocalDateTime createdAt; // ← Нужен индекс для сортировки
    
    @Column(name = "status")
    private PostStatus status; // ← Нужен индекс для фильтрации
}

// Миграция БД
CREATE TABLE posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    status VARCHAR(50) NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id), -- ← Для JOIN
    INDEX idx_created_at (created_at), -- ← Для ORDER BY
    INDEX idx_status (status) -- ← Для WHERE
);

// Запросы

// ✅ Использует индекс idx_status
List<Post> draft = postRepository.findByStatus(PostStatus.DRAFT);

// ✅ Использует индекс idx_created_at
List<Post> recent = postRepository.findAllByOrderByCreatedAtDesc();

// ✅ Использует индекс idx_user_id для JOIN
List<Post> userPosts = postRepository.findByAuthorId(userId);

// ⚠️ Full table scan (LIKE в начале — нет индекса)
List<Post> search = postRepository.findByContentContains("keyword");

Best Practices при создании индексов

1. Индексируй по WHERE условиям

CREATE INDEX idx_status ON posts(status); -- Часто в WHERE

2. Индексируй по JOIN полям

CREATE INDEX idx_user_id ON posts(user_id); -- Foreign key для JOIN

3. Индексируй по ORDER BY

CREATE INDEX idx_created_at ON posts(created_at); -- ORDER BY

4. Не переусложняй (каждый индекс — это затраты на INSERT/UPDATE)

-- ❌ Слишком много индексов
CREATE INDEX idx_col1 ON table1(col1);
CREATE INDEX idx_col2 ON table1(col2);
CREATE INDEX idx_col3 ON table1(col3);
CREATE INDEX idx_col4 ON table1(col4);
CREATE INDEX idx_col5 ON table1(col5);
-- INSERT станет очень медленным

5. Используй составные индексы

-- ✅ Один индекс для двух колонок
CREATE INDEX idx_user_created ON posts(user_id, created_at);
-- Работает для WHERE user_id = ? AND created_at > ?
-- И для WHERE user_id = ?

-- ❌ Два отдельных индекса
CREATE INDEX idx_user ON posts(user_id);
CREATE INDEX idx_created ON posts(created_at);

Выводы

• Индексы ускоряют SELECT, JOIN, GROUP BY, ORDER BY
• Индексы замедляют INSERT, UPDATE, DELETE (нужно обновлять индекс)
• PRIMARY KEY всегда индексирован (автоматически)
• Foreign keys должны быть индексированы для быстрого JOIN
• Не переусложняй с индексами — каждый стоит памяти и времени на запись
• Используй EXPLAIN для анализа какие индексы используются в запросе
• Составные индексы часто лучше чем несколько отдельных