Как индекс повышает скорость выборки из таблицы

# Как индекс повышает скорость выборки из таблицы

Основной механизм

Индекс — это структура данных, которая позволяет СУБД быстро находить данные без полного сканирования таблицы. Вместо линейного поиска через все строки, база использует упорядоченную структуру (чаще всего B-tree), что снижает количество дисковых операций с O(n) до O(log n).

Как это работает

-- Без индекса: полное сканирование таблицы (Full Table Scan)
SELECT * FROM users WHERE email = "john@example.com";
-- Время: O(n), может быть очень медленно на 1М+ строк

-- С индексом по email: быстрый поиск через B-tree
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = "john@example.com";
-- Время: O(log n), находит за несколько операций

Пример в коде Java + JPA

@Entity
@Table(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    
    @Column(name = "email")
    @Index(name = "idx_email")  // Создаёт индекс автоматически
    private String email;
    
    @Column(name = "created_at")
    @Index(name = "idx_created_at")
    private LocalDateTime createdAt;
}

// Repository с условиями, использующими индексы
@Repository
public interface UserRepository extends JpaRepository<User, UUID> {
    User findByEmail(String email);  // Использует idx_email
    List<User> findByCreatedAtAfter(LocalDateTime date);  // Использует idx_created_at
}

Внутренняя структура B-tree индекса

B-tree имеет иерархическую структуру с корневым узлом, промежуточными узлами и листьями, содержащими указатели на строки таблицы.

Уровень 0 (корень):        [email_10...email_20]
                           /              \
Уровень 1:      [a...m]              [n...z]
                  /   \                 /   \
Уровень 2 (листья): ссылки на строки таблицы

Поиск по email "john@" займёт ~3 операции вместо сканирования всей таблицы.

Типы индексов и их применение

1. Простой индекс (Single Column)

CREATE INDEX idx_user_status ON users(status);
-- Эффективен для условий: WHERE status = active

2. Составной индекс (Composite)

CREATE INDEX idx_user_org_status ON users(organization_id, status);
-- Эффективен для:
-- WHERE organization_id = ? AND status = ?
-- WHERE organization_id = ? (first part of index)

3. Уникальный индекс

CREATE UNIQUE INDEX idx_user_email ON users(email);
-- Гарантирует уникальность и ускоряет поиск

4. Частичный индекс (Partial)

CREATE INDEX idx_active_users ON users(email) WHERE status = active;
-- Индексирует только активных пользователей, экономит место

Критические моменты

Когда индексы помогают:

• Условия WHERE, JOIN, ORDER BY
• Поиск по точному значению или диапазону
• Большие таблицы (100k+ строк)

Когда индексы не помогают:

• Функции: WHERE LOWER(email) = ? (индекс не используется)
• Отрицание: WHERE status != inactive (может быть медленнее)
• Очень селективные значения (если результат 50%+ таблицы, Full Scan быстрее)

Минусы индексов:

• Занимают дополнительную память (5-20% размера таблицы)
• Замедляют INSERT/UPDATE/DELETE (требуется обновить индекс)
• Требуют обслуживания (дефрагментация)

Оптимизация запросов

// ❌ Плохо: без индекса, медленно на больших таблицах
@Query("SELECT u FROM User u WHERE YEAR(u.createdAt) = 2025")
List<User> findByYear(int year);

// ✅ Хорошо: используется диапазонный индекс
@Query("SELECT u FROM User u WHERE u.createdAt >= :start AND u.createdAt < :end")
List<User> findByDateRange(@Param("start") LocalDateTime start, 
                           @Param("end") LocalDateTime end);

EXPLAIN для анализа

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = "john@example.com";
-- Outputs:
-- Seq Scan on users (cost=0.00..35.50 rows=1) — без индекса
-- Index Scan using idx_email on users (cost=0.27..8.29 rows=1) — с индексом

Индекс — это торговля памятью на скорость. На практике правильно спроектированные индексы ускоряют READ операции в 10-100 раз, при этом замедляя WRITE на 5-10%, что почти всегда выгодно для OLTP систем.