Когда выгоднее сканировать таблицу целиком, чем использовать индекс?

Когда выгоднее сканировать таблицу целиком вместо индекса?

Интересный вопрос! Индекс — не всегда лучше. Есть сценарии, когда full table scan быстрее.

Теория: Query Optimizer Decision

База данных сама решает: использовать индекс или сканировать. Основано на:

Cost = (Rows to read) × (Cost per row) + (Random seeks)

Full scan: 1000000 rows × 1 = 1000000
Index scan: 1000000 seeks + 1000000 reads = 2000000 (медленнее!)

Сценарий 1: Большой результат (> 5-10% таблицы)

-- Таблица: 100M rows
-- На диске: 40 GB, очень кэширована

-- ДА, используй индекс
SELECT * FROM users WHERE country = 'US';  -- 1% результат
-- Cost: индекс seek + 1M reads

-- НЕТ, не используй индекс (сканируй целиком)
SELECT * FROM users WHERE age > 18;  -- 80% результат
-- Cost: 80M random seeks > 40GB seq read
-- Full scan быстрее на 3-5x!

На практике:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 18;
-- Seq Scan cost: 0..100000
-- Index Scan cost: 0..2000000 (медленнее!)

-- База выберет Seq Scan (full table scan)

Сценарий 2: Индекс не кэширован, таблица в памяти

-- Индекс на диске (не в памяти)
SELECT * FROM events WHERE user_id = 123;

-- Cost индекса:
--   1. Read index pages (disk I/O) - SLOW
--   2. Random seek в таблице - SLOW
--   Total: несколько ms

-- Cost full scan:
--   Sequential read из памяти - FAST
--   Даже для 100M rows: 50-100 ms

Сценарий 3: Очень селективный фильтр, но много джойнов

-- 1000 rows в результате, но нужны 5 других таблиц
SELECT u.id, o.amount, p.name, c.address
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE u.email = 'test@example.com';

-- Индекс по email - ок (1 row)
-- Но потом 5 random seeks в другие таблицы
-- Full seq scan + hash join может быть быстрее!

Сценарий 4: Очень узкие индексы (много NULL значений)

-- Таблица: 100M rows
-- Индекс BTREE для nullable колонки

SELECT * FROM orders WHERE promo_code IS NOT NULL;

-- Индекс проблем: BTREE плохо с NULL
-- Может быть неэффективным

-- Full scan: просто последовательный read
-- Часто быстрее!

Практические пороги (когда full scan быстрее)

Пример из практики

-- Плохая схема индексов
CREATE INDEX idx_status ON orders(status);
-- 10M orders, 99% имеют status='COMPLETED'

-- Запрос
SELECT * FROM orders WHERE status = 'COMPLETED';
-- Result: 9.9M rows (99%)

-- Индекс читает: index pages + 9.9M random seeks
-- Full scan: просто читает 40GB последовательно
-- РЕЗУЛЬТАТ: Full Scan 10x быстрее!

-- Fix:
DROP INDEX idx_status;
-- Или используй partial index:
CREATE INDEX idx_status_pending ON orders(id) 
WHERE status IN ('PENDING', 'PROCESSING');

Как оптимизировать выбор

1. Используй ANALYZE для статистики

ANALYZE TABLE orders;  -- обновляет статистику

EXPLAIN SELECT * FROM orders WHERE status = 'COMPLETED';
-- Теперь оптимизатор знает что это 99% и выберет full scan

2. Force Full Scan если нужно

-- PostgreSQL
SELECT /*+ SEQSCAN(orders) */ * FROM orders WHERE status = 'COMPLETED';

-- MySQL
SELECT * FROM orders USE INDEX() WHERE status = 'COMPLETED';

-- MS SQL
SELECT * FROM orders WITH (NOLOCK) WHERE status = 'COMPLETED';

3. Используй Partial Indexes

-- Только для редких значений
CREATE INDEX idx_orders_cancelled ON orders(id) WHERE status = 'CANCELLED';
-- 1% данных, очень selective

-- Для common values - не нужен индекс
-- Full scan быстрее

Параметры оптимизатора

-- PostgreSQL: random_page_cost vs seq_page_cost
-- Если SSD, можно уменьшить random_page_cost
SET random_page_cost = 1.1;  -- SSD, случайный доступ почти как последовательный

-- MS SQL: adjust cost threshold
EXEC sp_configure 'cost threshold for parallelism', 50;

Вывод

Full scan быстрее индекса когда:

1. Результат > 5-10% таблицы
2. Индекс не в памяти
3. Таблица в памяти (кэширована)
4. Много джойнов после фильтра
5. Индекс некачественный (много NULL)

На практике:

• 80% моих запросов используют индексы (хорошо)
• 15% используют full scan (огромные результаты)
• 5% используют partial indexes (оптимальное решение)

Главное правило: Доверяй оптимизатору БД, но понимай его логику!