Почему индекс может не использоваться при его добавлении?

Почему индекс может не использоваться при его добавлении

Этот вопрос относится к базам данных (SQL), а не напрямую к Java, но Java разработчикам важно это знать. Давай разберёмся, почему оптимизатор БД может проигнорировать индекс.

Основные причины игнорирования индекса

1. Индекс не выборочный (селективный)

Если индекс содержит слишком много одинаковых значений, он неэффективен:

-- Таблица users с 1000000 записей
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    is_active BOOLEAN,  -- 99% значений TRUE
    name VARCHAR(100),
    INDEX idx_active (is_active)
);

-- Этот индекс не используется
SELECT * FROM users WHERE is_active = true; -- вернёт 990000 строк
-- Оптимизатор считает: "Дешевле full table scan, чем индекс + fetch 99% таблицы"

-- Но вот этот используется (выборочнее)
SELECT * FROM users WHERE is_active = false; -- вернёт 10000 строк

2. Недостаточно данных в таблице

Для маленьких таблиц индекс неэффективен:

CREATE TABLE small_table (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    INDEX idx_name (name)
);

INSERT INTO small_table VALUES (1, Alice), (2, Bob), (3, Charlie);

-- Для 3 строк full table scan быстрее, чем поиск по индексу
SELECT * FROM small_table WHERE name = Alice;
-- Оптимизатор: "Индекс + random access медленнее, чем просканировать 3 строки"

3. Type casting и функции в WHERE

Если в условии есть функция или преобразование типа, индекс может не использоваться:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    age INT,
    INDEX idx_age (age)
);

-- Индекс НЕ используется (функция преобразует значение)
SELECT * FROM users WHERE CAST(age AS VARCHAR) = 30;
SELECT * FROM users WHERE age + 0 = 30; -- функция
SELECT * FROM users WHERE UPPER(name) = ALICE; -- функция на column

-- Индекс ИСПОЛЬЗУЕТСЯ (условие прямое)
SELECT * FROM users WHERE age = 30;

4. NULL значения

В некоторых БД NULL значения не индексируются:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    cancelled_at TIMESTAMP NULL,
    INDEX idx_cancelled (cancelled_at)
);

-- В некоторых БД это может не использовать индекс
SELECT * FROM orders WHERE cancelled_at IS NULL;

-- Лучше использовать
SELECT * FROM orders WHERE cancelled_at IS NOT NULL;

5. Оптимизатор выбрал другой план

Оптимизатор может выбрать другой индекс или full scan на основе статистики:

-- У таблицы 2 индекса
CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT,
    price DECIMAL(10, 2),
    INDEX idx_category (category_id),
    INDEX idx_price (price)
);

-- Оптимизатор выбирает индекс, который вернёт меньше строк
SELECT * FROM products 
WHERE category_id = 5 AND price > 100;
-- Может использовать idx_category, idx_price или оба (index merge)

6. BETWEEN и IN с большим диапазоном

CREATE TABLE events (
    id BIGINT PRIMARY KEY,
    created_at TIMESTAMP,
    INDEX idx_created (created_at)
);

-- Индекс используется
SELECT * FROM events WHERE created_at > 2024-01-01 AND created_at < 2024-01-02;

-- Индекс может НЕ использоваться (слишком большой диапазон)
SELECT * FROM events WHERE created_at BETWEEN 2020-01-01 AND 2025-12-31;
-- Оптимизатор: "Full scan быстрее, так как вернётся большой % таблицы"

7. Неправильный порядок условий

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

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id INT,
    status VARCHAR(50),
    created_at TIMESTAMP,
    INDEX idx_user_status_created (user_id, status, created_at)
);

-- Индекс ИСПОЛЬЗУЕТСЯ (левый префикс)
SELECT * FROM orders WHERE user_id = 5 AND status = completed;
SELECT * FROM orders WHERE user_id = 5;

-- Индекс может НЕ использоваться (пропущен первый столбец)
SELECT * FROM orders WHERE status = completed AND created_at > NOW();
-- Нарушен order индекса (user_id пропущен)

8. Диапазон, начинающийся со LIKE %

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(100),
    INDEX idx_username (username)
);

-- Индекс НЕ используется
SELECT * FROM users WHERE username LIKE %alice%; -- поиск в начале
SELECT * FROM users WHERE username LIKE %alice; -- поиск в начале

-- Индекс используется
SELECT * FROM users WHERE username LIKE alice%; -- поиск в конце

Как это влияет на Java код

public class JdbcIndexExample {
    // Плохо: может не использовать индекс
    public List<User> findByNameLike(String pattern) {
        String query = "SELECT * FROM users WHERE UPPER(name) LIKE ?";
        // name имеет индекс, но UPPER() функция его портит
        return jdbcTemplate.query(query, new Object[]{"%" + pattern + "%"});
    }
    
    // Хорошо: использует индекс
    public List<User> findByNamePrefix(String prefix) {
        String query = "SELECT * FROM users WHERE name LIKE ?";
        return jdbcTemplate.query(query, new Object[]{prefix + "%"});
    }
    
    // Плохо: функция на column
    public User findByAge(int age) {
        String query = "SELECT * FROM users WHERE CAST(age AS VARCHAR) = ?";
        return jdbcTemplate.queryForObject(query, new Object[]{age});
    }
    
    // Хорошо: прямое сравнение
    public User findById(int id) {
        String query = "SELECT * FROM users WHERE age = ?";
        return jdbcTemplate.queryForObject(query, new Object[]{age});
    }
}

Как проверить, используется ли индекс

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = 5;
-- Посмотреть "Index Scan" или "Seq Scan"

-- MySQL
EXPLAIN SELECT * FROM users WHERE id = 5;
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 5;
-- Посмотреть "type: index" или "type: range"

-- SQLite
EXPLAIN QUERY PLAN SELECT * FROM users WHERE id = 5;

Best practices

public class DatabaseOptimization {
    // 1. Используй прямые сравнения
    public List<User> findByAge(int age) {
        return query("SELECT * FROM users WHERE age = ?", age);
    }
    
    // 2. Избегай функций на columns
    // ПЛОХО: WHERE YEAR(created_at) = 2024
    // ХОРОШО:
    public List<Order> findByYear(int year) {
        return query(
            "SELECT * FROM orders WHERE created_at >= ? AND created_at < ?",
            LocalDateTime.of(year, 1, 1, 0, 0, 0),
            LocalDateTime.of(year + 1, 1, 1, 0, 0, 0)
        );
    }
    
    // 3. Для LIKE используй префикс
    public List<User> findByName(String name) {
        return query("SELECT * FROM users WHERE name LIKE ?", name + "%");
    }
    
    // 4. Использование EXPLAIN для проверки
    public void explainQuery(String sql) {
        jdbcTemplate.query("EXPLAIN " + sql, rs -> {
            System.out.println(rs.getString("EXPLAIN"));
            return null;
        });
    }
    
    // 5. Составные индексы требуют порядка
    // INDEX (user_id, status, created_at)
    public List<Order> findOrders(int userId, String status) {
        // Хорошо: использует индекс (левый префикс)
        return query(
            "SELECT * FROM orders WHERE user_id = ? AND status = ?",
            userId, status
        );
    }
}

Вывод

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

• Недостаточная селективность
• Функции на columns
• Неправильный порядок в составном индексе
• Слишком большой диапазон результатов
• LIKE с % в начале
• Type casting

Всегда проверяй EXPLAIN план запроса и профилируй медленные запросы!