Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
rere1234
Member

Откуда:
Сообщений: 38
Доброе время суток уважаемые форумчане,
Столкнулся с не тривиальной для себя задачей настройки полнотекстового поиска, как альтернативой использованию оператора like(он отрабатывал крайне медленно).

Характеристики системы:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
	May 14 2014 18:34:29 
	Copyright (c) Microsoft Corporation
	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)


Схема таблица по которой планируется производить создание индекса:
CREATE TABLE [dbo].[Data](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[DataDateTime] [datetime2](7) NOT NULL,
	[Field1] [smallint] NULL,
	[Field2] [tinyint] NOT NULL,
	[Field3] [tinyint] NULL,
	[Field4] [real] NULL,
	[Field5] [bit] NOT NULL,
	[Field6] [bit] NOT NULL DEFAULT ((0)),
	[Field7] [tinyint] NULL,
	[FullTextSearchColumn] [varchar](20) NOT NULL,
	[FullTextSearchColumnReverse] [varchar](20) NOT NULL,
	[Field8] [nvarchar](1000) NULL,
 CONSTRAINT [PK_DATA] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [fgDataPrimaryIndex]
)


CREATE CLUSTERED INDEX [CIX_Data_DataDateTime] ON [dbo].[Data]
(
	[DataDateTime] 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, FILLFACTOR = 100)
on [fgData]


Число записей ~2 млрд. Таблица секционирована по полю DataDateTime по дням, среднее число записей в секции ~5 млн - то число записей которое попадает в систему за день (вставка производится BULK INSERT).
Поиск планируется производить по полям [FullTextSearchColumn] и [FullTextSearchColumnReverse], в которых хранится строка символов и та же строка с байтами в обратном порядке (Reverse()). Таблица изредка грузится запросами Select. Update используется очень редко

Планируемые скрипты по созданию полнотекстового индекса:

CREATE FULLTEXT CATALOG FullTextSearchCatalog
WITH ACCENT_SENSITIVITY = OFF

DROP FULLTEXT INDEX ON [dbo].[Data]

CREATE FULLTEXT INDEX ON [dbo].[Data] (FullTextSearchColumn, FullTextSearchColumnReverse)
KEY INDEX PK_DATA 
ON ([FullTextSearchCatalog], FILEGROUP [fgFullTextSearch])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = OFF, SEARCH PROPERTY LIST = OFF)


Все запросы на выборку имеют вид
Select top(6000) * from Data
where -- условия по любым из полей
order by DataDateTime desc


Хотелось бы услышать мнение людей имеющих опыт решения схожих задач. И вообще справится ли с такими нагрузками компонент FullTextSearch?
С каким интервалом будет отрабатывать автоматическое обновление индекса?
Заранее спасибо за ответы
14 янв 15, 17:38    [17118516]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
sub zero -275
Guest
rere1234,

Покажите предикат like который тормозил. А лучше планы выполнения. если это: like '%что то там%' то оно и будет тормозить, если like 'что то там%' то поможет индекс, если like '%что то там' то поможет индекс в обратном порядке. FTS не поможет. он делает лингвистической поиск. с поиском '%чт-то там' и like '%что-то там%' у него еще хуже, он может только 'что-то там%'.
14 янв 15, 17:45    [17118542]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8494
rere1234, если Вы собираетесь искать произвольные подстроки, а не по словам, то Вам ничего не поможет в простом случае.
14 янв 15, 17:51    [17118574]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
rere1234
Member

Откуда:
Сообщений: 38
sub zero -275,
Проблемы как раз были с like вида '%X%' который вызывал fullscan таблицы. Использовать FTS я хотел именно для поиска 'X%'. Чтобы не делать '%X' хотел использовать поиск по инверсному полю [FullTextSearchColumnReverse], по которому также смогу делать поиск 'X%'. Ну а для варианта '%X%' оставить использование like. Идея с FTS появилось по причине выигрыша по скорости поиска на сравнительно небольшой тестовой базе (10 млн записей).
14 янв 15, 17:54    [17118594]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
rere1234
Хотелось бы услышать мнение людей имеющих опыт решения схожих задач. И вообще справится ли с такими нагрузками компонент FullTextSearch?
Хелп уже читали?
На всякий: http://msdn.microsoft.com/ru-ru/library/ms142571.aspx
А вот тут есть некоторые формулы: http://msdn.microsoft.com/ru-ru/library/ms142560.aspx
Может, помогут оценить производительность и потребности в ресурсах. А то, бывает, вроде бы не такая большая таблица, и сервер не загружен, а скорость индексации маленькая.

