👩🏿‍⚕️ 👨🏿‍🎓 👨‍✈️ डॉक्ट्रीन में डीबी कर्सर 👨🏾‍⚖️ 🖖🏼 🤲🏼

कर्सर का उपयोग करके, आप डेटाबेस से प्राप्त कर सकते हैं और एप्लिकेशन मेमोरी को बर्बाद किए बिना बड़ी मात्रा में डेटा संसाधित कर सकते हैं। मुझे यकीन है कि प्रत्येक वेब डेवलपर को कम से कम एक बार एक समान कार्य का सामना करना पड़ा है, और मेरे सामने सिर्फ एक बार नहीं। इस लेख में मैं आपको बताऊंगा कि कर्सर किन कार्यों में उपयोगी हो सकते हैं, और मैं PostrgeSQL के उदाहरण का उपयोग करके PHP + Doctrine से उनके साथ काम करने के लिए तैयार कोड दे दूंगा।

समस्या

आइए कल्पना करें कि हमारे पास एक PHP परियोजना है, जो बड़ी और भारी है। निश्चित रूप से, यह कुछ ढांचे की मदद से लिखा गया था। उदाहरण के लिए, सिम्फनी। यह डेटाबेस का भी उपयोग करता है, उदाहरण के लिए, PostgreSQL, और डेटाबेस में ऑर्डर के बारे में जानकारी के साथ 2,000,000 रिकॉर्ड के लिए एक प्लेट है। और परियोजना स्वयं इन आदेशों का एक इंटरफ़ेस है, जो उन्हें प्रदर्शित और फ़िल्टर कर सकता है। और, मुझे कहने दो, यह बहुत अच्छा कर रहा है।

अब हमसे पूछा गया (क्या आपसे अभी तक नहीं पूछा गया है? उन्हें निश्चित रूप से पूछा जाएगा) एक्सेल फाइल में फ़िल्टरिंग ऑर्डर के परिणाम को अपलोड करने के लिए। आइए एक टेबल आइकन के साथ एक बटन को कोड़ा, जो उपयोगकर्ता के आदेश के साथ एक फ़ाइल को थूक देगा।

यह आमतौर पर कैसे तय किया जाता है, और यह खराब क्यों है?

एक प्रोग्रामर कैसे करता है जिसने अभी तक इस तरह के कार्य का सामना नहीं किया है। वह डेटाबेस में SELECT करता है, क्वेरी के परिणामों को पढ़ता है, प्रतिक्रिया को एक्सेल फाइल में परिवर्तित करता है और उपयोगकर्ता के ब्राउज़र को देता है। कार्य काम करता है, परीक्षण पूरा हो गया है, लेकिन उत्पादन में समस्याएं शुरू होती हैं।

निश्चित रूप से, हमने PHP के लिए कुछ उचित (यकीनन) प्रति प्रक्रिया 1 जीबी की मेमोरी सीमा निर्धारित की है, और जैसे ही ये 2 मिलियन लाइनें अब इस गीगाबाइट में फिट नहीं होती हैं, सब कुछ टूट जाता है। PHP एक "मेमोरी से बाहर" त्रुटि के साथ क्रैश होती है, और उपयोगकर्ता शिकायत करते हैं कि फ़ाइल अपलोड नहीं हुई है। ऐसा इसलिए होता है क्योंकि हमने डेटाबेस से डेटा को अनलोड करने के लिए एक बहुत ही अच्छा तरीका चुना है - उन सभी को पहले डेटाबेस मेमोरी (और इसके तहत डिस्क) से PHP प्रक्रिया की रैम में स्थानांतरित किया जाता है, फिर उन्हें संसाधित किया जाता है और ब्राउज़र पर अपलोड किया जाता है।

यह सुनिश्चित करने के लिए कि डेटा हमेशा मेमोरी में संग्रहीत होता है, आपको इसे डेटाबेस से टुकड़ों में लेने की आवश्यकता है। उदाहरण के लिए, 10,000 रिकॉर्ड पढ़े गए, संसाधित किए गए, एक फ़ाइल में लिखे गए, और कई बार।

