Как количество данных влияет на выбор индекса для поиска?

Что хотят услышать интервьюеры:

Чем больше данных, тем важнее выбирать индекс, который дает быстрый доступ по нужным запросам, а не просто экономит место. Для небольших таблиц иногда хватает полного сканирования, потому что накладные расходы на индекс могут быть выше пользы. Для больших объемов данных обычно приоритет у индексов, которые хорошо уменьшают число читаемых строк и подходят под тип запросов.

Определение:

Индекс — это дополнительная структура данных, которая ускоряет поиск строк по одному или нескольким полям. Выбор индекса зависит от объема данных, характера запросов и соотношения чтений к записям. При росте данных стоимость полного сканирования увеличивается, поэтому индекс начинает давать заметный выигрыш, но сам индекс тоже занимает место и замедляет вставки, обновления и удаления.

Пример использования:

Допустим, есть таблица заказов с миллионами строк, и чаще всего ищут заказы по customer_id и дате. В таком случае индекс по customer_id или составной индекс по customer_id, order_date обычно полезен. Если же таблица небольшая и запросы редкие, индекс может не окупиться.

-- Частый поиск заказов конкретного клиента
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Если часто ищут по клиенту и диапазону дат
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Пояснение кода:

• Первый индекс ускоряет поиск всех строк по конкретному customer_id.
• Второй индекс полезен, если запросы фильтруют сначала по клиенту, а потом по дате.
• При большом объеме данных это уменьшает число просматриваемых строк с миллионов до небольшой выборки.
• Если запросы идут по другим полям, такой индекс может не помочь.
• Чем больше и шире индекс, тем выше накладные расходы на запись и хранение.

Ключевые моменты:

• Чем больше таблица, тем чаще индекс дает выигрыш по времени поиска.
• Индекс выбирают под реальные запросы, а не только под размер данных.
• Для маленьких таблиц или низкой частоты запросов полное сканирование может быть дешевле.
• На больших данных особенно важны селективность и соответствие порядку колонок в составном индексе.
• Индекс ускоряет чтение, но ухудшает скорость записи и увеличивает расход памяти/диска.