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

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

Это один из самых часто встречаемых багов, которые я встречал в production. Индексы помогают, но только если их правильно использовать.

1. Индекс игнорируется из-за типов данных

-- Таблица
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  user_id VARCHAR(36),  -- UUID как строка
  name VARCHAR(255)
);
CREATE INDEX idx_user_id ON users(user_id);

-- ПЛОХО: индекс НЕ используется
SELECT * FROM users WHERE user_id = 123;  -- Ищем число в VARCHAR
-- PostgreSQL конвертирует: CAST(user_id AS INTEGER) = 123
-- Это функция на столбце → индекс игнорируется!

-- ХОРОШО:
SELECT * FROM users WHERE user_id = '123';  -- Строка

Почему: Когда на столбец применена функция (implicit или explicit), БД не может использовать индекс. Нужно искать по изначальному типу.

2. Индекс на столбце с функцией

-- ПЛОХО: индекс обычный
CREATE TABLE orders (
  id INTEGER,
  created_at TIMESTAMP
);
CREATE INDEX idx_created_at ON orders(created_at);

-- Этот запрос НЕ использует индекс
SELECT COUNT(*) FROM orders 
WHERE DATE(created_at) = '2024-03-20';
-- DATE() — функция на столбце → индекс не помогает

-- ХОРОШО: либо функциональный индекс
CREATE INDEX idx_created_date ON orders(DATE(created_at));

-- Либо range query
SELECT COUNT(*) FROM orders
WHERE created_at >= '2024-03-20'::TIMESTAMP 
AND created_at < '2024-03-21'::TIMESTAMP;
-- Это использует индекс на created_at

3. Индекс на низко-селективных столбцах

-- ПЛОХО: индекс на столбце с мало уникальных значений
CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  event_type VARCHAR(20),  -- только 5 типов: 'click', 'view', 'purchase' и т.д.
  timestamp TIMESTAMP
);
CREATE INDEX idx_event_type ON events(event_type);

-- Для 1 млрд строк, где 200M 'click' событий
SELECT * FROM events WHERE event_type = 'click';
-- Индекс найдёт 200M строк, что 20% таблицы
-- БД подумает: "быстрее полный scan" и не использует индекс

-- Решение: составной индекс
CREATE INDEX idx_type_time ON events(event_type, timestamp DESC);
SELECT * FROM events 
WHERE event_type = 'click' 
AND timestamp > NOW() - INTERVAL '1 day';

4. Индекс, но в неправильном порядке столбцов

-- ПЛОХО: индекс создан в неправильном порядке
CREATE TABLE sales (
  id BIGINT,
  user_id INTEGER,
  product_id INTEGER,
  amount DECIMAL(10,2)
);
CREATE INDEX idx_user_product ON sales(product_id, user_id);

-- Этот запрос НЕ использует индекс эффективно
SELECT * FROM sales WHERE user_id = 42;
-- Индекс начинается с product_id, а мы ищем по user_id
-- Нужно сканировать весь индекс

-- ХОРОШО: переделать индекс
DROP INDEX idx_user_product;
CREATE INDEX idx_user_product ON sales(user_id, product_id);
-- Теперь WHERE user_id = 42 использует индекс полностью

Правило: В составном индексе порядок столбцов важен — более селективные и часто используемые в WHERE идут первыми.

5. Индекс, который не помогает из-за LIMIT и OFFSET

-- ПЛОХО: индекс есть, но данные не отсортированы
CREATE TABLE users (
  id SERIAL,
  status VARCHAR(20),
  created_at TIMESTAMP,
  name VARCHAR(255)
);
CREATE INDEX idx_status ON users(status);

-- Запрос требует сортировку, индекс не помогает
SELECT * FROM users 
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 10 OFFSET 1000;
-- Индекс найдёт все 'active', но затем нужно сортировать по created_at
-- Дорого на больших выборках

-- ХОРОШО: составной индекс с сортировкой
CREATE INDEX idx_status_created ON users(status, created_at DESC);
-- Теперь индекс уже отсортирован, не нужна доп. сортировка

6. Индекс, но слишком много индексов (bloat)

-- ПЛОХО: слишком много индексов
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(255),
  category VARCHAR(100),
  price DECIMAL(10,2)
);
CREATE INDEX idx_name ON products(name);
CREATE INDEX idx_category ON products(category);
CREATE INDEX idx_price ON products(price);
CREATE INDEX idx_name_category ON products(name, category);
CREATE INDEX idx_category_price ON products(category, price);
-- 5 индексов!

-- Проблемы:
-- - INSERT/UPDATE медленнее (нужно обновлять все индексы)
-- - Память больше
-- - Оптимизатор запутается

-- ХОРОШО: 1-2 ключевых индекса
CREATE INDEX idx_category_price ON products(category, price);
-- Покрывает queries по категории и цене

7. Индекс на NULL значениях

-- ПЛОХО: B-tree индекс не находит NULL
CREATE TABLE logs (
  id BIGINT,
  error_code INTEGER,  -- NULL если нет ошибки
  message TEXT
);
CREATE INDEX idx_error ON logs(error_code);

-- Этот запрос НЕ использует индекс
SELECT * FROM logs WHERE error_code IS NULL;
-- B-tree индексы не хранят NULL значения стандартно

-- ХОРОШО: использовать partial индекс
CREATE INDEX idx_error_null ON logs(error_code) WHERE error_code IS NULL;
-- Или в PostgreSQL 15+: CREATE INDEX ... WHERE error_code IS NOT DISTINCT FROM NULL

Практический пример: диагностика

-- PostgreSQL: как найти неиспользуемые индексы
SELECT 
  schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL: explain вывод
EXPLAIN 
SELECT * FROM users WHERE status = 'active';
-- Если key_len маленький или NULL — индекс не используется

Вывод

Индексы — это как лекарство: без них плохо, но передозировка тоже вредит. Ключевой навык — понимать plan explain и знать, почему конкретный индекс (не) используется в конкретном запросе.