Проблеми с резултатите от търсенето и производителността

Един от типичните сценарии във всички приложения, с които сме запознати, е търсенето на данни по определени критерии и показването им в лесен за четене вид. Може да има и допълнителни опции за сортиране, групиране и страниране. Задачата на теория е тривиална, но при решаването й много разработчици правят редица грешки, които по-късно причиняват страдание на производителността. Нека се опитаме да разгледаме различни варианти за решаване на този проблем и да формулираме препоръки за избор на най-ефективното изпълнение.

Опция за пейджинг #1

Най-простият вариант, който идва на ум, е показване страница по страница на резултатите от търсенето в най-класическата им форма.

Да приемем, че вашето приложение използва релационна база данни. В този случай, за да покажете информация в този формуляр, ще трябва да изпълните две SQL заявки:

• Вземете редове за текущата страница.
• Изчислете общия брой редове, съответстващи на критериите за търсене - това е необходимо за показване на страници.

Нека да разгледаме първата заявка, използвайки тестова MS SQL база данни като пример AdventureWorks за 2016 сървър. За целта ще използваме таблицата Sales.SalesOrderHeader:

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Горната заявка ще върне първите 50 поръчки от списъка, сортирани по низходяща дата на добавяне, с други думи, 50-те най-нови поръчки.

Работи бързо на тестовата база, но нека да разгледаме плана за изпълнение и I/O статистиката:

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Можете да получите I/O статистика за всяка заявка, като изпълните командата SET STATISTICS IO ON в средата на изпълнение на заявката.

Както можете да видите от плана за изпълнение, най-ресурсоемката опция е да сортирате всички редове на изходната таблица по дата на добавяне. И проблемът е, че колкото повече редове се появяват в таблицата, толкова „по-трудно“ ще бъде сортирането. На практика подобни ситуации трябва да се избягват, така че нека добавим индекс към датата на добавяне и да видим дали потреблението на ресурси се е променило:

Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Очевидно е станало много по-добре. Но дали всички проблеми са решени? Нека променим заявката, за да търсим поръчки, при които общата цена на стоките надвишава $100:

SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Имаме забавна ситуация: планът на заявката не е много по-лош от предишния, но действителният брой логически четения е почти два пъти по-голям, отколкото при пълно сканиране на таблица. Има изход - ако направим съставен индекс от вече съществуващ индекс и добавим общата цена на стоките като второ поле, отново ще получим 165 логически прочитания:

CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);

Тази поредица от примери може да продължи дълго, но двете основни мисли, които искам да изразя тук са:

• Добавянето на нов критерий или ред на сортиране към заявка за търсене може да окаже значително влияние върху скоростта на заявката за търсене.
• Но ако трябва да извадим само част от данните, а не всички резултати, които съответстват на думите за търсене, има много начини за оптимизиране на такава заявка.

Сега нека да преминем към втората заявка, спомената в самото начало - тази, която отчита броя на записите, които отговарят на критерия за търсене. Да вземем същия пример - търсене на поръчки, които са над $100:

SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100

Като се има предвид съставният индекс, посочен по-горе, получаваме:

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Фактът, че заявката преминава през целия индекс, не е изненадващ, тъй като полето SubTotal не е на първа позиция, така че заявката не може да го използва. Проблемът се решава чрез добавяне на още един индекс към полето SubTotal и в резултат на това дава само 48 логически четения.

Можете да дадете още няколко примера за заявки за преброяване на количества, но същността остава същата: получаването на част от данните и преброяването на общата сума са две фундаментално различни заявки, и всеки изисква свои собствени мерки за оптимизация. Като цяло няма да можете да намерите комбинация от индекси, която работи еднакво добре и за двете заявки.

Съответно, едно от важните изисквания, които трябва да бъдат изяснени при разработването на подобно решение за търсене, е дали наистина е важно за бизнеса да вижда общия брой намерени обекти. Често се случва не. А навигацията по конкретни номера на страници според мен е решение с много тесен обхват, тъй като повечето сценарии за страниране изглеждат като „отидете на следващата страница“.

Опция за пейджинг #2

Да приемем, че потребителите не се интересуват от знанието на общия брой намерени обекти. Нека се опитаме да опростим страницата за търсене:

Всъщност единственото нещо, което се промени е, че няма начин за навигиране до конкретни номера на страници и сега тази таблица не трябва да знае колко може да има, за да я покаже. Но възниква въпросът - как таблицата знае дали има данни за следващата страница (за да покаже правилно връзката „Напред“)?

Отговорът е много прост: можете да прочетете от базата данни още един запис, отколкото е необходимо за показване, и наличието на този „допълнителен“ запис ще покаже дали има следваща част. По този начин трябва да изпълните само една заявка, за да получите една страница с данни, което значително подобрява производителността и улеснява поддържането на такава функционалност. В моята практика имаше случай, когато отказът да се преброи общият брой записи ускори предоставянето на резултати 4-5 пъти.

Има няколко опции на потребителския интерфейс за този подход: команди „назад“ и „напред“, както в примера по-горе, бутон „зареди още“, който просто добавя нова част към показаните резултати, „безкрайно превъртане“, което работи на принципа „заредете още“, но сигналът за получаване на следващата порция е потребителят да превърти всички показани резултати до края. Каквото и да е визуалното решение, принципът на вземане на проби от данни остава същият.

Нюанси на внедряване на страниране

Всички примери за заявки, дадени по-горе, използват подхода „отместване + брой“, когато самата заявка указва в кой ред редовете с резултати и колко реда трябва да бъдат върнати. Първо, нека да разгледаме как най-добре да организираме предаването на параметри в този случай. На практика съм срещал няколко метода:

• Серийният номер на заявената страница (pageIndex), размер на страницата (pageSize).
• Серийният номер на първия запис, който ще бъде върнат (startIndex), максималния брой записи в резултата (брой).
• Поредният номер на първия запис, който ще бъде върнат (startIndex), поредният номер на последния запис, който ще бъде върнат (endIndex).

На пръв поглед може да изглежда, че това е толкова елементарно, че няма разлика. Но това не е така - най-удобният и универсален вариант е вторият (startIndex, count). Има няколко причини за това:

• За подхода за коригиране на запис +1, даден по-горе, първата опция с pageIndex и pageSize е изключително неудобна. Например, искаме да показваме 50 публикации на страница. Според горния алгоритъм трябва да прочетете още един запис от необходимото. Ако този “+1” не е внедрен на сървъра, се оказва, че за първата страница трябва да поискаме записи от 1 до 51, за втората - от 51 до 101 и т.н. Ако зададете размер на страницата 51 и увеличите pageIndex, тогава втората страница ще се върне от 52 на 102 и т.н. Съответно, в първия вариант, единственият начин да се приложи правилно бутон за преминаване към следващата страница е сървърът да коригира „допълнителния“ ред, което ще бъде много имплицитен нюанс.
• Третият вариант изобщо няма смисъл, тъй като за да изпълнявате заявки в повечето бази данни, пак ще трябва да подадете броя, а не индекса на последния запис. Изваждането на startIndex от endIndex може да е проста аритметична операция, но тук е излишно.

Сега трябва да опишем недостатъците на внедряването на страниране чрез „отместване + количество“:

• Извличането на всяка следваща страница ще бъде по-скъпо и по-бавно от предишната, тъй като базата данни ще трябва да премине през всички записи „от самото начало“ според критериите за търсене и сортиране и след това да спре на желания фрагмент.
• Не всички СУБД могат да поддържат този подход.

Има алтернативи, но те също са несъвършени. Първият от тези подходи се нарича „странициране с ключове“ или „метод на търсене“ и е както следва: след получаване на част можете да запомните стойностите на полето в последния запис на страницата и след това да ги използвате, за да получите следващата порция. Например изпълнихме следната заявка:

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

И в последния запис получихме стойността на датата на поръчката „2014-06-29“. След това, за да получите следващата страница, можете да опитате да направите това:

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Проблемът е, че OrderDate е неуникално поле и посоченото по-горе условие вероятно ще пропусне много задължителни редове. За да добавите недвусмисленост към тази заявка, трябва да добавите уникално поле към условието (да приемем, че 75074 е последната стойност на първичния ключ от първата част):

SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
   OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Тази опция ще работи правилно, но като цяло ще бъде трудна за оптимизиране, тъй като условието съдържа оператор ИЛИ. Ако стойността на първичния ключ се увеличава с нарастването на OrderDate, тогава условието може да се опрости, като се остави само филтър по SalesOrderID. Но ако няма стриктна корелация между стойностите на първичния ключ и полето, по което се сортира резултатът, това ИЛИ не може да бъде избегнато в повечето СУБД. Изключение, за което знам, е PostgreSQL, който напълно поддържа сравнения на кортежи и горното условие може да бъде записано като "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Като се има предвид съставен ключ с тези две полета, заявка като тази би трябвало да е доста лесна.

Втори алтернативен подход може да се намери, например, в API за превъртане на ElasticSearch или Космос DB — когато заявката, в допълнение към данните, връща специален идентификатор, с който можете да получите следващата част от данните. Ако този идентификатор има неограничен живот (както в Comsos DB), тогава това е чудесен начин за прилагане на страниране с последователен преход между страниците (опция №2, спомената по-горе). Възможните му недостатъци: не се поддържа във всички СУБД; полученият идентификатор на следващата част може да има ограничен живот, което обикновено не е подходящо за прилагане на потребителско взаимодействие (като API за превъртане на ElasticSearch).

Комплексно филтриране

Нека усложним още повече задачата. Да предположим, че има изискване за прилагане на така нареченото фасетно търсене, което е много познато на всички от онлайн магазините. Горните примери, базирани на таблицата с поръчките, не са много илюстративни в този случай, така че нека преминем към таблицата с продукти от базата данни на AdventureWorks:

Каква е идеята зад фасетното търсене? Факт е, че за всеки филтърен елемент се показва броят на записите, които отговарят на този критерий като се вземат предвид филтрите, избрани във всички останали категории.

Например, ако изберем категорията Велосипеди и цвета Черен в този пример, таблицата ще показва само черни велосипеди, но:

• За всеки критерий в групата Категории броят на продуктите от тази категория ще бъде показан в черно.
• За всеки критерий от групата „Цветове“ ще бъде показан броят на велосипедите от този цвят.

Ето пример за резултата за такива условия:

Ако проверите и категорията „Дрехи“, таблицата ще покаже и черни дрехи, които са на склад. Броят на черните продукти в секцията „Цвят“ също ще бъде преизчислен според новите условия, само в секцията „Категории“ нищо няма да се промени... Надявам се, че тези примери са достатъчни, за да разберете обичайния алгоритъм за фасетно търсене.

Сега нека си представим как това може да се приложи на релационна основа. Всяка група критерии, като категория и цвят, ще изисква отделна заявка:

SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
  INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC

SELECT Color, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC

Какво не е наред с това решение? Много е просто - не се мащабира добре. Всеки филтърен раздел изисква отделна заявка за изчисляване на количествата и тези заявки не са от най-лесните. В онлайн магазините някои категории може да имат няколко десетки филтърни секции, което може да бъде сериозен проблем с производителността.

Обикновено след тези твърдения ми се предлагат някои решения, а именно:

• Комбинирайте всички количества в една заявка. Технически това е възможно с помощта на ключовата дума UNION, но няма да помогне много за производителността - базата данни ще трябва да изпълни всеки от фрагментите от нулата.
• Кеш количества. Това ми се предлага почти всеки път, когато описвам проблем. Уговорката е, че това по принцип е невъзможно. Да кажем, че имаме 10 "фасети", всяка от които има 5 стойности. Това е много „скромна“ ситуация в сравнение с това, което може да се види в същите онлайн магазини. Изборът на един аспектен елемент влияе върху количествата в други 9, с други думи, за всяка комбинация от критерии количествата могат да бъдат различни. В нашия пример има общо 50 критерия, които потребителят може да избере, така че ще има 250 възможни комбинации.Няма достатъчно памет или време за попълване на такъв масив от данни. Тук можете да възразите и да кажете, че не всички комбинации са реални и потребителят рядко избира повече от 5-10 критерия. Да, възможно е да се направи отложено зареждане и да се кешира количество само от това, което някога е било избрано, но колкото повече селекции има, толкова по-малко ефективен ще бъде такъв кеш и толкова по-забележими ще бъдат проблемите с времето за реакция (особено ако наборът от данни се променя редовно).

За щастие подобен проблем отдавна има доста ефективни решения, които работят предсказуемо върху големи обеми данни. За която и да е от тези опции има смисъл да разделите преизчисляването на фасетите и получаването на страницата с резултати на две паралелни извиквания към сървъра и да организирате потребителския интерфейс по такъв начин, че зареждането на данни по фасети „не пречи“ на показването на Резултати от търсенето.

