Le moteur de tables MyISAM

L'environnement
MyISAM est le type par défaut de table en MySQL depuis la version 3.23. Il est basé sur ISAM et ajoute de nombreuses extensions pratiques. Chaque table MyISAM est stockée en trois fichiers. Les fichiers portent le nom de la table, et ont une extension qui spécifie le type de fichier;  Le fichier ".frm" stocke la définition de la table,  l'index est stocké dans un fichier avec l'extension ".MYI" (MYIndex)  et les données sont stockées dans un fichier avec l'extension ".MYD" (MYData). Pour spécifier explicitement que vous souhaitez une table MyISAM, il faut l'indiquer le avec l'option ENGINE ou TYPE lors de la création de la table, comme suit :

CREATE TABLE t (i INT) ENGINE = MYISAM;
CREATE TABLE t (i INT) TYPE = MYISAM;

Remarque: Normalement, les options ENGINE et TYPE sont inutiles  car MyISAM est le type par défaut de table en MySQL depuis la version 3.23 comme c'est dit plus haut, à moins d'avoir été spécifié autrement. On peut  vérifier ou réparer une table MyISAM avec l'utilitaire myisamchk. On peut aussi compresser les tables MyISAM avec l'utilitaire myisampack pour réduire leur taille sur le disque.

les nouveautés des tables MyISAM

Toutes les clés numériques sont stockées avec l'octet de poids fort en premier, pour améliorer la compression. Cela rend les données indépendantes du système d'exploitation et de la machine. La seule règle pour assurer la portabilité binaire des fichiers est que la machine doit utiliser des entiers signés pour le complément à 2 (c'est le cas de toutes les machines ces 20 dernières années), et un format de nombre à virgule flottante compatible IEEE (c'est aussi le format dominant). Le seul point où la portabilité n'est pas assurée est les machine portables, qui ont des processeurs originaux. Il n'y a pas de coût spécifique à stocker les données dans ce format. Les octets dans la table sont généralement non-alignés, et cela ne prend pas longtemps d'aligner des octets. De plus, le code qui lit les valeurs des colonnes n'est pas critique par rapport au reste du code.

• Support des grands fichiers (63 bits) sur les systèmes de fichiers et les systèmes d'exploitation qui supportent les grands fichiers.
• Les lignes de taille dynamique sont bien moins fragmentées lors de l'utilisation d'insertion et d'effacement. Cela se fait en combinant automatiquement les blocs adjacents libres, et en étendant la taille des blocs avec le suivant s'il est vide.
• Le nombre maximal d'index par table est de 64 (32 avant MySQL 4.1.2). Cela peut être changé en recompilant.
• Le nombre de colonnes maximal par index est 16.
• La taille maximale d'une clé est de 1000 octets (500 avant MySQL 4.1.2). Cela peut être changé en recompilant MySQL. Dans le cas où la clé est plus longue que 250 octets, une taille de bloc de clé plus large est utilisée, en remplacement des 1024 octets par défaut.
• Les colonnes BLOB et TEXT peuvent être indexées.
• Les valeurs NULL sont autorisées dans une colonne indexée. Elles prennent 0 à 1 octets par clé.

Les valeurs numériques sont stockées avec l'octet de poids fort en premier, pour permettre une meilleure compression.
Les fichiers d'index sont généralement plus petits en MyISAM qu'en ISAM. Cela signifie que MyISAM va utiliser moins de ressources systèmes que ISAM, mais il prendra plus de processeur lors de l'insertion de données dans un index compressé.
Lorsque les lignes sont insérées dans un ordre trié (comme lorsque vous utilisez une colonne de type AUTO_INCREMENT), l'arbre des clé sera scindé, pour que le noeud principal ne contienne qu'une clé. Cela va améliorer l'utilisation d'espace dans l'arbre des clés.
La gestion interne des colonnes AUTO_INCREMENT. MyISAM va automatiquement modifier cette valeur lors d'une insertion ou d'une modification. La valeur courante d'AUTO_INCREMENT peut être modifiée avec myisamchk. Cela va rendre les colonnes AUTO_INCREMENT plus rapide (au moins 10%) et les anciens nombres ne seront pas réutilisés, comme avec les vieilles tables ISAM. Lorsqu' une clé AUTO_INCREMENT est définie à la fin d'une clé multiple, l'ancien comportement est toujours présent.
  On peut insérer de nouvelles lignes dans une table qui n'a aucun bloc vide dans le fichier de données, en même temps que d'autres threads lisent le fichier de données (insertion simultanée). Un bloc vide peut provenir d'une modification de ligne à format dynamique (les données sont maintenant plus petites). Lorsque tous les blocs vide sont à nouveau utilisés, les insertions suivantes peuvent être simultanées.
Vous pouvez placer les fichiers de données et d'index dans différents dossiers pour obtenir plus de vitesse avec les options de table DATA DIRECTORY et INDEX DIRECTORY, dans la commande CREATE TABLE.
Depuis MySQL version 4.1, chaque colonne de caractères peut avoir un jeu de caractères distinct.
Il y a un indicateur dans le fichier MyISAM qui indique si la table a été correctement fermée. Si mysqld est lancé avec l'option :
--myisam-recover ,
les tables MyISAM vont automatiquement être vérifiées et réparées, si elles n'ont pas été correctement refermées. myisamchk va marquer les tables comme vérifiéz s'il est exécuté avec l'option "--update-state".

"myisamchk --fast" va uniquement vérifier les tables qui n'ont pas cette marque.

"myisamchk -a" stocke les statistiques pour les parties de clés (et non plus pour les clés complètes, comme avec ISAM). myisampack peut compresser des colonnes BLOB et VARCHAR; pack_isam ne le peut pas.

MyISAM supporte aussi les fonctionnalités suivantes, dont MySQL pourra profiter sous peu :

• Support du vrai type VARCHAR; une colonne VARCHAR commence avec une taille, stockée sur 2 octets. Les tables ayant des colonnes VARCHAR peuvent avoir un format de lignes fixe ou dynamique. VARCHAR et CHAR peuvent prendre jusqu'à 64 ko.
• Un index de hashage peut être utilisé avec UNIQUE. Cela vous permettra d'avoir un index UNIQUE sur toute combinaison de colonnes de la table. Vous ne pourrez pas utiliser un index UNIQUE pour une recherche.

Options de démarrage MyISAM

Les options suivantes de mysqld permettent de modifier le comportement des tables MyISAM :
" --myisam-recover=mode"Active le mode de restauration automatique des tables MyISAM corrompues.
"--delay-key-write=ALL" N'écrit pas les buffers de clés entre deux écritures dans une table MyISAM.
Note : Si vous faîtes cela, vous ne devez pas utiliser les tables MyISAM avec d'autres programmes (comme depuis un autre serveur MySQL ou avec myisamchk) lorsque la table est utilisée. Sinon, vous allez obtenir une corruption d'index.
Utiliser "--external-locking" n'aidera pas les tables qui utilisent "--delay-key-write". voir « Options de ligne de commande de mysqld ».

Les variables systèmes suivantes affectent le comportement des tables MyISAM :
"bulk_insert_buffer_size" affecte la taille du cache d'index lors des insertions de masse.
Note : c'est une limite par par thread!
"myisam_max_extra_sort_file_size" est Utilisée pour aider MySQL à décider quand utiliser le cache de clé lent mais sûr.
Note : ce paramètre était donné en megaoctets avant MySQL 4.0.3, et en octets depuis 4.0.3.
"myisam_max_sort_file_size" N'utilise pas la méthode de tri rapide pour créer un index, si un fichier temporaire dépasse cette taille.
Note : ce paramètre était donné en megaoctets avant MySQL 4.0.3, et en octets depuis 4.0.3.
"myisam_sort_buffer_size": La taille du buffer lors de la restauration de table. La restauration automatique est activée si vous lancez mysqld avec l'option --myisam-recover. Dans ce cas, lorsque le serveur ouvre la table MyISAM, il vérifie si la table a été marquée comme crashée ou si le compteur de tables ouvertes n'est pas zéro ou si le serveur utilise --skip-external-locking.
Si une des conditions précédente est vraie, il arrive ceci :
La table est analysée pour rechercher des erreurs. Si le serveur trouve une erreur, il essaie de faire une réparation rapide (avec le tri, sans recréer de données). Si la réparation échoue à cause d'une erreur dans le fichier de données (par exemple, une erreur de clé), le serveur essaie à nouveau, en re-créant le fichier de données. Si la réparation échoue encore, le serveur essaie encore avec une ancienne méthode réparation (écrire les lignes les unes après les autres, sans tri). Cette méthode devrait être capable de réparer tout les types d'erreurs, et elle occupe peu de place sur le disque. Si la restauration n'est toujours pas capable de retrouver toutes les lignes, et que vous n'avez pas spécifié l'option FORCE dans la valeur de l'option --myisam-recover,; la réparation automatique s'annule, avec le message d'erreur suivant :
Error: Couldn't repair table: test.g00pages
Si vous spécifiez la valeur FORCE, une alerte comme celle-ci sera écrite dans les logs :
Warning: Found 344 of 354 rows when repairing ./test/g00pages
Notez que si la valeur de restauration automatique inclut BACKUP, le processus de restauration créera des fichiers avec des noms de la forme tbl_name-datetime.BAK. Vous devriez avoir une tâche régulière avec cron pour supprimer automatiquement ces fichiers dans les bases de données pour nettoyer le volume.

Espace nécessaire pour stocker les index

MySQL supporte plusieurs types d'index, mais le type normal est ISAM ou MyISAM. Ils utilisent un index B-tree, et vous pouvez avoir une approximation de la taille du fichier d'index en faisant la somme de (longueur_clef+4)/0.67 pour toutes les clefs. (Cela est le pire des cas où les clefs sont insérées dans l'ordre et qu'aucune n'est compressée.
Les index de chaînes de caractères sont compressés par rapport aux espaces. Si la première partie de l'index est une chaîne, son préfixe sera aussi compressé. La compression des espaces rend le fichier d'index plus petit que ce que nous avions calculé précédemment si la colonne chaîne possède beaucoup d'espaces invisibles en début et fin de chaîne ou est une colonne VARCHAR qui n'est pas toujours pleinement utilisée. La compression des préfixes est utilisée sur les clefs qui commencent par un chaîne de caractères. La compression des préfixes s'il y a plusieurs chaînes avec des préfixes identiques.

Dans les tables MyISAM, vous pouvez aussi compresser les nombres en spécifiant PACK_KEYS=1 lors de la création de la table. Cela vous aidera lorsque vous aurez plusieurs clefs de types entier qui auront un préfixe identique et que les nombres seront classé par ordre décroissant des grands octets.

Formats de table MyISAM

MyISAM supporte 3 différent types de tables. Deux des trois sont choisis automatiquement selon le type de colonne que vous utilisez. Le troisième, tables compressées, ne peut être crée qu'avec l'outil myisampack. Quand vous créez une table avec CREATE ou en modifiez la structure avec ALTER vous pouvez, pour les tables n'ayant pas de champ BLOB forcer le type de table en DYNAMIC ou FIXED avec l'option ROW_FORMAT=# des tables. Bientôt, vous pourrez compresser/décompresser les tables en spécifiant ROW_FORMAT=compressed | default à ALTER TABLE. Requière usuellement plus d'espace disque que les tables dynamiques.

Caractéristiques des tables statiques (taille fixée)

Requière usuellement plus d'espace disque que les tables dynamiques. Ceci est le format par défaut. Il est utilisé lorsque la table ne contient pas de colonnes de type VARCHAR, BLOB, ou TEXT. Ce format est le plus simple et le plus sûr. C'est aussi le format sur disque le plus rapide. La vitesse vient de la facilité avec laquelle les données peuvent être trouvées sur le disque. La recherche de quelque chose avec un index et un format statique est très simple. Multipliez juste le nombre de lignes par la longueur des lignes.
De même, lors du scannage d'une table, il est très facile de lire un nombre constant d'enregistrements avec chaque lecture du disque. La sécurité est mise en évidence si votre ordinateur crashe lors de l'écriture dans un fichier de taille fixée MyISAM, dans ce cas, myisamchk peur facilement trouver où commence et finit chaque ligne. Il peut donc retrouver tous les enregistrements à part celui dont l'écriture a été interrompue. Notez qu'avec MySQL tous les index peuvent toujours être reconstruits :
Toutes les colonnes CHAR, NUMERIC, et DECIMAL sont complétées par des espaces jusqu'à atteindre la longueur totale de la colonne. Très rapide.
Facile à mettre en cache.
Facile à reconstruire après un crash, car les enregistrements sont localisés dans des positions fixées.
N'a pas à être réorganisé (avec myisamchk) sauf si un grand nombre de lignes est effacé et que vous voulez retourner l'espace libéré au système d'exploitation.
Requière usuellement plus d'espace disque que les tables dynamiques.

Caractéristiques des tables à format de ligne dynamiques

Ce format est utilisé avec les tables qui contiennent des colonnes de type VARCHAR, BLOB ou TEXT, ou si la table a été créée avec l'option "ROW_FORMAT=dynamic".
Ce format est un peu plus complexe, car chaque ligne doit avoir un entête pour indiquer sa longueur. Une ligne peut aussi être répartie sur plusieurs blocs, lorsqu'elle est agrandie lors d'une modification. Vous pouvez utiliser la commande SQL OPTIMIZE table ou Shell myisamchk pour défragmenter une table. Si vous avez des données statiques que vous modifiez souvent dans la même table, avec des colonnes VARCHAR ou BLOB, il peut être une bonne idée de placer des colonnes dans une autre table, pour éviter la fragmentation@ :
Toutes les colonnes de type chaîne sont dynamiques (hormis celle qui sont de taille inférieure à 4).
Chaque ligne est précédée d'un octet qui indique quelles sont les lignes vides ('', bit à 1) et celle qui le ne sont pas (bit à 0). Une colonne vide n'est pas la même choses qu'une colonne qui contient NULL. Si une colonne a une taille de zéro après avoir supprimé les espaces finaux, ou un nombre a une valeur de zéro, il est marqué dans cet octet, et la colonne sera ignorée sur le disque. Les chaînes non vides sont sauvées avec un octet de plus pour y stocker la taille.
Ce format prend généralement moins de place que des tables à format fixe.
Chaque ligne consomme autant d'espace que nécessaire. Si une ligne devient trop grande, elle sera coupée en blocs et écrites dans le fichier de données. Cela engendre la fragmentation du fichier de données. Par exemple, si vous modifiez une ligne avec des informations qui excèdent la capacité courante de la ligne, la ligne sera fragmentée. Dans ce cas, vous pouvez avoir à exécuter la commande myisamchk -r de temps en temps pour améliorer les performances. Utilisez myisamchk -ei tbl_name pour obtenir des statistiques.
Ce format de table n'est pas toujours facile à reconstituer après un crash, car une ligne peut être fragmentée en de nombreux blocs, et un fragment peut manquer.
La taille d'une ligne de format variable se calcule avec :

      3
      + (nombre de colonnes + 7) / 8
      + (nombre de colonnes de tailles chars)
      + taille compactée des colonnes numériques
      + taille des chaînes
      + (nombre de colonne de valeur NULL + 7) / 8

Il y a un aussi un supplément de 6 octets pour chaque lien. Une ligne de format dynamique utilise un lien à chaque fois qu'une modification cause un agrandissement de la ligne. Chaque nouveau bloc lié fait au moins 20 octets, pour que le prochain agrandissement utilise aussi ce bloc. Si ce n'est pas le cas, un nouveau bloc sera lié, avec un autre coût de 6 octets. Vous pouvez vérifier le nombre de liens dans une table avec la commande myisamchk -ed. Tous les liens sont supprimés avec la commande myisamchk -r.

Caractéristiques des tables compressées

C'est un type en lecture seule qui est généré avec l'outil optionnel myisampack. Toutes les distributions MySQL depuis la version 3.23.19 incluent myisampack par défaut (C'est le moment où MySQL a été mis sous GPL). Pour les versions plus anciennes myisampack n'était inclus qu'avec les licences ou contrats, mais le serveur peut toujours lire les tables compressées myisampack. Les tables compressées peuvent être décompressées avec myisamchk. Pour le moteur de stockage ISAM, les tables compressées peuvent être compressées avec pack_isam et décompressées avec isamchk. Les tables compressées ont les avantages suivants :
Les tables compressées prennent très peu d'espace disque. Cela réduit l'espace requis, ce qui est fort utile lors de l'utilisation de petits disques (comme les CD-ROM).
Chaque ligne est compressée séparemment (optimisation des accès). L'entête d'un enregistrement est fixé (1-3 octets) selon le plus grand enregistrement dans la table. Chaque colonne est compressée différemment. Quelques un des types de compressions sont :
Compression des espaces en suffixe.
Compression des espaces en préfixe.
Les nombres avec la valeur 0 sont stockés en utilisant 1 octet.
Si les valeurs dans une colonne de type entier ont un petit intervalle, la colonne est stockée en utilisant le type le plus petit possible. Par exemple, une colonne BIGINT (8 octets) peut être stocké en tant que colonne TINYINT (1 octet) si toutes les valeurs sont entre 0 et 255.
Si une colonne n'a qu'un petit éventail de valeurs, son type est changé en ENUM.
Une colonne peut utiliser une combinaison des compressions précédentes.
Peut gérer les enregistrements de tailles fixes ou variables.

Problèmes avec les tables MyISAM

Tables MyISAM corrompues
Même si le format des tables MyISAM est relativement sûr (tous les changements sont écrits avant que la requête SQL ne retourne quoi que ce soit), vous pouvez quand même vous trouver face à des tables corrompues si l'une des choses suivantes arrive :
Le processus mysqld est tué au milieu d'une écriture.
Arrêt inattendu de la machine (par exemple, coupure de courant).
Un problème matériel.
Vous utilisez un programme externe (comme myisamchk) sur une table active.
Un bogue logiciel dans le code de MySQL ou de MyISAM.
Les symptômes typiques d'une table corrompue sont :
Vous obtenez l'erreur Incorrect key file for table: '...'. Try to repair it pendant la sélection de données à partir de cette table.
Les requêtes ne trouvent pas de lignes dans la table ou retournent des données incomplètes. Vous pouvez réparer une table corrompue avec REPAIR TABLE. Vous pouvez aussi réparer une table, lorsque mysqld ne fonctionne pas, avec la commande myisamchk. Lorsque mysqld est arrêté, vous pouvez vérifier une table avec la commande myisamchk. Voir  « Syntaxe de CHECK TABLE », « Syntaxe de REPAIR TABLE » et « Syntaxe de l'utilitaire myisamchk ».
Si vos tables sont souvent corrompues, vous devez essayez de trouver d'où vient le problème ! Dans ce cas, la chose la plus importante à savoir est, si la table est corrompue, si le serveur mysqld s'est interrompu. (cela peut être facilement vérifié en regardant s'il y a une entrée récente restarted mysqld dans le fichier d'erreurs de mysqld). Si ce n'est pas le cas, vous devez essayer d'effectuer une série de tests.

Des clients utilisent la table, ou bien elle n'a pas été fermée correctement

Chaque fichier MyISAM .MYI possède un compteur dans l'entête qui peut être utilisé pour savoir si une table a été fermée Proprement. Si vous obtenez l'avertissement suivant de la part de CHECK TABLE ou myisamchk :
# clients is using or hasn't closed the table properly
cela signifie que le compteur n'est plus synchrone. Cela ne signifie Pas que la table est corrompue, mais que vous devez au moins effectuer une vérification sur la table pour vous assurer de son bon fonctionnement.
Le compteur fonctionne de la fa¸on suivante :
La première fois qu'une table est mise à jour dans MySQL, un compteur dans l'entête du fichier est incrémenté.
Le compteur ne change pas pour les mises à jours suivantes.
Lors de la fermeture de la dernière instance d'une table (à cause d'un FLUSH ou qu'il n'y a plus de place dans le cache de la table) le compteur est décrémenté si la table n'a pas été mise à jour.
Lorsque vous réparez la table ou vérifiez quel est en bon état, le compteur est remis à zéro.
Pour éviter les problèmes d'interactions avec d'autres processus qui peuvent vérifier la table, le compteur n'est pas décrémenté à la fermeture si sa valeur était zéro.
En d'autres termes, les seuls moyens d'obtenir ce genre d'erreur sont :
Les tables MyISAM sont copiés sans LOCK et FLUSH TABLES.
MySQL a planté entre une mise à jour et la fermeture finale. (Notez que la table peut encore être bonne, vu que MySQL écrit toujours pour tout entre deux requêtes.)
quelqu'un a exécuté myisamchk --recover ou myisamchk --update-state sur une table qui était utilisée par mysqld.
Plusieurs serveurs mysqld utilisent la table et l'un d'eux a exécuté dessus un REPAIR ou un CHECK pendant qu'elle était utilisée par un autre serveur. Dans ce cas là, l'utilisation de CHECK n'est pas très grave (même si vous obtiendrez des avertissements sur les autres serveurs), mais REPAIR doit être évitée vu qu'elle remplace actuellement le fichier de données part un nouveau, ce qui n'est pas signalé aux autres serveurs. En général, c'est une mauvaise idée que de partager un dossier de données avec plusieurs serveurs.
Peut gérer les enregistrements de tailles fixes ou variables.