Записки оптимизатора 1С (часть 2). Полнотекстовый индекс или как быстро искать по подстроке

В прошлом выпуске записок оптимизатора я обещал вернуться к другим практическим кейсам использования программы QProcessing. Так вот, сегодня речь пойдет про полнотекстовый индекс в высоконагруженных базах данных 1С. А точнее об альтернативе, которую можно предложить взамен полнотекстового поиска от 1С или MS SQL.

Речь пойдет о поисковых запросах по подстроке, которые на стороне SQL превращаются в конструкцию LIKE ‘%текст%’. Именно с двумя %%. В этом случае стандартные индексы не работают и SQL производит полное сканирование таблиц.

Идея о замене стандартных механизмов поиска по подстроке появилась еще шесть лет назад, а в 2018г была выпущена статья на эту тему. Ссылку давать не буду на сторонний ресурс, но 1С-ники знают где искать :-). За эти годы мы уже не раз реализовывали замену стандартного поиска 1С в формах через нашу методику. Прежде чем описать подход, напомню несколько моментов почему такая потребность в принципе существует. Ведь есть движок полнотекстового поиска от СУБД и есть полнотекстовый поиск от 1С.

Где востребован поиск по подстроке с точки зрения бизнес-задач?

В большинстве случаев – это динамические списки: справочник договоров, контрагентов, заказов, платежных документов и т.п. Есть поле поиска или волшебное сочетание «Ctrl+F». Пользователи ищут кого-то по ФИО, ищут номер заказа, номер паспорта, номер платежки и т.п. Ищут как правило не полное ФИО, наименование или номер, а какую-то значимую часть. Например, номер договора ищут без префиксов или без лидирующих нулей. Кроме того, очень часто в поле поиска начинают набивать строку и система, не дожидаясь окончания ввода, начинает поиск чуть ли не после первого введенного символа.

Примерный круг задач я описал. Теперь тезисно перечислю некоторые подводные камни, которые сопровождают полнотекстовый поиск от 1С и от движка SQL. Погружаться в детали я не буду, т.к. про это уже много и не раз написано. Важно понимать то, что полнотекстовый поиск подходит далеко не всем и требует тщательного обслуживания.

Итак, некоторые особенности полнотекстового поиска, о которых следует помнить.

Полнотекстовый индекс SQL:

Заполнение индекса происходит асинхронно и изменения применяются с некоторой задержкой относительно основной транзакции, зависящей от длины очереди обновления индекса. Задержка может быть как несколько секунд, так и несколько часов.
Полнотекстовый поиск может искать только от начала слова. Т.е. если мы ищем, например, номер договора без префиксов и лидирующих нулей, то полнотекстовый поиск вернет нам ничего или совсем не то, что ожидаем. Соответственно, если такая задача имеется, то необходимо подготавливать отдельную колонку, где номер договора разбивается на значимые части.

Полнотекстовый поиск 1С:

Версия платформы и режим совместимости сильно влияют на работу полнотекстового поиска. Так, на старых версиях платформы полнотекстовый поиск может работать некорректно или вообще не работать, выдавая пустой результат.
При нештатных перезагрузках сервера СУБД индекс полнотекстового поиска данных (ППД) может прийти в негодность.
При значительном или интенсивном изменении данных, регламентное задание по обновлению индекса ППД оказывает существенную нагрузку на систему. Время на перестроение индекса может измеряться десятками часов.
Необходимо включить полнотекстовый поиск для всех объектов конфигурации, которые могут использоваться в качестве основной таблицы динамического списка. Также в полнотекстовом поиске должны участвовать все реквизиты объектов конфигурации, которые могут отображаться в динамическом списке и по которым может потребоваться поиск.
Поиск выполняется не по всем колонкам динамического списка (и объекта конфигурации), а только по тем колонкам, которые отображаются в таблице. А поиск по ссылочным полям выполняется по полям представления, которые также нужно не забывать добавлять в полнотекстовый индекс.

Все эти аспекты делают процедуру обслуживания индекса довольно трудоемкой. Ведь если что-то настроено не так, индекс не отработает и поиск вернет либо ничего, либо переключится на стандартный механизм.

