Wanneer u een nieuwe tabel aanmaakt, kunt u in MySQL aangeven wat voor soort tabel u wenst te creëren door aan het CREATE TABLE statement een ENGINE of TYPE tabeloptie toe te voegen:
| MySQL |
CREATE TABLE t (i INT) ENGINE = INNODB; (0 row(s) affected) |
Als u de ENGINE of of TYPE optie weglaat, is de standaardtabeltype (meestal) InnoDB. Dit kan worden veranderd door het instellen van de table_type_system variabele.
Als u een tabel naar een ander type wenst te converteren, gebruikt u een
ALTER TABLE
statement dat het nieuwe type aangeeft:
| MySQL |
ALTER TABLE t ENGINE = MYISAM; (0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0) |
Een database kan tabellen van verschillende types bevatten.
| Type | Omschrijving |
| ISAM | Legacy engine, niet langer ondersteund |
| MYISAM | Herziening van ISAM engine met ondersteuning voor dynamische lengte velden |
| INNODB | ACID-compliant transactionele engine met ondersteuning voor foreign keys |
| BDB (BerkeleyDB) | ACID-compliant, alleen beschikbaar op de besturingssystemen die dit ondersteunen (Linux Intel, Sun Solaris, FreeBSD, ...) |
| MEMORY (HEAP) | -Geheugen gebaseerde engine met ondersteuning voor hash indexen |
| CSV | Text-based engine voor CSV recordsets |
| ARCHIVE | Motor met compressie functies voor grote recordsets |
| FEDERATED | Engine voor remote tabellen |
| NDB CLUSTER | Engine voor geclusterde tabellen |
| MERGE | Engine voor samengevoegde tabellen |
| BLACKHOLE | BitBucket engine |
MySQL ondersteunt verschillende opslag engines die als handlers fungeren voor verschillende soorten tabellen. MySQL opslag engines omvatten zowel transactie-veilige tabel engines als niet-transactie-veilige tabel engines:
Transactie veilige tabellen (TST) hebben een aantal voordelen ten opzichte van niet-transactie-veilige tabellen (NTST):
Niet-transactie veilige tabellen (NTSTs) hebben een aantal voordelen ten opzichte van transactie-veilige tabellen (TST), die optreden omdat er geen transactie overhead plaatsvindt:
U kunt transactie-veilige en niet-transactie-veilige tabellen in dezelfde statements combineren.
Echter binnen een transactie of met autocommit uitgeschakeld, worden veranderingen in niet-transactie-veilige tabellen nog steeds onmiddellijk doorgevoerd en kunnen niet worden teruggedraaid.
MySQL ondersteunt een groot aantal verschillende opslag engines voor de
tabellen, elk met zijn eigen voor- en nadelen. Hoewel alle opslag engines in
MySQL redelijk efficiënt zijn, kan het gebruik maken van de verkeerde opslag
engine uw applicatie niet de mogelijkheid bieden om de maximaal mogelijke
prestaties te behalen. Bijvoorbeeld, tabellen die gebruik maken van de
ARCHIVE engine met frequente lees- en
schrijfopdrachten zullen aanzienlijk
langzamere prestaties optekenen dan als het gebruik zou maken van de InnoDB of
MyISAM engine.
ISAM was de originele opslag engine. MySQL versies voor MySQL 5.1 bevatten hoofdzakelijk de ISAM opslag engine voor compatibiliteit met legacy tabellen. Deze storage engine is vervangen door het MyISAM type en wordt niet meer ondersteund vanaf MySQL 5.1.
ISAM tabellen zijn vergelijkbaar met MyISAM tabellen, alhoewel het aan prestatieverbeteringen ontbreekt ten opzichte van het MyISAM formaat en daarom niet de optimalisatie efficiëntie prestatie van dat type bieden. Omdat ISAM indexen niet gecomprimeerd kunnen worden, gebruiken ze minder systeembronnen dan hun MyISAM tegenhangers. ISAM indexen vereisen ook meer schijfruimte ruimte, wat een probleem zou kunnen zijn in kleinere omgevingen.
Net als MyISAM, kunnen ISAM tabellen ofwel een vaste lengte of een dynamische-lengte hebben, hoewel de maximale sleutellengtes kleiner zijn met het ISAM-formaat. Het formaat kan niet overweg met tabellen groter dan 4 GB en de tafels zijn niet direct overdraagbaar tussen de verschillende platformen. Bovendien is de ISAM tabelvorm meer vatbaar voor fragmentatie, welk de query-snelheid kan verminderen, en heeft een beperktere ondersteuning voor data/index compressie.
De MyISAM opslag engine breidt het basis ISAM type uit met een bijkomende optimalisaties en verbeteringen. Tot MySQL versie 5.5 was MyISAM het standaard tabeltype.
De InnoDB opslag engine is onderdeel van MySQL sinds MySQL 4.0. Met MySQL 5.5 werd InnoDB de standaard opslag engine.
De NDBCLUSTER opslag engine implementeert een high-availability, in-memory, tabeltype die ontworpen is om alleen gebruikt te worden in geclusterde MySQL server omgevingen.
De ARCHIVE opslag engine biedt een manier om grote recordsets met niet frequente leesopdrachten op te slaan in een gecomprimeerd formaat. Het belangrijkste kenmerk van deze opslag engine is de mogelijkheid om gegevens met behulp van de zlib bibliotheek te comprimeren als ze worden ingevoegd en te decomprimeren als ze worden opgehaald.
Deze tabellen zijn ideaal voor opslag van historische gegevens, meestal om te voldoen aan audit- of compliance-normen.
Aangezien deze opslag engine niet ontworpen is voor het regelmatige leesopdrachten, mist het veel van de eigenschappen van InnoDB en MyISAM engines. ARCHIVE tabellen ondersteunen alleen de INSERT en SELECT bewerkingen, laten geen indexen toe (voeren volledige tabelscans uit tijdens leesopdrachten), negeren BLOB velden in leesbewerkingen en tonen lagere prestaties, op grond van hun on-the-fly compressie.
Dat gezegd hebbende zijn ARCHIVE tabellen nog steeds superieur aan gecomprimeerde MyISAM tabellen, omdat ze zowel lees- als schrijfbewerkingen ondersteunen en minder schijfruimte nodig hebben.
De FEDERATED opslag engine implementeert een 'stub'
tabel die alleen maar een tabeldefinitie bevat; deze tabeldefinitie wordt
gespiegeld op een externe MySQL server, die tevens de tabelgegevens bevat. Een
Federated tabel zelf bevat geen gegevens; liever gezegd, is het vergezeld van
verbindingsparameters die MySQL vertellen waar het moet kijken voor de
werkelijke tabelrecords. Een Federated tafel maakt het dus mogelijk om
vanaf een lokale server een MySQL tabel op een externe server te benaderen
zonder de noodzaak voor replicatie of clustering.
Een Federated 'stub' tabel kan wijzen naar een bron tabel dat gebruik maakt van een van de standaard MySQL opslag engines, met inbegrip van InnoDB en MyISAM.
Echter zijn Federated tabellen vrij beperkt: ze missen transactionele ondersteuning en indexen, kunnen geen gebruik maken van de MySQL query cache en zijn prestatiegewijs minder indrukwekkend.
De MEMORY opslag engine, zoals de naam al doet vermoeden, implementeert in-memory tabellen die hash indexen gebruiken (indices berekend volgens de (snelle) hashfunctie), waardoor ze ten minste 30 procent sneller zijn dan reguliere MyISAM tabellen.
De gegevens die hierin worden opgeslagen zijn enkel beschikbaar voor de levensduur van de MySQL server en worden gewist als de MySQL server crasht of wordt afgesloten. Hoewel deze tabellen een performatie voordeel kan bieden, maakt hun tijdelijke karakter ze ongeschikt voor geavanceerder gebruik dan tijdelijke gegevensopslag en beheer.
MEMORY prestaties worden beperkt als gevolg van
een enkele uitvoeringsthread en
de overhead van tabelvergrendelingen (niet-transactie-veilig; geen rij-niveau
vergrendelingen) bij het verwerken van updates. Dit limiteert de schaalbaarheid
wanneer de belasting toeneemt, in het bijzonder voor gemengde statements die
schrijfopdrachten bevatten.
Ondanks de in-memory verwerking van MEMORY tabellen, zijn zij op een drukke server niet per se sneller dan InnoDB tabellen, voor queries voor algemene doeleinden, of onder een lees-/schrijf-werkbelasting. Vooral waarbij tabelvergrendeling betrokken is kan het uitvoeren van wijzingen het gelijktijdig gebruik van MEMORY tabellen uit meerdere sessies vertragen.
Kan ik bepalen hoeveel geheugen een MEMORY tabel kan gebruiken? De grootte van de geheugentabellen kunnen worden beperkt door een waarde voor de 'max_heap_table_size' server variabele in te stellen.
Memory tabellen, welke gemaakt worden door het toevoegen van de
ENGINE=MEMORY
modifier aan een CREATE TABLE instructie, blijven
bestaande tijdens de levensduur
van de server. Hoewel ze worden vernietigd zodra het server-proces wordt
beëindigd; zijn ze toegankelijk voor alle connecterende cliënten.
Tijdelijke tabellen, welke zijn geïnitialiseerd met het CREATE TEMPORARY TABLE statement, zijn cliënt specifiek en blijven bestaan voor de duur van de sessie. Ze worden automatisch verwijderd wanneer de cliënt die hen heeft geïnstantieerd de verbinding met de MySQL-server sluit. Als zodanig, komen ze van pas voor vluchtige, sessie-gebaseerde gegevensopslag of berekeningen. Omdat ze sessie-afhankelijk zijn, kunnen twee verschillende cliënt-sessies gebruik maken van dezelfde tabelnaam zonder een conflict te veroorzaken. Tijdelijke tabellen kunnel elk van de ondersteunde MySQL opslag-engines gebruiken.
De CSV opslag engine biedt een handige manier om de overdraagbaarheid van tekstbestanden met de kracht van SQL queries samen te brengen. CSV tabellen zijn in wezen platte ASCII-bestanden, door elk veld van een record met komma's te scheiden. Dit formaat is begrijpelijk voor niet-SQL toepassingen, zoals Microsoft Excel, en kunnen dus gemakkelijk gegevens worden overgedragen tussen SQL en niet-SQL omgevingen.
Een vrij duidelijk beperking echter, is dat CSV tabellen geen ondersteuning bieden voor indexering en SELECT operaties moeten dus het volledige tabelscan uitvoeren, met de daarmee gepaard gaande impact op de prestaties.
CSV tabellen biedt ook geen ondersteuning voor het NULL datatype.
Een MERGE tabel, ook wel bekend als MRG_MyISAM tabel, is een virtuele tabel gemaakt door het combineren van meerdere identieke MyISAM tabellen in een enkele tafel. 'Identiek' betekent dat alle tabellen identieke kolom en index informatie hebben. MyISAM tabellen waarin de kolommen in een andere volgorde worden weergegeven, hebben niet precies dezelfde kolommen, of hebben de indexen in een andere volgorde, zal geen succesvolle samenvoeging toelaten.
Een MERGE tabel maakt gebruik van de indexen van zijn samengestelde tabellen en onderhoudt geen indexen van zijn eigen, wat in bepaalde situaties de snelheid kan verbeteren.
MERGE tabellen laten SELECT, DELETE en UPDATE bewerkingen toe, en kunnen van pas komen wanneer u gegevens uit verschillende tafels wilt halen of om de prestaties in joins of zoekopdrachten tussen een reeks tabellen te versnellen.
De BLACKHOLE opslag engine is MySQL equivalent van een bit bucket: Alle gegevens die in een Blackhole tabel worden ingevoerd, verdwijnen onmiddellijk, om nooit meer te zien. Dit heeft als nut als 'goedkope' SQL syntax controle-instrument, een statement logger, of een replicatie filter.
Om te beslissen wat de meest geschikte opslag engine voor een tabel is, moet men rekening houden met de volgende factoren:
MySQL laat u toe om verschillende opslag engines binnen dezelfde database te gebruiken. Je zou dus de MyISAM engine kunnen gebruiken voor tabelllen die frequent SELECT statements gebruiken en InnoDB engine voor tabellen die frequent INSERT statements of transacties gebruiken. De mogelijkheid om engines op een per-tabel basis te selecteren is uniek voor MySQL en speelt een belangrijke rol zijn uitmuntende prestaties.
Ontwikkelaars die op zoek zijn naar toepassingen die de MEMORY opslag engine gebruiken voor belangrijke, hoge beschikbaarheid, of regelmatig bij te werken gegevens moeten overwegen of de MySQL NDB Cluster geen betere keuze is.
Een typische gebruik voor de MEMORY engine omvat de volgende kenmerken:
MySQL NDB Cluster biedt dezelfde functies als de MEMORY engine maar met hogere prestatieniveaus en extra functies die niet beschikbaar met MEMORY: