Chapitre 14 Moteurs de tables MySQL et types de table

Table des matières

14.1 Le moteur de tables MyISAM
14.1.1 Options de démarrage MyISAM
14.1.2 Espace nécessaire pour stocker les index
14.1.3 Formats de table MyISAM
14.1.4 Problèmes avec les tables MyISAM
14.2 Tables assemblées MERGE
14.2.1 Problèmes avec les tables MERGE
14.3 Le moteur de table MEMORY (HEAP)
14.4 Tables BDB ou BerkeleyDB
14.4.1 Systèmes d'exploitation supportés par BDB
14.4.2 Installation de BDB
14.4.3 Options de démarrage BDB
14.4.4 Caractéristiques des tables BDB
14.4.5 Ce que nous devons corriger dans BDB dans un futur proche :
14.4.6 Restrictions avec les tables BDB
14.4.7 Erreurs pouvant survenir lors de l'utilisation des tables BDB
14.5 Le moteur de table EXAMPLE
14.6 Le moteur de table FEDERATED
14.6.1 Installation du moteur de table FEDERATED
14.6.2 Description du moteur de stockage FEDERATED
14.6.3 Comment utiliser les tables FEDERATED
14.6.4 Limitations du moteur de stockage FEDERATED
14.7 Le moteur de table ARCHIVE
14.8 Le moteur CSV
14.9 Tables ISAM

MySQL supporte plusieurs moteurs de stockage, qui gère différents types de tables. Les moteurs de tables MySQL peuvent être transactionnels ou non-transactionnels.

Ce chapitre décrit les différents moteurs de tables MySQL, hormis InnoDB, qui est présenté dans le chapitre Chapitre 15, Le moteur de tables InnoDB et NDBCluster qui est présenté dans le chapitre Chapitre 16, Introduction à MySQL Cluster.

Lorsque vous créez une table, vous pouvez indiquer à MySQL le type de table avec la clause ENGINE ou TYPE lors de la commande de CREATE TABLE :

CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;

ENGINE est le terme recommandé, mais il ne peut pas être utilisé avant MySQL 4.0.18. TYPE est disponible depuis MySQL 3.23.0, la première version de MySQL qui dispose de plusieurs moteurs de tables.

Si vous omettez l'option ENGINE ou TYPE, le type de table par défaut sera utilisé. C'est généralement MyISAM. Cela peut être changé en modifiant la variable système table_type.

Pour convertir une table d'un type à l'autre, utilisez la commande ALTER TABLE, pour indiquer le nouveau type :

ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;

See Section 13.2.5, « Syntaxe de CREATE TABLE » and Section 13.2.2, « Syntaxe de ALTER TABLE ».

Si vous essayez d'utiliser un moteur de stockage qui n'est pas compilé ou qui est désactivé, MySQL créera une table de type MyISAM. Ce comportement est pratique pour copier des tables entre serveurs MySQL qui supportent différents moteurs. Par exemple, dans une architecture de réplication, votre serveur maître supporte des tables transactionnelles, mais l'esclave n'utilise que des tables non-transactionnelles, pour améliorer la vitesse.

Cette substitution automatique par une table de type MyISAM pour un type de moteur indisponible peut être gênant pour un nouvel utilisateur MySQL. En MySQL 4.1 et plus récent, une alerte est générée lorsque le type de la table est modifié.

MySQL crée toujours un fichier .frm pour stocker le type de la table et les informations de définition. Les données et les index de la table peuvent être stockés ailleurs, en fonction du type de tables. Le serveur crée le fichier .frm par dessus le moteur de stockage. Les moteurs peuvent créer des fichiers supplémentaires, en fonction de leurs besoins.

Les avantages des tables transactionnelles (TST) sont :

Notez que pour utiliser le moteur InnoDB en MySQL 3.23, vous devez configurer au moins l'option de démarrage innodb_data_file_path. En 4.0 et plus récent, InnoDB utilise les valeurs par défaut de la configuration, si vous ne les spécifiez pas. See Section 15.4, « Configuration InnoDB ».

Avantages des tables non-transactionnelles (NTST) :

Vous pouvez combiner les tables TST et NTST dans la même requête pour obtenir le meilleur des deux types. Cependant, dans une transaction sans auto-validation, les modifications à une table non-transactionnelles seront toujours immédiatement enregistrés, et ne pourront pas être annulé.

14.1 Le moteur de tables MyISAM

MyISAM est le type par défaut de table en MySQL 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, indiquez le avec l'option ENGINE ou TYPE lors de la création de la table :

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

Normalement, les options ENGINE et TYPE sont inutiles : MyISAM est le type par défaut de table en MySQL, à moins d'avoir été spécifié autrement.

Vous pouvez vérifier ou réparer une table MyISAM avec l'utilitaire myisamchk. See Section 5.7.3.7, « Utiliser myisamchk pour restaurer une table ». Vous pouvez aussi compresser les tables MyISAM avec l'utilitaire myisampack pour réduire leur taille sur le disque. See Section 8.2, « myisampack, le générateur de tables MySQL compressées en lecture seule ».

Voici 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 adjacent 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 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. Notez que lorsque une clé AUTO_INCREMENT est définie à la fin d'une clé multiple, l'ancien comportement est toujours présent.

  • Vous pouvez 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. See Section 13.2.5, « Syntaxe de 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.

14.1.1 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.

See Section 5.2.1, « Options de ligne de commande de mysqld ».

Les variables systèmes suivantes affectent le comportement des tables MyISAM :

  • bulk_insert_buffer_size

    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

    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épasserait 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.

See Section 5.2.3, « Variables serveur système ».

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.

14.1.2 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.

14.1.3 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. See Section 13.2.5, « Syntaxe de CREATE TABLE ».

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

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.

14.1.3.2 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.

14.1.3.3 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.

14.1.4 Problèmes avec les tables MyISAM

Le format de fichier que MySQL utilise pour stocker les données a été testé à l'extrême, mais il y a toujours des circonstances qui peuvent corrompre les tables d'une base de données.

14.1.4.1 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. Voyez la section Section 13.5.2.3, « Syntaxe de CHECK TABLE », Section 13.5.2.6, « Syntaxe de REPAIR TABLE » et Section 5.7.3.1, « 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. Voyez Section A.4.2, « Que faire si MySQL plante constamment ? » et Section D.1.6, « Faire une batterie de tests lorsque vous faites face à un problème de table corrompue ».