Задача поиска по подстроке очень востребована и в нашей практике поддержки высоконагруженных систем мы встречали разные варианты ее реализации, но преимущественно это были самописные механизмы поиска. При этом штатный поиск во многих формах очень часто запрещался на уровне прав и интерфейсов, либо переписывался с различными ограничениями: состав полей для поиска, количество вводимых символов не менее N, разделением строки на значимые символы и распределением их по колонкам и использованием поиска по начальным символам.

Поиск по подстроке нужен, но полнотекстовый индекс использовать не хотим. Как быть?

Основная проблема с полнотекстовыми индексами – это настроить их на практические задачи и поддерживать в актуальном состоянии состав индексируемых полей, чтобы обеспечить непротиворечивость данных при поиске. Под непротиворечивостью я подразумеваю довольно распространенные ситуации при использовании полнотекстового поиска, когда он возвращает пустую или неверную выборку. Это может приводить к неверным действиям пользователя. Например, пользователь добавит в справочник новый товар, который окажется дублем, ведь поиск его не нашел, а он в справочнике есть. Или пользователь объявит клиенту об отсутствии позиции в каталоге, на складе и т.п.

Расскажу о нашем опыте реализации быстрого поиска с помощью программы QProcessing.

Нужно было модернизировать механизм поиска среди платежных документов по полю «Назначение платежа», в котором может содержаться ФИО, номер договора, паспорт и т.п. Поиск должен выдавать пользователю список документов, в которых встречается набранная строка поиска.

У клиента уже был реализован свой поиск, полностью самописный (на 1С). Время поиска в среднем занимало 5-20 секунд (не прям уж запредельно большие цифры), но при этом обслуживание поиска было трудоёмким, отнимало много времени у техподдержки, перезаполнение «индекса» часто не успевало выполниться в регламентное окно и накладывалось на рабочий период, что приводило к падению производительности всей системы, вплоть до простоев.

Итого, задача в общем виде формулировалась как:

Ускорить поиск в 1С для отбора платежных документов по части комментария из поля «Назначение платежа» (не более 5…8 сек).
Снизить трудозатраты на обслуживание механизма поиска, чтобы оно гарантированно укладывалось в технологическое окно (1-2 часа).

К решению мы шли в два этапа.

Этап 1. Используем полнотекстовый индекс SQL

Разбить строку на части. Разделителем может являться пробел, точка с запятой, запятая и т.д. Для разбиения использовалась специальная библиотека Word Breaker SQLNGRAM.DLL.
Создать уникальный некластерный индекс по одной колонке. В любом документе (или справочнике) такая колонка есть – это Ссылка.if not exists( select top 1 1 from sys.indexes where name = N'sfp_index4fulltext ' and object_id = object_id(N'_Document272')) create unique nonclustered index sfp_index4fulltext on _Document272 (_IDRRef)
Создать для полнотекстового поиска отдельную колонку – expanded_descr, которая будет содержать комбинацию необходимых символов для поиска.if not exists ( select top 1 1 from sys.columns where name = N'expanded_descr' and object_id = object_id(N'_Document272')) alter table _Document272 add expanded_descr nvarchar(max)
Созданная колонка заполняется необходимыми даннымиif exists( select top 1 1 from sys.triggers where name = N'sfp_update_exp_description') declare @disable_tr nvarchar(max) select @disable_tr = 'disable trigger ['+tr.name + '] on ['+t.name+']' from sys.triggers tr inner join sys.tables t on t.object_id = tr.parent_id and tr.name = 'sfp_update_exp_description' exec sp_executesql @disable_tr go UPDATE x SET x.expanded_descr = /* select expanded_descr = */ replace( replace( replace( replace( replace( replace( lower(x._Description + N' '+ x._Fld17764) , N' ', N'@@'), N',', N'.'), N'(', N'#<'), N')', N'#>'), N'&', N'#$'), N'!', N'##') FROM _Document272 x WHERE x._Folder = 0x1 AND x._Fld1401 = 0 GO if exists( select top 1 1 from sys.triggers where name = N'sfp_update_exp_description') declare @enable_tr nvarchar(max) select @enable_tr = 'enable trigger ['+tr.name + '] on ['+t.name+']' from sys.triggers tr inner join sys.tables t on t.object_id = tr.parent_id and tr.name = 'sfp_update_exp_description' exec sp_executesql @enable_tr go
Добавляется триггер, обновляющий содержимое колонки expanded_descr, если в элемент справочника были внесены изменения. Одновременно выполняется экранирование символов, которые могут быть служебными с точки зрения полнотекстового поиска.SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID ('sfp_update_exp_description', 'TR') IS NOT NULL DROP TRIGGER sfp_update_exp_description; GO CREATE or ALTER TRIGGER [dbo].[sfp_update_exp_description] ON [dbo].[_Document272] AFTER UPDATE ,INSERT AS BEGIN SET NOCOUNT ON IF NOT ( UPDATE (_Description) OR UPDATE (_Fld17764) /* НаименованиеПолное */ ) RETURN; UPDATE x SET x.expanded_descr = /* select expanded_descr = */ replace( replace( replace( replace( replace( replace( lower(x._Description + N' '+ x._Fld17764 ) , N' ', N'@@'), N',', N'.'), N'(', N'#<'), N')', N'#>'), N'&', N'#$'), N'!', N'##') FROM _Document272 x INNER JOIN inserted i ON x._IDRRef = i._IDRRef WHERE x._Folder = 0x1 AND x._Fld1401 = 0 END
Создаётся каталог полнотекстового поиска и, собственно, сам индекс полнотекстового поиска.if not exists (select top 1 1 from sys.fulltext_catalogs where name = 'basic_ftc') CREATE FULLTEXT CATALOG [basic_ftc] WITH ACCENT_SENSITIVITY = OFF AS DEFAULT AUTHORIZATION [dbo] GO if exists( select top 1 1 from sys.fulltext_indexes where object_id = object_id(N'_Document272')) drop fulltext index on _Document272 create fulltext index on _Document272 (expanded_descr Language 1) key index sfp_index4fulltext with change_tracking = Auto

