Ўсім прывітанне! Я бэкэнд-распрацоўшчык, пішу мікрасэрвісы на Java + Spring. Працую ў адной з каманд распрацоўкі ўнутраных прадуктаў у кампаніі Тинькофф.
У нас у камандзе часта паўстае пытанне аптымізацыі запытаў у СКБД. Заўсёды хочацца яшчэ крыху хутчэй, але не заўсёды можна абысціся прадумана выбудаванымі індэксамі – даводзіцца шукаць нейкія абыходныя шляхі. Падчас аднаго з такіх тулянняў па сетцы ў пошуках разумных аптымізацый пры працы з БД я знайшоў , аўтара кнігі SQL Performance Explained. Гэта той самы рэдкі від блогаў, у якім можна чытаць усе артыкулы запар.
Жадаю перавесці для вас невялікі артыкул Маркуса. Яе можна назваць у нейкай ступені маніфестам, які імкнецца прыцягнуць увагу да старой, але да гэтага часу актуальнай праблеме прадукцыйнасці аперацыі offset па стандарце SQL.
У некаторых месцах я буду дапаўняць аўтара тлумачэннямі і заўвагамі. Усе такія месцы я буду пазначаць як "заўв." для большай яснасці
невялікае ўвядзенне
Думаю, шматлікія ведаюць, наколькі праблемнай і тармазной апыняецца праца з пастаронкавымі селектамі праз offset. А ці ведаеце вы, што яе можна даволі проста замяніць на больш прадукцыйную канструкцыю?
Такім чынам, ключавое слова offset паказвае базе прапусціць першыя n запісаў у запыце. Аднак база ўсё яшчэ павінна прачытаць гэтыя першыя n запісаў з дыска, прычым у зададзеным парадку (заўв.: ужыць сартаванне, калі яна зададзена), і толькі пасля гэтага будзе магчыма вярнуць запісы пачынальна з n+1 і далей. Самае цікавае, што праблема не ў канкрэтнай рэалізацыі ў СКБД, але ў першапачатковым вызначэнні па стандарце:
…the rows are first sorted according to the and then limited by dropping the number of rows specified in the from the beginning…
-SQL:2016, Part 2, 4.15.3 Derived tables (заўв.: цяпер самы выкарыстоўваны стандарт)
Ключавы пункт тут у тым, што offset прымае адзіны параметр - колькасць запісаў, якія трэба прапусціць, і ўсё. Прытрымліваючыся такога вызначэння СКБД можа толькі дастаць усе запісы, а затым адкінуць непатрэбныя. Відавочна, што такое азначэнне offset'а прымушае праробліваць лішнюю працу. І тут нават не важна, SQL гэта ці NoSQL.
Яшчэ крыху болю
Праблемы offset на гэтым не сканчаюцца, і вось чаму. Калі паміж чытаннем дзвюх старонак дадзеных з дыска іншая аперацыя ўставіць новы запіс, што адбудзецца ў гэтым выпадку?
Калі выкарыстоўваецца offset для пропуску запісаў з папярэдніх старонак, у сітуацыі дадання новага запісу паміж аперацыямі чытання розных старонак, хутчэй за ўсё, вы атрымаеце дублікаты (заўв.: такое магчыма, калі мы чытаем пастаронкава з выкарыстаннем канструкцыі order by, тады ў сярэдзіну нашай выдачы можа патрапіць новы запіс).
Малюнак наглядна адлюстроўвае такую сітуацыю. База чытае першыя 10 запісаў, пасля гэтага ўстаўляецца новы запіс, які ссоўвае ўсе прачытаныя запісы на 1. Затым база бярэ новую старонку з 10 наступных запісаў і пачынае не з 11-й, як павінна, а з 10-й, дублюючы гэты запіс. Ёсць і іншыя анамаліі, звязаныя з выкарыстаннем гэтага выраза, але гэтая - самая распаўсюджаная.
Як мы ўжо высветлілі, гэта не праблемы канкрэтнай СКБД ці іх рэалізацый. Праблема - у вызначэнні пагінацыі па стандарце SQL. Мы гаворым СКБД, якую старонку трэба дастаць або як шмат запісаў прапусціць. База проста не ў стане аптымізаваць такі запыт, бо для гэтага замала інфармацыі.
Варта таксама ўдакладніць, што гэта праблема не канкрэтнага ключавога слова, а хутчэй за семантыку запыту. Ёсць яшчэ некалькі ідэнтычных па праблемнасці сінтаксісаў:
- Ключавое слова offset, як гаварылася раней.
- Канструкцыя з двух ключавых слоў limit [offset] (хоць сам па сабе limit не так ужо і дрэнны).
- Фільтраванне па ніжніх межах, пабудаваная на нумарацыі радкоў (напрыклад, row_number(), rownum і т. д.).
Усе гэтыя выразы проста кажуць, колькі радкоў трэба прапусціць, ніякай дадатковай інфармацыі ці кантэксту.
Далей у гэтым артыкуле ключавое слова offset выкарыстоўваецца як абагульненне ўсіх гэтых варыянтаў.
Жыццё без OFFSET
А зараз уявім, якім быў бы наш свет без усіх гэтых праблем. Аказваецца, жыццё без offset не так ужо і складаная: селектам можна выбіраць толькі тыя радкі, што мы яшчэ не бачылі (заўв.: гэта значыць тыя, якіх не было на мінулай старонцы), з дапамогай умовы ў where.
У гэтым выпадку мы адштурхваемся ад таго факту, што селекты выконваюцца над спарадкаваным мноствам (стары добры order by). Паколькі маем спарадкаванае мноства, можам выкарыстоўваць даволі просты фільтр, каб даставаць толькі тыя дадзеныя, якія знаходзяцца за апошнім запісам папярэдняй старонкі:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYВось і ўвесь прынцып такога падыходу. Вядома, пры сартаванні па многіх слупках усё становіцца весялей, але ідэя ўсё тая ж. Важна заўважыць, што гэтая канструкцыя дастасавальна на шматлікіх -рашэннях.
Такі падыход называецца seek method ці keyset pagination. Ён вырашае праблему з які плавае вынікам (заўв.: сітуацыя з запісам паміж чытаннямі старонак, апісаная раней) і, вядома, што мы ўсё каханы, працуе хутчэй і стабільней, чым класічны offset. Стабільнасць складаецца ў тым, што час апрацоўкі запыту не павялічваецца прапарцыйна нумару запытанай табліцы (заўв.: калі жадаецца падрабязней пазнаць пра працу розных падыходаў да пагінацыі, можна . Там жа можна знайсці параўнальныя бенчмаркі па розных метадах).
Адзін са слайдаў , Што пагінацыя па ключах, вядома ж, не ўсемагутная - яна мае свае абмежаванні. Найбольш значнае - у яе няма магчымасці чытаць выпадковыя старонкі (заўв.: непаслядоўна). Аднак у эпоху бясконцага скралінга (заўв.: на франтэндзе) гэта не такая ўжо і праблема. Указанне нумара старонкі для пстрычкі — у любым выпадку дрэннае рашэнне пры распрацоўцы UI (заўв.: меркаванне аўтара артыкула).
А што з інструментамі?
Пагінацыя на ключах часта не падыходзіць з-за адсутнасці інструментальнай падтрымкі дадзенага метаду. Большасць з інструментаў распрацоўкі, у тым ліку розныя фрэймворкі, не даюць выбару, якім менавіта спосабам будзе выконвацца пагінацыя.
Сітуацыю пагаршае тое, што апісаны метад патрабуе скразной падтрымкі ў выкарыстоўваных тэхналогіях – пачынальна ад СКБД і сканчаючы выкананнем AJAX-запыту ў браўзэры пры бясконцым скралінгу. Замест таго каб указваць толькі нумар старонкі, зараз давядзецца ўказваць набор ключоў для ўсіх старонак адразу.
Аднак колькасць фрэймворкаў, якія падтрымліваюць пагінацыю на ключах, паступова расце. Вось што ёсць цяпер:
- для Java;
- для Ruby;
- и для Django;
- для Python;
- - criteria API для рэалізацый JPA;
- для Perl;
- , мапер для Node.js .
(Заўв.: некаторыя спасылкі былі прыбраныя з прычыны таго, што на момант перакладу некаторыя бібліятэкі не абнаўляліся з 2017—2018 года. Калі цікава, можна зазірнуць у першакрыніцу.)
Якраз на гэтым моманце і патрэбна ваша дапамога. Калі вы распрацоўваеце ці падтрымліваеце фрэймворк, які хоць неяк выкарыстоўвае пагінацыю, то я прашу, я заклікаю, я малю вас зрабіць натыўную падтрымку для пагінацыі на ключах. Калі ёсць пытанні ці вам патрэбна дапамога, буду рады дапамагчы (, , ) (заўв.: па маім досведзе зносін з Маркусам магу сказаць, што ён сапраўды ставіцца з энтузіязмам да распаўсюджвання гэтай тэмы).
Калі ж вы карыстаецеся гатовымі рашэннямі, якія, як вы думаеце, вартыя мець падтрымку пагінацыі па ключах, - стварыце рэквест ці нават прапануеце гатовае рашэнне, калі гэта магчыма. Можна таксама пазначыць у спасылцы дадзены артыкул.
Заключэнне
Чыннік, чаму такі просты і карысны падыход, як пагінацыя па ключах, мала распаўсюджаны, не ў тым, што гэта складана ў тэхнічнай рэалізацыі ці патрабуе нейкіх вялікіх высілкаў. Галоўная прычына ў тым, што многія прывыклі бачыць і працаваць з offset - такі падыход дыктуецца самім стандартам.
Як следства, нямногія задумваюцца аб змене падыходу да пагінацыі, а з-за гэтага і інструментальная падтрымка з боку фрэймворкаў і бібліятэк развіваецца слаба. Таму, калі вам блізкая ідэя і мэта безафсетнай пагінацыі, - дапамажыце распаўсюдзіць яе!
Крыніца:
Аўтар: Markus Winand
Крыніца: habr.com