Duombazių optimizavimas greitaveikai

Įvadas

Galima teigti, kad duomenų bazės yra visur. Praktiškai kiekvienas informacinės sistemos sprendimas turi turėti kažkokią tai duomenų talpyklą, kuri saugo su sistema susijusius duomenis: socialinis tinklo puslapis kažkur turi saugoti vartotojų duomenis, meteorologinės informacijos sistema turi kažkur saugoti duomenis apie temperatūras/spaudimą/kritulius, kokybės valdymo sistema turi saugoti informaciją apie detalių auditus ir t.t. Šiuo metu duomenų saugojimo rolę atlieka duomenų bazės ir populiariausios iš jų yra SQL (Structured Query Language) interpretuojančios reliacinės duomenų bazių valdymo sistemos (angl. Relational DataBase Management System - RDBMS). Tačiau, tas faktas, kad duombazės yra visur nereiškia, kad žmonės dirbantys su duombazėmis yra pilnai įvaldę šią technologiją. Daugelis duombazių/sistemų kenčia nuo blogo schemos dizaino, blogai suformuotų užklausų, blogo kodo, kuris sąveikauja su duombaze ir panašių trūkumų, kurie tiesiogiai įtakoją sistemos prižiūrimumą (angl. maintainability), suprantamumą, tvirtumą ir galiausiai NAŠUMĄ. Našumo stoka arba jo nepastovumas, yra tiesiogiai jaučiamas sistemos vartojo ir dėl to nenašiai veikiančios sistemos yra paliekamos/pamirštamos vartotojų (be abejo, jeigu egzistuoja alternatyvos). Šiame straipsnyje bus aptarti keli populiariausi būdai, kai paspartinti duomenų bazės greitaveiką bei bus pateikta patarimų į ką atkreipti dėmesį kuriant duombazės schemą, kad našumo problemos neiškiltų vėliau. Straipsnis liečia tiktai SQL tipo reliacines duombazes ir kai kurie patobulinimai veikia tik ant specifinio platintojo sprendimų.

Trūkumų nustatymas

Dažniausiai mes žinome, koks sistemos panaudos atvejas veikia lėtai ir pagal tai yra įmanoma atsekti lėtai veikiančią užklausą (arba lėtai veikiančia veiksmų seka, kurioje dalyvauja užklausa). Jeigu yra norima atlikti bendra duombazės optimizavimą greitaveikai, reikia turėti duombazės veikimo statistiką, pagal kurią įmanoma matyti, kurios užklausos trunka ilgiausiai (arba kurių yra daugiausia). Tai galima atlikti su srauto imitavimo ir profiliavimo įrankiais [8]. Srautą galima imituoti tiesiogiai:

netiesiogiai (generuojant užklausas į tinklapį, kuris sąveikauja su duombaze):

Bus atveju, kai dėl prasto greitaveikos yra atsakinga duombazę valdanti geležis: lėtas kietasis diskas, mažas darbinės atminties kiekis, prastas procesorius ir pan. Tokius atvejus galima patikslinti su bendriniais sistemos testavimo įrankiais:

Egzistuoja daug daugiau srauto generavimo bei benchmark'inimo įrankių kitoms duombazių sistemoms ir skaitytojui yra patariama susirasti jam/jai patogiausią. Tarkime, kad mūsų duombazė yra apkrauta ir dabar mums belieka nustatyti, kur slypi problemos. Tai galima atlikti su profiliavimo įrankiais:

Šie ir panašūs įrankiai pateikia detalų išrašą apie užklausų vykdymo laikus, iš kurio galima lengvai atpažinti kurios užklausos lėtina sistemos darbą. Jeigu atrandame, kad kažkokia tai užklausa veikia lėtai, tačiau nevisai esame įsitikinę kas su ja blogai, galima patyrinėti jos vykdymo planą:

