Optimizacija upita

Optimizacija upita (engl. Query optimization) je komponenta procesora upitnog jezika koja je neophodna, bar kod velikih sistema i za velike baze podataka, da bi sistem uopšte mogao da zadovolji traženu efikasnost. Optimizator upita pokušava da odredi najefikasniji način da se izvrši dati upit razmatranjem mogućih planova upita. Posebnu pogodnost kod optimizacija upita pružaju relacioni sistemi, s obzirom na dovoljno apstraktni nivo relacionih izraza kojima se upiti zadaju. Obično sam korisnik ne može da pristupi direktno samom optimizatoru upita jer se on odvija automatski unutar sistema za upravljanje bazama podataka, međutim, postoje baze podataka koje dozvoljavaju vođenje optimizatora upita i to su nerelacioni sistemi.

Upit (engl. Query) je zahtev za informacijama iz baze podataka. Rezultati upita se generišu pristupanjem i manipulacijom relevantnih podataka unutar baze podataka u zavisnosti od tražene informacije. Pošto je struktura baza podataka kompleksna, u većini slučajeva, upiti su komleksni i ponekad zahtev za podacima iz upita mogu biti prikupljani od stane različitih baza podataka kojima se pristupa na različite načine. Svaki drugačiji način pristupa bazi podataka zahteva različito vreme obrade. Pa tako, vreme obrade može varirati od jednog delića sekunde do više sati, zavisno od izabranog načina pristupa bazi podataka. Svrha optimizacije upita, koji je automatizovan proces, jeste da se pronađe način da se dati upit obradi u minimalnom vremenu. Pronalaženje tačnog optimalnog puta za izvršavanje upita, među svim mogućim, je veoma složen, dugotrajan proces, koji može puno da košta po ceni vremena a često i ne može da se pronađe. Tako optimizacija upita obično pokušava pronalazak optimalnog puta upoređivanjem nekoliko razumnih alternativa koje se odvijaju u razumnom roku i da tako pronađe "dovoljno dobar" plan koji obično mnogo ne odstupa od najboljeg mogućeg rezultata.

Istorija

Istorija optimizacije upita počinje nastankom prvih upitnih jezika, pa je tako razvijanjem baza podataka i upitnih jezika za te baze podataka, razvijana i optimizacija upita.

Uzroci loših performansi upita

Situacije koje loše utiču na performanse SQL (engl. Structured Query Language) upita a samim ti i same aplikacije mogu biti različite, a neke od njih su: loše indeksiranje, netačna statistika, previše međusobnih blokiranja transakcija i zastoja, operacije koje nisu zasnovane na radu sa skupovima podataka, loša specifikacija upita, loša organizacija baze podataka, plan izbršenja upita koji se ne kešira, česta rekompilacija plana izvršenja, nepravilna upotreba kursora.

• Loše indeksiranje

Loše indeksiranje može drastično da utiče na vreme izbršavanja upita, jer se prilikom izbršenja upita pristupa većoj količini podataka što direktno dovodi do povećanja pristupa disku, korišćenja memorije i porecesora, a što direktno utiče na povećanje vremena izbršenja upita.

• Netačna statistika

Netačna statistika može značajno da naruši performanse upita. Optimizator upita u okviru sistema za upravljanje bazama podataka, na osnovu netačne statistike o distribuciji podataka, može da donose pogrešnu procenu o npr. broju zapisa koje upit treba da vrati, a koji je važan parametar optimizatoru za definisanje optimalnog i efikasnog plana izbršenja upita.

• Previše međusobnog blokiranja transakcija i zastoja

Previše međusobnog blokiranja transakcija i zastoja dovodi do značajnog narušavanja performansi upita. Ukoliko dve transakcije pristupaju istom podatku u tabeli sa konfliktom, dolazi do uzajmnog blokiranja jedne od transakcija i na taj način do čekanja ili vraćenja blokirane transakcije na ponovno izbršenje. Blokiranje je normalna pojava, ali previše blokiranja može značajno da naruši performanse. Zastoj se dešava kada dve transakcije istovremeno blokiraju jedna drugu. Sistemi za upravljanje bazama podataka periodično tragaju za zastojima i jednu od transakcija žrtvuju i braćaju je na početak izvršavanja.

• Operacije koje nisu zasnovane na radu sa skupovima podataka

