PostgreSQL հակապատկերներ. նավարկություն գրանցամատյանում

Այսօր SQL-ում բարդ դեպքեր և բարդ ալգորիթմներ չեն լինի։ Ամեն ինչ շատ պարզ կլինի, Կապիտան Ակնհայտի մակարդակով. եկեք դա անենք դիտելով իրադարձությունների գրանցամատյանը դասավորված ըստ ժամանակի.

Այսինքն տվյալների բազայում նշան կա events, և նա դաշտ ունի ts - ճիշտ այն ժամանակ, երբ մենք ցանկանում ենք ցուցադրել այս գրառումները կարգով.

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Հասկանալի է, որ մենք այնտեղ տասնյակ ձայնագրություններ չենք ունենա, ուստի մեզ անհրաժեշտ կլինի ինչ-որ ձև էջի նավարկություն.

#0. «Ես մորս ջարդարարն եմ»

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Դա գրեթե կատակ չէ, դա հազվադեպ է, բայց հայտնաբերվել է վայրի բնության մեջ: Երբեմն, ORM-ի հետ աշխատելուց հետո, կարող է դժվար լինել անցնել SQL-ի հետ «ուղղակի» աշխատանքի:

Բայց եկեք անցնենք ավելի տարածված և ոչ այնքան ակնհայտ խնդիրներին:

#1. ՕՖՍԵՏ

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

Որտեղի՞ց է առաջացել 26 թիվը: Սա մուտքերի մոտավոր թիվն է մեկ էկրանը լրացնելու համար: Ավելի ճիշտ, ցուցադրված 25 գրառում, գումարած 1, ազդանշան, որ նմուշում գոնե մեկ այլ բան կա, և իմաստ ունի առաջ գնալ:

Իհարկե, այս արժեքը չի կարող «կարվել» հարցման մարմնի մեջ, այլ անցնել պարամետրով: Բայց այս դեպքում PostgreSQL ժամանակացույցը չի կարողանա ապավինել այն գիտելիքին, որ պետք է լինեն համեմատաբար քիչ գրառումներ, և հեշտությամբ կընտրի անարդյունավետ պլան:

Եվ մինչ հավելվածի միջերեսում ռեեստրի դիտումն իրականացվում է որպես վիզուալ «էջերի» միջև անցում, ոչ ոք երկար ժամանակ կասկածելի բան չի նկատում: Հենց այն պահը, երբ հարմարության համար պայքարում UI/UX-ը որոշում է ինտերֆեյսը վերածել «անվերջ ոլորման», այսինքն՝ ռեեստրի բոլոր գրառումները կազմված են մեկ ցուցակում, որը օգտվողը կարող է ոլորել վեր ու վար:

Եվ այսպես, հաջորդ թեստավորման ժամանակ քեզ բռնում են գրառումների կրկնօրինակում գրանցամատյանում։ Ինչու, քանի որ աղյուսակը նորմալ ցուցանիշ ունի (ts), որի վրա հիմնված է ձեր հարցումը:

Հենց այն պատճառով, որ դուք դա հաշվի չեք առել ts եզակի բանալի չէ այս աղյուսակում. Իրականում և դրա արժեքները եզակի չեն, ինչպես ցանկացած «ժամանակ» իրական պայմաններում, հետևաբար, նույն գրառումը երկու հարակից հարցումներում հեշտությամբ «ցատկում» է էջից էջ՝ նույն հիմնական արժեքի տեսակավորման շրջանակներում տարբեր վերջնական կարգի պատճառով:

Փաստորեն, այստեղ թաքնված է նաև երկրորդ խնդիրը, որը նկատելը շատ ավելի դժվար է. որոշ գրառումներ չեն ցուցադրվի ընդհանրապես! Ի վերջո, «կրկնօրինակ» գրառումները զբաղեցրին ուրիշի տեղը: Գեղեցիկ նկարներով մանրամասն բացատրություն կարելի է գտնել կարդալ այստեղ.

Ընդլայնելով ցուցանիշը

