В процессе работы с большими базами данных 1С часто возникает задача оптимизации дискового пространства. Один из очевидных, на первый взгляд, способов — уменьшить максимальную длину текстовых реквизитов в крупных таблицах. Однако, как показывает практика, это не всегда приводит к ожидаемому результату. Рассмотрим реальный эксперимент, в ходе которого попытка сократить размер таблицы объемом более 11 Гб дала неожиданные результаты и помогла выявить истинные причины большого объема данных.
Исходная ситуация: имеется регистр сведений, хранящий историю изменений объектов. В нем более 7 миллионов записей, а размер таблицы в SQL составляет 11.6 Гб. Все текстовые поля имеют переменную длину 100 символов. Автор эксперимента рассчитал максимальную фактическую длину для каждого поля и установил новые, более короткие ограничения, ожидая сокращения размера таблицы на 20-30%. Однако итоговая экономия составила всего 5%. Последующий эксперимент с переводом полей на фиксированную длину и вовсе привел к двукратному увеличению размера таблицы. Давайте разберемся, почему так произошло и как на самом деле эффективно оптимизировать хранение данных.
Ключ к пониманию результатов эксперимента лежит в том, как MS SQL Server, используемый платформой 1С, работает с разными типами строковых данных. Платформа 1С транслирует свои типы данных в соответствующие типы СУБД.
Переменная длина (varchar или nvarchar). Когда вы в 1С устанавливаете для реквизита тип "Строка" с определенной длиной, в базе данных создается поле типа varchar (или nvarchar для Unicode). Главная особенность этого типа в том, что он занимает на диске ровно столько места, сколько символов содержит конкретное значение, плюс несколько байт служебной информации для хранения длины. Например, если в поле varchar(100) хранится строка "Привет", она займет место под 6 символов, а не под 100. Именно поэтому простое уменьшение максимальной длины со 100 до 50 не дало эффекта — средняя длина хранимых строк и так была небольшой, и для них ничего не изменилось. Экономия была достигнута только за счет служебной перестройки таблицы (дефрагментации), а не за счет логики хранения.
Фиксированная длина (char или nchar). Ситуация кардинально меняется с полями фиксированной длины. Если бы мы использовали поле типа char(100), оно всегда занимало бы на диске место, эквивалентное 100 символам. Если реальная строка короче, оставшееся пространство заполняется пробелами. Этим и объясняется двукратное увеличение размера таблицы во второй части эксперимента. Каждая запись в каждом текстовом поле стала занимать максимально отведенное ей место, независимо от реальной длины данных.
Вывод: для экономии места всегда предпочтительнее использовать строки переменной длины, так как они более эффективно используют дисковое пространство.
Как показал дальнейший анализ в ходе обсуждения, основную часть дискового пространства занимали не сами данные, а индексы. Индексы — это специальные структуры, которые СУБД создает для ускорения поиска и отборов по таблице. За скорость приходится платить местом на диске.
В рассматриваемом случае выяснилось, что из 11 Гб общего размера таблицы сами данные занимали около 4 Гб, а индексы — 7.5 Гб! Когда в состав индекса включаются длинные текстовые поля, размер самого индекса может многократно превышать размер данных.
После того, как автор эксперимента пересмотрел состав индексов и отключил несколько избыточных, размер таблицы значительно сократился:
При этом, по словам автора, скорость выполнения отборов практически не изменилась, что говорит о том, что удаленные индексы были неэффективны для его задач. Это главный урок эксперимента: оптимизацию больших таблиц нужно начинать с ревизии индексов.
Хранение истории изменений в одной "плоской" таблице с множеством текстовых полей — простое, но не самое эффективное решение. Рассмотрим более продвинутые архитектурные подходы, которые помогут кардинально сократить объем данных.
Вместо того чтобы в каждой из 7 миллионов записей хранить текстовые строки "ИмяТабличнойЧасти", "ТипОбъекта", "Пользователь", можно создать отдельные таблицы-справочники (словари).
Пример: Вместо хранения строки "ДокументСсылка.РеализацияТоваровУслуг" (37 символов) в каждой записи, вы один раз создаете элемент в справочнике "ТипыОбъектов" и в дальнейшем храните только его 16-байтную ссылку. Экономия становится колоссальной при большом количестве однотипных записей.
ХранилищаЗначенияЭтот метод предполагает упаковку всех деталей одного события изменения в структуру или массив и сохранение их в одно поле типа ХранилищеЗначения.
Как это работает: Если в одном документе изменилось три реквизита, вместо трех записей в регистре вы делаете одну. В ее поле типа ХранилищеЗначения вы помещаете массив структур, где каждая структура описывает одно изменение: имя реквизита, старое значение, новое значение.
Можно разделить одну большую таблицу на несколько логических частей:
Такой подход позволяет более гибко управлять индексами. Например, основная таблица "шапок" может быть хорошо проиндексирована для быстрого поиска событий по дате или пользователю, а таблица с деталями может иметь минимум индексов.
Самый простой и часто очень эффективный метод. Если исторические данные за прошлые годы не требуются для оперативной работы, их можно периодически удалять или переносить в архивную базу.
Пример реализации: Создайте регламентное задание, которое раз в год (или чаще) удаляет из регистра записи старше, например, трех лет. Перед удалением эти данные можно выгрузить в архивную копию базы, которая будет храниться отдельно.
Итог: Эксперимент наглядно показал, что для эффективной оптимизации дискового пространства в базах данных 1С важен комплексный подход. Простая настройка длины полей не даст результата, если не понимать, как СУБД хранит данные. Первоочередное внимание следует уделять анализу и оптимизации индексов, а для кардинального решения проблемы — пересмотру архитектуры хранения данных.