పేజీల ప్రశ్నలలో OFFSET మరియు LIMITని ఉపయోగించడం మానుకోండి

డేటాబేస్ పనితీరును ఆప్టిమైజ్ చేయడం గురించి మీరు చింతించాల్సిన అవసరం లేని రోజులు పోయాయి. కాలం నిలబడదు. ప్రతి కొత్త టెక్ వ్యవస్థాపకుడు తమ చేతికి అందే మొత్తం డేటాను సేకరించేందుకు ప్రయత్నిస్తున్నప్పుడు, తదుపరి Facebookని సృష్టించాలని కోరుకుంటారు. వ్యాపారాలకు డబ్బు సంపాదించడంలో సహాయపడే మెరుగైన రైలు మోడల్‌లకు ఈ డేటా అవసరం. అటువంటి పరిస్థితులలో, ప్రోగ్రామర్లు భారీ మొత్తంలో సమాచారంతో త్వరగా మరియు విశ్వసనీయంగా పని చేయడానికి అనుమతించే APIలను సృష్టించాలి.

మీరు ఎప్పుడైనా అప్లికేషన్ లేదా డేటాబేస్ బ్యాకెండ్‌లను డిజైన్ చేస్తుంటే, పేజీల ప్రశ్నలను అమలు చేయడానికి మీరు బహుశా కోడ్‌ని వ్రాసి ఉండవచ్చు. ఉదాహరణకు, ఇలా:

SELECT * FROM table_name LIMIT 10 OFFSET 40

ఇది మార్గం?

అయితే మీరు మీ పేజినేషన్‌ను ఇలా చేసి ఉంటే, మీరు దీన్ని అత్యంత సమర్ధవంతంగా చేయలేదని చెప్పడానికి క్షమించండి.

మీరు నాకు అభ్యంతరం చెప్పాలనుకుంటున్నారా? మీరు చేయవచ్చు కాదు ఖర్చు చేయడానికి время. మందగింపు, Shopify и మిక్స్మాక్స్ నేను ఈ రోజు మాట్లాడాలనుకుంటున్న టెక్నిక్‌లను వారు ఇప్పటికే ఉపయోగిస్తున్నారు.

ఎన్నడూ ఉపయోగించని కనీసం ఒక బ్యాకెండ్ డెవలపర్‌ని పేరు పెట్టండి OFFSET и LIMIT పేజీల ప్రశ్నలను నిర్వహించడానికి. MVP (కనీస ఆచరణీయ ఉత్పత్తి) మరియు చిన్న మొత్తంలో డేటాను ఉపయోగించే ప్రాజెక్ట్‌లలో, ఈ విధానం చాలా వర్తిస్తుంది. ఇది "కేవలం పనిచేస్తుంది," మాట్లాడటానికి.

కానీ మీరు మొదటి నుండి నమ్మదగిన మరియు సమర్థవంతమైన వ్యవస్థలను సృష్టించాల్సిన అవసరం ఉంటే, అటువంటి సిస్టమ్‌లలో ఉపయోగించే డేటాబేస్‌లను ప్రశ్నించే సామర్థ్యం గురించి మీరు ముందుగానే జాగ్రత్త వహించాలి.

ఈ రోజు మనం సాధారణంగా ఉపయోగించే (చాలా చెడ్డ) పేజీల ప్రశ్న ఇంజిన్‌ల అమలులో ఉన్న సమస్యల గురించి మరియు అటువంటి ప్రశ్నలను అమలు చేసేటప్పుడు అధిక పనితీరును ఎలా సాధించాలనే దాని గురించి మాట్లాడుతాము.

OFFSET మరియు LIMITలో తప్పు ఏమిటి?

ఇప్పటికే చెప్పినట్లుగా, OFFSET и LIMIT పెద్ద మొత్తంలో డేటాతో పని చేయాల్సిన అవసరం లేని ప్రాజెక్ట్‌లలో వారు బాగా పని చేస్తారు.

డేటాబేస్ సర్వర్ మెమరీలో సరిపోని పరిమాణానికి పెరిగినప్పుడు సమస్య తలెత్తుతుంది. అయితే, ఈ డేటాబేస్తో పని చేస్తున్నప్పుడు, మీరు పేజీల ప్రశ్నలను ఉపయోగించాలి.

ఈ సమస్య మానిఫెస్ట్ కావాలంటే, ప్రతి పేజీలోని ప్రశ్నపై DBMS అసమర్థ పూర్తి టేబుల్ స్కాన్ ఆపరేషన్‌ను ఆశ్రయించే పరిస్థితి తప్పనిసరిగా ఉండాలి (చొప్పించడం మరియు తొలగింపు కార్యకలాపాలు సంభవించవచ్చు మరియు మాకు పాత డేటా అవసరం లేదు!).

