Нужен ли индекс на поле большой таблицы при использовании ЛЕВОГО СОЕДИНЕНИЯ?

При работе с большими объемами данных в 1С (порядка 10–15 миллионов записей и выше) вопросы оптимизации запросов встают особенно остро. Типичная ситуация: у нас есть огромная таблица (например, РегистрСведений на 13 млн записей) и относительно небольшая таблица (100 тыс. записей). Мы выполняем ЛЕВОЕ СОЕДИНЕНИЕ, где первая таблица является ведущей. Поле соединения во второй таблице проиндексировано, а в первой — нет. Нужно ли добавлять индекс в первую таблицу? Давайте подробно разберем этот вопрос с точки зрения работы SQL-сервера и платформы 1С.

Особенности физической структуры Регистров Сведений

Для начала проанализируем, как 1С хранит данные на уровне СУБД. В 1С любой РегистрСведений всегда имеет физический кластерный индекс. Он формируется из всех измерений в порядке их следования в конфигураторе (плюс поле Период, если регистр периодический). Рассмотрим ситуацию, когда поле соединения — это измерение, находящееся в конце списка.

Важный нюанс: если поле является частью кластерного индекса, но стоит не на первом месте, SQL-сервер не сможет выполнить эффективный поиск (Index Seek), если в условии запроса не задействованы все вышестоящие измерения. В этом случае сервер будет вынужден сканировать либо весь индекс целиком, либо всю таблицу. Установка флажка «Индексировать» в конфигураторе создаст дополнительный некластерный индекс. Это позволит оптимизатору выполнять Index Scan (чтение только «узкого» индекса), что значительно быстрее, чем Clustered Index Scan (чтение всех данных таблицы с диска).

Алгоритмы соединения: Nested Loops, Hash Join и Merge Join

Чтобы понять, поможет ли индекс, выясним, как SQL-сервер может соединять таблицы. Существует три основных алгоритма, и выбор каждого из них зависит от наличия индексов и объема данных:

1. Nested Loops (Вложенные циклы): База берет строку из первой таблицы и ищет соответствие во второй. Если во второй таблице есть индекс, поиск происходит быстро (логарифмическая сложность). Однако при 13 млн итераций даже быстрый поиск может затянуться.
2. Hash Join (Соединение хешированием): Оптимизатор строит хеш-таблицу по меньшей таблице (100 тыс. записей) в оперативной памяти, а затем один раз сканирует большую таблицу (13 млн), проверяя хеш для каждой строки. Это наиболее вероятный сценарий для нашего случая.
3. Merge Join (Соединение слиянием): Самый эффективный метод для больших объемов. Он требует, чтобы оба набора данных были заранее отсортированы по полям соединения.

Проанализируем ситуацию: если на большой таблице нет индекса, SQL-сервер никогда не выберет Merge Join, так как сортировка 13 млн записей «на лету» в TempDB — операция крайне дорогая. Таким образом, отсутствие индекса ограничивает выбор оптимизатора менее эффективными алгоритмами.

Почему индекс важен при ЛЕВОМ СОЕДИНЕНИИ?

Существует миф, что при LEFT JOIN индекс на ведущей (левой) таблице не нужен, так как «мы и так берем из нее всё». Рассмотрим, почему это не совсем так:

1. Сокращение объема чтений (I/O). Без индекса SQL-сервер делает Full Table Scan. Если запись в регистре «широкая» (много ресурсов и реквизитов), серверу придется прочитать гигабайты данных с диска. Наличие индекса по полю соединения позволяет серверу прочитать только этот индекс — он намного меньше всей таблицы. Это на порядки снижает нагрузку на дисковую подсистему.

2. Проблема Hash Spill в TempDB. При использовании Hash Join (который часто выбирается для таких объемов) SQL-сервер выделяет память под хеш-таблицу. Если статистика устарела или данных оказалось больше, чем ожидалось, происходит «сброс» (spill) во временную базу TempDB на диске. Это катастрофически замедляет запрос. Индекс помогает оптимизатору точнее оценить стоимость плана и избежать подобных ошибок.

3. Внутренняя трансформация запроса. Иногда оптимизатор может решить, что выгоднее начать соединение с «правой» (меньшей) таблицы, если это сократит итоговую выборку (например, если есть дополнительные фильтры). В этом случае наличие индекса на «левой» таблице станет критическим фактором производительности.

Практические рекомендации по применению

Рассмотрим по шагам, когда всё же стоит нажать заветную галочку «Индексировать»:

• Если поле соединения является Измерением, но не первым в списке, и запрос к нему выполняется часто — индекс нужен.
• Если поле является Реквизитом (а по реквизитам в 1С кластерный индекс не строится никогда) — индекс нужен.
• Если вы используете это поле не только в соединении, но и в условиях ГДЕ или УПОРЯДОЧИТЬ ПО — индекс обязателен.

Однако помните о «цене» индекса. Каждый новый индекс замедляет операции записи (проведение документов, загрузку данных), так как системе нужно обновлять не только саму таблицу, но и все ее индексы. Если этот регистр обновляется тысячи раз в секунду, добавление лишнего индекса может создать очередь на запись.

Пример анализа плана запроса

Представим типовой запрос на языке 1С:

ВЫБРАТЬ
    РегистрПродажи.Номенклатура,
    РегистрПродажи.Количество,
    ЦеныНоменклатуры.Цена
ИЗ
    РегистрСведений.Продажи КАК РегистрПродажи
ЛЕВОЕ СОЕДИНЕНИЕ 
    РегистрСведений.Цены КАК ЦеныНоменклатуры
ПО 
    РегистрПродажи.Номенклатура = ЦеныНоменклатуры.Номенклатура

Проанализируем ситуацию: если Номенклатура в таблице Продажи не проиндексирована, СУБД будет читать все 13 млн строк. Если же индекс есть, СУБД может применить Index Scan или даже Merge Join, если вторая таблица также отсортирована. В высоконагруженных системах разница во времени выполнения может составлять от нескольких минут до нескольких секунд.

Резюме

Подведем итог нашему исследованию. Нужно ли индексировать поле в большой таблице при левом соединении? В большинстве случаев — да. Это дает оптимизатору СУБД пространство для маневра, позволяет использовать более легкие операции сканирования индекса вместо сканирования всей таблицы и защищает от перегрузки TempDB. Исключением являются только те случаи, когда критически важна скорость записи в этот регистр, а сам запрос выполняется крайне редко (например, раз в месяц при закрытии периода).