При проектировании регистров сведений в платформе «1С:Предприятие 8» разработчики регулярно сталкиваются с дилеммой: стоит ли устанавливать признак «Индексировать» для ресурсов регистра? Особенно остро этот вопрос встает, когда ресурс имеет тип Строка, а логика приложения предусматривает выполнение к нему запросов с использованием отборов или оператора ПОДОБНО (аналог LIKE в SQL). В этой статье мы подробно и по шагам разберем, как физически устроен индекс по ресурсу, ускорит ли он ваши запросы, с какими подводными камнями вы можете столкнуться в СУБД MS SQL Server и PostgreSQL, а также рассмотрим архитектурные альтернативы.
Для того чтобы принять осознанное решение об индексировании, мы должны четко понимать, как платформа «1С» транслирует эту настройку на уровень СУБД. Распространенное заблуждение начинающих разработчиков заключается в том, что индекс строится исключительно по самому выбранному полю. На самом деле это не так.
Согласно официальной документации «1С:Предприятия» и стандартам разработки, при установке свойства «Индексировать» для ресурса непериодического регистра сведений платформа создает в СУБД некластерный индекс следующей структуры:
[Разделители] + Ресурс + Измерение1 + Измерение2 + ...
Если же мы анализируем периодический регистр сведений, то в структуру индекса дополнительно включается поле Период:
[Разделители] + Ресурс + Период + Измерение1 + Измерение2 + ...
Давайте разберем подробнее, почему платформа «1С» проектирует индексы именно таким образом. В основе этого решения лежат принципы работы реляционных СУБД, в частности механизмы адресации строк в таблицах (Row Locators).
В СУБД MS SQL Server таблицы физически организуются либо в виде кучи (Heap), либо в виде кластерного индекса (Clustered Index). В платформе «1С» таблицы регистров сведений всегда имеют кластерный индекс, ключом которого является комбинация всех измерений (плюс период и разделители, если они есть). Это означает, что данные в таблице физически отсортированы по измерениям.
Когда мы создаем дополнительный (некластерный) индекс по ресурсу, СУБД должна иметь возможность быстро связать найденную в индексе запись с физической строкой в основной таблице. Для этого в листовых страницах любого некластерного индекса СУБД обязана хранить ключ кластерного индекса — то есть все измерения регистра. Таким образом, включение измерений в индекс по ресурсу — это не прихоть платформы «1С», а архитектурная необходимость самой СУБД для выполнения операции Key Lookup (поиска по ключу).
Поскольку в индекс по ресурсу автоматически входят все измерения регистра, мы получаем так называемый покрывающий индекс (Covering Index) для определенного класса запросов. Давайте проанализируем ситуацию на примере.
Представим себе регистр сведений Штрихкоды, в котором:
Штрихкод (Строка)Номенклатура (СправочникСсылка.Номенклатура)Если у ресурса Номенклатура включено индексирование, СУБД построит индекс по схеме: Номенклатура + Штрихкод. Рассмотрим типичный запрос, который часто выполняется при выводе списка товаров:
ВЫБРАТЬ
Штрихкоды.Штрихкод КАК Штрихкод
ИЗ
РегистрСведений.Штрихкоды КАК Штрихкоды
ГДЕ
Штрихкоды.Номенклатура = &Номенклатура
В данном случае СУБД не придется обращаться к основной таблице на диске. Все необходимые данные (и условие отбора по номенклатуре, и выбираемое поле штрихкода) уже содержатся непосредственно в самом индексе ресурса. СУБД выполнит быструю операцию Index Seek (или Index Only Scan в PostgreSQL), что обеспечит мгновенный возврат результата даже на таблицах с миллионами записей.
Однако если ваш регистр содержит другие ресурсы (например, Характеристика) или реквизиты, и вы попытаетесь выбрать их в запросе:
ВЫБРАТЬ
Штрихкоды.Штрихкод,
Штрихкоды.Характеристика
ИЗ
РегистрСведений.Штрихкоды КАК Штрихкоды
ГДЕ
Штрихкоды.Номенклатура = &Номенклатура
Тогда СУБД, найдя нужные записи по индексу Номенклатура, будет вынуждена для каждой строки выполнять операцию Key Lookup (в MS SQL) или Bitmap Heap Scan (в PostgreSQL), чтобы «добрать» значение характеристики из основной таблицы. На больших выборках это может свести на нет всю эффективность использования индекса, и оптимизатор СУБД может принять решение полностью отказаться от него в пользу полного сканирования таблицы (Table Scan / Seq Scan).
Теперь ответим на ключевой вопрос автора темы: поможет ли индекс, если в запросах планируется использовать оператор ПОДОБНО по строковому ресурсу? Здесь все зависит от того, в какой части строки находится подстановочный знак процента (%).
Рассмотрим первый вариант — поиск по префиксу (началу строки):
ГДЕ
РегистрСведений.РесурсСтрока ПОДОБНО "Значение%"
В этом случае индекс по ресурсу ускорит выполнение запроса. Для СУБД такой запрос эквивалентен условию диапазона (например, РесурсСтрока >= 'Значение' И РесурсСтрока < 'Значениф'). СУБД сможет применить операцию поиска по индексу (Index Seek), быстро перейдя к первой подходящей записи и прочитав диапазон строк.
Рассмотрим второй вариант — поиск по подстроке или окончанию строки:
ГДЕ
РегистрСведений.РесурсСтрока ПОДОБНО "%Значение%"
// Или поиск по окончанию:
ГДЕ
РегистрСведений.РесурсСтрока ПОДОБНО "%Значение"
В этой ситуации стандартный индекс B-Tree (который используется во всех поддерживаемых «1С» СУБД) окажется абсолютно бесполезным. Поскольку сортировка в индексе идет слева направо, СУБД не может определить начальную точку поиска. Ей придется выполнить полный просмотр всего индекса или всей таблицы (Index Scan / Table Scan), последовательно проверяя каждую строчку на соответствие шаблону. В высоконагруженных системах такие запросы вызывают колоссальную нагрузку на дисковую подсистему и процессор.
Отдельно проанализируем поведение СУБД PostgreSQL, которая стала стандартом де-факто для многих российских внедрений «1С». Поведение PostgreSQL при работе со строковыми индексами имеет важную специфику, о которой часто забывают разработчики.
По умолчанию в PostgreSQL стандартные индексы B-Tree поддерживают сравнения на равенство (=) и диапазонные условия (<, >= и т.д.). Однако если в базе данных используется локаль, отличная от стандартной локали "C" (а в базах «1С» практически всегда настроена русская локаль для корректной сортировки строк), стандартный индекс не будет использоваться для префиксного поиска LIKE 'Значение%'!
Для того чтобы префиксный поиск задействовал индекс, в PostgreSQL его необходимо создавать с использованием специальных классов операторов, таких как varchar_pattern_ops. Так как платформа «1С» самостоятельно управляет структурой БД и генерирует стандартные индексы без указания этих специфических классов, в PostgreSQL даже запрос вида ПОДОБНО 'Значение%' по индексированному ресурсу с высокой долей вероятности приведет к полному сканированию таблицы. Об этом важно помнить при кроссплатформенной разработке.
Установка индекса на строковое поле таит в себе скрытую опасность, которая может проявиться в самый неподходящий момент — например, при попытке выгрузить клиент-серверную базу в файловую версию для локальной отладки или разработки. Для точечной очистки таких баз от лишних организаций перед началом работ используют обработку выборочного удаления.
В файловой СУБД «1С» (формат базы данных .1CD) существует жесткое системное ограничение: максимальная длина ключа индекса не может превышать 1920 байт. Давайте выясним, как платформа рассчитывает размер строкового поля при индексации:
РазмерВБайтах = ДлинаСтроки * 3 + 2
Если ваш строковый ресурс имеет неограниченную длину или просто объявлен как длинная строка (например, Строка(500)), то только под это поле в индексе зарезервируется 1502 байта. Добавим к этому структуру индекса регистра сведений, куда, как мы выяснили ранее, обязательно входят все измерения регистра. Если среди измерений есть другие строки или составные типы данных, суммарный размер ключа индекса легко перешагнет порог в 1920 байт.
В результате при первой же попытке записи данных или реструктуризации таблицы в файловом режиме вы получите критическую ошибку СУБД, и работа с базой будет заблокирована. Именно поэтому стандарты разработки фирмы «1С» строго запрещают устанавливать индексирование для строковых полей, длина которых превышает 300 символов. Для автоматизации тестирования и создания архивов баз перед выполнением реструктуризации используют программу Обновлятор 1С.
Каждый созданный индекс в базе данных — это не бесплатная функция. Мы должны четко соблюдать баланс между скоростью чтения и скоростью записи. Рассмотрим, что происходит при изменении данных в регистре сведений:
Период, любая вставка «задним числом» вызывает физическое перемещение страниц индекса на диске (операция Page Split), что крайне негативно сказывается на общей производительности СУБД.Поэтому золотое правило оптимизации звучит так: никогда не ставьте индексирование «про запас». Индекс должен создаваться только под конкретный критически важный запрос, который выполняется часто и производительность которого неудовлетворительна.
Если в процессе анализа бизнес-требований вы понимаете, что вам необходимо регулярно делать отборы по строковому полю, рассмотрите альтернативные варианты реализации, которые не будут вредить производительности базы данных.
Если отборы по данному полю являются ключевыми для функционирования системы, возможно, на этапе проектирования была допущена методическая ошибка, и это поле на самом деле является измерением регистра, а не ресурсом. Перенос поля в состав измерений автоматически включит его в кластерный индекс, что обеспечит максимальную скорость поиска без создания дополнительных некластерных индексов.
Если перед вами стоит задача реализовать нечеткий поиск по подстроке (поиск по части слова, поиск с опечатками, морфологический поиск), то стандартные индексы СУБД здесь бессильны. В платформу «1С» встроен отличный механизм — Полнотекстовый поиск данных (ППД). Он работает на базе специализированных индексов, оптимизированных для текстового анализа, и позволяет мгновенно находить нужные записи без создания нагрузки на транзакционную СУБД.
В крупных информационных системах с высокой интенсивностью транзакций тяжелые поисковые запросы выносят за пределы основной базы данных. Для этого настраивают регулярную синхронизацию данных (например, через механизмы интеграции или очереди сообщений) в специализированные поисковые движки, такие как Elasticsearch, или аналитические СУБД вроде ClickHouse. Пользователи осуществляют поиск там, а в «1С» передаются только готовые идентификаторы найденных объектов.
Подведем итог нашему подробному анализу:
ПОДОБНО "Значение%").ПОДОБНО "%Значение%") индекс работать не будет, СУБД выполнит полный просмотр таблицы.