Šios komandos pateikia detalų planą kaip yra vykdoma specifinė užklausą. Iš šių planų galima matyti kokia specifinė operacija(os) užklausoje užima daugiausiai laiko, ar atliekamas skanavimas ar sekimas (indeksavimas), kokie indeksai yra naudojami ir panaši vertinga informacija. Likusios straipsnio dalis darys prielaidą, kad sekcijoje sprendžiama problema jau yra atpažinta.

Indeksavimas

Duomenų bazė susideda iš lentelių, kur kiekviena iš jų gali turėti daug įrašų. Dažniausiai duomenų bazė neužtikrina, kad įrašai bus fiziškai talpinami ta seka kuria jie yra atvaizduojami, dėl to specifinių įrašų paieška, be papildomų priemonių/informacijos, yra O(N) sudėtingumo, kas stipriai atsiliepia greitaveikai, jeigu lentelėje yra labai didelis įrašų kiekis. Įrašų paieška yra spartinama indeksais, kurių veikimą galima apibūdinti telefonų knygos analogija: žmonės (įrašai) yra išsidėstę be tvarkos, tačiau telefonų knygoje (indekso struktūroje), jie yra išrikiuoti pagal pavardę (pirmą lauką), vardą (antrą lauką) ir turi telefono numerį ir adresą (fizinis lokatorius faile). Indeksai yra dviejų tipų: klasteriuoti (angl. clustered) ir neklasteriuoti (non-clustered) ir implementacijos lygyje dažniausiai būna arba dvinariai medžiai O(log n) arba hash lentelės O(1). Nuosekli paieška be indekso yra vadinama skanavimu (Scanning), su indeksu - sekimu (Seeking).

Klasteriuotas Indeksas

Klasteriuotas indeksas truputi sugriauna aukščiau paminėta analogiją, nes jis fiziškai perorganizuoja duomenis lentelėje taip, kad jų seka atitiktų indekso loginę seką (dėl šios priežasties lentelei gali būti priskirtas tiktai vienas klasteriuotas indeksas). Taip suindeksuotoje lentelėje galima labai sparčiai vykdyti užklausas kurios duomenis prieina iš eilės, arba gražina įrašų intervalą, nes disko skaitymas tokioje situacijoje irgi yra nuoseklus (nereikia šokinėti per atskirus blokus). Tarkime, kad turime lentą, kurioje saugome duomenis apie asmenis:


CREATE TABLE Person 
  ( 
     Id          INT NOT NULL 
     , FirstName VARCHAR NOT NULL 
     , LastName  VARCHAR NOT NULL 
     , OtherInfo VARCHAR NULL 
  )
Užklausa sukurianti asmenų lentelę

Ir žinome, kad programa/tinklapis, visuomet pateiks šiuos duomenis surikiuotus pagal pavardę ir atskiruose puslapiuose. Tokiu atveju klasteriuota indeksą yra geriau padaryti ne ant Id stulpelio, bet ant pavardės: 


CREATE CLUSTERED INDEX IX_Person_LastName 
  ON Person (LastName)
Klasteriuoto indekso užklausa

Pagal nutylėjimą MSSQL duombazėje klasteriuotas indeksas yra priskiriamas pradiniam raktui (primary key - PK). Oracle duombazėje klasteriuotas indeksas yra vadinamas kitaip: Index-Organized Table (IOT). Neklasteriuotas indeksas veikia taip pat, tiktai neliečia fizinio įrašų išdėstymo, dėl to neklasteriuotų indeksų lentelė gali turėti daug [9, 10].

Persidengiantis indeksas

Tarkime, kad sistemoje/tinklapyje leidžiame vartotojui ieškoti asmenis pagal jų vardą ir pavardę: 


SELECT Id 
       , FirstName 
       , LastName 
       , OtherInfo 
FROM   Person 
WHERE  FirstName = 'Vardenis' 
       AND LastName = 'Pavardenis'
Užklausa išrenkanti asmenis pagal vardą ir pavardę

