Что замедляет индекс

Что замедляет индекс в базе данных

Индексы в БД предназначены для ускорения поиска, но при неправильном использовании они могут замедлить систему. Рассмотрим основные причины деградации производительности индексов.

1. Изменения данных (INSERT, UPDATE, DELETE)

Когда вы добавляете, изменяете или удаляете записи, индекс должен быть обновлен. Это требует дополнительных операций ввода-вывода.

-- Таблица с индексом
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    name VARCHAR(100),
    INDEX idx_email (email)
);

-- При каждом INSERT индекс тоже обновляется
INSERT INTO users (id, email, name) VALUES (1, 'user@example.com', 'John');
-- Медленнее, чем без индекса, но результат окупается при SELECT

2. Фрагментация индекса

С течением времени индекс может стать фрагментированным - страницы данных становятся разрозненными на диске, что замедляет чтение.

-- Проверка фрагментации в SQL Server
SELECT 
    object_name(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
    JOIN sys.indexes AS i ON ips.object_id = i.object_id 
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10;

-- Дефрагментация индекса
ALTER INDEX idx_email ON users REBUILD;

3. Выборочность индекса (Selectivity)

Если индекс имеет низкую выборочность (много дублирующихся значений), он становится менее эффективным.

-- ПЛОХО: индекс на поле с низкой выборочностью
CREATE INDEX idx_gender ON users(gender);
-- Только два значения: 'M' и 'F' - индекс неэффективен

-- ХОРОШО: индекс на поле с высокой выборочностью
CREATE INDEX idx_email ON users(email);
-- Каждый email уникален - индекс очень эффективен

-- Проверка выборочности
SELECT 
    name,
    COUNT(*) as count,
    (COUNT(*) * 100.0 / (SELECT COUNT(*) FROM users)) as percentage
FROM users
GROUP BY name
ORDER BY count DESC;

4. Индекс с условиями (LIKE, >, <)

Неправильное использование условий может привести к полному сканированию таблицы вместо использования индекса.

-- ПЛОХО: LIKE с подстановкой в начале
SELECT * FROM users WHERE email LIKE '%example.com';
-- Не использует индекс, сканирует всю таблицу

-- ХОРОШО: LIKE с подстановкой в конце
SELECT * FROM users WHERE email LIKE 'user%';
-- Использует индекс

-- ХОРОШО: точное совпадение
SELECT * FROM users WHERE email = 'user@example.com';
-- Использует индекс

5. Составной индекс неправильного порядка

Порядок полей в составном индексе критично влияет на производительность.

-- Индекс создан в порядке: (department, salary)
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- БЫСТРО: используется весь индекс
SELECT * FROM employees 
WHERE department = 'Sales' AND salary > 50000;

-- МЕДЛЕННО: индекс не используется эффективно
-- Потому что ищем по salary без department
SELECT * FROM employees WHERE salary > 50000;

-- Правило: наиболее часто используемые условия в начале
CREATE INDEX idx_salary_dept ON employees(salary, department);

6. Индекс по большому столбцу

Индекс по текстовому полю большого размера требует больше памяти и медленнее.

-- ПЛОХО: индекс на весь LONGTEXT
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content LONGTEXT,
    INDEX idx_content (content)
);
-- Очень медленно

-- ХОРОШО: индекс только на первые символы
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content LONGTEXT,
    INDEX idx_content (content(100))
);
-- Быстрее, используем префиксный индекс

7. Использование функций в WHERE

Применение функций к полям индекса приводит к полному сканированию.

-- ПЛОХО: функция в WHERE
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- Не использует индекс на created_at

-- ХОРОШО: условие без функции
SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- Использует индекс

-- ПЛОХО: функция на полях
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';

-- ХОРОШО: без функции
SELECT * FROM users WHERE email = 'user@example.com';

8. Слишком много индексов

Кажный индекс требует обновления при INSERT, UPDATE, DELETE, замедляя эти операции.

-- ПЛОХО: индекс на каждое поле
CREATE INDEX idx_fname ON users(first_name);
CREATE INDEX idx_lname ON users(last_name);
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_phone ON users(phone);
CREATE INDEX idx_address ON users(address);
-- Много индексов = медленные INSERT/UPDATE

-- ХОРОШО: составной индекс для частых условий
CREATE INDEX idx_search ON users(first_name, last_name, email);

9. Статистика индекса устаревшая

Если статистика не обновлена, оптимизатор может неправильно выбрать план запроса.

-- Обновить статистику индекса
ANALYZE TABLE users;

-- MySQL:
ANALYZE TABLE users;

-- PostgreSQL:
ANALYZE users;

-- SQL Server:
EXEC sp_updatestats;

10. Покрывающий индекс недостаточен

Если индекс содержит не все необходимые поля, БД должна обратиться к основной таблице (bookmark lookup).

-- Индекс только на email
CREATE INDEX idx_email ON users(email);

-- МЕДЛЕННО: нужно обратиться к таблице
SELECT id, email, name FROM users WHERE email = 'user@example.com';
-- Индекс найдёт строку, но потребуется дополнительное чтение

-- БЫСТРО: покрывающий индекс
CREATE INDEX idx_email_cover ON users(email) INCLUDE (id, name);
-- Всё необходимое уже в индексе

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

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email", columnList = "email", unique = true),
    @Index(name = "idx_created", columnList = "created_at")
})
public class User {
    @Id
    private Long id;
    
    @Column(unique = true)
    private String email;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

// Правильный запрос с использованием индекса
public interface UserRepository extends JpaRepository<User, Long> {
    @Query("SELECT u FROM User u WHERE u.email = :email")
    Optional<User> findByEmail(@Param("email") String email);
    
    // Для диапазонов
    @Query("SELECT u FROM User u WHERE u.createdAt >= :from AND u.createdAt < :to")
    List<User> findCreatedBetween(
        @Param("from") LocalDateTime from,
        @Param("to") LocalDateTime to
    );
}

Вывод

Индексы замедляются из-за:

1. Частых INSERT/UPDATE/DELETE операций
2. Фрагментации индекса со временем
3. Низкой выборочности поля
4. Неправильного построения запроса (LIKE %, функции)
5. Неправильного порядка полей в составных индексах
6. Слишком большого количества индексов
7. Устаревшей статистики

Регулярно анализируйте план выполнения запросов (EXPLAIN) и мониторьте использование индексов для оптимальной производительности.