Operacije koje nisu zasnovane na radu sa skupovima podataka nego se zasnivaju na obradi podataka upotrebom kursora i petlji, značajno mogu da naruše performanse upita.

• Loša specifikacija upita

Loša specifikacija upita je najčešće razlog loših performansi upita, jer se željeni rezultat može dobiti definisanjem upita na više različitih načina, a neki od tih načina onemogućuju optimizator da upotrebi adekvatan indeks i smanji vreme izvršavanja upita.

• Loša organizacija baze podataka

Loša organizacija baze podataka, odnosno nedovoljna ili prekomerna normalizacija baze podataka, može negativno da utiče na vreme izbršavanja upita zbog nepotrebnog povećanja obima podataka koje upit obrađuje i povećanja broja blokiranja upita ili povećanja broja operacija spajanja (engl. Join).

• Plan izvršenja upita koji se ne kešira

Plan izvršenja upita koji se ne kešira narušava performanse upita, jer optimizator upita sistema za upravljanje bazama podataka troši određeno vreme za kreiranje plana izvršenja upita i produžava ukupno vreme izvršavanja upita.

• Česta rekompilacija plana izvršenja

Česta rekompilacija plana izvršenja povećava vreme izvršenja upita, jer se troši određeno vreme za rekompilaciju plana izvršenja. Za izbegavanje rekompilacije, plan izvršenja upita mora biri nezavisan od parametra koji se prosleđuju upitu prilikom izvršenja, a to se najčešće postiže upotrebom uskladištenih procedura.

• Nepravilna upotreba kursora

Nepravilna upotreba kursora povećava vreme izvršavanja upita. Upotrebom kursora u okviru upita podaci iz tabele se dobavljaju red po red, a sistemi za upravljanje bazama podataka su optimizovani za čitanje i obradu čitavih skupova redova. Ukoliko se kursori ipak moraju koristiti, onda treba voditi računa o tome da postoje različite vrste kursora koje različito utiču na povećanje vremena izvršavanja upita.

Implementacija optimizacije upita u SQL

Upit se često može napisati na dva ili više načina, a da se pri tome dobije isti rezultat nakon njegovog izvršenja. Upiti napisani na različite načine obično daju različite planove izvršenja upita, koje na osnovu napisanog upita kreira optimizator upita u okviru sistema za upravljanje bazama podataka. Različiti planovi izvršenja upita daju različita vremena izvršenja upita, pa stoga postoje planovi koji su bolji od drugih planova. Prilikom pisanja upita, ukoliko je moguće, uvek treba definisati upit na način da se dobije dobar (ili optimalan) plan izvršenja, a time i manje vreme izvršenja upita. Dve osnovne vrste optimizacije koje se sreću u procesorima upitnih jezika jesu strategija algebarskih transformacija upita i stategija procene cena. Prva ne zavisi od specifičnih podataka u bazi, dok druga zavisi. Tako bi, na primer, jedno pravilo algebarske transformacije, koje je skoro uvek dobro primeniti, bilo: primeniti restrikciju što je pre moguće ranije u izvršavanju upita. Strategije procene cene koriste informaciju (iz sistemskog kataloga) o postojanju indeksa nad tabelom iz upita. Sa ciljem postizanja najboljih performansi upita poželjno je pridržavati se sledećih preporuka:

• rad sa malim skupovima podataka
• efektivna upotreba indeksa
• izbegavati davanje uputa optimizatoru
• koristiti domenski i referencijalni integritet
• izbjegavati upite koji intenzivno koriste resurse
• smanjiti komunikaciju preko mreže
• smanjiti trajanje transakcija

Međutim, navedene preporuke različito utiču na poboljšanje performansi upita u različitim realnim okruženjima, pa je najbolje izvršiti testiranja različitih formi istog upita u realnom okruženju i na osnovu dobijenih rezultata izvršiti optimizaciju upita.

Rad sa malim skupovima podataka

