При проектировании и поддержке крупных высоконагруженных систем (HighLoad) на платформе 1С разработчики часто сталкиваются с лавинообразным ростом объемов данных. Когда счет записей в таблицах остатков или оборотов переходит за десятки миллионов (например, 20 миллионов записей по Москве, чуть меньше по Санкт-Петербургу и миллионы по другим регионам), неизбежно возникает вопрос: стоит ли искусственно разделять данные по разным таблицам или даже базам? Например, выносить остатки крупных регионов в отдельные физические таблицы.
В этой статье мы подробно проанализируем, как СУБД реагирует на сверхбольшие таблицы, разберем физику работы индексов, выясним причины эскалации блокировок и определим правила проектирования регистров сведений для оптимальной производительности без необходимости усложнять архитектуру базы данных.
Среди разработчиков существует мнение, что современным промышленным СУБД (MS SQL Server, PostgreSQL) практически безразлично количество записей в таблице, если запросы написаны правильно. Это утверждение верно, но лишь наполовину — преимущественно для операций чтения (выборок).
Давайте проанализируем, что происходит при росте таблицы с 1 миллиона до 100 миллионов строк:
Index Seek требуют больше дискового ввода-вывода (I/O), и общая производительность системы деградирует.rebuild) и обновление статистики (update statistics), на таблицах в десятки миллионов строк могут выполняться часами. В условиях круглосуточной работы бизнеса (режим 24/7) выделить такое технологическое окно становится серьезной проблемой.Платформа 1С создает индексы для таблиц базы данных автоматически, основываясь на структуре метаданных. Для независимого регистра сведений платформа всегда создает уникальный кластерный индекс, состоящий из измерений регистра плюс служебное поле периода (если регистр периодический).
Важное правило: Порядок полей в индексе жестко соответствует порядку расположения измерений в дереве конфигурации 1С. Рассмотрим это на примере.
Представим, что у нас есть регистр сведений остатков со следующей структурой измерений:
Товар (тип СправочникСсылка.Номенклатура)Магазин (тип СправочникСсылка.Склады)В базе данных СУБД создаст индекс по полям (Товар, Магазин). Если мы пишем запрос, в котором отбор накладывается только на измерение Магазин, то СУБД не сможет выполнить эффективный поиск по индексу (Index Seek). Ей придется сканировать весь индекс целиком (выполнять Index Scan), потому что ведущее поле индекса (Товар) не указано в условиях запроса. Для таблицы в 20 млн строк такое сканирование приведет к колоссальной нагрузке на процессор и дисковую подсистему.
Чтобы отбор по магазину работал мгновенно, необходимо сделать измерение Магазин самым первым в списке измерений регистра в конфигураторе. В таком случае индекс будет иметь вид (Магазин, Товар), и СУБД легко выполнит точечный поиск.
Давайте проанализируем ошибку проектирования регистра сведений, которая часто встречается у начинающих разработчиков. Рассмотрим структуру, описанную в обсуждении:
Store, GoodsQuantity, PriceStoreId, GoodsId (строковые уникальные идентификаторы объектов)Разработчик добавил реквизиты StoreId и GoodsId для того, чтобы делать по ним быстрый отбор и избегать левых соединений со справочниками (обращений через точку в запросе). Однако это приводит к избыточности данных и проблемам с индексацией.
Если мы делаем запрос к основной таблице регистра сведений с условием по реквизиту (например, Где StoreId = &Параметр), то СУБД будет вынуждена сканировать таблицу, так как по умолчанию реквизиты регистра сведений не входят в основной индекс. Если же мы проиндексируем эти реквизиты, мы получим дополнительные некластерные индексы, которые замедлят запись в базу данных.
Правильным решением будет отказаться от хранения строковых идентификаторов в реквизитах регистра. Вместо этого в условиях запроса следует использовать ссылки (измерения Store и Goods). Платформа 1С прекрасно справляется с передачей массива ссылок в параметры запроса. Посмотрим на правильный шаблон запроса:
Выбрать
Рег.Store Как Магазин,
Рег.Goods Как Товар,
Рег.Quantity Как Количество
Из
РегистрСведений.ОстаткиТоваров Как Рег
Где
Рег.Store = &ПараметрМагазин
В данном случае, если измерение Store стоит первым в списке измерений в конфигураторе, СУБД мгновенно найдет нужные строки, используя Clustered Index Seek.
Эскалация блокировок — это защитный механизм СУБД, предназначенный для экономии оперативной памяти сервера. Когда один процесс (транзакция) накладывает слишком много мелких блокировок строк (ROWLOCK) или страниц (PAGLOCK), СУБД автоматически заменяет их на одну монопольную блокировку всей таблицы (TABLOCK).
Порог эскалации в MS SQL Server: По умолчанию укрупнение блокировок происходит, когда одна транзакция запрашивает более 5 000 блокировок к одной таблице.
Почему это критично для нашего примера? Допустим, мы обновляем остатки по крупному магазину в Москве (где числится 10 000 товаров) одним пакетом. СУБД начинает блокировать строки для записи. Как только количество заблокированных строк превысит 5 000, СУБД заблокирует всю таблицу остатков. В этот самый момент все кассы других магазинов, менеджеры и фоновые задания, пытающиеся записать или прочитать данные из этой таблицы, выстроятся в очередь (начнутся ожидания на блокировках). По истечении времени ожидания пользователи увидят ошибку "Конфликт блокировок при выполнении транзакции".
Как мы можем предотвратить эскалацию блокировок?
ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Однако помните, что это увеличит потребление оперативной памяти сервером СУБД.Если мы используем современные версии платформы 1С (начиная с 8.3.25 и 8.3.26), мы можем задействовать новые механизмы записи независимых регистров сведений, которые кардинально повышают скорость работы с большими таблицами.
Раньше стандартный метод записи набора данных с замещением работал по схеме "удалить старые записи по отбору — вставить новые". На СУБД это транслировалось в тяжелые операции DELETE и INSERT, что приводило к генерации гигантского объема транзакционных логов и частым блокировкам.
Теперь нам доступно системное перечисление РежимЗамещения:
MERGE (или его аналог для PostgreSQL). Данные сопоставляются по ключевым полям (измерениям). Если строка с такими измерениями уже существует — она просто обновляется (выполняется UPDATE), если её нет — добавляется новая (INSERT). Это минимизирует количество блокировок и ускоряет запись в разы.Пример использования режима слияния при записи набора:
НаборЗаписей = РегистрыСведений.ОстаткиТоваров.СоздатьНаборЗаписей();
НаборЗаписей.Отбор.Store.Установить(ВыбранныйМагазин);
// Заполняем набор записей новыми данными...
НоваяЗапись = НаборЗаписей.Добавить();
НоваяЗапись.Store = ВыбранныйМагазин;
НоваяЗапись.Goods = ВыбранныйТовар;
НоваяЗапись.Quantity = НовоеКоличество;
// Записываем с использованием режима слияния
ПараметрыЗаписи = Новый ПараметрыЗаписиРегистраСведений;
ПараметрыЗаписи.РежимЗамещения = РежимЗамещенияРегистраСведений.Слияние;
НаборЗаписей.Записать(ПараметрыЗаписи);
Если объем таблицы остатков действительно колоссальный (сотни миллионов строк), и регламентные операции начинают парализовать работу системы, не нужно пытаться делить данные в конфигураторе 1С на разные таблицы (создавать регистры "ОстаткиМосква", "ОстаткиПитер" и т.д.). Это усложнит код, сделает невозможными простые консолидированные отчеты и превратит поддержку системы в кошмар.
Вместо этого следует использовать секционирование (партиционирование) таблиц на уровне СУБД:
Подводя итог нашему анализу, ответим на главный вопрос: нет, разделять таблицы вручную в конфигураторе 1С не имеет смысла. СУБД отлично справляется с таблицами в десятки и сотни миллионов строк при соблюдении правил разработки.
Для обеспечения максимальной производительности придерживайтесь следующих правил:
Магазин или Регион). Это гарантирует выполнение быстрого поиска Index Seek вместо сканирования всей таблицы.РежимЗамещения.Слияние для минимизации нагрузки на транзакционные логи СУБД.