14.1.4.2 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. Voyez la section Section 5.10, « Faire fonctionner plusieurs serveurs MySQL sur la même machine » pour plus de détails.

14.2 Tables assemblées MERGE

Les tables MERGE ont été ajoutée en MySQL version 3.23.25. Ce type de table est aussi connu sous le nom de MRG_MyISAM. Le code raisonnablement stable.

Une table MERGE est un groupe de tables MyISAM identiques qui sont utilisées comme une seule. ``Identique'' signifie que toutes les tables ont la même structure de colonnes et d'index. Vous ne pouvez pas regrouper des tables qui ont des index dans un ordre différent. Toutefois, une ou plusieurs tables peuvent être compressées avec myisampack. See Section 8.2, « myisampack, le générateur de tables MySQL compressées en lecture seule ».

Lorsque vous créez une table MERGE, MySQL crée deux fichiers sur le disque. Les fichiers ont pour nom celui de la table, et ont un extension qui indique le type de fichiers. Le fichier .frm stocke la définition de la table, et le fichier .MRG contient les noms des tables qui doivent être utilisées. Originalement, toutes les tables utilisées dans la même table MERGE devaient être dans la même base que la table MERGE . Cette restriction a été levée en MySQL 4.1.1.

Pour le moment, vous avez simplement besoin des droits de SELECT, UPDATE et DELETE sur les tables que vous avez rassemblé dans la table MERGE.

L'exemple suivant vous montre comme utiliser les tables MERGE :

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Notez que la colonne a est indexée dans la table MERGE, mais elle n'est pas déclarée comme PRIMARY KEY comme elle peut l'être dans les tables MyISAM sous-jacente. C'est nécessaire car une table MERGE ne peut pas assurer l'unicité de valeurs à travers les tables.

Après la création de la table MERGE, vous pouvez faire des commandes comme :

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

Pour redéfinir une table MERGE avec un autre groupe de tables MyISAM, vous pouvez faire ceci :

Notez que vous pouvez aussi manipuler le fichier .MRG directement, à l'extérieur du serveur MySQL :

shell> cd /mysql-data-directory/current-database
shell> ls -1 t1 t2 > total.MRG
shell> mysqladmin flush-tables
  • Effacez la table avec la commande DROP, puis recréez la.

  • Utilisez ALTER TABLE tbl_name UNION=(...) pour redéfinir les tables regroupées.

  • Modifiez le fichier .MRG et utilisez la commande FLUSH TABLE sur la table MERGE et toutes les tables sous-jacentes, pour forcer le gestionnaire à relire la définition.

Les tables MERGE peuvent vous aider dans les situations suivantes :

  • Gérer facilement un jeu de tables d'historique. Par exemple, vous pourriez placer les données de chaque mois dans un fichier séparé, en compresser certains avec myisampack puis créer une table MERGE pour les utiliser.

  • Vous donner plus de vitesse. Vous pouvez répartir les grandes tables en lecture seule dans différentes parties du disque. Une table MERGE bâtie de cette fa¸on peut être plus rapide qu'une grosse table (vous pouvez aussi et bien sûr, utiliser un système RAID pour arriver aux mêmes avantages).

  • Effectuer des recherches plus efficaces. Si vous savez exactement ce que vous recherchez, vous pouvez faire des recherches dans une seule des tables individuelles pour les recherches, et utiliser la table MERGE pour les autres opérations. Vous pouvez même avoir de nombreuses tables MERGE actives, qui partagent les même fichiers.

  • Des réparations plus efficaces. Il est plus facile de réparer les fichiers individuels qui sont rassemblés dans une table MERGE que de réparer une grande table.

  • Fusion instantanée de plusieurs tables en une seule. Une table MERGE utilise les index des tables individuelles. Il n'y a pas besoin de gérer un seul index. Cela rend les tables MERGE très rapides à faire ou défaire. Notez que vous devez spécifier les définitions de clés lorsque vous créez la table MERGE!

  • Si vous avez un jeu de table que vous rassemblez dans une grande à la demande ou pour un traitement batch, vous devriez utiliser une table MERGE. C'est bien plus rapide, et cela va vous faire économiser de l'espace disque.

  • Contourner les limitations de taille du système d'exploitation.

  • Vous pouvez créer un alias ou un synonyme pour une table, en utilisant simplement MERGE sur une seule. Il n'y a pas de coûts particulier en performance (hormis quelques appels de fonctions indirects, et des memcpy() avant chaque lecture).

Les inconvénients des tables de type MERGE sont :

  • Vous devez utiliser des tables MyISAM identiques pour faire une table MERGE.

  • MERGE utilise plus de pointeurs de fichiers. Si vous utilisez une table MERGE qui couvre 10 tables et que 10 utilisateurs l'utilisent, vous consommez 10*10 + 10 pointeurs de fichiers (10 fichiers de données, et 10 utilisateurs avec 10 fichiers d'index).

  • Les lectures de clés sont plus lentes. Lorsque vous faîtes une lecture sur une clé, le gestionnaire MERGE doit faire une lecture dans tous les fichiers d'index des tables sous-jacentes, pour vérifier lequel est le plus proche de la valeur recherchée. Si vous faîtes une lecture du type "lit le suivant", le gestionnaire de table assemblée doit rechercher dans tous les buffers de clés pour la trouver. Uniquement lorsqu'un buffer clé est complet, doit il lire le prochain bloc. Cela rend l'accès aux clés MERGE bien plus lent que les recherches eq_ref, mais pas aussi lent que les recherches de type ref. Voyez la section Section 7.2.1, « Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT) » pour plus d'informations sur eq_ref et ref.

14.2.1 Problèmes avec les tables MERGE

