(univerzitetni študij) (PDF: 2,3 MB)
UNIVERZA V LJUBLJANI
FAKULTETA ZA ELEKTROTEHNIKO
MATJAŽ MRGOLE
OGRODJE ZA OPTIMIZACIJO INDEKSOV V MYSQL
BAZAH PODATKOV
DIPLOMSKO DELO UNIVERZITETNEGA ŠTUDIJA
Mentor: doc. dr. Boštjan Murovec
Ljubljana, 2013
ZAHVALA
Iskreno se zahvaljujem svojemu mentorju, doc. dr. Boštjanu Murovcu, za neprecenljivo pomoč in
nasvete pri izdelavi naloge ter hitre odzive na vsa moja vprašanja.
Posebej se zahvaljujem svoji družini, ki mi je omogočila študij in me podpiralana moji poti.
POVZETEK
Informacijsko dobo, v kateri se trenutno nahajamo, zaznamuje široka in intenzivna uporaba
informacij. Te je potrebno nekam shraniti, da jih ne izgubimo in da lahko kasneje do njih
dostopamo. Ta problem v veliki meri rešujejo podatkovne baze. Ena izmed popularnih
podatkovnih baz je MySQL.
Pri relacijskih podatkovnih bazah do podatkov dostopamo s poizvedbami. Te lahko napišemo na
več načinov, zato je neizogibno,da se le–te med seboj po kakovosti razlikujejo. Hkrati je
priporočljivo pri poizvedbah, ki iščejo določeno vrednost ali združujejo podatke iz večih tabel,
postaviti indekse.Indeks je podatkovna struktura, ki nam omogoča, da v poizvedbi poiščemo
vrstice iz tabele mnogo hitreje kot sicer. V primeru, da indeksi v podatkovnih bazah niso
ustrezno postavljeni, poizvedbe trajajo dlje časa, kot je dejansko potrebno. Analiza optimalnega
postavljanja indeksov in optimalnega pisanja poizvedb zahteva veliko predznanja. Ker za
podatkovno bazo MySQL ne obstajajo orodja, ki bi to namesto nas avtomatizirala, smo v ta
namen izdelali lastno ogrodje. Podatkovna bazaje v našem primeru integrirana v spletni strežnik
s podporo programskega jezika PHP za izdelavodinamičnih spletnih vsebin.
Izdelano ogrodje s pregledom PHP izvorne kode zazna poizvedbe in nato postavi indekse ter
opozori na potencialne možnosti izboljšav v sestavi poizvedbe. Delovanje smo preizkusili na
dveh projektih. Eden je uporabljal podatkovno bazo, ki jo MySQL priporoča kot testno
podatkovno bazo za učenje. V obeh primerih je ogrodje zelo dobro služilo svojem namenu, saj je
indekse postavilo ekvivalentno dobremu strokovnjaku z nekaj letnimi izkušnjami dela s
podatkovnimi bazami. Zato je rešitevprimerna tako za začetnike kot za izkušene uporabnike
podatkovnih baz.
KLJUČNE BESEDE:
optimizacija SQL poizvedb, postavitev indeksov, relacijske podatkovne baze, MySQL
ABSTRACT
The information age we live in nowadays is marked by a wide and intensive use of information.
In order not to be lost and to be accessible in the long term, it needs to be stored. This problem is
mostly solved by databases. One of the popular databases is called MySQL.
In relational databases,the data is accessed via queries. As these can be written in many ways, it
is inevitable for them to be different in quality. At the same time, with queries which search for a
specific value or combine data from multiple tables, it is recommendedto create indexes. An
index is a data structure which provides a quicker access to rows from tables in a query. Query
performances in cases, where indexes in databases aren’t created efficiently, last longer than
actually needed. The analysis of optimal index creation and query writing requires a lot of prior
knowledge. Since there are no tools available, which would automatize it for MySQL database,
our own framework has been designed for this purpose. The database in our case is integrated
into an internet server with the support of a programming language PHP for creating dynamic
web content.
The framework finds queries by checking the PHP source code, creates indexes and informs
about potential improvement possibilities of a query structure. Its operation has been tested in
two projects. One of them used a database recommended by MySQL as a testing database for
learning. In both cases the framework served its purpose very well as it created indexes
equivalent to an expert with years of experience in the field of databases. Such solution is
therefore appropriate for beginners as well as for experienced users of databases.
KEYWORDS:
optimizing sql queries, positioning of indexes, relational databases, MySQL
VSEBINA
1
UVOD..............................................................................................................................................................1
2
PODATKOVNE BAZE .................................................................................................................................3
2.1
PODATKOVNA BAZA ....................................................................................................................................3
2.1.1
SISTEM ZA UPRAVLJANJE S PODATKOVNO BAZO ................................................................................................. 4
2.1.2
PODATKOVNI MODEL .............................................................................................................................................. 5
2.1.3
JEZIK SQL ................................................................................................................................................................. 6
2.2
PODATKOVNA BAZA MYSQL .....................................................................................................................7
2.2.1
2.3
INDEKSIRANJE .......................................................................................................................................................... 8
OPTIMIZACIJE ........................................................................................................................................... 11
2.3.1
STAVEK SELECT.................................................................................................................................................. 11
2.3.2
STAVEK EXPLAIN ............................................................................................................................................... 14
2.3.3
STAVEK WHERE ................................................................................................................................................. 14
2.3.4
STAVEK UNION ................................................................................................................................................... 15
2.3.5
STAVEK JOIN ........................................................................................................................................................ 15
2.3.6
INDEKS POPRAVKI ................................................................................................................................................ 16
2.3.7
STAVEK ORDER BY ............................................................................................................................................ 17
2.3.8
STAVEK GROUP BY ............................................................................................................................................ 18
3
PHP .............................................................................................................................................................. 19
4
NALOGE OGRODJA.................................................................................................................................. 21
4.1
STAVEK SELECT ..................................................................................................................................... 22
4.2
STAVEK WHERE ..................................................................................................................................... 22
4.3
STAVEK ORDER BY ................................................................................................................................ 22
4.4
STAVEK GROUP BY ................................................................................................................................ 22
5
ARHITEKTURA OGRODJA .................................................................................................................... 23
5.1
OPIS POSTOPKA ....................................................................................................................................... 23
5.2
ISKANJE POIZVEDB ................................................................................................................................... 23
5.2.1
RAZRED SCANCODE ............................................................................................................................................. 24
5.2.2
RAZRED CRAWLER ............................................................................................................................................... 25
5.2.3
RAZRED FILEMANIPULATION ............................................................................................................................ 25
5.3
ANALIZA IN OPTIMIZACIJA POIZVEDB ..................................................................................................... 25
I
5.3.1
RAZRED ANALYZEQUERIES ................................................................................................................................. 25
5.3.2
RAZRED MYSQLPARSER ..................................................................................................................................... 25
5.3.3
RAZRED INDEXFILTER ......................................................................................................................................... 27
5.4
TESTIRANJE ..............................................................................................................................................27
5.4.1
RAZRED BENCHMARK .......................................................................................................................................... 28
5.4.2
RAZRED DATAGENERATOR ................................................................................................................................. 28
5.5
UNITTEST RAZREDI..................................................................................................................................28
5.6
POMOŽNI RAZREDI ...................................................................................................................................29
5.6.1
RAZRED RUN ......................................................................................................................................................... 29
5.6.2
RAZRED CONFIG .................................................................................................................................................... 29
5.6.3
MYSQL RAZREDI................................................................................................................................................... 30
6
REALIZACIJA OGRODJA .........................................................................................................................31
6.1
RAZRED RUN ............................................................................................................................................31
6.2
RAZRED CONFIG .......................................................................................................................................32
6.3
RAZRED FILEMANIPULATION .................................................................................................................33
6.4
RAZRED SCANCODE..................................................................................................................................33
6.5
RAZRED CRAWLER ...................................................................................................................................35
6.6
RAZRED ANALYZEQUERIES .....................................................................................................................36
6.7
RAZRED MYSQLPARSER .........................................................................................................................37
6.8
RAZRED INDEXFILTER .............................................................................................................................40
6.9
RAZRED BENCHMARK ..............................................................................................................................41
6.10
RAZRED DATAGENERATOR ..................................................................................................................42
7
UPORABA OGRODJA ..............................................................................................................................45
7.1
STRUKTURA DATOTEK .............................................................................................................................45
7.2
UKAZI ORODJA ..........................................................................................................................................46
7.2.1
UKAZ INFO .............................................................................................................................................................. 46
7.2.2
UKAZ COMPATIBILITYTEST ................................................................................................................................. 46
7.2.3
UKAZ SCANCODE ................................................................................................................................................... 46
7.2.4
UKAZ CRAWLER ..................................................................................................................................................... 46
7.2.5
UKAZ ANALYZEQUERIES ...................................................................................................................................... 46
7.2.6
UKAZ CLEANUP ...................................................................................................................................................... 46
7.2.7
UKAZ SETUPCONFIG ............................................................................................................................................. 47
8
TESTIRANJE OGRODJA ..........................................................................................................................49
II
8.1
METODOLOGIJA TESTIRANJA................................................................................................................... 49
8.1.1
ANDERSON-DARLING TEST ................................................................................................................................. 49
8.2
TEST PODATKOVNE BAZE SAKILA ........................................................................................................... 50
8.3
TEST PODATKOVNE BAZE 1A-VREME ..................................................................................................... 52
9
SKLEPNE UGOTOVITVE IN IDEJE ZA NADALJNE DELO .............................................................. 55
DODATEK A: NASTAVITVE OGRODJA ..................................................................................................... 57
1.
MYSQL NASTAVITVE .................................................................................................................................. 57
2.
DIREKTORIJSKE NASTAVITVE .................................................................................................................... 57
3.
NASTAVITVE SPLETNEGA PAJKA ................................................................................................................ 57
4.
TESTIRANJE ................................................................................................................................................. 57
5.
GENERATOR PODATKOV ............................................................................................................................ 58
DODATEK B: IZPIS UKAZNIH LUPIN ........................................................................................................ 59
DODATEK C: SQL POIZVEDBE .................................................................................................................... 63
1.
SQL POIZVEDBE PODATKOVNE BAZE SAKILA ........................................................................................... 63
2.
PRIMERI SQL POIZVEDB PODATKOVNE BAZE 1A-VREME ....................................................................... 65
DODATEK D: REZULTATI TESTIRANJ ..................................................................................................... 67
1.
PODATKOVNA BAZA SAKILA ...................................................................................................................... 67
2.
PODATKOVNA BAZA 1A-VREME ................................................................................................................ 69
10
LITERATURA ......................................................................................................................................... 71
III
IV
SEZNAM SLIK
Slika 1: Primer relacije..................................................................................................................................................... 5
Slika 2: Primer iskalnega dvojiškega drevesa. ....................................................................................................... 9
Slika 3: Neuravnoteženo dvojiško drevo............................................................................................................... 10
Slika 4: Stavek EXPLAIN s poizvedbo SELECT [4]. ............................................................................................ 14
Slika 5: Postopek analiziranja poizvedb ................................................................................................................ 23
Slika 6: Diagram poteka iskanja MySQL poizvedb. ........................................................................................... 24
Slika 7: Diagram poteka optimizacije poizvedbe. .............................................................................................. 26
Slika 8: Razredni diagram poenotenega jezika modeliranja (angl.: Unified Modeling Language,
krat.: UML) MySQL razredov. ........................................................................................................................... 30
Slika 9: Razredni diagram UML Run razreda. ..................................................................................................... 32
Slika 10: Razredni diagram UML razreda Config. .............................................................................................. 32
Slika 11: Razredni diagram UML razreda FileManipulation. ........................................................................ 33
Slika 12: Razredni diagram UML razreda ScanCode. ....................................................................................... 34
Slika 13: Razredni diagram UML Crawler razreda............................................................................................ 35
Slika 14: Razredni diagram UML razreda AnaylzeQueries. ........................................................................... 36
Slika 15: Razredni diagram UML razreda MySQLParser. ............................................................................... 37
Slika 16: Razredni diagram UML razreda IndexFilter. .................................................................................... 40
Slika 17: Razredni diagram UML razreda Benchmark. ................................................................................... 41
Slika 18: Razredni diagram UML razreda DataGenerator.............................................................................. 42
Slika 19: Direktorijska struktura projekta. .......................................................................................................... 45
Slika 20: Model intetitet in povezav (angl.: Entity–relationship, krat.: ER) diagram podatkovne
baze Sakila................................................................................................................................................................ 50
Slika 21: ER diagram podatkovne baze 1a-vreme............................................................................................. 52
SEZNAM TABEL
Tabela 1: Tržni delež proizvajalcev podatkovnih baz v letu 2011, vir: Gartner (marec 2012)......... 4
Tabela 2: Časovna zahtevnost operacij [5]........................................................................................................... 21
Tabela 3: Rezultati testiranja podatkovne baze Sakila. .................................................................................. 51
Tabela 4: Rezultati testiranja podatkovne baze 1a-vreme. ........................................................................... 53
Tabela 5: Indeksi, postavljeni na podatkovni bazi Sakila. .............................................................................. 67
Tabela 6: Časi trajanj primerjalnih testov na podatkovni bazi Sakila. ...................................................... 68
Tabela 7: Časi trajanj primerjalnih testov na podatkovni bazi 1a-vreme. .............................................. 69
Tabela 8: Indeksi, postavljeni na podatkovni bazi 1a-vreme. ...................................................................... 70
V
SEZNAM SINTAKS
Sintaksa 1: Stavek SELECT [22]................................................................................................................................. 12
Sintaksa 2: Izraz expr [23]. .......................................................................................................................................... 13
Sintaksa 3: Stavek EXPLAIN [19]. ............................................................................................................................. 14
Sintaksa 4: Stavek UNION [24]. ................................................................................................................................. 15
Sintaksa 5: Stavek JOIN [25]. ...................................................................................................................................... 16
Sintaksa 6: Sintaksa uporabe indeks popravkov [26]. ..................................................................................... 17
Sintaksa 7: Stavek ORDER BY [21]. .......................................................................................................................... 17
Sintaksa 8: Stavek GROUP BY [21]. .......................................................................................................................... 18
SEZNAM UKAZNIH LUPIN
Ukazna lupina 1: Zagon ogrodja. ............................................................................................................................... 59
Ukazna lupina 2: Zagon ogrodja z opcijo compatibilityTest.......................................................................... 60
Ukazna lupina 3: Zagon ogrodja z opcijo scanCode. ......................................................................................... 60
Ukazna lupina 4: Zagon ogrodja z opcijo crawler. ............................................................................................. 60
Ukazna lupina 5: Izvajanje ukaza analyzeQueries. ............................................................................................ 61
Ukazna lupina 6: Izvajanje ukaza analyzeQueries na projektu, kjer ni predlaganih izboljšav........ 62
Ukazna lupina 7: Zagon ogrodja z opcijo cleanup.............................................................................................. 62
Ukazna lupina 8: Zagon ogrodja z opcijo setupConfig. .................................................................................... 62
SEZNAM UPORABLJENIH KRATIC IN SIMBOLOV
DDL
(angl.: Data Definition Language)
DML
(angl.: Data Manipulation Language)
ER
(angl.: Entity–relationship) model intetitet in povezav
LAMP
Linux, Apache, MySQL in PHP
SQL
(angl.: Structured Query Language) strukturiran povpraševalni jezik
UML
(angl.: Unified Modeling Language) poenoten jezik modeliranja
URL
(angl.: Uniform Resource Locator) spletni naslov
WAMP
Windows, Apache, MySQL in PHP
VI
1 UVOD
Današnji svet je poln informacij. Te je potrebno nekam shraniti, da lahko kasneje do njih
dostopamo. Ta problem v veliki meri rešujejo podatkovne baze. Glede na to, da lahko poizvedbe
(angl.: query), ki nam vrnejo podatke iz podatkovne baze,napišemo na več načinov, je
neizogibno,da se le–te med seboj po kakovosti razlikujejo. Hkrati je priporočljivo pri poizvedbah,
ki iščejo določeno vrednost ali združujejo podatke iz večih tabel, postaviti indekse.Indeksi so
podatkovne strukture, ki omogočajo hitroizvedbo tovrstnih poizvedb.Pri srednjih in večjih
projektih imamo veliko uporabnikov in posledično veliko poizvedb na sekundo.S tem se pojavi
vprašanje, kakšna je idealna postavitev indeksov ter ali so naše poizvedbe optimalne.
To
diplomsko
delo
se
posveča
MySQL
podatkovnim
bazam
in
rešuje
problem
optimalnepostavitve indeksov, poleg tega pa se tudi dotakne predlogov optimalne zasnove
poizvedb.Podatkovno bazo ponavadi uporabljamo v navezi z enim od programskih jezikov.
MySQL se pogosto uporablja v povezavi spopularnim programskim jezikom PHP za razvoj
dinamičnih spletnih vsebin.Drugi proizvajalci podatkovnih baz za njihponujajo podobna
orodjaza svoje podatkovne baze, vendar je potrebno njihove poizvedbe v orodje vnašati
ročno.Naše ogrodje to stori samodejno, s pregledom PHP izvorne kode projekta. Poleg tega ima
dodatno prednost, da se s časom uči, ali je glede na število vpisov v bazo postavitev indeksa
smotrna.
Diplomsko delo je sestavljeno iz treh delov. Prvi zajema drugo poglavje, kjerbomo spoznali
podatkovne baze in pripadajočesisteme za upravljanje s podatkovnimi bazami.Pojasnili bomo
osnovne pojme, ki se pojavljajo v podatkovnih bazah. Nato si bomo ogledalipodatkovno
bazoMySQLteropisaliekstrakcijo podatkov iz podatkovne baze.Pod prvi del spada še tretje
poglavje, kjer bomo opisali programski jezik PHP in razloge za njegovo uporabo.
Drugi del diplomske naloge zajema naslednja tri poglavja, ki se posvečajo orisu ogrodja, ki smo
ga razvili. V četrtem poglavju si bomo pogledali naloge ogrodja. Sledi peto poglavje, kjer bomo
opisali arhitekturo ogrodja in njegovo delovanje. Implementacijski detajli so zbrani v šestem
poglavju.
V zadnjem delubomo predstavili uporabo ogrodja in rezultate. V petem poglavju bomo
predstavili uporabo ogrodja. Sledi šesto poglavje,kjer bomo na dveh različnih projektih s
pripadajočima podatkovnima bazama naredili primerjalne teste(angl.: benchmark test)s stanjem
pred in po uporabi ogrodjater ovrednotili rezultate. Sklepne ugotovitve diplomskega dela
1
predstavlja sedmo poglavje, kjer bomo povzeli dosežke, opisali probleme, s katerimi smo se
srečali tekom razvoja,ter omenili smernice za nadaljnje delo.
2
2 PODATKOVNE BAZE
Poglavje je namenjenopregledu podatkovnih baz s pripadajočimi sistemi za njihovo upravljanje
in problematike, ki se ji posveča to diplomsko delo. V prvem podpoglavjubomo spoznali,kaj
podatkovne baze sploh so, kaj nam ponujajo in zakaj so tako uporabne, da jih srečujemona
vsakem koraku. Naslednje podpoglavje je namenjeno pregledupodatkovne bazeMySQL. Zadnje
podpoglavjeje namenjeno problemu ekstrakcije podatkov iz podatkovne baze, kjer si bomo
ogledali možnosti za pohitritev izvajanja poizvedb.
2.1 Podatkovna baza
Podatkovne bazeso organizirane zbirke podatkov. Pojavile so se zaradi vse večje potrebe po
shranjevanju podatkov ter hitrem dostopu do njih. Podatki ponavadi modelirajo stanje iz
realnega sveta (primer: zasedenost sob v hotelu), na način, ki nam omogoča premišljeno
odločanje o prihodnosti (primer:rezervacija proste sobe v hotelu).
Besedna zveza »podatkovna baza« se nanaša na zbirko podatkov in njihove pripadajoče
podatkovne strukture, ne pa tudi na sisteme za upravljanje z podatkovnimi bazami. Sliednji so
programski paketi, ki nam omogočajo učinkovito, uporabnikom prijazno in večuporabniško
okolje, namenjeno shranjevanju, vzdrževanju in dostopanju do medsebojno povezanih
podatkov[11].
Prvi začetki podatkovnih baz in njihovih sistemov upravljanja segajo v obdobje, ko se je večina
informacij shranjevala na magnetne trakove. V 60-ih letih je Charles Bachman iz General Electric
razvil prvo podatkovno bazo za splošno uporabo, za kar je prejel Turingovo nagrado. V začetku
70-ih let je Edgar Corr predlagal prvi relacijski podatkovni model, ki je v naslednjih dveh
desetletjih postal standard za upravljanje s podatki v organizacijskih sistemih. V istem desetletju
se je za podatkovne baze razvila močna teoretična podlaga, s čimer so podatkovne baze
postaleakademsko področje. V 80-ih letih se je razvil strukturiran povpraševalni jezik(angl.:
Structured Query Language, krat.: SQL)(IMB-ov projekt System R), ki je bil v poznih letih istega
desetletja tudi standardiziran. Standard se imenuje SQL-92. V naslednjem desetletju je veliko
proizvajalcev podatkovnih baz (IBM – DB2, Oracle 8, Informix UDS) razširilo svoje sisteme s
podporo kompleksnejšim poizvedbam in novim podatkovnim tipom, kot so slike inbesedilo.
Naslednja stopnja je bila vstop podatkovnih baz v dobo interneta, kjer so nadomestili prvo
generacijo spletnih mest, ki je shranjevala podatke v datotekah operacijskega sistema[1, 2].
3
2.1.1 Sistem za upravljanje s podatkovno bazo
Naloge in zmožnosti sistemov za upravljanje s podatkovno bazo[11, 12]:
definiranje sheme podatkovne baze in shranjevanje te sheme na zunanjem pomnilniku,
shranjevanjevsebine v podatkovno bazo,
poizvedbe po vsebini v podatkovni bazi,
spreminjanje vsebine vpodatkovni bazi,
omogoča kreiranje, spreminjanje ter brisanje tabel in podatkovnih baz z modulom,
imenovanimDDL (angl.: Data Definition Language),
skrbi
za
vzdrževanje
podatkovne
baze
z
DML
(angl.:
Data
Manipulation
Language)modulom, ki omogoča črpanje, brisanje in spreminjanje podatkov v
podatkovni bazi,
ščiti podatke,
zagotavlja integriteto podatkov,
izvaja transakcije.
Med velike proizvajalce sistemov podatkovnih baz(v nadaljevanju podatkovne baze) štejemo
Oracle, IBM in Microsoft (tabela1).MySQLse razvija pod okriljem podjetja Oracle,kije pod
njegovo okrilje prišel z nakupom podjetja Sun Microsystems leta 2010[13].
Proizvajalec
Prodaja 2011 (v milijonih $)
Tržni delež v letu 2011 (%)
11787
48,8
IBM
4870
20,2
Microsoft
4098
17,0
SAP/Sybase
1101
4,6
882
3,7
1389
5,8
Oracle
Teradata
Ostali
Tabela 1: Tržni delež proizvajalcevpodatkovnih baz v letu 2011, vir: Gartner (marec 2012).
4
2.1.2 Podatkovni model
Podatkovni model je povezana zbirka konceptov, namenjenih opisovanju in manipulaciji s
podatki.Obstaja več vrst podatkovnih modelov. Hierarhični in mrežni podatkovni model že
izginjata iz uporabe.Danes se uporabljajo entitetno-relacijski, objektno-relacijski, objektni in
relacijskipodatkovni model. Slednji je daleč najbolj v uporabi in ga podpirajo vsi večji
proizvajalci podatkovnih baz[14].
Relacijski podatkovni model odlikujejo predvsem naslednje lastnosti [1]:
formalno je definiran in osnovan na matematičnih strukturah – relacijah,
ne vsebuje elementov fizičnega shranjevanja, s čimer je zagotovljena fizična podatkovna
neodvisnost,
relacije so predstavljive s tabelami, ki so človeku dobro razumljive.
Relacijsko podatkovno bazo uporabnik vidi kot množico tabel (slika 1). Tabela je sestavljena iz
dveh delov– čelne vrstice in podatkovnih vrstic.
Atributi
Oddelek
oddelekID
ulica
mesto
postna_stevilka
OD1
Tržaška ulica 55
Ljubljana
1000
OD4
Savinškova ulica 32
Novo mesto
8000
OD2
Gospejna ulica 94
Maribor
2000
OD3
Kopališka ulica 34
Škofja Loka
4220
Primarni ključ
Tuji ključ
Osebje
osebjeID
ime
priimek
spol
rojstvo
oddelekID
OS1
Maja
Hribar
Ž
12.4.1967
ID1
OS2
Borut
Kovačevič
M
10.9.1979
ID3
OS3
Luka
Cesar
M
16.12.1983
ID3
OS4
Sara
Lušina
Ž
21.2.1965
ID4
OS5
Ivan
Novak
M
3.6.1959
ID1
OS6
Janez
Jelenc
M
10.9.1960
ID2
Slika 1: Primer relacije.
5
Osnovna koncepta relacijskega podatkovnega modela sta domena in relacija. Relacija je
definirana nad množico domen, ki predstavljajo zalogo vrednosti, ki jih lahko zavzame atribut
(podatkovni tipi: niz, število, decimalno število, datum, …) in njegove omejitve (pozitivna števila,
točno 10 znakov, unikatni podatki, …).Ime stolpca imenujemo atribut. V vsak stolpec lahko
shranjujemo le podatke, ki ustrezajo definiranim zalogam vrednosti in omejitvam. Čelna vrstica
je več skupaj združenih atributov, ki definirajo relacijsko shemo.
Relacijske podatkovne baze povežejo več tabel v eno relacijo. Osnova za povezovanje tabel so
primarni in tuji kjuč.Primarni ključ (angl.: Primary Key) tabele je stolpec ali kombinacija stolpcev
znotraj relacije, ki enolično določa vrstico. Je poseben primer enoličnih ključev (angl.: Unique
Key), s to razliko, da primarni ključ ne sme vsebovati vrednostiNULL. Vrednost NULL je poseben
indikator uporabljen v jeziku SQL, ki pove, da podatek v polje ni vpisan. Primer primarnega
ključa je EMŠO. Tuji ključ je stolpec ali kombinacija stolpcev znotraj relacije, ki se ujema s
ključem druge relacije (lahko tudi iste).
2.1.3 Jezik SQL
Jezik SQL je osnovni gradnik modernih podatkovnih baz. Med vsemi jeziki za delo s
podatkovnimi bazami jenajbolj razširjen [2, 13]. Določen je sstandardom ANSI/ISO SQL.
Standard SQL se je začel razvijati leta 1986, danes pa obstaja več različic. Standard SQL-92 je bil
izdan v letu 1992, SQL:1999 v letu 1999 itd. Uporablja se kot DML in DDLjezik. Čeprav
obstajanatančno definiran SQL standard, jejezik SQL vseeno implementiran na različne načine.
Oracle imalastnistandard PL/SQL, Microsoft SQL Server pa Transact-SQL.Ne glede na to sta obe
verziji zgrajeni na standardu SQL.MySQL verzija 5.* v celoti podpira standard ANSI/ISO SQL [16].
V nadaljevanjusoprimeri osnovnih stavkov, stavke pomembne za optimizacijo pa bomo razčlenili
v poglavju 2.3.
Osnovni stavki za delo s tabelami:
CREATE DATABASE: ustvari novo podatkovno bazo,
CREATE TABLE: ustvari novo tabelo v podatkovni bazi,
ALTER TABLE: spremeni tabelo v podatkovni bazi,
DROP TABLE: odstrani tabelo iz podatkovne baze,
INSERT: vnese novo vrstico v tabelo,
SELECT: vrne vrstice iz podatkovne baze,
UPDATE: posodobi vrstice v podatkovni bazi,
DELETE: zbriše vrstice iz podatkovne baze.
6
2.2 Podatkovna baza MySQL
Začetki baze MySQL segajo v leto 1979, ko je Michael »Monty« Widenius za švedsko podjetje TcX
ustvarilorodje UNIREG za podatkovne baze. Nato je leta 1994 TcX začel iskati podatkovno bazo s
podporo zajezik SQL za uporabo pri razvijanju spletnih aplikacijah. Testirali so nekaj
komercialnih ponudnikov podatkovnih baz, vendar so ugotovili, da so vsi prepočasni za njihove
velike tabele. Ogledali so si mSQL, vendar tani imelzahtevanih funkcionalnosti, kijih je TcX
potreboval, zato so se znova obrnili na Michaela Wideniusa, ki je razvil današnji MySQL. Leta
1996 je bil izdan MySQL 3.11.1, ki je bil dostopen v obliki binarne datoteke za operacijska
sistema Linux in Solaris. Danes je na voljo v obliki binarnih datotek in izvorne kode za praktično
vse operacijske sisteme. Ustanovljeno je bilo podjetje MySQL AB, ki je distribuiralo MySQL pod
kombinacijo odprtokodnein komercialne licence; slednja vklučuje tehnično podporo. Leta 2008
je podjetje Sun Microsistems kupilo MySQL AB, slednjega pa je leta 2010 kupil Oracle[3].
Prednosti baze MySQL [3]:
je ena najhitrejših podatkovnih baz, katerouporabljajo podjetja Associated Press, Cox
Communications, Google, Lufthansa, MIT Lincoln Labs, NASA, Sabre Holdings, Yahoo in
drugi [17],
veliko lažje vzdrževanje in upravljanje kot pri večjih podatkovnih bazah,
celostnapodpora jeziku SQL[15],
verzija za skupnost (MySQL Community Edition) je brezplačna,
distribucijske datoteke so zelo majhne v primerjavi z drugimi podatkovnimi bazami,
je na voljo na vseh komercialno dostopnih operacijskih sistemih (MAC OS X, Windows,
HP-UX, Unix, Linux),
ima polno podporo za povezljivost preko omrežja, prav tako je možen dostop preko
interneta, kjer so povezave enkriptirane z uporabo protokolaSSL,
je odprtokodni projekt.
Do nedavnega je bilo na račun MySQL tudi nekaj kritik, sajni podpiral transakcij in tujih ključev.
Vendar se je MySQL v zadnjih letih močno razvijal in dobil obe omenjeni funkcionalnosti, poleg
tega pa tudi poglede (angl.: views), bazne procedure (angl.: stored procedure) in prožilnike
(angl.: triggers). S tem se je približal večjim proizvajalcem podatkovnih baz. Posledica tega je, da
danes vse več podjetij, ki so v preteklosti uporabljala rešitvevodilnih proizvajalcihpodatkovnih
baz,danes uporablja MySQL [3].
7
2.2.1 Indeksiranje
Indeks je podatkovna struktura, ki nam omogoča, da v poizvedbi poiščemo vrstice iz tabele
mnogo hitreje kot sicer. Če ne uporabimo indeksa, mora MySQL preiskati celotno tabelo, pri
čemer začne pri prvi vrstici in preišče celotnozbirko podatkov, dokler ne najde ustreznih
zadetkov. Časovna zahtevnost iskanja v tem primeru narašča linearno s številom vrstic O(n),
zato je iskanje hitro, dokler vrstic v tabeli ni preveč. Ko velikost tabele narašča, pa ustrezno
postavljen indeks pospeši iskanje, saj je v tem primeru časovna zahtevnost iskanja O(log n).
Indeks lahko postavimo čez enegaali več stolpcev[4, 20].
Slabost indeksov je, da zasedajo dodatni prostor v računalniškem pomnilniku in da lahko
upočasnijo vnose podatkov v tabelo, saj je potrebno pri vsakem vpisu ponovno uravnotežiti
drevo, kar ustreza časovni zahtevnosti O(log n) [4, 20].
Večina MySQL indeksov (primarni ključ, unikaten ključ, indeks, iskanje po polnem besedilu
(angl.: full-text)) uporablja kot podatkovno strukturo uravnoteženo iskalno dvojiško drevo [ 21].
To je podatkovna struktura, kjer sta v vsakem vozlišču (elementu oz. očetu)dve povezavi na
naslednji vozlišči (levi in desni sin), ki sta enake strukture. Vozlišče, kjer začnemo drevesno
strukturo, je koren drevesa, vozlišča brez sinov pa so listi drevesa. Vrednosti elementov v levem
poddrevesu so manjše od vrednosti korena, vrednosti elementov v desnem poddrevesu pa so
večje od vrednosti korena. V iskalnem dvojiškem drevesu ni podvojenih elementov.Dvojiško
drevo je urejeno, če za vsako vozlišče velja, da je vrednost levega sina manjša, vrednost desnega
sina pa večja od njegove vrednosti [5].
8
Predpostavimo, da imamo naslednje podatke: 8, 10, 3, 6, 14, 7, 4, 13, 1. Postopek izgradnje
urejenega dvojiškega drevesa je naslednji:
1. vzamemo prvo številko, to je 8, in jo vstavimo v prazno drevo; ta številka postane koren
drevesa;
2. naslednja številka je 10;primerjamo jo s korenom drevesa in ker je 10 > 8, jo vstavimo v
desno poddrevo;
3. naslednja je 3; primerjamo jo s številko 8 in jo vstavimo v levo poddrevo;
4. naslednja je 6; ker je 6 < 8, jo vstavimo v levo poddrevo, vendar ne smemo pozabiti na
številko 3; ker je 3 < 6, jo vstavimo v desno poddrevo številke 3;
5. naslednja je 14; ker je 14 > 8, gremo v desno in ker je 13 > 10, jo vstavimo v desno
poddrevo številke 10;
6. naslednja je 7; ker je 7 < 8, gremo v levo; ker je 7 > 3, gremo v desno in ker je 7 > 6, jo
vstavimo v desno poddrevo številke 6;
7. naslednja je 4; ker je 4 < 8, gremo v levo; ker je 4 > 3, gremo v desno in ker je 3 < 6, jo
vstavimo v levo poddrevo številke 6;
8. naslednja je 13; ker je 13 > 8, gremo v desno; ker je 13 > 10, gremo v levo in ker je 13 <
14, jo vstavimo v levo poddrevo številke 14;
9. zadnja številka je 1; ker je 1 < 8, gremo v levo in ker je 1 < 3, jo vstavimo v levo poddrevo
številke 3.
Po opravljenem postopku dobimo dvojiško drevo narisano na sliki 2.
Slika 2: Primer iskalnega dvojiškega drevesa.
V primeru, da bi želeli iz podatkov poiskati številko 7, bi bil postopek sledeč:
1. začnemo pri korenu drevesa in primerjamo številko 7 s številko 8,
2. 7 < 8, zato gremo v levo poddrevo,
3. 7 > 3, zato gremo v desno poddrevo,
4. 7 > 6, zato gremo v desno poddrevo,
5. ker je 7 = 7, smo našli iskani element.
9
Da smo našli številko 7, smo potrebovali 4 operacije (primerjave). V splošnem potrebujemo za
iskanje elementa v urejenem dvojiškem drevu toliko operacij, kot imamo nivojev drevesa. Le–te
izračunamo z enačbolog2n, kjer je n število elementov v polnem drevesu, rezultat pa nato
pretvorimo v celo število. To seveda velja samo takrat, ko je drevo uravnoteženo. Če zgradimo
neuravnoteženo dvojiško drevo s prejšnjimi podatki, dobimo v najslabšem primeru dvojiško
drevo na sliki 3. V tem primeru je hitrost iskanja linearna, enako kot pri linearnem seznamu [5].
Slika 3: Neuravnoteženo dvojiško drevo.
Če poiščemo številko 7 v tem drevesu, imamo postopeksledeč:
1. začnemo pri korenu drevesa in primerjamo številko 7 s številko 1,
2. 7 > 1, zato gremo v desno poddrevo,
3. 7 > 3, zato gremo v desno poddrevo,
4. 7 >4, zato gremo v desno poddrevo,
5. 7 > 6, zato gremo v desno poddrevo,
6. ker je 7 = 7, smo našli iskani element.
Da smo našli številko 7, smo potrebovali 5 operacij (primerjav).To jeena operacija več kot pri
uravnoteženem drevesu. V najslabšem primeru, ko bi iskali število 14, pa bi potrebovali celo 9
operacij.V primeru neuravnoteženega dvojiškega drevesa je torej časovna zahtevnost iskanja
O(n). Iz tega sledi, da je uravnoteženje nujno, da je iskanje optimalno. Za postopek uravnoteženja
velja, daje časovna zahtevnost O(log2n)samo, če je pred vnosom novega podatka drevo že bilo
uravnoteženo, zato je to nujno delati sproti ob vsakem vnosu.
10
Če bi številko 7 iskali v (linearnem) seznamu, bi potrebovali 6 operacij. V splošnem v seznamu
potrebujemo toliko operacij, kolikor je elementov. Vidimo, da je iskanje z indeksi opazno
hitrejše. Slabost je ta, da tudi za vnašanje novega elementa v drevo potrebujemo v najslabšem
primeru log2n operacij, medtem ko za navaden seznam potrebujemo samo eno operacijo. Poleg
tega dvojiško drevo zaseda dodaten prostor v pomnilniku.Postavitev indeksov je torej
kompromis med hitrejšim iskanjem podatkov in počasnejšimi vnosi ter večjo porabo
pomnilnika.
2.3 Optimizacije
Ker za MySQL niorodja, ki bi predlagalo postavitev indeksov glede na dane poizvedbe, smo se
odločili, da ga izdelamo sami. Trenutno se indekse tipično postavlja glede na pretekle izkušnje in
ročno,kjer si lahko delno pomagamo s stavkomEXPLAIN.Drugi proizvajalci podatkovnih baz
ponujajo podobna orodja za svoje podatkovne baze (primer je Index Tuning Wizard v SQL Query
Analyzer [18]), vendar je treba poizvedbe v orodje vnašati ročno. Naše ogrodje stori to
samodejno
s
pregledom
PHP
izvorne
kode.Hkrati
se
s
časom
uči,
kakšno
je
številostavkovSELECT in koliko je posodobitvenih stavkov. Na podlagi teh podatkov in števila
vrstic v tabelah nam predlaga odstranitev indeksov, če to predstavlja optimalno rešitev.
V naslednjih podpoglavjih bomo podrobneje razčlenili standardne poizvedbe v MySQL in
pogledali možnosti za njihove optimizacije.
2.3.1 Stavek SELECT
S stavkom SELECT pridobimo podatke iz ene ali več tabel oziroma pogledov. V večini aplikacij je
SELECT najbolj uporabljen stavek. Vključuje lahko stavek JOIN in podpoizvedbe (angl.:
subquery). V sintaksi 1 izraz select_expr označuje, katere vrstice iz tabelenaj poizvedba
vrne. Opcija table_refences je razčlenjena vpoglavju 2.3.5., opcija expr, ki se pojavlja v
sintaksi 2 in vseh naslednjih definicijah sintaks, je podrobno razčlenjena v sintaksi 2.
11
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT]
[SQL_BIG_RESULT]
[SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE]
[SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
Sintaksa 1: Stavek SELECT [22].
Stavek SELECT lahko vsebuje več opcijskih stavkov, od katerih so za naše ogrodje najbolj
pomembni stavki WHERE, GROUP BY in ORDER BY. Te bomo v naslednjih poglavjih podrobno
razčlenili.
12
expr:
expr OR expr
| expr || expr
| expr XOR expr
| expr AND expr
| expr&&expr
| NOT expr
| ! expr
| boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}
| boolean_primary
boolean_primary:
boolean_primary IS [NOT] NULL
| boolean_primary<=>predicate
| boolean_primarycomparison_operatorpredicate
| boolean_primarycomparison_operator {ALL | ANY} (subquery)
| predicate
comparison_operator: = | >= | > | <= | < | <> | !=
predicate:
bit_expr [NOT] IN (subquery)
| bit_expr [NOT] IN (expr [, expr] ...)
| bit_expr [NOT] BETWEEN bit_expr AND predicate
| bit_expr SOUNDS LIKE bit_expr
| bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
| bit_expr [NOT] REGEXP bit_expr
| bit_expr
bit_expr: bit_expr | bit_expr | bit_expr&bit_expr | bit_expr<<bit_expr
|
bit_expr>>bit_expr | bit_expr + bit_expr | bit_expr - bit_expr | bit_expr *
bit_expr | bit_expr / bit_expr | bit_expr DIV bit_expr | bit_expr MOD
bit_expr | bit_expr % bit_expr | bit_expr ^ bit_expr | bit_expr +
interval_expr | bit_expr - interval_expr | simple_expr
simple_expr: literal | identifier | function_call | simple_expr COLLATE
collation_name | param_marker | variable | simple_expr || simple_expr | +
simple_expr | - simple_expr | ~ simple_expr | ! simple_expr | BINARY
simple_expr | (expr [, expr] ...) | ROW (expr, expr [, expr] ...) |
(subquery) | EXISTS (subquery) | {identifierexpr} | match_expr | case_expr|
interval_expr
Sintaksa 2:Izraz expr [23].
13
2.3.2 Stavek EXPLAIN
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[EXTENDED] SELECT select_options
Sintaksa 3: Stavek EXPLAIN [19].
Ena od metod optimizacij, ki jihuporabljamo za MySQL poizvedbe, jestavek EXPLAIN.
Uporabljamo ga bodisi za povratno informacijo, kako MySQL izvaja poizvedbo,bodisi kot
sinonim stavku DESCRIBE. Če pred stavkom SELECT uporabimo ključno besedoEXPLAIN, MySQL
prikaže informacije o tem, kako so tabele združene in katere indekse se uporablja (primer kaže
slika 4).Ti dve informaciji nam pomagata pri oceni postavitve indeksov, vendar samo, česo
indeksi že predhodno postavljeni[4].
Slika 4: Stavek EXPLAIN s poizvedboSELECT [4].
2.3.3 Stavek WHERE
Stavek WHERE definira pogoje, ki jih morajo vrstice izpolnjevati, da jih bo poizvedba vrnila.
Vopcijiwhere_condition, definiraniv sintaksi 1, uporabimo MySQL funkcije in operatorje,
definirane v sintaksi 2. Kostavka WHERE ne uporabimo, poizvedba vrne vse vrstice tabele. V
relacijski algebri jestavek WHEREekvivalent selekciji.
Da MySQL uporabi indeks, mora stavekWHERE ustrezati naslednjim zahtevam [21]:
dovoljeni operatorji so =, >, >=, <, <=in BETWEEN,
pogojno lahko uporabimo tudi operator LIKE, vendar samo v primeru, da se maskirni
znakpojavi drugje kot na začetku konstante (primer: LIKE '%primer' ne bo uporabil
indeksa, LIKE 'primer%' pa bo uporabil indeks),
logični operator med osnovnimi delistavka WHERE mora biti operator AND,
paziti moramo tudi na vrstni red polj v indeksu; indeks i_2(A, B, C) ni enak indeksu i_1(A,
C, B), ko združujemo indekse iz različnih poizvedb.
14
Primeri SQL stavkov, kjer MySQL uporabi indekse:
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
Primeri SQL stavkov, kjer MySQL ne uporabi indeksov:
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;
SELECT * FROM tbl_name WHERE index_part1=1 OR index_part2=10;
2.3.4 Stavek UNION
Stavek UNION uporabljamo, da združimo rezultate več SELECT stavkov v en sklop rezultatov. V
relacijski algebri je stavku UNION ekvivalentna operacija unija.
SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
Sintaksa 4: Stavek UNION [24].
Primer poizvedbe UNION:
(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a
FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
2.3.5 Stavek JOIN
Stavek JOIN združi vrstice iz dveh ali večtabel v en sklop vrstic. Opcijski ukazindex_hint je
razčlenjen v naslednjem poglavju.MySQL uporabi indeks na stolpcih,ki so uporabljeni v
opcijijoin_condition.Vrstice lahko združimo tudi sstavkom WHERE. Ta možnost je
prikazana v primerih.
15
table_references:
table_reference [, table_reference] ...
table_reference:
table_factor
| join_table
table_factor:
tbl_name [[AS] alias] [index_hint]
| table_subquery [AS] alias
| ( table_references )
| { OJ table_reference LEFT OUTER JOIN table_reference
ON conditional_expr }
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN
table_referencejoin_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
join_condition:
ON conditional_expr
| USING (column_list)
Sintaksa 5: Stavek JOIN [25].
Primeri JOIN poizvedb, ki ustrezajo sintaksi 5:
SELECT A.*, B.* FROM tbl_name AS A, tbl_name AS B WHERE A.col1 = B.col1
SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON
t1.name = t2.name;
SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name =
t2.name;
SELECT * FROM (SELECT 1, 2, 3) AS t1;
SELECT t1.name, t2.salary FROM employee AS t1 LEFT JOIN b USING (c1,c2,c3)
SELECT left_tbl.*
FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON
left_tbl.id = right_tbl.id } WHERE right_tbl.id IS NULL;
2.3.6 Indekspopravki
Če v poizvedbi uporabimo ukaza USE INDEX aliFORCE INDEX, MySQLpri dani poizvedbi uporabi
enega od naštetih indeksov v opciji index_list. Z ukazom IGNORE INDEX MySQL določenega
indeksa ne uporabi. Poleg omenjenih možnosti je na voljo še ključna beseda FOR, ki ji sledijo
JOIN, ORDER BY ali GROUP BY.
16
tbl_name [[AS] alias] [index_hint_list]
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name] ...
Sintaksa 6: Sintaksa uporabe indekspopravkov [26].
Primeri SQL poizvedb, ki ustrezajo sintaksi 6:
SELECT * FROM table1 USE INDEX (col1_index, col2_index) WHERE col1=1 AND
col2=2 AND col3=3;
SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND col2=2 AND
col3=3;
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
2.3.7 Stavek ORDER BY
Stavek ORDER BY uporabljamo za sortiranje vrstic. Sortiramo jih na dva načina. Če v poizvedbi
poleg stavka ORDER BY uporabimo ključno besedo ASC, bodo vrstice sortirane od najmanjše
proti največji vrednosti in obratno v primeru uporabe ključne besede DESC. Da lahko MySQL
uporabi indeks, mora stavek ustrezati naslednjim zahtevam [27]:
vsi stolpci imajo enako vrsto sortiranja(ASC ali DESC),
uporablja lahko samo imena stolpcev in ključni besedi ASC ali DESC, ne pa tudi izrazov in
funkcij,
sestavljen je samo iz stolpcev iste tabele.
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
Sintaksa 7: Stavek ORDER BY [21].
Primeri SQL stavkov, kjer MySQL uporabi indekse:
SELECT * FROM t1 ORDER BY key_part1, key_part2, ...;
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
17
Primeri SQL stavkov, kjer MySQL ne uporabi indeksov:
SELECT * FROM t1 ORDER BY key1, key2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
SELECT * FROM t1 ORDER BY ABS(key);
2.3.8 Stavek GROUP BY
Stavek GROUP BY v povezavi z agregatnimi funkcijami (angl.: aggregate functions) uporabljamo,
da združimo vrstice. Da lahko MySQL uporabi indeks, morastavek ustrezati naslednjima
zahtevama [28]:
vstavku SELECT sta ediniuporabljeni agregatni funkciji MIN in MAX,
paziti moramo tudi na vrstni red polj v indeksu;indeks i_2(A, B, C) ni enak indeksu i_1(A,
C, B), ko združujemo indekse iz različnih poizvedb.
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
Sintaksa 8: Stavek GROUP BY [21].
Ob predpostavki, da imamo postavljena dva indeksai_1(c1, c2, c3) in i_2(c1, c2, c3, c4),ju MySQL
uporabi v naslednjih primerih:
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
Primeri stavkov, kjer MySQL ne uporabi indeksov:
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
18
3 PHP
PHP jesplošno namenski programski jezik, prvotno namenjen izdelavi dinamičnih spletnih
strani. PHP kodo vključimo kar neposrednov HTML stran, izvršena pa je vsakokrat, ko je naša
stran obiskana[6, 29].
Jezik je leta 1994 ustvarilRasmus Lerdorf. Kmalu zatem ga je posvojilo veliko število razvijalcev,
ki so ga pripeljali skozi štirivečje verzije. Trenutno je aktualna verzija 5.4, ki je izdana marca
2013.Kljub temu naše ogrodje deluje s PHP verzijo od 5.1naprej, s čimer omogočamo
združljivost sstarejšimi projekti.
Kljub prvotnemu namenu programiranja dinamičnih spletnih strani se je jezik kasneje razvil in
danes omogoča samostojno pisanje namiznih aplikacij ter programiranje skript za vmesnik z
ukazno vrstico (angl.:command-line interface, CLI) [30]. Prva možnost ni tako zanimiva in je
redko uporabljena, zadnja možnost pa je bila uporabljena za zagon našega ogrodja.
Razlog, da je izvorna koda našega ogrodjanapisana v PHP, tiči v tem, da je ta najbolj uporabljen
jezik za spletne strani[31] in je v navezi z MySQL in Apache spletnim strežnikom tudi eden
najbolj uporabljenih paketov za poganjanje spletnih strani.
Prednosti PHP [29, 32]:
poceni in efektivna horizontalna skalabilnost,
poleg MySQL podpore se je možno povezati tudi na PostgreSQL, Oracle, DB2itd.,
ker je bil PHP zasnovan za uporabo na spletu, ima veliko vgrajenih funkcij, ki so uporabne za
splet; primeri so povezovanje na spletne povezovalne tehnologije (angl.: web services), XML
podpora, piškotkiali pošiljanje spletne pošte,
od verzije 5 dalje ima močno podporo za objektno programiranje (angl.: object oriented
programming);ker se zgleduje po Javi in C++, ima skoraj vse, kar odlikuje objektni model
Jave in C++: dedovanje, privatne in javne deklaracije, abstraktne razrede, vmesnike (angl.:
interface), konstruktorje (angl.: constructor) in ostalo,
dostopnost na večini operacijskihsistemov, zaradi česar je izvorna koda prenosljiva,
je odprtokodni projekt in s tem brezplačen,
je najbolj podprt pri spletnih gostovanjih,
ima zelo obširno dokumentacijo, ki jo najdemo na uradni spletni strani in opisujevse aspekte
programskega jezika,zaradi razširjenosti jezika pa so na voljo izvorne kode ter ogromno
vtičnikov in drugih vsebin, ki namolajšajo razvoj programske opreme.
19
20
4 NALOGE OGRODJA
V poglavju 2.3 smo definirali pogoje, katerim mora poizvedba ustrezati, da lahko MySQL uporabi
indeks. Te pogoje mora naše ogrodje upoštevati pri predlogih, ki jih sintetizira. Preden indeks
postavimo, potrebujemo oceno njegove upravičenosti, kar ugotavljamo z naslednjo cenilko.
MySQL poskuša indekse shranjevati v pomnilnik, v kolikor je ta na voljo. V naslednji analizi smo
upoštevali, da se celoten indeks nahaja v pomnilniku. Analiza ne vsebuje časa, ki ga podatkovna
baza potrebuje za realokacijo podatkov v pomnilniku. Da lahko izračunamo,kolikšno je
maksimalno število operacij, ki jih zahtevajo poizvedbe, potrebujemo število poizvedb v
podatkovni bazi in časovno kompleksnost operacij, ki so zbrane v tabeli 2.
Brez indeksa
Z indeksom
SELECT
O(n)
O(log2 n)
INSERT
O(1)
O(log2 n)
UPDATE
O(1)
O(log2 n)
DELETE
O(1)
O(log2 n)
Tabela 2: Časovna zahtevnost operacij [5].
Izračunajmo,kolikšno je maksimalno število operacij, ki jih zahtevajote poizvedbe, če indeks ni
postavljen:
𝐶brez indeksov 𝑛, 𝑚𝑠 , 𝑚𝑖 , 𝑚𝑢 , 𝑚𝑑 = 𝑚𝑠 𝑛 + 𝑚𝑖 + 𝑚𝑢 + 𝑚𝑑
(1)
Pri tem smo uporabili naslednjo notacijo:
n število vrstic v tabeli,
ms število poizvedb v tabeli,
mi število vnosov v tabelo,
muštevilo posodobitev tabele,
md število zbrisov v tabeli.
Izračunajmo
še
maksimalno
število
operacij,
ki
jihzahtevajo
iste
poizvedbe,
če
jeindekspostavljen:
𝐶z indeksom 𝑛, 𝑚𝑠 , 𝑚𝑖 , 𝑚𝑢 , 𝑚𝑑
= 𝑚𝑠 log 2 𝑛 + 𝑚𝑖 log 2 𝑛 + 𝑚𝑢 log 2 𝑛 + 𝑚𝑑 log 2 𝑛
= log 2 𝑛 (𝑚𝑠 + 𝑚𝑖 + 𝑚𝑢 + 𝑚𝑑 )
21
(2)
Če delimoenačbo2zenačbo 1, dobimo:
𝐶 𝑛, 𝑚𝑠 , 𝑚𝑖 , 𝑚𝑢 , 𝑚𝑑 =
𝐶z indeksom
log 2 𝑛 (𝑚𝑠 + 𝑚𝑖 + 𝑚𝑢 + 𝑚𝑑 )
=
𝐶brez indeksov
𝑚𝑠 𝑛 + 𝑚𝑖 + 𝑚𝑢 + 𝑚𝑑
(3)
V kolikor je C ≥ 1, postavimo indeks, v nasprotnem primeru ga izpustimo.
Ko imamo na voljo oceno o koristnosti indeksa, se ogrodje odloči, ali je indeks koristno
uporabiti. Naše ogrodje poleg ocene pravilne postavitve indeksov pregleda SQL poizvedbe in
ugotovi napačne odločitve, ki se pojavijo v poizvedbah. Za te poizvedbe poda predloge za
izboljšave.
4.1 Stavek SELECT
Ogrodje v primeru uporabe maskirnega znaka (angl.: wildcard) vrne priporočilo, naj se temu
izogibamo, saj lahko poizvedba traja dlje zaradi morebitne velike količine vrnjenih podatkov.
4.2 Stavek WHERE
Ogrodjepoleg pravilne postavitve indeksov poda še naslednje predloge:
v primeru uporabe operatorja LIKE in maskirnega znaka pred konstanto (primer: LIKE
'%primer') nas opozori, da indeks ne bo uporabljen,
v primeru, da uporabimo kot logični operator operacijoOR, nam svetuje, naj raje
uporabimo stavek UNION in združimo dve poizvedbi.
4.3 Stavek ORDER BY
Ogrodjepoleg pravilne postavitve indeksov poda še naslednje predloge:
v primeru, da je stavek sestavljen iz stolpcev različnih tabel, nas opozori, da v tem
primeru ne moremo uporabiti indeksa in se lahko zgodi, da je poizvedba počasna,
če uporabimo različna vrstna reda sortiranja (ASC in DESC), nas ogrodje na to ustrezno
opozori.
4.4 Stavek GROUP BY
Poleg pravilne postavitve indeksov ogrodje v primeru, da je stavek sestavljen iz stolpcev
različnih tabel, opozori, da v tem primeru ne moremo uporabiti indeksa in se lahko zgodi, da je
poizvedba počasna.
22
5 ARHITEKTURA OGRODJA
V poglavjih2.3in 4smo naredili pregledmožnosti izboljšanja poizvedb teropredelili, kje in kako
postaviti indekse. V tem poglavju bomo predstavili implementacijo ogrodja, ki to izvede. V
prvem podpoglavju bomo na grobo opisali postopek analiziranja poizvedb, v naslednjih
podpoglavjih pa bomo vsak korak podrobneje razčlenili.
5.1 Opis postopka
Da naše ogrodje za optimizacijo poizvedb deluje, imamo v postopku analiziranja tri korake, kot
kaže slika 5.
Slika 5: Postopek analiziranja poizvedb
Vloga posameznih korakov je naslednja:
iskanje poizvedb:preden lahko analiziramo MySQL poizvedbe, jih moramo v PHP
izvorni kodi poiskati,čemur je namenjeni prvi korak postopka,
analiza in optimizacija poizvedb: ko smo MySQL poizvedbe našli, jih analiziramo; kot
izhod koraka so predlogi postavitve indeksovin predlogi za izboljšave poizvedb,
testiranje sprememb: ta korak je opcijski in se izvede,če se uporabnik odloči
zatestiranje časaizvajanja predlaganih izboljšav.
V nadaljevanju bomo vsakega od korakov podrobno opisali.
5.2 Iskanje poizvedb
Razred ScanCode preišče PHP izvorno kodo datotek in v njih vrine košček PHP kode. Te koščke
kode zažene razred Crawlerin s tem v podatkovno bazo shrani MySQL poizvedbe, katere
potrebujemo v drugem koraku. Razred FileManipulation je pomožni razred, namenjen
lažjemu delu z datotekami.
23
5.2.1 Razred ScanCode
Slika 6 je grobi opis poteka iskanja poizvedb v PHP kodi. Skozi datoteke projekta se sprehodimo
zrazredom RecursiveIterator, ki je del standardne PHP knjižnice [34].Najprej preverimo
PHP izvorno kodo, četa komunicira z bazo podatkov. To storimo zato, ker se inicializacija objekta
lahko zgodi v drugi datoteki, kot se zgodi MySQL poizvedba.MySQL objektezapišemo v
mysqliObjects lastnost razreda, da kasneje do njih dostopamo.
Slika 6: Diagram poteka iskanja MySQL poizvedb.
V PHP kodi iščemo veljavne poizvedbe tako, da jo najprej s PHP Tokenizer-jem spravimo v
zaporedje znakov sfunkcijotoken_get_all, katera sprejme izvorno kodo datoteke in vrne
poljeznakov [35]. Z njihovim pregledom nato algoritem poišče klice funkcij,v katerih se izvajajo
MySQL
poizvedbe.
Upoštevati
moramo
naslednje
[36,
37]:mysql_query,
query,
mysql_query, multi_query, mysqli, real_query in mysqli_realy_query. Prav tako
moramo upoštevati tudi predhodno najdene MySQLobjekte in z njimi klicane metode: query,
multi_query in real_query. Komentirano kodo ignoriramo.
24
V vrstice, kjer najdemo veljavne MySQL poizvedbe, vrinemo PHP kodo:
"\$_optimizeQuery = mysql_real_escape_string({query}); mysql_query(\"INSERT
INTO " . $config['MYSQL_SCHEMA'] . ".optimizeQueries (query) VALUES('\" .
\$_optimizeQuery . \"') \");"
Tanam zagotovi, da se bodo poizvedbe zapisale v tabelo optimizeQueries, ko bomo s spletnim
pajkom(angl.: web crawler), ustvarjenim vrazreduCrawler,preiskalispletne naslove (angl.:
Uniform Resource Locator, krat.: URL) projekta.
Na koncu moramo zapisati lokacije datotek in vrstice, kjer smo vrinili PHP kodo, da se izognemo
duplikatom v primeru,da uporabnik še enkrat zažene ukaz scanCode, in da lahko počistimo
datoteke za sabo in jih s tem vrnemo v prvotno stanje. Te informacije zapišemo v podatkovno
bazo.
5.2.2 Razred Crawler
Razred Crawler je spletni pajek. Spletni pajek je program, ki pregleduje splet [33]. V primeru
ogrodja pajek namestospleta preiskuje projekt, katerega spletni naslov je definiran v
nastavitveni datoteki. Namen razreda je vzpostavitev kode, ki smo jo predhodno vstavili v PHP
datoteke projekta z razredom ScanCode.
5.2.3 Razred FileManipulation
Razred je namenjen lažjemu delu v datotekah. Vsebuje metode, ki jih potrebujeta razreda
ScanCode in Run.
5.3 Analiza in optimizacija poizvedb
S prejšnjim korakom smo MySQL poizvedbe našli. Sedaj je naloga ogrodja, da jih analizira in
ugotovi, kje postaviti indekse in kje je prostor za izboljšave poizvedb.
5.3.1 Razred AnalyzeQueries
Razred AnalyzeQueriespredhodno najdene poizvedbe servira razredu MySQLParser, ki
nato vrača predloge za optimizacijo,razred IndexFilterpa jih po potrebi filtrira. Na koncu na
željo uporabnikarazred izvede šeprimerjalne teste in zapiše potrebne indekse v bazo.
5.3.2 Razred MySQLParser
Razred MySQLParser je jedro našega ogrodja.Kot vhod mu razred AnalyzeQueries podaja
MySQL poizvedbe, sam pa nato ugotavlja, kje v posamezni poizvedbi postaviti indekse in kje je
prostor za izboljšave poizvedb.
25
V prvem korakuz regularnim izrazom (angl.: regular expression) razbijemo poizvedbe v
zaporedje znakov[7], s katerimi kasneje operira večina metod razreda(slika7).
Slika 7: Diagram poteka optimizacije poizvedbe.
26
Regularni izraz je osnovan na regularnem izrazu za Perl, ki ga je napisal Igor Sutton [38]:
((?:--|\#)[\ \t\S]*|(?:<>|<=>|>=|<=|==|=|!=|!|<<|>>|<|>|\|\||\||&&|&||\+|\*(?!\/)|\/(?!\*)|\%|~|\^|\?)|[\[\]\(\),;`]|\'\'(?!\')|\"\"(?!\"")|".*?(?:(?:""){1,}"|(?<!["\\])"(?!")|\\"{2}
)|'.*?(?:(?:''){1,}'|(?<!['\\])'(?!')|\\'{2})|\/\*[\ \t\n\S]*?\*\/|(?:[\w:@]+(?:\.(?:\w+|\*)?)*)|[\t\
]+|[\.]|[\s])
Sledipreverjanje,ali je poizvedba tipa SELECT. Četemu ni tako in gre za stavek tipa INSERT,
UPDATE ali DELETE, preštejemo število izvajanj posameznega stavka. Slednje kasneje
potrebujemo za izračun cenilke postavljanja indeksov.
Če gre za stavek SELECT, preverimo, ali naša poizvedba vsebuje psevdonime. Ti dodelijo tabeli
ali stolpcu drugačno ime, kiga lahko nato uporabljamo v isti poizvedbi. Primera psevdonimov:
SELECT column AS psevdonim1 FROM table
SELECT column FROM table AS psevdonim2
Nato v teh stavkih iščemo stolpce, ki so kandidati za indekse, kot to definira 2.3 poglavje. To
storimo za stavke WHERE, ORDER BY, GROUP BY in JOIN. Algoritem sloni na tem, da se sprehodi
čez znake poizvedbe, pri čemer gleda, ali znak ustreza imenustolpca v tabeli,ter preveri,
aliznakiv okolici ustrezajo SQL sintaksi in pravilom za postavitev indeksov.Nazadnje
upoštevamo še indekspopravke, kot je to definirano v poglavju2.3.6.
5.3.3 Razred IndexFilter
Razred IndexFilterfiltriraindekse, ki jih predlaga razred MySQLParser. Med podanimi
predlogi odstrani podvojene indekse, jih združuje skupaj in jih na podlagi cenilke, definirane v
poglavju 4, izloča ali ohranja.
5.4 Testiranje
Po analizi poizvedb imamo podane predloge za postavitve indeksov in za izboljšave poizvedb. Da
izvemo, ali so predlogi smiselni, jih je potrebno preizkusiti. Temu je namenjen zadnji in opcijski
korak. Razred Benchmarkizvaja meritve, razred DataGenerator pa generira podatke, ki jih
morebiti potrebujemo za bolj verodostojne meritve.
Pred začetkom testiranja ogrodje zažene naslednjiMySQL stavek:
SET SESSION query_cache_type = OFF
27
Taje namenjen temu, da MySQL ne shranjuje rezultatov poizvedb v predpomnilniku (angl.:
cache), ker bi to vplivalo na čase trajanj poizvedb. Ogrodje nato zažene poizvedbe, ki jih je
predhodno lociralo v PHP kodi, s predhodno postavljenimi indeksi in si zabeleži čase trajanja.
Sledi isti postopek, tokrat z indeksi,predlaganimi od ogrodja. Sledi izračun povprečnih časov ter
pripadajočih izboljšav ali poslabšanj, izraženih v odstotkih. Nazadnje se podatkovna baza vrne v
prvotno stanje.
5.4.1 Razred Benchmark
Razred je namenjen shranjevanju časaizvajanja meritev, katere uporabimo, da primerjamo, ali so
naši predlogi izboljšav izvedeni časovno hitreje kot rezultati prvotnega stanja.
5.4.2 Razred DataGenerator
Razred generira podatke, ki jih potrebujemo pri preizkušanju izboljšav. Potrebujemo ga, kadar
imajo tabele premalo zapisov, da bi bili rezultati testiranja verodostojni. Zapisi obstajajo samo v
času testiranja.
Jedro razreda je metoda generateData, ki glede na podano tabelo ustvari ustrezne podatke,
ki ustrezajo podatkovnim tipom, ki so definirani v specifikaciji tabele (slednjo podarazred
AnalyzeQueries s pripadajočo metodo
loadSchema). MySQL podpira naslednje
podatkovne tipe: tinyint, smallint, mediumint, int oz. integer, bigint, float, double, double
precision, real, decimal oz. numeric, date, datetime, timestamp, time, year, char, varchar,
tinyblob, tinytext, blob, text, mediumblob, mediumtext, longblob, longtext, enum in set[39].
Pri algoritmu za ustvarjanje tipov moramo paziti, da je polje, za katerega ustvarjamo vrednost,
lahko tuji ključ stolpca druge tabele, zato moramo izbrati naključno vrednost primarnega ključa.
Če je na polju postavljen unikaten ključ, moramo paziti, da generiramo unikatno vrednost.
5.5 UnitTest razredi
Ker ima naše ogrodje vsega skupaj okoli 7000 vrstic izvorne kode, je nujno preverjanje
posameznih modulov, če delujejo v skladu s pričakovanji. Temu je namenjeno testiranje enot, ki
programerjem pomaga identificirati in odpraviti hrošče ter služi kot dokumentacija za metodo,
na kateri izvajamo test. Enota je najmanjši del programa, v objektnem programiranju je to
ponavadi kar metoda. Metodo testiramo tako, da ji na vhod podamo argumente in opazujemo
njen izhod. Če se izhod ujema z našimi pričakovanji, metoda deluje pravilno. Idealno so vsi
individualni testi neodvisni med seboj, kar pa je težko dosegljivo (zaradi konstant, globalnih
spremenljivk, statičnih metod, itd.)[8].
28
Problema soodvisnosti se rešimo s strategijo,imenovano dependency injection, ki nam omogoča
odstranitev soodvisnosti v izvorni kodi. Eden od možnosti je uporaba vmesnikov namesto imena
razredov [40]. Primer vidimo v spodnji PHP kodi, kjer smo kot parameter metode namesto
razreda Config uporabili vmesnik ConfigInterface, ki ga kasneje med testiranjem enot
nadomestimo z imitiranim objektom (angl.: mock object)[41]:
public function __construct(ConfigInterface $config, MysqlInterface $mysql)
{
$this->config = $config; $this->mysql = $mysql;
}
V našem ogrodju so napisani testi enot za vsak pomembni del izvorne kode. Vsega skupaj je 93
testnih enot s 165 testnimi primeri, pokritost kode s testnimi enotami pa je 95 %. Kot osnova za
testiranje
je
bilo
uporabljeno
PHPUnit
ogrodje.
soConfigDataGeneratorUnitTest,
Imitirani
razredi
ConfigCrawlerUnitTest,
ConfigIndexFilterUnitTest, ConfigScanCodeTest in MySQLScanCodeUnitTest.
5.6 Pomožni razredi
Poleg do sedaj omenjenih razredov se v ogrodju nahajajo še pomožni razredi, ki so
namenjenilažji interakciji z bazo in nastavitvami, ter skrbe za zagon ogrodja preko ukazne
vrstice.
5.6.1 Razred Run
Ogrodje
se
zaganjaz
razredomRun,kivzpostavipomožne
objekte
(MySQL,
Config,
FileManipulation) in prebere argumente, ki smo jih podali preko ukazne vrstice. Glede na
uporabnikov vhodnato zažene primerno operacijo.
5.6.2 Razred Config
Razred Config je namenjen shranjevanjunastavitev, da do njih lažje dostopamo znotraj drugih
razredov. Pripadajoči razredConfigInterface je vmesnik, ki je namenjen testiranju enot
(angl.: unit test).
29
5.6.3 MySQL razredi
MySQL razredi so namenjenilažjemudelu z bazo. Vmesnik MySQLInterface definira, katere
metode morajo vsebovati razredi, ki ga implementirajo. Obenem nam je v pomoč pri testiranju
enot, da lahko naredimo imitiran objekt baze, s čimer se izognemo soodvisnosti posameznih
modulov in testiranju na podatkovni bazi. RazredaMySQLNormal in MySQLImproved
razširjatarazredMySQL.Kateri podrazred MySQL razreda bo uporabljen, odloča Run razred, ki
izbere vtičnik MySQLi, vkolikor je ta na voljo, drugače izbere MySQL. Z instancoobjekta MySQL
nato izvajamo poizvedbe squery metodo.MetodigetRecords in getOneRecordvrneta
rezultate poizvedbe v obliki polja. Razreda vsebujeta tudi metodi za transakcije, in sicer commit
in rollback. Metoda getNoOfRecordsvrne število vrstic v trenutni poizvedbi.
Slika 8: Razredni diagrampoenotenega jezika modeliranja(angl.: Unified Modeling Language, krat.:
UML) MySQL razredov.
30
6 REALIZACIJA OGRODJA
V prejšnjem poglavju smo opisali arhitekturo ogrodja. V tem poglavju pa bomo v desetih
podpoglavjih podrobneje opisali knjižnice, metode in lastnosti razredov.
6.1 Razred Run
Ogrodje zaženemo s priklicem razreda Run,kivzpostavi potrebne objekte (MySQL, Config,
FileManipulation) in prebere argumente, ki smo jih podali preko ukazne vrstice. Na podlagi
vhoda ukazne vrstice se zgodi naslednje:
v primeru, da zaženemo ogrodje brez vhoda ali pa ga kličemo z ukazom info, se kliče
metoda runInfo, ki nam izpiše možne ukaze,
ukaz
compatibilityTest
zaženemetodo
runCompatibilityTest,
ki
preveri,
čeuporabnikovo okolje ustrezaminimalnim zahtevam, ki so potrebne za delovanje
ogrodja,
uporaba ukaza scanCodezaženemetodo
runScanCode, kiz razredomScanCode
preišče PHP datoteke projekta in poišče MySQL poizvedbe,
klic ogrodja z ukazom crawlerzažene metodo runCrawler, ki vzpostavi spletnega
pajkaz razredom Crawler, ki omogoči, da se zažene koda, dodana z ukazom scanCode,
ukaz
analyzeQueries
zažene
metodo
runAnalyzeQueries,
ki
z
razredom
AnalyzeQueries analizira predhodno zbrane poizvedbe v bazi in jih s pomočjo
MySQLParser in IndexFilter optimizira; na koncu lahko izvedemo še primerjalne
teste in izbrane indekse zapišemo v bazo,
ukazcleanup zažene metodo runCleanup, ki zrazredomScanCodevrne spremenjene
datoteke in podatkovno bazo v prvotno stanje,
ukazsetupConfigzažene metodo runSetupConfig, ki nastavi nastavitveno datoteko.
31
Slika 9: Razredni diagram UML Run razreda.
Metode isWindows, success, info, failure in readInputCLI so namenjene izpisu in
vnosu podatkov iz in v orodno vrstico. RazredRunExceptionje namenjen prikazu izjem, ki se
pojavijo v času poganjanja ogrodja.
6.2 Razred Config
Slika 10 prikazuje metode, njihove vhode in lastnosti razreda Config, katere bomo v
nadaljevanju podrobneje opisali.
Slika 10: Razredni diagram UML razreda Config.
32
Metode razreda:
Metoda __constructzapiše v lastnosti razreda nastavitvene vrednosti, ki so
natančneje razložene v dodatku B.
Metoda loadje namenjena morebitnemu naknadnemu nalaganju nastavitev, ko je bil
objekt že vzpostavljen.
Metoda set zapiše nastavitveno vrednost v lastnost razreda.
Z metodo __get dostopamo do nastavitev, shranjenih kot lastnosti razreda.
6.3 Razred FileManipulation
Slika 11 prikazuje metode, njihove vhode in lastnosti razreda FileManipulation, katere
bomo v nadaljevanju podrobneje opisali.
Slika 11: Razredni diagram UML razreda FileManipulation.
Metode razreda:
Metoda getFile prebere datoteko innjene vrstice vrne v obliki polja.
Metoda replaceFile zamenja vrstice v datoteki. Kliče metodo writeToFileArray.
Metoda writeToFileArray zapiše parameter contentv datoteko.
6.4 Razred ScanCode
Slika 12 prikazuje metode, njihove vhode in lastnosti razreda ScanCode, katere bomo v
nadaljevanju podrobneje opisali.
33
Slika 12: Razredni diagram UML razreda ScanCode.
Lastnosti razreda:
Polje replaceArray: lokacije MySQL poizvedb.
Niz directory: pot projekta, ki ga bomo pregledali.
Objekt MySQL.
ObjektFileManipulation.
Objekt Config.
Polje mysqliObjects: lokacije MySQLi objektov.
Metode razreda:
Metoda __constructvzpostavi objekt.
Metoda
setReplaceArray
je
namenjena
testiranju
enot,
z
namenom
ponastavitvereplaceArray lastnosti razreda.
Metoda scanDirectoryForObjectsrekurzivno poišče datoteke v direktoriju in
poddirektorijih projekta. Če je datoteka PHP tipa, kliče metodo findObjects.
Metoda scanDiretoryrekurzivno poišče datoteke v direktoriju in poddirektorijih
projekta. Če je datoteka PHP tipa, kliče metodo findQuery.
Metoda findObjects pregleda vsebino PHP datoteke in išče vzpostavitev MySQL
objektov.
Vrednosti
spremenljivk,
ki
kažejo
na
objekt
MySQL,shrani
v
mysqliObjectslastnost razreda.
Metoda findQuery pregleda vsebino PHP datoteke in v replaceArraylastnost
razreda zapiše vrstico kode, kjer je bila najdena poizvedba.
34
Metoda replaceFiles je iterator čez replaceArraylastnost razreda. Ob vsaki
iteraciji kliče metodo writeToFile.
Metoda writeToFilevstavi v datoteko PHP košček kode, omenjen v poglavju 5.2.1, v
datoteko.
Metoda
writeToDB
zapiše
v
tabelo
optimizeReplacedFiles
(ki
je
bila
predhodnoustvarjena) lokacijo in položaj sprememb v datotekah.
Metoda cleanup vrne spremenjene datoteke in podatkovno bazo v prvotno stanje.
6.5 Razred Crawler
Slika 13 prikazuje metode, njihove vhode in lastnosti razreda Crawler, katere bomo v
nadaljevanju podrobneje opisali.
Slika 13: Razredni diagram UML Crawler razreda.
Lastnosti razreda:
Polje checkedUrls: URL naslovi že obiskanih strani.
Polje parseUrl: URL projekta, shranjenega zmetodo parse_url.
Objekt Config.
Metode razreda:
Metoda __constructvzpostavi objekt.
Metoda getCheckedUrls vrne seznam strani, ki so bile preverjene s spletnim pajkom.
Metoda initCrawlPage vzpostavi spletnega pajka. Kličemetodo crawlPage.
Rekruzivna metoda crawlPageima kot vhod parameter depth, ki nam pove, koliko
nivojev spletnih povezav bo pajek obiskal. Če je argument 1, bo obiskal samo domenski
URL, če je argument 2 ali več, pa tudi ustrezno število podnivojev. Metoda dobiargument
depth iz nastavitev.
Metoda rel2abs pretvori relativni URLv absolutni URL.
35
Metoda curlDownloadvzpostavi URL klic na spletni naslov. cURL je orodje za orodno
vrstico, ki omogoča prenašanje podatkov z URL sintakso.Podpira HTTP, HTTPS, FTP,
Gopher, Telnet, DICV, file in LDAP protokole. PHP v celoti podpira knjižnicocURL [42].
6.6 Razred AnalyzeQueries
Slika 14 prikazuje metode, njihove vhode in lastnosti razreda AnalyzeQueries, katere bomo v
nadaljevanju podrobneje opisali.
Slika 14: Razredni diagram UML razreda AnaylzeQueries.
Lastnosti razreda:
Objekt Config.
ObjektMySQL.
Polje testParameters: hrani vrednosti dveh nastavitev uporabnika (samodejna
nastavitev indeksovin izvajanje testiranja).
Polje columns:hrani podatke o strukturi tabele in pripadajočih vrsticah v podatkovni
bazi.
Polje indexes: hrani podatke o strukturi indeksov v podatkovni bazi.
Metode razreda:
Metoda __constructvzpostavi objekt.
Metoda optimizeIndexes vzpostavi razredaMySQLParser in IndexFilter.
Zažene primerjalne teste in po potrebi zapiše nove indekse v bazo.
MetodaremoveCharactersodstrani znake iz ukazne vrstice.
Metoda queryForIndexesustvari besedilo s priporočeno postavitvijo indeksov glede
na argumentaindexQuery in dropOldIndex.
36
Metoda loadSchema shrani stolpce tabele in njihove pripadajoče indekse v columns in
indexeslastnostih razreda.
Metoda generateQuery ustvari SQL kodo za vnos podatkov iz polja data, v tabelo
table.
6.7 Razred MySQLParser
Slika 15 prikazuje metode, njihove vhode in lastnosti razreda MySQLParser, katere bomo v
nadaljevanju podrobneje opisali.
Slika 15: Razredni diagram UML razreda MySQLParser.
37
Lastnosti razreda:
Polje filter: znaki, ki jih želimo odstraniti.
Polje allowedTypes: tipi poizvedb, ki so relevantne za optimizacijo.
Polje statements: ločila stavka SELECT.
Polje joinSeparators: ločila stavkov JOIN in SELECT.
Polje logicalOperators: logični operatorji v MySQL.
Polje aggregateFunctions: agregatne funkcije, ki se uporabljajo v stavku GROUP BY.
Polje queries: hrani poizvedbo in njene morebitne pripadajoče podpoizvedbe v obliki
znakov.
Polje queriesText: hrani poizvedbo in njene morebitne pripadajoče podizvedbe v
tekstovni obliki.
Polje columns: izhodni parameter, ki shrani stolpce, ki so potencialni kandidati za
postavitev indeksa.
Polje tokens:znaki poizvedb.
Polje tableNames: hrani imena tabel in pripadajočih psevdonimov, ki se pojavijo v
poizvedbi.
Polje columnNames: hrani imena stolpcev in pripadajočih psevdonimov, ki se pojavijo v
poizvedbi.
Polje databaseColumns: hrani podatke o strukturi table in pripadajoči vrsticah v
podatkovni bazi.
Polje databaseIndexes: hrani podatke o strukturi indeksov v podatkovni bazi.
Polje optimizationMessages: sporočila za optimizacijo SQL poizvedb.
Metode razreda:
Metoda __constructvzpostavi objekt.
Metoda splitQueries razdrobi SQL poizvedbo na več delov, v kolikor je ta ločena z
podpičjem.
Metoda checkTypepreveri, ali poizvedba ustreza kriterijem optimizacije (biti mora
tipa SELECT, INSERT, UPDATE ali DELETE).
Metoda getColumns vrne columns lastnost razreda.
Metoda
getOptimizationMessagesvrne
optimizationMessages
lastnost
razreda.
Metoda getSubTokens vrne znake, ki ustrezajoparametru type. Če je naprimer
argument type enak WHERE, bomo dobili WHERE znake.
38
Metoda checkIUDpreveri,koliko INSERT, UPDATE ali DELETE stavkov vsebuje
poizvedba.
Metoda checkAliasespreveri psevdonime s klicem metodcheckAliasesSelect in
checkAliasesFrom.
Metoda checkAliasesSelectshrani psevdonime, ki se pojavijo v stavku SELECT, in
jih shrani v columnNames lastnost razreda.
Metoda checkAliasesFrom shrani psevdonime, ki se pojavijo v stavkihFROM in JOIN,
v tableNameslastnost razreda.
Metoda
kličemetodeoptimizationColumnsWhere,
optimizationColumns
optimizationColumnsSelect,
optimizationColumnsOrderBy,
optimizationColumnsGroupBy in optimizationColumnsJoin.
Metoda optimizationColumnsWhere išče ustrezne stolpce ali kombinacijo stolpcev,
ki bi bili primerni za postavitev indeksa v stavkuWHERE. Upoštevati moramo vsa pravila,
ki smo jih definirali v poglavju 2.3.3.
Metoda optimizationColumnsSelect pregleda, ali stavek SELECT vsebuje funkcije
iskanja po polnem besedilu.
Metoda optimizationColumnsGroupByišče ustrezne stolpce ali kombinacijo
stolpcev, ki bi bili primerni za postavitev indeksa v stavku JOIN. Upoštevati moramo vsa
pravila, ki smo jih definirali v poglavju 2.3.8.
Metoda optimizationColumnsJoin išče ustrezne stolpce ali kombinacijo stolpcev,
ki bi bili primerni za postavitev indeksa v stavku JOIN. Upoštevati moramo vsa pravila, ki
smo jih definirali v poglavju 2.3.5.
Metoda checkIndexHintišče ustrezne znake, ki ustrezajo sekciji indeks popravkov
(USE, IGNORE, FORCE) in jih posreduje resolveIndexHint metodi.
Metoda resolveIndexHint, vkolikor poizvedba vsebuje indekspopravke, upošteva
pravila, definirana v poglavju 2.3.6.
Metoda resolveTokenNamerazreši znak, v kolikor ta vsebuje psevdonim,ter ga
pretvori nazaj v originalno ime.
Metoda getSubQueries razbije poizvedbe na več podizvedb, v kolikor te obstajajo.
Podizvedbe se shranjujejo v queries lastnost razreda.
Metoda tokenize z regularnim izrazom razbije poizvedbe v zaporedje znakov.
Metoda removeFilterChars iz znakov poizvedbe odstrani znak, vkolikor je enak eni
od filter lastnosti.
39
6.8 Razred IndexFilter
Slika 16 prikazuje metode, njihove vhode in lastnosti razreda IndexFilter, katere bomo
podrobneje opisali.
Slika 16: Razredni diagram UML razreda IndexFilter.
Lastnosti razreda:
Polje columns: hrani podatke o strukturi tabele in pripadajočih vrsticah v podatkovni
bazi.
Polje indexes: hrani podatke o strukturi indeksov v podatkovni bazi.
Objekt Config.
Polje noOfIUD: hrani vrednost, koliko stavkov INSERT, UPDATE in DELETE se je izvedlo
v posamezni tabeli.
Polje noOfSelects: hrani vrednost, koliko SELECT stavkov se je izvedlo v posamezni
tabeli.
Metode razreda:
Metoda __constructvzpostavi objekt.
Metoda extractIndexes na podlagi enačbe 3 ugotovi, ali bo postavila indeks ali ne. V
primeru, da je cenilka C ≥ 1, postavi med vsemi možnimi indeksi, ki jih predlaga razred
MySQLParser, samo tiste, ki ustrezajo pravilom, definiranim v poglavju 2.3.
Metoda groupIndexesišče identične indekse in jih združuje.
Metoda reshapeIndexespreuredi vhodoptimizeIndexes v groupedIndexes, da
je slednjibolj primeren za metodo removeSetIxdexes.
Metoda removeSetIndexes odstrani indekse, ki že obstajajo v podatkovni bazi.
40
Metoda removeDuplicateIndexes odstrani indekse, ki so nepotrebni (indeks i_1(A,
B) je v MySQL enak indeksu i_2(A, B, C), zato moramo slednjega odstraniti).
6.9 Razred Benchmark
Slika 17 prikazuje metode, njihove vhode in lastnosti razreda Benchmark, katere bomo v
nadaljevanju podrobneje opisali.
Slika 17: Razredni diagram UML razreda Benchmark.
Lastnosti razreda:
Polje startTime: začetni časi v obliki UNIX časovnega žiga (angl.: timestamp).
Polje savedTimes: končni časi testiranja v milisekundah.
Metode razreda:
Metoda startTime se kliče pred začetkom testiranja. Parameter group predstavlja
ime, ki ga želimo uporabiti za trenutni sklop rezultatov.
Metodo endTime se uporabi ob zaključku testiranja.
Metoda showTimevrne čas testiranja,shranjenega v polju savedTimes. Parametra
group in index predstavljata pozicijo rezultata, ki se nahaja v polju savedTimes.
Metoda showAverage uredirezultate po velikosti in izloči zgornjih in spodnjih 10 %
rezultatov ter vrne povprečje. Parameter size definira število decimalnih mest
rezultata, ki ga metoda vrne.
Metoda roundToSignificantDigit zaokroži število number na n pomembnih
mest. Parameter roundCeil definira, ali se število zaokroži navzgor ali navzdol.
Metoda standardDeviationvrne strandardni odklon, izračunan iz parametra values.
Metoda standardError vrne standardno napako. Parameter standardDeviation
predstavlja standardni odklona, parameter sampleSize pa število vzorcev.
41
Metoda roundMeanAccToUncertaintyštevilo mean zaokroži na enako število
decimalk, kot jo ima uncertainty število.
6.10 Razred DataGenerator
Slika 18 prikazuje metode, njihove vhode in lastnosti razreda DataGenerator, katere bomo v
nadaljevanju podrobneje opisali.
Slika 18: Razredni diagram UML razreda DataGenerator.
Lastnosti razreda:
Polje columns: hrani podatke o strukturi tabele in pripadajočih vrsticah v podatkovni
bazi.
Polje indexes: hrani podatke o strukturi indeksov v podatkovni bazi.
Objekt Config.
Metode razreda:
Metoda __constructvzpostavi objekt.
MetodagenerateDataiterira skozi stolpce v tabletabeli in glede na tip stolpca
pokliče metodo generateRecords.
Metoda generateRecordsustvari podatke za vpis v bazo. Preveri, ali je zahtevan
podatek unikaten ter prisotnost tujega ključa.
Metoda
generateRandomIntegervrne
naključno
število,
dolžine
parametra
length.
MetodagenerateRandomFloat vrne naključno decimalno število, dolžineparametra
length.
MetodagenerateRandomDecimalvrne naključno decimalno število.
42
Metoda generateRandomString vrne naključni niz, dolžine parametra length.
Metoda generateRandomDatevrne naključen datum, ki je definiran med vrednostima
01.01.1000 00:00:00 in 9999-12-31 23:59:59.
Metoda generateRandomEnum ustvari naključno enum vrednost. Enum je niz,
katerega vrednost je izbrana iz polja dovoljenih vrednosti, definiranih ob kreiranju
tabele.
Metoda generateRandomBit generira naključno binarno vrednost.
43
44
7 UPORABA OGRODJA
V tem poglavju bomo predstavili uporabo ogrodja. Prvo podpoglavje je namenjeno predstavitvi
potrebne datotečnestruktureprojekta, ki ga želimo optimizirati. Sledijo možne uporabeogrodja.
7.1 Struktura datotek
Predpostavljamo, da ima uporabnikže nameščeno programsko opremo, ki omogoča poganjanje
paketov PHP in MySQL.
Potrebna
programska oprema zajema operacijski
sistem
Windows,spletni strežnik Apache,podatkovno bazo MySQL inprogramski jezik PHP(krat.:
WAMP)aliustrezno ekvivalentno zbirko na operacijskem sistemu Linux(krat.: LAMP).
Na sliki 19vidimo priporočeno osnovno strukturoogrodja spripadajočim projektom. V
direktoriju »myProject« se nahaja PHP projekt, kateremu bi radi optimizirali MySQL poizvedbe.
Slika 19: Direktorijska struktura projekta.
45
V »FMQO« direktoriju se nahaja naše ogrodje z vsemi pripadajočimi datotekami. V
»config«poddirektoriju se nahajajo nastavitve ogrodja, katere so podrobno predstavljene v
prilogi A.Poddirektorij »Library«vsebuje knjižnice, ki so jedro našega ogrodja, »tests«vsebuje
datoteke s testnimi enotami, kizagotavljajo, da knjižnice delujejo v skladu s pričakovanji.
Nazadnje je tu še »run.php« datoteka, katere naloga je izvajanje operacij, ki jih ogrodje
omogoča.Za direktorij »FMQO« ni potrebno, da se nahaja v istem domenskem direktoriju, kot se
nahaja projekt, je pa priporočena praksa.
7.2 Ukazi orodja
Ogrodje zaganjamo z ukazno vrstico. Teče tako na operacijskem sistemu Linux kot na
operacijskem sistemu Windows.V naslednjih sedmih podpoglavjih bomo predstavili možnosti, ki
jih ogrodje ponuja. Izpisi ukazov so v dodatku B.
7.2.1 Ukaz info
Ukaz prikaže možnosti, ki jih lahko uporabimo pri zagonu ogrodja,ter njihovo obrazložitev.
7.2.2 Ukaz compatibilityTest
Ukaz sporoči, ali ima uporabnik nameščene potrebne knjižnice. Če jih nima, uporabnika
primerno opozori.
7.2.3 Ukaz scanCode
Ukazpregledadirektorij, podan v nastavitveni datoteki. Ustvari potrebne tabele v podatkovni
bazi in vrine potrebno PHP kodo v datoteke projekta.
7.2.4 Ukaz crawler
Ukazvzpostavi spletnega pajka, kise sprehodi čez spletne naslove projekta, da se vzpostavi koda,
dodana z ukazom scanCode. Pri izpisu nam poda spletne naslove, ki jih je pajek obiskal.
7.2.5 Ukaz analyzeQueries
Ukaz analizira poizvedbe, zbrane v podatkovni bazi. Pred analizo nas vpraša, ali želimo izvesti
primerjalni preizkus in ali želimo vpis predlaganih indeksov v bazo. Vrne priporočene indekse in
optimizacije, obenem pa nam pove,kolikšne hitrostne izboljšave lahko pričakujemo.
7.2.6 Ukaz cleanup
Ukazvrne datotekev prvotno stanje. Enako stori s podatkovno bazoob pogoju, da nismo zahtevali
vpisa predlaganih indeksov v bazo.
46
7.2.7 Ukaz setupConfig
Poleg spreminjanjanastavitvenih vrednostiv datoteki »config.conf.php« imamo to možnost tudi z
uporabo ukazasetupConfig. Spremenimo lahko vrednosti hostname, password, schema, project
path in projectUrl.Ostale vrednosti spremenimo v nastavitvenidatoteki.
47
48
8 TESTIRANJE OGRODJA
Testiranje ogrodja smo izvedli na dveh različnih testih. Prvi test vključuje podatkovno bazo, ki jo
MySQL priporoča kot testno podatkovno bazo za učenje MySQL[43]. Pri drugem testu smo
optimizirali projekt 1a-vreme, ki je na voljo na spletnem naslovu http://www.1a-vreme.si.
8.1 Metodologija testiranja
V rezultatih v naslednjih poglavjih je razš irjena negotovost določ ena kot s tandardna negotovost,
pomnož ena s faktorjem razš iritve k = 2, ki pri normalni porazdelitvi ustreza ravni zaupanja
približ no 95 %. Standardna negotovost je sestavljena iz standardne negotovosti povprečne
vrednosti, ki jo izračunamo z enačbo 4. Za izračunanje povprečne vrednosti upoštevamo
rezultate meritev, s tem da ne upoštevamo zgornjih in spodnjih 10 %urejenih meritev.
Standardni odklon vseh enot statistične populacije je definiran kot [9]:
𝑁
𝑖=1(𝑥𝑖
𝜎=
− 𝑥)
𝑁
(4)
kjer je xii-ta enota v statistični populaciji, 𝑥 aritmetična sredina populacije, N pa število vseh enot.
Standardna napaka vseh enot statistične populacije [9]:
𝐸𝜎 =
𝜎
𝑛
(5)
8.1.1 Anderson-Darling test
Ker bomo v nadaljevanju za naše teste računali standardni odklon in standardno negotovost,
predpostavljamo, da imajo časi testov normalno porazdelitev. Vendar, če je ta predpostavka
napačna, naši rezultati niso zanesljivi. V ta namen uporabimo Anderson-Darling test,ki nam
pove, ali naši podatki ustrezajo določeni porazdelitveni funkciji [10, 44].
𝑛
𝐴𝐷 =
𝑖=1
1 − 2𝑖
[ln 𝐹 𝑌𝑖
𝑛
+ ln
(1 − 𝐹 𝑌𝑛+1−𝑖 )] − 𝑛
49
(6)
Fje porazdelitvena funkcija, n je število vzorcev, Yi pa predstavlja i-ti vzorec iz sortirane tabele
vzorcev.V našem primeru bomo preverjali, če rezultati ustrezajo normalni porazdelitvi, zato je
porazdelitvena funkcija [45]:
𝐹 𝑥, 𝜇, 𝜎 =
1
2𝜋𝜎
𝑒
(𝑥−𝜇 )2
2𝜎 2
−
(7)
Simbol μpredstavlja aritmetično sredino populacije in σ standardniodklon (enačba4). V primeru,
da velja enačba 8, naši vzorci ne pripadajo pripadajoči porazdelitvi [10]:
𝐴𝐷 >
0.752
1+
0.75
𝑛
+
2.25
𝑛2
(8)
8.2 Test podatkovne bazeSakila
Podatkovno bazo Sakila je razvil Mike Hilyer, nekdanji član MySQL dokumentacijske ekipe, in je
namenjena kot primer standardne podatkovne sheme, ki se uporablja v knjigah, člankih in
tečajih [43].
Slika 20: Model intetitet in povezav (angl.: Entity–relationship, krat.: ER) diagram podatkovne
bazeSakila.
50
Podatkovna baza ima že definirane poglede, katerih poizvedbe so navedene v prilogi C.Za te so
indeksi že optimalno postavljeni. Za namen testiranja smo ustvarili testni projekt, kjer smo
uporabili omenjeneSQL poizvedbe. Z ogrodjem smo optimizirali podatkovno bazo, iz katere smo
odstranili vse pripadajoče indekse, ki se nahajajov prilogi D. Na podatkovni bazi s postavljenimi
indeksi od ogrodja smo nato pognali 33 testov in izračunali povprečje časov izvajanja teh testov
s pripadajočo standardno negotovostjo (metodologija testiranja je razložena v poglavju 8.1).
Nato smo naredili enakos prvotno postavljenimi indeksi in primerjali čase. Predlagani indeksi
ogrodja se nahajajo v prilogi D.
Predlagani indeksi
Prvotni indeksi
AD
0,23
0,34
Čas izvajanja
1,3882 s ± 3,4 ms
1,3541 s ± 3,8 ms
Izboljšava
–2,5 % ± 0,3 %
Tabela 3: Rezultati testiranja podatkovne bazeSakila.
Časov izvajanja stavkov brez postavljenih indeksov nismo merili, saj imajo poizvedbe veliko
število JOIN stavkov. Kot primer naj omenimo spodnjopoizvedbo, ki ima JOIN stavke v sedmih
tabelah: rental, inventory, store, address, city, country in staff.
SELECT
CONCAT(c.city, _utf8',', cy.country) AS store
, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN store AS s ON i.store_id = s.store_id
INNER JOIN address AS a ON s.address_id = a.address_id
INNER JOIN city AS c ON a.city_id = c.city_id
INNER JOIN country AS cy ON c.country_id = cy.country_id
INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
GROUP BY s.store_id
ORDER BY cy.country, c.city
Če ni postavljenih indeksov, MySQL ustvari začasno tabelo,ki je v tem primeru sestavljena iz
1,15939×1016 vrstic. Poizvedba bi brez postavljenih indeksov trajala zelo dolgo časa.Iz tega
lahko predpostavljamo, da bi primerjava pokazala, da je izboljšava blizu 100 %.Iz rezultatov
meritev je razvidno, daje čas izvajanja poizvedb z originalnimi indeksi približno 2,5 % hitrejši
kot v primeru izvajanja poizvedb z indeksi, ki jih predlaga ogrodje. Poraba prostora je v korist
ogrodju, saj podatkovna baza v prvotnem stanju zaseda 6,6 MB, z indeksi, postavljenimi z
ogrodjem, pa 4,3 MB.
51
8.3 Test podatkovne baze1a-vreme
Napodatkovni bazi 1a-vreme je bilo ogrodje na produkcijskem strežnikuvklopljeno slab dan. V
tem času se je izvršilo in zabeležilo preko 14000 poizvedb, izmed katerih smo jih nekaj omenili v
prilogi C.
Slika 21: ER diagram podatkovne baze1a-vreme.
52
Z ogrodjem smo nato optimizirali podatkovno bazo, iz katere smo odstranili prvotno postavljene
indekse, ki so definirani v prilogi D. Pognali smo 33 testov in izračunali povprečje časov izvajanja
teh testov s pripadajočo standardno negotovostjo (metodologija testiranja je razložena v
poglavju 8.1). Nato smo z ogrodjem postavili indekse definirane, ki se nahajajo v prilogi D.
Nazadnje smo to naredili še sprvotno postavljenimi indeksi. Te je postavil strokovnjak s petimi
leti izkušenj s podatkovno bazo MySQL.
Brez indeksov
Ogrodje indeksi
Prvotni indeksi
AD
0,51
0,35
0,41
Čas izvajanja
0,7493 s ± 2,4 ms
0,76368 s ± 0,30 ms
0,79432 s ± 0,26 ms
Izboljšava
89,8 % ± 0,4 % in 3,9 % ± 0,4 %
Tabela 4: Rezultati testiranja podatkovne baze1a-vreme.
Iz rezultatov razberemo, da smo z uporabo ogrodja izboljšali hitrost izvajanja poizvedb za okoli
89,8 % v primerjavi s poizvedbami, kjer ni postavljenih indeksov. 3,9 % pa je izboljšava s
prvotnim stanjem.
Poleg predlaganih indeksov nam je ogrodje podalo še naslednje predloge, ki pa v primeru
projekta 1a-vreme niso relevantni, ker se pri izpisu uporabljajo vsi stolpci v tabeli. Predloga sta:
»Selecting all columns with the * wildcard is not recommended, because the query might
have to retrieve too much data. (query: "SELECT * FROM forecastDataLarge WHERE
forecastCityID = 3")« in
»Selecting all columns with the * wildcard is not recommended, because the query might
have to retrieve too much data. (query: "SELECT * FROM skiingForecast WHERE
forecastCityID = 17")«.
53
54
9 SKLEPNE UGOTOVITVE IN IDEJE ZA NADALJNE DELO
Analiza optimalnega postavljanja indeksov in optimalnega pisanja poizvedb zahteva veliko
predznanja. Ker na trgu za podatkovno bazoMySQL ne obstaja orodje, ki bi to namesto nas
avtomatiziralo,smo v ta namen izdelali lastno ogrodje, ki optimalno postavi indekse in opozori
na potencialne izboljšave v sestavi poizvedbe. Ogrodje smo preizkusili na dveh podatkovnih
bazah.
Prva podatkovna baza je bila Sakila in je primer standardne MySQL podatkovne sheme, ki se
uporablja v knjigah, člankih in tečajih podatkovnih baz. Ima že vnaprej pripravljene poizvedbein
indekse.Ogrodje
za
skoraj 100 %
izboljša
hitrost izvajanja
poizvedb v primerjavi
sSakilapodatkovno bazo, ki nima postavljenih indeksov. V primerjavi spredhodno postavljenimi
indeksi pa je razlika v hitrosti približno 2,5 % v prid prvotnemu stanju. Poraba prostora je v prid
ogrodju, saj podatkovna baza v prvotnem stanju zaseda približno 53 % več prostora kot
podatkovna baza z indeksi, postavljenimi z ogrodjem.Ogrodje smo nato preizkusili še na
projektu 1a-vreme, ki se uporablja v produkciji, in sicer na spletnem naslovu http://www.1avreme.si.Iz rezultatov smo razbrali, da smo z uporabo ogrodja izboljšali hitrost izvajanja
poizvedb za okoli 89,8 % v primerjavi s poizvedbami, kjer ni postavljenih indeksov. Približno
3,9 % pa je izboljšava s stanjem, kot je bilo, ko so bili indeksi postavljeni predhodno. V obeh
primerih je ogrodje zelo dobro služilo svojem namenu. Indekse je postavilo ekvivalentno dobro
strokovnjaku z nekajletnimi izkušnjami dela s podatkovnimi bazami. Kot tako je zato ogrodje
primerno tako za začetnike kot izkušene uporabnike podatkovnih baz. Izvorno kodo
nameravamo preko spletne strani Github ponuditi širši skupnosti.
Čeprav ogrodje izpolnjuje zadane cilje, pa je v prihodnosti možnih kar nekaj izboljšav oz. odprav
pomanjkljivosti, ki smo jih odkrili že v času razvoja. Ena od pomanjkljivosti je ta, da ogrodje
zaenkrat deluje samo v primeru, če ima projekt, ki ga optimiziramo s povezavo samo na eno
podatkovno bazo. Torej bi bila potencialna izboljšava povezovanje na več podatkovnih baz. Ker
MySQL ni edina podatkovna baza, s katero se PHP lahko poveže, bi bila naslednja potencialna
izboljšava razširitev optimizacij na še druge podatkovne baze. Zadnja možna izboljšavapa je
seveda razširitev ogrodja za druge programske jezike, ki se povezujejo napodatkovno bazo
MySQL.
55
56
DODATEK A: NASTAVITVEOGRODJA
V tem razdelku podajamo možne nastavitve ogrodja, ki jih nastavimo v datoteki
»config.conf.php«.
1. MySQL nastavitve
Nastavitev MYSQL_HOSTNAME: IP-naslov ali ime gostitelja (angl.: hostname) MySQL
strežnika. Če optimiziramo projekt na strežniku, je to vrednost IP-naslova strežnika, kjer
se nahaja MySQL. V primeru razvoja na lokalnem računalniku je ta vrednost navadno kar
localhost ali "127.0.0.1".
Nastavitev MYSQL_USERNAME: Ime uporabnika, s katerim dostopamo do podatkovne
baze.
Nastavitev MYSQL_PASSWORD: Geslo za MySQL uporabnika, s katerim dostopamo do
podatkovne baze.
Nastavitev MYSQL_SCHEMA: Ime podatkovne baze, ki je uporabljena v projektu in čigar
poizvedbe želimo optimizirati.
2. Direktorijske nastavitve
NastavitevPROJECT_PATH: Pot do projekta. Lahko je v absolutni ali relativni obliki. Če se
direktorij FMQO nahaja v istem domenskem direktoriju kot projekt, je lahko ta vrednost
relativna: "../ime_projekta/".
Nastavitev PROJECT_URL: Spletni naslov, preko katerega dostopamo do našega projekta.
Ta vrednost je opcijska, potrebna le, če uporabimo spletnega pajka, ki smo ga predstavili
v poglavju 5.2.2.
Nastavitev SCAN_DIRECTORY_EXTENSIONS: Polje, ki definira končnice PHP datotek.
Ogrodje jih bo upoštevalo pri pregledu datotečne strukture.
3. Nastavitve spletnega pajka
Nastavitev CRAWLER_DEPTH: Koliko nivojev spletnih povezav bo pajek obiskal.
4. Testiranje
Nastavitev BENCHMARK_NO_OF_INSERTS: Število vnosov vrstic v tabele, uporabljene za
testiranje. Večja kot je številka, boljša bo natančnost rezultatov testiranja (v kolikor
imajo tabele malo zapisov).
Nastavitev BENCHMARK_REPEATS: Število ponovitev testa. Za dobro oceno primerjave
testiranja naj bo številka čim večja.
57
5. Generator podatkov
Tukaj se nahajajo nastavitve, ki jih uporablja generator podatkov (razred DataGenerator).
Nastavimo lahko dolžine podatkovnih tipov. Vrednosti naj bodo manjše, kot je maksimalna
vrednost podatkovnega tipa, saj se bodo tako testi izvajali hitreje. Ogrodje samodejno prilagaja
dolžino teh vrednosti, če je vrstica v podatkovni bazi definirana kot unikatna in vkolikor ima
podatkovni tip manjšo vrednost, kolikor je zapisov v tabeli.
58
DODATEK B: IZPIS UKAZNIH LUPIN
V tem razdelku prikažemo različne izpise ogrodja, ki jih dobimo z zagonom tega preko ukazne
vrstice z različnimi opcijami.
>php run.php
Framework for MySQL optimization
Usage: php run.php [switches]
--info
Shows all possible commands.
--compatibilityTest
Runs compatibility test.
--scanCode
Scans the code and prepares database structure.
--crawler
Scans website for links.
--analyzeQueries
Analyzes queries and returns prepositions for optimization.
--cleanup
Cleans database structure and code inserts.
--setupConfig <config>Sets config.conf.php file.
Example: --setupConfig -hostname 1 -username 2 -password 3 -schema 4 -projectPath ../ -projectUrl
http://localhost/
Total time: 0 seconds, Memory: 1.00 MB
Ukazna lupina 1: Zagon ogrodja.
59
> php run.php --compatibilityTest
Framework for MySQL optimization
PHP Environment Compatibility Test (CLI)
---------------------------------------PHP 5.1 or newer...............Yes 5.3.6
MySQL.....................................Yes
MySQLi....................................Yes
cURL.........................................Yes
---------------------------------------Your environment meets the minimum requirements.
Total time: 0 seconds, Memory: 1.00 MB
Ukazna lupina 2: Zagon ogrodja z opcijo compatibilityTest.
> php run.php --scanCode
Framework for MySQL optimization
The code was scanned and update.
Total time: 0 seconds, Memory: 1.25 MB
Ukazna lupina 3: Zagon ogrodja z opcijo scanCode.
> php run.php --crawler
Framework for MySQL optimization
URL: http://localhost/diplomska/myProject/file1.php
URL: http://localhost/diplomska/myProject/file2.php
URL: http://localhost/diplomska/myProject/subfolder/file1.php
URL: http://localhost/diplomska/myProject/subfolder/file2.php
URL: http://localhost/diplomska/myProject/index.html
Site was successfully scanned by crawler.
Total time: 0 seconds, Memory: 1.25 MB
Ukazna lupina 4: Zagon ogrodja z opcijo crawler.
60
> php run.php --analyzeQueries
Framework for MySQL optimization
Do you want to run benchmark? Type 'yes' if you do: yes
Do you want the system to set indexes automatically? Type 'yes' if you do: no
---------------------------------------Suggested indexes:
ALTER TABLE language ADD INDEX AISPM_index1 (section);
ALTER TABLE cities ADD INDEX AISPM_index3 (id,name);
---------------------------------------Suggested optimizations on queries:
- ORDER BY is using both descending and ascending ordering. Index can't be used, which can be a
performance problem. (query: SELECT section, sKey, sValue FROM language ORDER BY section DESC,
sKey ASC)
- Selecting all columns with the * wildcard is not recommended, because the query might have to retrieve
too much data. (query: SELECT * FROM `forecastDataShort` WHERE `iconTodayMorning` = 'overcast')
---------------------------------------Number of repeats: 33
Mean time without index: 1.976 s +/- 0.021 s
Mean time with index: 1.918 s +/- 0.016 s
Improvement: 2.9 % +/- 1.9 %
-------------------------------------------------------------------------------Total time: 65 seconds, Memory: 14.00 MB
Ukazna lupina 5: Izvajanje ukazaanalyzeQueries.
61
> php run.php --analyzeQueries
Framework for MySQL optimization
Do you want to run benchmark? Type 'yes' if you do: yes
Do you want the system to set indexes automatically? Type 'yes' if you do: no
---------------------------------------Suggested indexes:
Indexes are already set correctly. Congratulations :)!
---------------------------------------Total time: 4 seconds, Memory: 3.00 MB
Ukazna lupina 6: Izvajanje ukaza analyzeQueries na projektu, kjer ni predlaganih izboljšav.
> php run.php --cleanup
Framework for MySQL optimization
The code was cleaned.
Total time: 0 seconds, Memory: 1.25 MB
Ukazna lupina 7: Zagon ogrodja z opcijo cleanup.
> php run.php --setupConfig -hostname 1 -username 2 -password 3 -schema 4 -projectPath ../ -projectUrl
http://localhost/
Framework for MySQL optimization
Config file was updated.
Total time: 0 seconds, Memory: 1.25 MB
Ukazna lupina 8: Zagon ogrodja z opcijo setupConfig.
62
DODATEK C: SQL POIZVEDBE
1. SQL poizvedbe podatkovne baze Sakila
SELECT film.film_id AS FID, film.title AS title, film.description AS
description, category.name AS category, film.rental_rate AS price,
film.length AS length, film.rating AS rating,
GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR
', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id =
film_category.category_id LEFT JOIN film ON film_category.film_id =
film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id
SELECT film.film_id AS FID, film.title AS title, film.description AS
description, category.name AS category, film.rental_rate AS price,
film.length AS length, film.rating AS rating,
GROUP_CONCAT(CONCAT(CONCAT(UCASE(SUBSTR(actor.first_name,1,1)),
LCASE(SUBSTR(actor.first_name,2,LENGTH(actor.first_name))),_utf8'
',CONCAT(UCASE(SUBSTR(actor.last_name,1,1)),
LCASE(SUBSTR(actor.last_name,2,LENGTH(actor.last_name))))))
SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id =
film_category.category_id LEFT JOIN film ON film_category.film_id =
film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
GROUP BY film.film_id
SELECT
CONCAT(c.city, _utf8',', cy.country) AS store
, CONCAT(m.first_name, _utf8' ', m.last_name) AS manager
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN store AS s ON i.store_id = s.store_id
INNER JOIN address AS a ON s.address_id = a.address_id
INNER JOIN city AS c ON a.city_id = c.city_id
INNER JOIN country AS cy ON c.country_id = cy.country_id
INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id
GROUP BY s.store_id
ORDER BY cy.country, c.city
63
SELECT
a.actor_id,
a.first_name,
a.last_name,
GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
(SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ',
')
FROM sakila.film f
INNER JOIN sakila.film_category fc
ON f.film_id = fc.film_id
INNER JOIN sakila.film_actor fa
ON f.film_id = fa.film_id
WHERE fc.category_id = c.category_id
AND fa.actor_id = a.actor_id
)
)
ORDER BY c.name SEPARATOR '; ')
AS film_info
FROM sakila.actor a
LEFT JOIN sakila.film_actor fa
ON a.actor_id = fa.actor_id
LEFT JOIN sakila.film_category fc
ON fa.film_id = fc.film_id
LEFT JOIN sakila.category c
ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name
SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name)
AS name, a.address AS address, a.postal_code AS `zip code`,
a.phone AS phone, city.city AS city, country.country AS country,
IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN
city ON a.city_id = city.city_id
JOIN country ON city.country_id = country.country_id
SELECT
c.name AS category
, SUM(p.amount) AS total_sales
FROM payment AS p
INNER JOIN rental AS r ON p.rental_id = r.rental_id
INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id
INNER JOIN film AS f ON i.film_id = f.film_id
INNER JOIN film_category AS fc ON f.film_id = fc.film_id
INNER JOIN category AS c ON fc.category_id = c.category_id
GROUP BY c.name
ORDER BY total_sales DESC
64
2. Primeri SQL poizvedb podatkovne baze 1a-vreme
SELECT observationData.temperature, cities.name, cities.id,
observationData.icon, forecastDataShort.temperatureTodayAfternoon,
forecastDataShort.temperatureTodayMorning FROM observationData INNER JOIN
cities ON observationData.observationCityID = cities.observationCityID
INNER JOIN forecastDataShort ON cities.forecastCityID =
forecastDataShort.forecastCityID WHERE cities.name LIKE 'portorož%' ORDER
BY cities.name DESC LIMIT 0, 8
SELECT cities.id, observationData.temperature, cities.forecastCityID,
observationData.pressure, observationData.humidity, observationData.rain,
observationData.dateIssued, observationData.windSpeed,
observationData.windDirection, observationData.icon, observationData.text,
cities.name FROM observationData INNER JOIN cities ON
observationData.observationCityID = cities.observationCityID WHERE
cities.id = 37
UPDATE forecastDataLarge SET d1t = 'Danes', d1lt = -4, d1ht = -3, d1i =
'overcast_lightSN', d2t = 'Jutri', d2lt = -5, d2ht = -2, d2i =
'overcast_lightSN', d3t = 'Torek', d3lt = -4, d3ht = 1, d3i =
'prevCloudy_lightSN', d4t = 'Sreda', d4lt = -5, d4ht = -2, d4i =
'overcast_lightSN', d5t = 'Četrtek', d5lt = -4, d5ht = -2, d5i =
'overcast_modSN' WHERE forecastCityID = 22
SELECT id, dateIssued FROM observationDataHistory WHERE observationCityID =
43 ORDER BY id DESC LIMIT 0, 1
65
66
DODATEK D: REZULTATI TESTIRANJ
1. Podatkovna baza Sakila
Tabela
Prvotni indeksi
Predlagani indeksi ogrodja
actor
(actor_id); (last_name)
(actor_id)
address
(address_id); (city_id)
(address_id,city_id); (city_id)
category
(category_id)
(category_id)
city
(city_id); (country_id)
(city_id,country_id);(country_id); (city, city_id,
country_id)
country
(country_id)
(country_id); (country, country_id)
customer
(customer_id); (store_id);
/
(address_id); (last_name)
film
(film_id); (title);
(film_id)
(language_id);
(original_language_id)
film_actor
(actor_id, film_id);
(actor_id); (film_id, actor_id)
(film_id)
film_category
(film_id, category_id);
(film_id, category_id); (category_id, film_id)
(category_id)
film_text
(film_id); (title,
description)
inventory
(inventory_id); (film_id);
(inventory_id, film_id); (inventory_id, store_id)
(store_id, film_id)
language
(language_id)
/
payment
(payment_id); (staff_id);
(amount, rental_id); (rental_id)
(customer_id);
(rental_id)
rental
(rental_id); (rental_date,
(rental_id, inventory_id);
inventory_id,
(inventory_id)
customer_id);
(inventory_id);
(customer_id); (staff_id)
staff
(staff_id); (store_id);
(staff_id); (address_id);
(address_id)
store
(store_id);
(store_id,address_id,manager_staff_id);(address_id);
(manager_staff_id);
(manager_staff_id)
(address_id)
Tabela 5: Indeksi, postavljeni na podatkovni baziSakila.
67
Predlagani indeksi [ms]
Prvotni indeksi [ms]
1,376591
1,344317
1,377894
1,344799
1,379005
1,345092
1,380190
1,345359
1,380679
1,345973
1,382905
1,346942
1,383053
1,349032
1,383097
1,349298
1,384258
1,349599
1,384380
1,350148
1,385689
1,350956
1,386336
1,351078
1,386667
1,351446
1,387462
1,351453
1,387684
1,352699
1,387991
1,353527
1,388050
1,353574
1,388467
1,354766
1,388539
1,354849
1,388591
1,355607
1,389749
1,356701
1,389942
1,356937
1,390001
1,358312
1,392227
1,358886
1,392650
1,359662
1,393390
1,359751
1,393520
1,359902
1,393654
1,360293
1,396079
1,360914
1,396380
1,362436
1,396773
1,363531
1,398712
1,368839
1,399968
1,392605
Tabela 6: Časi trajanj primerjalnih testov na podatkovni baziSakila.
68
2. Podatkovna baza 1a-vreme
Brez indeksov [s]
Predlagani indeksi [ms]
Prvotni indeksi [ms]
0,745436
75,651
78,912
0,745450
75,676
78,957
0,745533
75,756
78,967
0,745548
75,775
78,969
0,745564
75,778
78,973
0,746067
75,871
78,985
0,746178
75,991
78,990
0,746208
75,996
79,080
0,746228
76,030
79,098
0,746297
76,059
79,178
0,746507
76,107
79,254
0,747690
76,175
79,281
0,747840
76,192
79,303
0,748077
76,276
79,318
0,748443
76,286
79,342
0,748571
76,302
79,377
0,749210
76,304
79,400
0,749421
76,318
79,412
0,749628
76,406
79,421
0,749824
76,432
79,496
0,750213
76,469
79,500
0,750876
76,517
79,554
0,751217
76,532
79,558
0,751642
76,586
79,578
0,751666
76,741
79,744
0,751693
76,859
79,834
0,753241
76,903
79,868
0,754085
76,984
79,993
0,755061
76,997
80,047
0,755297
77,040
80,110
0,755424
77,047
80,112
0,757751
77,290
80,185
0,759634
77,406
80,262
Tabela 7: Časi trajanj primerjalnih testov na podatkovni bazi1a-vreme.
69
Tabela
Prvotni indeksi
Predlagani indeksi ogrodja
cities
(id); (name)
(observationCityID,forecastCityID);
(id,name,observationCityID,forecastCityID);
(name,observationCityID,forecastCityID);
(id,observationCityID,forecastCityID)
forecastCities
(id)
/
forecastDataLarge
(forecastCityID)
(forecastCityID)
forecastDataShort
(forecastCityID)
(forecastCityID)
forecastTenDays
(id)
/
generalForecast
(id); (datetime)
(id); (datetime)
observationCities
(id)
/
observationData
(observationCityID)
(observationCityID)
observationDataHistory
(id);
/
(observationCityID)
radar
(id); (datetime)
/
skiingCities
(id)
(id);
skiingForecast
(forecastCityID)
/
skiingObservationalData
(id)
(id,observationCityID);(observationCityID,id);
Tabela 8: Indeksi, postavljeni na podatkovni bazi1a-vreme.
70
10 LITERATURA
[1]
Tomaž Mohorič.Podatkovne baze. Ljubljana, str. 11–20 in 125–151, 2002.
[2]
Thomas M. Connolly, Carolyn E. Begg.Database Systems A Practical Approach to Design,
Implementation and Management, Pearson Education, str. 24–26 in 89–103, 2005.
[3]
Paul DuBois.MySQL (4th Edition). Pearson Education, 2008.
[4]
Baron Schwartz, Peter Zaitsev, Vadim Tkachenko.High Performance MySQL Optimization,
Backups, and Replication (3rd Edition). O'Rielly Media, str. 719–734, 2012.
[5]
Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, Clifford Stein. Introduction to
Algorithms (3th Edition). Massachusetts Institute of Technology, str. 286–734, 2009.
[6]
Luke Welling, Laura Thomson.PHP and MYSQL Web Development (4th Edition). Pearson
Education, str. 2–10, 2009.
[7]
Jeffrey E. F. Friedl. Mastering Regular Expressions (3rd Edition). O'Rielly Media, str. 4–33,
2006.
[8]
Sebastian Bergmann, Stefan Priebsch.Real-World Solutions for Developing High-Quality
PHP Frameworks and Applications. Wiley Publishing, str. 15–71, 2011.
[9]
Gregor Geršak.Merilna negotovost za začetnike. Ljubljana, str. 8–15, 2006.
[10]
Jorge Luis Romeu.Anderson-Darling: A goodness of Fit Test for Small Samples Assumptions.
START, letnik X, št. 5,str. 1–3, 2003.
Spletni viri (dostopnost vseh preverjena marca 2013)
[11]
Sistem za upravljanje podatkovne baze. Dostopno na:
http://colos1.fri.unilj.si/ERI/RACUNALNISTVO/PODATKOVNE_BAZE/opredelitev_pojma_supb.html
[12]
Sistem za upravljanje s podatkovno bazo. Dostopno na:
http://sl.wikipedia.org/wiki/Sistem_za_upravljanje_s_podatkovno_bazo
[13]
Oracle acquired Sun. Dostopno na:
http://www.oracle.com/us/sun/index.htm
[14]
Podatkovni model. Dostopno na:
http://colos1.fri.unilj.si/ERI/RACUNALNISTVO/PODATKOVNE_BAZE/podatkovni_model.html
71
[15]
SQL. Dostopno na:
http://en.wikipedia.org/wiki/SQL
[16]
What Standards MySQL Follows. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/standards.html
[17]
Benchmarks. Dostopno na:
http://www.mysql.com/why-mysql/benchmarks/
[18]
Overview of SQL Query Analyzer. Dostopno na:
http://msdn.microsoft.com/en-us/library/aa216945(v=sql.80).aspx
[19]
EXPLAIN Syntax. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/explain.html
[20]
Database index. Dostopno na:
http://en.wikipedia.org/wiki/Database_index
[21]
How MySQL Uses Indexes. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
[22]
SELECT Syntax. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/select.html
[23]
Expression Syntax. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/expressions.html
[24]
UNION Syntax. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/union.html
[25]
JOIN Syntax. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/join.html
[26]
Index Hint Syntax. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html
[27]
ORDER BY Optimization. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
[28]
GROUP BY Optimization. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html
[29]
PHP. Dostopno na:
http://en.wikipedia.org/wiki/PHP
72
[30]
What can PHP do. Dostopno na:
http://www.php.net/manual/en/intro-whatcando.php
[31]
PHP Usage Trends. Dostopno na:
http://trends.builtwith.com/framework/PHP
[32]
PHP. Dostopno na:
http://www.php.net/
[33]
Web crawler. Dostopno na:
http://en.wikipedia.org/wiki/Web_crawler
[34]
The RecursiveIteratorIterator class. Dostopno na:
http://php.net/manual/en/class.recursiveiteratoriterator.php
[35]
Function token_get_all. Dostopno na:
http://www.php.net/manual/en/function.token-get-all.php
[36]
MySQL Functions. Dostopno na:
http://php.net/manual/en/ref.mysql.php
[37]
The MySQLi Extension Function Summary. Dostopno na:
http://php.net/manual/en/mysqli.summary.php
[38]
SQL::Tokenizer. Dostopno na:
http://search.cpan.org/~izut/SQL-Tokenizer-0.24/lib/SQL/Tokenizer.pm
[39]
Data Type Overview. Dostopno na:
http://dev.mysql.com/doc/refman/5.0/en/data-type-overview.html
[40]
Dependency injection. Dostopno na:
http://en.wikipedia.org/wiki/Dependency_injection
[41]
Mock Objects. Dostopno na:
http://www.phpunit.de/manual/3.0/en/mock-objects.html
[42]
Client URL Library. Dostopno na:
http://php.net/manual/en/book.curl.php
[43]
Sakila Sample Database. Dostopno na:
http://dev.mysql.com/doc/sakila/en/index.html
[44]
Anderson–Darling test. Dostopno na:
http://en.wikipedia.org/wiki/Anderson%E2%80%93Darling_test
[45]
Normal distribution. Dostopno na:
http://en.wikipedia.org/wiki/Normal_distribution2
73
IZJAVA
Izjavljam, da sem diplomsko delo izdelal samostojno pod vodstvom mentorja doc . dr. Boš tjana
Murovca, univ. dipl. inž . el. Izkazano pomoč drugih sodelavcev sem v celoti navedel v zahvali.
Ljubljana, 22.05.2013
Matjaž Mrgole
© Copyright 2024