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

இங்கே என்ன நடந்து கொண்டிருக்கின்றது?

நாங்கள் 25 பதிவுகளை "கீழே" மற்றும் "எல்லை" மதிப்பைப் பெறுகிறோம் ts.
ஏற்கனவே எதுவும் இல்லை என்றால், NULL மதிப்பை மாற்றவும் -infinity.
பெறப்பட்ட மதிப்புக்கு இடையில் மதிப்புகளின் முழுப் பகுதியையும் கழிக்கிறோம் ts மற்றும் $1 அளவுரு இடைமுகத்திலிருந்து அனுப்பப்பட்டது (முந்தைய "கடைசி" வழங்கப்பட்ட மதிப்பு).
ஒரு தொகுதி 26 பதிவுகளுக்குக் குறைவாக இருந்தால், அதுவே கடைசி பதிவு.

அல்லது அதே படம்:

ஏனென்றால் இப்போது நம்மிடம் உள்ளது மாதிரியில் குறிப்பிட்ட "ஆரம்பம்" எதுவும் இல்லை, பின்னர் இந்த கோரிக்கையை எதிர் திசையில் "விரிவாக்க" மற்றும் "குறிப்பு புள்ளியில்" இருந்து தரவுத் தொகுதிகளின் மாறும் ஏற்றத்தை இரு திசைகளிலும் - கீழே மற்றும் மேலே செயல்படுத்துவதில் இருந்து எதுவும் நம்மைத் தடுக்காது.

கருத்து

ஆம், இந்த விஷயத்தில் நாம் குறியீட்டை இரண்டு முறை அணுகுகிறோம், ஆனால் எல்லாமே "முழுமையான குறியீட்டால்". எனவே, ஒரு துணை வினவல் மட்டுமே விளைவிக்கும் ஒரு கூடுதல் குறியீட்டிற்கு மட்டும் ஸ்கேன் செய்யவும்.
உங்களிடம் மதிப்புகள் இருக்கும்போது மட்டுமே இந்த நுட்பத்தைப் பயன்படுத்த முடியும் என்பது மிகவும் வெளிப்படையானது ts தற்செயலாக மட்டுமே கடக்க முடியும், மற்றும் அவற்றில் பல இல்லை. உங்கள் வழக்கமான வழக்கு "00:00:00.000 மணிக்கு ஒரு மில்லியன் பதிவுகள்" என்றால், நீங்கள் இதைச் செய்யக்கூடாது. அதாவது, இதுபோன்ற ஒரு வழக்கு நடக்க நீங்கள் அனுமதிக்கக்கூடாது. ஆனால் இது நடந்தால், நீட்டிக்கப்பட்ட குறியீட்டுடன் விருப்பத்தைப் பயன்படுத்தவும்.

ஆதாரம்: www.habr.com

PostgreSQL எதிர்ப்பு வடிவங்கள்: பதிவேட்டில் வழிசெலுத்துதல்