Andmebaasi päringute dünaamiline koostamine
03.09.2008 | Gunnar
Sattusin suvel tegelema ühe süsteemiga, mis suutis oma päringutega tappa ka tugevad serverid. Pudelikaelaks osutus ühe päris mahuka otsinguvormi taga peituv päring, mida nähes mul suhteliselt kõhe hakkas. Ma arvan, et taolise päringu autori esimene kohtumine DBA-ga jääb ühtlasi ka tema viimaseks.
Vaatame korra peale järgmisele koodile, mis koostab päringu.
select distinct
o.id, o.date, o.code, o.title
from orders
left join order_line on order.id = order_line.order_id
left join customer on order.customer_id = customer.id
//...
//ja veel külluses left joine
where
";
...
Nagu näeme, võtab see päring LEFT JOIN abil külge päris palju tabeleid. Seejuures SELECT klausel välju neist tabelitest ei kaasa. Järgmiseks vaatame korra peale sellele osale koodist, mis ennist varju jäi.
{
if(substr($field,0,2)!='_f')
continue;
$sql .= $field."='" . $value . "' and";
}
// korista viimane and lõpust maha
$q = mysql_query($sql);
Näeme, et siin lisatakse päringu WHERE klauslisse ainult neile väljadele vastavad tingimused, mis vormil täideti. Seega teeb valdaval osal juhtudest see päring hulga tühja tööd. Need piiranguteta LEF JOIN klauslid olidki põhjuseks, miks päringud aeglased olid.
Kindlasti ei ole sellised päringud just andmebaaside suured sõbrad, kuid need saab muuta oluliselt efektiivsemaks. Päringute koostamise algoritm võiks olla midagi sellist.
// 2. Kontrolli, kas väli lisatakse päringusse
// 2.1 lisa välja jaoks vajalik join osa
// 2.2 lisa välja jaoks vajalik where osa
// 3. Pane päring osadest kokku
// 4. Küsi andmed andmebaasist
Selle algoritmi korral võetakse külge ainult nii palju tabeleid kui antud päringu sooritamiseks on vaja. Andmebaasile langevat koormust võib see lähenemine vähendada oluliselt.
05.09.2008 kell 15:05
Ääremärkusena peaks ka ära märkima, et nimetatud viisil päringute moodustamine kipub jätma turvaauke, mida saab SQL injection rünnakutega ära kasutada.
06.09.2008 kell 10:37
See on laialt levinud halb praktika. Kui võimalik, on hea plaan näiteks PDO-d kasutada ning lasta parameetritega tegeleda andmebaasiga suhtlemise kihil.
13.09.2008 kell 19:27
Kas on kogemusi otse SQL otsingu asemel eelindekseeritud otsingutega nagu näiteks Lucene?
25.09.2008 kell 15:58
MySQL-il on olemas näiteks query cache, mis on küll suhteliselt algeline, kuid primitiivsemate vajadustega saitide korral võib see väga vajalikuks osutuda. Alati on muidugi võimalus kirjutada valmis otsingutabelid, mida tasapisi uuendatakse töö käigus ja mis otsingutest tuleneva koormuse enda kanda võtavad (selleks on parim lahendus otsinguserverid). Lucene jmt kogemused paraku puuduvad mul, sest suuremad asjad teen MS-i peal tavaliselt.