Խորամանկ մշակողը հասկանում է, որ ինդեքսի բանալին պետք է եզակի դարձնել, և ամենահեշտ ճանապարհն այն ընդլայնելն է ակնհայտ եզակի դաշտով, որի համար PK-ն կատարյալ է.

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

Եվ խնդրանքը փոխվում է.

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. Անցնել «կուրսորների»

Որոշ ժամանակ անց, DBA-ն գալիս է ձեզ մոտ և «գոհ է» ձեր հարցումներից նրանք դժոխքի պես բեռնում են սերվերը իրենց OFFSET կանոններով, և ընդհանրապես, ժամանակն է անցնելու նավարկություն վերջին ցուցադրված արժեքից. Ձեր հարցումը կրկին փոխվում է.

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Թեթևացած շունչ քաշեցիր, մինչև եկավ...

#3. Մաքրման ինդեքսներ

Որովհետև մի օր ձեր DBA-ն կարդաց հոդված անարդյունավետ ինդեքսներ գտնելու մասին և հասկացավ, որ «Ոչ ամենավերջին» ժամանակացույցը լավ չէ. Եվ ես նորից եկա ձեզ մոտ, հիմա այն մտքով, որ այդ ցուցանիշը դեռ պետք է վերածվի (ts DESC).

Բայց ի՞նչ անել էջերի միջև գրառումները «ցատկելու» սկզբնական խնդրի հետ: Եվ ամեն ինչ պարզ է. անհրաժեշտ է ընտրել բլոկներ՝ չֆիքսված թվով գրառումներով:

Ընդհանրապես, ո՞վ է մեզ արգելում կարդալ ոչ թե «ուղիղ 26», այլ «26-ից ոչ պակաս»։ Օրինակ, որպեսզի հաջորդ բլոկում կան ակնհայտորեն տարբեր իմաստներով գրառումներ ts - ապա բլոկների միջև գրառումները «ցատկելու» խնդիր չի լինի:

Ահա թե ինչպես հասնել դրան.

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Ինչ է այստեղ կատարվում?

1. Մենք 25 գրառում ենք «ներքևում» և ստանում «սահմանային» արժեքը ts.
2. Եթե ​​այնտեղ արդեն ոչինչ չկա, ապա փոխարինեք NULL արժեքը -infinity.
3. Ստացված արժեքի միջև մենք հանում ենք արժեքների ամբողջ հատվածը ts և $1 պարամետրն անցել է ինտերֆեյսից (նախորդ «վերջին» մատուցված արժեքը):
4. Եթե ​​բլոկը վերադարձվում է 26-ից պակաս գրառումներով, ապա դա վերջինն է:

Կամ նույն պատկերը.

Քանի որ հիմա մենք ունենք նմուշը չունի որևէ կոնկրետ «սկիզբ», ապա ոչինչ չի խանգարում մեզ «ընդլայնել» այս հարցումը հակառակ ուղղությամբ և իրականացնել տվյալների բլոկների դինամիկ բեռնում «հղման կետից» երկու ուղղություններով՝ ինչպես ներքև, այնպես էլ վերև:

Նշում.

1. Այո, այս դեպքում մենք երկու անգամ մուտք ենք գործում ինդեքս, բայց ամեն ինչ «զուտ ինդեքսով» է։ Հետևաբար, ենթահարկը միայն կհանգեցնի մեկ հավելյալ ինդեքսի սկանավորում.
2. Ակնհայտ է, որ այս տեխնիկան կարող է օգտագործվել միայն այն դեպքում, երբ դուք ունեք արժեքներ ts կարող է անցնել միայն պատահաբար, և դրանք շատ չեն. Եթե ​​ձեր բնորոշ դեպքը «միլիոն գրառում է 00:00:00.000-ին», դուք չպետք է դա անեք: Այսինքն՝ դուք չպետք է թույլ տաք, որ նման դեպք լինի։ Բայց եթե դա տեղի ունենա, օգտագործեք ընդլայնված ինդեքսով տարբերակը:

Source: www.habr.com