Не варта карыстацца OFFSET і LIMIT у запытах з разбіццём на старонкі

Мінулі тыя дні, калі не трэба было турбавацца аб аптымізацыі прадукцыйнасці баз дадзеных. Час не стаіць на месцы. Кожны новы бізнэсмэн са сферы высокіх тэхналогій жадае стварыць чарговы Facebook, імкнучыся пры гэтым збіраць усе дадзеныя, да якіх можа дацягнуцца. Гэтыя дадзеныя патрэбны бізнэсу для больш якаснага навучання мадэляў, якія дапамагаюць зарабляць. У такіх умовах праграмістам неабходна ствараць такія API, якія дазваляюць хутка і надзейна працаваць з вялізнымі аб'ёмамі інфармацыі.

Калі вы ўжо некаторы час займаецеся праектаваннем серверных частак прыкладанняў ці баз дадзеных, тыя вы, верагодна, пісалі код для выканання запытаў з разбіццём на старонкі. Напрыклад - такі:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Дык яно і ёсць?

Але калі разбіццё на старонкі вы выконвалі менавіта так, я са шкадаваннем магу адзначыць, што вы рабілі гэта далёка не самай эфектыўнай выявай.

Жадаеце мне запярэчыць? можаце ня марнаваць час. Млявы, Shopify и Міксмакс ужо ўжываюць прыёмы, пра якія я хачу сёння расказаць.

Назавіце хаця б аднаго распрацоўніка бэкэндаў, які ніколі не карыстаўся OFFSET и LIMIT для выканання запытаў з разбіццём на старонкі. У MVP (Minimum Viable Product, мінімальны жыццяздольны прадукт) і ў праектах, дзе выкарыстоўваюцца невялікія аб'ёмы дадзеных, гэты падыход цалкам дастасавальны. Ён, так бы мовіць, "проста працуе".

Але калі трэба з нуля ствараць надзейныя і эфектыўныя сістэмы, варта загадзя паклапаціцца аб эфектыўнасці выканання запытаў да баз даных, якія выкарыстоўваюцца ў такіх сістэмах.

Сёння мы пагаворым аб праблемах, якія спадарожнічаюць шырока выкарыстоўваным (шкада, што гэта так) рэалізацыям механізмаў выканання запытаў з разбіццём на старонкі, і аб тым, як дамагчыся высокай прадукцыйнасці пры выкананні падобных запытаў.

Што не так з OFFSET і LIMIT?

Як ужо было сказана, OFFSET и LIMIT добра паказваюць сябе ў праектах, у якіх не трэба працаваць з вялікімі аб'ёмамі дадзеных.

Праблема ўзнікае ў тым выпадку, калі база дадзеных разрастаецца да такіх памераў, што перастае змяшчацца ў памяці сервера. Але пры гэтым падчас працы з гэтай базай дадзеных трэба выкарыстоўваць запыты з разбіццём на старонкі.

Для таго каб гэтая праблема сябе выявіла, трэба, каб узнікла сітуацыя, у якой СКБД звяртаецца да неэфектыўнай аперацыі поўнага сканавання табліцы (Full Table Scan) пры выкананні кожнага запыту з разбіццём на старонкі (у той жа час могуць адбывацца аперацыі па ўстаўцы і выдаленні дадзеных , і састарэлыя дадзеныя нам пры гэтым не патрэбныя!).

Што такое "поўнае сканіраванне табліцы" (або "паслядоўны прагляд табліцы", Sequential Scan)? Гэта - аперацыя, падчас якой СКБД паслядоўна счытвае кожны радок табліцы, гэта значыць - якія змяшчаюцца ў ёй дадзеныя, і правярае іх на адпаведнасць зададзенай умове. Вядома, што гэты тып сканавання табліц з'яўляецца самым марудным. Справа ў тым, што пры ім выкананні выконваецца шмат аперацый уводу/высновы, якія задзейнічаюць дыскавую падсістэму сервера. Сітуацыю пагаршаюць затрымкі, спадарожныя працы з дадзенымі, якія захоўваюцца на дысках, і тое, што перадача дадзеных з дыска ў памяць - гэта рэсурсаёмістая аперацыя.

Напрыклад, у вас ёсць запісы аб 100000000 карыстальніках, і вы выконваеце запыт з канструкцыяй OFFSET 50000000. Гэта значыць, што СКБД давядзецца загрузіць усе гэтыя запісы (хоць яны нам нават не патрэбныя!), змясціць іх у памяць, а ўжо пасля гэтага ўзяць, напрыклад, 20 вынікаў, пра якія паведамлена ў LIMIT.

Скажам, гэта можа выглядаць так: "абраць радкі ад 50000 да 50020 з 100000". Гэта значыць, сістэме для выканання запыту трэба будзе спачатку загрузіць 50000 радкоў. Бачыце, як шмат непатрэбнай працы ёй давядзецца выканаць?

Калі не верыце - зірніце на прыклад, які я стварыў, карыстаючыся магчымасцямі db-fiddle.com. 

Прыклад на db-fiddle.com

Там, злева, у полі Schema SQL, маецца код, які выконвае ўстаўку ў базу дадзеных 100000 радкоў, а справа, у поле Query SQL, паказаны два запыты. Першы, павольны, выглядае так:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

А другі, які ўяўляе сабой эфектыўнае рашэнне той жа задачы, так:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Для таго каб выканаць гэтыя запыты, дастаткова націснуць на кнопку Run у верхняй частцы старонкі. Зрабіўшы гэта, параўнаем звесткі аб часе выканання запытаў. Аказваецца, што на выкананне неэфектыўнага запыту ідзе, як мінімум, у 30 разоў больш часу, чым на выкананне другога (ад запуску да запуску гэты час адрозніваецца, напрыклад, сістэма можа паведаміць аб тым, што на выкананне першага запыту пайшло 37 мс, а на выкананне другога - 1 мс).

А калі дадзеных будзе больш, то ўсё будзе выглядаць яшчэ горш (для таго каб у гэтым пераканацца - зірніце на мой. прыклад з 10 мільёнамі радкоў).

Тое, што мы толькі што абмеркавалі, павінна даць вам некаторае разуменне таго, як, насамрэч, апрацоўваюцца запыты да баз дадзеных.

Улічвайце, што чым большае значэнне OFFSET - тым даўжэй будзе выконвацца запыт.

Што варта выкарыстоўваць замест камбінацыі OFFSET і LIMIT?

Замест камбінацыі OFFSET и LIMIT варта выкарыстоўваць канструкцыю, пабудаваную па такой схеме:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Гэта - выкананне запыту з разбіццём на старонкі, заснаванае на курсоры (Cursor based pagination).

Замест таго, каб лакальна захоўваць бягучыя OFFSET и LIMIT і перадаваць іх з кожным запытам, трэба захоўваць апошні атрыманы першасны ключ (звычайна - гэта ID) І LIMIT, у выніку і будуць атрымлівацца запыты, якія нагадваюць вышэйпрыведзены.

Чаму? Справа ў тым, што ў відавочным выглядзе паказваючы ідэнтыфікатар апошняга прачытанага радка, вы паведамляеце сваёй СКБД аб тым, дзе ёй трэба пачынаць пошук патрэбных дадзеных. Прычым, пошук, дзякуючы выкарыстанню ключа, будзе ажыццяўляцца эфектыўна, сістэме не давядзецца адцягвацца на радкі, якія знаходзяцца за межамі названага дыяпазону.

Давайце зірнем на наступнае параўнанне прадукцыйнасці розных запытаў. Вось неэфектыўны запыт.

Павольны запыт

А вось - аптымізаваная версія гэтага запыту.

Хуткі запыт

Абодва запыты вяртаюць у дакладнасці адзін і той жа аб'ём дадзеных. Але на выкананне першага сыходзіць 12,80 секунд, а на другі - 0,01 секунда. Адчуваеце розніцу?

магчымыя праблемы

Для забеспячэння эфектыўнай працы прапанаванага метаду выканання запытаў трэба, каб у табліцы прысутнічаў бы слупок (ці слупкі), утрымоўвальны ўнікальныя, паслядоўна размешчаныя азначнікі, накшталт цэлалікавага ідэнтыфікатара. У некаторых спецыфічных выпадках гэта можа вызначаць поспех ужывання падобных запытаў дзеля падвышэння хуткасці працы з базай дадзеных.

Натуральна, канструюючы запыты, трэба ўлічваць асаблівасці архітэктуры табліц, і выбіраць тыя механізмы, якія найлепшай выявай пакажуць сябе на наяўных табліцах. Напрыклад, калі трэба працаваць у запытах з вялікімі аб'ёмамі злучаных дадзеных, вам можа здацца цікавай гэтая артыкул.

Калі перад намі стаіць праблема адсутнасці першаснага ключа, напрыклад, калі маецца табліца з стаўленнем "многія-да-многім", то традыцыйны падыход, які прадугледжвае прымяненне OFFSET и LIMIT, нам гарантавана падыдзе. Але яго прымяненне можа прывесці да выканання патэнцыйна павольных запытаў. У падобных выпадках я парэкамендаваў бы выкарыстоўваць першасны ключ з аўтаінкрыментам, нават калі ён патрэбен толькі для арганізацыі выканання запытаў з разбіццём на старонкі.

Калі вам цікавая гэтая тэма вось, вось и вось - некалькі карысных матэрыялаў.

Вынікі

Галоўная выснова, якую мы можам зрабіць, заключаюцца ў тым, што заўсёды, пра якія б памеры баз даных ні ішла размова, трэба аналізаваць хуткасць выканання запытаў. У наш час вельмі важная маштабаванасць рашэнняў, і калі з самага пачатку працы над нейкай сістэмай спраектаваць усё правільна, гэта, у будучыні, здольна пазбавіць распрацоўніка ад мноства праблем.

Як вы аналізуеце і аптымізуеце запыты да баз дадзеных?

Крыніца: habr.com