При проектировании крупных учетных систем (например, для ЖКХ, ERP-решений или WMS) разработчики регулярно сталкиваются с дилеммой: как организовать хранение множества дополнительных характеристик объектов, если эти характеристики нужны далеко не для каждого элемента справочника? В этой статье мы подробно разберем ситуацию, когда справочник (например, «Помещения» или «Лицевые счета») содержит более 40 реквизитов, заполняемых избирательно, а количество записей измеряется десятками и сотнями тысяч элементов.
Мы проанализируем различные архитектурные подходы с точки зрения производительности СУБД (MS SQL Server, PostgreSQL), удобства написания запросов и особенностей платформы «1С:Предприятие 8». Также мы выясним причину распространенной проблемы, когда соединение небольших таблиц в запросах начинает занимать критически много времени.
---
Это самый простой и очевидный подход. Все 40 реквизитов создаются как обычные поля справочника. На первый взгляд, это кажется неоптимальным («база будет раздуваться из-за пустых полей»), однако давайте проанализируем ситуацию на физическом уровне хранения данных в СУБД.
Современные СУБД хранят данные страницами (обычно по 8 КБ). Пустые значения простых типов данных (числа, даты, булево) или пустые ссылки практически не занимают физического места на диске благодаря использованию битовой карты NULL-значений (NULL bitmap). Таким образом, объем базы данных при наличии пустых реквизитов увеличивается незначительно.
Однако здесь кроется другая проблема, специфичная для объектной модели «1С:Предприятие»:
ПолучитьОбъект() платформа 1С выполняет SQL-запрос, который считывает из СУБД абсолютно все поля шапки и табличных частей. Если у вас заполнено 3 реквизита из 40, остальные 37 пустых значений все равно будут переданы по сети на сервер приложений 1С и загружены в оперативную память процесса rphost.Рекомендация: Если реквизиты активно используются в расчетах и отборах, а объем справочника не превышает 100 000 элементов, их размещение в шапке справочника будет наиболее быстродействующим вариантом для чтения через запросы, так как СУБД выполнит выборку из одной физической таблицы без соединений.
---
Иногда разработчики предлагают перенести редко заполняемые реквизиты в табличную часть (ТЧ) справочника, создав там структуру «одна строка со всеми свойствами» или «ключ-значение».
Давайте разберем, почему этот паттерн является крайне нежелательным:
Ссылка. Каждое обращение к свойствам ТЧ в запросе потребует операции ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN).---
Этот подход реализован в Библиотеке стандартных подсистем (БСП) в виде механизма «Дополнительные реквизиты и сведения». На физическом уровне это классическая модель EAV (Entity-Attribute-Value):
ДополнительныеРеквизитыИСведения (список доступных свойств).[Объект, Свойство, Значение].Давайте проанализируем этот подход. С точки зрения гибкости — это идеальное решение. Администраторы системы могут добавлять новые свойства прямо в пользовательском режиме без изменения метаданных в Конфигураторе.
Однако с точки зрения производительности массовых расчетов (например, ежемесячное начисление коммунальных услуг по 30 000 лицевых счетов) модель EAV превращается в проблему. Представим, что для расчета начисления нам нужно получить 6 различных свойств помещения (общая площадь, жилая площадь, количество зарегистрированных, этаж, тип отопления, наличие лифта). Нам придется написать запрос с шестью левыми соединениями к одной и той же таблице регистра сведений:
ВЫБРАТЬ
Помещения.Ссылка КАК Помещение,
ЕСТЬNULL(Свойство1.Значение, 0) КАК ОбщаяПлощадь,
ЕСТЬNULL(Свойство2.Значение, 0) КАК ЖилаяПлощадь,
ЕСТЬNULL(Свойство3.Значение, 0) КАК КоличествоПрописанных
ИЗ
Справочник.Помещения КАК Помещения
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК Свойство1
ПО Свойство1.Объект = Помещения.Ссылка
И Свойство1.Свойство = &СвойствоОбщаяПлощадь
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК Свойство2
ПО Свойство2.Объект = Помещения.Ссылка
И Свойство2.Свойство = &СвойствоЖилаяПлощадь
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК Свойство3
ПО Свойство3.Объект = Помещения.Ссылка
И Свойство3.Свойство = &СвойствоКоличествоПрописанных
Для СУБД такой запрос крайне тяжел. Многократные соединения одной и той же таблицы приводят к неверной оценке кардинальности оптимизатором запросов, из-за чего SQL-сервер может выбрать неоптимальный план выполнения (например, сканирование индекса вместо поиска по ключу).
---
Интересный и современный подход — сериализация всех редких и вспомогательных свойств в одну текстовую строку неограниченной длины в формате JSON и хранение ее в одном реквизите справочника.
Начиная с версии платформы 8.3.23, работа с JSON в 1С стала очень быстрой благодаря встроенным методам сериализации. Рассмотрим пример записи свойств объекта в JSON:
// Пример упаковки свойств объекта в строку JSON
ПараметрыОбъекта = Новый Структура;
ПараметрыОбъекта.Вставить("КадастровыйНомер", "77:01:0001001:1234");
ПараметрыОбъекта.Вставить("ГисЖкхИдентификатор", "DOM-REG-98765");
ПараметрыОбъекта.Вставить("ДатаВводаВЭксплуатацию", ТекущаяДата());
ЗаписьJSON = Новый ЗаписьJSON;
ЗаписьJSON.УстановитьСтроку();
ЗаписатьЗначениеJSON(ЗаписьJSON, ПараметрыОбъекта);
СтрокаJSON = ЗаписьJSON.Закрыть();
// СтрокаJSON записывается в реквизит справочника "ДополнительныеПараметры"
При необходимости прочитать эти данные на форме элемента справочника мы выполняем обратную операцию:
// Пример чтения свойств из строки JSON
ЧтениеJSON = Новый ЧтениеJSON;
ЧтениеJSON.УстановитьСтроку(Объект.ДополнительныеПараметры);
ПараметрыОбъекта = ПрочитатьЗначениеJSON(ЧтениеJSON);
ЧтениеJSON.Закрыть();
КадастровыйНомер = ПараметрыОбъекта.КадастровыйНомер;
Преимущества: Таблица справочника остается максимально узкой. Запись и чтение объекта выполняются мгновенно. Структура свойств гибкая и может меняться без доработки метаданных.
Критический недостаток: Язык запросов 1С не умеет работать с JSON-строками. Вы не сможете написать запрос с отбором по кадастровому номеру или рассчитать сумму начислений по площади, хранящейся внутри JSON. Этот метод подходит исключительно для хранения справочной информации, которая выводится на форму и никогда не участвует в выборках, расчетах и отчетах.
---
Давайте найдем «золотую середину». Вертикальное секционирование — это разделение реквизитов на группы по частоте их использования и вынесение редко используемых параметров во внешние таблицы СУБД (в контексте 1С — в непериодические Регистры сведений).
Проанализируем структуру данных типичного ЖКХ-решения. Реквизиты справочника «Помещения» можно разделить на три логические группы:
ДополнительныеХарактеристикиПомещений. Измерением регистра будет ссылка на СправочникСсылка.Помещения, а ресурсами — соответствующие реквизиты.ПараметрыИнтеграцииГисЖкх.Этот подход гарантирует:
---
Разработчик в обсуждении упомянул проблему: на мощном сервере (процессор i9, 64 ГБ оперативной памяти) левое соединение 9 таблиц объемом всего по 2 000 – 3 000 элементов выполняется около 5 секунд. На таких объемах это катастрофически медленно. Давайте выясним причины такого поведения СУБД и способы локализации проблемы:
Документ.Начисление или Документ.Оплата), платформа 1С на уровне СУБД транслирует это соединение в гигантскую конструкцию со множеством CASE WHEN и LEFT JOIN для всех физических таблиц документов, входящих в составной тип. СУБД не может применить индекс и переходит на полное сканирование таблиц (Table/Index Scan). Правило: соединять таблицы в запросах можно исключительно по полям простых ссылочных типов.Индексировать (или они являются ведущими измерениями).Выборка.Помещение.Владелец.Адрес.Город приводят к созданию платформой неявных левых соединений. Если таких обращений в запросе много, итоговый SQL-текст будет перегружен скрытыми соединениями, что парализует оптимизатор СУБД. Выбирайте данные только через явные соединения в запросе.UPDATE STATISTICS).---
Для создания быстрой и масштабируемой системы хранения редко заполняемых реквизитов выполним следующие шаги: