Полнотекстовый поиск: MS Full-Text Search

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

Итак, я не буду повторять массу открыто доступной информации о Microsoft Full-Text Search (общее описание от Microsoft (eng/рус) и Wikipedia, архитектура), не буду и переписывать простые и не очень примеры. Сосредоточимся на следующем сценарии: у нас имеется некоторое не слишком сложное приложение (веб-приложение, два-три слоя, до несколько Гб данных (до десятка-другого), пара-тройка вспомогательных сервисов) и нам необходимо обеспечить "интеллектуальный поиск" для некоторых сущностей этого приложения. "Интеллектуальность" поиска - маркетинговый прием, которым sales/accounts привлекают пользователей. С чисто технической точки зрения за ним может скрываться следующее:

• находить не только точные вхождения слов из поискового запроса, но и словоформ (мама/маме/мамы/маму ...)
• находить не только слова, введенные пользователем, но и синонимы
• ранжировать результаты поиска по релевантности
• искать не только по атрибутам сущности, но и в теле файла, который с этой сущностью связан
• выполнение сложных запросов (вроде "чтобы вот эти два термина находились поблизости", задание весов для слов в поисковом запросе и много чего еще)

Из описания можно заключить что скорее всего этот функционал все-таки в большей степени ориентирован на конечного пользователя (и скорее всего "продвинутого пользователя") и врядли будет использоваться в каких-то вспомогательных сервисах (хотя как знать ...). Теперь рассмотрим насколько сложно/просто будет все это сделать и отгрузить заказчику. Обратимся к следующим основным вопросам: изменения в архитектуре приложения, поддержка/сопровождение и альтернативы.

Архитектура

Здесь Microsoft потрудилась на славу: технология достается нам бесплатно не только в смысле денег (идет в комплекте с SQL Server и есть даже у SQL Server Express (with Advanced Services)) но и в смысле интеграции. Вся сложность ложится на плечи SQL Server-а:

1. Создаем полнотекстовый каталог
2. Создаем полнотекстовый индекс
3. Используем специальные функции (CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE) для обращения к индексам из T-SQL кода

Таким образом, код приложения может быть полностью абстрагирован от того, каким образом получены данные: с применением полнотекстового поиска или без него. В обмен на эту простоту на стороне СУБД нас поджидает:

1. Необходимость проектирования физического уровня: сколько каталогов нам нужно и где они будут храниться
2. Логический уровень: какие индексы нам нужны, какие колонки индексировать
3. Подробности алгоритмов обработки данных: стоп-список (noise words (в SQL Server 2005 - фиксированный, один на сервер, в SQL Server 2008 можно создавать пользовательские)), алгоритм разбиения на слова (word breaker), выделение словоформ (stammer), фильтрация содержимого (content filters)

Первые два уровня для начала/в несложных случаях, пожалуй, можно отдать на откуп SQL Server-у, а вот третий пункт таит в себе массу подводных камней, которые могут значительно усложнить жизнь "среднестатистическому разработчику":

• стоп-список: слова, входящие в него, попросту игнорируются и не включаются в индекс (поэтому неудивительно, что найти их тоже не получится)
• разбиение на слова: зависит от выбранного языка (локали), может быть нейтральным (по пробелам/знакам препинания)
• выделение словоформ: зависит от языка, список доступных языков можно узнать из системного представления sys.fulltext_languages (зависит от версии SQL Server, в 2005м нужно было некоторые языки (в том числе и русский) регистрировать вручную). Если выберем "неправильный" язык (например в поле, индексируемом согласно правилам английского языка, будем хранить русский текст), то словоформы работать не будут - все слова будут искаться "как есть" (буквально). Проблемы также возникнут с синонимами и "похожестью" слов.
• фильтрация содержимого: фактически дает возможность проиндексировать документы, хранящиеся в БД (в поле типа image/varbinary(max)). Хорошая новость заключается в том, что поддерживаются все форматы, которые "понимает" Windows, на которой выполняется SQL Server - нужно лишь задать специальное поле, в котором для данной строки будет храниться формат потока (фактически расширение файла).

Самая большая проблема, конечно же, заключается в правильной обработке мультилокальных данных (и в нашем глобальном плоском мире с каждым днем все меньше и меньше приложений, который могут обойтись поддержкой одного языка). Альтернатив целых две:

1. Хранить данные для всех языков в одних и тех же полях вперемешку - проще всего, но теряется значительная часть функциональности
2. Для каждого языка создавать отдельное поле/поля (например Title_EN, Title_RU и т.д.) - функциональность остается при нас, но придется довольно много "плясать" вокруг этих данных (в том числе и в приложении - чтобы правильно сохранить/загрузить)

Что бы мы ни выбрали в конечном итоге, за языковыми настройками индексации каждого поля (как и за collation ;-) ) надо следить.

Поддержка/сопровождение

Здесь хорошая новость заключается в том, что полнотекстовые каталоги являются частью базы данные и, как следствие, входят в состав резервной копии/восстанавливаются. Более сложным является вопрос поддержания полнотекстовых индексов в актуальном состоянии. Здесь есть два основных варианта:

• автоматическое наполнение/обновление (CHANGE_TRACKING AUTO): сервер сам будет следить за актуальностью индексов - хорошее решение со многих точек зрения, но могут возникнуть некоторые неожиданные побочные эффекты, связанные с тем, что к нашей базе данных будет обращаться еще один (системный) процесс, накладывающий некоторое количество блокировок
• наполнение/синхронизация индексов по запросу (CHANGE_TRACKING MANUAL): больше ответственности, но и больше уверенности в том что и когда мы делаем - может потребоваться в основном при большой загрузке, когда мы хотим максимально разгрузить сервер БД в течении рабочего времени и готовы смириться с некоторой степенью неактуальности информации в индексах

Довольно сложно дела обстоят и с мониторингом/оптимизацией производительности. Microsoft не разглашает внутреннюю структуру хранения полнотекстовых индексов поэтому на них не распространяется опыт оптимизации "обычных" индексов. Единственное, что можно сказать наверняка: чем больше индексов (в том числе и полнотекстовых) "навернуто" на таблицу, тем больше времени будут занимать операции добавления/обновления/удаления данных.

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

Выводы делаем сами (впрочем, я думаю и так понятно, что бесплатного в этом мире ничего не бывает).

Альтернативы

Если не рассматривать в качестве альтернативы переход на другую СУБД (в PostgreSQL, Oracle и MySql есть аналогичные технологии), то остается применение неких внешних по отношению к нашему приложению сервисов, производящих индексацию содержимого. Тут можно упомянуть:

• Windows Search - встроен в Windows (следовательно "бесплатен"), позволяет индексировать файлы, хранящиеся на компьютере
• Google Desktop - аналогичный продукт от Google
• Apache Lucene / Solr - индексатор / поисковый сервер от Apache Foundation

Первые два продукта скорее ориентированы на индексацию файлов и подойдут скорее в том случае, если перед нами поставлена задача индексировать какие-то документы (довольно широкая тема), в то время как последний - именно внешний индекс, который может "втянуть" почти все, что угодно, физически представляет из себя веб-службу, поддерживает уйму разного функционала, распределенные индексы, очень гибко настраивается и много чего еще. Об этом чудо-средстве я планирую написать отдельный пост (а может быть и не один).

 

HTH

AlexS

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

Данным постом я начинаю серию статей о полнотекстовом поиске. Для начала определимся с тем, что это такое и зачем это нужно.

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

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

Поиск с помощью регулярных выражений - более сложный (и функциональный) вариант поиска подстроки в строке. Регулярное выражение задает правило, по которому формируется набор строк, которые в свою очередь ищутся в строках/документах. В общем случае требует гораздо больше ресурсов, чем простой поиск подстроки в строке.

"Полнотекстовый поиск" - технология, призванная увеличить объемы обрабатываемой информации, скорость поиска и функциональность. Так, например, используя первые два способа, достаточно сложно найти все строки/документы, содеражащие некоторое слово (с учетом весех возможных словофор) - требуется либо несколько запросов, либо достаточно сложное регулярное выражение (да и то не всегда это помогает). В ситуации когда мы хотим найти несколько слов (которые могут идти не подряд), ситуация еще больше усложняется.

Итак, основная особенность полнотекстового поиска заключается в том, что мы ищем не "строгую последовательность символов", а слова (со всеми словоформами). Большинство продуктов, реализующих эту технологию, также позволяют искать "слова, похожие на заданное", использовать булевые операторы и т.п. Кроме того, все коммерческие реализации ищут не по самим строкам/документам, а по индексу, построенному для них.

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

1. Разбить текст на слова (более точно: лексемы) - за это отвечает лексический аналазитор (tokenizer).
2. Произвести фильтрацию полученных слов (пропустить некоторые из них (стоп-список), обработать одинаковые и т.п.) - за это отвечает (необязательный) фильтр лексем (token filter).
3. Найти основу каждого слова (стем) - за это отвечает стеммер (stemmer).

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

В ходе поиска запрос обрабатывается по-тому же алгоритму, что и исходные строки/документы (хотя возможны и отличия, например на стадии фильтрации). Далее, фактически, мы ищем в индексе те строки/документы, которые содержат стемы, полученные в рзультате обработки поискового запроса (с учетом булевых операторов). Кроме этого, поиск может быть сдела более интеллектуальным: поиск слов, похожих, на заданное, поиск документов, в которых заданные слова встречаются рядом, и т.п. - здесь все зависит от функциональности составленного нами индекса.

Далее я планирую остановиться на двух реализциях данной технологии: MS SQL Server Full Text Search и Apache Solr.

HTH

AlexS

Для быстрой страницы шепчите на ушко богам SQL-я

Давно собирался изложить, да вот все никак руки не доходили. А тут совсем меня тоска зеленая загрызла (по былым денькам) - понял, что сегодня или никогда.

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

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

Об объектной модели и технологиях реализации я сейчас говорить не буду, а сконцентрируюсь на уровне доступа к данным. Не будем заморачиваться также на способ получения данных: хранимые процедуры (что врядли) или динамическая генерация SQL-кода (рекомендую, хотя тут тоже не все так просто). Пусть у нас уже есть костяк запроса и мы хотим чтобы он работал побыстрее.

Итак, чтобы быть более конкретным, возьмем две таблицы (простейший случай):

CREATE TABLE [dbo].[CityData](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [CityName] [varchar](50) NULL,
    [Province] [varchar](50) NULL,
    [ZipCode] [varchar](10) NULL);

CREATE TABLE [dbo].[PersonData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CityId] [int] NULL,
    [FullName] [varchar](255) NULL,
    [ZipCode] [varchar](10) NULL,
    [Email] [varchar](255) NULL,
    [ContactPhone] [varchar](100) NULL);

Отображать будем данные о людях (PersonData) вместе с информацией о городе:

select *
from
(
    select
        p.Id, p.FullName, c.CityName,
        ROW_NUMBER() OVER (ORDER BY p.FullName) as RowNumber
    from
        PersonData p
        left join CityData c on c.id = p.CityId
) q
where q.RowNumber between @StartRow and @EndRow

Все хорошо, НО: зачем присоединять данные о городах ко всем записям из таблицы PersonData в то время, как нам нужно получить всего лишь (@EndRow - @StartRow) записей? Правильно, севершенно незачем. Поэтому можем сделать так:

select
    q.*,
    c.CityName
from
(
    select
        p.Id, p.FullName, p.CityId,
        ROW_NUMBER() OVER (ORDER BY p.FullName) as RowNumber
    from
        PersonData p
) q
left join CityData c on c.id = q.CityId
where q.RowNumber between @StartRow and @EndRow

Что изменилось? Пришлось явно выбирать p.CityId (чтобы потом можно было присоединить данные о городах) и ... в общем-то все. Каков результат? Вот что говорит нам план выполнения запроса:

• таблица CityData в первом случае присоединяется ДО того, как происходит фильтрация данных для страницы, а во-втором ПОСЛЕ
• стоимость выполнения второго запроса в два раза меньше (это на нашем простом примере, где в PersonData 100 записей, а в CityData - 50)
• статистика ввода/вывода тоже вдохновляет: удалось почти вдвое сократить количество логических чтений на обработке таблицы CityData

Примерно такая же картина наблюдается в MySQL и PostgreSQL (только план запроса не так наглядно выглядит).

Несколько замечание касательно этой техники:

1. Чем больше таблиц нужно присоединять и чем больше у нас данных - тем значительнее выигрыш (хотя зависимость нелинейная)
2. Те таблицы, которые связаны с главной таблицей через inner join, должны всегда быть во внутреннем запросе, а вот те, которые через left join можно смело выносить наружу
3. Та таблица, по полю которой нужно сортировать результат, должна быть во внутреннем запросе

HTH

Использование и настройка псевдонимов (server alias)

Сегодня почти весь день (с перерывами на работу) пришлось заниматься "разруливанием проблем" с приложением на стороне заказчика. Один из сервисов все никак не хотел подключаться к SQL Server-у. Проблема усугублялась тем, что у нашей команды нет доступа к целевому окружению (вообще никакого доступа). Есть доступ только к индусам, которые в свою очередь имеют доступ к этому самому окружению. Причем "интерфейс" к индусам - электронная почта и Skype. После многочисленных советов "разобраться в конфигурации" и столь же многочисленных ответов "все точно так же, как и на стэйджинге", причина таки нашлась: на "проблемном" сервере не был сконфигурирован псевдоним (alias).

Мне сразу по приходу в этот проект не понравилось использование этой "фичи" - и вот опасения материализовались (правда это первый раз когда все было так серьезно). Не могу не признать, что в определенной степени alias - это удобно. Он, например, дает свободу в выборе сервера: можно переключиться с "общественного" на локальный меняя не строку подключения, а настройки псевдонима - т.е. действуя снаружи приложения. Причем псевдоним действует в рамках комьютера, т.е. (потенциально) можно влиять сразу на целый пакет приложений и сервисов. Побочный эффект: пока не посмотришь в настройки - не узнаешь куда же приложение "ходит" за данными. В целом я бы, наверное, избегал использования этой возможности.

Возник, однако, другой вопрос. В случае, когда на компьютере установлен SQL Server Client Tools (на машине разработчика есть с вероятностью 99%) все просто: запускаем SQL Server Configuration Manager, идем в раздел SQL Native Client Configuration, далее Aliases. А если этого пакета нет (на сервере - не редкость)? Как быть? Вариантов несколько:

• "для слабаков": установить SQL Server Client Tools и получить SQL Server Configuration Manager
• "для простых админов": в комплекте с клиентскими библиотеками SQL Server идет утилита cliconfg.exe (находится в папке Windows\systems32 так что можно вызывать из командной строки), в которой можно сконфигурировать не только псевдоним, но и много чего еще
• "для хардкорных админов": воспользоваться Power Shell и SMO (используя объект Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias) - правда для этого придется установить на компьютер PowerShell (в Server 2008 и новее есть всегда) и собственно SMO

Рекомендуемый вариант - "для админов". Или "для хардкорных админов" (возможно с вариациями) - если нужно чтобы настройка происходила автоматически и/или была повторяемой.

За деталями отсылаю к MSDN и документации по PowerShell. А еще рекомендую блог SQL Protocols - такой подробной информации о протоколах SQL Server и решении проблем с соединением вы больше нигде не найдете.

HTH

Об опциях соединения вообще и об IDENTITY_INSERT в частности

Сегодня пришлось "играться" с IDENTITY_INSERT. С удивлением обнаружил прочувствовал что это таки "установка уровня соединения". Не скажу, что это прямо так удивительно, в MSDN об этом явно сказано:

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server 2000/2005/2008 returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

но как-то до сих пор я не особенно обращал внимание на это примечание.

Ну да ладно. Сама ситуация в очередной раз напомнила мне о том, что опции соединения - это вам не просто так и что за ними нужно следить. Главная проблема (с точки зрения программиста) с ними заключается даже не в том, как они инициализируются (это отдельная непростая тема), а в том, что они не сбрасываются при закритии соединения (точнее: при возвращении соединения в пул).

Для того, чтобы постигнуть "всю глубину наших глубин", рассмотрим такой сценарий. Мы выполняем некоторый SQL код и хотим повысить уровень его изоляции:

using(SqlConnection = new SqlConnection())

{

con.Open();

SqlCOmmand com = new SqlCommand("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- очень важный SQL код", con);

com.ExecuteNonQuery();

}

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

Граждане! Будьте бдительны применяя опции соединения (особенно уровень изоляции).

HTH

Мой первый тренинг в качестве MCT

Легкий оффтоп.

Сегодня закончился мой первый тренинг в качестве сертифицированного тренера Microsoft.

Группа была не слишком многочисленная, но бодрая - держали меня в тонусе и постоянно задавали вопросы "вширь и вглубь". Отчасти именно из-за моего неумения вовремя остановить свои пояснения приходилось жертвовать практикой, отдавая ее на откуп самостоятельной (считай домашней) работе.

На собственном опыте убедился, что подобный тренинг и преподавание в ВУЗе - это далеко не одно и тоже. Во-первых трениг проходит интенсивнее, чем обычные занятия, во-вторых очень здорово помогает наличие учебных материалов. Ну и кроме того, методика подачи материала совсем другая, нет разделения на лекции/практику, тут "все в одном": рассказал - показал. В общем - новый опыт.

Надеюсь первый блин все-таки не вышел комом.

Организация доступа к данным: как сделать шоколадно-конфетное приложение

Дано: база данных, разработчик. Разработчик разрабатывает приложение. Приложение работает с данными, хранящимися в ... базе данных - отображает, редактирует, сохраняет.

Цель: разработчик хочет сделать из приложения "конфетку" - чтобы и хорошо, и красиво, и производительно, и чтобы много пользователей, и умеренные требования к "железу".

Сразу ограничу область "приложения" - не слишком большое (не больше 2х - 3х слоев, ибо бизнес-логика присутствует), но и не слишком маленькое (т.е. о масштабировании думать уже нужно, и вариант "меня спасут помощники в Visual Studio" не пройдет). Также договоримся, что большая часть тех данных, с которыми работает приложение хранится именно в реляционной СУБД - т.е. для доступа к ним используется SQL (в той или иной форме).

Несколько лет назад (5-6) в подобных случаях советовалось не хранить SQL в коде приложения, а выносить его в хранимые процедуры. Отлично. Результатом применения такого подхода является умопомрачительное количество этих самых процедур - по одной накаждую из CRUD -операций плюс еще что-то на бизнес-логику и логику отображения данных (как раз сейчас работаю с одним legacy приложением, в котором на 130 таблиц более 550 хранмых процедур). Такое положение дел не очень хорошо по целому ряду причин:

• сложно поддерживать
• сложно расширять
• сложно обновлять
• по производительности тривиальные хранимые процедуры, реализующие CRUD-операции, хуже динамически сгенерированного SQL-кода (особенно это касается операций обновления данных)

Новое время дает нам новые инструменты. На замену хранимым процедурам пришли ORM-библиотеки. Вроде бы все стало хорошо, мило и приятно - теперь уже можно вообще не писать SQL код. Но не все так просто. Да, с CRUD-операциями ORM справляется хорошо, но вот бизнес-логика зачастую вполне ощутимо "подтормаживает", т.к. требует извлечения довольно большого количества сущностей из БД. Если мы хотим создать приложение, которое будет "быдро пошевеливаться", то полностью отказываться от хранимых процедур нам нельзя - проверку сложной бизнес-логики, работающей на множестве объектов, из которых состоит/которыми оперирует наша система, лучше держать поближе к хранилищу этих объектов, т.е. в БД.

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

1. необходимо обеспечить сортировку по широкому спектру атрибутов (что делает невозможным применение хранимых процедур для получения данных)
2. отображаемые данных "собираются" из нескольких сущностей (что ставит под сомнение эффективность применения ORM)
3. необходимо обеспечить разбивку на страницы (что вкупе с первыми двумя пунктами "напрягает" и хранимые процедуры и ORM-средства)
4. наличие функциональности по фильтрации данных (так что либо мы изощренно издеваемся над ORM, генерирую хитрые условия, либо динамически генерируем SQL-код в хранимых процедурах)
5. в большинстве случаев данные, отображаемые в списке, не нужно редактировать (для этого есть отдельная страница)

По моему опыту, именно такие запросы сильнее всего загружают сервер баз данных. Можно, конечно, решить все перечисленные выше проблемы "методом лома" - грубой силой заставив выбранную технологию (хранимые процедуры или ORM) делать то, что требуется. Но тогда наверняка пострадает гибкость и производительность решения. А мы хотим "конфетку". В итоге мы приходим к необходимости еще одного нишевого решения - динамической генерации SQL для отображения списков (list view). Положительный эффект на уровне БД при этом может достигать 50 - 60% (по сравнению с вариантом "в лоб").

Итак, с моей точки зрения, сбаллансированный подход к организации доступа к данных (СУБД) заключается в следующем:

• слой бизнес-логики (aka domain model) с применением ORM
• бизнес-логика проверяется на сервере БД и (по крайнем мере часть ее) материализуется в виде хранимых процедур
• для отображения списков используется динамическая генерация SQL-кода, которая позволяет получить эффективный (SQL-)код и уменьшить нагрузку на сервер БД