Очень часто (до нескольких раз в неделю) на одном из популярных форумов по SQL-серверным технологиям, задается вопрос:
"Как быстро искать в таблице, когда начало строки или слова неизвестно?".
Вариаций вопроса много, а ответ один: MSSQL сервер не умеет эффективно искать по маске слова, если начало слова в маске неизвестно, причем полнотекстовый поиск тоже бессилен - ему нужно знать пусть не начало строки, но хотя бы слово из этой строки целиком. Да и другие СУБД не сильно лучше.
Ну не умеет так не умеет, мы его научим. Разобъем текст на слова, слова - на "синонимы" и запишем все это в самые обычные таблицы. Никаких дополнительных внешних механизмов, перестроений полнотекстовых каталогов и тому подобное.
Цитата
/* Если Вы еще не установили себе MSSQL2008 или 2012,
самое время это сделать - скрипты писались именно для 2008 версии,
хотя с незначительными переделками пойдут и на 2005
*/
-- Создаем таблицу, которую будем индексировать
IF OBJECT_ID('MyTable') IS NOT NULL DROP TABLE MyTable
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
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
Желающие в качестве домашнего задания могут попытаться оптимизировать триггер - данный был написан в лучших традициях 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)