Разработчики в 1С часто сталкиваются с соединениями таблиц в запросах. Мы привыкли оперировать понятиями «Левое соединение» и «Внутреннее соединение» на уровне логики: при левом соединении выбираются все записи из левой таблицы и подходящие из правой, а при внутреннем — только те записи, которые есть в обеих таблицах. Но задумывались ли вы, как именно происходит это «таинство соития» на физическом уровне СУБД? Почему в одном случае запрос отрабатывает мгновенно, а в другом — «вешает» систему? Почему внутреннее соединение иногда выполняется дольше левого?
В этой статье мы подробно разберем физику процесса соединений, изучим поведение оптимизатора СУБД (будь то MS SQL Server или PostgreSQL), проанализируем неявные соединения в 1С при обращении через точку и научимся писать действительно эффективные запросы.
Прежде чем перейти к физическим алгоритмам СУБД, рассмотрим важное математическое различие между видами соединений с точки зрения реляционной алгебры.
Внутреннее соединение (INNER JOIN) коммутативно. Это означает, что для оптимизатора СУБД операция ТаблицаА INNER JOIN ТаблицаБ математически абсолютно эквивалентна операции ТаблицаБ INNER JOIN ТаблицаА. Оптимизатор СУБД имеет полную свободу выбора: он оценивает объем данных, статистику распределения значений и наличие индексов, после чего сам решает, какую таблицу прочитать первой (сделать ведущей), а какую — ведомой.
Левое соединение (LEFT JOIN) не коммутативно. СУБД обязана сохранить в итоговой выборке абсолютно все строки из левой таблицы, независимо от того, найдутся ли для них соответствия в правой. Это жесткое ограничение связывает оптимизатору руки: он практически всегда вынужден логически обрабатывать левую таблицу как ведущую (главную) в плане выполнения.
Почему левое соединение иногда работает быстрее внутреннего? На практике часто возникает ситуация, когда статистика базы данных устарела. В этом случае при внутреннем соединении оптимизатор может ошибочно предположить, что в огромной таблице документов содержится всего пара строк, и сделает ее ведомой в цикле. Это приводит к катастрофическому падению производительности. При левом соединении у СУБД меньше свободы для «самодеятельности», поэтому она строит более предсказуемый и зачастую более быстрый план выполнения запроса.
Когда платформа 1С транслирует наш запрос в язык SQL и передает его СУБД, планировщик сервера баз данных выбирает один из трех основных физических алгоритмов (операторов) соединения.
Это самый простой и интуитивно понятный алгоритм соединения. СУБД берет первую строку из внешней (ведущей) таблицы и начинает искать совпадения во внутренней (ведомой) таблице. Затем переходит ко второй строке внешней таблицы и так далее.
Когда эффективен: Этот метод идеален, когда внешняя таблица содержит небольшое количество строк, а по полю соединения внутренней таблицы построен индекс. Поиск по индексу происходит за микросекунды, поэтому операция выполняется мгновенно.
В чем опасность: Если во внутренней таблице нет подходящего индекса по полю соединения, СУБД будет вынуждена выполнять полное сканирование всей внутренней таблицы для *каждой* строки внешней таблицы. Если во внешней таблице у нас 1 000 документов, а во внутренней — 10 000 контрагентов, СУБД выполнит 10 000 000 операций чтения. Это классическая проблема производительности.
Этот алгоритм работает по принципу сопоставления двух предварительно отсортированных наборов данных. СУБД одновременно читает обе таблицы, упорядоченные по ключу соединения, и сравнивает значения. Как только указатель одной из таблиц отстает, он сдвигается вперед.
Когда эффективен: Это самый быстрый и ресурсоэффективный алгоритм для соединения больших объемов данных. Сложность алгоритма минимальна.
Условие применимости: Данные обязательно должны быть отсортированы по ключам соединения. Если на полях соединения построены кластерные индексы, СУБД без раздумий выберет этот метод. Если же индексов нет, СУБД придется сначала отсортировать таблицы в оперативной памяти (или во временной базе данных, например, TempDB в MS SQL), что может нивелировать всю выгоду.
Если таблицы огромны, не отсортированы и не имеют подходящих индексов, СУБД применяет алгоритм хеширования. Она сканирует меньшую таблицу (таблицу сборки) и строит по ней хеш-таблицу в оперативной памяти. Затем СУБД сканирует большую таблицу (таблицу зондирования), вычисляет хеш-код для поля соединения каждой строки и ищет совпадения в подготовленной хеш-таблице.
Когда эффективен: Используется как «спасательный круг» для тяжелых запросов без индексов по большим таблицам.
В чем опасность: Данный оператор крайне требователен к оперативной памяти. Если памяти сервера СУБД не хватает для размещения всей хеш-таблицы, промежуточные данные начинают сбрасываться на медленный жесткий диск. Это моментально снижает производительность системы в сотни раз.
Рассмотрим практическую ситуацию, которую часто обсуждают на форумах разработчиков. У нас есть простой запрос с внутренним соединением:
ВЫБРАТЬ
Поступление.Ссылка КАК Документ,
Склады.Наименование КАК Склад
ИЗ
Документ.ПоступлениеТоваровУслуг КАК Поступление
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Склады КАК Склады
ПО Поступление.Склад = Склады.Ссылка
ГДЕ
Поступление.Дата МЕЖДУ &НачДата И &КонДата
Логика человека подсказывает: сначала нужно отобрать документы за указанный период (их мало), а затем сопоставить их со складами. Однако СУБД ничего не знает о бизнес-логике 1С. При создании таблиц платформа 1С не объявляет внешние ключи (Foreign Keys) для ссылочных полей СУБД, все поля создаются просто с поддержкой значения NULL (или без нее, но без жестких связей целостности между таблицами).
Поэтому оптимизатор СУБД рассуждает чисто математически:
Справочник.Склады) всего 10 записей.Склад, а уже потом отсеку лишние даты».Если реквизит Склад в документе проиндексирован, СУБД с огромной долей вероятности выберет индекс этого реквизита, проигнорировав индекс по дате документа. В результате мы получим сканирование огромного объема данных вместо быстрого точечного поиска.
Если же мы перепишем этот запрос с использованием ЛЕВОГО СОЕДИНЕНИЯ:
ВЫБРАТЬ
Поступление.Ссылка КАК Документ,
Склады.Наименование КАК Склад
ИЗ
Документ.ПоступлениеТоваровУслуг КАК Поступление
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Склады КАК Склады
ПО Поступление.Склад = Склады.Ссылка
ГДЕ
Поступление.Дата МЕЖДУ &НачДата И &КонДата
В этом случае оптимизатор СУБД жестко ограничен правилом левого соединения: он обязан сначала получить выборку из левой таблицы (документов), наложить на нее фильтр по датам, и только для оставшихся строк найти наименования складов в справочнике. Запрос выполнится оптимально.
В платформе 1С реализован механизм «разыменования» ссылок. Мы можем написать в запросе простое обращение через точку:
ВЫБРАТЬ
Расходная.Ссылка КАК Документ,
Расходная.Контрагент.Наименование КАК КонтрагентНаименование
ИЗ
Документ.РеализацияТоваровУслуг КАК Расходная
СУБД на своем физическом уровне ничего не знает о ссылках 1С и получении реквизитов «через точку». Синтаксический анализатор платформы 1С при трансляции этого запроса автоматически преобразует его в явный SQL-код, добавляя к основной таблице документов ЛЕВОЕ СОЕДИНЕНИЕ с таблицей справочника контрагентов.
Эта магия работает отлично, пока реквизит имеет простой тип. Но что происходит, если реквизит имеет составной тип данных? Например, реквизит Субконто1 в регистре бухгалтерии может принимать значения справочников «Контрагенты», «Договоры», «ФизическиеЛица» и еще десятка других.
Для СУБД составной тип 1С представляется в виде нескольких физических колонок в таблице (обычно это колонка под тип данных _Fld123_TYPE и колонка под уникальный идентификатор ссылки _Fld123_RRef).
Когда вы пишете запрос вида:
ВЫБРАТЬ
Движения.Субконто1.Наименование
ИЗ
РегистрБухгалтерии.Проводки КАК Движения
Платформа 1С не знает заранее, какой именно тип данных записан в каждой конкретной строке. Поэтому она генерирует SQL-запрос, содержащий левые соединения абсолютно со всеми таблицами СУБД, которые входят в этот составной тип! Если в составной тип входит 30 справочников, СУБД получит гигантский запрос с 30 левыми соединениями. Производительность такого запроса стремится к нулю, оптимизатор СУБД путается в планах, а пользователи жалуются на зависание отчетов.
Для оптимизации таких запросов мы должны использовать функцию ВЫРАЗИТЬ(). Она жестко указывает платформе, к какому типу нужно привести реквизит перед обращением к его свойствам:
ВЫБРАТЬ
ВЫРАЗИТЬ(Движения.Субконто1 КАК Справочник.Контрагенты).Наименование КАК КонтрагентНаименование
ИЗ
РегистрБухгалтерии.Проводки КАК Движения
После этого платформа 1С сгенерирует ровно одно левое соединение только с таблицей контрагентов. Это значительно снизит нагрузку на СУБД и ускорит выполнение запроса.
Поскольку в настоящее время огромное количество баз данных 1С работает под управлением PostgreSQL, необходимо учитывать ключевую особенность оптимизатора этой СУБД.
В настройках PostgreSQL есть важнейший параметр: join_collapse_limit (по умолчанию он равен 8). Этот параметр указывает планировщику, до какого количества таблиц в одном запросе он может пытаться перестраивать и оптимизировать порядок соединений.
Для сложных баз 1С на PostgreSQL рекомендуется увеличивать данный параметр до 12–16, но важно помнить, что чрезмерное его увеличение приведет к тому, что СУБД будет тратить слишком много времени на само планирование запроса перед его запуском.
Подведем итог нашему исследованию и сформулируем правила для написания быстрых запросов:
СправочникСсылка или ДокументСсылка) индексируются автоматически, но если вы соединяете таблицы по какому-то числу или строке, убедитесь, что у реквизита стоит свойство «Индексировать».ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Оно дает оптимизатору СУБД больше свободы для поиска лучшего плана выполнения.ЛЕВОЕ СОЕДИНЕНИЕ.ВЫРАЗИТЬ(), чтобы избежать «гирлянды» лишних левых соединений в СУБД.