SQLite (2013) में नया क्या है?

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

अगली पीढ़ी अनुरोध शेड्यूलर (NGQP, संस्करण 3.8.0 के बाद से)

अनुसूचक डेटा को जल्दी से जल्दी प्राप्त करने के लिए एक क्वेरी निष्पादन योजना बनाता है। SQLite संस्करण 3.8.0 में, शेड्यूलर को तेजी से और बेहतर काम करने के लिए मौलिक रूप से फिर से तैयार किया गया था।

यदि किसी क्वेरी को एक तालिका पर बनाया गया है, तो इसे अनुकूलित करना आसान है। यदि कोई क्वेरी कई तालिकाओं को जोड़ती है, तो इसके संभावित निष्पादन के लिए एक हजार और एक विकल्प हैं। प्लानर को अधूरी जानकारी के आधार पर यह तय करना चाहिए कि इनमें से कौन सा विकल्प सबसे प्रभावी रूप से काम करेगा।

SQLite नेस्टेड लूप का उपयोग करके कई तालिकाओं में शामिल होने वाले प्रश्नों को निष्पादित करता है, प्रत्येक तालिकाओं के लिए एक (प्लस अतिरिक्त लूप यदि स्थिति में OR या IN शामिल है)। एक लूप एक या एक से अधिक अनुक्रमित का उपयोग कर सकता है या तालिका का पूर्ण क्रॉल कर सकता है। इस प्रकार, नियोजन में दो कार्य होते हैं:

1) घोंसले के शिकार चक्र का क्रम चुनें;
2) प्रत्येक चक्र के लिए एक सूचकांक का चयन करें;

पहला कार्य दूसरे की तुलना में बहुत अधिक जटिल है। टेबल ट्रैवर्सल ऑर्डर चुनने के बाद, एक नियम के रूप में, सूचकांकों का चयन, पहले से ही तुच्छ है।

SQLite क्वेरी प्लानर "स्थिर" है, अर्थात यह उसी योजना का चयन करता है, बशर्ते कि:

1) डेटाबेस स्कीमा सूचकांकों के संदर्भ में नहीं बदला है;
2) ANALYZE पार नहीं किया गया है;
3) SQLITE_ENABLE_STAT3 विकल्प सेट नहीं है;
4) SQLite संस्करण नहीं बदला है।

इसका अर्थ है कि यदि परीक्षण के दौरान प्रश्न "अच्छी तरह से" काम करते हैं, तो वे SQLite के समान संस्करण पर युद्ध प्रणाली में अच्छी तरह से "काम" करेंगे। "बड़े" डेटाबेस, एक नियम के रूप में, इस तरह की गारंटी नहीं देते हैं क्योंकि वे लगातार सूचकांक आँकड़े अपडेट करते हैं।

3.8.0 से पहले SQLite के संस्करण जटिल प्रश्नों के साथ अच्छी तरह से काम नहीं करते थे। ऐसे "मुश्किल मामले" का एक उदाहरण: लेन-देन प्रसंस्करण प्रदर्शन परिषद से "टीपीसी-एच क्यू 8"। इस क्वेरी में, 8 तालिकाएँ शामिल हैं:



इस आरेख में, एक वृत्त का अर्थ है FROM क्वेरी से एक तालिका। मंडलियों को जोड़ने वाले चाप पर, प्रत्येक चक्र के निष्पादन की अनुमानित "लागत" लिखी गई है, इस धारणा के आधार पर कि वह चक्र जहां से आर्क उत्पन्न होता है, बाहरी दिया गया है। उदाहरण के लिए, यदि आप चक्र एस को चक्र एल में निवेश करते हैं, तो लागत 2.30 है। और यदि आप चक्र एल को एस में रखते हैं, तो लागत 9.17 है।

"लागत" लघुगणक हैं और जब घोंसले के शिकार चक्र होते हैं, तो उनके मूल्यों को गुणा किया जाता है, ऊपर नहीं जोड़ा जाता है। एल में एक एस चक्र को एम्बेड करने से 6.87 का लाभ का मतलब है कि अगर एस में एल एम्बेड किया गया है, तो एस 963 गुणा तेजी से क्वेरी एल में एम्बेडेड है।

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

समस्या का यह वर्णन एक सरलीकरण है, क्योंकि वास्तविकता में लागत अनुमान हमेशा अनुमानित होते हैं और एक अंक द्वारा नहीं, बल्कि विभिन्न व्यवहारों के कई "वेट" द्वारा प्रस्तुत किए जाते हैं। उदाहरण के लिए, "शुरुआती" वजन, जिसमें एक स्वचालित सूचकांक बनाने का समय निर्धारित है। इस तरह के ग्राफ के रूप में सभी प्रश्नों का प्रतिनिधित्व नहीं किया जाता है। यदि क्वेरी में ORDER BY शामिल है, तो अतिरिक्त सॉर्टिंग से बचने के लिए एक इंडेक्स का उपयोग करना उपयोगी होगा। और इसी तरह। लेकिन टीपीसी-एच क्यू 8 के मामले पर विचार करने के लिए, इन सभी कठिनाइयों को समझने के लिए बाहर रखा जाएगा।