Сам я на таких таблицах FTS использовал, но время уже прошло, не помню деталей (вроде всё было быстро, не было проблем).
14 янв 15, 17:59    [17118623]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
rere1234
Использовать FTS я хотел именно для поиска 'X%'. Чтобы не делать '%X' хотел использовать поиск по инверсному полю [FullTextSearchColumnReverse], по которому также смогу делать поиск 'X%'
Для такого поиска обычный индекс будет безусловно быстрее, никаких сомнений.

Для варианта '%X%' FTS поможет, только если вы ищите целиком по словам. Если искать по произвольной части слов в тексте, то и FTS будет бессилен.

Тогда только делать самодельный поиск (можно воспользоваться наработками Dmitriy Kostylev, поищите, но что то их удалили отовсюду :-( )
14 янв 15, 18:09    [17118677]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Может как раз ваш случай
Тынц
15 янв 15, 10:03    [17120723]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
Maxx
Может как раз ваш случай
Тынц
Мда, всё меньше микрософтовских ресурсов остаётся доступными. Сначала команда Live их убивала, сейчас подключились TechNet. MSDN у меня пару лет вообще не работал...
Сейчас просит создать вики-аккаунт, кнопки "создать" нету, она невидима, если её всё таки найти и нажать, возвращает в эту же форму, никаких сообщений об ошибке естественно нет.
Из нескольких браузеров пробовал, само собой.

Деградация, раньше у МС были лучшие в индустрии порталы и хелп, постепенно всё убивается...
15 янв 15, 10:43    [17120973]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
alexeyvg,

упс сорри не проверил,оно у меня в Избранных в спец папке лежит - просто перепечатал сюда и все :( А жаль если не работает
15 янв 15, 10:45    [17120985]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
мимокрокодил83284
Guest
Maxx,

у мены тынц открылся
15 янв 15, 10:47    [17121001]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
мимокрокодил83284
Maxx,

у мены тынц открылся

ну так если не сложно - заверните в спойлер и выложите сюда.
15 янв 15, 10:48    [17121007]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
мимокрокодил83284
Guest
Maxx
Может как раз ваш случай
Тынц


http://social.technet.microsoft.com/wiki/ru-ru/contents/articles/12921.full-text-ru-ru.aspx
Очень часто (до нескольких раз в неделю) на одном из популярных форумов по 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

15 янв 15, 10:58    [17121042]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31824
мимокрокодил83284
Maxx,

у мены тынц открылся
Ну понятно, что он у кого то открывается. Если я переустановлю винду, у меня может тоже откроется.
Это просто криворукие программисты у них теперь, а менеджеры уже как несколько лет перестали отвечать за результат. Надо же, додуматься, при совершении действия пользователем (заполнении профиля пользователя) они не возвращают результат, а выводят опять ту же страницу :-)

Извините за оффтроп, конечено.

Спасибо за выложенный код, я вот собственно его выше и предлагал использовать. К сожалению, из ресурсов msdn, на которые были ссылки из sql.ru, его удалили (ну, тоже см. выше про криворукий МС - ссылки там теперь больше нескольких месяцев не живут).
15 янв 15, 11:10    [17121146]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
o-o
Guest
alexeyvg,

+
боюсь, это какие-то проблемы именно у вас.
у нас РЕЖУТ ВСЕ.
иногда пишут причину, как они отклассифицировали данный сайт, а чаще просто не отрывается и пишет таймаут.
пару раз в год мне выпадает ошибка, что и форум отклассифицирован как чат(!)
но вот это спокойно открылось
15 янв 15, 12:08    [17121519]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
ЕвгенийВ
Member

Откуда: Москва
Сообщений: 4968
alexeyvg
Мда, всё меньше микрософтовских ресурсов остаётся доступными. Сначала команда Live их убивала, сейчас подключились TechNet. MSDN у меня пару лет вообще не работал...
Сейчас просит создать вики-аккаунт, кнопки "создать" нету, она невидима, если её всё таки найти и нажать, возвращает в эту же форму, никаких сообщений об ошибке естественно нет.
Из нескольких браузеров пробовал, само собой.

Деградация, раньше у МС были лучшие в индустрии порталы и хелп, постепенно всё убивается...

У меня все прекрасно работает... Даже многие исходники на их TFS лежат.
15 янв 15, 16:40    [17123415]     Ответить | Цитировать Сообщить модератору
 Re: Настройка полнотекстового поиска на большой таблице (~2 млрд)  [new]
o-o
Guest
ЕвгенийВ,
а suppport у вас открывается?
у меня, например, https://support.microsoft.com/kb/815436?wa=wsignin1.0
сегодня весь день думает

К сообщению приложен файл. Размер - 74Kb
15 янв 15, 17:09    [17123612]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить