Что делать, когда Full-Text бессилен

Что делать, когда Full-Text бессилен

Очень часто (до нескольких раз в неделю) на одном из популярных форумов по SQL-серверным технологиям, задается вопрос:

"Как быстро искать в таблице, когда начало строки или слова неизвестно?".

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

Ну не умеет так не умеет, мы его научим. Разобъем текст на слова, слова - на "синонимы" и запишем все это в самые обычные таблицы. Никаких дополнительных внешних механизмов, перестроений полнотекстовых каталогов и тому подобное.

Цитата

/* Если Вы еще не установили себе MSSQL2008 или 2012,

самое время это сделать - скрипты писались именно для 2008 версии,

хотя с незначительными переделками пойдут и на 2005


*/

-- Создаем таблицу, которую будем индексировать

IF OBJECT_ID('MyTable') IS NOT NULL DROP TABLE MyTable

GO

CREATE TABLE MyTable

(

RecID uniqueidentifier default newid() primary key,

Descr nvarchar(4000) COLLATE Cyrillic_General_100_CI_AS NOT NULL,

InputTime datetime default GETDATE()

)

GO

Данные в эту таблицу мы внесем позже, когда все будет готово к "индексации".



Теперь - создадим кое-какие служебные функции и таблицы:

Цитата

-- Создаем "опорную" таблицу с числами

IF OBJECT_ID('FT_Numbers') IS NOT NULL DROP TABLE FT_Numbers

GO

CREATE TABLE

FT_Numbers

(

Number smallint PRIMARY KEY

)

-- Заполняем (2048 чисел)

INSERT INTO FT_Numbers(Number)

SELECT number

FROM master..spt_values

WHERE type='P'

GO

-- Функции разбивки на слова

IF OBJECT_ID('FT_Split') IS NOT NULL DROP FUNCTION FT_Split

GO

CREATE FUNCTION FT_Split(@str nvarchar(4000))

RETURNS TABLE

AS

RETURN(

SELECT TOP 2000

Number as Pos,

SUBSTRING(@str, Number, ISNULL(NULLIF(CHARINDEX(' ', @str, Number), 0), LEN(@str)+1) - Number) as Word

FROM FT_Numbers (NOLOCK)

WHERE Number BETWEEN 1 AND LEN(@str)

AND (SUBSTRING(@str, Number-1, 1) = ' ')

AND CHARINDEX(' ', @str, Number) > Number

)

GO

IF OBJECT_ID('FT_SplitWord') IS NOT NULL DROP FUNCTION dbo.FT_SplitWord

GO

CREATE FUNCTION dbo.FT_SplitWord(@word nvarchar(4000))

RETURNS TABLE

AS

RETURN

(

SELECT

SUBSTRING(@word, n1.Number, LEN(@word) - n1.Number + 1) as Word

FROM FT_Numbers n1 (NOLOCK)

WHERE n1.Number BETWEEN 1 AND LEN(@word) - 2

)

GO

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

Вторая - возвращает... впрочем, проще показать пример:

select * from FT_SplitWord('12345')

Word

----------

12345

2345

345

в общем, подстроки исходного слова, начиная с N позиции и до конца слова

Конечно, эти функции можно реализовать и на CLR, это уже дело вкуса и желания. (Кстати, пример реализации первой функции на CLR есть в Samples от Microsoft еще к MSSQL2005).

Далее - создадим таблицы, в которых будет складываться данные в подготовленном к поиску виде:

Цитата

-- Справочник "слов"

IF OBJECT_ID('FT_Word') IS NOT NULL DROP TABLE FT_Word

GO

CREATE TABLE FT_Word

(

WordID int identity(1,1) CONSTRAINT PK_FT_Word PRIMARY KEY NONCLUSTERED,

Word nvarchar(400) COLLATE Cyrillic_General_100_CI_AS NOT NULL

)

CREATE UNIQUE CLUSTERED INDEX IX_FT_Word_Word ON FT_Word(Word) WITH(DATA_COMPRESSION = PAGE)

go



-- "Индекс"

