При оптимизации быстродействия запросов в 1С разработчики часто сталкиваются с вопросом: поможет ли установка свойства Индексировать на строковый реквизит, если в тексте запроса накладывается условие с оператором ПОДОБНО? На практике нередко обнаруживается парадоксальная ситуация: после включения индексации запрос с поиском по маске не только не ускоряется, но иногда начинает отрабатывать даже медленнее, чем до оптимизации. Разберем по шагам, как работают индексы реляционных СУБД под капотом платформы 1С:Предприятие, почему шаблон поиска имеет решающее значение и какими штатными механизмами следует решать подобные задачи.
Для понимания сути проблемы проанализируем внутреннее устройство индексов. В СУБД, с которыми работает платформа 1С (MS SQL Server, PostgreSQL, IBM DB2, Oracle), пользовательские индексы строятся на основе сбалансированных деревьев (B-Tree). Данные в таком дереве упорядочены строго по алфавиту слева направо — по аналогии с классическим бумажным словарем или телефонной книгой.
Когда мы выполняем поиск по маске, поведение оптимизатора запросов СУБД напрямую зависит от положения спецсимвола процента (%):
ГДЕ
СпрНоменклатура.Наименование ПОДОБНО "Кабель%"
В этом сценарии СУБД точно знает, с каких символов начинается искомое значение. Оптимизатор преобразует такое условие в диапазонный поиск: от значения "Кабель" до первого значения, которое по алфавиту больше "Кабель...". В плане выполнения запроса это вызовет операцию Index Seek (точечный поиск по индексу). СУБД быстро находит нужную ветку дерева и считывает только подходящие строки. Здесь индекс работает на 100% своей эффективности.
ГДЕ
СпрНоменклатура.Наименование ПОДОБНО "%Кабель%"
ИЛИ СпрНоменклатура.Наименование ПОДОБНО "%Кабель"
Если маска начинается со знака процента, СУБД не знает первых символов строки. Древовидная упорядоченная структура B-Tree теряет возможность прямого перехода к нужным узлам. Чтобы найти совпадение, СУБД обязана просмотреть абсолютно каждую запись в таблице от начала до конца. В этом случае классический точечный поиск по индексу невозможен физически.
Рассмотрим техническую дискуссию, которая часто возникает при анализе планов выполнения в MS SQL Server или PostgreSQL. При условии ПОДОБНО "%Значение%" профайлер СУБД действительно может показывать обращение к индексу, но в режиме Index Scan (сканирование индекса), а не Index Seek.
Выясним разницу между этими операциями:
Почему же оптимизатор СУБД иногда предпочитает сканировать не саму таблицу, а некластерный индекс по реквизиту? Дело в размере данных. Индекс содержит только индексируемое поле и ссылку (_IDRRef), поэтому его страницы занимают на диске меньше места, чем строки всей таблицы со всеми остальными реквизитами. СУБД требуется прочитать меньше мегабайт с диска. Однако если в секции ВЫБРАТЬ запроса требуются другие поля объекта, которых нет в этом индексе, СУБД придется на каждую найденную строку делать дополнительное обращение к основной таблице (операция Key Lookup или RID Lookup), что на больших выборках полностью нивелирует всю выгоду.
Проанализируем практический результат автора вопроса: почему после включения индексирования тест показал ухудшение производительности? Выделим три ключевые причины:
INSERT, UPDATE, DELETE), так как СУБД обязана модифицировать не только основную таблицу, но и все связанные индексные деревья.Рассмотрим стандарты разработки фирмы «1С» и системные ограничения платформы:
Строка неограниченной длины платформой 1С в принципе запрещено устанавливать флаг Индексировать. Индексироваться могут только строки фиксированной или ограниченной длины.ПОДОБНО эффективно использует индекс только тогда, когда поиск ведется строго по префиксу ("Текст%"), а индексируемый строковый реквизит является либо единственным полем составного индекса, либо располагается первым в списке его полей.Если бизнес-логика задачи непременно требует поиска по произвольной подстроке (вхождению слова в середину текста), решать ее с помощью оператора ПОДОБНО "%...%" на таблицах объемом более нескольких десятков тысяч строк крайне не рекомендуется. Рассмотрим проверенные архитектурные альтернативы.
Для быстрого поиска фрагментов слов в платформе 1С предусмотрен встроенный механизм полнотекстового поиска. В версиях платформы 8.3.22 и выше реализован полнотекстовый поиск версии 2, обновляющий поисковый индекс на лету в момент записи объектов.
Посмотрим на пример программного поиска по подстроке через полнотекстовый поиск:
СписокПоиска = ПолнотекстовыйПоиск.СоздатьСписок();
СписокПоиска.СтрокаПоиска = "планы*";
СписокПоиска.ОбластьПоиска.Добавить(Метаданные.Справочники.Номенклатура);
СписокПоиска.ПерваяЧасть();
Пока СписокПоиска.ЕстьСледующая() Цикл
Для Каждого ЭлементРезультата Из СписокПоиска Цикл
НайденнаяСсылка = ЭлементРезультата.Значение;
// Обработка найденной ссылки
КонецЦикла;
СписокПоиска.СледующаяЧасть();
КонецЦикла;
Полнотекстовый поиск использует инвертированные индексы, ориентированные именно на поиск слов и корней без необходимости сканирования таблиц.
Если поиск требуется выполнять именно в рамках запросов (например, внутри сложных отчетов или динамических списков), разберем методику декомпозиции данных:
ПоисковыеСловаНоменклатуры) с измерениями Слово (индексированная строка фиксированной длины) и Номенклатура.
ВЫБРАТЬ
РегистрСлов.Номенклатура КАК Ссылка
ИЗ
РегистрСведений.ПоисковыеСловаНоменклатуры КАК РегистрСлов
ГДЕ
РегистрСлов.Слово ПОДОБНО "план%"
В такой схеме запрос будет использовать полноценный Index Seek по измерению регистра, обеспечивая мгновенный отклик даже на миллионах записей.
=), который всегда работает по индексу максимально эффективно.Подведем практический итог:
ПОДОБНО только при поиске по шаблону "НачалоСтроки%"."%Подстрока%" B-Tree индекс не дает эффективного поиска. Оптимизатор СУБД выполняет полное сканирование данных, а наличие индекса может даже замедлить общую скорость работы системы.