PostgreSQL Antipatterns- Registry ကို လမ်းညွှန်ခြင်း။

ယနေ့ SQL တွင်ရှုပ်ထွေးသောကိစ္စများနှင့်ခေတ်မီသော algorithms များရှိမည်မဟုတ်ပါ။ Captain သိသာထင်ရှားတဲ့အဆင့်မှာ အရာအားလုံးက အရမ်းရိုးရှင်းပါလိမ့်မယ် - အဲဒါကို လုပ်ကြရအောင် event registry ကိုကြည့်ရှုခြင်း။ အချိန်အားဖြင့် စီထားသည်။

ဆိုလိုသည်မှာ ဒေတာဘေ့စ်တွင် ဆိုင်းဘုတ်တစ်ခုရှိသည်။ 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 နှင့် "တိုက်ရိုက်" အလုပ်သို့ပြောင်းရန် ခက်ခဲနိုင်သည်။

ဒါပေမယ့် ပိုအဖြစ်များပြီး လူသိနည်းတဲ့ ပြဿနာတွေကို ဆက်သွားကြည့်ရအောင်။

#၁။ အော့ဖ်ဆက်

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

နံပါတ် ၂၆ က ဘယ်ကလာတာလဲ။ ဤသည်မှာ ဖန်သားပြင်တစ်ခုအား ဖြည့်ရန် အနီးစပ်ဆုံး အရေအတွက်ဖြစ်သည်။ ပို၍တိကျသည်မှာ၊ ပြသထားသော မှတ်တမ်း 26 ခု နှင့် 25 သည် နမူနာတွင် အနည်းဆုံး နောက်ထပ် တစ်ခုခု ရှိနေကြောင်း အချက်ပြပြီး ရှေ့ဆက်ရန် အဓိပ္ပာယ် ရှိပါသည်။

ဟုတ်ပါတယ်၊ ဒီတန်ဖိုးကို တောင်းဆိုမှုရဲ့ကိုယ်ထည်မှာ “ချုပ်” လို့ မရပါဘူး၊ ဒါပေမယ့် ကန့်သတ်ချက်တစ်ခုကို ဖြတ်သန်းပါတယ်။ သို့သော်ဤကိစ္စတွင်၊ PostgreSQL အစီအစဉ်ဆွဲသူသည် မှတ်တမ်းအနည်းငယ်သာရှိသင့်သည်ဟူသော အသိပညာအပေါ် အားကိုးနိုင်မည်မဟုတ်ပေ - ထိရောက်မှုမရှိသောအစီအစဉ်ကို အလွယ်တကူရွေးချယ်နိုင်မည်ဖြစ်သည်။

အက်ပလီကေးရှင်းမျက်နှာပြင်တွင်ရှိနေစဉ်၊ registry ကိုကြည့်ရှုခြင်းကိုမြင်သာသော“ စာမျက်နှာများ” များအကြားပြောင်းခြင်းအဖြစ်လုပ်ဆောင်သည်၊ မည်သူမျှသံသယဖြစ်စရာကိုအကြာကြီးသတိမထားမိပါ။ အဆင်ပြေစေရန်အတွက် ရုန်းကန်မှုတွင် UI/UX သည် အင်တာဖေ့စ်ကို "အဆုံးမဲ့ scroll" အဖြစ် ပြန်လည်ဖန်တီးရန် ဆုံးဖြတ်သည် - ဆိုလိုသည်မှာ၊ အသုံးပြုသူသည် အပေါ်နှင့်အောက် ရွှေ့နိုင်သော စာရင်းတစ်ခုတည်းတွင် မှတ်ပုံတင်ခြင်းအားလုံးကို ရေးဆွဲထားသည်။

ထို့ကြောင့် နောက်တစ်ကြိမ် စမ်းသပ်မှုအတွင်း သင်သည် ဖမ်းမိသွားမည်ဖြစ်သည်။ မှတ်တမ်းများပွားခြင်း။ registry တွင်။ အဘယ့်ကြောင့်ဆိုသော် ဇယားတွင် ပုံမှန်အညွှန်းတစ်ခုရှိသည်။ (ts)သင်၏မေးမြန်းချက်သည် မည်သည့်အရာကို အားကိုးနေသနည်း။

အဲဒါကို သေချာ ထည့်မတွက်ထားလို့ပါ။ ts ထူးခြားသောသော့မဟုတ်ပါ။ ဤဇယား၌။ တကယ်တော့၊ နှင့် ၎င်း၏တန်ဖိုးများသည်ထူးခြားသည်မဟုတ်။လက်တွေ့အခြေအနေများတွင် မည်သည့် "အချိန်" ကဲ့သို့ဖြစ်သည် - ထို့ကြောင့်၊ ကပ်လျက်မေးမြန်းချက်နှစ်ခုရှိ တူညီသောမှတ်တမ်းသည် တူညီသောသော့တန်ဖိုးကိုစီရန်ဘောင်အတွင်း မတူညီသောနောက်ဆုံးအမှာစာတစ်ခုကြောင့် စာမျက်နှာတစ်ခုမှတစ်ခုသို့ လွယ်ကူစွာ "ခုန်"သွားပါသည်။

တကယ်တော့၊ ဒီနေရာမှာ သတိထားရပိုခက်တဲ့ ဒုတိယပြဿနာတစ်ခုလည်း ရှိပါသေးတယ်။ အချို့သောအကြောင်းအရာများကို ပြသမည်မဟုတ်ပါ။ လုံးဝ! နောက်ဆုံးတွင်၊ "မိတ္တူ" မှတ်တမ်းများသည် အခြားသူတစ်ဦး၏နေရာသို့ ရောက်သွားပါသည်။ လှပသောပုံများနှင့်အတူ အသေးစိတ်ရှင်းလင်းချက်ကို တွေ့နိုင်ပါသည်။ ဒီမှာဖတ်ပါ.

အညွှန်းကိုချဲ့ထွင်ခြင်း။

လိမ္မာသောဆော့ဖ်ဝဲရေးသားသူသည် အညွှန်းကိန်းကို ထူးခြားအောင်ပြုလုပ်ရန် လိုအပ်ကြောင်း နားလည်သဘောပေါက်ပြီး အလွယ်ဆုံးနည်းလမ်းမှာ PK အတွက် ပြီးပြည့်စုံသော သိသာထင်ရှားသောထူးခြားသောနယ်ပယ်တစ်ခုဖြင့် ၎င်းကိုချဲ့ထွင်ရန်ဖြစ်သည်-

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

တောင်းဆိုချက်သည် ပြောင်းလဲသွားသည်-

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

#၂။ "ကာဆာများ" သို့ ပြောင်းပါ

အချိန်အနည်းငယ်ကြာသောအခါ၊ DBA သည် သင့်ထံရောက်လာပြီး သင့်တောင်းဆိုမှုများကို "ကျေနပ်" ပါသည်။ ၎င်းတို့သည် ၎င်းတို့၏ OFFSET စည်းမျဉ်းများဖြင့် ငရဲကဲ့သို့ ဆာဗာကို ဆွဲချသည်။၊ ယေဘုယျအားဖြင့်၊ သို့ပြောင်းရန်အချိန်ဖြစ်သည်။ ပြထားသော နောက်ဆုံးတန်ဖိုးမှ လမ်းညွှန်ချက်. သင့်မေးမြန်းချက်သည် ထပ်မံပြောင်းလဲသွားသည်-

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

မင်းရောက်လာတဲ့အထိ သက်ပြင်းတစ်ချက်ချလိုက်တယ်...

#၃။ သန့်ရှင်းရေးအညွှန်းများ

ဘာကြောင့်လဲဆိုတော့ တစ်နေ့မှာ မင်းရဲ့ DBA က ဖတ်တယ်။ ထိရောက်မှုမရှိသော အညွှန်းများကို ရှာဖွေခြင်းအကြောင်း ဆောင်းပါး အဲဒါကို သဘောပေါက်တယ်။ “နောက်ဆုံးပေါ်မဟုတ်ဘူး” အချိန်တံဆိပ်က မကောင်းပါဘူး။. ပြီးတော့ ငါမင်းဆီ တစ်ဖန်လာခဲ့တယ်- အခု ဒီညွှန်းကိန်းကို ပြန်ပြောင်းရတော့မယ် ဆိုတဲ့ အတွေးနဲ့ အခုပဲ မင်းဆီရောက်လာတယ်။ (ts DESC).

သို့သော် စာမျက်နှာများကြားတွင် "ခုန်ခြင်း" မှတ်တမ်းများ၏ ကနဦးပြဿနာနှင့် မည်သို့လုပ်ဆောင်ရမည်နည်း။ ။ အရာအားလုံးသည် ရိုးရှင်းပါသည် - မရေမတွက်နိုင်သော မှတ်တမ်းအရေအတွက်ဖြင့် ပိတ်ဆို့မှုများကို သင်ရွေးချယ်ရန်လိုအပ်ပါသည်။

ယေဘူယျအားဖြင့်၊ “၂၆ အတိအကျ” မဟုတ်ဘဲ “၂၆ ထက်မနည်း” မဖတ်ဖို့ ဘယ်သူက တားမြစ်တာလဲ။ ဥပမာ- ဒါမှ နောက်ဘလောက်မှာ ရှိတာ။ ထင်ရှားစွာကွဲပြားသော အဓိပ္ပါယ်များဖြင့် မှတ်တမ်းတင်ထားသည်။ ts - ထို့နောက် blocks များကြားတွင် "ခုန်ခြင်း" မှတ်တမ်းများနှင့် ပြဿနာရှိမည်မဟုတ်ပါ။

ဒါကို အောင်မြင်အောင် ဘယ်လိုလုပ်ရမလဲ၊

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. ဟုတ်ကဲ့၊ ဤအခြေအနေတွင် ကျွန်ုပ်တို့သည် အညွှန်းကို နှစ်ကြိမ်ဝင်ရောက်ကြည့်ရှုနိုင်သော်လည်း အရာအားလုံးသည် "အညွှန်းအားဖြင့်သာ" ဖြစ်သည်။ ထို့ကြောင့်၊ subquery သည်သာ ရလဒ်ထွက်လာလိမ့်မည်။ နောက်ထပ် Index Only Scan တစ်ခုသို့.
2. သင့်တွင် တန်ဖိုးများရှိမှသာ ဤနည်းပညာကို အသုံးပြုနိုင်မည်မှာ သိသာထင်ရှားပါသည်။ ts အခွင့်အလမ်းနဲ့သာ ဖြတ်ကျော်နိုင်တယ်။ အများအပြားမရှိပါ။. သင်၏ပုံမှန်ကိစ္စသည် "00:00:00.000" တွင် "သန်းပေါင်းများစွာသောစံချိန်" ဖြစ်ပါက၊ သင်သည် ဒါကိုမလုပ်သင့်ပါ။ ဆိုလိုတာက ဒီလိုကိစ္စမျိုးတွေ ဖြစ်မလာသင့်ဘူး။ သို့သော် ထိုသို့ဖြစ်လာပါက၊ တိုးချဲ့အညွှန်းတစ်ခုဖြင့် ရွေးချယ်မှုကို အသုံးပြုပါ။

source: www.habr.com