IF OBJECT_ID('FT_Index') IS NOT NULL DROP TABLE FT_Index

GO

CREATE TABLE FT_Index

(

WordID int NOT NULL,

ID uniqueidentifier NOT NULL,

CONSTRAINT PK_FT_Index PRIMARY KEY(WordID, ID) WITH(DATA_COMPRESSION = PAGE)

)

CREATE NONCLUSTERED INDEX IX_FT_Index_ID ON FT_Index(ID)

GO



-- Тезаурус или словарь синонимов

IF OBJECT_ID('FT_Tes') IS NOT NULL DROP TABLE FT_Tes

GO

CREATE TABLE FT_Tes

(

WordID int NOT NULL,

Word nvarchar(400) COLLATE Cyrillic_General_100_CI_AS NOT NULL,

CONSTRAINT PK_FT_Tes PRIMARY KEY (Word, WordID) WITH(DATA_COMPRESSION = PAGE)

)

CREATE NONCLUSTERED INDEX IX_FT_Tes_WordID ON FT_Tes(WordID)

GO

Тезаурус будет заполняться "на лету":

Цитата

-- Триггер, заполняющий тезаурус по словарю

IF OBJECT_ID('TR_FT_Word') IS NOT NULL DROP TRIGGER TR_FT_Word

GO

CREATE TRIGGER TR_FT_Word ON FT_Word

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

SET NOCOUNT ON



DELETE FROM FT_Tes WHERE WordID in (SELECT WordID FROM deleted)



INSERT INTO FT_Tes

(

WordID,

Word

)

SELECT

i.WordID,

s.Word

FROM inserted i

CROSS APPLY FT_SplitWord(i.Word) s

END

GO

Пока все просто: как только в таблице FT_Word появится "12345", в таблице FT_Tes будут записи "12345", "2345", "345" с соответствующим номером WordID

Теперь расскажем системе, как индексировать сам текст:

Цитата

-- Индексирующий триггер

IF OBJECT_ID('TR_MyTable_FT') IS NOT NULL DROP TRIGGER TR_MyTable_FT

GO

CREATE TRIGGER TR_MyTable_FT ON MyTable

AFTER

INSERT, UPDATE, DELETE

AS

BEGIN

SET NOCOUNT ON



IF NOT UPDATE(Descr) RETURN

DECLARE

@IC int,

@UC int



-- Создаем список слов

CREATE TABLE #WordList (

RecID uniqueidentifier not null,

WordID int NULL,

Word nvarchar(400) COLLATE Cyrillic_General_100_CI_AS NOT NULL,

UNIQUE CLUSTERED (Word, RecID)

)



INSERT INTO #WordList

(

RecID,

Word)

SELECT

RecID,

sq.Word

FROM(

SELECT DISTINCT RecID, s.Word

FROM (SELECT RecID, UPPER(Descr) as Descr FROM inserted) c

CROSS APPLY dbo.FT_Split(c.Descr) s

) sq

SET @IC = @@ROWCOUNT



-- Можно было бы сразу подключать существующие слова, используя LEFT JOIN в предыдущем запросе

-- Но данный вариант более "стабильно" работает на больших объемах данных.

UPDATE wl

SET WordID = w.WordID

FROM #WordList wl

INNER JOIN FT_Word w WITH(NOLOCK)

ON w.Word = wl.Word



SET @UC = @@ROWCOUNT



-- Если число добавленных слов не равно числу полученных идентификаторов слов

-- (попросту - не все слова из индексируемых записей нашлись в словаре)

IF @IC <> @UC

BEGIN

-- Добавляем в словарь несуществующие еще слова

INSERT INTO FT_Word WITH(TABLOCKX) (Word)

SELECT DISTINCT Word

FROM #WordList

WHERE WordID IS NULL



-- Если что-то добавилось, обновляем значения

-- Вообще-то, проверку на @@ROWCOUNT можно и не далать :)

IF @@ROWCOUNT >0

BEGIN

UPDATE wl

SET

WordID = w.WordID

FROM #WordList wl

