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 ന് ഒരു ദശലക്ഷം റെക്കോർഡുകൾ" ആണെങ്കിൽ, നിങ്ങൾ ഇത് ചെയ്യാൻ പാടില്ല. ഞാൻ ഉദ്ദേശിക്കുന്നത്, അത്തരമൊരു കേസ് സംഭവിക്കാൻ നിങ്ങൾ അനുവദിക്കരുത്. എന്നാൽ ഇത് സംഭവിക്കുകയാണെങ്കിൽ, ഒരു വിപുലീകൃത സൂചിക ഉപയോഗിച്ച് ഓപ്ഷൻ ഉപയോഗിക്കുക.

അവലംബം: www.habr.com