“పూర్తి టేబుల్ స్కాన్” (లేదా “సీక్వెన్షియల్ టేబుల్ స్కాన్”, సీక్వెన్షియల్ స్కాన్) అంటే ఏమిటి? ఇది ఒక ఆపరేషన్, ఈ సమయంలో DBMS పట్టికలోని ప్రతి అడ్డు వరుసను, అంటే దానిలో ఉన్న డేటాను వరుసగా చదువుతుంది మరియు ఇచ్చిన షరతుకు అనుగుణంగా వాటిని తనిఖీ చేస్తుంది. ఈ రకమైన టేబుల్ స్కాన్ చాలా నెమ్మదిగా ఉంటుంది. వాస్తవం ఏమిటంటే ఇది అమలు చేయబడినప్పుడు, సర్వర్ యొక్క డిస్క్ సబ్‌సిస్టమ్‌ను కలిగి ఉన్న అనేక ఇన్‌పుట్/అవుట్‌పుట్ కార్యకలాపాలు నిర్వహించబడతాయి. డిస్క్‌లలో నిల్వ చేయబడిన డేటాతో పని చేయడంతో సంబంధం ఉన్న జాప్యం కారణంగా పరిస్థితి మరింత దిగజారింది మరియు డిస్క్ నుండి మెమరీకి డేటాను బదిలీ చేయడం అనేది రిసోర్స్-ఇంటెన్సివ్ ఆపరేషన్.

ఉదాహరణకు, మీరు 100000000 మంది వినియోగదారుల రికార్డులను కలిగి ఉన్నారు మరియు మీరు నిర్మాణంతో ప్రశ్నను అమలు చేస్తారు OFFSET 50000000. దీనర్థం DBMS ఈ రికార్డులన్నింటినీ లోడ్ చేయాల్సి ఉంటుంది (మరియు మనకు అవి కూడా అవసరం లేదు!), వాటిని మెమరీలో ఉంచండి మరియు ఆ తర్వాత 20 ఫలితాలు నివేదించబడ్డాయి LIMIT.

ఇది ఇలా ఉండవచ్చని అనుకుందాం: "50000 నుండి 50020 నుండి 100000 వరకు అడ్డు వరుసలను ఎంచుకోండి". అంటే, ప్రశ్నను పూర్తి చేయడానికి సిస్టమ్ మొదట 50000 వరుసలను లోడ్ చేయాల్సి ఉంటుంది. ఆమె ఎంత అనవసరమైన పని చేస్తుందో మీరు చూశారా?

మీరు నన్ను నమ్మకపోతే, నేను లక్షణాలను ఉపయోగించి సృష్టించిన ఉదాహరణను చూడండి db-fiddle.com. 

db-fiddle.comలో ఉదాహరణ

అక్కడ, ఎడమవైపు, మైదానంలో Schema SQL, డేటాబేస్‌లో 100000 అడ్డు వరుసలను మరియు కుడి వైపున ఫీల్డ్‌లో చేర్చే కోడ్ ఉంది Query SQL, రెండు ప్రశ్నలు చూపబడ్డాయి. మొదటి, నెమ్మదిగా, ఇలా కనిపిస్తుంది:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

మరియు రెండవది, అదే సమస్యకు సమర్థవంతమైన పరిష్కారం, ఇది ఇలా ఉంటుంది:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

ఈ అభ్యర్థనలను నెరవేర్చడానికి, బటన్‌పై క్లిక్ చేయండి Run పేజీ ఎగువన. దీన్ని పూర్తి చేసిన తర్వాత, మేము ప్రశ్న అమలు సమయం గురించి సమాచారాన్ని సరిపోల్చాము. పనికిరాని ప్రశ్నను అమలు చేయడం రెండవదాన్ని అమలు చేయడం కంటే కనీసం 30 రెట్లు ఎక్కువ సమయం తీసుకుంటుందని తేలింది (ఈ సమయం రన్ నుండి రన్‌కు మారుతుంది; ఉదాహరణకు, మొదటి ప్రశ్న పూర్తి కావడానికి 37 ఎంఎస్‌లు పట్టిందని సిస్టమ్ నివేదించవచ్చు, అయితే దాని అమలు రెండవ - 1 ms).

ఇంకా ఎక్కువ డేటా ఉంటే, ప్రతిదీ మరింత అధ్వాన్నంగా కనిపిస్తుంది (దీనిని ఒప్పించాలంటే, నా వైపు చూడండి ఒక ఉదాహరణ 10 మిలియన్ వరుసలతో).

మేము ఇప్పుడే చర్చించినవి మీకు డేటాబేస్ ప్రశ్నలు ఎలా ప్రాసెస్ చేయబడతాయో కొంత అంతర్దృష్టిని అందించాలి.

దయచేసి ఎక్కువ విలువ అని గమనించండి OFFSET - అభ్యర్థన పూర్తి కావడానికి ఎక్కువ సమయం పడుతుంది.

OFFSET మరియు LIMIT కలయికకు బదులుగా నేను ఏమి ఉపయోగించాలి?

కలయికకు బదులుగా OFFSET и LIMIT కింది పథకం ప్రకారం నిర్మించిన నిర్మాణాన్ని ఉపయోగించడం విలువ:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

