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 ნაჩვენები ჩანაწერი, პლუს 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