Далее с помощью QProcessing при помощи набора правил выполняется замена поискового запроса по подстроке (LIKE @P4) на полнотекстовый поиск по заранее подготовленной колонке: CONTAINS (T1. expanded_descr, @P4).

Результат. В целом все работало, поиск работал быстро. В 99% случаях корректно. Но бывали прецеденты, что поиск возвращал «ничего» или неверные строки (ссылки на документы), в которых искомой подстроки не было. Например, в первый раз поиск возвращал позицию, а при повторном запросе через несколько секунд уже ничего не возвращал.

В общем, это было непрогнозируемо, необъяснимо и непонятно, и сводилось к формулировке «Ну вот так работает полнотекстовый индекс».

Поэтому появился второй этап.

Этап 2. Свой аналог полнотекстового индекса

Основная идея – разбить строку «Назначение платежа» каждого документа на N частей окном (интервалом) 6 символов (6 – выбранное эмпирически ограничение на минимальную длину строки поиска) и смещением на один символ до конца строки. При этом Смещение окна учитывает разделители типа «;», «.», «:», «/» и т.д. То есть, если внутри окна содержится знак разделителя, то оно в индекс не входит.

Пример разбиения строки назначения платежа и ее упаковки в таблицу с учетом разделителей.

Платеж по договору №ФС-2134124124;Иванов Иван Иванович;паспорт№1378921

Подстрока из шести символов	Попадает в индекс или нет
Платеж	Да
латеж	Да
атеж п	Да
теж по	Да
…
124;Ив	Нет
24;Ива	Нет
4;Иван	Нет
;Ивано	Нет
Иванов	Да
…
в Иван	Да
…
378921	Да

Последовательность действий похожа на предыдущий этап.

