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

Едно од типичните сценарија во сите апликации што ни се познати е пребарување на податоци според одредени критериуми и нивно прикажување во лесно читлива форма. Може да има и дополнителни опции за подредување, групирање и страничење. Задачата е, теоретски, тривијална, но кога ја решаваат, многу програмери прават голем број грешки, кои подоцна предизвикуваат пад на продуктивноста. Ајде да се обидеме да разгледаме различни опции за решавање на овој проблем и да формулираме препораки за избор на најефективната имплементација.

Опција за страничење број 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-те најнови нарачки.

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

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.

Можете да добиете статистика за влез/излез за секое барање со извршување на командата SET STATISTISTICS 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 пати.

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

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

Сите примери за барање дадени погоре го користат пристапот „offset + count“, кога самото барање одредува по кој редослед резултатите од редовите и колку редови треба да се вратат. Прво, да погледнеме како најдобро да се организира пренесувањето на параметрите во овој случај. Во пракса, наидов на неколку методи:

Серискиот број на бараната страница (pageIndex), големината на страницата (pageSize).
Серискиот број на првиот запис што треба да се врати (startIndex), максималниот број на записи во резултатот (броење).
Секвенцискиот број на првиот запис што треба да се врати (startIndex), секвенцискиот број на последниот запис што треба да се врати (endIndex).

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

За пристапот за лекторирање на влезот +1 даден погоре, првата опција со PageIndex и PageSize е крајно незгодна. На пример, сакаме да прикажеме 50 објави на страница. Според горенаведениот алгоритам, треба да прочитате уште еден запис отколку што е потребно. Ако оваа „+1“ не е имплементирана на серверот, излегува дека за првата страница мора да бараме записи од 1 до 51, за втората - од 51 до 101, итн. Ако наведете големина на страница од 51 и го зголемите индексот на страницата, тогаш втората страница ќе се врати од 52 на 102, итн. Според тоа, во првата опција, единствениот начин правилно да се имплементира копче за да се оди на следната страница е серверот да ја лекторира линијата „дополнителна“, што ќе биде многу имплицитна нијанса.
Третата опција воопшто нема смисла, бидејќи за да извршите прашања во повеќето бази на податоци, сепак ќе треба да го поминете броењето наместо индексот на последниот запис. Одземањето startIndex од endIndex можеби е едноставна аритметичка операција, но тука е излишна.

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

Враќањето на секоја следна страница ќе биде поскапо и побавно од претходната, бидејќи базата на податоци сепак ќе треба да ги помине сите записи „од почеток“ според критериумите за пребарување и сортирање, а потоа да застане на саканиот фрагмент.
Не сите DBMS можат да го поддржат овој пристап.

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

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. Но, ако не постои строга корелација помеѓу вредностите на примарниот клуч и полето според кое се сортира резултатот, ова ИЛИ не може да се избегне во повеќето DBMS. Исклучок за кој знам е PostgreSQL, кој целосно поддржува споредби со множества, а горенаведениот услов може да се напише како „WHERE (Date на нарачка, SalesOrderID) < ('2014-06-29', 75074)". Со оглед на композитниот клуч со овие две полиња, барањето како ова треба да биде прилично лесно.

Втор алтернативен пристап може да се најде, на пример, во API за лизгање ElasticSearch или Космос ДБ — кога барањето, покрај податоците, враќа специјален идентификатор со кој можете да го добиете следниот дел од податоците. Ако овој идентификатор има неограничен животен век (како во Comsos DB), тогаш ова е одличен начин за спроведување на страничење со секвенцијална транзиција помеѓу страниците (опција бр. 2 спомената погоре). Неговите можни недостатоци: не е поддржан во сите DBMS; добиениот идентификатор на следното парче може да има ограничен животен век, што генерално не е погодно за спроведување на интеракција со корисникот (како што е 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 и други. Сите тие се дизајнирани да градат „фацети“ и го прават тоа доста ефикасно поради превртениот индекс. Како работат пребарувачите, зошто во такви случаи тие се поефикасни од базите на податоци за општа намена, какви практики и стапици постојат - ова е тема за посебна статија. Овде би сакал да го привлечам вашето внимание на фактот дека пребарувачот не може да биде замена за главното складирање на податоци, тој се користи како додаток: сите промени во главната база на податоци кои се релевантни за пребарување се синхронизираат во индексот за пребарување; Пребарувачот обично комуницира само со пребарувачот и не пристапува до главната база на податоци. Една од најважните точки овде е како сигурно да се организира оваа синхронизација. Сè зависи од барањата за „време на реакција“. Ако времето помеѓу промената во главната база на податоци и нејзината „манифестација“ во пребарувањето не е критично, можете да креирате услуга што бара неодамна променети записи на секои неколку минути и ги индексира. Ако сакате најкратко можно време за одговор, можете да имплементирате нешто слично трансакциско сандаче за испраќање ажурирања до услугата за пребарување.

Наоди

Спроведувањето на страничење од страна на серверот е значајна компликација и има смисла само за брзорастечки или едноставно големи збирки податоци. Не постои апсолутно точен рецепт за тоа како да се оцени „големото“ или „брзо расте“, но јас би го следел овој пристап:
- Ако примањето комплетна колекција на податоци, земајќи го предвид времето на серверот и преносот на мрежата, нормално одговара на барањата за изведба, нема смисла да се имплементира страничење на страната на серверот.
- Може да има ситуација во која не се очекуваат проблеми со перформансите во блиска иднина, бидејќи има малку податоци, но собирањето податоци постојано расте. Ако некој сет на податоци во иднина повеќе не ја задоволува претходната точка, подобро е веднаш да започнете со страничење.
Ако не постои строго барање од делот на бизнисот за прикажување на вкупниот број на резултати или за прикажување на броеви на страници, а вашиот систем нема пребарувач, подобро е да не ги имплементирате овие точки и да ја разгледате опцијата #2.
Ако постои јасен услов за фацетирано пребарување, имате две опции без да ги жртвувате перформансите:
- Не пресметувајте ги сите количини секогаш кога се менуваат критериумите за пребарување.
- Користете пребарувачи како Solr, ElasticSearch, Sphinx и други. Но, треба да се разбере дека не може да биде замена за главната база на податоци и треба да се користи како додаток на главната складиште за решавање на проблеми со пребарувањето.
Исто така, во случај на фацетирано пребарување, има смисла да се подели преземањето на страницата со резултати од пребарувањето и броењето на две паралелни барања. Броењето на количините може да потрае подолго од добивањето резултати, додека резултатите се поважни за корисникот.
Ако користите SQL база на податоци за пребарување, секоја промена на кодот поврзана со овој дел треба добро да се тестира за перформанси на соодветна количина на податоци (надминување на обемот во живата база на податоци). Исто така, препорачливо е да се користи следење на времето на извршување на барањето на сите инстанци на базата на податоци, а особено на онаа „во живо“. Дури и ако сè беше во ред со плановите за пребарување во фазата на развој, како што расте обемот на податоци, ситуацијата може значително да се промени.

Извор: www.habr.com