ठीक है, उस प्रोग्रामर के बारे में सोचता है जो पहली बार हमारे कार्य से मिला था। फिर मैं एक लूप बनाऊंगा और टुकड़ों में क्वेरी के परिणामों का बचाव करूँगा, जो लिमिट और ओएफएसईटीटी को दर्शाता है। यह काम करता है, लेकिन यह डेटाबेस के लिए एक बहुत महंगा ऑपरेशन है, और इसलिए रिपोर्ट अपलोड करने में 30 सेकंड नहीं लगते हैं, लेकिन 30 मिनट (यह इतना बुरा नहीं है!)। वैसे, अगर इस समय OFFSET के अलावा प्रोग्रामर के दिमाग में ज्यादा कुछ नहीं आता है, तो डेटाबेस को रैप किए बिना ही इसे हासिल करने के कई तरीके हैं ।

एक ही समय में, डेटाबेस में स्वयं से डेटा को पढ़ने की क्षमता है - कर्सर।

कर्सर

एक कर्सर डेटाबेस में रहने वाले क्वेरी के परिणामों में एक पंक्ति का एक संकेतक है। उनका उपयोग करते समय, हम डेटा को तुरंत डाउनलोड करने के मोड में नहीं चयन कर सकते हैं, लेकिन इस चयन के साथ कर्सर खोलें। अगला, हम डेटाबेस से डेटा प्रवाह प्राप्त करना शुरू करते हैं क्योंकि कर्सर आगे बढ़ता है। यह हमें एक ही परिणाम देता है: हम डेटा को भागवत रूप से पढ़ते हैं, लेकिन डेटाबेस उस लाइन को खोजने का एक ही काम नहीं करता है जिसके साथ इसे शुरू करने की आवश्यकता होती है, जैसा कि OFFSET के साथ होता है।

कर्सर केवल लेन-देन के अंदर खोला जाता है और तब तक रहता है जब तक लेनदेन जीवित है (एक अपवाद है, होल्ड के साथ देखें)। इसका मतलब है कि अगर हम धीरे-धीरे डेटाबेस से बहुत सारे डेटा पढ़ते हैं, तो हमारे पास एक लंबा लेनदेन होगा। यह कभी-कभी बुरा होता है , आपको इस जोखिम को समझने और स्वीकार करने की आवश्यकता है।

डॉक्ट्रिन में कर्सर

आइए सिद्धांत के साथ काम करने की कोशिश करते हैं। सबसे पहले, एक कर्सर खोलने के लिए एक अनुरोध कैसा दिखता है?

BEGIN; DECLARE mycursor1 CURSOR FOR ( SELECT * FROM huge_table );

DECLARE निर्दिष्ट चयन क्वेरी के लिए कर्सर बनाता है और खोलता है। कर्सर बनाने के बाद, आप इससे डेटा पढ़ना शुरू कर सकते हैं:

 FETCH FORWARD 10000 FROM mycursor1; < 10 000 > FETCH FORWARD 10000 FROM mycursor1; <  10 000 > ...

और इसी तरह, जब तक FETCH एक खाली सूची नहीं देता। इसका मतलब यह होगा कि उन्होंने अंत तक स्क्रॉल किया।

 COMMIT;

हम सिद्धांत के साथ संगत एक वर्ग को रेखांकित करते हैं जो कर्सर के साथ काम को अंजाम देगा। और 20% समय में 80% समस्या को हल करने के लिए , यह केवल मूल निवासी क्वेरी के साथ काम करेगा। तो चलिए इसे PgSqlNativeQueryCursor कहते हैं।

डिजाइनर:

 public function __construct(NativeQuery $query) { $this->query = $query; $this->connection = $query->getEntityManager()->getConnection(); $this->cursorName = uniqid('cursor_'); assert($this->connection->getDriver() instanceof PDOPgSqlDriver); }

यहां मैं भविष्य के कर्सर के लिए एक नाम उत्पन्न करता हूं।