संस्करण 3.8.0 से पहले, SQLite ने "समीपवर्ती पड़ोसी", "बीएस" हेयुरिस्टिक का उपयोग किया। इस मामले में, ग्राफ का एकमात्र ट्रैवर्सल कम से कम लागत वाले आर्क के साथ बनाया गया है। अजीब तरह से पर्याप्त है, यह कई मामलों में अच्छी तरह से काम करता है। और यह तेजी से काम करता है। लेकिन, अफसोस, "TPC-H Q8" के लिए यह विधि 36.92 की लागत वाले R-N1-N2-SCOLP मार्ग का चयन करती है। इस रिकॉर्ड का मतलब है कि आर एक बाहरी चक्र है, इसमें एन 1 एम्बेडेड है, आदि वास्तविकता में, सबसे अच्छा मार्ग PLOC-N1-RS-N2 है। इसकी लागत 27.38 है, अंतर नगण्य लगता है, लेकिन यह मत भूलो कि ये लघुगणकीय मूल्य हैं। इसलिए इष्टतम प्रदर्शन 750 गुना तेज चलता है। समाधान में सभी मार्गों की पूर्ण गणना शामिल हो सकती है, लेकिन इस तरह की गणना का क्रम चक्रों की संख्या के तथ्य के समानुपाती होता है।

नया नियोजक स्तंभ में मार्ग खोजने के लिए नए अनुमानक का उपयोग करता है: "एन निकटतम पड़ोसी।" एल्गोरिथ्म न्यूनतम लागत के साथ एक पड़ोसी चुनने के बजाय एन पथ की ओर जाता है। मान लीजिए N = 4. तब निम्नलिखित सर्वोत्तम मार्गों को TPC-H Q8 पर चुना गया है:

आर (लागत: 3.56)
एन 1 (लागत: 5.52)
एन 2 (लागत: 5.52)
पी (लागत: 7.71)

अगला, प्रत्येक मार्ग के लिए, न्यूनतम लागत के साथ अगला चरण चुनें:

आर-एन 1 (लागत: 7.03)
आर-एन 2 (लागत: 9.08)
एन 2-एन 1 (लागत: 11.04)
आरपी (लागत: 11.27)

तीसरा चरण:

आर-एन 1-एन 2 (लागत: 12.55)
आर-एन 1-सी (लागत: 13.43)
आर-एन 1-पी (लागत: 14.74)
आर-एन 2-एस (लागत: 15.08)

आदि कुल मिलाकर, तालिकाओं की संख्या के अनुसार 8 चरण संभव हैं। K चक्रों के लिए सामान्य मामले में, कम्प्यूटेशनल समय O (K * N) है, जो O (2 ^ K) से दूर है।

लेकिन N का उपयोग करने का मूल्य क्या है? शायद एन = के?

वास्तव में, "N नियर नेबर्स" N = 10. के लिए TPC-H Q8 का इष्टतम संस्करण पाता है और वर्तमान में, निम्न स्थिरांक को SQLite में सिल दिया जाता है: सरल प्रश्नों के लिए N = 1, दो चक्रों से N = 5 के लिए और N = 10 के लिए। दो से अधिक चक्रों वाले सभी प्रश्न। शायद ये स्थिरांक भविष्य में बदल जाएंगे।

SQLite 3.8.0 में अपग्रेड करते समय, कुछ प्रश्न धीरे-धीरे चलना शुरू हो सकते हैं।

पूरा ANZYZE!

नए अनुसूचक को दृढ़ता से आँकड़ों से बांधा जाता है, जो सूचकांक द्वारा एकत्र किए जाते हैं।

आंशिक सूचकांक (आंशिक सूचकांक, संस्करण 3.8.0 के बाद से)

आंशिक अनुक्रमणिका आपको सूचकांक से कुछ रिकॉर्ड को बाहर करने के लिए एक शर्त निर्दिष्ट करने की अनुमति देती है। उपयोग करने का "क्लासिक" तरीका:

CREATE INDEX po_parent ON purchaseorder(parent_po) WHERE parent_po IS NOT NULL; 

