🥇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 OFFSET

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 تاسو ته راځي او "خوښ" دی چې ستاسو غوښتنه کوي دوی سرور د دوزخ په څیر د دوی د آفیسټ مقرراتو سره باروي، او په عموم کې ، دا د تیرولو وخت دی له وروستي ارزښت څخه نیویګیشن ښودل شوی. ستاسو پوښتنه بیا بدلیږي:

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;

دلته څه روان دي؟

موږ 25 "لاندې" ریکارډ کوو او د "حد" ارزښت ترلاسه کوو ts.
که چیرې دمخه هلته هیڅ شتون نلري، نو بیا د NULL ارزښت سره بدل کړئ -infinity.
موږ د ترلاسه شوي ارزښت تر مینځ د ارزښتونو ټوله برخه کموو ts او $1 پیرامیټر د انٹرفیس څخه تیر شوی (پخوانی "وروستی" وړاندې شوی ارزښت).
که یو بلاک د 26 څخه کم ریکارډونو سره بیرته راستانه شي، دا وروستی دی.

یا ورته انځور:

ځکه چې اوس موږ لرو نمونه کوم ځانګړی "پیل" نلري، بیا هیڅ شی موږ ته د دې غوښتنې په مخالف لوري کې "پراخولو" او د ډیټا بلاکونو متحرک بار کولو څخه په دواړو لورو کې د "ریفرنس پوائنټ" څخه پلي کولو څخه منع کوي - دواړه ښکته او پورته.

یادونه

هو، پدې حالت کې موږ دوه ځله شاخص ته لاس رسی لرو، مګر هر څه "په بشپړه توګه د شاخص له مخې" دي. نو ځکه، یو فرعي پوښتنه به یوازې پایله ولري یوازې یو اضافي شاخص سکین ته.
دا خورا څرګنده ده چې دا تخنیک یوازې هغه وخت کارول کیدی شي کله چې تاسو ارزښتونه ولرئ ts یوازې په تصادف سره تیریدلی شي، او ډیری یې شتون نلري. که ستاسو عادي قضیه "په 00:00:00.000 کې یو ملیون ریکارډونه" وي، تاسو باید دا کار ونه کړئ. زما مطلب دی، تاسو باید اجازه ورنکړو چې دا ډول قضیه ترسره شي. مګر که دا پیښ شي، د پراخ شوي شاخص سره اختیار وکاروئ.

سرچینه: www.habr.com

د PostgreSQL ضد نمونې: د راجسټری حرکت کول