Адзін з тыпавых сцэнарыяў ва ўсіх звыклых нам прыкладаннях - пошук дадзеных па пэўных крытэрыях і вывад іх у зручным для чытання выглядзе. Тут жа могуць быць дадатковыя магчымасці па сартаванні, групоўцы, пастаронкавай выснове. Задача, па ідэі, трывіяльная, але пры яе вырашэнні многія распрацоўшчыкі робяць шэраг памылак, з-за якіх потым пакутуе прадукцыйнасць. Паспрабуем разгледзець розныя варыянты рашэнняў гэтай задачы і сфармуляваць рэкамендацыі па выбары найболей эфектыўнай рэалізацыі.
Варыянт пэйджынгу #1
Самы просты варыянт, які прыходзіць у галаву - гэта пастаронкавая выснова вынікаў пошуку ў яго самым класічным ў выглядзе.
Дапусцім, у дадатку выкарыстоўваецца рэляцыйная база дадзеных. У гэтым выпадку для вываду інфармацыі ў такім выглядзе трэба будзе выканаць два SQL запыту:
- Атрымаць радкі для бягучай старонкі.
- Палічыць агульную колькасць радкоў, якая адпавядае крытэрам пошуку - гэта трэба для паказу старонак.
Разгледзім першы запыт на прыкладзе тэставай MS SQL базы для 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 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 logical reads:
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 logical reads.
Можна прывесці яшчэ некалькі прыкладаў запытаў на падлік колькасці, але сутнасць застанецца той жа: атрыманне порцыі дадзеных і падлік агульнай колькасці - гэта два прынцыпова розных запыту, і кожны патрабуе сваіх мер для аптымізацыі. У агульным выпадку не атрымаецца знайсці камбінацыю азначнікаў, якая аднолькава добра працуе для абодвух запытаў.
Адпаведна, адно з важных патрабаванняў, якое варта ўдакладніць пры распрацоўцы такога пошукавага рашэння - ці сапраўды бізнэсу важна бачыць агульную колькасць знойдзеных аб'ектаў. Часцяком бывае, што не. А рух па пэўных нумарах старонкі, на мой погляд — рашэнне з вельмі вузкай вобласцю прымянення, бо большасць сцэнарыяў з пэйджынгам выглядае як «перайсці на наступную старонку».
Варыянт пэйджынгу #2
Выкажам здагадку, карыстачам не важна веданне агульнай колькасці знойдзеных аб'ектаў. Паспрабуем спрасціць пошукавую старонку:
Па факце змянілася толькі тое, што няма магчымасці пераходзіць па канкрэтных нумарах старонак, і зараз гэтай табліцы для адлюстравання не трэба ведаць, колькі ўсяго іх можа быць. Але ўзнікае пытанне - а як табліца даведаецца, ці ёсць дадзеныя для наступнай старонкі (каб правільна адлюстраваць спасылку "Next")?
Адказ вельмі просты: можна вычытваць з базы на адзін запіс больш, чым трэба для адлюстравання, і наяўнасць гэтага "дадатковага" запісу і будзе паказваць, ці ёсць наступная порцыя. Такім чынам, для атрымання адной старонкі дадзеных трэба будзе выканаць усяго адзін запыт, што істотна паляпшае прадукцыйнасць і палягчае падтрымку такой функцыянальнасці. У мяне на практыцы быў выпадак, калі адмова ад падліку агульнай колькасці запісаў паскорыла выдачу вынікаў у 4-5 разоў.
Для гэтага падыходу існуе некалькі варыянтаў карыстацкага інтэрфейсу: каманды "назад" і "наперад", як у прыкладзе вышэй, кнопка "загрузіць яшчэ", якая проста дадае новую порцыю ў якія адлюстроўваюцца вынікі, "бясконцая пракрутка", якая працуе па прынцыпе "загрузіць яшчэ », але сігналам для атрымання наступнай порцыі з'яўляецца пракрутка карыстачом усіх выведзеных вынікаў да канца. Якім бы ні было візуальнае рашэнне, прынцып выбаркі дадзеных застаецца такім жа.
Нюансы рэалізацыі пэйджынгу
Ва ўсіх прыкладах запытаў, прыведзеных вышэй, выкарыстоўваецца падыход "зрушэнне + колькасць", калі ў самім запыце паказваецца з якога па парадку радка выніку і якую колькасць радкоў трэба вярнуць. Перш разгледзім, як лепш арганізаваць перадачу параметраў у гэтым выпадку. На практыцы я сустракаў некалькі спосабаў:
- Парадкавы нумар запытанай старонкі (pageIndex), памер старонкі (pageSize).
- Парадкавы нумар першага запісу, які трэба вярнуць (startIndex), максімальная колькасць запісаў у выніку (count).
- Парадкавы нумар першага запісу, які трэба вярнуць (startIndex), парадкавы нумар апошняга запісу, які трэба вярнуць (endIndex).
На першы погляд можа падацца, што гэта настолькі элементарна, што ніякай розніцы няма. Але гэта не так - найболей зручным і ўніверсальным варыянтам з'яўляецца другі (startIndex, count). На гэта ёсць некалькі прычын:
- Для падыходу з вычыткай +1 запісы, прыведзенага вышэй, першы варыянт з pageIndex і pageSize вельмі няёмкі. Напрыклад, мы жадаем адлюстроўваць 50 запісаў на старонцы. Згодна з прыведзеным вышэй алгарытму, трэба чытаць на адзін запіс больш, чым трэба. Калі гэты "1" не закладзены на серверы, атрымліваецца, што для першай старонкі мы павінны запытваць запісы з 1 па 51, для другой - з 51 па 101 і г.д. Калі пазначыць памер старонкі 51 і павялічваць pageIndex, то другая старонка верне з 52 па 102 і г.д. Адпаведна, у першым варыянце адзіны спосаб нармальна рэалізаваць кнопку пераходу на наступную старонку - закладваць на серверы вычытку "лішняга" радка, што будзе вельмі няяўным нюансам.
- Трэці варыянт наогул не мае сэнсу, бо для выканання запытаў у большасці баз дадзеных усё роўна трэба будзе перадаць колькасць, а не азначнік апошняга запісу. Хай адніманне startIndex з endIndex і элементарная арыфметычная аперацыя, але яна тут лішняя.
Цяпер варта апісаць недахопы рэалізацыі пэйджынгу праз «зрушэнне + колькасць»:
- Атрыманне кожнай наступнай старонкі будзе больш затратным і павольным, чым папярэдняй, таму што базе даных усё роўна трэба будзе прайсці ўсе запісы "з пачатку" згодна з крытэрыямі пошуку і сартавання, пасля чаго спыніцца на патрэбным фрагменце.
- Ня ўсе СКБД могуць падтрымліваць гэты падыход.
Альтэрнатывы ёсць, але яны таксама неідэальныя. Першы з такіх падыходаў завецца "keyset paging" ці "seek method" і складаецца ў наступным: пасля атрымання порцыі можна запамінаць значэнні палёў у апошнім запісе на старонцы, а затым выкарыстоўваць іх для атрымання наступнай порцыі. Напрыклад, мы выконвалі такі запыт:
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
Гэты варыянт будзе працаваць карэктна, але ў агульным выпадку яго будзе цяжка аптымізаваць, бо ўмова ўтрымоўвае аператар OR. Калі з ростам OrderDate расце значэнне першаснага ключа, тая ўмова можна спрасціць, пакінуўшы толькі фільтр па SalesOrderID. Але калі паміж значэннямі першаснага ключа і палі, па якім адсартаваны вынік, няма строгай карэляцыі - у большасці СКБД пазбегнуць гэтага OR не атрымаецца. Вядомым мне выключэннем з'яўляецца PostgreSQL, дзе ў поўнай меры падтрымліваецца параўнанне картэжаў, і паказанае вышэй умова можна запісаць як "WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074)". Пры наяўнасці складовага ключа з гэтымі двума палямі падобны запыт павінен быць дастаткова лёгкім.
Другі альтэрнатыўны падыход можна сустрэць, напрыклад, у або - калі запыт акрамя дадзеных вяртае спецыяльны ідэнтыфікатар, з дапамогай якога можна атрымаць наступную порцыю дадзеных. Калі гэты ідэнтыфікатар мае неабмежаваны тэрмін жыцця (як у Comsos DB), тое гэта выдатны спосаб рэалізацыі пэйджынгу з паслядоўным пераходам паміж старонкамі (варыянт #2 згаданы вышэй). Яго магчымыя недахопы: падтрымліваецца далёка не ва ўсіх СКБД; атрыманы ідэнтыфікатар наступнай порцыі можа мець абмежаваны тэрмін жыцця, што ў агульным выпадку не падыходзіць для рэалізацыі ўзаемадзеяння з карыстачом (як, напрыклад, ElasticSearch scroll API).
Складанае фільтраванне
Ускладняем задачу далей. Выкажам здагадку, з'явілася патрабаванне прадаць так званы faceted search, добра ўсім знаёмы па Інтэрнэт-крамах. Прыведзеныя вышэй прыклады на аснове табліцы заказаў не вельмі паказальныя ў гэтым выпадку, таму пераключымся на табліцу Product з базы AdventureWorks:
У чым ідэя faceted search? У тым, што для кожнага элемента фільтра паказваецца колькасць запісаў, якія адпавядаюць гэтаму крытэру з улікам фільтраў, абраных ва ўсіх астатніх катэгорыях.
Напрыклад, калі мы выберам у гэтым прыкладзе катэгорыю Bikes і колер Black, табліца будзе выводзіць толькі ровары чорнага колеру, але пры гэтым:
- Для кожнага крытэра групы "Categories" будзе паказана колькасць прадуктаў з гэтай катэгорыі чорнага колеру.
- Для кожнага крытэрыю групы «Colors» будзе паказана колькасць ровараў гэтага колеру.
Вось прыклад вываду выніку для такіх умоў:
Калі ў дадатак адзначыць катэгорыю «Clothing», табліца пакажа яшчэ і адзенне чорнага колеру, якая ёсць у наяўнасці. Колькасць прадуктаў чорнага колеру ў секцыі "Color" таксама будзе пералічана паводле новых умоў, толькі ў секцыі "Categories" нічога не зменіцца… Спадзяюся гэтых прыкладаў дастаткова, каб зразумець звыклы алгарытм працы faceted search.
Цяпер уявім, як гэта можна рэалізаваць на рэляцыйнай базе. Кожная група крытэрыяў, такая як Category і Color, будзе патрабаваць асобнага запыту:
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 запісаў знойдзена, паказаць?» Карыстальнік можа альбо працягнуць мяняць умовы пошуку, альбо націснуць кнопку "паказаць". Толькі ў другім выпадку будуць выкананы ўсе запыты па атрыманні вынікаў і пераліку колькасцяў на ўсіх "фасетах". Пры гэтым, як нескладана заўважыць, давядзецца мець справу з запытам на атрыманне агульнай колькасці вынікаў і яго аптымізацыяй. Гэты спосаб можна сустрэць у шматлікіх невялікіх інтэрнэт-крамах. Відавочна, што гэта не панацэя для дадзенай праблемы, але ў простых выпадках можа быць нядрэнным кампрамісам.
- Выкарыстоўваць search engine для пошуку вынікаў і падліку фасет, такія як Solr, ElasticSearch, Sphinx і іншыя. Усе яны разлічаны на пабудову «фасетаў» і робяць гэта дастаткова эфектыўна за кошт інвертаваць індэкса. Як уладкованыя пошукавыя сістэмы, чаму яны ў такіх выпадках эфектыўней баз дадзеных агульнага прызначэння, якія ёсць практыкі і падводныя камяні - гэта тэма для асобнага артыкула. Тутака ж я жадаю звярнуць увагу, што search engine не можа быць заменай асноўнага сховішчы дадзеных, выкарыстоўваецца ён як дадатак: любыя змены ў асноўнай базе, мелыя значэнне для пошуку, сінхранізуюцца ў пошукавы азначнік; механізм пошуку ўзаемадзейнічае звычайна толькі з search engine і не звяртаецца да асноўнай базы. Адзін з самых важных момантаў тут - як арганізаваць гэтую сінхранізацыю надзейна. Усё залежыць ад патрабаванняў да "часу рэакцыі". Калі час паміж зменай у асноўнай базе і яго "праявай" у пошуку не крытычна, можна зрабіць сэрвіс, які раз у некалькі хвілін шукае нядаўна змененыя запісы і іх індэксуе. Калі патрабуецца мінімальна магчымы час рэакцыі, можна рэалізаваць нешта тыпу для адпраўкі абнаўленняў у пошукавы сэрвіс.
Высновы
- Рэалізацыя пэйджынгу на баку сервера - сур'ёзнае ўскладненне, і прымяняць яго мае сэнс толькі для хуткарослых або проста вялікіх набораў дадзеных. Як ацаніць "вялікі" ці "хуткарослы" - абсалютна дакладнага рэцэпту няма, але я б прытрымліваўся такога падыходу:
- Калі атрыманне поўнай калекцыі дадзеных з улікам сервернага часу і перадачы па сетцы нармальна ўкладваецца ў патрабаванні па прадукцыйнасці - рэалізоўваць пэйджынг на баку сервера сэнсу няма.
- Можа быць такая сітуацыя, што на бліжэйшы час праблем з прадукцыйнасцю не прадбачыцца, бо дадзеных мала, але калекцыя дадзеных увесь час расце. Калі нейкі набор дадзеных у перспектыве можа перастаць задавальняць папярэдняму пункту - лепш пэйджынг закласці адразу.
- Калі з боку бізнэсу няма жорсткага патрабавання па паказе агульнай колькасці вынікаў або па адлюстраванні нумароў старонак, і пры гэтым у вашай сістэме няма пошукавага рухавічка - лепш гэтыя моманты не рэалізоўваць і разглядаць варыянт #2.
- Калі ёсць дакладнае патрабаванне аб faceted search, у вас ёсць два варыянты не ахвяраваць прадукцыйнасцю:
- Не пералічваць усе колькасці на кожнай змене крытэрыяў пошуку.
- Выкарыстоўваць search engine такія як Solr, ElasticSearch, Sphinx і іншыя. Але трэба разумець, што ён не можа быць заменай асноўнай базе даных, і павінен выкарыстоўвацца як дадатак да асноўнага сховішча для вырашэння пошукавых задач.
- Таксама ў выпадку faceted search мае сэнс падзяліць атрыманне старонкі вынікаў пошуку і падлік колькасцяў на два паралельныя запыты. Падлік колькасцяў можа заняць большы час, чым атрыманне вынікаў, у той час як вынікі важней для карыстальніка.
- Калі вы выкарыстоўваеце SQL базу для пошуку, любая змена кода, якое адносіцца да гэтай часткі, павінна добра тэставацца ў стаўленні прадукцыйнасці на які адпавядае аб'ёме дадзеных (праўзыходным аб'ём у "жывы" базе). Пажадана таксама выкарыстоўваць маніторынг часу выканання запытаў на ўсіх экзэмплярах базы, і асабліва - на "жывым". Нават калі на этапе распрацоўкі з планамі запытаў усё было добра, з ростам аб'ёму даных сітуацыя можа прыкметна змяніцца.
Крыніца: habr.com