MySQL InnoDB vs. MyISAM saugojimo varikliai

Vienam universiteto moduliui ruošiau pristatymą apie InnoDB vs. MyISAM MySQL saugojimo variklius (storage engine). Pagalvojau, kad tai gali būti naudinga ir mano blogo skaitytojams. Tad štai ir mano pristatymas - praktiškai paėmus, santrauka iš kelių rastų internetinių šaltinių :)

Kodėl yra  svarbi ši tema?
MySQL 5.5 naudoja InnoDB kaip standartinį saugojimo variklį (storage engine), o MySQL 5.1 naudoja MyISAM kaip standartinį saugojimo variklį.
MySQL kūrėjai sako: "Mūsų patirtis sako, kad dauguma vartotojų niekada nekeičia standartinių nustatymų. Su MySQL 5.5 InnoDB tampa standartiniu saugojimo varikliu. Vėlgi mes tikimės, kad daugoma vartotojų nekeis standartinių nustatymų".
Skirtingi MySQL saugojimo varikliai
MySQL saugojimo variklis (storage engine) yra duomenų organizavimo duomenų bazėje būdas. MySQL turi šiuos standartinius duomenų bazių saugojimo variklius:
MyISAM - lentelės yra saugomos kietajame diske trijuose failuose.
InnoDB - tranzakcijų atžvilgiu saugus (su ACID suderinamas) MySQL saugojimo variklis, kuris turi nusiuntimo (commit), atkūrimo (rollback) ir lūžių atstatymo (crash recovery) galimybes vartotojo duomenų apsaugai.
MERGE - identiškų MyISAM lentelių rinkinys, kurios gali būti naudojamos kaip viena.
MEMORY (HEAP) - lentelių turinys yra saugojamas kompiuterio atmintyje.
BDB (BerkelyDB) - pašalintas pradedant nuo MySQL 5.1.
EXAMPLE - nieko nedarantis saugojimo variklis. Jo paskirtis - tarnauti kaip MySQL kodo pavyzdžiui, iliustruojančiam kaip pradėti rašyti saugojimo variklį.
FEDERATED - prieina prie lentelių duomenų nutolusiose duomenų bazėse, o ne vietinėse.
ARCHIVE - naudojamas saugoti didelius duomenų kiekius be indeksų su labai mažais antspaudais (footprint).
CSV - saugo duomenis tekstiniuose failuose. Duomenys saugomi atskirti kableliais.
BLACKHOLE - yra "juodoji skylė" - priima duomenis, tada juos pašalina ir nieko nesaugo.
InnoDB galimybės
InnoDB turi daug pranašumų, palyginus su iki 5.5 buvusiu standartiniu MyISAM saugojimo varikliu. Pagrindiniai pranašumai:
Tranzakcijos - galimybė atlikti veiksmų sekas. Veiksmų metu nepasisekus kuriai nors operacijai, galima atstatyti prieš tai buvusią būseną (rollback), panaikinant veiksmų sekos metu atliktus pakeitimus.
Išorinių raktų (foreign key) palaikymas - galimybė sudaryti ryšius tarp lentelių.
Atsarginių kopijų darymas ir atkūrimas.
Vienos duomenų bazės maksimalus dydis - 64 TB.
Sudėtingesnės galimybės
Skaldymas (partitioning) - galimybė išskaidyti lentelių dalis po visą failų sistemą pagal vartotojo nustatytas taisykles. Dėl to skirtingos lentelių dalys yra saugomos kaip atskiros lentelės skirtingose vietose.
Adaptyvinė maiša (Adaptive Hash)- "kai tos pačios eilutės yra išrenkamos nuolat iš lentelės, Adaptive Hash Index galimybė leidžia prieiti prie duomenų žymiai greičiau, visai kaip naudojant maišos (hash) lentelę."
Įterpimo (insert) buferis
InnoDB įterpimo buferis žymiai sumažina disko įvesties/išvesties (IO) kreipinių skaičių atliekant aktyvias pakeitimo  (įterpimo, atnaujinimo, pašalinimo) operacijas, kai duomenų bazė netelpa buferio "baseine" (buffer pool).
"Mano aprašytame teste įterpimo bufferis paspartina InnoDB 36 kartus ir yra tikėtina, kad šis dydis priartės prie 80-ies kartų jeigu aš leisčiau šiam testui pasibaigti" - rašo MySQL@Facebook grupėje kūrėjai.

MyISAM privalumai
Paprasčiau projektuoti ir kurti, tad geriau pradedantiesiems. Nereikia jaudintis dėl ryšių tarp lentelių, kadangi nėra išorinių raktų (foreign key) palaikymo.
Greitesnis negu InnoDB dėl paprastesnės struktūros ir reikalauja mažiau serverio resursų.
Pilno teksto (full-text) indeksavimas.
Labai gerai lentelėmss, iš kurių yra dažnai išrinkinėjami duomenys (SELECT užklausos).
MyISAM trūkumai
Nėra duomenų integralumo (ryšių) patikros, tad duomenų bazių administratoriai ir programuotojai tampa atsakingi už duomenų integralumą.
Nepalaiko tranzakcijų, kurios yra būtinos sistemoms, dirbančioms su svarbiais duomenimis (pavyzdžiui, bankinėms sistemoms).
Lėtesnis saugojimo variklis negu InnoDB lentelėse, kurios yra dažnai atnaujinamos (update) arba įterpinėjamos (insert), kadangi visa lentelė yra užrakinama dėl įterpimo arba atnaujinimo užklausos.
InnoDB privalumai
InnoDB turėtų būti naudojama ten, kur duomenų integralumas yra svarbus. InnoDB naudoja ryšius tarp lentelių ir tranzakcijas duomenų integralumui užtikrinti.
Greitesnis rašymo užduotyse (įterpimo, šalinimo), kadangi naudoja eilutės lygio užrakinimą (row-level locking) ir saugo tiktai vienos eilutės pasikeitimus įterpimo ar atnaujinimo operacijos metu.
InnoDB trūkumai
Kadangi InnoDB privalo dirbti su ryšiais tarp lentelių, duomenų bazių administratoriai ir duomenų bazių schemų kūrėjai turi praleisti daugiau laiko projektuodami duomenų modelius. Jie yra sudėtingesni negu MyISAM modeliai.
Užima daugiau sisteminių resursų, pavyzdžiui, RAM. Dėl to rekomenduojama išjungti InnoDB variklį tose sistemose, kuriose jo nereikia po MySQL instaliavimo.
Nėra pilno teksto (full-text) indeksavimo.
Išvados

Kita vertus, galima vienoje duomenų bazėje laikyti kelis lentelių tipus. Ir tai yra net rekomenduojama bei dažnai naudojama. Tiesa, jeigu dėl skirtingų lentelių tipų sujungimo kyla našumo problemų, reikėtų pabandyti konvertuoti vieną lentelių saugojimo tipą į kitą ir įvertinti, ar tai padėjo išspręsti našumo problemą.

Šaltiniai