केवल प्रविष्टियाँ जिनके पास खाली पैरेंट_पो नहीं है, उन्हें po_parent इंडेक्स में शामिल किया जाएगा। यदि खाली parent_po के साथ तालिका में कई रिकॉर्ड हैं, तो महत्वपूर्ण संसाधन बचत प्राप्त की जाती है, क्योंकि सूचकांक में ये रिकॉर्ड बिल्कुल नहीं होते हैं।

उपयोग करने का एक पेचीदा तरीका। मान लीजिए कि लोग टीमों में विभाजित हैं। प्रत्येक टीम में एक नेता टीम में शामिल होता है। उदाहरण के लिए:

  CREATE TABLE person( person_id INTEGER PRIMARY KEY, team_id INTEGER REFERENCES team, is_team_leader BOOLEAN, --    ); 

यह गारंटी कैसे दें कि एक टीम में एक से अधिक नेता नहीं होंगे? टीम द्वारा केवल एक अद्वितीय सूचकांक बनाना असंभव है (team_id, is_team_leader) । चूंकि वह कई "गैर-नेताओं" को टीम में शामिल करने की अनुमति नहीं देगा। समाधान एक आंशिक सूचकांक बनाने के लिए है:

 CREATE UNIQUE INDEX team_leader ON person(team_id) WHERE is_team_leader; 

यह इंडेक्स टीम लीडर को खोजने में आपकी सहायता करेगा:

 SELECT person_id FROM person WHERE is_team_leader AND team_id=?1; 

और साथ ही यह टीम में दूसरे नेता को जोड़ने की अनुमति नहीं देगा।

स्मृति मैपिंग (मेमोरी-मैपेड I / O, संस्करण 3.7.17 से) का उपयोग कर इनपुट / आउटपुट

SQLite एक "आभासी वातावरण" में चलता है। यह एक विशेष कॉल टेबल का उपयोग करता है, जो ऑपरेटिंग सिस्टम की "बाहरी दुनिया" को अपनी रीडिंग फाइलों, तालों आदि के साथ छुपाता है। संस्करण 3.7.17 से पहले, पढ़ने और लिखने वाले डेटा ने पर्यावरण के xRead () और xWrite () कॉल का उपयोग किया था। ये कॉल आमतौर पर एपीआई कॉल में "फाइल से रीड" और ऑपरेटिंग सिस्टम के "राइट टू फाइल" में अनुवादित होते हैं। 3.7.17 संस्करण के साथ शुरू, SQLite नियमित I / O (नए कॉल: xFetch () और xUnfetch ()) के बजाय मेमोरी-मैप्ड I / O का उपयोग कर सकता है।

जब डेटा को सामान्य रूप से पढ़ा जाता है, तो SQLte एक बफर आवंटित करता है और सिस्टम को पेज द्वारा डेटा पेज पढ़ने के लिए कहता है। इस स्थिति में, डेटा को हमेशा मेमोरी में कॉपी किया जाता है। मेमोरी मैपिंग का उपयोग करते समय, SQLite एक मेमोरी लोकेशन पॉइंटर को पुनः प्राप्त करता है। यदि फ़ाइल पहले ही डाउनलोड की जा चुकी है, तो नकल नहीं होती है और इससे उत्पादकता बढ़ जाती है ( 146,200 % तक)। यह सिस्टम और SQLite के बीच साझा करके मेमोरी को भी बचाता है।

हालांकि, इसके नुकसान भी हैं। बफ़र ओवरफ़्लो त्रुटियां डेटाबेस को तुरंत बर्बाद कर देंगी, क्योंकि डिस्क के साथ मेमोरी सिंक्रोनाइज़ेशन तुरंत सिस्टम द्वारा किया जाएगा। हालाँकि, यह सबसे दुखद बात नहीं है। बुरी खबर यह है कि नए स्कीमा में I / O त्रुटि SQLite द्वारा "पकड़ी गई" नहीं है। इसके बजाय, एप्लिकेशन को समाप्ति के लिए एक संकेत मिलता है (विंडोज सिस्टम पर व्यवहार मेरे लिए अज्ञात है - शायद कोई इसे टिप्पणियों में लिख देगा)।

साथ ही, कुछ कार्य योजनाओं पर, प्रदर्शन में गिरावट भी संभव है।

मैप किए गए I / O को सक्षम करने के लिए, कमांड का उपयोग करें:

 PRAGMA mmap_size=268435456; 

मान प्रति डेटाबेस मेमोरी बफर का अधिकतम आकार सेट करता है। अक्षम:

 PRAGMA mmap_size=0; 

यह सब खबर है। हमें उम्मीद है कि समीक्षा उपयोगी होगी। विस्तृत SQLite रिलीज़ इतिहास के लिए यहां क्लिक करें ।

पुनश्च। SQLite डेटाबेस को प्रबंधित करने के लिए एक आधुनिक प्रबंधक का उपयोग करें।