Limita practică la lungimea interogării SQL (în mod special MySQL)

Este deosebit de rău să aveți o interogare SQL foarte mare, cu o mulțime de clauze WHERE (potențial redundante)?

De exemplu, iată o interogare pe care am generat-o din aplicația mea web cu totul dezactivat, care ar trebui să fie cea mai mare interogare posibilă pentru generarea acestui program:

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

Se pare că funcționează suficient de bine, dar nu este și un trafic foarte mare (câteva sute de hit-uri pe zi) și mă întreb dacă ar merita efortul de a încerca și de a optimiza interogările pentru a elimina concedieri și altele.

0
1. Vă mulțumesc pentru răspunsul la întrebare, cred că dimensiunea interogării nu ar trebui să fie o problemă pentru mine acum. 2. Vă mulțumesc tuturor pentru sfaturile de formatare a SQL. Sunt nou la asta și există multe trucuri pe care nu le cunosc (de ex., "Not type in (...)") 3. La fel ca un addendum, aceasta este o aplicație PHP/MySQL
adăugat autor Asmor, sursa
Iată un utilitar online de formatare: sqlinform.com
adăugat autor micahwittman, sursa
Când încercați să utilizați site-ul web, pare a fi lent? Dacă sunt doar câteva sute de hit-uri pe zi, cred că nu te-ai îngrijorat. Te aștepți ca traficul să crească? Cu cat mai mult? Dacă nu sunteți presați cu greu pentru timp, puteți face acest lucru, pentru a proteja viitorul site-ului. Dar, atunci este timpul necesar pentru a găsi și a elimina programatic concedieri mai mari decât timpul necesar doar pentru a rula interogarea?
adăugat autor Matt Blaine, sursa

6 răspunsuri

Citirea interogării mă face să vreau să joc un RPG.

Acest lucru nu este cu siguranță prea lung. Atâta timp cât sunt bine formatate, aș spune că o limită practică este de aproximativ 100 de linii. După aceea, ar fi mai bine să rupeți subcheile în vederea doar pentru a vă împiedica trecerea ochilor.

Am lucrat cu câteva interogări care au 1000 de linii și este greu de depanat.

Apropo, pot sugera o versiune reformata? Aceasta este cea mai mare parte pentru a demonstra importanța formatării; Am încredere că acest lucru va fi mai ușor de înțeles.

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
adăugat
Pentru a doua idee a lui David B ... Experiența mea este cea mai mare parte Oracle/SQL Server, și ambii nu suferă de limitarea Aeon ridicată. Dacă MySql se comportă într-adevăr așa, atunci cu siguranță veți dori să păstrați legătura în FROM. Vă sugerăm să încercați în ambele moduri și să comparați timpii de execuție.
adăugat autor JosephStyons, sursa
Asta pentru că armura (și armele) sunt filtrate în continuare după tip. Deci, de exemplu, dacă se selectează Pânză și Hide, care ar fi citit: (tip! = 'Armor' SAU FALSE SAU restricții precum 'C' SAU restricții ca H)) Pagina doar adaugă " "în interiorul parenzelor, este nevoie de falsă.
adăugat autor Asmor, sursa
aderarea la clauza FROM va despacheta clauza WHERE. @Aeon - nu ar trebui să existe diferențe de performanță cu un optimizator de interogare corespunzător. Este MySQL atât de rău?
adăugat autor Amy B, sursa
De fapt, asocierea va accelera lucrurile, mai ales cu indicii adecvați. Motivul este că dacă toate clauzele sunt în WHERE, MySQL va prelua toate datele și apoi va filtra; în timp ce printr-o îmbinare corectă se vor selecta doar datele necesare, care pot fi unele ordine de mărime mai mici - mai rapid pentru filtrare.
adăugat autor Aeon, sursa
Oh, și ... Dacă nu-mi lipsește ceva, (tip! = 'Armor' sau 'false') va evalua fie adevărat, fie fals, dar în niciun caz nu va afecta setul de rezultate, deci chiar nu este nevoie.
adăugat autor Aeon, sursa
downvote pentru aglomerarea clauzei WHERE cu condiții JOIN.
adăugat autor longneck, sursa

Presupun că vrei să spui prin oprirea că un câmp nu are valoare?

În loc de a verifica dacă ceva nu este acest lucru, și nu este, de asemenea, că nu puteți verifica doar dacă domeniul este nulă? Sau setați câmpul la "off" și verificați dacă tipul sau orice este egal cu "off".

0
adăugat

Majoritatea bazelor de date acceptă proceduri stocate pentru a evita această problemă. Dacă codul dvs. este suficient de rapid pentru a fi executat și ușor de citit, nu doriți să îl modificați pentru a obține timpul de compilare în jos.

O alternativă este să utilizați instrucțiunile pregătite pentru a obține succesul o singură dată pe conexiune pentru client și apoi să transmiteți numai parametrii pentru fiecare apel

0
adăugat

Limitarea implicită a serverului MySQL 5.0 este " 1 MB ", configurabilă până la 1 GB.

Acest lucru este configurat prin setarea max_allowed_packet la ambele client și server, iar limitarea efectivă este leagănul celor două.

caveats:

  • Este posibil ca această limitare a "pachetului" să nu fie direcționată direct către caracterele dintr-o instrucțiune SQL. Sigur doriți să țineți cont de codificarea caracterelor în cadrul clientului, unele metadate de pachete etc.)
0
adăugat

Din punct de vedere practic, în general, consider că orice SELECT care sfârșește prin a lua mai mult de 10 linii pentru a scrie (punând fiecare clauză/condiție pe o linie separată) este prea lungă pentru a fi ușor de întreținut. În acest moment, ar trebui probabil să fie făcut ca o procedură stocată de un fel sau ar trebui să încerc să găsesc o modalitate mai bună de a exprima același concept - posibil prin crearea unui tabel intermediar pentru a captura o relație pe care par să o inteleg frecvent.

Kilometrajul dvs. poate varia și există câteva interogări extrem de lungi care au un motiv bun de a fi. Dar regula mea este de 10 linii.

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

Este probabil prea mare; optimizarea, totuși, ar depinde în mare măsură de context.

Amintiți-vă, cu cât interogarea este mai lungă și mai complexă, cu atât va fi mai greu să o mențineți.

0
adăugat

SELECT @@ global.max_allowed_packet

aceasta este singura limită reală pe care o puteți regla pe un server, astfel încât nu există un răspuns real adevărat

0
adăugat
MySQL - comunitatea Română
MySQL - comunitatea Română
19 participanți

Comunitatea română a programatorilor MySQL.