PostgreSQL Antipatterns: रजिस्ट्री नेभिगेट गर्दै

आज SQL मा कुनै जटिल केसहरू र परिष्कृत एल्गोरिदमहरू हुनेछैनन्। सबै कुरा धेरै सरल हुनेछ, कप्तान स्पष्ट को स्तर मा - यो गरौं घटना रजिस्ट्री हेर्दै समय अनुसार क्रमबद्ध।

त्यो हो, डाटाबेसमा एउटा चिन्ह छ events, र उनीसँग एउटा क्षेत्र छ ts - ठ्याक्कै जुन समयमा हामी यी रेकर्डहरू व्यवस्थित रूपमा प्रदर्शन गर्न चाहन्छौं:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

यो स्पष्ट छ कि हामीसँग त्यहाँ दर्जनौं रेकर्डहरू छैनन्, त्यसैले हामीलाई केहि फारम चाहिन्छ पृष्ठ नेभिगेसन.

#०। "म मेरी आमाको पोग्रोमिस्ट हुँ"

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 प्रदर्शित रेकर्डहरू, प्लस 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;

#२। "कर्सर" मा स्विच गर्नुहोस्

केहि समय पछि, एक DBA तपाईंकहाँ आउँछ र तपाईंको अनुरोधमा "खुश" हुन्छ तिनीहरूले आफ्नो OFFSET नियमहरूसँग नरक जस्तै सर्भर लोड, र सामान्यतया, यो स्विच गर्ने समय हो देखाइएको अन्तिम मानबाट नेभिगेसन। तपाईंको प्रश्न फेरि म्युटेट हुन्छ:

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

त्यो नआउन्जेल तपाईले राहतको सास फेर्नुभयो ...

#३। सरसफाई सूचकांक

किनभने एक दिन तपाईको 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