Be indekso būtų vykdomas skanavimas, kas yra negerai. Jeigu indeksas būtų tiktai ant vieno iš laukų (tarkime - LastName), būtų geriau, tačiau duombazės varikliukas vistiek turėtų atlikti skanavimą tarp visų 'Pavardenių'. Pilnai padengti užklausą reiktų sukurti persidengiantį indeksą: 


CREATE NONCLUSTERED INDEX IX_Person_LastName_FirstName 
  ON Person ( LastName, FirstName )
Užklausa sukurianti persidengianti indeksą

Duombazės varikliukas, naudodamasis tokiu indeksu, iš pradžių, atseka 'Pavardenis' [indekso] įrašą ir ten jis randa ne nuorodas kur slypi visi Pavardeniai, o dar vieną Pavardenių indeksą, pagal kūrį galima labai greitai atsekti 'Vardenį'.

Persidengiančiuose indeksuose yra svarbus laukų eiliškumas: IX_LastName_FirstName ≠ IX_FirstName_LastName. Taipogi jie turi tokią savybę: IX_LastName_FirstName gali būti panaudotas vietoj IX_LastName, nes tiek vieno tiek kito indekso įrašai turi informaciją apie vieną specifinę pavardę. Greitaveika su persidengiančiais indeksai galia prarasti jeigu, lentelėje yra mažai vardų po viena pavarde: apeiti indekso struktūra kurioje slypi mažai įrašų trunka daugiau laiko negu juos paprasčiausiai nuskenuoti. Netgi kai kurie užklausų optimizatoriai (programinis SQL serverio komponentas), pastebėję mažą įrašų kiekį lentelėje, suformuoja užklausos vykdymo planą, kuriame nenurodo indekso, tam kad duombazės varikliukas ją greičiau įvykdytų.

Dalinis indeksas

Tarkime, kad turime labai dažną panaudos atvejį(ų), kuriame(uose) yra dirbama tiktai su aktyviais vartotojais(UserName) (WHERE Avtive = TRUE). Tokiu atveju kurti indeksą: 


CREATE NONCLUSTERED INDEX IX_User_Username 
  ON User ( Username )
Paprastas vieno lauko indeksas

ant visų vartotojų nėra pats efektyviausias sprendimas. Yra įmanoma sukonstruoti indeksą tiktai įrašams kurie tenkina tam tikrą sąlygą: 


CREATE NONCLUSTERED INDEX IX_User_ActiveUsername 
  ON USER ( Username ) 
  WHERE (Active=(TRUE))
Užklausa sukurianti dalinį indeksą

Šis indeksas yra mažesnis ir jis nėra modifikuojamas kai yra pakeičiamas arba įterpiamas neaktyvus vartotojas. [17]

Niuansai

Indeksavimas, kaip ir daugelis optimizavimo būdų iš visų sričių, turi savo trūkumų. Kai į lentelę yra įterpiamas naujas (INSERT) arba modifikuojamas (UPDATE) įrašas, visi indeksai irgi turi būti atnaujinamas. Jeigu indeksų yra daug, įterpimo operacijos vykdymo laikas žymiai išauga. Dėl to prieš kuriant indeksus lentelėms, kurioms yra vykdoma daug rašymo operacijų, reikia nuspręsti (arba eksperimentiniu būtu patikrinti) ar kažkokios 'SELECT' užklausos paspartinimas, nepalėtins kitų programos dalių.

Kartais į lentelę reikia įkelti daug duomenų vienu metų: duomenų iš senesnės sistemos importavimas, iš produkcijos duombazės lentelės perkėlimas į darbinės lentelę ir pan. Prieš darant didelio mąsto duomenų įkėlimą yra patartina išjungti lentelės indeksus (nevisus!) ir juos įjungti, kai įkėlimas baigtas [11]. Tačiau, vėlgi, reikia būti atsargiems ir įsitikinti, kad sistemos vartotojai (jeigu tokių yra) nebus paveikti tuo metu kai vyksta įterpimas, arba dar blogiau: netyčia neišjungti reikšmės unikalumą užtikrinančio indekso, dėl kurio nebūvimo būtų galima sugadinti lentelės būsena. Indekso išjungimas: 


