Easy-Micro

ESPACEMEMBRE MySQL/MariaDB
Moteurs de table
Les différents moteurs de tables

Formation MySQL par Easy-Micro 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 MySQL 
mysql> SHOW ENGINES;
Liste des les moteurs de MySQL

Formation MySQL par Easy-Micro 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
Source : Site du Zéro

⚠ 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 table 
mysql> CREATE TABLE matable(5) ENGINE = INNODB;
Note: La commande ENGINE remplace la commande TYPE depuis MySQL 3 (première version avec plusieurs moteurs de tables).
Modifier le type de table (ou en modifiant la variable système table_type) 
mysql> ALTER TABLE matable ENGINE = MYISAM;

Formation MySQL par Easy-Micro 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 LIKE 
SELECT * FROM maTable WHERE monChamp LIKE '%ma recherche%';
Cette requête nous retournera toutes les lignes où le champ "monChamp" contiendra "ma recherche".

Formation MySQL par Easy-Micro 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

Formation MySQL par Easy-Micro 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;
Liste des tables à fusionner. INSERT_METHOD définit où seront insérées les nouvelles valeurs : FIRST insérera les données dans la première table de la liste alors que LAST les insérera dans la dernière table de la liste

Formation MySQL par Easy-Micro 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.

Formation MySQL par Easy-Micro 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.

Formation MySQL par Easy-Micro 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).
Source : Mariadb.com

Formation MySQL par Easy-Micro 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.
Source : Site du Zéro
< Page précédente MYSQL/MARIADB Page suivante >