Проблеми са резултатима претраге и перформансама

Један од типичних сценарија у свим апликацијама које су нам познате је тражење података према одређеним критеријумима и њихово приказивање у лако читљивом облику. Такође могу постојати додатне опције за сортирање, груписање и странице. Задатак је, у теорији, тривијалан, али када га решавају, многи програмери праве велики број грешака, због чега касније пати продуктивност. Покушајмо да размотримо различите опције за решавање овог проблема и формулишемо препоруке за избор најефикасније примене.

Опција пејџинга #1

Најједноставнија опција која вам пада на памет је приказ резултата претраге страница по страницу у најкласичнијем облику.

Рецимо да ваша апликација користи релациону базу података. У овом случају, да бисте приказали информације у овом облику, мораћете да покренете два СКЛ упита:

• Добијте редове за тренутну страницу.
• Израчунајте укупан број редова који одговарају критеријумима претраге - ово је неопходно за приказ страница.

Хајде да погледамо први упит користећи тестну МС СКЛ базу података као пример АдвентуреВоркс за сервер за 2016. За ову сврху користићемо табелу Салес.СалесОрдерХеадер:

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

Горњи упит ће вратити првих 50 поруџбина са листе, сортираних према опадајућем датуму додавања, другим речима, 50 последњих поруџбина.

Брзо ради на бази теста, али хајде да погледамо план извршења и статистику И/О:

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.

Можете да добијете И/О статистику за сваки упит покретањем команде СЕТ СТАТИСТИЦС ИО ОН у окружењу извођења упита.

Као што можете да видите из плана извршења, опција са највише ресурса је сортирање свих редова изворне табеле по датуму додавања. А проблем је у томе што што се више редова појави у табели, сортирање ће бити „теже“. У пракси такве ситуације треба избегавати, па хајде да додамо индекс на датум додавања и видимо да ли се потрошња ресурса променила:

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.

Чињеница да упит пролази кроз цео индекс није изненађујућа, пошто поље СубТотал није на првој позицији, па га упит не може користити. Проблем је решен додавањем другог индекса у поље СубТотал, и као резултат даје само 48 логичких читања.

Можете навести још неколико примера захтева за бројање количина, али суштина остаје иста: примање податка и пребројавање укупног износа су два суштински различита захтева, а свака захтева своје мере за оптимизацију. Генерално, нећете моћи да пронађете комбинацију индекса која подједнако добро функционише за оба упита.

Сходно томе, један од важних захтева који треба разјаснити приликом развоја оваквог решења за претрагу јесте да ли је заиста важно да предузеће види укупан број пронађених објеката. Често се дешава да не. А навигација према одређеним бројевима страница, по мом мишљењу, представља решење са веома уским опсегом, пошто већина сценарија страница изгледа као „иди на следећу страницу“.

Опција пејџинга #2

Претпоставимо да корисницима није стало да знају укупан број пронађених објеката. Покушајмо да поједноставимо страницу за претрагу:

У ствари, једина ствар која се променила је то што не постоји начин за навигацију до одређених бројева страница, а сада ова табела не мора да зна колико их може бити да би је приказала. Али поставља се питање - како табела зна да ли постоје подаци за следећу страницу (како би се исправно приказала веза „Следеће“)?

Одговор је врло једноставан: можете прочитати из базе података један запис више него што је потребно за приказ, а присуство овог „додатног“ записа ће показати да ли постоји следећи део. На овај начин, потребно је да покренете само један захтев да бисте добили једну страницу података, што значајно побољшава перформансе и олакшава подршку такве функционалности. У мојој пракси је био случај када је одбијање да се преброји укупан број записа убрзало испоруку резултата за 4-5 пута.

Постоји неколико опција корисничког интерфејса за овај приступ: команде „назад“ и „напред“, као у горњем примеру, дугме „учитај више“, које једноставно додаје нови део приказаним резултатима, „бесконачно померање“, што функционише по принципу „учитај више““, али сигнал за добијање следеће порције је да корисник скролује све приказане резултате до краја. Без обзира на визуелно решење, принцип узорковања података остаје исти.

Нијансе имплементације страница

Сви горе наведени примери упита користе приступ „оффсет + цоунт“, када сам упит одређује којим редоследом су редови резултата и колико редова треба да се врати. Прво, погледајмо како најбоље организовати преношење параметара у овом случају. У пракси сам наишао на неколико метода:

• Серијски број тражене странице (пагеИндек), величина странице (пагеСизе).
• Серијски број првог записа који треба да се врати (стартИндек), максималан број записа у резултату (број).
• Редни број првог записа који треба да се врати (стартИндек), редни број последњег записа који треба да се врати (ендИндек).

На први поглед може изгледати да је ово толико елементарно да нема разлике. Али то није тако - најпогоднија и универзална опција је друга (стартИндек, цоунт). Постоји неколико разлога за то:

• За приступ лекторисања +1 уноса који је горе наведен, прва опција са индексом странице и величина странице је изузетно незгодна. На пример, желимо да прикажемо 50 постова по страници. Према горе наведеном алгоритму, потребно је да прочитате један запис више него што је потребно. Ако овај „+1“ није имплементиран на серверу, испада да за прву страницу морамо тражити записе од 1 до 51, за другу - од 51 до 101 итд. Ако наведете величину странице од 51 и повећате пагеИндек, онда ће се друга страница вратити са 52 на 102, итд. Сходно томе, у првој опцији, једини начин да се правилно примени дугме за прелазак на следећу страницу је да сервер лекторише „додатни“ ред, што ће бити веома имплицитна нијанса.
• Трећа опција уопште нема смисла, пошто да бисте покренули упите у већини база података, и даље ћете морати да проследите број уместо индекса последњег записа. Одузимање стартИндек-а од ендИндек-а може бити једноставна аритметичка операција, али је овде сувишна.

Сада би требало да опишемо недостатке имплементације страница кроз „оффсет + количина“:

• Преузимање сваке следеће странице биће скупље и спорије од претходне, јер ће база података и даље морати да прође кроз све записе „од почетка“ према критеријумима претраге и сортирања, а затим да се заустави на жељеном фрагменту.
• Не могу сви ДБМС-ови да подрже овај приступ.

Постоје алтернативе, али су и оне несавршене. Први од ових приступа се назива „пагинг по кључевима“ или „метод тражења“ и гласи: након што примите део, можете запамтити вредности поља у последњем запису на страници, а затим их користити за добијање следећи део. На пример, покренули смо следећи упит:

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

