В аналитическом запросе по большой таблице чем колоночный индекс принципиально выгоднее обычного строчного индекса?
Колоночный индекс выгоднее для аналитики, потому что хранит значения одного столбца вместе: запрос читает только нужные столбцы, эффективно сжимает данные и может обрабатывать их пакетно. Обычный строчный индекс лучше подходит для точечного поиска и небольших выборок, где важно быстро найти отдельные записи.
Традиционное строчное хранение создавалось прежде всего для OLTP-нагрузки: быстро найти или изменить конкретную строку, проверить ключ и поддержать транзакцию. Аналитические запросы имеют другую структуру: они читают большие диапазоны данных, используют небольшое число столбцов и выполняют агрегации.
Колоночное хранение появилось как способ уменьшить лишний ввод-вывод и повысить эффективность обработки больших объёмов данных. Вместо чтения всех полей каждой строки система читает только те столбцы, которые участвуют в фильтрации, группировке и вычислениях.
Предположим, таблица фактов содержит десятки столбцов и миллиарды строк, а запросу нужны только дата, идентификатор товара и сумма. При строчном хранении чтение диапазона обычно затрагивает целые строки или значительную часть их структуры, даже если остальные столбцы не нужны.
Это увеличивает объём чтения, нагрузку на кэш и стоимость агрегаций. Попытка решить проблему большим покрывающим B-деревом может привести к широкому индексу, дорогому обслуживанию при изменениях и плохой эффективности для запросов, выбирающих значительную долю таблицы.
В колоночном формате значения каждого столбца хранятся совместно. Поэтому движок может выполнить column pruning — исключить из чтения столбцы, которые не нужны запросу. Для аналитики это особенно важно, когда таблица широкая, а запрос использует лишь несколько полей.
Однотипные значения хорошо сжимаются. Дополнительно движок может обрабатывать значения пакетами, применяя векторизованные операции к группе строк вместо отдельной обработки каждой строки. В системах с поддержкой колоночных индексов также возможна сегментная фильтрация: метаданные сегмента, например минимальное и максимальное значение, позволяют пропустить сегменты, которые заведомо не удовлетворяют предикату.
У колоночного индекса есть цена. Точечные операции, частые изменения отдельных строк и запросы, возвращающие несколько строк по селективному ключу, обычно лучше обслуживаются B-деревом. Обновления колоночных структур могут включать промежуточные области хранения и последующее объединение данных; конкретная реализация зависит от СУБД.
В SQL Server кластерный columnstore организует данные в rowgroup-группы и использует специальные структуры для недавно изменённых строк. Это не означает, что любой фильтр автоматически пропустит большую часть данных: эффективность сегментной фильтрации зависит от распределения значений и от того, насколько данные физически коррелируют с условием.
Колоночный индекс не заменяет все остальные индексы. В смешанной системе часто используют колоночное хранение для больших сканирующих и агрегирующих запросов, а отдельные строчные индексы — для точечных обращений и операций поддержания целостности.
В витрине продаж запросы строят ежемесячные агрегаты по большой таблице фактов, выбирая четыре столбца из нескольких десятков. Команда рассматривает три варианта: оставить строчное хранение, создать широкий покрывающий B-деревянный индекс или использовать колоночный индекс.
Оставить таблицу без изменений проще, но запросы читают много лишних данных. Широкий B-деревянный индекс может ускорить отдельный отчёт, однако увеличивает размер хранилища и стоимость вставок. Колоночный индекс лучше соответствует сканированию и агрегации, поскольку сокращает чтение ненужных столбцов и эффективнее сжимает однородные данные.
Выбирают колоночный индекс, сохраняя узкие строчные индексы для точечного доступа, необходимого приложению. В результате отчётный контур обрабатывает большие диапазоны более эффективно, а OLTP-запросы не вынуждены использовать аналитическую структуру. Итог подтверждают сравнением фактических планов, объёма чтения и времени выполнения на репрезентативных данных.
Нет. Чтение только нужных столбцов и пропуск сегментов — разные механизмы. Если значения фильтра распределены по большинству сегментов, сегментная фильтрация почти не сократит число прочитанных сегментов, хотя экономия на выборе столбцов и сжатии всё равно может остаться.
Такой запрос требует найти конкретное значение и быстро получить связанную запись. Колоночный формат оптимизирован для пакетного чтения и обработки диапазонов, а не обязательно для точечного доступа. Узкий B-деревянный индекс с подходящим ключом часто выполняет такую операцию с меньшим объёмом работы.
Сжатые колоночные группы удобны для чтения, но изменение отдельной строки может нарушить их организацию. СУБД может временно размещать изменённые или новые строки в отдельной структуре, поддерживать сведения об удалённых строках и позже объединять данные. Поэтому частые изменения увеличивают стоимость обслуживания и требуют оценивать режим нагрузки, а не только скорость аналитического чтения.