MySQL डेवलपर्स के लिए टिप्स और ट्रिक्स। SQL के साथ काम करें

यह लेख MySQL डेटाबेस में SQL प्रश्नों के उपयोग और अनुकूलन पर कुछ दिलचस्प बिंदुओं के संग्रह के रूप में मेरे विचार से है, मेरी राय में, इंटरनेट पर खराब रूप से कवर किया गया है। तो, लेख से आप रोलअप के साथ निर्माण के बारे में सीखेंगे, और कैसे शामिल होने के लिए और उप-सूचियों में फिर से लिखना और साथ ही कई तालिकाओं में डेटा को अपडेट करना और हटाना - एक अनुरोध के साथ, और बहुत कुछ। चलिए शुरू करते हैं।

में शामिल होने और नहीं में से उप-श्रेणियों को फिर से लिखना

सबसे सामान्य उपश्रेणियों में से एक में और नहीं के साथ प्रश्न है। इसके अलावा, हम जानते हैं कि MySQL में क्वेरीज़ प्रायः ऑप्टिमाइज़र द्वारा क्वेरी निष्पादन योजना के कुशल निर्माण (कुछ शर्तों के तहत, साथ ही अन्य डेटाबेस के लिए, इस कथन के विपरीत हो सकती हैं) के कारण तेजी से काम करती हैं, इसलिए हम शामिल होने के लिए क्लासिक उप-सूची को फिर से लिखने का प्रयास करेंगे। हम यह कैसे करेंगे? पहले, यह समझ लें कि क्वेरीज़ में उन सभी मानों को प्रिंट किया गया है जो दोनों तालिकाओं में मौजूद हैं, जिसका अर्थ है कि आंतरिक आंतरिक जुड़ाव विशिष्ट रूप से इस क्वेरी से मेल खाएगा। नहीं के साथ क्वेरी, इसके विपरीत, सभी मानों को प्रदर्शित करें जो कि उपशम में मौजूद नहीं हैं, जिसका अर्थ है कि उनके पास पहले से ही एक बाहरी बाहरी जुड़ाव होगा। तो, शुरुआत की गई है, आइए इसके बारे में कुछ करने की कोशिश करें।

उदाहरण के लिए, मैं परीक्षण डेटाबेस दुनिया का उपयोग करूंगा, जिसे यहां आधिकारिक mysql वेबसाइट से लिया जा सकता है

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

SELECT * FROM Country WHERE Code IN (SELECT CountryCode FROM CountryLanguage) 

ध्यान दें, इस प्रश्न को इस प्रकार भी लिखा जा सकता है:

 SELECT * FROM Country c WHERE EXISTS ( SELECT 1 FROM CountryLanguage cl WHERE c.Code = cl.CountryCode ) 

अब, ऊपर दी गई धारणा के आधार पर, हम उपकुंजी को फिर से जुड़ने के लिए लिखते हैं:

 SELECT c.Name FROM Country c INNER JOIN CountryLanguage cl ON c.Code = cl.CountryCode +-------------+ | Name | +-------------+ | Aruba | | Aruba | | Aruba | | Aruba | | Afghanistan | +-------------+ 5 rows in set (0.00 sec) 

यह लगभग हो गया, लेकिन हमारे पास डेटा दोहराव था, जिसे हम अलग विकल्प के माध्यम से हटाते हैं। तालिका के सभी क्षेत्रों के लिए अंतिम संस्करण इस प्रकार है:

 SELECT DISTINCT c.* FROM Country c INNER JOIN CountryLanguage cl ON c.Code = cl.CountryCode 

बहुत बढ़िया! इसमें शामिल होने के लिए उपश्रेणी को फिर से लिखा गया।

अब थोड़ा और जटिल है - हम बाहरी जुड़ाव में नहीं लिखते हैं। अनुरोध का उद्देश्य सभी क्षेत्र हैं जहां लोग नहीं रहते हैं और कोई आधिकारिक भाषा नहीं है। दोबारा, पहले मैं सबकुछ में मानक नहीं लाऊंगा:

 SELECT Name FROM Country WHERE Code NOT IN (SELECT CountryCode FROM CountryLanguage) 

और यह मौजूद नहीं है के लिए दिखा रहा है:

 SELECT Name FROM Country c WHERE NOT EXISTS ( SELECT 1 FROM CountryLanguage cl WHERE c.Code = cl.CountryCode ) 

जैसा कि पहले मामले में, हम बाएं हाथ से लिखते हैं:

 SELECT c.* FROM Country c LEFT JOIN CountryLanguage cl ON c.Code = cl.CountryCode 