Smanjenjem i ograničenjem skupa podataka (kolona i redova) nad kojima se izvršava upit smanjuje se upotreba resursa i povećava efikasnost indeksa, što pozitivno utiče na performanse upita. Jedan od načina za smanjenje skupa podataka u upitu je da se lista kolona u okviru SELECT liste upita ograniči samo na kolone koje su neophodne, odnosno da se izbjegava upit tipa SELECT *. Drugi način za smanjenje skupa podataka u upitu je da se upotrebom WHERE klauzule smanjuje broj redova ukoliko je to moguće, jer se na taj način optimizacija upita realizuje efikasnijom upotrebom indeksa. Ukoliko se kao rezultat upita korisniku treba prezentovati velika količina podataka, onda se može, ukoliko je prihvatljivo, izvršiti straničenje podataka (eng. paging), odnosno na zahtjev korisnika slati jedan po jedan skup zapisa.

Efektivna upotreba indeksa

Poznato je da indeksi (clustered index – podaci su smešteni na disku redosledom kako su sortirani i non-clustered indexes – sortirani su samo indeksi na podatke koji se nalaze razbacani na disku) definisani nad odgovarajućim kolonama (najčešće korištenim u upitima) ubrzavaju izvršenje upita. Međutim, podjednako je važno da ovi indeksi budu efikasno iskorišteni prilikom definisanja upita. Efikasna upotreba indeksa nad kolonom koja se nalazi u okviru where klauzule upita zavisi od izraza, odnosno operacija koje se vrše nad tom kolonom u okviru where klauzule (brza Index Seek operacija izvršava se ukoliko se radi o visoko selektivnom upitu, odnosno ukoliko se za dobijanje rezultata upita mora obraditi mali broj redova, 10-15% redova od ukupnog broja redova u tabeli). Na primjer, operator „!=“ zahtijeva skeniranje čitave tabele iako je nad kolonom definisan indeks, što predstavlja neefikasnu upotrebu indeksa (sporija Index Scan operacija). Nepoželjni operatori, koje treba izbjegavati nad kolonama sa indeksom u okviru WHERE klauzule, su operatori isključenja (<>, !=, !>, !<, NOT EXISTS, NOT IN i NOT LIKE IN, OR), a nekada i operator LIKE (LIKE '%tekst'). Naravno, ne mogu se uvek izbeći ovi operatori, ali kada je moguće predefinisati upit tako da vraća isti rezultat korištenjem drugih operatora, onda to treba razmotriti u cilju poboljšanja performansi upita.

  Неоптимизовани LIKE упит:
  
  SELECT zaposleni.ime 
  FROM zaposleni
  WHERE zaposleni.ime LIKE 'Dej%'

  Оптимизован LIKE упит:
  
  SELECT zaposleni.ime 
  FROM zaposleni 
  WHERE zaposleni.ime >= 'Dej' AND zaposleni.ime < 'DeK'

Vrlo često optimizator sistema za upravljanje bazama podataka, ukoliko je to moguće, izvrši konverziju upita u optimizovani oblik tako što konvertuje nepoželjni izraz u odgovarajući poželjni izraz (npr. !< zamjeni sa >=). Da bi se postigla efikasnija upotreba indeksa, potrebno je izbegavati aritmetičke operacije nad kolonama u okviru where klauzule:

  Неоптимизовани упит:
  
  SELECT zaposleni.ime, zaposleni.pozicija 
  FROM zaposleni 
  WHERE zaposleni.godina * 2 > 60

  Оптимизован упит:
  
  SELECT zaposleni.ime, zaposleni.pozicija 
  FROM zaposleni 
  WHERE zaposleni.godine > 60/2

Izbjegavati funkcije nad kolonama u okviru WHERE klauzule, npr:

  Неоптимизовани упит:
  
  SELECT zaposleni.ime 
  FROM zaposleni 
  WHERE SUBSTRING(zaposleni.ime,1,1) = 'D'

  Оптимизован упит:
  
  SELECT zaposleni.ime 
  FROM zaposleni 
  WHERE zaposleni.ime>='F' AND zaposleni.ime<'G'

Izbegavati davanje uputa optimizatoru

Izbegavati davanje uputa optimizatoru. Davanjem uputa optimizatoru se onemogućava da optimizator dinamički definiše plan izvršenja upita u vreme njegovog planiranja i realizacije. Na ovaj način se obično narušavaju performanse upita, pa treba izbegavati JOIN upute (eng. join hints), INDEX upute (eng. index hints) i FORCEPLAN upute (eng. forceplan hints) prilikom definisanja upita.

Koristiti domenski i referencijalni integritet

Domenski integritet predstavlja ograničenje skupa vrednosti koje neki atribut skupa entiteta (neka kolona u tabeli) može da sadrži, a referencijalni integritet je ograničenje da se neka vrednost koja se pojavljuje u jednoj relaciji mora nalaziti i u drugoj relaciji. Domenski i referencijalni integritet omogućavaju optimizatoru da proveri validnost podataka bez fizičkog pristupa podacima. Smanjivanje broja fizičkih pristupa podacima, pozitivno utiče na performanse upita, smanjujući vreme izvršenja upita. Primer uticaja domenskog integriteta na performanse upita je NOT NULL ograničenje (definisanje da kolona mora sadržati neku vrednost). Upit definisan na sledeći način

  SELECT zaposleni.ime, zaposleni.godine 
  FROM zaposleni 
  WHERE zaposleni.godine != 30

je optimizovan, ali ukoliko kolona zaposleni.godine može da sadrži NULL vrednost, ovaj upit ne vraća sve zapise iz tabele koji u potpunosti zadovoljavaju uslov. Ukoliko je potrebno da upit vrati i zapise sa vrijednošću NULL u koloni zaposleni.godine, neophodno je proširiti uslov na sledeći način:

  SELECT zaposleni.godine 
  FROM zaposleni 
  WHERE zaposleni.godine != 30 OR zaposleni.godine IS NULL

Međutim, proširenjem upita menja se plan izvršenja upita tako da dolazi do povećanja vremena izvršenja upita. Preporučljivo je, kada je to moguće, onemogućiti da kolona može da sadrži NULL vrednost. Definisanjem referencijalnog integriteta između dve tabele poboljšavaju se performanse JOIN upita između te dve tabele.

  SELECT zaposleni.zaposleniid, zaposleni.ime, kontakti.adresa 
  FROM zaposleni JOIN kontakti ON zaposleni.kontaktiid = kontakti.kontaktiid
  WHERE zaposleni.zaposleniid = 2345

Naime, ukoliko postoji referencijalni integritet definisan između kolona kontaktid u tabelama i ukoliko kolona kontaktid u tabeli zaposleni ne može sadržati NULL vrednost, optimizator će preskočiti skeniranje tabele kontakti, jer na osnovu referencijalnog i domenskog integriteta pouzdano zna da za svaki zapis u tabeli zaposleni postoji jedan zapis u tabeli kontakti. Na ovaj način se smanjuje broj fizičkih pristupa podacima i poboljšavaju performanse upita.

Izbegavati upite koji intenzivno koriste resurse servera

Da bi upiti manje koristili resurse servera moraju se definisati tako da se u okviru njih:

• izbegava konverzija iz jednog tipa podataka u drugi tip prilikom poređenja kompatibilnih kolona tabele ili vrednosti (npr. varchar sa nvarchar).
• za proveru postojanja nekih podataka koristi EXISTS umesto COUNT(*);
• kad god je to moguće umesto UNION koristi UNION ALL pri čemu treba imati u vidu da se tada, ukoliko postoje, ne eliminišu duplikati u okviru rezultata upita;
• koriste indeksi nad kolonama nad kojima se koriste agregatne funkcija ili sortiranje;
• da se izbegava sp prefiks kod definisanja imena ugnježdenih procedura jer ukoliko ime procedure počinje sa sp onda server prvo traži proceduru u sistemskim procedurama i na taj način gubi dragoceno vreme.

Smanjiti mrežnu komunikaciju

Da bi se smanjila mrežna komunikacija, koja značajno utiče na vreme izvršavanja upita posmatrano sa strane aplikacije i korisnika, neophodno je smanjiti broj mrežnih ciklusa (eng. network round-trips) i trajanje mrežnog ciklusa. Trajanje mrežnog ciklusa je vreme potrebno da paket stigne od izvora (računara koji inicira komunikaciju) do destinacije i opet nazad do izvora komunikacije. Smanjivanje broja mrežnih ciklusa se može postići izvršavanjem više upita zajedno kao batch upit (jedan ili više SQL iskaza koji se izvršavaju kao jedan iskaz) ili kao uskladištena procedura (eng. stored procedure). Smanjivanje vremena mrežnog ciklusa se može postići definisanjem da se kao rezultat upita ne vraća broj zapisa koji su obrađeni u okviru upita (batch upit ili 'stored procedura). Broj obrađenih zapisa (redova) vrlo često je nebitan podatak i može se naredbom SET NOCOUNT ON izbeći njegovo vraćanje nakon izvršenja upita (SET NOCOUNT OFF je suprotna operacija u okviru SQL servera).

Smanjiti trajanje transakcije

Prilikom izvršavanja jednog upita, koji se posmatra kao atomic akcija (akcija koja ne može biti prekinuta drugom akcijom i pri kojoj baza podataka prelazi iz jednog konzistentnog stanja u drugo), vrši se upis dva konzistentna stanja u transakcijski log baze podataka (eng. transaction log) na disku. Ovo je vremenski zahtevna operacija, koja značajno utiče na performanse upita. Broj ovih operacija se može smanjiti grupisanjem više upita u jednu transakciju (više upita se izvršava kao jedna atomic akcija).

  Неоптимизован упит:
  
  BEGIN
  INSERT INTO zaposleni VALUE (Milan,Ivić,programer)
  INSERT INTO zaposleni VALUE (Petar,Jović,programer)
  INSERT INTO zaposleni VALUE (Luka,Lukić,programer)
  END

  Оптимизовани упит:
  
  BEGIN TRANSACTION
  BEGIN
  INSERT INTO zaposleni VALUE (Milan,Ivić,programer)
  INSERT INTO zaposleni VALUE (Petar,Jović,programer)
  INSERT INTO zaposleni VALUE (Luka,Lukić,programer)
  END
  COMMIT

Međutim, na ovaj način se povećava vreme kada su pojedini zapisi u tabeli zaključani što može negativno uticati na performanse drugih upita. Naime, SQL iskazi (SELECT, INSERT, UPDATE, DELETE) vrše zaključavanje zapisa u tabelama kako bi se podaci koje obrađuju zaštitili od drugih SQL iskaza. Zaključavanje zapisa povećava čekanje drugih upita i na taj način se produžava vreme njihovog izvršavanja. Početna postavka servera baza podataka je da se prilikom izvršavanja jednog upita zaključavanje vrši na nivou zapisa, tako da se ne dešava često da neki drugi upit zahteva pristup zaključanom zapisu i da mora da čeka na otključavanje tog zapisa. Međutim, ukoliko jedan upit u toku izvršavanja obrađuje veliku količinu zapisa, a zaključavanje se vrši na nivou zapisa, onda će se naizmenično izvršavati veliki broj operacija zaključavanja i otključavanja zapisa, čime se usložnjava upravljanje ovim operacijama i povećava vreme neophodno za njihovo izvršavanja, a samim tim i vreme izvršavanja upita. Ovo vreme se može smanjiti povećanjem nivoa na kojem se vrši zaključavanje tako da se na primer zaključavanje vrši na nivou stranice (eng. pagelock) ili čak na nivou cele tabele (eng. tablock):

  SELECT zaposleni.ime, zaposleni.prezime 
  FROM zaposleni 
  WITH(PAGLOCK ili TABLOCK)

Ukoliko se iz neke tabele podaci najčešće samo čitaju, a vrlo retko upisuju, menjaju ili brišu, onda je vrlo korisno upite (SELECT) izvršavati bez zaključavanja zapisa:

  SELECT zaposleni.* 
  FROM zaposleni 
  WITH(NOLOCK)

Primene

Optimizacija upita se upotrebljava u većini baza podataka, jer danas važnu ulogu u informacionim tehnologijama igra brzina obrade podataka. Postoje razni sistemi baza podataka i svaki sistem ima svoju implementaciju optimizatora upita. Postoje razni sistemi baza podataka, a neki od njih su: IBM DB2, Oracle, System R, INGRES, MySQL, PostgreSQL, Microsoft Access, SQL Server, itd.

Literatura

1. Uvod u relacione baze podataka, Gordana Pavlović-Lažetić, Matematički fakultet, Beograd
2. Optimizacija SQL upita, Dejan Stjepanović, Elektrotehnički fakultet, Banja Luka, mart 2010.

Spoljašnje veze

Matematički fakultet Beograd

Uvod u relacione baze podataka, Gordana Pavlović-Lažetić, Matematički fakultet Beograd

Query optimization Wikipedia