Почему агрегатные функции MAX и MIN в SQL-запросах 1С работают быстрее других и как на это влияют индексы?

← На главную

В среде разработчиков 1С существует устойчивый стереотип: агрегатные функции, такие как МАКСИМУМ или МИНИМУМ, — это тяжелые операции, которые могут существенно замедлить выполнение запроса. Многие из нас при планировании архитектуры базы данных стараются избегать группировок, опасаясь избыточного сканирования таблиц. Однако на практике SQL Server (и другие современные СУБД) ведет себя гораздо умнее, чем кажется на первый взгляд.

Рассмотрим ситуацию, когда нам необходимо получить одно любое (например, максимальное) значение из подчиненной таблицы или регистра для целого списка родительских объектов. Разберемся, почему правильно настроенный индекс превращает потенциально тяжелую группировку в мгновенную операцию, и выясним, как СУБД удается выполнять поиск всего за одно чтение.

Постановка задачи и выбор подхода

Представим типичную задачу: для массива документов "Справка Б" нам нужно получить хотя бы одну марку PDF417 из регистра сведений АСФСостоянияМарок. У нас есть два основных пути реализации этой задачи на стороне 1С.

Вариант 1. Группировка в одном запросе

Мы передаем массив ссылок и используем агрегатную функцию для выбора значения:


ВЫБРАТЬ
    АСФСостоянияМарок.СправкаБ КАК СправкаБ,
    МАКСИМУМ(АСФСостоянияМарок.МаркаPDF417) КАК МаркаPDF417
ИЗ
    РегистрСведений.АСФСостоянияМарок КАК АСФСостоянияМарок
ГДЕ
    АСФСостоянияМарок.СправкаБ В (&МассивСправокБ)
СГРУППИРОВАТЬ ПО
    АСФСостоянияМарок.СправкаБ

Вариант 2. Запрос в цикле с выборкой первой записи

Мы обходим массив в коде 1С и для каждого элемента выполняем отдельный запрос:


Для Каждого Справка Из МассивСправокБ Цикл
    Запрос = Новый Запрос;
    Запрос.Текст = 
    "ВЫБРАТЬ ПЕРВЫЕ 1
    |   АСФСостоянияМарок.СправкаБ КАК СправкаБ,
    |   АСФСостоянияМарок.МаркаPDF417 КАК МаркаPDF417
    |ИЗ
    |   РегистрСведений.АСФСостоянияМарок КАК АСФСостоянияМарок
    |ГДЕ
    |   АСФСостоянияМарок.СправкаБ = &СправкаБ";
    Запрос.УстановитьПараметр("СправкаБ", Справка);
    // ... выполнение запроса
КонецЦикла;

Многие программисты интуитивно выбирают второй вариант, полагая, что ВЫБРАТЬ ПЕРВЫЕ 1 отработает быстрее, чем агрегат МАКСИМУМ. Однако анализ через SQL Profiler показывает удивительный результат: первый вариант с группировкой выполняется практически мгновенно и показывает всего 1-2 логических чтения на каждую справку из списка.

Механика работы B-дерева и составных индексов

Чтобы понять, почему это происходит, проанализируем структуру индекса в SQL Server. В 1С регистры сведений почти всегда имеют составные индексы, сформированные из измерений. Если наше измерение СправкаБ является первым в индексе, а МаркаPDF417 — вторым (или включенным полем), то индекс представляет собой сбалансированное B-дерево (B-Tree), отсортированное сначала по Справке, а затем по Марке.

Разберем процесс поиска значения МАКСИМУМ по шагам:

Навигация к диапазону: СУБД получает команду найти данные для конкретной СправкиБ. Оптимизатор использует Index Seek, чтобы мгновенно спуститься от корня B-дерева к листовому уровню, где начинаются записи для этой справки.
Поиск граничного значения: Поскольку индекс внутри каждой СправкиБ уже отсортирован по возрастанию МаркаPDF417, максимальное значение всегда находится в самом конце диапазона этой справки, а минимальное — в самом начале.
Оптимизация "Top N Sort": SQL Server не сканирует все записи справки. Он просто "прыгает" в конец (для MAX) или в начало (для MIN) найденного диапазона. В плане запроса это отражается как Index Seek с направлением сканирования BACKWARD или FORWARD.

Именно поэтому количество чтений минимально. СУБД достаточно прочитать страницы индекса, составляющие путь от корня к листу, и саму листовую страницу с нужной записью. Если индекс прогрет и находится в кэше, профайлер может зафиксировать всего 1 логическое чтение.

Сравнение агрегатных функций: MAX, MIN и COUNT

Проанализируем, почему другие функции ведут себя иначе в тех же условиях. Рассмотрим поведение СУБД при наличии индекса (СправкаБ, МаркаPDF417):

МАКСИМУМ / МИНИМУМ: Требуют только одну крайнюю точку. Поиск эффективен (1 чтение).
КОЛИЧЕСТВО (COUNT): Чтобы ответить на вопрос "сколько записей у этой справки", СУБД вынуждена физически пересчитать их все. Если у одной справки 20 000 марок, SQL Server выполнит сканирование всего диапазона этих 20 000 записей в индексе. Это приведет к значительному росту чтений (в примерах тестов — до 19 800 и более).
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ): Самая тяжелая операция, так как требует не только сканирования, но и анализа уникальности значений, что часто влечет за собой построение временных таблиц или использование Hash Match в плане запроса.

Почему MIN() может быть предпочтительнее MAX()?

Хотя теоретически стоимость поиска в B-дереве в обе стороны одинакова, существуют нюансы физической реализации. Поскольку стандартный индекс в 1С всегда строится по возрастанию (ASC), поиск МИНИМУМ идет в естественном порядке следования записей (Forward Scan). Поиск МАКСИМУМ заставляет СУБД читать индекс в обратном порядке (Backward Scan).

В современных версиях SQL Server разница между ними практически стерта, однако для очень больших объемов данных прямой порядок чтения страниц может быть чуть эффективнее за счет механизмов упреждающего чтения (Read-Ahead), которые лучше работают при движении вперед по цепочке страниц листового уровня. Поэтому, если вам не важно, какое именно значение получить (первое или последнее), использование МИНИМУМ считается "хорошим тоном" оптимизации.

Практические выводы для разработчика

Проанализировав ситуацию, мы можем сформулировать следующие рекомендации по работе с запросами в 1С:

Группировка эффективнее цикла: Всегда отдавайте предпочтение одному запросу с оператором ГДЕ ... В (&Массив) и группировкой. Это избавляет систему от накладных расходов на сетевое взаимодействие между сервером приложений 1С и сервером СУБД (round-trips).
Используйте покрывающие индексы: Для того чтобы МАКСИМУМ работал за 1 чтение, поле, по которому берется агрегат, должно входить в индекс после полей, по которым идет отбор или группировка. В 1С это достигается правильным порядком измерений в регистрах.
Не бойтесь MAX/MIN на индексированных полях: Эти функции не являются "злом", если они опираются на структуру B-дерева. Опасаться стоит функций СУММА и КОЛИЧЕСТВО на больших объемах данных, так как они всегда вызывают сканирование диапазона.
Проверяйте планы запросов: Если вы видите в плане Index Scan вместо Index Seek при использовании агрегатов, значит, ваш индекс не подходит для данной задачи и СУБД вынуждена просматривать всю таблицу.

Таким образом, понимание внутреннего устройства индексов позволяет писать лаконичный и производительный код, не прибегая к сложным процедурным ухищрениям там, где SQL Server может справиться самостоятельно за доли миллисекунды.