MySQL/MariaDBMoteurs de table
Les différents moteurs de tables
Moteurs de stockage
On appelle moteur de stockage l'ensemble des algorithmes utilisés par un SGBDR pour stocker les informations et y accéder au moyen d'une requête SQL.
Le moteur de tables originale était ISAM (supprimé en MySQL 5.0).
Les moteurs de MySQLmysql> SHOW ENGINES; Moteurs transactionnels
Certains moteurs gèrent les transactions là où d'autres ne le font pas.
Moteurs transactionnels
Les moteurs transactionnels (InnoDB...) sont plus surs que les moteurs non transactionnels, car ils assurent qu'une opération s'est exécutée du début à la fin sans être interrompue, et permettent d'annuler l'opération entière au cas où un incident serait survenu. Les moteurs transactionnels offrent les sécurités suivantes :
- Si un problème matériel ou électrique survient pendant une opération et que celle-ci ne peut se terminer, les anciennes données sont récupérables et ne sont pas corrompues avec des fragments de nouvelles données.
- Vous pouvez grouper les instructions exécutées par MySQL. Il est plus simple pour lui d'effectuer plusieurs opérations identiques à la suite que d'effectuer de nombreuses opérations différentes.
- Si une mise à jour échoue, les changements sont annulés
- Les moteurs transactionnels obtiennent de meilleures performances pour les accès concurrents en lecture
Moteurs non transactionnels
Les moteurs non transactionnels (MyIsam...) offrent en contrepartie de meilleures performances, car ils ne sont pas soumis à des vérifications nombreuses
- Plus rapides
- Moins de place utilisée sur le disque
- Moins de mémoire consommée
⚠ il est très fortement déconseillé de mélanger des tables à moteurs transactionnels et des tables à moteurs non transactionnels au sein d'une même transaction.
Indiquer le type de tablemysql> CREATE TABLE matable(5) ENGINE = INNODB;mysql> ALTER TABLE matable ENGINE = MYISAM; Moteur de table MyISAM
Le classique. Utilise l'utilitaire myisamchk : pour vérifier ou réparer une table MyISAM. Il est très utilisé pour le web car, comme il ne gère ni les clés étrangères, ni les transactions, il n'a pas à vérifier la validité des enregistrements. Cela permet donc un précieux gain de temps sur des tables très fréquemment ouvertes en écriture/lecture.
Une table MyISAM utilise trois fichiers :- maTable.FRM : Fichier de définition de la table
- maTable.MYD : Fichier contenant les données de la table
- maTable.MYI : Fichier d'index
Recherche FULL-TEXT (Texte Intégral)
L'utilisation de la recherche FULL-TEXT est un des grands avantages de MyISAM. Lorsque nous souhaitons rechercher un mot dans une table, nous pensons par défaut à l'opérateur de recherche LIKE et à ses différents jokers.
Exemple avec l'opérateur LIKESELECT * FROM maTable WHERE monChamp LIKE '%ma recherche%'; Moteur de table innoDB
Un gestionnaire de table pour de grandes quantités de données. Premières tables MySQL qui supportent les contraintes de clés étrangères (FOREIGN KEY).
InnoDB est un moteur relationnel. Il s'assure que les relations entre les données de plusieurs tables sont cohérentes et que si l'on modifie certaines données, que ces changements soient répercutés aux tables liées.
Plus d'info sur le moteur InnoDB
Moteur de tables MERGE
Il s'agit d'un moteur regroupant plusieurs tables MyISAM de manière transparente. Les tables fusionnées existent encore indépendamment, MERGE se contente de fournir une interface unique pour accéder en lecture à toutes les tables simultanément, et en écriture selon des règles que l'on aura fixé.
Les tables MyISAM fusionnées avec MERGE peuvent provenir de plusieurs bases de données, tant qu'elles sont sur le même serveur physique. MERGE gère les index de la même manière que MyISAM, sauf pour les index FULLTEXT qu'il ne prend pas en compte.
MERGE utilise 2 paramètres supplémentaires dans sa déclaration en plus de ENGINE=MERGE :CREATE TABLE Personne(
...
)ENGINE=MERGE UNION(baseSite1.PersonneSite1, baseSite2.PersonneSite2) INSERT_METHOD=LAST; Moteur de table MEMORY
Les tables de type Memory enregistrent les données dans la mémoire vive de la machine (RAM), d'où un gain considérable de rapidité pour accéder aux informations.
Ceci en fait le moteur de stockage le plus rapide que propose MySQL, mais aussi le plus dangereux.
MEMORY est parfait pour stocker des données purement temporaires qui ont besoin d'être traitées rapidement et surtout dont la perte n'est pas significative.
Autre moteur de table
BLACKHOLE
Ou en français : trou noir. Ce moteur porte bien son nom, ce qu'on y stocke n'en ressort jamais, et pour cause, il est l'équivalent du /dev/null d'Unix/Linux.
Toute données envoyées dans une table BLACKHOLE est immédiatement détruite, mais l'action est cependant consignée dans les logs de MySQL.
Ce moteur de table est utile pour simuler des copies de tables ou vérifier la syntaxe d'un système de sauvegarde. Il permet entre autres de recherche facilement des goulots d'étranglement dans les requêtes SQL d'une application dans écrire réellement sur le disque, ou lorsqu'on désire tester les performances du système de logs de MySQL.
BerkeleyDB
Fonctionnalités équivalentes à celles proposées par InnoDB. Il a depuis été retiré de la distribution depuis MySQL 5.0. Ce moteur était surtout utile à l'époque des premières versions de MySQL, lorsqu'InnoDB n'existait pas.
ARCHIVE
Moteur spécialisé dans le stockage de grosses quantités de données de manière très économique : les données sont compressées à leur insertion et aucun index n'est généré, ce qui améliore la rapidité en écriture. Il ne gère ni les transactions, ni les relations ni les index, et ne permet de faire que des requete SELECT et INSERT. Les ordres de suppression ou de modifications seront refusés. On peut ainsi conserver d'énormes quantités de données sans craindre qu'elles soient supprimées ou modifiées.
CSV
Les valeurs sont stockées dans un fichier texte, séparées par des virgules, les lignes sont séparées par des sauts de ligne. Il permet une grande interopérabilité entre des systèmes externes à MySQL
FEDERATED
Crée une définition de table, mais rien n'est stocké directement sur le serveur. Il s'agit d'un moteur de stockage distant : les données sont en réalité hébergées sur un autre serveur MySQL.
Moteurs MariaDB
En plus des moteurs standard MyISAM, Blackhole, CSV, MEMORY, Archive et MERGE les suivants sont ajoutés dans MariaDB :
- Aria (ex Maria) est un upgrade de MyIsam
- XtraDB pour remplacer InnoDB
- FederatedX pour remplacer Federated
- OQGRAPH (dans MariaDB 5.x)
- SphinxSE
- IBMDB2I Oracle l'a retiré dans MySQL 5.1.55 mais le code à été gardé dans MariaDB jusqu'en version 5.5
- TokuDB (dans MariaDB 5.5 et MariaDB 10.0)
- Cassandra : moteurs de stockage NoSQL dans MariaDB 10.0
- CONNECT dans MariaDB 10.0
- SEQUENCE dans MariaDB 10.0
- Spider permet de partitionner de grandes tables afin de les répartir sur plusieurs serveurs distants.
- PBXT dans MariaDB 5.1, 5.2 et 5.3. (Désactivé dans 5.5)
- S3 (MariaDB 10.5), qui sert pour héberger des tables MariaDB dans Amazon S3 ou tout autre stockage en nuage
- ColumnStore (MariaDB 10.5), qui stocke les données dans les liens de colonne. Conçu pour organiser le traitement et l'exécution de requêtes analytiques sur de grands ensembles de données (Data Warehouse).
Les questions à se poser pour le choix d'un moteur
- Est-ce que mes données sont temporaires et peu importantes ?
Si les données sont temporaires et ne sont pas vitales à l'application, MEMORY sera un bon choix pour sa volatilité. - Est-ce que mes données sont en relations avec des données d'autres tables ?
Si les données sont liées à d'autres tables, on choisira InnoDB pour son respect des relations. - Est-ce que mes données doivent rester à tout prix intègres et ne pas contenir d'incohérences ?
Si la cohérence de la base de données et son intégrité sont primordiales, InnoDB sera choisi pour son respect des transactions. - Est-ce que je dois effectuer des recherches sur des textes de taille importante ?
Si des recherches sur des textes de grande taille sont à faire, MyISAM et ses index FULLTEXT seront un choix judicieux. - Est-ce que je dois faire plus d'insertions, modifications ou suppressions que de lectures dans ma table ?
MyISAM verrouille la table entière à chaque insertion, modification ou suppression. Si de nombreuses opérations de ce type sont faites, il faudra plutôt s'>orienter vers InnoDB qui verrouille indépendamment chaque ligne et évite ainsi de ralentir inutilement l'application. - Est-ce que j'ai besoin d'historiser mes données ?
S'il est important de pouvoir historiser les données, MERGE sera interessant pour sa possiblité d'agrandir pratiquement à l'infini la table tout en structurant les données en sous-tables.
