2 лайфхака: альтернативы классическому поиску в microsoft sql server

В чём преимущества

Базы дан­ных и их систе­мы управ­ле­ния зато­че­ны на рабо­ту с боль­шим объ­ё­мом дан­ных и от лица боль­шо­го чис­ла поль­зо­ва­те­лей. Сей­час вы пой­мё­те.

Ско­рость — ещё одно пре­иму­ще­ство базы дан­ных. База дан­ных устро­е­на так, что она лег­ко и быст­ро нахо­дит, запи­сы­ва­ет, пере­пи­сы­ва­ет и сно­ва нахо­дит дан­ные. Всё пото­му, что СУБД все­гда зна­ет, что где лежит и по како­му кри­те­рию искать. Там не будет слу­чай­ных дан­ных в слу­чай­ном месте.

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

Слож­ность. Базы дан­ных нуж­ны в чис­ле про­че­го для хра­не­ния слож­но струк­ту­ри­ро­ван­ных дан­ных. Мы при­вык­ли думать, что база дан­ных — это такая таб­ли­ца, где есть стро­ки и столб­цы. Но база дан­ных при пра­виль­ной орга­ни­за­ции может намно­го боль­ше:

• Свя­зы­вать одну еди­ни­цу дан­ных с мно­же­ством дру­гих. Напри­мер, если один чело­век совер­шил мно­го зака­зов со мно­же­ством това­ров внут­ри каж­до­го, база дан­ных спо­соб­на хра­нить и обра­ба­ты­вать такие свя­зи.
• База может хра­нить дере­во дан­ных — вро­де того, о кото­ром мы писа­ли недав­но. Попро­буй в реаль­ной жиз­ни похра­нить дере­во!
• В базах могут жить ссыл­ки на дру­гие фраг­мен­ты и отде­лы базы.

Базу мож­но пред­ста­вить как таб­ли­цу, но лишь в самом упро­щён­ном виде. Для более слож­ных задач базу мож­но пред­ста­вить как очень слож­ное дере­во, или огром­ный склад упо­ря­до­чен­ных коро­бок, или даже как огром­ный завод по фасов­ке дан­ных.

Разные базы — разные правила

Внут­ри каж­дой базы дан­ных и её управ­ля­ю­щей систе­мы свои стро­гие пра­ви­ла:

• какие дан­ные могут хра­нить­ся: текст, циф­ры, фото, видео или всё вме­сте;
• какие свой­ства есть у этих дан­ных: дата запи­си, кто запи­сал, кто может про­чи­тать;
• что делать, если с базой хотят рабо­тать одно­вре­мен­но несколь­ко чело­век: раз­ре­шать толь­ко одно­му или пусть все вме­сте рабо­та­ют.

Рабо­чая ситу­а­ция: допу­стим, вы рабо­та­е­те в бан­ке и откры­ли кар­точ­ку кли­ен­та, что­бы поме­нять ему кре­дит­ный лимит. В этот же момент дру­гой сотруд­ник из сосед­не­го офи­са тоже хочет поме­нять лимит это­му же кли­ен­ту, но уже на дру­гую сум­му. Как база отре­а­ги­ру­ет на такое? Долж­на ли она раз­ре­шать вто­ро­му сотруд­ни­ку откры­вать кар­точ­ку или её нуж­но забло­ки­ро­вать, пока пер­вый не закон­чит? А если она раз­ре­шит открыть кар­точ­ку, то что будет, если двое сотруд­ни­ков напи­шут там раз­ный лимит — какой из них сохра­нять в ито­ге? СУБД зада­ёт эти пра­ви­ла и сле­дит за их выпол­не­ни­ем.

Реляционные

Реля­ци­он­ные базы дан­ных ещё назы­ва­ют таб­лич­ны­ми, пото­му что все дан­ные в них мож­но пред­ста­вить в виде раз­ных таб­лиц. Одни таб­ли­цы свя­за­ны с дру­ги­ми, а дру­гие — с тре­тьи­ми. Напри­мер, база дан­ных поку­пок в мага­зине может выгля­деть так:

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

Если у поку­па­те­ля поме­ня­ет­ся номер теле­фо­на, то нам доста­точ­но будет поме­нять это в одной таб­ли­це «Кли­ен­ты». Бла­го­да­ря тому, что в «Покуп­ки» запи­сы­ва­ет­ся толь­ко код поку­па­те­ля, нам не нуж­но менять имя боль­ше нигде — дан­ные сами обно­вят­ся авто­ма­ти­че­ски, когда мы захо­тим посмот­реть, кто имен­но купил табу­рет.

Наивно используем полнотекстовый поиск

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

Для поиска в PostgreSQL есть функции , , . Для ранжирования результатов есть . Их использование интуитивно понятно и они хорошо описаны в документации, поэтому на подробностях их использования останавливаться не будем.

Традиционный поисковый запрос с помощью них будет выглядеть так:

Мы вывели id-ы документов, в тексте которых есть слово «запрос», и отсортировали их по убыванию релевантности. Кажется, всё хорошо? Нет.

У подхода выше есть много недостатков:

1. Мы не используем индекс для поиска.
2. Функция ts_vector вызывается для каждой строки таблицы.
3. Функция ts_rank вызывается для каждой строки таблицы.

Это все приводит к тому, что поиск выполняется реально долго. Результаты на боевой базе:

420 секунд! На один запрос!

Ещё база генерит множество ворнингов вида . В этом ничего страшного нет. Причина в том, что в моей базе лежат документы, созданные в WYSIWYG-редакторе. Он вставляет множество всюду, где можно, и их бывает по 54 тысячи штук подряд. Postgres слова такой длины игнорирует и пишет ворнинг, который нельзя отключить.

Попробуем исправить все замеченные проблемы и ускорить поиск.