• Обадете се на пълно преизчисляване на „фасети“ възможно най-рядко. Например, не преизчислявайте всичко всеки път, когато критериите за търсене се променят, а вместо това намерете общия брой резултати, които отговарят на текущите условия, и подканете потребителя да ги покаже - „Намерени са 1425 записа, показват ли се?“ Потребителят може или да продължи да променя думите за търсене, или да натисне бутона „покажи“. Само във втория случай ще бъдат изпълнени всички заявки за получаване на резултати и преизчисляване на количества по всички „фасети“. В този случай, както лесно можете да видите, ще трябва да се справите със заявка за получаване на общия брой резултати и неговата оптимизация. Този метод може да се намери в много малки онлайн магазини. Очевидно това не е панацея за този проблем, но в прости случаи може да бъде добър компромис.
• Използвайте търсачки за намиране на резултати и преброяване на аспекти, като Solr, ElasticSearch, Sphinx и други. Всички те са проектирани да изграждат „фасети“ и правят това доста ефективно поради обърнатия индекс. Как работят търсачките, защо в такива случаи те са по-ефективни от базите данни с общо предназначение, какви практики и клопки има - това е тема за отделна статия. Тук бих искал да обърна внимание на факта, че търсачката не може да бъде заместител на основното хранилище на данни, тя се използва като допълнение: всички промени в основната база данни, които са от значение за търсенето, се синхронизират в индекса за търсене; Търсачката обикновено взаимодейства само с търсачката и няма достъп до основната база данни. Един от най-важните моменти тук е как да организирате надеждно тази синхронизация. Всичко зависи от изискванията за „време за реакция“. Ако времето между промяната в основната база данни и нейното „проявление“ в търсенето не е критично, можете да създадете услуга, която търси наскоро променени записи на всеки няколко минути и ги индексира. Ако искате възможно най-краткото време за реакция, можете да приложите нещо подобно транзакционна изходяща кутия за изпращане на актуализации до услугата за търсене.

Данни

1. Внедряването на страниране от страна на сървъра е значително усложнение и има смисъл само за бързо развиващи се или просто големи набори от данни. Няма абсолютно точна рецепта как да оценим „голям“ или „бързо развиващ се“, но аз бих следвал следния подход:
   • Ако получаването на пълна колекция от данни, като се вземе предвид времето на сървъра и мрежовото предаване, отговаря нормално на изискванията за производителност, няма смисъл да се прилага страниране от страната на сървъра.
   • Може да има ситуация, при която не се очакват проблеми с производителността в близко бъдеще, тъй като има малко данни, но събирането на данни непрекъснато нараства. Ако някой набор от данни в бъдеще може вече да не отговаря на предходната точка, по-добре е да започнете пейджинг веднага.
2. Ако няма строго изискване от страна на бизнеса да показва общия брой резултати или да показва номера на страници и вашата система няма търсачка, по-добре е да не прилагате тези точки и да разгледате вариант №2.
3. Ако има ясно изискване за фасетно търсене, имате две възможности, без да жертвате производителността:
   • Не преизчислявайте всички количества при всяка промяна на критериите за търсене.
   • Използвайте търсачки като Solr, ElasticSearch, Sphinx и други. Но трябва да се разбере, че тя не може да бъде заместител на основната база данни и трябва да се използва като допълнение към основното хранилище за решаване на проблеми с търсенето.
4. Също така, в случай на фасетно търсене, има смисъл да се раздели извличането на страницата с резултати от търсенето и преброяването на две паралелни заявки. Преброяването на количествата може да отнеме повече време от получаването на резултати, докато резултатите са по-важни за потребителя.
5. Ако използвате SQL база данни за търсене, всяка промяна на кода, свързана с тази част, трябва да бъде добре тествана за ефективност върху подходящото количество данни (надвишаващо обема в активната база данни). Също така е препоръчително да се използва мониторинг на времето за изпълнение на заявката на всички екземпляри на базата данни и особено на „живата“. Дори ако всичко беше наред с плановете за заявки на етапа на разработка, с нарастването на обема на данните ситуацията може да се промени значително.

Източник: www.habr.com