Table des matières
MyISAMMERGEMEMORY (HEAP)BDB ou BerkeleyDBBDBBDBBDBBDBBDB dans un futur proche : BDBBDBEXAMPLEFEDERATEDARCHIVECSVISAMMySQL supporte plusieurs moteurs de stockage, qui gère différents types de tables. Les moteurs de tables MySQL peuvent être transactionnels ou non-transactionnels.
Le moteur de tables originale était ISAM,
qui gérait des tables non-transactionnelles. Ce moteur a été
remplacé par le moteur MyISAM et ne doit
plus être utilisé. Il est abandonné et ne doit plus être
utilisé. depuis MySQL 4.1, et il sera supprimé en MySQL 5.0.
En MySQL 3.23.0, les moteurs MyISAM et
HEAP ont été introduits.
MyISAM est une version améliorée de
ISAM. Le moteur HEAP
propose des tables stockées en mémoire. Le moteur
MERGE a été ajouté en MySQL 3.23.25. Il
permet le regroupement de tables MyISAM
identiques sous la forme d'une seule table. Tous les trois
moteurs sont non transactionnels, et sont tous inclus par
défaut. Notez que le moteur HEAP est
maintenant appelé MEMORY.
Les moteurs InnoDB et BDB
gèrent des tables transactionnelles, et ont été introduits en
MySQL 3.23. Les deux font partie de la distribution source de
MySQL 3.23.34a. BDB est inclus dans les
distributions MySQL-Max pour les systèmes
d'exploitation qui le supportent. InnoDB est
aussi inclus dans les distributions binaires
MySQL-Max de MySQL 3.23. Depuis MySQL 4.0,
InnoDB est inclus par défaut dans toutes les
distributions binaires. Dans les distributions source, vous
pouvez l'activer ou pas en configurant la compilation.
NDBCluster est le moteur de stockage du
cluster MySQL qui implémente des tables réparties sur
plusieurs serveurs. Il est disponible avec les distributions
source depuis MySQL 4.1.2.
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 :
Plus sûr. Même si MySQL crashe ou que vous avez un problème matériel, vous pouvez récupérer vos données, soit par un recouvrement automatique, soit à partir d'une sauvegarde combinée avec le log des transactions.
Vous pouvez combiner plusieurs commandes et les accepter toutes
d'un seul coup avec la commande COMMIT.
Vous pouvez utiliser ROLLBACK pour ignorer
vos modifications (si vous n'êtes pas en mode auto-commit).
Si une mise à jour échoue, tout vos changements seront annulés. (Avec les tables NTST tous les changements opérés sont permanents)
Gère mieux les accès concurrents si la table re¸oit simultanément plusieurs lectures.
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) :
Plus rapides
Utilisent moins d'espace disque
Utilisent moins de mémoire pour exécuter les mises à jour.
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é.
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.
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.
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.
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 ».
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.
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.
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.
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.
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 ».
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.
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-databaseshell>ls -1 t1 t2 > total.MRGshell>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.
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.
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.
BDBBDBBDBBDBBDB dans un futur proche : BDBBDB
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 crashs, 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/.
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 dtabases
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.
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 ».
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 ».
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.
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.
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.
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).
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.
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.
Pour activer ce moteur de table, utilisez l'option
--with-federated-storage-engine avec la
commande configure lorsque vous compilez
MySQL.
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
. Pour lire le
résultat, les lignes sont lues avec la fonction C
tbl_namemysql_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 :
Les commandes SQL sont re¸ues localement.
Les commandes sont passées au gestionnaire MySQL (au format du gestionnaire)
Les commandes sont passées à l'API client MySQL (les données sont converties en appel SQL)
Les commandes seont recues par la base de données distante, via l'API client.
Les résultats, s'il y en a, sont convertis au format du gestionnaire.
Les résultats sont re¸us localement.
La procédure pour utiliser les tables
FEDERATED est très simple. Normalement, vous
devez avoir 2 serveurs en focntionnement, 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'optin
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.
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.
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.
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
uassi 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. Lorque 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.
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 MySAM 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.
This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.