ప్రోహోస్టర్ > బ్లాగ్ > పరిపాలన > PostgreSQL యాంటీప్యాటర్న్స్: రిజిస్ట్రీని నావిగేట్ చేయడం

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 మీ వద్దకు వస్తుంది మరియు మీరు కోరినందుకు "సంతోషించబడింది" వారు తమ ఆఫ్‌సెట్ నిబంధనలతో సర్వర్‌ను నరకం లాగా లోడ్ చేస్తారు, మరియు సాధారణంగా, ఇది మారడానికి సమయం చూపిన చివరి విలువ నుండి నావిగేషన్. మీ ప్రశ్న మళ్లీ పరివర్తన చెందుతుంది:

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 కంటే తక్కువ రికార్డ్‌లతో బ్లాక్‌ను తిరిగి ఇస్తే, అదే చివరిది.

లేదా అదే చిత్రం:
PostgreSQL యాంటీప్యాటర్న్స్: రిజిస్ట్రీని నావిగేట్ చేయడం

ఎందుకంటే ఇప్పుడు మన దగ్గర ఉంది నమూనాకు నిర్దిష్ట "ప్రారంభం" లేదు, అప్పుడు ఈ అభ్యర్థనను వ్యతిరేక దిశలో "విస్తరింపజేయడం" నుండి మరియు "రిఫరెన్స్ పాయింట్" నుండి డేటా బ్లాక్‌ల డైనమిక్ లోడింగ్‌ను రెండు దిశలలో - క్రిందికి మరియు పైకి అమలు చేయకుండా ఏదీ మమ్మల్ని నిరోధించదు.

వ్యాఖ్య

  1. అవును, ఈ సందర్భంలో మేము సూచికను రెండుసార్లు యాక్సెస్ చేస్తాము, కానీ ప్రతిదీ "పూర్తిగా ఇండెక్స్ ద్వారా" ఉంటుంది. అందువల్ల, ఉపప్రశ్న మాత్రమే ఫలితమౌతుంది ఒక అదనపు సూచికకు మాత్రమే స్కాన్ చేయండి.
  2. మీకు విలువలు ఉన్నప్పుడే ఈ టెక్నిక్ ఉపయోగించబడుతుందని చాలా స్పష్టంగా ఉంది ts అవకాశం ద్వారా మాత్రమే దాటవచ్చు, మరియు వాటిలో చాలా లేవు. మీ సాధారణ కేసు "00:00:00.000 వద్ద మిలియన్ రికార్డులు" అయితే, మీరు దీన్ని చేయకూడదు. నా ఉద్దేశ్యం, మీరు అలాంటి సందర్భాన్ని అనుమతించకూడదు. కానీ ఇది జరిగితే, పొడిగించిన సూచికతో ఎంపికను ఉపయోగించండి.

మూలం: www.habr.com

ఒక వ్యాఖ్యను జోడించండి