ALTER INDEX IX_MyTableIndex ON [MyTable] DISABLE
Indekso išjungimo užklausa

ir įjungimas: 


ALTER INDEX IX_MyTableIndex ON [MyTable] REBUILD PARTITION = ALL
Indekso įjungimo užklausa

Indeksus galima ir visai išmesti su DROP ir sukurti per nauja su CREATE komandomis, tačiau taip yra prarandama indeksu statistika, kuri yra atstatoma tiktai gyvai naudojantis sistema [12].

Užklausų optimizavimas

Koreliuotos užklausos

Koreliuota užklausa yra sunerta (nested) užklausą kuri, gali būti panaši į štai tokią: 


SELECT prs.FirstName 
	   , prs.LastName 
	   , (SELECT phn.PhoneNumber 
		  FROM   PhoneNumbers phn 
		  WHERE  phn.PersonId = prs.Id 
				 AND phn.primary = TRUE) AS PrimaryPhoneNumber 
FROM   Person prs
Koreliuotos užklausos pavyzdys

Ši užklausa gražina asmenų vardus, pavardes ir jų pagrindinį telefono numerį. Problema yra tame, kad vidinė užklausa, kuri kreipiasi į PhoneNumber lentelę yra vykdoma kiekvienam Person lentelės įrašui. Jeigu yra labai didelis kiekis asmenų, tarkime: 5 milijonai, vidinė užklausa irgi bus įvykdoma 5 milijonus kartų. Nesvarbu kad vidinė užklausa yra jau užkešuota ir/arba turi atitinkamus indeksus, papildomas laikas reikiamos jos inicijavimui ir rezultatų suformavimui susidės į didelius našumo nuostolius. Tokios užklausas dažniausiai rašo nepatyrę programuotojai, kurie vadovaujasi struktūrine mąstysena [8, 11]. Dirbant su duombaze, reikia į duomenis žiūrėti kaip į vieną esybę/duomenų setą, o ne į individualių įrašų rinkinį: operacijos turi būti taikomos vieną kartą duomenų setui, o ne daug kartų kiekvienam įrašui. Aukščiau paminėtą užklausą (ir didžiąją daugumą panašių koreliuotų užklausų) galima perrašyti pasinaudojant JOIN'u: 


SELECT prs.FirstName 
	   , prs.LastName 
	   , phn.PhoneNumber 
FROM   Person prs 
	   LEFT JOIN PhoneNumbers phn 
			  ON phn.PersonId = prs.Id 
				 AND phn.primary = TRUE
Koreliuota užklausa perrašyta su JOIN

Select *

Dauguma programuotojų yra linkę netikslinti kokių laukų jiems reikia iš lentelės: 


SELECT * 
FROM   Person prs 
WHERE  prs.LastName LIKE 'N%'
Užklausa išrenkanti visus lentelės laukus

Tarkime, kad ši užklausa yra naudojama atvaizduoti asmenis sąraše, kur yra matomas vardas, pavardė ir gimimo data. Jeigu programos raidos pradžioje Person lentelėje buvo tiktai minėti 3 stulpeliai, tai 'Select *' būtų ganėtinai intuityvus sprendimas. Greitaveikos problemos prasidėtų, jeigu kas nors sumanytų pridėti papildomų stulpelių tarp kurių būtų: asmens nuotrauka (IMAGE), aprašas (TEXT) arba panašus vidutiniškai didelės apimties laukas. Tarkime kad vienas įrašas iš vardo, pavardės ir datos nesveria daugiau negu 200 baitų: parsisiuntimas susidedantis iš 200.000 tokių įrašų, jau būtų apie 40 megabaitų. Jeigu kas nors pridėtų profilio nuotrauka, kurios dydis vidutiniškai yra 24 kilobaitai, užklausos rezultato apimti jau būtų apie 4.8 gigabaitų. Reikiamų stulpelių tikslinimas: 


SELECT FirstName 
       , LastName 
       , BirthDate 
FROM   Person prs 
WHERE  prs.LastName LIKE 'N%'
Užklausa kuri pasiima tiktai reikiamą informaciją

Be akivaizdaus užklausos paspartinimo, padaro programą tvirtesnę iš dizaino pusės: iš pačios užklausos matosi jos tikslas ir užklausos rezultatas yra stipraus tipo (strong-typed). Tai sumažina šansą, kad ateityje programoje bus padarytas potencialiai žalingas pakeitimas [9, 11, 13].

Union ir OR

Jeigu reikia išrinkti įrašus, kurie tenkina vieną arba kitą sąlygą dažniausiai matytumėme, kad buvo pritaikytas toks sprendimas: 


SELECT FirstName 
       , LastName 
       , OtherInfo 
FROM   Person 
WHERE  LastName = 'Pavardenis' 
        OR LastName = 'Petraitis'
Užklausa, kuri išrenka asmenis kurių vardas yra Pavardenis arba asmenis kurių pavardė yra Petraitis

UNION komandos panaudojimas gali paspartinti užklausą [14, 15]: 


SELECT FirstName 
       , LastName 
       , OtherInfo 
FROM   Person 
WHERE  LastName = 'Pavardenis' 
UNION 
SELECT FirstName 
       , LastName 
       , OtherInfo 
FROM   Person 
WHERE  LastName = 'Petraitis'
Užklausos su OR pavertimas į užklausa su UNION

Taip yra dėl to, nes kartais, priklausomai nuo laukų kombinacijos ar reikšmių, užklausų optimizatorius gali nerasti tinkamo plano, kuris išnaudotų indeksus ir paprasčiausiai lieptų varikliukui atlikti skanavimą. Atskirdami WHERE sąlyga rankiniu būdų, į dvi (arba daugiau) paprastesnes dalis, kartais pagerėja šansai kad optimizatorius, bus užvestas ant tinkamo kelio ir išnaudos indeksą ant vieno (arba daugiau) sąlygos perskyrų. [16]

Normalizavimas

Duomenų struktūrų normalizavimas yra 30 metų senumo sąvoka, kuri padeda racionaliai identifikuoti ir išskaidyti atskirus duomenų vienetus. SQL ir kitos reliacinės duombazės yra paremtos šia sąvoka ir dėl to jos geriausiai dirba su normalizuotais duomenimis [18]. Nenormalizuotos struktūros turi paprastesnę schemą ir dėl to gali atrodyti intuityvesnės ir geresnio dizaino negu normalizuotos [17]. Tarkime, kad samdinių lentelėje saugome samdinio kontaktinę informaciją: 


CREATE TABLE Employee 
  ( 
     Id          INT IDENTITY(1, 1) 
     , FirstName VARCHAR 
     , LastName  VARCHAR 
     , WorkPhone VARCHAR 
     , HomePhone VARCHAR 
     , CellPhone VARCHAR 
     , FaxPhone  VARCHAR 
  )
Nenormalizuota samdinių lentelė

Ši lentelė yra labai paprasta, pakankamai aiški ir švari, dėl to lengva matyti, kodėl kai kurie programuotojai ją taikytų. Problemos prasideda, jeigu darbuotojas turi daugiau negu vieną mobilųjį telefoną. Jeigu sistema jau yra naudojama, tokio papildomo funkcionalumo prašymas dažnai įtakotų prastų problemos apėjimų (workaround) taikymą, pvz.: numerius saugoti atskirtus per kablelį ("86712345678, 8679874563"). Jeigu atsiranda naujo tipo telefonas kontaktas, reiktų keisti schemą; jeigu didžioji dauguma samdinių turi tiktai mobilų telefoną, lentelė turėtų daug nepanaudotos vietos. Visą tai gali būti pašalinama sumodeliavus schemą kurioje telefono tipas, telefono numeris ir samdinys yra atskiros būtybės: 


CREATE TABLE Employee 
  ( 
     Id          INT IDENTITY(1, 1) 
     , FirstName VARCHAR 
     , LastName  VARCHAR 
  ) 

