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 አንድ ሚሊዮን መዝገቦች" ከሆነ ይህን ማድረግ የለብዎትም። ማለቴ, እንደዚህ አይነት ጉዳይ እንዲከሰት መፍቀድ የለብዎትም. ነገር ግን ይህ ከተከሰተ, ከተራዘመ ኢንዴክስ ጋር ያለውን አማራጭ ይጠቀሙ.

ምንጭ: hab.com