चूँकि कक्षा में PostgreSQL-विशिष्ट SQL कोड है, इसलिए यह जाँचना बेहतर है कि हमारा ड्राइवर PG है।

कक्षा से हमें तीन चीजें चाहिए:

कर्सर को खोलने में सक्षम हो।
हमारे लिए डेटा वापस करने में सक्षम हो।
कर्सर को बंद करने में सक्षम हो।

कर्सर खोलें:

 public function openCursor() { if ($this->connection->getTransactionNestingLevel() === 0) { throw new \BadMethodCallException('Cursor must be used inside a transaction'); } $query = clone $this->query; $query->setSQL(sprintf( 'DECLARE %s CURSOR FOR (%s)', $this->connection->quoteIdentifier($this->cursorName), $this->query->getSQL() )); $query->execute($this->query->getParameters()); $this->isOpen = true; }

जैसा कि मैंने कहा, एक लेन-देन में अभिशाप खुले। इसलिए, यहां मैं जांच करता हूं कि हम पहले से ही खुले लेनदेन के अंदर इस पद्धति पर कॉल करना नहीं भूल गए हैं। (भगवान का शुक्र है, वह समय बीत चुका है जब मुझे यहीं लेन-देन खोलने के लिए तैयार किया जाएगा!)

एक नया NativeQuery बनाने और शुरू करने के कार्य को सरल बनाने के लिए, मैं बस एक को क्लोन करता हूं जिसे कंस्ट्रक्टर में खिलाया गया था और इसे DECLARE ... CURSOR FOR (here_original_query) में लपेटें। मैं इसे बाहर ले जाता हूं।

चलो getFetchQuery विधि बनाते हैं। यह डेटा नहीं लौटाएगा, लेकिन एक अन्य अनुरोध जिसका उपयोग आप कर सकते हैं जैसे कि दिए गए बैचों में वांछित डेटा प्राप्त करना है। इससे कॉलिंग कोड को अधिक स्वतंत्रता मिलती है।

 public function getFetchQuery(int $count = 1): NativeQuery { $query = clone $this->query; $query->setParameters([]); $query->setSQL(sprintf( 'FETCH FORWARD %d FROM %s', $count, $this->connection->quoteIdentifier($this->cursorName) )); return $query; }

विधि का एक पैरामीटर है - यह बंडल का आकार है, जो इस विधि द्वारा वापस किए गए अनुरोध का हिस्सा बन जाएगा। मैं क्वेरी क्लोनिंग के साथ एक ही चाल लागू करता हूं, इसमें मापदंडों को अधिलेखित करता हूं और SQL को FETCH ... FROM ..., निर्माण के साथ बदल देता हूं।

GetFetchQuery () को अचानक कॉल करने से पहले कर्सर को खोलने के लिए मत भूलना (अचानक मुझे पर्याप्त नींद नहीं मिलेगी), मैं सीधे getFetchQuery () विधि में इसका एक प्रारंभिक उद्घाटन करूंगा:

 public function getFetchQuery(int $count = 1): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } …

और OpenCursor () पद्धति को ही निजी बनाया जाएगा। जब मुझे स्पष्ट रूप से कॉल करने की आवश्यकता हो, मैं उन सभी मामलों को नहीं देखूंगा।

GetFetchQuery () के अंदर, मैं कर्सर को आगे ले जाने के लिए हार्ड-कोडेड फॉरवर्ड को दी गई लाइनों की संख्या को आगे बढ़ाता हूं। लेकिन FETCH कॉलिंग मोड कई अलग हैं। चलिए उन्हें भी जोड़ते हैं?

 const DIRECTION_NEXT = 'NEXT'; const DIRECTION_PRIOR = 'PRIOR'; const DIRECTION_FIRST = 'FIRST'; const DIRECTION_LAST = 'LAST'; const DIRECTION_ABSOLUTE = 'ABSOLUTE'; // with count const DIRECTION_RELATIVE = 'RELATIVE'; // with count const DIRECTION_FORWARD = 'FORWARD'; // with count const DIRECTION_FORWARD_ALL = 'FORWARD ALL'; const DIRECTION_BACKWARD = 'BACKWARD'; // with count const DIRECTION_BACKWARD_ALL = 'BACKWARD ALL';

