PostgreSQL Antipatterns: රෙජිස්ට්රි සැරිසැරීම

අද 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. OFFSET

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

අංක 26 පැමිණියේ කොහෙන්ද? මෙය එක් තිරයක් පිරවීම සඳහා ආසන්න වශයෙන් ඇතුළත් කිරීම් ගණනයි. වඩාත් නිවැරදිව, ප්රදර්ශනය කරන ලද වාර්තා 25 ක්, plus 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