नतीजतन, हम प्राप्त करते हैं, पहले मामले में, डेटा दोहराव, और निश्चित रूप से, पंक्तियों को दूसरी तालिका में एक जोड़ी मान नहीं मिला। यह ऐसी रेखाएँ हैं जो समस्या का समाधान देती हैं, इसलिए सभी युग्मित पंक्तियों को हटा दें:

 SELECT c.* FROM Country c LEFT JOIN CountryLanguage cl ON c.Code = cl.CountryCode WHERE cl.CountryCode IS NULL 

इन सरल परिवर्तनों के साथ, हम क्वेरी ऑप्टिमाइज़र को थोड़ी मदद करने में सक्षम थे।

उप-वर्गों में तुलना पंक्तियों

ऐसे दुर्लभ मामले हैं जब हमें एक उपश्रेणी लिखने की आवश्यकता होती है जिसमें तुलना एक नहीं बल्कि कई स्तंभों द्वारा की जाती है, हालांकि, यह लिखना स्पष्ट रूप से गलत होगा:

 SELECT c.Name FROM City c WHERE c.ID = (SELECT Capital FROM Country WHERE Name='Finland') AND c.CountryCode = (SELECT Code FROM Country WHERE Name='Finland') 

इन उद्देश्यों के लिए, एक SQL क्वेरी है:

 SELECT c.Name FROM City c WHERE (c.ID, c.CountryCode) = (SELECT Capital, Code FROM Country WHERE Name='Finland') 

ऐसी क्वेरी को "स्ट्रिंग निर्माता" कहा जाता है और इसे ROW () फ़ंक्शन द्वारा बल दिया जा सकता है। इस मामले में, हम लिखेंगे:

 SELECT c.Name FROM City c WHERE ROW(c.ID, c.CountryCode) = (SELECT Capital, Code FROM Country WHERE Name='Finland') 

इसके आकर्षण के बावजूद, स्ट्रिंग निर्माता की कई सीमाएँ हैं:
1. उपशम को एक पंक्ति में वापस करना चाहिए, न कि कई में
2. आप तुलना संचालकों का उपयोग नहीं कर सकते हैं <,> या <>, हालांकि इस प्रतिबंध को विशेष शब्दों के साथ, किसी भी, या में सभी को दरकिनार किया जा सकता है।

यह ध्यान देने योग्य है कि इस तरह के निर्माण का उपयोग न केवल उप-क्षेत्रों के लिए किया जा सकता है, बल्कि स्केलर अभिव्यक्तियों में भी किया जा सकता है:

 SELECT Name, Population FROM Country WHERE (Continent, Region) = ('Europe', 'Western Europe') 

हालाँकि, व्यवहार में, क्वेरी डिज़ाइनर स्केलर एक्सप्रेशन के लिए बहुत कुशल नहीं है, इसलिए हम क्वेरी को उसके सामान्य रूप में फिर से लिखते हैं:

 SELECT Name, Population FROM Country WHERE Continent = 'Europe' AND Region = 'Western Europe' 

एक साथ कई टेबल से डेटा को अपडेट और डिलीट करें।

शायद इस तरह की हेडलाइन किसी को हैरान कर देगी, लेकिन क्यों नहीं? चलिए डेटा को अपडेट करके शुरू करते हैं। आधिकारिक दस्तावेज निम्नलिखित सिंटैक्स कहता है:

 UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition] 

सबसे अधिक संभावना है, आप फॉर्म का अनुरोध करेंगे:

 UPDATE t1, t2 SET t1.name1 = t2.name2 WHERE t1.id = t2.id; 

दूसरी ओर, कोई भी एक अनुरोध करने के लिए परेशान नहीं करता है जो डेटा को एक साथ दो, तीन या अधिक तालिकाओं में अपडेट करेगा:

 UPDATE t1, t2 SET t1.name1 = 'name1', t2.name2 = 'name2' WHERE t1.id = t2.id; 

सच है, यह समझ में आने की संभावना नहीं है, लेकिन, फिर भी, यह संभव है।

डिलीट ऑपरेशन के साथ, चीजें और भी दिलचस्प हैं। आधिकारिक दस्तावेज इस वाक्यविन्यास की घोषणा करता है:

 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name[.*]] ... FROM table_references [WHERE where_condition] 

या

 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.*] [, tbl_name[.*]] ... USING table_references [WHERE where_condition] 

जो फॉर्म के अनुरोधों से मेल खाती है:

 DELETE t1 FROM t1, t2 WHERE t1.id = t2.id; DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id; 

इन दो प्रश्नों में, तालिका t1 से विलोपन होता है, और t2 का उपयोग डेटा चयन स्थिति बनाने के लिए किया जाता है।

और जैसा कि आपने अनुमान लगाया है, एक साथ दो तालिकाओं से डेटा हटाने के लिए, यह करें:

 DELETE t1, t2 FROM t1, t2 WHERE t1.id = t2.id; DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id; 

OLAP के बारे में थोड़ा सा। रोलअप संशोधक के साथ

शायद जो लोग ओरेकल या एसक्यूएल सर्वर के रूप में इस तरह के औद्योगिक डेटाबेस में आते हैं, शीर्षक पढ़ते हुए रोते हुए कहते हैं: "ठीक है, यह आवश्यक है!" - लेकिन, अफसोस, मैं तुरंत उनके उग्र विस्मयादिबोधक को शांत करूंगा। चूंकि MySQL 4.1.1, जब रोलअप संशोधक के साथ दिखाई दिया, तो इस विषय में एक मिलीमीटर उन्नत नहीं हुआ है, इसलिए आप इस डेटाबेस के अंतर्निहित टूल का उपयोग करके कोई डेटा क्यूब्स नहीं बना सकते हैं।

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

मान लीजिए कि हमें सभी भौगोलिक क्षेत्रों (क्षेत्रों), साथ ही महाद्वीपों और दुनिया भर में रहने वाले लोगों की कुल और औसत संख्या प्राप्त करने की आवश्यकता है। यदि आप माथे पर निर्णय लेते हैं, तो हमें निम्नलिखित प्रश्न मिलते हैं:

सभी भौगोलिक क्षेत्रों (क्षेत्रों) में रहने वाले लोगों की कुल और औसत संख्या:

 SELECT Continent, Region, SUM(Population) AS pop, AVG(Population) AS avg_pop FROM Country GROUP BY Continent, Region 

सभी महाद्वीपों पर रहने वाले लोगों की कुल और औसत संख्या:

 SELECT Continent, SUM(Population) AS pop, AVG(Population) AS avg_pop FROM Country GROUP BY Continent 

दुनिया भर में रहने वाले लोगों की कुल और औसत संख्या:

 SELECT SUM(Population) AS pop, AVG(Population) AS avg_pop FROM Country 

इन प्रश्नों को निष्पादित करने और फिर जटिल रूप से परिणामों के संयोजन के बजाय, आप सिर्फ एक कर सकते हैं:

 SELECT Continent, Region, SUM(Population) AS pop, AVG(Population) AS avg_pop FROM Country GROUP BY Continent, Region WITH ROLLUP; +---------------+---------------------------+------------+----------------+ | Continent | Region | pop | avg_pop | +---------------+---------------------------+------------+----------------+ | Asia | Eastern Asia | 1507328000 | 188416000.0000 | | Asia | Middle East | 188380700 | 10465594.4444 | | Asia | Southeast Asia | 518541000 | 47140090.9091 | | Asia | Southern and Central Asia | 1490776000 | 106484000.0000 | | Asia | NULL | 3705025700 | 72647562.7451 | | Europe | Baltic Countries | 7561900 | 2520633.3333 | | Europe | British Islands | 63398500 | 31699250.0000 | ... | Europe | Eastern Europe | 307026000 | 30702600.0000 | | Europe | Nordic Countries | 24166400 | 3452342.8571 | | Europe | Southern Europe | 144674200 | 9644946.6667 | ... | Europe | Western Europe | 183247600 | 20360844.4444 | | Europe | NULL | 730074600 | 15871186.9565 | | North America | Caribbean | 38140000 | 1589166.6667 | | North America | Central America | 135221000 | 16902625.0000 | | North America | North America | 309632000 | 61926400.0000 | | North America | NULL | 482993000 | 13053864.8649 | | Africa | Central Africa | 95652000 | 10628000.0000 | ... | Africa | Eastern Africa | 246999000 | 12349950.0000 | | Africa | Northern Africa | 173266000 | 24752285.7143 | | Africa | Southern Africa | 46886000 | 9377200.0000 | ... | Africa | Western Africa | 221672000 | 13039529.4118 | | Africa | NULL | 784475000 | 13525431.0345 | | Oceania | Australia and New Zealand | 22753100 | 4550620.0000 | ... | Oceania | Melanesia | 6472000 | 1294400.0000 | | Oceania | Micronesia | 543000 | 77571.4286 | | Oceania | Micronesia/Caribbean | 0 | 0.0000 | ... | Oceania | Polynesia | 633050 | 63305.0000 | | Oceania | NULL | 30401150 | 1085755.3571 | | Antarctica | Antarctica | 0 | 0.0000 | | Antarctica | NULL | 0 | 0.0000 | | South America | South America | 345780000 | 24698571.4286 | | South America | NULL | 345780000 | 24698571.4286 | | NULL | NULL | 6078749450 | 25434098.1172 | +---------------+---------------------------+------------+----------------+ 

ध्यान दें कि गैर-एकत्रित कॉलम में कुछ पंक्तियाँ NULL हैं, जो इंगित करती हैं कि यह पंक्ति एक उप-योग है। उदाहरण के लिए, रेखा

 | South America | NULL | 345780000 | 24698571.4286 | 

आपको यह पढ़ने की आवश्यकता है कि दक्षिण अमेरिका में कुल जनसंख्या 345.78 मिलियन लोग हैं, और औसत मूल्य 24698571.4286 है

और यहाँ लाइन है

 | NULL | NULL | 6078749450 | 25434098.1172 | 

यह संपूर्ण विश्व की जनसंख्या के संबंध में अंतिम परिणाम है।

रोलअप संशोधक के साथ सकारात्मक प्रभाव यह है कि एक बार रिकॉर्ड के माध्यम से पुनरावृत्ति करता है! वैसे, साइट (प्रोग्राम) पर किसी भी आंकड़े को प्रदर्शित करते समय यह कार्यक्षमता बहुत सुविधाजनक है। यदि आप इस कार्यक्षमता में रुचि रखते हैं या यदि आपके कोई प्रश्न हैं, तो मैं विवरण के लिए आधिकारिक दस्तावेज मांगता हूं

लॉन्च विकल्प - आई-ए-ए-डमी

"- आम बोलचाल की भाषा में" मैं एक मूर्ख हूं - "मैं एक मूर्ख हूं।" यह विकल्प - कैफे-अपडेट विकल्प का पर्याय है, जो शुरुआती लोगों के लिए बनाया गया था और कई प्रतिबंध लगाता है ताकि आपका पसंदीदा डेटाबेस आपके कार्यों से बुरा न बने:
1. अद्यतन और हटाने के अनुरोध को केवल तभी निष्पादित किया जाता है यदि स्थिति जहां या सीमा है
2. एक तालिका का चयन करें (बिना join'ov और सबक्वेरी के) केवल पहली 1000 पंक्तियाँ लौटाता है, जब तक कि सीमा स्पष्ट रूप से निर्दिष्ट न हो
3. केवल पहली 1,000,000 पंक्तियों में शामिल होने या उपशम प्रक्रियाओं के साथ प्रश्नों का चयन करें

इन प्रतिबंधों को ओवरराइड करने के लिए, अपने मापदंडों के साथ निम्नलिखित क्वेरी चलाएँ:

 SET sql_safe_updates=1, sql_select_limit=1000, sql_max_join_size=1000000; 

वर्तमान सेटिंग देखने के लिए:

 SELECT @@sql_safe_updates, @@sql_select_limit, @@sql_max_join_size 

शेल में क्लाइंट शुरू करते समय सेटिंग्स को पैरामीटर करना भी संभव है

 mysql -u user -p -h host --i-am-a-dummy --select_limit=500 --max_join_size=10000 

सौंदर्य संबंधी टिप्पणी

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

मेरा क्या मतलब है? MySQL में हम इस तरह लिखते हैं:

 -- SELECT 1 

(चयन से पहले एक स्थान के साथ), और अन्य डेटाबेस में:

 --SELECT 1 

(एक नियंत्रण चरित्र के बिना)।

उत्तर बहुत सरल निकला। तथ्य यह है कि यदि आप इस तरह के एक लापरवाह अनुरोध लिखते हैं

 UPDATE account SET credit=credit--1 

फिर पहले डैश को माइनस और दूसरे -1 के रूप में व्याख्या की जाएगी, लेकिन टिप्पणी नहीं। यदि आप दो डैश के बाद एक नियंत्रण चरित्र रखते हैं, तो केवल इस मामले में हमें एक टिप्पणी मिलेगी। इस टिप्पणी के बारे में यहाँ और अधिक पढ़ें ।

नतीजतन, एकल-लाइन टिप्पणियों के लिए मेरे काम में सबसे अधिक बार मैं पाउंड चिह्न (#) का उपयोग करता हूं, बल्कि एक नियंत्रण वर्ण के साथ दोहरे डैश के बजाय :-)

यूडीपी:
इसमें शामिल होने के लिए पुनर्लेखन अनुरोधों की उपयुक्तता के बारे में टिप्पणियों में संदेह है। नीचे, मेरा छोटा बेंचमार्क।

स्रोत डेटा:

 mysql> SELECT @@version; +-------------------------+ | @@version | +-------------------------+ | 5.5.32-0ubuntu0.12.04.1 | +-------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE TABLE t11\G *************************** 1. row *************************** Table: t11 Create Table: CREATE TABLE `t11` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `t11` varchar(45) NOT NULL, PRIMARY KEY (`id`), KEY `t11` (`t11`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM t11; +----------+ | COUNT(*) | +----------+ | 977360 | +----------+ 1 row in set (0.03 sec) mysql> SHOW CREATE TABLE t22\G *************************** 1. row *************************** Table: t22 Create Table: CREATE TABLE `t22` ( `id` int(10) unsigned NOT NULL DEFAULT '0', `t22` varchar(45) NOT NULL, PRIMARY KEY (`id`), KEY `t22` (`t22`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM t22; +----------+ | COUNT(*) | +----------+ | 962078 | +----------+ 1 row in set (0.04 sec) 

बनाम बनाम में शामिल हों

जॉइन इंडेक्स (7.84 सेकंड) के साथ काम करने पर यह IN (1.74 सेकंड) और EXISTS (2.44 सेकंड) की तुलना में बहुत कम हो जाता है।
नीचे एक उदाहरण है जब कॉलम t11 और t22 बिना इंडेक्स के हैं:

 mysql> SELECT SQL_NO_CACHE * -> FROM t11 -> WHERE t11 IN (SELECT t22 FROM t22) -> LIMIT 1000; +------+-------+ | id | t11 | +------+-------+ | 2 | 5718 | | ........ | | 1001 | 54192 | +------+-------+ 1000 rows in set (13.59 sec) mysql> SELECT SQL_NO_CACHE * -> FROM t11 c -> WHERE EXISTS ( -> SELECT 1 FROM t22 cl WHERE c.t11 = cl.t22 -> ) -> LIMIT 1000; +------+-------+ | id | t11 | +------+-------+ | 2 | 5718 | | ........ | | 1001 | 54192 | +------+-------+ 1000 rows in set (13.94 sec) mysql> SELECT DISTINCT SQL_NO_CACHE c.* -> FROM t11 c -> INNER JOIN t22 cl ON c.t11 = cl.t22 -> LIMIT 1000; +-------+-------+ | id | t11 | +-------+-------+ | 25 | 74376 | | ......... | | 34359 | 62666 | +-------+-------+ 1000 rows in set (3.36 sec) 

सीमा निर्धारित करें ताकि लंबे समय तक उत्तर की प्रतीक्षा न करें यह परिणाम को प्रभावित नहीं करता है।

ओस्टर जॉय बनाम नॉट इन नॉट एक्ज़िट्स

एक सूचकांक के बिना, इन प्रश्नों को लगभग उसी समय MySQL 5.5 में संसाधित किया जाता है।
नीचे दिए गए उदाहरण सूचकांक का उपयोग कर रहे हैं:

 mysql> SELECT SQL_NO_CACHE * -> FROM t11 -> WHERE t11 NOT IN (SELECT t22 FROM t22) -> LIMIT 1000; +--------+---------------------+ | id | t11 | +--------+---------------------+ | 1 | 0.23670763544431667 | | ........................ | | 891610 | 97811 | +--------+---------------------+ 91 rows in set (1.36 sec) mysql> SELECT SQL_NO_CACHE * -> FROM t11 c -> WHERE NOT EXISTS ( -> SELECT 1 FROM t22 cl WHERE c.t11 = cl.t22 -> ) -> LIMIT 1000; +--------+---------------------+ | id | t11 | +--------+---------------------+ | 1 | 0.23670763544431667 | | ........................ | | 891610 | 97811 | +--------+---------------------+ 91 rows in set (1.87 sec) mysql> SELECT SQL_NO_CACHE c.* -> FROM t11 c -> LEFT JOIN t22 cl ON c.t11 = cl.t22 -> WHERE cl.t22 IS NULL -> LIMIT 1000; +--------+---------------------+ | id | t11 | +--------+---------------------+ | 1 | 0.23670763544431667 | | ........................ | | 891610 | 97811 | +--------+---------------------+ 91 rows in set (1.27 sec) 

नतीजतन, परिणाम डेटाबेस के संस्करण और स्रोत डेटा पर निर्भर करता है!