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:

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:

ALTER TABLE t ENGINE = MYISAM;

(0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0)

Een database kan tabellen van verschillende types bevatten.

MySQL oplag-engines

TypeOmschrijving
ISAMLegacy engine, niet langer ondersteund
MYISAMHerziening van ISAM engine met ondersteuning voor dynamische lengte velden
INNODBACID-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
CSVText-based engine voor CSV recordsets
ARCHIVEMotor met compressie functies voor grote recordsets
FEDERATEDEngine voor remote tabellen
NDB CLUSTEREngine voor geclusterde tabellen
MERGEEngine voor samengevoegde tabellen
BLACKHOLEBitBucket 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:

  • Niet-transactie veilige engines
  • De originele opslag engine was ISAM, welke niet-transactionele tabellen beheerde. Deze engine is vanaf MySQL 5.1 verwijderd en is vervangen door MyISAM.
  • De MyISAM opslag engine is een verbeterde vervanging voor ISAM.
  • De MEMORY opslag engine (voorheen bekend als HEAP) voorziet in-memory tabellen.
  • De MERGE opslag engine maakt het mogelijk een verzameling van identieke MyISAM tabellen te behandelen als één enkele tabel.
  • Transactieveilige engines
  • De InnoDB, BDB en NDB opslag engines werden in latere versies van MySQL geïntroduceerd en behandelen transactie-veilige tabellen.
  • InnoDB is de standaard storage engine.
  • BDB is alleen beschikbaar op de besturingssystemen die dit ondersteunen (Linux Intel, Sun Solaris, FreeBSD, …).
  • NDB (Network Database) Cluster is de opslag engine gebruikt die door MySQL Cluster wordt gebruikt om tabellen te implementeren die verdeeld zijn over vele computers.

Voordelen van transactie-veilige tabellen (TST)

Transactie veilige tabellen (TST) hebben een aantal voordelen ten opzichte van niet-transactie-veilige tabellen (NTST):

  • Veiliger. Zelfs als MySQL crasht of hardwareproblemen zou krijgen, kunt u uw data terugkrijgen, hetzij door automatisch herstel of vanaf een backup plus het transactielogboek.
  • U kunt de vele statements combineren en ze allemaal op hetzelfde moment accepteren met het COMMIT statement (indien autocommit is uitgeschakeld, of na het START TRANSACTION).
  • U kunt ROLLBACK uitvoeren om de wijzigingen te negeren (als autocommit is
    uitgeschakeld, of na START TRANSACTIE).
  • Als er een update transactie mislukt, zullen al uw wijzigingen worden hersteld. (Met
    niet-transactie-veilige tabellen, zullen alle veranderingen die hebben plaatsgevonden
    permanent zijn.)
  • Transactie-veilige opslag engies kunnen beteregelijktijdigheid voorzien voor tabellen
    die veel gelijktijdige updates  met leesopdrachten krijgt.

Niet-transactie veilige tabellen (NTSTs) hebben een aantal voordelen ten opzichte van transactie-veilige tabellen (TST), die optreden omdat er geen transactie overhead plaatsvindt:

  • Veel sneller
  • Lagere schijfruimte vereisten
  • Minder geheugen nodig om updates uit te voeren

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.

Selecteren van een tabel opslag engine

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 opslag 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.

MyISAM opslag engine

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.

  • MyISAM tabellen zijn geoptimaliseerd voor compressie en snelheid.
  • Het MyISAM formaat ondersteunt grote tabel bestanden (maximaal 256TB in grootte).
  • Tabellen en tabelindexen kunnen met het MySQL’s myisampack hulpprogramma gecomprimeerd of ‘verpakt’ worden in kleinere read-only tabellen die minder schijfruimte innemen. Deze functie is handig bij het opslaan van grote BLOB of tekstvelden.
  • MyISAM tabellen zijn direct uitwisselbaar tussen verschillende besturingssystemen en platforms (bijvoorbeeld, dezelfde MyISAM tabel kan worden gebruikt voor zowel Windows als UNIX-besturingssystemen).
  • MyISAM tabellen heeft de mogelijkheid om BLOB en TEXT kolommen te indexeren.
  • VARCHAR velden kunnen ofwel beperkt worden tot een bepaalde lengte of dynamisch aangepast worden.
  • MyISAM tabellen kunnen ofwel een vaste lengte of dynamische-length hebben.
  • MySQL controleert bij het opstarten automatisch of MyISAM tabellen corrupt zijn en kan ze zelfs repareren na problemen.
  • Tabelgegevens en tabelindex bestanden kunnen op verschillende locaties opgeslagen worden, zelfs op verschillende bestandsformaatsystemen.
  • Intelligente defragmentatie logica zorgt voor een hoge-prestatie coëfficiënt, zelfs voor tabellen met een groot aantal invoegingen, wijzigingen en verwijderingen.

InnoDB opslag engine

De InnoDB opslag engine is onderdeel van MySQL sinds MySQL 4.0. Met MySQL 5.5 werd InnoDB de standaard opslag engine.

  • InnoDB is een volledig ACID-compliant en efficiënt tafel indeling die volledige ondersteuning voor transacties in MySQL
    zonder afbreuk te doen aan snelheid of prestaties. Fijnkorrelig (rij- en tabel-niveau) sloten het verbeteren van de betrouwbaarheid van
    MySQL transacties en InnoDB ondersteunt ook puls- leest en multiversioning (kenmerkt voorheen alleen
    beschikbaar in Oracle Database).
  • InnoDB tabellen kunnnen groeien tot 64TB in grootte.
  • asynchrone I / O en een sequentiële read-ahead buffer te verbeteren data retrieval snelheid en een “maatje algoritme” en
    Oracle-type tablespaces leiden tot geoptimaliseerde bestand en geheugenbeheer.
  • InnoDB ondersteunt ook automatische creatie van hash indexen (indices berekend met behulp van de (snel) hash-functie) in
    geheugen op een als een noodzakelijke basis om de prestaties te verbeteren, en het gebruikt als buffer om de betrouwbaarheid te verbeteren en
    snelheid van de database operaties.
  • Als gevolg hiervan, InnoDB tabellen wedstrijd (en soms hoger zijn dan) de prestaties van MyISAM tabellen.
  • InnoDB tafels zijn volledig draagbare tussen verschillende besturingssystemen en architecturen.
  • Door hun transactionele aard, ze zijn altijd in een consistente toestand.
  • MySQL maakt ze nog robuuster door ze te controleren op corruptie en het herstellen van hen bij het opstarten. Herstellen
    van een crash van uw MySQL server, de enige vereiste is om het opnieuw op te starten. InnoDB controleert automatisch de logboeken en
    voert een roll-forward van de database naar het heden. InnoDB rolt automatisch back-vastgelegde transacties
    dat bij het tijdstip van de crash waren.
  • Ondersteuning voor buitenlandse sleutels en plegen, en rollback.
  • Dit is een van de meest full-featured table formaten beschikbaar in MySQL.

NDB opslag engine (NDBCLUSTER)

De NDBCLUSTER opslag engine implementeert een high-availability, in-memory, tabeltype die ontworpen is om alleen gebruikt te worden in geclusterde MySQL server omgevingen.

  • Deze tabellen zijn direct toegankelijk vanuit alle MySQL-servers in het cluster.
  • Het NDB-formaat ondersteunt multistatement transacties en rollbacks.
  • Het NDB-formaat (samen met enkel InnoDB) ondersteunt foreign key constraints.
  • Het NDB formaat ondersteunt grote tabel bestanden (maximaal 384 EB in grootte), velden van variabele lengte en replicatie.
  • NDB tabellen ondersteunen geen opslagpunten of statement-gebaseerde replicatie, en het aantal velden en indexen per tafel is beperkt tot 128.
  • Optionele-schijf back-operatie voor data duurzaamheid.

ARCHIVE opslag engine

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.

FEDERATED opslag engine

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.

MEMORY (HEAP) opslag engine

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.

Wat is een tijdelijke tabel? Is dat hetzelfde als een tabel gemaakt met de Memory Storage Engine?

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.

CSV opslag engine

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.

MERGE opslag engine

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.

BLACKHOLE opslag engine

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.

Criteria voor opslag engine

Om te beslissen wat de meest geschikte opslag engine voor een tabel is, moet men rekening houden met de volgende factoren:

  • Frequentie van leesopdrachten versus schrijfopdrachten
  • Transactionele ondersteuning
  • Ondersteuning voor externe sleutels
  • Indexering vereisten
  • Grootte van de tabellen en de snelheid waarmee het zal groeien
  • OS / architectuur overdraagbaarheid
  • Vereisten voor toekomstige uitbreidbaarheid en aanpasbaarheid aan veranderende gegevensvereisten

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.

Wanneer MEMORY of NDB Cluster gebruiken?

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:

  • Bewerkingen met betrekking tot transiënte, niet-kritieke gegevens zoals sessiebeheer of caching. Wanneer de MySQL server stopt of opnieuw wordt opgestart, gaat de data in de geheugentabellen verloren.
  • In-memory opslag voor snelle toegang en lage latentie.
  • Gegevens volume kan volledig in het geheugen passen zonder dat het besturingssysteem virtuele geheugen pagina’s moet uitwisselen.
  • Een alleen-lezen of meestal-gelezen gegevens toegangspatroon (beperkte bijwerkingen).

MySQL NDB Cluster biedt dezelfde functies als de MEMORY engine maar met hogere prestatieniveaus en extra functies die niet beschikbaar met MEMORY:

  • Rij-niveau vergrendeling en multiple-thread bewerkingen voor laag conflict tussen cliënten.
  • Schaalbaarheid zelfs met de gemengde statements die schrijfopdrachten bevatten.
  • Optionele schrijf ondersteunde bewerkingen voor duurzaamheid van gegevens.
  • Gedeelde-niets architectuur en meervoudige-host-bewerkingen zonder single point of failure, waardoor 99,999% beschikbaarheid mogelijk is.
  • Automatische verdeling van de gegevens over de knooppunten; applicatie-ontwikkelaars hoeven geen aangepaste opdeling of partitionering oplossingen te voorzien.
  • Ondersteuning voor data types van variabele lengte (met inbegrip van BLOB en TEXT) die niet ondersteund worden door MEMORY.