ఇది కర్సర్ ఆధారిత పేజినేషన్‌తో క్వెరీ ఎగ్జిక్యూషన్.

కరెంట్‌ను స్థానికంగా నిల్వ చేయడానికి బదులుగా OFFSET и LIMIT మరియు ప్రతి అభ్యర్థనతో వాటిని ప్రసారం చేయండి, మీరు చివరిగా అందుకున్న ప్రాథమిక కీని నిల్వ చేయాలి (సాధారణంగా ఇది ID) మరియు LIMIT, ఫలితంగా, పైన పేర్కొన్న వాటికి సమానమైన ప్రశ్నలు పొందబడతాయి.

ఎందుకు? పాయింట్ ఏమిటంటే, చివరి వరుస చదివిన ఐడెంటిఫైయర్‌ను స్పష్టంగా పేర్కొనడం ద్వారా, మీరు మీ DBMSకి అవసరమైన డేటా కోసం వెతకడం ఎక్కడ ప్రారంభించాలో చెప్పండి. అంతేకాకుండా, శోధన, కీని ఉపయోగించినందుకు కృతజ్ఞతలు, సమర్ధవంతంగా నిర్వహించబడుతుంది; సిస్టమ్ పేర్కొన్న పరిధికి వెలుపల ఉన్న పంక్తుల ద్వారా పరధ్యానంలో ఉండవలసిన అవసరం లేదు.

వివిధ ప్రశ్నల యొక్క క్రింది పనితీరు పోలికను పరిశీలిద్దాం. ఇక్కడ పనికిరాని ప్రశ్న ఉంది.

నెమ్మదిగా అభ్యర్థన

మరియు ఈ అభ్యర్థన యొక్క ఆప్టిమైజ్ చేసిన వెర్షన్ ఇక్కడ ఉంది.

త్వరిత అభ్యర్థన

రెండు ప్రశ్నలు సరిగ్గా ఒకే మొత్తంలో డేటాను అందిస్తాయి. కానీ మొదటిది పూర్తి చేయడానికి 12,80 సెకన్లు పడుతుంది, మరియు రెండవది 0,01 సెకన్లు పడుతుంది. మీకు తేడా అనిపిస్తుందా?

సాధ్యమయ్యే సమస్యలు

ప్రతిపాదిత ప్రశ్న పద్ధతి ప్రభావవంతంగా పని చేయడానికి, పట్టిక తప్పనిసరిగా పూర్ణాంకం ఐడెంటిఫైయర్ వంటి ప్రత్యేకమైన, వరుస సూచికలను కలిగి ఉండే నిలువు వరుస (లేదా నిలువు వరుసలు) కలిగి ఉండాలి. కొన్ని నిర్దిష్ట సందర్భాల్లో, డేటాబేస్తో పని చేసే వేగాన్ని పెంచడానికి ఇటువంటి ప్రశ్నలను ఉపయోగించడం యొక్క విజయాన్ని ఇది నిర్ణయించవచ్చు.

సహజంగానే, ప్రశ్నలను నిర్మించేటప్పుడు, మీరు పట్టికల యొక్క నిర్దిష్ట నిర్మాణాన్ని పరిగణనలోకి తీసుకోవాలి మరియు ఇప్పటికే ఉన్న పట్టికలలో ఉత్తమంగా పనిచేసే ఆ యంత్రాంగాలను ఎంచుకోవాలి. ఉదాహరణకు, మీరు సంబంధిత డేటా యొక్క పెద్ద వాల్యూమ్‌లతో ప్రశ్నలలో పని చేయవలసి వస్తే, మీరు దానిని ఆసక్తికరంగా కనుగొనవచ్చు ఈ వ్యాసం.

మేము ప్రాథమిక కీని కోల్పోయే సమస్యను ఎదుర్కొన్నట్లయితే, ఉదాహరణకు, మనకు అనేక నుండి అనేక సంబంధాలతో పట్టిక ఉంటే, అప్పుడు ఉపయోగించే సంప్రదాయ విధానం OFFSET и LIMIT, మాకు సరిపోతుందని హామీ ఇవ్వబడింది. కానీ దాని ఉపయోగం నెమ్మదిగా ప్రశ్నలకు దారితీయవచ్చు. అటువంటి సందర్భాలలో, పేజినేట్ క్వెరీలను నిర్వహించడానికి మాత్రమే అవసరమైనప్పటికీ, ఆటో-ఇంక్రిమెంటింగ్ ప్రైమరీ కీని ఉపయోగించమని నేను సిఫార్సు చేస్తాను.

మీకు ఈ అంశంపై ఆసక్తి ఉంటే - ఇక్కడ, ఇక్కడ и ఇక్కడ - అనేక ఉపయోగకరమైన పదార్థాలు.

ఫలితాలు

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

మీరు డేటాబేస్ ప్రశ్నలను ఎలా విశ్లేషిస్తారు మరియు ఆప్టిమైజ్ చేస్తారు?

మూలం: www.habr.com