Voici une liste des problèmes connus avec les tables de type MERGE :

  • Si vous utilisez la commande ALTER TABLE pour modifier une table de type MERGE en un autre type de table, la liste des tables sous-jacentes sera perdue. Au lieu de cela, les lignes des tables seront copiées dans la nouvelle table, puis on lui assignera le nouveau type.

  • Avant MySQL 4.1.1, toutes les tables sous-jacentes et la table MERGE devaient être dans la même base de données.

  • REPLACE ne fonctionne pas.

  • Vous ne pouvez pas utiliser DROP TABLE, ALTER TABLE, DELETE FROM dans clause WHERE, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, ou ANALYZE TABLE sur l'une des tables qui est dans une table MERGE ``ouverte''. Si vous faîtes cela, la table MERGE va utiliser la table originale, et vous obtiendrez des résultats étranges. Le plus simple est d'utiliser la commande FLUSH TABLES pour s'assurer qu'aucune table MERGE ne reste ``ouverte.''

  • Une table MERGE peut pas pas supporter de contrainte de type UNIQUE sur toute la table. Lorsque vous faites une insertion, les données vont dans la première ou la dernière table (suivant la méthode d'insertion INSERT_METHOD=xxx) et cette table MyISAM s'assure que les données sont uniques, mais rien n'est fait pour vérifier l'unicité auprès des autres tables MyISAM tables.

  • Avant MySQL 3.23.49, DELETE FROM merge_table utilisé sans clause WHERE va uniquement détruire la table assemblée, mais ne va pas toucher les tables sous-jacentes. En fait, le fichier .MRG est effacé, mais pas les tables.

  • RENAME TABLE utilisé sur une table de type MERGE peut corrompre la table. Cela sera corrigé en MySQL 4.1.x.

  • La création d'une table de type MERGE ne vérifie pas si les tables sous-jacentes sont compatibles. Si vous utilisez une table MERGE de cette fa¸on, vous devriez rencontrer des problèmes très étranges.

  • L'ordre des index dans la table MERGE et ses tables sous-jacentes doit être le même. Si vous utilisez la commande ALTER TABLE pour ajouter un index de type UNIQUE à une table qui est utilisée dans une table assemblée MERGE, puis que vous utilisez ALTER TABLE pour ajouter un index normal dans la table MERGE, l'ordre des clé sera différent suivant les tables, si jamais il y avait une vieille clé non unique. Ceci est dû au fait que ALTER TABLE place les clés UNIQUE avant les clés normales, pour être capable de détecter les doublons le plus tôt possible.

  • DROP TABLE sur une table qui est utilisé par une table MERGE ne fonctionne pas sous Windows car le gestionnaire de MERGE garde les connexions vers les tables cachées sous la couche MySQL. Comme Windows ne vous permet pas d'effacer une table qui est ouverte, vous devez d'abord fermer toute les tables MERGE (avec la commande FLUSH TABLES) ou effacer la table MERGE avant de pouvoir effacer la table désirée. Nous allons corriger lorsque nous introduirons les vues. VIEWs.

14.3 Le moteur de table MEMORY (HEAP)

Le moteur de stockage MEMORY crée des tables dont le contenu est stocké en mémoire. Avant MySQL 4.1, les tables MEMORY étaient appelées des tables HEAP. Depuis 4.1, HEAP est un synonyme de MEMORY, et MEMORY est le terme recommandé.

Chaque table MEMORY est associée à un fichier sur le disque. Le fichier a le nom de la table, et pour extension .frm pour indiquer la définition de la table.

Pour spécifier explicitement que vous voulez une table MEMORY, indiquez l'option ENGINE ou TYPE :

CREATE TABLE t (i INT) ENGINE = MEMORY;
CREATE TABLE t (i INT) TYPE = HEAP;

Les tables HEAP utilisent un index de hachage, et sont stockées en mémoire. Elles sont très rapides, mais si MySQL plante, vous perdrez toutes vos données. La table continuera d'exister car leur définition est stockée sur le serveur, dans le fichier .frm mais le contenu sera perdu au redémarrage du serveur. Les tables HEAP sont très pratiques pour être des tables temporaires.

Voici un exemple qui montre comment créer, utiliser et détruire une table MEMORY :

mysql> CREATE TABLE test TYPE=MEMORY
    ->     SELECT ip,SUM(downloads) AS down
    ->     FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

Les tables MEMORY ont les caractéristiques suivantes :

  • Les données pour les tables HEAP sont alloués par petits blocs. Les tables sont 100% dynamiques (en insertion). Aucune zone de débordement ou d'espace de clé supplémentaire n'est nécessaire. Les lignes effacées sont placées dans une liste, prêtes à être réutilisées.

  • Les tables MEMORY peuvent avoir jusqu'à 32 index par table, 16 colonnes par index, et un maximum de 500 pour la tailles des clés.

  • Avant MySQL 4.1, le moteur MEMORY n'implémentait que des index hash. Depuis MySQL 4.1, Les index hash sont le type par défaut, mais vous pouvez spécifier explicitement que l'index MEMORY doit être de type HASH ou BTREE en ajoutant la clause USING :

    CREATE TABLE lookup
        (id INT, INDEX USING HASH (id))
        ENGINE = MEMORY;
    CREATE TABLE lookup
        (id INT, INDEX USING BTREE (id))
        ENGINE = MEMORY;
    

    Les caractéristiques générales des hash et B-tree sont décrites dans la section Section 7.4.5, « Comment MySQL utilise les index ».

  • Vous pouvez avoir des clés non-uniques dans une table MEMORY. (C'est une fonctionnalité rare pour les index hash).

  • Si vous avez un index hash sur une table HEAP avec un haut degré de duplication (de nombreux valeurs d'index contiennent la même valeur), les modifications dans cette table peuvent affecter les valeurs des clés et toutes les suppressions seront plus lentes. Le facteur de ralentissement est proportionnel au degré de duplication (ou inversement proportionnel à la cardinalité). Depuis la version 4.1, MySQL supporte les index BTREE les tables HEAP, que vous pouvez utiliser pour éviter le problème.

  • Les tables HEAP utilisent un format de ligne fixe.

  • HEAP ne supporte pas les colonnes de type BLOB/TEXT.

  • HEAP ne supporte pas les colonnes de type AUTO_INCREMENT.

  • Avant MySQL 4.0.2, HEAP ne supportait les index sur les valeurs NULL.

  • Les tables HEAP sont partagées entre tous les clients (comme une autre table).

  • La caractéristique des tables MEMORY qui fait que les tables sont stockées en mémoire est partagée avec les tables internes que le serveur crée à la volée lors du traitement des requêtes. Cependant, les tables internes ont aussi la capacité d'être converties en tables disques automatiquement, si elles deviennent trop grandes. La taille limite est déterminée par la valeur de tmp_table_size.

    Les tables MEMORY ne peuvent pas être converties en tables disques. Pour vous assurer que vous ne faîtes rien de dangereux pour le serveur, vous pouvez utiliser la variable système max_heap_table_size pour imposer une taille maximale aux tables MEMORY. Pour des tables individuelles, vous pouvez utiliser l'option de table MAX_ROWS avec la commande CREATE TABLE.

  • Vous avez besoin de suffisamment de mémoire pour accepter toutes les tables HEAP que vous allez utiliser simultanément.

  • Pour libérer de la mémoire, vous devez exécuter la commande DELETE FROM heap_table, TRUNCATE heap_table ou DROP TABLE heap_table.

  • Si vous voulez remplir les tables MEMORY au lancement du serveur MySQL, vous pouvez utiliser l'option --init-file. Par exemple, vous pouvez mettre les commandes telles que INSERT INTO ... SELECT et LOAD DATA INFILE pour lire des données dans une source de données persistante. See Section 5.2.1, « Options de ligne de commande de mysqld ».

  • Si vous utilisez la réplication, les tables MEMORY du maître se vident à l'extinction. Rependant, un esclave peut ne pas s'apercevoir que ces tables ont été vidées, et il risque de retourner des données invalides si vous l'utilisez. Depuis MySQL 4.0.18, lorsqu'une table MEMORY est utilisée sur le maître, il émet une commande DELETE FROM automatiquement, pour synchroniser l'esclave et le maître. Notez que même avec cette stratégie, l'esclave aura des données obsolètes entre le moment où le maître s'éteint et celui où il est redémarré. Mais si vous utilisez l'option --init-file pour remplir la table MEMORY au lancement du serveur, elle s'assurera que cette intervalle est bien null.

  • La mémoire nécessaire pour les tables HEAP sont :

    SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
    + ALIGN(length_of_row+1, sizeof(char*))
    

    ALIGN() représente un facteur d'arrondi, car la taille de la ligne doit faire exactement un multiple de la taille du pointeur de char. sizeof(char*) vaut 4 sur les machines 32 bits et 8 sur une machine 64 bits.

14.4 Tables BDB ou BerkeleyDB

Sleepycat Software fournit à MySQL le moteur de stockage transactionnel Berkeley DB. Ce moteur est généralement appelée BDB. Le support des tables BDB est inclus par la distribution des sources de MySQL à partir de la version 3.23.34 et est activé dans le binaire MySQL-Max.

En utilisant les tables DBD, vos tables ont plus de chances de survivre aux crashes, et vous avez accès à COMMIT et ROLLBACK avec les transactions. La distribution des sources de MySQL fournit une distribution corrigée de BDB pour lui permettre de fonctionner d'une fa¸on plus souple avec MySQL. Vous pouvez utiliser une version non-patchée de BDB avec MySQL.

Nous travaillons chez MySQL AB en coopération étroite avec Sleepycat pour garantir une bonne qualité d'interface MySQL/BDB. Même si Berkeley DB est très surveillée et fiable, l'interface MySQL est considérée de qualité Gamma. Nous y travaillons et l'optimisons.

Lorsqu'ils utilisent les tables BDB, nous aiderons nos utilisateurs à trouver les problèmes et à créer des batteries de tests reproductibles pour tout problème ayant trait aux tables BDB. De tels tests seront aussi envoyés à Sleepycat qui nous aidera aussi à trouver et résoudre les problèmes. Vu qu'il s'agit d'une collaboration à deux niveaux, les problèmes concernant les tables BDB prendront un peu plus de temps à être résolus en comparaison avec les autres gestionnaires de tables. Toutefois, étant donné que le code de BerkeleyDB a lui-même été utilisé par plusieurs autres applications à part MySQL, nous n'envisageons pas de rencontrer de gros problèmes avec.

Pour des informations générales sur Berkeley DB, visitez le site de Sleepycat Web, http://www.sleepycat.com/.

14.4.1 Systèmes d'exploitation supportés par BDB

Actuellement, nous savons que le gestionnaire BDB fonctionne avec les systèmes d'exploitation suivants :

  • Linux 2.x Intel

  • Sun Solaris (SPARC et x86)

  • FreeBSD 4.x/5.x (x86, sparc64)

  • IBM AIX 4.3.x

  • SCO OpenServer

  • SCO UnixWare 7.1.x

Il ne fonctionne pas sur les systèmes d'exploitations suivants :

  • Linux 2.x Alpha

  • Linux 2.x AMD64

  • Linux 2.x IA64

  • Linux 2.x s390

  • Max OS X

Note : La liste ci-dessus n'est pas complète; nous la mettrons à jour au fur et à mesure que nous recevrons des informations à ce propos.

Si, après avoir compilé MySQL avec le support des tables BDB tables, obtenez l'erreur suivante dans le fichier de logs quand vous démarrez mysqld :

bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init databases

Cela signifie que les tables BDB ne sont pas supportées par votre architecture. Dans ce cas, vous devez recompiler MySQL sans le support des tables BDB.

14.4.2 Installation de BDB

Si vous avez téléchargé une version binaire de MySQL qui inclut le support de BerkeleyDB, vous n'avez qu'à suivre les instructions classiques (MySQL-Max inclut le support BDB).

Si vous compilez MySQL depuis les sources, vous pouvez activer le support BDB en ajoutant au script configure l'option --with-berkeley-db en plus des autres options que vous avez. Téléchargez la distribution de MySQL 3.23.34 ou plus récent, allez dans la racine des sources, et tapez :

shell> ./configure --with-berkeley-db [other-options]

Pour plus d'informations, voyez Section 2.3, « Installer MySQL sur d'autres systèmes type Linux », Section 5.1.2, « mysqld-max, la version étendue du serveur mysqld » et See Section 2.4, « Installation de MySQL avec une distribution source ».

14.4.3 Options de démarrage BDB

Les options suivantes de mysqld peuvent être utilisées pour modifier le comportement des tables BDB :

  • --bdb-home=répertoire

    Répertoire de base des tables BDB. Cela doit être le même répertoire que vous avez utilisés pour --datadir.

  • --bdb-lock-detect=#

    Détection des verrouillages Berkeley. (DEFAULT, OLDEST, RANDOM, ou YOUNGEST).

  • --bdb-logdir=répertoire

    Répertoire des fichiers de log de Berkeley DB.

  • --bdb-no-recover

    Ne pas démarrer Berkeley DB en mode de restauration.

  • --bdb-no-sync

    Ne pas vider les tampons synchroniquement.

  • --bdb-shared-data

    Démarrer Berkeley DB en mode multi-processus (Ne pas utiliser DB_PRIVATE lors de l'initialisation de Berkeley DB)

  • --bdb-tmpdir=répertoire

    Répertoire des fichiers temporaires de Berkeley DB.

  • --skip-bdb

    Désactive l'utilisation des tables BDB.

See Section 5.2.1, « Options de ligne de commande de mysqld ».

Les variables systèmes suivante affectent le comportement des tables BDB :

  • bdb_max_lock

    Le nombre maximal de verrous actifs sur une table BDB.

See Section 5.2.3, « Variables serveur système ».

Si vous utilisez --skip-bdb, MySQL n'initialisera pas la bibliothèque Berkeley DB et cela économisera beaucoup de mémoire. Bien sûr, vous ne pouvez pas utiliser les table BDB si vous utilisez cette option. Si vous essayez de créer une table BDB, MySQL créera une table MyISAM à la place.

Normalement, vous devez démarrer mysqld sans --bdb-no-recover si vous avez l'intention d'utiliser des tables BDB. Cela peut cependant vous poser des problèmes si vous essayez de démarrer mysqld alors que des fichiers de log BDB sont corrompus. See Section 2.5.2.3, « Problèmes de démarrage du serveur MySQL ».

Vous pouvez spécifier le nombre maximal de verrous avec bdb_max_lock (10000 par défaut) que vous pouvez activer sur une table BDB. Vous devez l'augmenter si vous obtenez des erreurs du type :

bdb: Lock table is out of available locks
Got error 12 from ...

lorsque vous avez fait de longues transactions ou quand mysqld doit examiner beaucoup de lignes pour calculer la requête.

Vous pouvez aussi changer les options binlog_cache_size et max_binlog_cache_size si vous utilisez de grandes transactions multi-lignes. See Section 5.9.4, « Le log binaire ».

14.4.4 Caractéristiques des tables BDB

Chaque table BDB est stocké sur le disque en deux fichiers. Les fichiers portent le nom de la table, et ont des extensions qui indiquent le type de fichier. Un fichier .frm stocke la définition de la table, et le fichier .db contient les données et les index.

Pour spécifier explicitement que vous voulez une table BDB, indiquez l'option de création de table ENGINE ou TYPE :

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

BerkeleyDB est un synonyme de BDB pour les options ENGINE et TYPE.

Le moteur de tables BDB fournit un modèle transactionnel. La fa¸on dont vous utilisez ces tables dépend du mode de validation :

  • Si vous utilisez le mot d'auto-validation (ce qui est le mode par défaut), les modifications dans les tables BDB sont validées immédiatement, et ne peuvent pas être annulées.

  • Si vous utilisez le mode de validation manuel, les modifications ne seront rendues permanentes que si vous envoyez la commande COMMIT. AU lieu de valider, vous pouvez aussi annuler avec la commande ROLLBACK pour détruire les modifications.

    Vous pouvez démarrer une transaction avec la commande BEGIN WORK pour suspendre le mode d'auto-validation, ou avec SET AUTOCOMMIT=0 pour le désactiver explicitement.

See Section 13.4.1, « Syntaxes de START TRANSACTION, COMMIT et ROLLBACK ».

Le moteur de tables BDB a les caractéristiques suivantes :

  • Les tables BDB peuvent avoir jusqu'à 31 index par table, 16 colonnes par index, et un taille maximale de 1024 octets par index (500 octets avant MySQL 4.0).

  • MySQL requiert une clé PRIMARY KEY dans chaque table BDB pour être capable de faire référence aux lignes précédemment lues. Si vous n'en créez pas, MySQL va gérer une telle clé de manière cachée. La clé cachée a une taille de 5 octets, et est incrémentée à chaque nouvelle insertion.

  • La clé PRIMARY KEY sera plus rapide que n'importe quelle autre clé, car la PRIMARY KEY est stockée avec les données. Comme les autres clés sont stockées sous la forme données + PRIMARY KEY, il est important de garder une clé PRIMARY KEY aussi courte que possible pour économiser de l'espace disque, et améliorer la vitesse.

    Ce comportement est similaire à celui d'InnoDB, où des clés primaires courtes économisent de l'espace pour la clé primaire et pour les index secondaire aussi.

  • Si toutes les colonnes auxquelles vous accédez dans une table BDB font partie du même index dans la clé primaire, alors MySQL peut exécuter la requête sans avoir à lire la ligne elle-même. Dans une tableMyISAM, ce qui précède n'est valable que si les colonnes font partie du même index.

  • Scanner séquentiellement est plus lent qu'avec MyISAM car les tables BDB stockent les données dans un fichier B-tree et non pas dans un fichier séparé.

  • Les clés ne sont pas compressées avec les clés précédentes, comme pour les tables ISAM et MyISAM. En d'autres termes, les informations de clés prennent un peu plus d'espace pour les tables BDB, comparativement aux tables MyISAM qui n'utilisent pas l'option PACK_KEYS=0.

  • Il y a souvent des trous dans les tables BDB pour vous permettre d'insérer de nouvelles lignes au milieu de l'arbre de données. Cela rend les tables BDB un peu plus grandes que les tables MyISAM.

  • SELECT COUNT(*) FROM table_name est très lent, car les tables BDB ne maintiennent pas un compte de leur lignes dans la table.

  • L'optimiseur a besoin de connaître une approximation du nombre de lignes dans la table. MySQL résout ce problème en comptant les insertions et en conservant ce compte dans un segment séparé pour chaque table BDB. Si vous ne faites pas souvent de DELETE ou ROLLBACK, ce nombre sera plutôt précis pour l'optimiseur MySQL, mais comme MySQL ne stocke ce nombre qu'à la fermeture de la table, il peut être incorrecte si MySQL s'interrompt inopinément. Cela ne doit pas être fatal si ce nombre n'est pas à 100% correct. Vous pouvez forcer la mise à jour de ce nombre avec la commande ANALYZE TABLE ou OPTIMIZE TABLE. Section 13.5.2.1, « Syntaxe de ANALYZE TABLE » . Section 13.5.2.5, « Syntaxe de OPTIMIZE TABLE ».

  • Le verrouillage interne des tables BDB est fait au niveau page.

  • LOCK TABLES fonctionne avec les tables BDB sur les autres tables. Si vous n'utilisez pas le verrou LOCK TABLE, MySQL va poser un verrou interne multiple sur la table, pour s'assurer que la table est bien verrouillée, si un autre thread tente de poser un verrou.

  • Pour permettre les annulations de transaction, BDB gère un fichier de log. Pour maximiser les performances, vous devriez placer ces fichiers sur un autre disque que celui de votre base, en utilisant l'option --bdb-logdir.

  • MySQL fait un point de contrôle à chaque fois qu'un nouveau fichier de log BDB est démarré, et supprime les fichiers de logs anciens qui ne sont pas utiles. Si vous exécutez la commande FLUSH LOGS, vous placerez un nouveau point de contrôle pour les tables Berkeley DB.

    Pour la restauration après crash, vous devez utiliser les sauvegardes et le log binaire de MySQL. See Section 5.7.1, « Sauvegardes de base de données ».

    Attention : si vous effacez les anciens fichiers de log qui sont en cours d'utilisation, BDB ne sera pas capable de faire la restauration et vous risquez de perdre des données.

  • L'application doit toujours être prête à gérer des cas où une modification sur une table BDB peut être annulée, ou une lecture abandonnée pour cause de blocage de verrous.

  • Si vous atteignez la capacité maximale du disque avec la table BDB, vous allez obtenir une erreur (probablement l'erreur 28), et la transaction va s'annuler. C'est un comportement différent des tables MyISAM et ISAM qui vont attendre que mysqld ait trouvé de l'espace disque avant de continuer.

14.4.5 Ce que nous devons corriger dans BDB dans un futur proche :

  • Il est très lent d'ouvrir de nombreuses tables BDB en même temps. Si vous utilisez des tables BDB, il ne faut pas avoir un cache de table trop grand (par exemple, > 256) et vous devriez utiliser l'option --no-auto-rehash avec le client mysql. Nous envisageons de corriger cela en partie en version 4.0.

  • SHOW TABLE STATUS ne fourni pas encore beaucoup d'informations pour les tables BDB tables.

  • Optimiser les performances.

  • Ne pas utiliser les verrous de pages lorsque l'on scanne les tables.

14.4.6 Restrictions avec les tables BDB

Voilà les restrictions que vous pouvez rencontrer en travaillant avec les tables BDB :

  • Les tables BDB enregistrent dans le fichier .db le chemin vers le fichier tel qu'il était lorsqu'il a été crée. Cela fait que les tables BDB ne peuvent être changées de répertoire !

  • Lors de la sauvegarde de tables BDB, vous devez utiliser mysqldump ou effectuer des sauvegardes de tous les fichiers de table (les fichiers .frm et .db) et les fichiers de log BDB. Les fichiers de log de BDB sont les fichiers dans le répertoire de base des données nommés log.XXXXXXXXXX (dix chiffres);

  • Si une colonne accepte les valeurs NULL, avec un index unique, seule une valeur NULL est autorisée. Cela diffère du comportement des autres moteurs.

14.4.7 Erreurs pouvant survenir lors de l'utilisation des tables BDB

  • Si vous obtenez l'erreur suivante dans le fichier hostname.err log lors du démarrage de mysqld :

    bdb:  Ignoring log file: .../log.XXXXXXXXXX: unsupported log version #
    

    cela signifie que la nouvelle version de BDB ne supporte pas l'ancien format de log. Dans ce cas, vous devez effacer tous les logs BDB du dossier des données (les fichiers dont le nom est au format log.XXXXXXXXXX) et redémarrer mysqld. Nous vous recommandons aussi d'exécuter un mysqldump --opt de vos vieilles tables BDB, de les effacer, puis de restaurer les copies.

  • Si vous n'êtes pas en mode auto-commit et que vous effacez une table qu'un autre thread utilise, vous obtiendrez le message d'erreur suivant dans le fichier d'erreurs de MySQL :

    001119 23:43:56  bdb:  Missing log fileid entry
    001119 23:43:56  bdb:  txn_abort: Log undo failed for LSN:
                           1 3644744: Invalid
    

    Ce n'est pas une erreur très grave mais nous ne vous recommandons pas d'effacer vos tables si vous n'êtes pas en mode auto-commit, tant que ce problème n'est pas résolu (la solution n'est pas triviale).

14.5 Le moteur de table EXAMPLE

Le moteur de stockage EXAMPLE a été ajouté en MySQL 4.1.3. C'est un moteur ``bidon'' qui ne fait rien du tout. Son but est de fournir des exemples au niveau du code source de MySQL pour illustrer l'écriture d'un moteur de table. En tant que tel, il intéressera surtout les développeurs.

Pour examiner les codes source du moteur EXAMPLE, voyez le dossier sql/examples dans la distribution source de MySQL 4.1.3 ou plus récent.

Pour activer ce moteur de stockage, utilisez l'option --with-example-storage-engine de la commande configure lors de la compilation de MySQL.

Lorsque vous créez une table EXAMPLE, le serveur crée un fichier de définition dans le dossier de base de données. Le fichier porte le nom de la table, et fini avec l'extension .frm. Aucun autre fichier n'est créé. Aucune données ne peut être stockée dans la table, ni même lue.

mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE;
Query OK, 0 rows affected (0.78 sec)

mysql> INSERT INTO test VALUES(1),(2),(3);
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option

mysql> SELECT * FROM test;
Empty set (0.31 sec)

Le moteur de table EXAMPLE ne supporte pas l'indexation.

14.6 Le moteur de table FEDERATED

Le moteur de table FEDERATED a été ajouté en MySQL 5.0.3. C'est un moteur de table qui accède à des tables dans une base de données distante, plutôt que dans des fichiers locaux.

Pour examiner le code source pour le moteur FEDERATED, reportez-vous dans le dossier sql de la distribution source de MySQL 5.0.3 ou plus récent.

14.6.1 Installation du moteur de table FEDERATED

Pour activer ce moteur de table, utilisez l'option --with-federated-storage-engine avec la commande configure lorsque vous compilez MySQL.

14.6.2 Description du moteur de stockage FEDERATED

Lorsque vous créez une table FEDERATED, le serveur crée un fichier de définition de fichier dans le dossier de données. Le fichier porte le nom de la table et l'extension .frm. Aucun autre fichier n'est créé, car les données résident en fait sur un autre serveur. C'est la différence principale avec un moteur de table local.

Pour les tables locales, les fichiers de données sont locaux. Par exemple, si vous créez une table MyISAM du nom de users, le gestionnaire MyISAM crée un fichier de données appelée users.MYD. Un gestionnaire local lit, écrit et efface les données sur un fichier local, et les données sont enregistrées dans un format particulier au gestionnaire. Pour lire les lignes, le gestionnaire doit analyser les colonnes des tables. Pour écrire les lignes, les valeurs des colonnes doivent être converties en un format linéaire.

Avec le moteur de table MySQL FEDERATED, il n'y a pas de données locales pour la table : par exemple, il n'y a pas de fichier .MYD. Au lieu de cela, un serveur de base de données distant se charge de stocker les données de la table. Cela impose l'utilisation du protocole client MySQL pour lire, écrire et effacer les données. La lecture des données est initiée via la commande SQL SELECT * FROM tbl_name. Pour lire le résultat, les lignes sont lues avec la fonction C mysql_fetch_row(), puis converties en colonnes tel que la commande SELECT l'attent, au format demandé par le gestionnaire FEDERATED.

Le processus de base est le suivant :

  1. Les commandes SQL sont re¸ues localement.

  2. Les commandes sont passées au gestionnaire MySQL (au format du gestionnaire)

  3. Les commandes sont passées à l'API client MySQL (les données sont converties en appel SQL)

  4. Les commandes seront recues par la base de données distante, via l'API client.

  5. Les résultats, s'il y en a, sont convertis au format du gestionnaire.

  6. Les résultats sont re¸us localement.

14.6.3 Comment utiliser les tables FEDERATED

La procédure pour utiliser les tables FEDERATED est très simple. Normalement, vous devez avoir 2 serveurs en fonctionnement, sur le même hôte ou sur deux hôtes distincts. Il est aussi possible pour une table FEDERATED d'utiliser une autre table gérée par un autre serveur, mais il y a quelques limitations qui s'ajoutent.

D'abord vous devez avoir une table sur un serveur distant, à laquelle vous voulez accéder via la table FEDERATED. Supposez que la table distante dans la base federated est définie comme ceci :

CREATE TABLE test_table (
    id     int(20) NOT NULL auto_increment,
    name   varchar(32) NOT NULL default '',
    other  int(20) NOT NULL default '0',
    PRIMARY KEY  (id),
    KEY name (name),
    KEY other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1 ;

La table ENGINE peut utiliser n'importe quel moteur de stockage; la table n'est pas obligatoirement une table MyISAM.

Ensuite, créez une table FEDERATED pour accéder à la table distante. Le serveur où vous créez la table FEDERATED est le ``client-serveur''. Sur ce serveur, créez une table comme ceci :

CREATE TABLE federated_table (
    id     int(20) NOT NULL auto_increment,
    name   varchar(32) NOT NULL default '',
    other  int(20) NOT NULL default '0',
    PRIMARY KEY  (id),
    KEY name (name),
    KEY other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
COMMENT='mysql://root@remote_host:9306/federated/test_table';

La structure de cette table doit être exactement la même que la table distante, hormis le moteur ENGINE qui doit valoir FEDERATED et l'option de table COMMENT qui contient la chaîne de connexion pour spécifier au moteur FEDERATED comment se connecter au serveur distant.

Le moteur FEDERATED ne crée que le fichier test_table.frm dans la base de données federated.

Les informations d'hôte distant représente le serveur sur lequel votre serveur se connecte en tant que ``client'', et les informations de tables et de bases qui représentent les ``données''. Dans l'exemple, le serveur distant va fonctionne en tant que remote_host sur le port 9306 : il est recommandé de lancer ce serveur pour qu'il soit en attente sur le port 9306.

La forme générale de la chaîne de connexion de l'option COMMENT est la suivante :

scheme://user_name[:password]@host_name[:port_num]:/db_name/tbl_name

Seul le protocole mysql est supporté comme valeur pour scheme actuellement, et le numéro de port ainsi que le mot de passe sont optionnels.

Voici quelques exemples de chaînes de connexion :

COMMENT='mysql://username:password@hostname:port/database/tablename'
COMMENT='mysql://username@hostname/database/tablename'
COMMENT='mysql://username:password@hostname/database/tablename'

L'utilisation de COMMENT pour spécifier la chaîne de connexion n'est pas optimale, et nous allons probablement changer cela en MySQL 5.1. Gardez cela en tête que lorsque vous utilisez les tables FEDERATED, car cela vous obligera à faire des modifications dans un avenir proche.

De même, comme le mot de passe est stocké en texte clair dans la chaîne, il peut être vu par un autre utilisateur avec un accès à SHOW CREATE TABLE ou SHOW TABLE STATUS pour la table FEDERATED.

14.6.4 Limitations du moteur de stockage FEDERATED

Ce que le moteur de stockage FEDERATED fait et ne fait pas :

  • Dans la première version, le serveur distant doit être un serveur MySQL. Le support d'autres serveurs par le moteur FEDERATED est à l'étude actuellement.

  • La table distante sur laquelle pointe la table FEDERATED doit exister avant que vous essayez d'y accéder via la table FEDERATED.

  • Il est possible opur une table FEDERATED de pointer sur une autre table, mais vous devez être prudents et ne pas créer de boucle. Vous avez déjà entendu parlé de l'effet Larsen? Vous avez déjà vu ce que ca fait d'avoir deux miroirs face à face? Cela devrait illustrer la situation à éviter.

  • Il n'y a pas de support pour les transactions.

  • Il n'y a pas de moyen pour que le moteur FEDERATED sache que la table distante à changé. La raison à cela est que la table doit fonctionner comme un fichier de données qui n'est jamais écrit par autre chose que la base de odnnées. L'intégrité des données dans la table locale pourrait être cassée s'il y a des modifications dans la table distante.

  • Le moteur de stockage FEDERATED supporte les commandes SELECT, INSERT, UPDATE, DELETE et les index. Il ne supporte pas les commandes ALTER TABLE, DROP TABLE ou les autres commandes de Définition des données (Data Definition Language). Cette première implémentation n'utilise pas les commandes préparées. Nous étudions actuellement la possibilité d'ajouter le support de ces fonctionnalités au client.

  • L'implémentation utilise SELECT, INSERT, UPDATE, DELETE et non pas HANDLER.

  • Les tables FEDERATED ne fonctionne pas avec le cache de requêtes.

Certaines limitations seront levées dans les futures versions du gestionnaire FEDERATED.

14.7 Le moteur de table ARCHIVE

Le moteur de table ARCHIVE a été ajouté en MySQL 4.1.3. Il est utilisé pour stocker de grande quantité de données, sans index, et de manière très économique.

Pour activer ce moteur de table, utilisez l'option --with-archive-storage-engine avec la commande configure lors de la compilation de MySQL.

Lorsque vous créez une table de type ARCHIVE, le serveur crée un fichier de définition dans le dossier de données. Le fichier porte le nom de la table, et l'extension .frm. Le moteur de table crée les autres fichiers, qui portent tous les nom de la table. Les données et les metadonnées portent les extensions .ARZ et .ARM, respectivement. Un fichier .ARN peut aussi apparaître durant les opérations d'optimisation.

Le moteur ARCHIVE ne supporte que les commandes INSERT et SELECT : aucun effacement, remplacement ou modification. Une commande SELECT effectue un scan de table complet. Les enregistrements sont compressé au moment de leur insertion. Vous pouvez utiliser la commande OPTIMIZE TABLE pour analyser la table, et compresser encore plus.

Le moteur de table ARCHIVE utilise un verrouillage de ligne.

14.8 Le moteur CSV

Le moteur CSV a été ajouté en MySQL 4.1.4. Ce moteur stocke les données dans un fichier texte, avec le format valeurs séparées par des virgules.

Pour activer ce moteur de stockage, utilisez l'option --with-csv-storage-engine lors de la configuration configure de la compilation de MySQL.

Lorsque vous créez une table CSV, le serveur créé un fichier de définition de table dans le dossier de données. Le fichier commence avec le nom de table, et porte l'extension .frm. Le moteur de stockage crée aussi un fichier de données. Il porte le nom de la table, et l'extension .CSV. Le fichier de données est un fichier texte simple. Lorsque vous stockez des données dans la table, le moteur les écrit au format CSV dans le fichier de données.

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+------+------------+
| i    | c          |
+------+------------+
|    1 | record one |
|    2 | record two |
+------+------------+
2 rows in set (0.00 sec)

Si vous ouvrez le fichier test.CSV issu du dossier de données, après avoir exécuté les commande ci-dessus, vous trouverez le contenu suivant :

"1","record one"
"2","record two"

Le moteur CSV ne supporte pas l'indexation.

14.9 Tables ISAM

Le moteur de table originale de MySQL est le moteur ISAM. Il a été le seul moteur disponible jusqu'en MySQL 3.23, lorsque la version améliorée MyISAM a été inventée. ISAM est maintenant obsolète. Depuis MySQL 4.1, il est inclus dans le source, mais pas activé dans les versions binaires. Il disparaîtra en MySQL 5.0. Les versions embarquées de MySQL ne supportent pas les tables ISAM par défaut.

Etant donné l'obsolescence de ISAM, et comme MyISAM est une version améliorée de ISAM, il est recommandé de convertir les tables ISAM en MyISAM dès que possible. Pour convertir une table ISAM en MyISAM, utilisez la commande ALTER TABLE :

mysql> ALTER TABLE tbl_name TYPE = MYISAM;

Pour plus d'informations sur MyISAM, voyez Section 14.1, « Le moteur de tables MyISAM ».

Chaque table ISAM est stockée dans trois fichiers. Les fichiers portent le nom de la table, et ont une extension qui indique leur type. Un fichier .frm stocke la définition de table. Le fichier de données a pour suffixe .ISD. Le fichier d'index a l'extension .ISM.

ISAM utilise les index B-tree.

Vous pouvez réparer ou vérifier une table ISAM avec l'utilitaire isamchk. See Section 5.7.3.7, « Utiliser myisamchk pour restaurer une table ».

ISAM possède les fonctionnalités/propriétés suivantes :

  • Clefs compressées et de tailles fixes

  • Enregistrements de taille fixée ou dynamique

  • 16 clefs avec 16 parties de clefs/clefs

  • Taille maximale de la clef 256 (défaut)

  • Les données sont enregistrées au format machine; c'est rapide, mais c'est dépendant de la machine/système d'exploitation.

La plupart des choses vraies pour les tables MyISAM le sont pour les tables ISAM. La différence majeure comparées aux tables MyISAM sont :

  • Les tables ISAM ne sont pas portables directement entre les plates-formes/systèmes d'exploitation.

  • Ne peut pas gérer les tables de taille supérieure à 4 Go.

  • Ne supporte que la compression des préfixes sur les chaînes de caractères.

  • Limites de clefs plus basses.

  • Les tables à taille de ligne dynamique sont plus fragmentées.

  • Ne supporte pas les tables MERGE.

  • Les tables sont vérifiées et réparées avec isamchk plutôt que myisamchk.

  • Les tables sont compressées avec pack_isam plutôt que myisampack.

  • Impossible d'utiliser les commandes BACKUP TABLE et RESTORE TABLE.

  • Impossible d'utiliser les commandes d'entretien CHECK TABLE, REPAIR TABLE, OPTIMIZE TABLE et ANALYZE TABLE.

  • Pas de support pour les index en texte plein ou spatiaux.

  • Pas de support pour les jeux de caractères multiples.

  • Les index ne peuvent pas être assignés à des caches de clés spécifiques.