Проблем је у томе што ОрдерДате није јединствено поље и горе наведени услов ће вероватно пропустити много обавезних редова. Да бисте додали недвосмисленост овом упиту, потребно је да додате јединствено поље у услов (претпоставимо да је 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

Ова опција ће радити исправно, али генерално ће је бити тешко оптимизовати пошто услов садржи оператор ОР. Ако се вредност примарног кључа повећава како се ОрдерДате повећава, онда се услов може поједноставити остављањем само филтера по СалесОрдерИД-у. Али ако не постоји строга корелација између вредности примарног кључа и поља по коме је резултат сортиран, ово ОР се не може избећи у већини ДБМС-ова. Изузетак који ми је познат је ПостгреСКЛ, који у потпуности подржава поређење торва, а горњи услов се може написати као „ВХЕРЕ (Датум поруџбине, СалесОрдерИД) < (’2014-06-29’, 75074)“. С обзиром на сложени кључ са ова два поља, овакав упит би требао бити прилично лак.

Други алтернативни приступ се може наћи, на пример, у ЕластицСеарцх АПИ за скроловање или Цосмос ДБ — када захтев, поред података, враћа посебан идентификатор помоћу којег можете добити следећи део података. Ако овај идентификатор има неограничен животни век (као у Цомсос ДБ), онда је ово одличан начин за имплементацију страница са секвенцијалним прелазом између страница (опција #2 поменута горе). Његови могући недостаци: није подржан у свим ДБМС-овима; резултујући идентификатор следећег дела може имати ограничен животни век, што генерално није погодно за примену корисничке интеракције (као што је ЕластицСеарцх АПИ за померање).

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

Хајде да додатно закомпликујемо задатак. Претпоставимо да постоји захтев да се примени такозвана фасетирана претрага, која је свима позната из онлајн продавница. Горе наведени примери засновани на табели поруџбина нису баш илустративни у овом случају, па пређимо на табелу производа из базе података АдвентуреВоркс:

Која је идеја иза фасетиране претраге? Чињеница је да је за сваки филтерски елемент приказан број записа који испуњавају овај критеријум узимајући у обзир филтере изабране у свим осталим категоријама.

На пример, ако одаберемо категорију Бицикли и црну боју у овом примеру, табела ће приказати само црне бицикле, али:

• За сваки критеријум у групи Категорије, број производа из те категорије биће приказан црном бојом.
• За сваки критеријум групе „Боје“ биће приказан број бицикала ове боје.

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

Ако проверите и категорију „Одећа“, у табели ће бити приказана и црна одећа која је на залихама. Број црних производа у одељку „Боја“ такође ће бити прерачунат према новим условима, само у одељку „Категорије“ ништа се неће променити... Надам се да су ови примери довољни да разумемо уобичајени алгоритам фасетиране претраге.

Хајде сада да замислимо како се ово може применити на релационој основи. За сваку групу критеријума, као што су категорија и боја, биће потребан посебан упит:

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

Шта није у реду са овим решењем? Врло је једноставно - не скалира се добро. Сваки одељак филтера захтева посебан упит за израчунавање количина, а ови упити нису најлакши. У онлајн продавницама, неке категорије могу имати неколико десетина одељака филтера, што може представљати озбиљан проблем са перформансама.

Обично ми се после ових изјава нуде нека решења, и то:

• Комбинујте све количине у један упит. Технички, ово је могуће коришћењем кључне речи УНИОН, али то неће много помоћи у перформансама – база података ће и даље морати да извршава сваки од фрагмената од нуле.
• Кеш количине. Ово ми се сугерише скоро сваки пут када опишем проблем. Упозорење је да је то генерално немогуће. Рецимо да имамо 10 „аспеката“, од којих свака има 5 вредности. Ово је веома „скромна“ ситуација у поређењу са оним што се може видети у истим онлајн продавницама. Избор једног елемента аспекта утиче на количине у 9 других, другим речима, за сваку комбинацију критеријума количине могу бити различите. У нашем примеру постоји укупно 50 критеријума које корисник може да изабере, па ће бити могућих комбинација 250. Нема довољно меморије или времена да се попуни такав низ података. Овде можете приговорити и рећи да нису све комбинације стварне и да корисник ретко бира више од 5-10 критеријума. Да, могуће је извршити лењо учитавање и кеширати само оно што је икада изабрано, али што је више избора, то ће такав кеш бити мање ефикасан и проблеми са временом одзива ће бити уочљивији (нарочито ако скуп података се редовно мења) .

На срећу, такав проблем већ дуго има прилично ефикасна решења која раде предвидљиво на великим количинама података. За било коју од ових опција, има смисла поделити поновно израчунавање фасета и примање странице са резултатима на два паралелна позива серверу и организовати кориснички интерфејс на такав начин да учитавање података по аспектима „не омета“ приказ Резултати претраге.

• Позовите потпуно поновно израчунавање „фасета“ што је ређе могуће. На пример, немојте све поново израчунавати сваки пут када се критеријуми претраге промене, већ уместо тога пронађите укупан број резултата који одговарају тренутним условима и затражите од корисника да их прикаже – „Пронађено је 1425 записа, прикажи?“ Корисник може или да настави да мења термине за претрагу или да кликне на дугме „прикажи“. Само у другом случају биће извршени сви захтеви за добијање резултата и прерачунавање количина на свим „фасетима“. У овом случају, као што можете лако видети, мораћете да се позабавите захтевом да бисте добили укупан број резултата и његову оптимизацију. Овај метод се може наћи у многим малим онлине продавницама. Очигледно, ово није лек за овај проблем, али у једноставним случајевима може бити добар компромис.
• Користите претраживаче да пронађете резултате и бројите аспекте, као што су Солр, ЕластицСеарцх, Спхинк и други. Сви они су дизајнирани да граде „фасете“ и то раде прилично ефикасно због обрнутог индекса. Како функционишу претраживачи, зашто су у таквим случајевима ефикаснији од база података опште намене, које праксе и замке постоје - ово је тема за посебан чланак. Овде бих желео да вам скренем пажњу на чињеницу да претраживач не може бити замена за главно складиште података, већ се користи као додатак: све промене у главној бази података које су релевантне за претрагу синхронизују се са индексом претраге; Претраживач обично ради само са претраживачем и не приступа главној бази података. Једна од најважнијих тачака овде је како поуздано организовати ову синхронизацију. Све зависи од захтева за „време реакције“. Ако време између промене у главној бази података и њене „манифестације“ у претрази није критично, можете креирати услугу која сваких неколико минута тражи недавно промењене записе и индексира их. Ако желите најкраће могуће време одговора, можете имплементирати нешто попут трансакционо одлазно сандуче да бисте послали ажурирања сервису претраге.

Налази

1. Имплементација страница на страни сервера је значајна компликација и има смисла само за брзорастуће или једноставно велике скупове података. Не постоји апсолутно тачан рецепт за процену „великих“ или „брзо растућих“, али бих следио овај приступ:
   • Ако примање комплетне колекције података, узимајући у обзир време сервера и мрежни пренос, нормално одговара захтевима перформанси, нема смисла имплементирати страничење на страни сервера.
   • Може доћи до ситуације у којој се у блиској будућности не очекују проблеми са перформансама, јер података има мало, али прикупљање података стално расте. Ако неки скуп података у будућности можда више не задовољава претходну тачку, боље је одмах почети са страницама.
2. Ако не постоји строги захтев од стране предузећа да прикаже укупан број резултата или да прикаже бројеве страница, а ваш систем нема претраживач, боље је да не примењујете ове тачке и размотрите опцију #2.
3. Ако постоји јасан захтев за фасетирано претраживање, имате две опције без жртвовања перформанси:
   • Немојте поново израчунавати све количине сваки пут када се критеријуми претраге промене.
   • Користите претраживаче као што су Солр, ЕластицСеарцх, Спхинк и други. Али треба схватити да не може бити замена за главну базу података, већ да се користи као додатак главној меморији за решавање проблема претраге.
4. Такође, у случају фасетиране претраге, има смисла поделити преузимање странице резултата претраге и бројање на два паралелна захтева. Бројање количина може потрајати дуже од добијања резултата, док су резултати важнији за корисника.
5. Ако користите СКЛ базу података за претрагу, свака промена кода која се односи на овај део треба да буде добро тестирана за перформансе на одговарајућој количини података (премашивши обим у живој бази података). Такође је препоручљиво користити праћење времена извршавања упита на свим инстанцама базе података, а посебно на оној „живој“. Чак и ако је све било у реду са плановима упита у фази развоја, како обим података расте, ситуација се може приметно променити.

Извор: ввв.хабр.цом