last modified July 10, 2020
In dit hoofdstuk gaan we het hebben over MySQL storage engines.
Een storage engine is een software module die een database management systeem gebruikt om data te creëren, te lezen, te updaten van een database. Er zijn twee soorten storage engines in MySQL: transactionele en niet-transactionele.
Voor MySQL 5.5 en later, de standaard storage engine is InnoDB.De standaard storage engine voor MySQL voorafgaand aan versie 5.5 was MyISAM. Het kiezen van de juiste storage engine is een belangrijke strategische beslissing, die van invloed zal zijn op de toekomstige ontwikkeling. In deze tutorial, zullen we gebruik maken van MyISAM, InnoDB, Memory enCSV storage engines. Als MySQL nieuw voor u is en u bent het MySQL database management systeem aan het bestuderen, dan is dit niet zo’n probleem. Als u van plan bent een productie database te maken, dan wordt het ingewikkelder.
Lijst van opslag-engines
MySQL ondersteunde opslag-engines:
- InnoDB
- MyISAM
- Memory
- CSV
- Merge
- Archive
- Federated
- Blackhole
- Example
InnoDB is de meest gebruikte storage engine met transactie-ondersteuning. Het is een ACID-compliant opslagsysteem. Het ondersteunt vergrendeling op rij-niveau, herstel na een crash en multi-versie concurrency-controle. Het is de enige engine die foreign key referentialintegrity constraint biedt. Oracle raadt aan om InnoDB te gebruiken voor tabellen, behalve in gespecialiseerde gevallen.
MyISAM is de oorspronkelijke opslag-engine. Het is een snelle storage engine die geen transacties ondersteunt. MyISAM biedt tabel-levellocking. Het wordt meestal gebruikt in Web en data warehousing.
Memory storage engine maakt tabellen in het geheugen. Het is de snelste engine. Het biedt vergrendeling op tabelniveau. Het ondersteunt geen transacties.Memory storage engine is ideaal voor het maken van tijdelijke tabellen of snelle lookups.De gegevens gaan verloren wanneer de database opnieuw wordt opgestart.
CSV slaat gegevens op in CSV-bestanden. Het biedt een grote flexibiliteit, omdat gegevens in dit formaat gemakkelijk in andere toepassingen kunnen worden geïntegreerd.
Merge werkt op onderliggende MyISAM-tabellen. Met samenvoegtabellen kunnen grote hoeveelheden gegevens gemakkelijker worden beheerd. Het groepeert logisch een reeks identieke MyISAM-tabellen, en verwijst ernaar als één object.Goed voor data warehousing-omgevingen.
Archive storage engine is geoptimaliseerd voor zeer snel invoegen. Het comprimeert gegevens wanneer ze worden ingevoegd. Het ondersteunt geen transacties. Het is ideaal voor het opslaan en ophalen van grote hoeveelheden historische, gearchiveerde gegevens waarnaar zelden wordt verwezen.
De Blackhole-opslagmotor accepteert gegevens, maar slaat ze niet op. Opvragingen retourneren altijd een lege set. De functionaliteit kan worden gebruikt in gedistribueerde database ontwerpen waar gegevens automatisch worden gerepliceerd, maar niet lokaal worden opgeslagen. Deze storage engine kan worden gebruikt om performance tests of andere tests uit te voeren.
Federated storage engine biedt de mogelijkheid om MySQL servers te scheiden om zo één logische database te maken van vele fysieke servers.Queries op de lokale server worden automatisch uitgevoerd op de remote (federated) tabellen. Er worden geen gegevens opgeslagen op de lokale tabellen.Het is goed voor gedistribueerde omgevingen.
mysql> SHOW ENGINES\G*************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 2. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO...
Het SHOW ENGINES commando toont alle beschikbare engines die de server ondersteunt.
Het kiezen van de juiste engine
Geen storage engine is ideaal voor alle omstandigheden. Sommige presteren het beste onder bepaalde omstandigheden en presteren slechter in andere situaties. Er zijn compromissen die moeten worden overwogen. Een veiligere oplossing kost meer middelen; het kan langzamer zijn, meer CPU tijd kosten, en meer schijfruimte. MySQL is zeer flexibel in het feit dat het verscheidene verschillende opslag engines biedt. Sommige van hen, zoals de Archive engine, zijn gemaakt om te worden gebruikt in specifieke situaties.
In sommige gevallen is het antwoord duidelijk. Wanneer we te maken hebben met bepaalde betalingssystemen, zijn we verplicht de meest veilige oplossing te gebruiken. Wij kunnen ons niet veroorloven dergelijke gevoelige gegevens te verliezen. InnoDB is de juiste oplossing. Als we full-textsearch willen, dan kunnen we kiezen voor MyISAM of InnoDB. Alleen InnoDB ondersteunt een foreign key referentiële integriteitsbeperking en als we van plan zijn om deze beperking te gebruiken, dan is de keuze duidelijk.
Specificeren en wijzigen van opslag-engines
De opslag-engine wordt gespecificeerd op het moment dat de tabel wordt gemaakt.
mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), -> Cost INTEGER) ENGINE='MyISAM';
Het ENGINE sleutelwoord specificeert de opslag-engine die voor deze specifieke tabel wordt gebruikt.
Als we de storage engine niet expliciet opgeven, dan wordt de standaard storage engine gebruikt. Voor MySQL 5.5 was de standaard storage engine MyISAM. Voor MySQL 5.5 en later, de standaard storage engine is InnoDB.
Het is mogelijk om te migreren naar een andere storage engine. Merk op dat het migreren van een grote tabel kan een lange tijd duren. Ook kunnen er problemen optreden bij het migreren van tabellen. Sommige functies worden mogelijk niet in beide tabellen ondersteund.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| InnoDB |+--------+1 row in set (0,05 sec)
Deze SQL-instructie achterhaalt de gebruikte opslag-engine voor een Cars tabel in een mydb database. We kunnen ook SELECT CREATE TABLE Cars SQL-instructie gebruiken. De information_schema is een tabel die technische informatie over onze tabellen opslaat.
mysql> ALTER TABLE Cars ENGINE='MyISAM';
Deze SQL verklaring verandert de opslag engine van de Cars tabel in MyISAM.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| MyISAM |+--------+1 row in set (0,00 sec)
Nu is de opslag-engine van de tabel MyISAM.
In dit deel van de MySQL tutorial, hebben we storage engines behandeld.