एसक्यूएल क्वेरी की लंबाई तक व्यावहारिक सीमा (विशेष रूप से MySQL)

क्या यह विशेष रूप से खराब है कि बहुत सारे (संभावित रूप से अनावश्यक) खंडों के साथ बहुत, बहुत बड़ी SQL क्वेरी है?

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

SELECT * 
FROM 4e_magic_items 
INNER JOIN 4e_magic_item_levels 
  ON 4e_magic_items.id = 4e_magic_item_levels.itemid 
INNER JOIN 4e_monster_sources 
  ON 4e_magic_items.source = 4e_monster_sources.id 
WHERE (itemlevel BETWEEN 1 AND 30)  
  AND source!=16 AND source!=2 AND source!=5 
  AND source!=13 AND source!=15 AND source!=3 
  AND source!=4 AND source!=12 AND source!=7 
  AND source!=14 AND source!=11 AND source!=10 
  AND source!=8 AND source!=1 AND source!=6 
  AND source!=9  AND type!='Arms' AND type!='Feet' 
  AND type!='Hands' AND type!='Head' 
  AND type!='Neck' AND type!='Orb' 
  AND type!='Potion' AND type!='Ring' 
  AND type!='Rod' AND type!='Staff' 
  AND type!='Symbol' AND type!='Waist' 
  AND type!='Wand' AND type!='Wondrous Item' 
  AND type!='Alchemical Item' AND type!='Elixir' 
  AND type!='Reagent' AND type!='Whetstone' 
  AND type!='Other Consumable' AND type!='Companion' 
  AND type!='Mount' AND (type!='Armor' OR (false )) 
  AND (type!='Weapon' OR (false )) 
 ORDER BY type ASC, itemlevel ASC, name ASC

ऐसा लगता है कि यह काफी अच्छा काम करता है, लेकिन यह विशेष रूप से उच्च यातायात नहीं है (दिन में कुछ सौ हिट), और मुझे आश्चर्य है कि क्या यह अनावश्यकताओं को हटाने के लिए प्रश्नों को आजमाने और अनुकूलित करने के प्रयास के लायक होगा।

0
1. प्रश्न का उत्तर देने के लिए धन्यवाद, मुझे लगता है कि क्वेरी का आकार अब मेरे लिए कोई समस्या नहीं होनी चाहिए। 2. एसक्यूएल स्वरूपण पर युक्तियों के लिए सभी को धन्यवाद। मैं इसके लिए नया हूं और वहां बहुत सारी चालें हैं जिन्हें मैं नहीं जानता (उदा। "टाइप न करें (...)") 3. बस एक परिशिष्ट के रूप में, यह एक PHP/MySQL ऐप है
जोड़ा लेखक Asmor, स्रोत
यहां एक उपयोगी ऑनलाइन SQL फॉर्मेटर है: sqlinform.com
जोड़ा लेखक micahwittman, स्रोत
जब आप और वेबसाइट का उपयोग करने का प्रयास करते हैं, तो क्या यह धीमा लगता है? यदि दिन में केवल कुछ सौ हिट हैं, तो मुझे लगता है कि आप इसके बारे में चिंता नहीं कर सकते। क्या आप यातायात में वृद्धि की उम्मीद करते हैं? कितनो के द्वारा? यदि आप समय के लिए कड़ी मेहनत नहीं कर रहे हैं, तो आप इसे भविष्य में सबूत के लिए कर सकते हैं। लेकिन फिर, क्या समय पर क्वेरी चलाने के लिए समय-समय पर अनावश्यकता को खोजने और निकालने का समय लगता है?
जोड़ा लेखक Matt Blaine, स्रोत

6 उत्तर

अपनी क्वेरी पढ़ना मुझे आरपीजी खेलना चाहता है।

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

मैंने कुछ प्रश्नों के साथ काम किया है जो 1000+ लाइनें हैं, और डीबग करना मुश्किल है।

वैसे, क्या मैं एक सुधारित संस्करण का सुझाव दे सकता हूं? यह ज्यादातर स्वरूपण के महत्व को प्रदर्शित करने के लिए है; मुझे विश्वास है कि यह समझना आसान होगा।

select *  
from
  4e_magic_items mi
 ,4e_magic_item_levels mil
 ,4e_monster_sources ms
where mi.id = mil.itemid
  and mi.source = ms.id
  and itemlevel between 1 and 30
  and source not in(16,2,5,13,15,3,4,12,7,14,11,10,8,1,6,9)  
  and type not in(
                  'Arms' ,'Feet' ,'Hands' ,'Head' ,'Neck' ,'Orb' ,
                  'Potion' ,'Ring' ,'Rod' ,'Staff' ,'Symbol' ,'Waist' ,
                  'Wand' ,'Wondrous Item' ,'Alchemical Item' ,'Elixir' ,
                  'Reagent' ,'Whetstone' ,'Other Consumable' ,'Companion' ,
                  'Mount'
                 )
  and ((type != 'Armor') or (false))
  and ((type != 'Weapon') or (false))
order by
  type asc
 ,itemlevel asc
 ,name asc

/*
Some thoughts:
==============
0 - Formatting really matters, in SQL even more than most languages.
1 - consider selecting only the columns you need, not "*"
2 - use of table aliases makes it short & clear ("MI", "MIL" in my example)
3 - joins in the WHERE clause will un-clutter your FROM clause
4 - use NOT IN for long lists
5 - logically, the last two lines can be added to the "type not in" section.
    I'm not sure why you have the "or false", but I'll assume some good reason
    and leave them here.
*/
0
जोड़ा
डेविड बी के दूसरे विचार के लिए ... मेरा अनुभव ज्यादातर ओरेकल/एसक्यूएल सर्वर है, और उनमें से दोनों सीमा एयन उठाए गए नहीं हैं। यदि MySQL वास्तव में इस तरह से व्यवहार करता है, तो आप निश्चित रूप से FROM में शामिल होना चाहते हैं। मैं सुझाव देता हूं कि इसे दोनों तरीकों से प्रयास करें और निष्पादन के समय की तुलना करें।
जोड़ा लेखक JosephStyons, स्रोत
ऐसा इसलिए है क्योंकि कवच (और हथियारों) को प्रकार के द्वारा आगे फ़िल्टर किया जाता है। तो उदाहरण के लिए, यदि कपड़ा और छिपे हुए चुने गए हैं, तो यह पढ़ेगा: (टाइप! = 'आर्मर' या ('सी' या 'एच' जैसे प्रतिबंध या प्रतिबंध)) पृष्ठ बस "या कुछ भी 'जैसे प्रतिबंध जोड़ता है "माता-पिता के अंदर, इतनी झूठी जरूरत है।
जोड़ा लेखक Asmor, स्रोत
FROM क्लॉज में शामिल होने से आपके WHERE खंड को अनचाहे कर दिया जाएगा। @ एयन - एक उचित क्वेरी अनुकूलक के साथ कोई प्रदर्शन अंतर नहीं होना चाहिए। क्या MySQL यह बुरा है?
जोड़ा लेखक Amy B, स्रोत
दरअसल, चीजों को गति देगा, विशेष रूप से उचित सूचकांक के साथ। कारण यह है कि यदि आपके सभी खंड कहां हैं, तो MySQL सभी डेटा लाएगा, और उसके बाद इसे फ़िल्टर करेगा; जबकि एक उचित जुड़ाव के साथ यह केवल आवश्यक डेटा का चयन करेगा, जो कि छोटे पैमाने पर कुछ आदेश हो सकता है - फ़िल्टर करने के लिए तेज़।
जोड़ा लेखक Aeon, स्रोत
ओह, और ... जब तक मैं कुछ याद नहीं कर रहा हूं, (टाइप करें! = 'आर्मर' या (झूठा)) या तो सत्य या गलत का मूल्यांकन करने जा रहा है, लेकिन किसी भी मामले में यह परिणाम सेट को प्रभावित नहीं करेगा, इसलिए यह वास्तव में भी जरूरी नहीं है।
जोड़ा लेखक Aeon, स्रोत
जॉइन स्थितियों के साथ WHERE खंड को अव्यवस्थित करने के लिए डाउनवोट।
जोड़ा लेखक longneck, स्रोत

मुझे लगता है कि आप 'बंद' से मतलब है कि एक फ़ील्ड का कोई मूल्य नहीं है?

यह जांचने के बजाय कि कुछ ऐसा नहीं है, और यह भी नहीं है आदि। क्या आप सिर्फ यह जांच नहीं सकते कि फ़ील्ड शून्य है या नहीं? या फ़ील्ड को 'ऑफ' पर सेट करें, और जांचें कि क्या टाइप या जो भी 'ऑफ' बराबर है।

0
जोड़ा

अधिकांश डेटाबेस इस समस्या से बचने के लिए संग्रहीत प्रक्रियाओं का समर्थन करते हैं। यदि आपका कोड निष्पादित करने के लिए पर्याप्त तेज़ और पढ़ने में आसान है, तो आप संकलन समय को कम करने के लिए इसे बदलना नहीं चाहते हैं।

एक विकल्प तैयार कथन का उपयोग करना है ताकि आप क्लाइंट कनेक्शन प्रति बार केवल एक बार हिट प्राप्त कर सकें और फिर प्रत्येक कॉल के लिए केवल पैरामीटर में पास हो जाएं

0
जोड़ा

डिफ़ॉल्ट MySQL 5.0 सर्वर सीमा " 1MB " है, जो 1 जीबी तक विन्यास योग्य है।

यह max_allowed_packet सेटिंग दोनों के माध्यम से कॉन्फ़िगर किया गया है ग्राहक और सर्वर, और प्रभावी सीमा दोनों के पाठक है।

चेतावनियां:

  • यह संभावना है कि यह "पैकेट" सीमा सीधे SQL कथन में वर्णों पर मैप न करे। निश्चित रूप से आप ग्राहक के भीतर चरित्र एन्कोडिंग, कुछ पैकेट मेटाडाटा, आदि में ध्यान रखना चाहते हैं।)
0
जोड़ा

एक व्यावहारिक परिप्रेक्ष्य से, मैं आम तौर पर किसी भी चयन पर विचार करता हूं जो लिखने के लिए 10 से अधिक लाइनों को लेता है (प्रत्येक खंड/शर्त को एक अलग रेखा पर डालकर) आसानी से बनाए रखने के लिए बहुत लंबा होता है। इस बिंदु पर, इसे शायद किसी प्रकार की संग्रहीत प्रक्रिया के रूप में किया जाना चाहिए, या मुझे एक ही अवधारणा को व्यक्त करने का एक बेहतर तरीका खोजने की कोशिश करनी चाहिए - संभवत: एक रिश्ते को पकड़ने के लिए इंटरमीडिएट टेबल बनाकर मुझे अक्सर पूछताछ की जा रही है।

आपका लाभ भिन्न हो सकता है, और कुछ असाधारण लंबे प्रश्न हैं जिनके पास एक अच्छा कारण है। लेकिन अंगूठे का मेरा नियम 10 लाइन है।

Example (mildly improper SQL):

SELECT x, y, z
FROM a, b
WHERE fiz = 1
AND foo = 2
AND a.x = b.y
AND b.z IN (SELECT q, r, s, t
            FROM c, d, e
            WHERE c.q = d.r
              AND d.s = e.t
              AND c.gar IS NOT NULL)
ORDER BY b.gonk

यह शायद बहुत बड़ा है; अनुकूलन, हालांकि, संदर्भ पर काफी हद तक निर्भर करेगा।

बस याद रखें, प्रश्न जितना लंबा और जटिल होगा, उतना कठिन होगा इसे बनाए रखना होगा।

0
जोड़ा

SELECT @@ global.max_allowed_packet

चुनें

यह एकमात्र वास्तविक सीमा है जो सर्वर पर समायोज्य है इसलिए कोई वास्तविक सीधा जवाब नहीं है

0
जोड़ा