CREATE TABLE PhoneNumberType 
  ( 
     Id           INT IDENTITY(1, 1) 
     , NumberType VARCHAR 
  ) 

CREATE TABLE PhoneNumber 
  ( 
     Id             INT IDENTITY(1, 1) 
     , NumberTypeId INT 
     , EmployeeId   INT 
     , Number       VARCHAR 
  )
Normalizuota samdinių lentelė

Kadangi SQL yra sukurta dirbti su normalizuotais duomenimis, yra labai lengva susikonstruoti bet kokį įrašą iš daugelio skirtingų lentelių. Taipogi SQL geriausiai dirba su pilnomis laukų reikšmėmis: jeigu telefono lauke yra saugomi per kablelį atskirti telefonai, paieška pagal vieną specifinį telefoną tampa daug brangesnė, nes tokio lauko neįmanoma indeksuoti ir reikia naudoti LIKE arba SUBSTRING() funkcija.

Duomenų bazių normalizavimas yra skirstomas į atskiro lygio formas, kur pirmoji normalinė forma yra pati paprasčiausia ir sekančios yra sudėtingesnės. Yra rekomenduojama turėti duomenų bazę kuri atitinka bent 3-ąja normalinę formą (4-oji ir 5-oji yra naudingos bet nebūtinos)[18]. Jeigu yra matoma, kad aukštesnis normalizavimas gali sukelti greitaveikos problemų, yra vis vien geriau iš pradžių sunormalizuoti ir po to denormalizuoti tiktai ten kur reikia [8].

Kitos optimizacijos

Iš programinio kodo, kuris sąveikauja su duombaze, duomenų įterpimas per ciklą dažniausiai yra pats paprasčiausias/intuityviausias sprendimas: 


foreach(var person in people)
{
  sql.RunQuery(
	"INSERT INTO Person (LastName, FirstName, OtherInfo) VALUES (@l, @f, @o)",
	person.LastName,
	person.FirstName,
	person.OtherInfo );
}
Paprastas įterpimo iš kodo sprendimas

Jeigu 'people' kolekcija yra labai didelė, toks įterpimas gali labai ilgai užtrukti, nes kiekvienam asmeniui turi būti pilnai praeita užklausos vykdymo grandinė (sujungimo su SQL serveriu inicijavimas, užklausos nusiuntimas ir t.t.). Tokiais atvejais yra įmanoma įterpti daugiau negu vieną įrašą su viena užklausa: 


INSERT INTO Person 
  (LastName, FirstName, OtherInfo) 
VALUES
  (@l1 , @f1 , @o1),
  (@l2 , @f2 , @o2),
  (@l3 , @f3 , @o3),
  ...
Įterpimo užklausa, vykdanti daugiau negu vieno įrašo įterpimą

Jeigu 'people' yra labai didelis (skaičiuojamas milijonais), tektų grąžinti ciklą, tiktai kiekvienu jo taktu būtų galima įterpti aprėpiama kiekį (1000 įrašu vienu metu). [11]

Jeigu reiktų atnaujinti kelių įrašų laukus su unikalia informaciją, tektų daryti kažką panašaus į tai: 


foreach(var levelBaseCostMapping in levelBaseCostMappings)
{
  sql.RunQuery(
	"UPDATE Item SET BaseCost = @bCost WHERE Level = @lvl",
	levelBaseCostMapping.BaseCost,
	levelBaseCostMapping.Level );
}
Paprastas įrašų atnaujinimo iš kodo sprendimas

Tokio tipo ciklą galima apeiti pasinaudojant SQL CASE'u: 


UPDATE Item 
SET    BaseCost = CASE Level 
					WHEN @lvl1 THEN @bCost1 
					WHEN @lvl2 THEN @bCost2 
					WHEN @lvl3 THEN @bCost3
					...
				  END 
WHERE  Level IN ( @lvl1, @lvl2, @lvl3 )
Įrašų atnaujinimo su CASE užklausa

Literatūros sąrašas