उनमें से आधे पैरामीटर में लाइनों की संख्या को स्वीकार करते हैं, और अन्य आधे नहीं करते हैं। यहाँ मुझे क्या मिला है:

 public function getFetchQuery(int $count = 1, string $direction = self::DIRECTION_FORWARD): NativeQuery { if (!$this->isOpen) { $this->openCursor(); } $query = clone $this->query; $query->setParameters([]); if ( $direction == self::DIRECTION_ABSOLUTE || $direction == self::DIRECTION_RELATIVE || $direction == self::DIRECTION_FORWARD || $direction == self::DIRECTION_BACKWARD ) { $query->setSQL(sprintf( 'FETCH %s %d FROM %s', $direction, $count, $this->connection->quoteIdentifier($this->cursorName) )); } else { $query->setSQL(sprintf( 'FETCH %s FROM %s', $direction, $this->connection->quoteIdentifier($this->cursorName) )); } return $query; }

CLOSE के साथ कर्सर को बंद करें, लेन-देन के पूरा होने की प्रतीक्षा करना आवश्यक नहीं है:

 public function close() { if (!$this->isOpen) { return; } $this->connection->exec('CLOSE ' . $this->connection->quoteIdentifier($this->cursorName)); $this->isOpen = false; }

नाशक:

 public function __destruct() { if ($this->isOpen) { $this->close(); } }

यहाँ सम्पूर्ण वर्ग अपनी संपूर्णता में है । चलो कार्रवाई में कोशिश करते हैं?

मैं कुछ सशर्त XLSX में कुछ सशर्त लेखक खोलता हूं।

 $writer->openToFile($targetFile);

यहां मुझे डेटाबेस से ऑर्डर की सूची खींचने के लिए NativeQuery मिलता है।

 /** @var NativeQuery $query */ $query = $this->getOrdersRepository($em) ->getOrdersFiltered($dateFrom, $dateTo, $filters);

इस क्वेरी के आधार पर, मैं एक कर्सर घोषित करता हूं।

 $cursor = new PgSqlNativeQueryCursor($query);

और उसके लिए मुझे 10,000 लाइनों के बैचों में डेटा प्राप्त करने का अनुरोध मिलता है।

 $fetchQuery = $cursor->getFetchQuery(10000);

जब तक मुझे कोई खाली परिणाम नहीं मिल जाता, मैं इसका पुनरावृत्ति करता हूं प्रत्येक पुनरावृत्ति में, मैं FETCH करता हूं, परिणाम की प्रक्रिया करता हूं, और एक फाइल पर लिखता हूं।

 do { $result = $fetchQuery->getArrayResult(); foreach ($result as $row) { $writer->addRow($this->toXlsxRow($row)); } } while ($result);

मैं कर्सर और लेखक बंद कर देता हूं।

 $cursor->close(); $writer->close();

मैं अस्थायी फ़ाइलों के लिए निर्देशिका में डिस्क पर फ़ाइल लिखता हूं, और रिकॉर्डिंग पूरी होने के बाद मैं इसे फिर से बचने के लिए ब्राउज़र में भेज देता हूं, स्मृति में बफरिंग करता हूं।

पढ़ें READY! हमने सभी डेटा को संसाधित करने के लिए PHP से लगातार मेमोरी का उपयोग किया और भारी प्रश्नों की एक श्रृंखला के साथ डेटाबेस को यातना नहीं दी। और अनलोडिंग ने अनुरोध को पूरा करने के लिए आवश्यक आधार की तुलना में थोड़ा अधिक समय लिया।

देखें कि क्या आपकी परियोजनाओं में ऐसे स्थान हैं जो कर्सर के साथ मेमोरी को गति / सहेज सकते हैं?

डॉक्ट्रीन में डीबी कर्सर

समस्या

यह आमतौर पर कैसे तय किया जाता है, और यह खराब क्यों है?

कर्सर

डॉक्ट्रिन में कर्सर

More articles: