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

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

Индексы в базах данных - это критические структуры для оптимизации производительности. Давайте разберемся, в каких операциях SQL они используются и как это влияет на производительность.

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

1. WHERE с условиями поиска

Это главная область использования индексов:

-- БЕЗ индекса: полное сканирование таблицы (O(n))
SELECT * FROM users WHERE id = 42;

-- С индексом на id: быстрый поиск (O(log n))
CREATE INDEX idx_users_id ON users(id);
SELECT * FROM users WHERE id = 42; -- ✓ Использует индекс

-- С индексом на email: быстрый поиск
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = user@example.com; -- ✓ Использует индекс

2. JOIN операции

Индексы на foreign key очень ускоряют join:

-- Индекс на user_id в orders помогает быстро найти заказы пользователя
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Эта операция будет быстрой
SELECT u.name, o.amount 
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 5; -- ✓ Индексы на оба id ускоряют join

3. ORDER BY и сортировка

Индексы могут избежать явной сортировки:

-- БЕЗ индекса: сортировка в памяти
SELECT * FROM users ORDER BY created_at DESC;

-- С индексом: уже отсортировано
CREATE INDEX idx_users_created_at ON users(created_at DESC);
SELECT * FROM users ORDER BY created_at DESC; -- ✓ Нет Sort операции

-- Индекс на составном ключе помогает при сортировке по нескольким полям
CREATE INDEX idx_users_status_created ON users(status, created_at);
SELECT * FROM users WHERE status = active ORDER BY created_at;

4. DISTINCT операции

-- Индекс помогает найти уникальные значения быстрее
CREATE INDEX idx_users_country ON users(country);

SELECT DISTINCT country FROM users; -- ✓ Быстрее с индексом
SELECT COUNT(DISTINCT country) FROM users; -- ✓ Может быть быстрее

5. GROUP BY агрегация

-- Индекс на поле для группировки ускоряет результат
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

SELECT customer_id, COUNT(*), SUM(amount) 
FROM orders
GROUP BY customer_id; -- ✓ Может использовать индекс

6. LIMIT с OFFSET

-- Без индекса на ORDER BY, LIMIT может быть медленным
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 1000000;
-- Может отсканировать 1 миллион строк даже при LIMIT 10

-- С индексом на id: быстрый LIMIT
CREATE INDEX idx_users_id ON users(id);
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 1000000; -- ✓ Быстрее

7. BETWEEN и диапазоны

-- Индекс очень эффективен для диапазонных запросов
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Все эти запросы используют индекс
SELECT * FROM orders WHERE created_at BETWEEN 2024-01-01 AND 2024-12-31;
SELECT * FROM orders WHERE created_at >= 2024-01-01;
SELECT * FROM orders WHERE price > 100 AND price < 1000;

8. IN и OR условия

-- Индекс помогает при IN
CREATE INDEX idx_users_status ON users(status);

SELECT * FROM users WHERE status IN (active, premium); -- ✓ Использует индекс

-- OR может использовать индексы на оба столбца
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_category_id ON posts(category_id);

SELECT * FROM posts WHERE user_id = 1 OR category_id = 5; -- ✓ Возможно использует оба индекса

Составные индексы (Composite Indexes)

-- Составной индекс может использоваться для нескольких целей
CREATE INDEX idx_users_country_city ON users(country, city);

-- Это использует индекс
SELECT * FROM users WHERE country = US AND city = NY;

-- Это тоже использует индекс
SELECT * FROM users WHERE country = US;

-- Это НЕ использует индекс (пропущен первый столбец)
SELECT * FROM users WHERE city = NY; -- ✗ Не эффективно

Операции, где индексы НЕ помогают

-- LIKE с % в начале (приходится сканировать все)
SELECT * FROM users WHERE email LIKE %@example.com; -- ✗ Нет индекса

-- LIKE с % в конце (индекс может помочь)
SELECT * FROM users WHERE email LIKE user%; -- ✓ Может использовать индекс

-- Функции на столбце (обычно не используют индекс)
SELECT * FROM users WHERE UPPER(email) = USER@EXAMPLE.COM; -- ✗ Нет индекса

-- Арифметические операции
SELECT * FROM products WHERE price * quantity > 1000; -- ✗ Нет индекса
SELECT * FROM products WHERE price > 1000 / quantity; -- ✗ Нет индекса

-- NOT IN (обычно неэффективно)
SELECT * FROM orders WHERE customer_id NOT IN (1, 2, 3); -- ✗ Полное сканирование

В Java коде

// JPA/Hibernate с индексами
@Entity
public class User {
    @Id
    private Long id;
    
    @Column(name = "email", unique = true)
    private String email; // Уникальный индекс автоматически
    
    @Column(name = "created_at")
    @Index(name = "idx_created_at") // Явный индекс
    private LocalDateTime createdAt;
}

// Query быстрая благодаря индексам
public interface UserRepository extends JpaRepository<User, Long> {
    // Использует индекс на id (primary key)
    Optional<User> findById(Long id);
    
    // Использует индекс на email
    Optional<User> findByEmail(String email);
    
    // Использует индекс на created_at и сортировку
    List<User> findAllByOrderByCreatedAtDesc();
}

// Но плохие запросы остаются медленными
public interface UserRepository extends JpaRepository<User, Long> {
    // Без индекса на name - полное сканирование
    List<User> findByName(String name);
    
    // LIKE в начале - полное сканирование
    @Query("SELECT u FROM User u WHERE u.email LIKE %:email")
    List<User> findByEmailLike(@Param("email") String email);
}

Проверка использования индекса

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

-- MySQL
EXPLAIN
SELECT * FROM users WHERE email = user@example.com;
-- Смотреть на "key" и "rows" для оценки эффективности

Выводы

Индексы ускоряют:

• WHERE условия (особенно ==, <, >, BETWEEN)
• JOIN операции по foreign keys
• ORDER BY и LIMIT (сортировка)
• GROUP BY агрегацию
• DISTINCT

Индексы НЕ помогают:

• LIKE %value (в начале)
• Функции на столбцах
• Арифметика на столбцах
• NOT IN
• Полные сканирования для анализа

Лучшая практика - создавать индексы на столбцы, которые часто используются в WHERE, JOIN, ORDER BY и сортировке.