Создание индексной таблицыCREATE TABLE [dbo].[FullIndex]( [id] [varchar](6) NOT NULL, [ref] [binary](16) NOT NULL, [c] [bigint] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_FullIndex] PRIMARY KEY CLUSTERED ( [id] ASC, [ref] ASC, [c] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Создание некластерного индекса для индексной таблицыCREATE NONCLUSTERED INDEX [ind1] ON [dbo].[FullIndex] ( [ref] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO
Создание хранимой процедуры по разбиению строки на словаCREATE PROC [dbo].[SetFullIndex] @Str varchar(max), @_IDRref binary(16), @fulltextindexname varchar(256) AS SET NOCOUNT ON declare @tempstr varchar(max) declare @int int declare @sql varchar(max) declare @delsql varchar(max) declare @startid int declare @endid int set @startid = 1 SET @tempstr = '' SET @delsql = 'DELETE FROM ' + @fulltextindexname + ' WHERE ref = 0x'+CONVERT(varchar(max),@_IDRref,2) +' ' SET @sql = 'INSERT INTO ' + @fulltextindexname + '(id,ref) VALUES' SET @int = 1 WHILE CHARINDEX(';',@str)<>0 BEGIN SET @endid = CHARINDEX(';',@str) SET @tempstr = SUBSTRING(@str,@startid,@endid-1) /*print @Str print STR(@startid) print STR(@endid) print @tempstr*/ IF LEN(@tempstr) > 6 BEGIN SET @int = 1 WHILE @int + 5 <= LEN(@tempstr) BEGIN IF @sql <> 'INSERT INTO ' + @fulltextindexname + '(id,ref) VALUES' SET @sql = @sql + ',' SET @sql = @sql + '(''' + REPLACE(SUBSTRING(@tempstr,@int,6),'''','''''') + ''',0x'+CONVERT(varchar(100),@_IDRref,2) + ') ' SET @int = @int+1 END END ELSE BEGIN IF @sql <> 'INSERT INTO ' + @fulltextindexname + '(id,ref) VALUES' SET @sql = @sql + ',' SET @sql = @sql + '(''' + REPLACE(@tempstr,'''','''''') +''',0x'+CONVERT(varchar(100),@_IDRref,2) + ') ' END SET @Str = SUBSTRING(@str,@endid+1,len(@Str)-@endid) END --print @str IF LEN(@str)>0 BEGIN SET @tempstr = @str IF LEN(@tempstr) > 6 BEGIN SET @int = 1 WHILE @int + 5 <= LEN(@tempstr) BEGIN IF @sql <> 'INSERT INTO ' + @fulltextindexname + '(id,ref) VALUES' SET @sql = @sql + ',' SET @sql = @sql + '(''' + REPLACE(SUBSTRING(@tempstr,@int,6),'''','''''') +''',0x'+CONVERT(varchar(100),@_IDRref,2) + ') ' SET @int = @int+1 END END ELSE BEGIN IF @sql <> 'INSERT INTO ' + @fulltextindexname + '(id,ref) VALUES' SET @sql = @sql + ',' SET @sql = @sql + '(''' + REPLACE(@tempstr,'''','''''') +''',0x'+CONVERT(varchar(100),@_IDRref,2) + ') ' END END --print @sql Exec(@delsql) EXEC(@sql)
Создание триггера на таблице с документами на изменение индексной таблицыСREATE TRIGGER [dbo].[sfp_update_fulltextindex] ON [dbo].[_Document272] AFTER UPDATE ,INSERT AS BEGIN SET NOCOUNT ON IF ( UPDATE (_Fld23035) ) BEGIN DECLARE @fulldescr varchar(max), @IDRref binary(16) DECLARE fullindex CURSOR FOR SELECT _Fld23035 as fulldescr, _IDRRef FROM INSERTED OPEN fullindex FETCH NEXT FROM fullindex INTO @fulldescr, @IDRref WHILE @@FETCH_STATUS = 0 BEGIN EXEC SetFullIndex @fulldescr, @IDRref, 'FullIndex' FETCH NEXT FROM fullindex INTO @fulldescr, @IDRref END CLOSE fullindex; DEALLOCATE fullindex; END END
Первоначальное заполнение индексной таблицы.Самая долгая процедура и длилась более суток. В результате индексная таблица заняла более 300 Гб.DECLARE @int int DECLARE @max int DECLARE @tempidrref binary(16) DECLARE @tempstr varchar(max) SET @int = 1 IF OBJECT_ID('tempdb..#AllRows') is null CREATE TABLE #AllRows (id int identity(1,1) primary key, _IdRref binary(16), _str nvarchar(4000)) ELSE truncate table #AllRows INSERT INTO #AllRows(_IDRref,_str) SELECT _IDRref, _Fld23035 expanded_descr from _Document272 SELECT @max = max(id) from #AllRows WHILE @int<=@max BEGIN SELECT @tempidrref = _IDRref,@tempstr = _str from #AllRows where id = @int Exec SetFullIndex @tempstr,@tempidrref,'FullIndex' SET @int = @int + 1 END
Далее настраиваем QProcessing и подменяем штатные поисковые запросы с LIKE%% от 1С на модифицированныеИсходный запросSELECT T1._IDRRef, T1._Marked, ... T1._Fld23035, ... FROM dbo._Document272 T1 WHERE ((T1._Fld2507 = @P10)) AND ((T1._Fld23035 LIKE @P11))', N'@P1 varbinary(16),@P2 varbinary(16),@P3 varbinary(16),@P4 numeric(10), @P5 numeric(10),@P6 numeric(10),@P7 numeric(10),@P8 numeric(10), @P9 numeric(10),@P10 numeric(10),@P11 nvarchar(4000)',0x8FE88206F16BC0A94F138691C51DBB25, 0x8FE88206F16BC0A94F138691C51DBB25, 0x8FE88206F16BC0A94F138691C51DBB25, 10,9,8,2,1,0,0,N'%Иванов Иван%'Измененный запросПоскольку количество возвращаемых записей из таблицы индекса может быть достаточно большим по популярным строкам (тысячи и даже десятки тысяч), то для уменьшения выборки в подменном запросе, без исключения основного условия с LIKE, мы вставляем дополнительную фильтрацию. Условие содержит не только первые шесть символов поисковой строки, но и последние шесть символов.SELECT T1._IDRRef, T1._Marked, ... T1._Fld23035, ... FROM dbo._Document272 T1 WHERE ( T1._IDRRef IN ( SELECT t1.ref FROM FullIndex t1 INNER JOIN FullIndex t2 ON t1.ref = t2.ref INNER JOIN FullIndex t3 ON t1.ref = t3.ref WHERE t1.id = SUBSTRING(@P11, 2, 6) AND t2.id = SUBSTRING(@P11, LEN(@P11) - 6, 6) AND t3.id = SUBSTRING(@P11, IIF(LEN(@P11) > 11,5,2), 6) ) ) AND ((T1._Fld2507 = @P10)) AND ((T1._Fld23035 LIKE @P11))', N'@P1 varbinary(16),@P2 varbinary(16),@P3 varbinary(16),@P4 numeric(10), @P5 numeric(10),@P6 numeric(10),@P7 numeric(10),@P8 numeric(10), @P9 numeric(10),@P10 numeric(10),@P11 nvarchar(4000)',0x8FE88206F16BC0A94F138691C51DBB25, 0x8FE88206F16BC0A94F138691C51DBB25, 0x8FE88206F16BC0A94F138691C51DBB25, 10,9,8,2,1,0,0,N'%Иванов Иван%'

Полученные результаты

1) Скорость поиска

По результатам внедрения было проведено отдельное автоматическое тестирование механизма поиска и сравнение со старым. Было отобраны 18 000 «номеров документов» и по ним осуществлен поиск среди документов за 6 лет. Время поиска фиксировалось.

Результаты представлены на рисунке ниже.

Комментарии, полагаю, излишни. Поиск в 1С ускорился в сотни раз и в 90% случаев стал занимать меньше 1 секунды.

2) Обслуживание и размер индексной таблицы [FullIndex]

После первоначального заполнения в таблице оказалось 2 млрд. строк, объём таблицы составил 345 Гб. Время первоначального заполнения – чуть более суток. У всех это время будет, естественно, разное и зависит от количества строк в исходной таблице (25 млн. строк), количества символов в поле комментария (в среднем 160 символов) и от аппаратных ресурсов.

При общем размере базы данных ~12 Тб увеличение ее размера составило ~3%.

Обновление записей таблицы [FullIndex] происходит автоматически – либо по триггеру на изменение таблицы [_Document272], либо по заданию с любой указанной периодичностью. Мы остановились на задании, чтобы исключить ненужные блокировки у пользователей.

Кроме того, настроили отдельное задание на пересчет статистик по таблице [FullIndex] раз в сутки, чтобы они пересчитывались гарантированно, независимо от пересчета статистик по всем остальным таблицам.

Ссылки на все части Записок оптимизатора1С: