В практике крупномасштабных внедрений 1С и на экзамене «1С:Эксперт по технологическим вопросам» часто встречается задача: необходимо получить точное количество записей в огромном регистре сведений (100 млн строк) за время, не превышающее 1 секунду. При этом ситуация осложняется тем, что в базе одновременно работают 1000 пользователей, а стандартный запрос ВЫБРАТЬ КОЛИЧЕСТВО(*) выполняется около 60 секунд. Разберем детально, какими способами можно добиться целевого показателя, используя возможности платформы 1С и СУБД.
Для начала проанализируем, что происходит на уровне СУБД при выполнении команды COUNT(*). В большинстве случаев SQL Server или PostgreSQL выполняет операцию Index Full Scan. Это означает, что серверу необходимо прочитать все страницы самого «узкого» индекса таблицы. Если записей 100 миллионов, объем данных, которые нужно считать с диска в память, огромен. При наличии 1000 активных пользователей дисковая подсистема и оперативная память нагружены, что приводит к очередям и деградации времени ответа до 60 секунд.
Рассмотрим основные направления оптимизации, которые помогут сократить это время в 60 раз.
Если ваша система работает на MS SQL Server версии Enterprise (или Standard начиная с 2016 SP1), одним из самых эффективных способов является создание некластерного колоночного индекса (Nonclustered Columnstore Index). В отличие от обычных индексов, хранящих данные построчно, колоночные индексы сжимают данные в десятки раз и оптимизированы для агрегатных функций.
Проанализируем преимущества: при выполнении COUNT(*) SQL Server обратится к колоночному индексу, который может весить в 10–50 раз меньше основной таблицы. Считывание такого объема данных происходит практически мгновенно. Важно помнить, что создание индекса на стороне СУБД напрямую формально может нарушать лицензионное соглашение, если это делается не через средства платформы, однако в рамках экспертных обсуждений этот метод считается «технологическим решением».
Разберем подход, который полностью реализуется средствами платформы 1С. Если один запрос не успевает отработать, мы можем распараллелить задачу. Идея заключается в том, чтобы разбить диапазон данных (например, по периоду или по значению первого измерения) на несколько сегментов и запустить их подсчет одновременно.
Посмотрим на пример логики реализации:
// Пример разделения задачи на 10 потоков
МассивЗаданий = Новый Массив;
Для НомерПотока = 1 По 10 Цикл
Параметры = Новый Массив;
Параметры.Добавить(НомерПотока); // Границы сегмента данных
МассивЗаданий.Добавить(ФоновыеЗадания.Выполнить("ОбщийМодуль.ПосчитатьСегмент", Параметры));
КонецЦикла;
ФоновыеЗадания.ОжидатьЗавершения(МассивЗаданий);
ОбщееКоличество = 0;
Для Каждого Задание Из МассивЗаданий Цикл
ОбщееКоличество = ОбщееКоличество + Задание.ПолучитьРезультат();
КонецЦикла;
Этот метод позволяет задействовать все ядра процессора сервера приложений и СУБД, эффективно обходя ограничение последовательного сканирования одной таблицы.
Существует мнение, что метод Выбрать() менеджера регистра сведений в некоторых условиях может работать быстрее прямого запроса за счет динамического считывания порциями. Рассмотрим этот вариант:
Выборка = РегистрыСведений.НашРегистр.Выбрать();
Счетчик = 0;
Пока Выборка.Следующий() Цикл
Счетчик = Счетчик + 1;
КонецЦикла;
Важное замечание: на практике для 100 млн записей обычный обход в цикле Пока Выборка.Следующий() будет работать очень долго из-за накладных расходов на передачу данных между сервером СУБД и сервером 1С. Однако сам механизм «порционного» чтения (курсоров) может быть полезен, если СУБД эффективно кэширует данные. Но для задачи в 1 секунду этот метод редко оказывается жизнеспособным без дополнительных оптимизаций.
Если для регистра сведений включен план обмена, мы можем попробовать использовать таблицу регистрации изменений. Выясним причину, почему это может помочь: таблица изменений обычно содержит гораздо меньше записей, чем основной регистр. Если задача бизнес-логики позволяет опираться на эти данные или если мы можем временно «зарегистрировать» все записи, подсчет по маленькой таблице РегистрацияИзменений пройдет мгновенно.
Начиная с версии платформы 8.3.14, 1С предоставляет мощный инструмент — Дата-акселератор. Это In-memory СУБД, специально предназначенная для быстрого выполнения аналитических запросов над большими данными.
Рассмотрим алгоритм действий:
Поскольку данные в акселераторе хранятся в памяти и организованы колоночно, COUNT(*) по 100 млн записей легко уложится в 1 секунду.
Хотя преподаватели на экзаменах часто запрещают прямой доступ к SQL, эксперт должен знать, как получить данные о количестве строк из системной статистики. В MS SQL Server информация о количестве строк хранится в системных представлениях, таких как sys.partitions или sys.dm_db_partition_stats.
Проанализируем ситуацию: СУБД всегда «знает» примерное (а часто и точное) количество строк для целей планировщика запросов. Чтение из этих таблиц занимает миллисекунды, так как это просто получение готового числа из метаданных, а не скан всей таблицы.
Часто медленная работа COUNT(*) при 1000 пользователях вызвана тем, что СУБД не может выделить достаточное количество потоков для сканирования из-за ограничений Max Degree of Parallelism (MAXDOP) или высокого порога стоимости параллелизма (Cost Threshold for Parallelism).
Разберем по шагам настройку: если установить MAXDOP в значение 0 (использовать все ядра) или в оптимальное количество ядер для одного узла NUMA, SQL Server сможет распределить чтение индекса между всеми ядрами. При пустом сервере это ускорит запрос, но при 1000 пользователях может вызвать дефицит ресурсов. Поэтому здесь важен баланс и настройка Resource Governor для выделения ресурсов под конкретные тяжелые операции чтения.
В некоторых случаях обращение через встроенный REST-интерфейс (OData) с параметром $count=true может использовать внутренние оптимизации платформы, недоступные через обычный язык запросов. Это стоит проверить как один из альтернативных «штатных» способов.
Подведем итог: универсального «волшебного» запроса в 1С, который сам по себе ускорит COUNT(*) в 60 раз без изменения инфраструктуры или настроек, не существует. Решение задачи уровня «Эксперт» всегда комплексно:
Проанализировав все способы, мы видим, что наиболее реалистичным для выполнения требования «1 секунда» на 100 млн записей является либо использование Дата-акселератора 1С, либо создание колоночного индекса на уровне СУБД. Все остальные методы (реиндексация, дефрагментация, RCSI) лишь немного улучшат ситуацию, но не дадут кратного прироста производительности.