INNER JOIN FT_Word w WITH(TABLOCKX) ON w.Word = wl.Word

WHERE wl.WordID IS NULL

END

END



DELETE f

FROM deleted i

INNER

JOIN FT_Index f WITH(TABLOCKX)

ON f.ID = i.RecID



-- Неиспользованные "слова" не удаляем - даже если не используются - пригодятся потом.

-- Это повысит производительность при изменении данных и позволит работать с нестандартным наполнение тезауруса



INSERT FT_Index WITH(TABLOCKX)

(

WordID,

ID

)

SELECT

WordID,

RecID

FROM #WordList



END

GO

Логика триггера простая - разбиваем текст на слова, выявляем их идентификаторы, если они уже есть в таблице FT_Word, если еще нет - добавляем слова и в итоге - сохраняем пары "��дентификатор слова<=>идентификатор записи" в FT_Index.

Данный триггер обрабатывает записи примерно со скоростью 200-5000 записей в секунду в зависимости от размера строк и количества слов в них:

Цитата

-- Наполняем данными из sysmessages

DECLARE

@st datetime,

@rc int

SET @st = GETDATE()



insert into MyTable(Descr)

select text from master.sys.messages --where language_id = 1031

SET @rc = @@ROWCOUNT



PRINT CONVERT(varchar(20), (@rc) / CONVERT(money, DATEDIFF(ms, @st, GETDATE())/1000. )) + ' rec/sec'

Желающие в качестве домашнего задания могут попытаться оптимизировать триггер - данный был написан в лучших традициях MSSQL под среднепотолочную задачу - не свалиться на больших изменениях и не распылять ресурсы ради одной записи.



Теперь уже можно искать одно слово, например, так:

Цитата

DECLARE @srch varchar(200) = 'ерийного'

SELECT m.RecID, m.Descr

FROM

(

SELECT DISTINCT i.ID

FROM FT_Tes t

INNER JOIN FT_Index i ON i.WordID = t.WordID

WHERE Word LIKE @srch+'%'

) q

INNER JOIN MyTable m ON m.RecID = q.ID

Или искать по одновременному нахождению нескольких слов, используя специальную функцию:

Цитата

IF OBJECT_ID('FT_Search') IS NOT NULL DROP FUNCTION FT_Search

GO

CREATE FUNCTION FT_Search(@wordlist nvarchar(800))

RETURNS @ID TABLE(ID uniqueidentifier)

AS

BEGIN

DECLARE @wl TABLE(word nvarchar(500) COLLATE Cyrillic_General_100_CI_AS NOT NULL, number int)

DECLARE @wc int

INSERT INTO @wl(word, number)

SELECT --TOP 200

DISTINCT

SUBSTRING(@wordlist, Number, ISNULL(NULLIF(CHARINDEX(' ', @wordlist, Number), 0), LEN(@wordlist)+1) - Number),

ROW_NUMBER() OVER ( ORDER BY SUBSTRING(@wordlist, Number, ISNULL(NULLIF(CHARINDEX(' ', @wordlist, Number), 0), LEN(@wordlist)+1) - Number))

FROM FT_Numbers WITH(NOLOCK)

WHERE Number BETWEEN 1 AND LEN(@wordlist)

AND (SUBSTRING(@wordlist, Number-1, 1) = ' ')



SET @wc = @@ROWCOUNT

IF @wc > 0

BEGIN

INSERT INTO @ID

SELECT

i.ID

FROM @wl wl

INNER JOIN FT_Tes w

ON w.Word LIKE wl.Word + '%'

INNER JOIN FT_Index i

ON i.WordID = w.WordID

GROUP BY i.ID

HAVING COUNT(distinct wl.number) = @wc

END

RETURN

END

GO

SELECT t.*

FROM FT_Search('НДЕКС CREATE') i

INNER JOIN MyTable t

on t.RecID = i.ID

Сортировать по: Дата публикации | Последние | Самый полезный
Комментарии
  • Интересная статья, спасибо.

  • Good article

Страница 1 из 1 (элементов: 2)