10.18 Types de tables MySQL

Les types de table ont été introduit à partir de MySQL 3.23!

Lors de la création d'une table, vous pouvez préciser à MySQL quel type de table utiliser. MySQL va, dans tous les cas, créer un fichier .frm pour y noter la structure de la table, et les définitions des colonnes. Suivant le type de table, l'index et les données seront enregistrées dans d'autres fichiers.

Vous pouvez convertir des tables d'un type à l'autre avec la commande ALTER TABLE. Reportez vous à la section ALTER TABLE.

  • ISAM Ce type est le type original de pour les tables MySQL. Il utilise un index B-tree. L'index est enregistré dans un fichier avec l'extension.ISM et les données sont enregistrées dans un fichier avec l'extension .ISD. Vous pouvez vérifier et réparer les tables ISAM avec l'utilitaire isamchk. Reportez vous à la section 13.4 Utiliser isamchk pour réparer une table. Les tables ISAM ne are sont pas portables d'une plate forme à l'autre. Les fichiers ISAM ont les caractéristiques suivantes :
    • Clés compressées et de longueur fixe
    • Enregistrement de longueur fixe ou variable
    • 16 clés avec 16 parties de clé par clé
    • Longueur maximale d'une clé : 256 (par défaut)
    • Les informations sont enregistrées dans un format propre à la machine. Rapide mais pas portable.
  • MyISAM est le type de table par défaut à partir de MySQL 3.23. Il est basé sur le code de ISAM et dispose de nombreuses amélioration : L'index est desormais enregistré dans le fichier .MYI et les données sont enregistrées dans le fichier .MYD. Vous pouvez vérifier et réparer des fichiers MyISAM avec l'utilitaire myisamchk. 13.4 Utiliser isamchk pour réparer une table. Les caractéristiques suivantes sont nouvelles:
    • Support de gros fichiers (63 bits) sur les OS qui le supporte.
    • Les données sont toutes sauvegardées avec le bit de poids faible en premier. MyISAM est donc indépendant de la machine et de l'OS.
    • Les clés sont enregistrées avec le bit de poids fort en premier, pour améliorer la compression.
    • Gestion interne des colonnes AUTO_INCREMENT. MyISAM va automatiquement mettre à jour ces colonnes lors des commandes INSERT/UPDATE. Les commandes AUTO_INCREMENT peuvent être modifiée avec myisamchk. Tout ceci accélère les traitements sur les colonnes AUTO_INCREMENT et les anciens numéros ne seront par réutilisé.
    • Taille maximale des clés : 500 par défaut. Dans le cas ou les clés dépassent la taille de 250, un bloc de clé plus grand est généré et utilisé.
    • Nombre maximum de clés par table est porté à 32 par défaut. Il peut même être étendu à 64 sans avoir à recompiler myisamchk.
    • Un flag dans MyISAM indique si le fichier a bien été fermé ou non. Cela permettra bientôt de faire des réparation automatiques.
    • myisamchk marque les tables.. myisamchk --fast ne vérifiera que les talbes qui ne sont pas marquées.
    • myisamchk -a crée des statistiques sur les parties de clés (et non plus la totalité des clés, comme avec NISAM).
    • Les enregistrements de taille dynamiques sont bien moins fragmentés lors de modifications et effacements. Les blocs effacés et adjacents sont combinés pour agrandir les enregistrements.
    • myisampack (appelé pack_isam sous NISAM) comrpess les colonnes BLOB et VARCHAR

MyISAM supporte les caractéristiques suivantes, que MySQL sera bientôt capable d'exploiter :

    • Les colonnes BLOB et TEXT peuvent être indexées/
    • Support d'un véritable type VARCHAR : Les colonnes de type VARCHAR commence par une longueur enregistré sur 2octets.
    • Les tables avec des colonnes de type VARCHAR peut avoir des enregistrements de longueur variable ou fixes.
    • VARCHAR et CHAR peuvent atteindre la taille de 64Ko. Tous les segments de clés on leur propres définitions. Cela permettra à MySQL d'utiliser des langages différents pour chaque colonne.
    • NULL sont autorisés dans les colonnes indexées. Cela prend de 0 à 1 octet par clé.
    • Un index hashé peut être utilisé comme UNIQUE; Cela va permettre d'avoir l'attribut UNIQUE pour n'importe quelle combinaison de colonne d'une table. (On ne pourra pas faire de recherche sur ces index).
  • HEAP Ces tables utilisent un index hashé est sont enregistrées en mémoire. Cela les rend beaucoup plus rapide, mais si MySQL crashe, toutes les données seront perdues. HEAP est un bon type pour les tables temporaire ! 

CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down FROM log_table GROUP BY ip;

SELECT COUNT(ip),AVG(down) from test;

drop table test;

Voici quelques considérations sur les tables HEAP:

    • Vous devriez toujours spécifier le nombre maximal de ligne (MAX_ROWS) dans la commande CREATE pour s'assurer que la table ne prend pas toute la mémoire.
    • Les index ne seront utilisé qu'avec les opérateurs = et <=> (mais TRES rapide )
    • HEAP est une table à enregistrement de taille fixe.
    • HEAP n'accepte pas les types BLOB/TEXT.
    • HEAP n'accepte pas les colonnes AUTO_INCREMENT.
    • HEAP n'accepte pas les index sur les colonnes avec des valeurs NULL.
    • Vous pouvez avoir des clés non unique dans une table HEAP (ce qui n'est pas le cas des tables hashées normales)
    • Les tables HEAP sont partagées entre tous les clients (comme n'importe quelle table)
    • Les données des tables HEAP sont allouées par petits blocs. Ces tables sont purement dynamiques en insertion. Pas de dépassement de capacité, ni d'espace de clé supplémentaires à prévoir. Les lignes effacées sont placées dans une liste, et seront réutilisées à la prochaine insertion.
    • Pour libérer de la mémoire, vous devez exécuter la commande DELETE FROM heap_table ou DROP TABLE heap_table.
    • Pour s'assurer que vous ne faite rien de grave, la taille des tables HEAP est limitée à max_heap_table_size.