Depuis la version 3.23.6 de MySQL, vous pouvez choisir entre trois
formats basique de tables (ISAM, HEAP et MyISAM).
Les nouvelles versions de MySQL peuvent supporter d'autres types de
tables (InnoDB, ou BDB), cela dépend de comment vous
l'avez compilé.
Lorsque vous créez une nouvelle table,vous pouvez dire à MySQL quel type de table il doit utiliser pour celle-ci. MySQL créera toujours un fichier `.frm' pour stocker les définitions de la table et des colonnes. Selon le type de table, les index et les données seront stockés dans d'autres fichiers.
Notez que pour utiliser les tables InnoDB vous devez au moins
utiliser l'option de démarrage innodb_data_file_path.
See section 7.5.2 Options de démarrage InnoDB.
Le type de table par défaut de MySQL est MyISAM. Si vous
essayez d'utiliser un type de table qui n'est pas compilée ou activée,
MySQL créera à la place une table de type MyISAM. C'est une
fonctionnalité très utile quand vous voulez copier des tables entre
différents serveurs SQL qui ne supportent pas les mêmes types de
tables (comme copier des tables vers un esclave qui est optimisé pour
la vitesse en ne supportant pas les tables transactionnelles). Ce
changement de table automatique peut toutefois induire en erreur les
nouveaux utilisateurs de MySQL. Nous allons introduire des messages
d'avertissement dans MySQL 4.0 et les afficher lors des transtypages
automatiques des tables.
Vous pouvez changer les types de tables en utilisant la commande ALTER
TABLE. See section 6.5.4 Syntaxe de ALTER TABLE.
Notez que MySQL supporte deux différents types de tables :
tables transactionnelles (InnoDB et BDB) et tables
non-transactionnelles (HEAP, ISAM, MERGE, et MyISAM).
Les avantages des tables transactionnelles (TST) sont :
COMMIT.
ROLLBACK pour ignorer vos modifications
(si vous n'êtes pas en mode auto-commit).
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.
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.
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).
Vous pouvez vérifier et réparer les tables MyISAM avec myisamchk.
See section 4.4.6.7 Utiliser myisamchk pour restaurer une table. Vous pouvez compresser les tables MyISAM avec
myisampack, pour gagner de l'espace disque.
See section 4.7.4 myisampack, le générateur de tables MySQL compressées en lecture seule.
Voici les nouveautés des tables MyISAM :
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.
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,
le vieux comportement est toujours présent.
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.
BLOB et TEXT peuvent être indexés.
NULL sont autorisées dans une colonne indexée. Elles prennent
0 à 1 octets par clé.
myisamchk.
myisamchk va marquer les tables comme vérifiée si 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 statistique pour les parties de clés (et non plus
pour les clés complètes, comme en ISAM).
myisampack peut compresser des colonnes BLOB et VARCHAR.
DATA/INDEX DIRECTORY="path" de
CREATE TABLE). See section 6.5.3 Syntaxe de CREATE TABLE.
MyISAM supporte aussi les fonctionnalités suivantes, dont
MySQL pourra profiter sous peu :
VARCHAR; une colonne VARCHAR commence
avec une taille, stockée sur 2 octets.
VARCHAR peuvent avoir un format de lignes
fixe ou dynamique.
VARCHAR et CHAR peuvent prendre jusqu'à 64ko.
Tous les segments de clé ont leur propre définition de langue. Cela
permettra à MySQL d'avoir des définitions de langue différentes pour chaque
colonne.
UNIQUE.
Cela vous permettra d'avoir l'attribut UNIQUE sur n'importe quelle combinaison
de colonne de votre table (vous ne pouvez pas faire de recherche sur un tel index
UNIQUE).
Notez que les fichiers d'index sont généralement plus plus petit avec les tables
MyISAM qu'avec les tables ISAM. Cela signifie que MyISAM va normalement
utiliser moins de ressources systèmes que ISAM, mais aura besoin de plus de processeur
lors de l'insertion dans un index compressé.
Les options suivantes de mysqld peuvent être utilisée pour modifier le comportement
des tables MyISAM. See section 4.5.6.4 Syntaxe de SHOW VARIABLES.
| Option | Description |
--myisam-recover=# | Réparation automatique des tables crashées. |
-O myisam_sort_buffer_size=# | Buffer utilisé lors de la réparation des tables. |
--delay-key-write=ALL | Ne pas écrire les buffers de clés entre deux écriture dans une table MyISAM.
|
-O myisam_max_extra_sort_file_size=# | Utilisé pour aider MySQL à décider quand utiliser la méthode du cache d'index, lente mais sûre. Notez que ce paramètre était donné en méga-octets avant la version 4.0.3 et en octets depuis cette version. |
-O myisam_max_sort_file_size=# | Ne pas utiliser la méthode de tri rapide pour créer l'index, si le fichier temporaire dépasse la taille indiquée ici. Notez que ce paramètre était donné en méga-octets avant la version 4.0.3 et en octets depuis cette version. |
-O bulk_insert_buffer_size=# | Taille du cache d'arbre lors d'insertion massives. Notez que c'est une limite par thread! |
La réparation automatique est activée si vous démarrez mysqld avec
l'option --myisam-recover=#. See section 4.1.1 Options de ligne de commande de mysqld.
Lors de l'ouverture, la marque de fermeture de la table est vérifiée, ou le
compteur d'ouverture de la table n'est pas 0 et vous fonctionnez avec l'option
--skip-external-locking. Si l'un des deux ci-dessus est vrai, la procédure
suivante s'applique :
Si la réparation n'a pas été capable de retrouver toutes les lignes d'une
requête précédente, et que vous ne spécifiez pas l'option FORCE à
myisam-recover, alors la correction automatique va s'arrêter avec
l'erreur suivante dans le fichier d'erreurs :
Error: Couldn't repair table: test.g00pages
Dans ce cas, si vous utilisez l'option FORCE, vous obtiendrez le message
suivant dans le fichier d'erreurs :
Warning: Found 344 of 354 rows when repairing ./test/g00pages
Notez que si vous exécutez une restauration automatique avec une option automatique
de BACKUP, vous devriez avoir un script cron qui va automatiquement
copier les fichiers avec l'extension `tablename-datetime.BAK' depuis le dossier
de données vers une solution de sauvegarde.
See section 4.1.1 Options de ligne de commande de mysqld.
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édement 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
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 champs 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 6.5.3 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 :
CHAR, NUMERIC, et DECIMAL
sont complétées par des espaces jusqu'à atteindre la longueur totale
de la colonne.
myisamchk) sauf si un grand
nombre de lignes est effacé et que vous voulez retourner l'espace
libéré au système d'exploitation.
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 :
'', 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.
myisamchk -r de temps en temps pour
améliorer les performances. Utilisez myisamchk -ei tbl_name pour obtenir des
statistiques.
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) / 8Il 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 (pack_isam pour les tables ISAM) :
myisampack.
0 sont stockés en utilisant 1 octet.
BIGINT (8 octets) peut être stocké en tant que colonne TINYINT
(1 octet) si toutes les valeurs sont entre 0 et 255.
ENUM.
myisamchk.
MyISAMLe 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.
MyISAM corrompuesMê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 :
mysqld est tué au milieu d'une écriture.
Les symptômes typiques d'une table corrompue sont :
Incorrect key file for table: '...'. Try to repair it
pendant la sélection de données à partir de cette table.
Vous pouvez vérifier l'état d'une table avec la commande CHECK TABLE.
See section 4.4.4 Syntaxe de CHECK TABLE.
Vous pouvez réparer une table corrompue avec REPAIR TABLE. See section 4.4.5 Syntaxe de REPAIR TABLE.
Vous pouvez aussi réparer une table, lorsque mysqld ne
fonctionne pas, avec la commande myisamchk. myisamchk syntax.
Si vos tables sont souvent corrompues, vous devez essayez de trouver d'où vient le problème ! See section A.4.1 Que faire si MySQL crashe constamment ?.
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. See 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 :
FLUSH ou qu'il n'y a plus de place dans le cache de la table)
le compteur est decrémenté si la table n'a pas été mise à jour.
En d'autres termes, les seuls moyens d'obtenir ce genre d'erreur sont :
MyISAM sont copiés sans LOCK et FLUSH TABLES.
myisamchk --recover ou myisamchk
--update-state sur une table qui était utilisée par mysqld.
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.
MERGE
Les tables MERGE sont nouvelles depuis MySQL version 3.23.25.
Le code est toujours en phase gamma, mais il est déjà raisonnablement
stable.
Une table MERGE (aussi connue sous le nom de MRG_MyISAM, ou table
assemblée) est un regroupement de tables MyISAM identiques, qui peuvent
être utilisées ensemble, comme une seule. Nous ne pouvez faire que des
commandes SELECT, DELETE et UPDATE dans ces tables.
Si vous effacez (avec DROP) la table MERGE, vous ne faîtes qu'annuler
le rassemblement MERGE.
Notez que la commande DELETE FROM merge_table utilisée sans la clause
WHERE va seulement effacer le rassemblement de tables, et non pas les
lignes dans les tables. Nous envisageons de corriger cela en version 4.1.
Avec des tables identiques, nous voulons dire que toutes les tables sont créées
avec les mêmes colonnes et index. Vous ne pouvez pas rassembler des tables
dans lesquelles les colonnes sont définies différemment, n'ont pas exactement
le même nombre de colonne, ou ont des index dans un autre ordre. Cependant,
certaines des tables peuvent être compressées avec myisampack.
See section 4.7.4 myisampack, le générateur de tables MySQL compressées en lecture seule.
Lorsque vous créez une table MERGE, vous allez obtenir un fichier
`.frm', de définition de table, et un fichier de liste de tables
`.MRG'. Le fichier `.MRG' contient simplement la liste des
fichiers d'index (extension `.MYI') qui doivent être utilisés comme
un seul et même. Toutes les tables utilisées doivent être dans la même
base que la table MERGE elle-même.
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.
Les tables MERGE peuvent vous aider dans les situations suivantes :
myisampack puis créer une table MERGE pour les utiliser.
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).
MERGE pour les
autres opérations. Vous pouvez même avoir de nombreuses tables
MERGE actives, qui partagent les même fichiers.
MERGE que de réparer une
grande 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!
MERGE. C'est bien plus rapide, et cela va vous faire économiser de l'espace
disque.
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 :
MyISAM identiques pour faire une table MERGE.
REPLACE ne fonctionne pas.
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).
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.
See section 5.2.1 Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT).
DROP TABLE,
ALTER TABLE,
DELETE FROM table_name sans clause WHERE,
REPAIR TABLE,
TRUNCATE TABLE,
OPTIMIZE TABLE ou
ANALYZE TABLE
sur aucune des tables qui sont couvertes par une table MERGE qui est
"ouverte". Si vous faîtes cela, la table MERGE peut toujours pointeur
sur la table originale, et vous allez obtenir des résultats inattendus.
Le plus simple palliatif est d'utiliser la commande FLUSH TABLES, pour
s'assurer qu'aucune des tables MERGE ne reste ouverte.
Lorsque vous créez une table MERGE, vous devez spécifier la liste
des tables que vous allez utiliser, avec l'option UNION(list-of-tables).
Optionellement, grâce à l'option INSERT_METHOD, vous pouvez spécifier
si vous voulez que la table MERGE ajoute les nouvelles lignes dans la
première ou la dernière table de l'UNION. Si vous spécifiez pas
INSERT_METHOD ou si vous spécifiez NO, alors les commandes INSERT
donnée à la table MERGE retourneront une erreur.
L'exemple suivant vous montre comme utiliser les tables MERGE :
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
CREATE TABLE total (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20))
TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
Notez que vous pouvez aussi manipuler le fichier `.MRG' directement, hors du serveur MySQL :
shell> cd /mysql-data-directory/current-database shell> ls -1 t1.MYI t2.MYI > total.MRG shell> mysqladmin flush-tables
Maintenant, vous pouvez faire des commandes comme :
mysql> SELECT * FROM total; +---+---------+ | a | message | +---+---------+ | 1 | Testing | | 2 | table | | 3 | t1 | | 1 | Testing | | 2 | table | | 3 | t2 | +---+---------+
Notez que la colonne a, bien que déclarée PRIMARY KEY,
n'est pas vraiment unique, car les tables MERGE ne peuvent pas garantir
l'unicité entre les tables sous -jacente à la MyISAM.
Pour redéfinir une table MERGE, vous pouvez faire ceci :
DROP la table, puis recréez la.
ALTER TABLE table_name UNION(...)
FLUSH TABLE sur
la table MERGE et toutes les tables sous-jacentes, pour forcer
le gestionnaire à relire la définition.
MERGE
Voici une liste des problèmes connus avec les tables de type MERGE :
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.
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.
RENAME TABLE utilisé sur une table de type MERGE peut
corrompre la table. Cela sera corrigé en MySQL 4.0.x.
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.
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.
MERGE
efficacement, et il produit parfois des jointures sub-optimales. Cela sera
corrigé en MySQL 4.0.x.
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.
ISAM
Vous pouvez aussi utiliser le type de tables désapprouvé ISAM. Il disparaîtra
bientôt (probablement dans MySQL 5.0) car MyISAM est une meilleure
implémentation de la même chose. ISAM utilise un index B-tree.
L'index est stocké dans un fichier portant l'extension `.ISM', et les données
sont enregistrées dans un fichier avec l'extension `.ISD'. Vous pouvez
vérifier/réparer les tables ISAM avec l'utilitaire isamchk.
See section 4.4.6.7 Utiliser myisamchk pour restaurer une table.
ISAM possède les fonctionnalités/propriétés suivantes :
La plupart des choses vraies pour les tables MyISAM le sont pour les tables
ISAM. See section 7.1 Tables MyISAM. La différence majeure comparées
aux tables MyISAM sont :
ISAM ne sont pas binairement portables entre les plate-formes/systèmes d'exploitations.
pack_isam plutôt qu'avec myisampack.
Si vous voulez convertir vos tables ISAM en MyISAM pour pouvoir utiliser
des utilitaires tels que mysqlcheck, utilisez la commande ALTER TABLE :
mysql> ALTER TABLE nom_de_table TYPE = MYISAM;
Les versions embarquées de MySQL ne supportent pas les tables ISAM.
HEAP
Les tables HEAP utilisent un index de hachage, et sont stockées en mémoire.
Elles sont très rapides, mais si MySQL crashe, vous perdrez toutes vos données.
Les tables HEAP sont très pratiques pour être des tables temporaires!
Les tables HEAP internes MySQL utilisent des hachages 100% dynamiques
sans zones de débordement. Il n'y a pas d'espace nécessaire pour des listes libres.
Les tables HEAP n'ont aussi aucun problème d'effacement et d'insertions,
qui sont le lot commun des tables de hachage :
mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down
-> FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;
Voici quelques conseils concernant les tables HEAP :
MAX_ROWS dans la commande de création CREATE pour vous assurer
de ne pas accidentellement consommer toute la mémoire.
= et <=>
(mais ils sont très rapides).
HEAP ne peuvent utiliser que des clés entières pour rechercher
une ligne. Par comparaisons, les tables MyISAM peuvent utiliser un préfixe de clé
pour trouver des lignes.
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.
HEAP ne supportait les index sur les valeurs NULL.
HEAP
(ce qui n'est pas courant pour les tables de hachage).
HEAP sont partagées entre tous les clients (comme une autre table).
ORDER BY).
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.
HEAP que vous allez utiliser simultanément.
DELETE FROM heap_table,
TRUNCATE heap_table ou DROP TABLE heap_table.
MyISAM
en table HEAP.
HEAP plus grande que max_heap_table_size.
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*))
sizeof(char*) vaut 4 sur les machines 32 bits et 8 sur une machine 64 bits.
InnoDBInnoDB
InnoDB fournit à MySQL un gestionnaire de table transactionnelle (compatible ACID),
avec validation (commits), annulations (rollback) et capacités
de restauration après crash. InnoDB utilise un verrouillage de lignes,
et fournit des lectures cohérentes comme Oracle, sans verrous. Ces fonctionnalités
accroissent les possibilités d'utilisation simultanées des tables, et les
performances. Il n'y a pas de problème de queue de verrous avec
InnoDB, car les verrous de lignes utilisent très peu de place.
Les tables InnoDB sont les premières tables MySQL qui supportent
les contraintes de clés étrangères (FOREIGN KEY).
InnoDB a été conçu pour maximiser les performances lors du traitement
de grandes quantités de données. Son efficacité processeur n'est égalée
par aucun autre moteur de base de données.
Techniquement, InnoDB est un gestionnaire de table placé sous MySQL.
InnoDB dispose de son propre buffer pour mettre en cache les données
et les index en mémoire centrale. InnoDB stocke les tables et index
dans un espace de table, qui peut être réparti dans plusieurs fichiers.
Ceci diffère des tables comme, par exemple, MyISAM où chaque table est
stockée dans un fichier différent. Les tables InnoDB peuvent prendre n'importe
quelle taille, même sur les systèmes d'exploitation dont la limite est
de 2 Go par fichier.
Vous pouvez trouver les dernières informations sur InnoDB à
http://www.innodb.com/. La dernière version du manuel InnoDB
est toujours disponible la-bas, et vous pouvez aussi commander
des licences commerciales et du support InnoDB.
InnoDB est actuellement (Octobre 2001) utilisé en production dans
plusieurs sites où de grandes capacités de stockages et des performances
accrues sont nécessaires. Le fameux site web Slashdot.org utilise
InnoDB. Mytrix, Inc. stocke plus de 1 To de données dans une base
InnoDB, et un autre site gère une moyenne de 800 insertions/modifications
par secondes avec InnoDB.
Les tables InnoDB sont inclues dans la distribution source de MySQL depuis
la version 3.23.34a et sont activées dans le binaire MySQL-Max. Pour
Windows, les binaires de MySQL-Max sont disponibles dans la
distribution standard.
Si vous avez téléchargé une version binaire de MySQL qui inclut
le support de InnoDB, suivez simplement les instructions du manuel
MySQL pour installer une version binaire. Is vous avez déjà la version
MySQL-3.23 installée, alors le moyen de plus simple est d'installer
le serveur `mysqld' avec l'exécutable correspondant de la distribution
-Max. MySQL et MySQL -Max ne diffèrent que par leur exécutable.
See section 2.2.11 Installer MySQL à partir d'une distribution binaire.
See section 4.7.5 mysqld-max, la version étendue du serveur mysqld.
Pour compiler MySQL avec le support InnoDB, téléchargez MySQL-3.23.34a ou plus
récent depuis http://www.mysql.com/
et configurez MySQL avec l'option --with-innodb. Voyez le manuel
MySQL sur l'installation d'une distribution source.
See section 2.3 Installer MySQL à partir des sources.
cd /chemin/vers/source/de/mysql-3.23.37 ./configure --with-innodb
Pour utiliser InnoDB, vous devez spécifier les options de démarrage InnoDB
dans le fichier `my.cnf' ou `my.ini'. La méthode minimale pour
modifier ces options est d'ajouter à la section [mysqld], la ligne
innodb_data_file_path=ibdata:30M
mais pour obtenir de meilleures performances, vous devez spécifier
les options telles que recommandé. See section 7.5.2 Options de démarrage InnoDB.
InnoDB est sous licence GNU GPL License Version 2 (de Juin 1991).
Dans la distribution source de MySQL, InnoDB apparaît comme un sous-dossier.
InnoDB
Pour utiliser les tables InnoDB en MySQL-Max-3.23, vous devez spécifier des paramètres
de configuration dans la section [mysqld] du fichier de configuration
`my.cnf', ou optionnelle, dans le fichier `my.ini' sous Windows.
Au minimum, en 3.23, vous devez spécifier innodb_data_file_path
pour spécifier les noms et tailles de fichiers de données. Si vous décidez
de ne pas mentionner innodb_data_home_dir dans `my.cnf', le comportement
par défaut est de créer ces fichiers dans le dossier de données de MySQL.
Si vous ne spécifiez pas innodb_data_home_dir sous la forme d'une chaîne vide,
vous pouvez donner un chemin absolu jusqu'au stockage de vos données dans
innodb_data_file_path. En MySQL 4.0, vous n'avez même pas à spécifier
l'option innodb_data_file_path : le comportement par défaut est de créer
un fichier de données auto-croissant de 10 Mo appelé `ibdata1' dans le
dossier de données de MySQL. En MySQL 4.0.0 et 4.0.1, le fichier de données était de
64 Mo et de taille fixe.
Si vous ne voulez pas utiliser les tables InnoDB, vous pouvez ajouter
l'option skip-innodb dans le fichier d'options de MySQL.
Mais pour obtenir de bonnes performances, vous devez explicitement choisir
les paramètres InnoDB listés dans les exemples suivants :
Depuis les versions 3.23.50 et 4.0.2, InnoDB fait que le dernier
fichier spécifié dans l'option innodb_data_file_path peut être
auto-croissant (auto-extending). La syntaxe pour la ligne
innodb_data_file_path est alors la suivante :
pathtodatafile:sizespecification;pathtodatafile:sizespecification;... ... ;pathtodatafile:sizespecification[:autoextend[:max:sizespecification]]
Si vous spécifiez le dernier fichier avec l'option autoextend, InnoDB
va augmenter la taille du dernier fichier de données jusqu'à ce qu'il n'y ait plus
de place dans l'espace de table. Les incréments se feront par bloc de 8 Mo. Par
exemple :
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend
indique à InnoDB de créer un fichier de données unique, de taille initiale de
100 Mo et qui sera agrandi de 8Mo jusqu'à ce qu'il n'y ait plus de place.
Si le disque se remplit, vous placerez le prochain fichier de données sur un autre
disque. Vous devez alors regarder la taille du fichier de données
`ibdata1', arrondir sa taille au Mo précédent (multiple de 1024 * 1024 octets (= 1 Mo))
et spécifier la taille du fichier `ibdata1' explicitement dans l'option
innodb_data_file_path.
Après cela, vous pouvez spécifier un autre fichier de données :
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
Soyez prudents avec les systèmes de fichiers où la taille maximale de fichier
est de 2 Go! InnoDB n'est pas capable de détecter la taille maximale de fichier
pour votre système d'exploitation. Sur d'autres systèmes, vous devrez spécifier
la taille maximale du fichier :
innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M
Exemple de fichier `my.cnf' simple Supposons que vous avez
un serveur avec 128 Mo de RAM et un disque dur. Voici un exemple de configuration
de fichier `my.cnf' ou `my.ini' pour InnoDB. Nous supposons que vous
exécutez MySQL-Max-3.23.50 ou plus récent, ou MySQL-4.0.2 ou plus récent.
Cet exemple devrait convenir à une majorité d'utilisateurs, Unix et Windows,
qui ne souhaitent pas répartir leur fichiers de données InnoDB et leurs logs
sur plusieurs disques. Cette configuration crée un fichier de données auto-croissant,
appelé `ibdata1' et deux fichiers de log InnoDB `ib_logfile0' et
`ib_logfile1' dans le dossier de données MySQL (typiquement `/mysql/data').
De plus, le petit fichier d'archive InnoDB `ib_arch_log_0000000000'
sera placé dans datadir.
[mysqld] # Vous pouvez placer d'autres options MYSQL ici # ... # Le fichier de données doivent contenir # vos données et index. # Assurez vous que vous avez l'espace disque # nécessaire. innodb_data_file_path = ibdata1:10M:autoextend # Utilisez un buffer de taille # 50 à 80 % de votre mémoire serveur set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # Utiisez un fichier de log de taille # 25 % du buffer mémoire set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # Utilisez ..flush_log_at_trx_commit # à 0 si vous pouvez accepter de perdre # quelques transactions innodb_flush_log_at_trx_commit=1
Vérifiez que le serveur MySQL a les droits de créer ces fichiers dans
le datadir.
Notez que le fichier de données doit être inférieure à 2Go sur certains systèmes d'exploitation. La taille combinée des fichiers de log doit être inférieure à 4Go. La taille combinée des fichiers de données doit être inférieure à 10Go.
Lorsque vous créez pour la première fois une base de données InnoDB,
il est mieux de lancer le serveur depuis la commande en ligne. InnoDB
va afficher des informations sur la création de la base, et vous verrez
commence ça se passe. Voyez la section plus bas, pour une illustration.
Par exemple, sous Windows, vous pouvez démarrer `mysqld-max.exe' avec :
chemin-jusqu-a-mysqld>mysqld-max --console
Oz mettre le fichier `my.cnf' ou `my.ini' sous Windows? Les règles sous Windows sont les suivantes :
WINDIR, e.g,
`C:\WINDOWS' ou `C:\WINNT'. Vous pouvez utiliser la commande SET
de MS-DOS pour afficher la valeur de WINDIR.
Oz placer les fichiers d'options sous Unix? Sous Unix, `mysqld' lit les options dans les fichiers suivants, si ils existent, et dans cet ordre :
--defaults-extra-file=....
`COMPILATION_DATADIR' est le dossier de données de MySQL qui a été spécifié
lors de l'utilisation du script ./configure, avant la compilation de `mysqld'.
(typiquement, `/usr/local/mysql/data' pour une installation binaire, ou
`/usr/local/var' pour une installation source).
Si vous n'êtes pas sûr des chemins où `mysqld' lit les données `my.cnf'
et `my.ini', vous pouvez indiquer le chemin, avec la première option du serveur :
mysqld --defaults-file=your_path_to_my_cnf.
InnoDB forme le chemin de dossier en concaténant innodb_data_home_dir
avec les noms de fichiers, ou le chemin de
innodb_data_file_path, en ajoutant les slash nécessaires. Si le
mot clé innodb_data_home_dir n'est pas mentionné dans
`my.cnf', la valeur par défaut est 'point' `./', ce qui signifie
le dossier de données de MySQL.
Exemple de fichier `my.cnf' avancé Supposons que vous avez un serveur
Linux avec 2 Go de RAM et trois disques de 60 Go (situés dans les
dossiers `/', `/dr2' et `/dr3'. Voici ci-dessous un exemple
de configuration possible pour `my.cnf', de InnoDB.
Notez que InnoDB ne crée pas de dossier : vous devez le créer vous même.
Utilisez la commande Unix ou MS-DOS mkdir pour créer les répertoires de données
et de logs.
[mysqld] # Vous pouvez placer d'autres options MYSQL ici # ... innodb_data_home_dir = # Le fichier de données doivent contenir # vos données et index. # Assurez vous que vous avez l'espace disque # nécessaire. innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend # Utilisez un buffer de taille # 50 à 80 % de votre mémoire serveur # mais assurez vous sous Linux que l'utilisation # totale est inférieure à 2Go set-variable = innodb_buffer_pool_size=1G set-variable = innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /dr3/iblogs # .._log_arch_dir doit être le même que # .._log_group_home_dir innodb_log_arch_dir = /dr3/iblogs set-variable = innodb_log_files_in_group=3 # Utiisez un fichier de log de taille # 15 % du buffer mémoire set-variable = innodb_log_file_size=150M set-variable = innodb_log_buffer_size=8M # Utilisez ..flush_log_at_trx_commit # à 0 si vous pouvez accepter de perdre # quelques transactions innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 #innodb_flush_method=fdatasync #set-variable = innodb_thread_concurrency=5
Notez que nous avons placé deux fichier de données sur des disques
différents. InnoDB va remplir l'espace de tables jusqu'au maximum.
Dans certains cas, les performances seront améliorées si les données
ne sont pas toutes placées sur le même disque physique. Placer les fichiers
de log dans des disques séparés est souvent une bonne chose. Vous pouvez aussi
utiliser des partitions de disques brutes (raw devices) comme fichier
de données. Sur certains Unix, cela va accélérer les accès disques. Voyez le
manuel de InnoDB, pour savoir comment les spécifier dans `my.cnf'.
Attention : en Linux x86, vous devez être très prudent, et
ne pas utiliser trop de mémoire. glibc va autoriser les processus
à dépasser la pile de thread, et votre système va crasher. Cela représente
un risque réel si la valeur de
innodb_buffer_pool_size + key_buffer + max_connections * (sort_buffer + read_buffer_size) + max_connections * 2 MB
est proche de 2 Go ou excède 2 Go. Chaque thread va utiliser une pile
(souvent 2Mo, mais les exécutables MySQL uniquement 256 ko) et dans le pire des
scénarios, sort_buffer + read_buffer_size de mémoire supplémentaire.
Comment optimiser d'autres paramètres du serveur `mysqld'? Les valeurs qui conviennent à la majorité des utilisateurs sont :
skip-locking set-variable = max_connections=200 set-variable = read_buffer_size=1M set-variable = sort_buffer=1M # key_buffer vaut de 5 à 50% # de la RAM disponible, suivant l'utilisation des # tables MyISAM, mais garder # key_buffer + InnoDB # en deça de < 80% de votre RAM set-variable = key_buffer=...
Notez que certains paramètres sont données au format numérique dans `my.cnf' :
set-variable = innodb... = 123, d'autres (chaînes et booléens) sont données
dans un autre format :
innodb_... = ... .
Les significations des paramètres de configuration sont les suivantes :
| Option | Description |
innodb_data_home_dir |
La partie commune du chemin de tous les fichiers de données InnoDB.
Si vous ne mentionnez pas cette option dans `my.cnf',
la valeur par défaut sera celle du dossier de données MySQL.
Vous pouvez aussi spécifier une chaîne vide, et dans ce cas,
les chemins spécifiés dans innodb_data_file_path seront des chemins absolus.
|
innodb_data_file_path |
Chemin individuel vers les fichiers de données, et leur taill. Le chemin
complet de chaque fichier de données est créé en concaténant
innodb_data_home_dir avec les chemins spécifiés ici. La taille
du fichier est spécifiée en méga-octets, ce qui explique la présence
du 'M' après les spécifications ci-dessus.
Depuis la version 3.23.44, vous pouvez donner au fichier une taille
supérieure à 4 Go sur les systèmes d'exploitation qui acceptent les
gros fichiers. Sur certains systèmes, la taille doit être inférieure à
2 Go.
La somme des tailles des fichiers doit faire au moins 10 Mo.
|
innodb_mirrored_log_groups | Nombre de copies identiques de groupe de log que nous conservons. Actuellement, cette valeur doit être au minimum de 1. |
innodb_log_group_home_dir | Le dossier pour les fichiers de logs. |
innodb_log_files_in_group |
Nombre de fichier de logs dans un groupe. InnoDB écrit les logs de manière
circulaire. Une valeur de 3 est recommandée ici.
|
innodb_log_file_size | Taille de chaque fichier de log dans un groupe de log, exprimé en méga-octets. Les valeurs pratiques vont de 1Mo à une fraction de la taille du buffer de log (1 / le nombre de logs, en fait). Plus la taille est grande, moins de points de contrôles seront utilisés, réduisant les accès disques. La taille combinée des logs doit être inférieure à 4 Go sur les systèmes 32 bits. |
innodb_log_buffer_size |
La taille du buffer que InnoDB utilise pour écrire les log dans les
fichiers de logs, sur le disque. Les valeurs utiles vont de 1 Mo à 8 Mo.
Un grand buffer de log permet aux grandes transactions de s'exécuter
sans avoir à écrire de données dans le fichier de log jusqu'à la validation.
Par conséquent, si vous avez de grandes transactions, augmenter cette taille
va réduire les accès disques.
|
innodb_flush_log_at_trx_commit | Normalement, cette option vaut 1, ce qui signifie que lors de la validation de la transaction, les logs sont écrits sur le disque, et les modifications faites par la transaction deviennent permanentes, et survivront un crash de base. Si vous souhaitez réduire la sécurité de vos données, et que vous exécutez de petites transactions, vous pouvez donner une valeur de 0 à cette option, pour réduire les accès disques. |
innodb_log_arch_dir |
Le dossier où les logs complétés doivent être archivés, si nous utilisons l'archivage
de logs. La valeur de ce paramètre doit être actuellement la même que la
valeur de innodb_log_group_home_dir.
|
innodb_log_archive |
Cette valeur doit être actuellement de 0. Au moment de la restauration
de données à partir d'une sauvegarde, à l'aide des log binaires de MySQL,
il n'y a actuellement pas besoin d'archiver les fichiers de log InnoDB.
|
innodb_buffer_pool_size |
La taille de buffer mémoire que InnoDB utiliser pour mettre en cache les données
et les index de tables. Plus cette valeur est grand, et moins vous ferez d'accès
disques. Sur un serveur dédiés, vous pouvez monter cette valeur jusqu'à 80%
de la mémoire physique de la machine. Ne lui donnez pas une valeur trop grande,
car cela peut engendrer l'utilisation de mémoire sur le disque par votre serveur.
|
innodb_additional_mem_pool_size |
La taille du buffer mémoire d'InnoDB, pour ses dictionnaires d'informations,
et ses structures internes de données. Une valeur pratique est 2Mo, mais
plus vous aurez de tables dans votre application, plus vous devrez augmenter
cette valeur. Si InnoDB est à court de mémoire, il va allouer de la mémoire
auprès du système, et écrire des messages dans le fichier de logs MySQL.
|
innodb_file_io_threads |
Nombre de pointeurs de fichier de InnoDB. Normalement, cette valeur doit être de
4, mais sur des disques Windows, les accès peuvent être améliorés en augmentant
cette valeur.
|
innodb_lock_wait_timeout |
Le délai d'expiration des transactions InnoDB, en cas de blocage de verrou,
avant d'annuler. InnoDB détecte automatiquement les blocages de verrous
et annule alors les transactions. Si vous utilisez la commande
LOCK TABLES, ou un autre gestionnaire de table transactionnelles que
InnoDB dans la même transaction, un blocage de verrou peut survenir, et
InnoDB ne pourra pas le détecter. Ce délai est donc pratique pour résoudre
ces situations.
|
innodb_flush_method |
(Disponible depuis 3.23.40 et plus récent)
La valeur par défaut pour cette option est fdatasync.
Une autre option est O_DSYNC.
|
InnoDB
Supposons que vous avez installé MySQL et que vous avez édité le fichier `my.cnf'
de façon à ce qu'il contiennent les paramètres de configuration nécessaires
de InnoDB. Avant de démarrer MySQL, vous devez vérifier que les dossiers que vous
avez spécifié pour les fichiers de données InnoDB et les fichiers de logs existent,
et que vous avez des accès suffisants dans ces dossiers. InnoDB ne peut pas créer
de dossiers, uniquement des fichiers. Vérifiez aussi que vous avez d'espace
disque pour les données et les logs.
Lorsque vous démarrez MySQL, InnoDB va commencer à créer vos fichiers de données
et vos fichiers de log. InnDB va afficher ceci :
~/mysqlm/sql > mysqld InnoDB: The first specified datafile /home/heikki/data/ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728 InnoDB: Database physically writes the file full: wait... InnoDB: datafile /home/heikki/data/ibdata2 did not exist: new to be created InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000 InnoDB: Database physically writes the file full: wait... InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size to 5242880 InnoDB: Log file /home/heikki/data/logs/ib_logfile2 did not exist: new to be created InnoDB: Setting log file /home/heikki/data/logs/ib_logfile2 size to 5242880 InnoDB: Started mysqld: ready for connections
Une nouvelle base de données InnoDB a été créée. Vous pouvez vous connecter
au serveur MySQL avec votre client MySQL habituel, comme mysql.
Lorsque vous arrêtez le serveur MySQL avec `mysqladmin shutdown',
InnoDB va afficher :
010321 18:33:34 mysqld: Normal shutdown 010321 18:33:34 mysqld: Shutdown Complete InnoDB: Starting shutdown... InnoDB: Shutdown completed
Vous pouvez observer vos fichiers de données et de logs, et vous apercevrez
les fichiers créés. Le dossier de log va aussi contenir un petit fichier
appelé `ib_arch_log_0000000000'. Ce fichier est le résultat de la création
de base, à partir duquel InnoDB a désactivé l'archivage des logs.
Lorsque MySQL va être redémarré, l'affichage sera :
~/mysqlm/sql > mysqld InnoDB: Started mysqld: ready for connections
Si InnoDB renvoie une erreur de système d'exploitation lors d'une opération
sur fichier, habituellement le problème est l'un des suivants :
InnoDB.
innodb_data_home_dir ou
innodb_data_file_path.
Si quelque chose se passe mal lors de la création d'une base de données InnoDB,
vous devez effacer tous les fichiers créés par InnoDB. Cela inclut tous les fichiers
de données, tous les journaux, les archives. Dans le cas où vous avez déjà crées
des tables InnoDB, effacez aussi les fichiers `.frm' concernés dans le dossier
de données de MySQL. Vous pourrez alors essayer une nouvelle création de base de
données InnoDB.
InnoDB
Supposons que vous avez démarré le client MySQL avec la commande
mysql test.
Pour créer une table au format InnoDB vous devez spécifier le type
TYPE = InnoDB lors de la création de table, dans la commande SQL :
CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;
Cette commande SQL va créer une table et un index sur la colonne A
dans la base InnoDB constituée par les fichiers de données que vous avez spécifié
dans le fichier de configuration `my.cnf'. De plus, MySQL va créer un fichier
`CUSTOMER.frm' dans le dossier de données de MySQL `test'.
En interne, InnoDB va ajouter une entrée dans son propre dictionnaire de données
une entrée pour la table 'test/CUSTOMER'. De cette façon, vous pouvez
créer plusieurs table avec le même nom de CUSTOMER, mais dans d'autres
bases MySQL, et les noms de seront pas en conflit avec InnoDB.
Vous pouvez demander la quantité d'espace disponible dans l'espace de tables
InnoDB avec la commande de statut de MySQL pour toutes les tables de type
TYPE = InnoDB. La quantité d'espace disponible apparaît dans la section
de commentaire de la commande SHOW. Par exemple :
SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'
Notez que les statistiques que SHOW vous donne sur les tables InnoDB
ne sont que des approximations : elles sont utilisées pour les optimisations
SQL par MySQL. Les tailles réservées d'index et de table, exprimées en octets,
sont précises.
InnoDB
InnoDB n'a pas d'optimisation particulière pour la création d'un fichier
externe d'index. Donc, exporter pour réimporter les données n'apporte pas
de gain de performances. La méthode la plus rapide pour donner à une table
le format InnoDB est de faire les insertions directement dans la table InnoDB,
avec la commande ALTER TABLE ... TYPE=INNODB, ou de créer une table
vide InnoDB avec les mêmes caractéristiques, et d'insérer les lignes avec
la commande INSERT INTO ... SELECT * FROM ....
Pour avoir un meilleur contrôle sur le processus d'insertion, c'est une bonne idée de faire les insertions par portions de table :
INSERT INTO newtable SELECT * FROM oldtable WHERE votre_cle > borne1 AND votre_cle <= borne2;
Une fois que toutes les données ont été insérées, vous pouvez renommer les tables.
Durant la conversion des grandes tables, vous devriez utiliser le buffer InnoDB
en lui donnant une grande taille pour réduire les accès disques. Ne lui donnez
pas une taille supérieure à 80% de votre mémoire physique. Vous devriez
aussi avoir de grand fichiers de log et un buffer de log important.
Assurez vous que vous avez toujours de l'espace pour les données : les tables
InnoDB prennent bien plus de place que les tables MyISAM. Si une commande
ALTER TABLE rencontre un problème d'espace disque, elle va s'annuler,
et cela va prendre des heures si le disque est plein.
Lors des insertions, InnoDB utilise un buffer d'insertion pour fusionner les
index par groupe. Cela économise beaucoup d'accès disques. Lors d'une annulation,
aucun mécanisme de ce type n'est utilisé, et l'annulation peut prendre jusqu'à
30 fois la durée de l'insertion.
Dans le cas d'une annulation, si vous n'avez pas de données importante dans votre
base, il est mieux de terminer le processus et de détruire les données InnoDB
et les fichiers de log, ainsi que le fichier de table `.frm', et de
recommencer, plutôt que d'attendre les millions d'accès disques.
Depuis la version 3.23.43b, InnoDB supporte les contraîntes de clé étrangères.
InnoDB est le premier géstionnaire de tables MySQL qui permet de définir des
contraîntes de clé étrangères pour assurer la cohérence de vos données.
La syntaxe d'une clé étrangère avec InnoDB :
[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
REFERENCES table_name (index_col_name, ...)
[ON DELETE CASCADE | ON DELETE SET NULL]
Les deux tables doivent être de type InnoDB et il doit y avoir un
index dans lesquelles les colonnes de clé étrangère et de clé référencée
sont en premier. InnoDB ne crée pas automatiquement des index pour les clés
étrangères et les clé référencées : vous devez le faire vous même.
Les colonnes correspondantes dans la clé étrangère doivent être de même type interne à InnDB, de façon à ce qu'elle puissent être comparées sans conversion. La taille et le signe des types entiers doivent être les mêmes. La taille des chaînes n'a pas besoin d'être les mêmes.
Depuis la version 3.23.50, vous pouvez aussi associer la clause
ON DELETE CASCADE ou ON DELETE SET NULL
avec les contraintes de clé étrangère.
Si ON DELETE CASCADE est spécifié, et qu'une ligne de la table
parente est effacée, alors InnoDB va automatiquement effacer la
lgine dans la table fille, dont la clé étrangère est égale à la clé référencée
dans la table parente. Si ON DELETE SET NULL est spécifié,
les lignes de la table fille sont automatiquement modifiée de façon à
ce que les colonnes dans la clé étrangère prennent automatiquement la
valeur SQL de NULL.
Depuis la version 3.23.50, InnoDB ne vérifie pas les contraintes de clé
étrangère pour ces clés étrangères, ou les clés qui font référence à
la valeur NULL.
Depuis la version 3.23.50, InnoDB vous permet d'utiliser les guillemets
américains (`) autour des noms de tables et colonnes dans la clause
FOREIGN KEY ... REFERENCES ... mais InnoDB ne prend pas encore en compte
l'option lower_case_table_names que vous pouvez spécifier dans le fichier
`my.cnf'.
Un exemple :
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE SET NULL
) TYPE=INNODB;
Si MySQL retourne une erreur numéro 1005 à la commande CREATE TABLE,
et que l'erreur fait référence à au numéro 150, alors la création de table
a échoué car la clé étrangère est mal formée.
De même, si une commande ALTER TABLE échoue avec un numéro
d'erreur de 150, cela signifie que la définition de la clé étrangère a été
mal formée dans la table.
Depuis la version 3.23.50, InnoDB vous permet d'ajouter une nouvelle
clé étrangère, comme ceci :
ALTER TABLE yourtablename ADD [CONSTRAINT symbol] FOREIGN KEY (...) REFERENCES anothertablename(...)
N'oubliez pas de commencer par créer vos index.
En InnoDB versions inférieures à 3.23.50, ALTER TABLE
ou CREATE INDEX ne doivent pas être utilisée avec les tables
qui ont une clé étrangère, ou qui sont référencées dans une clé étrangère :
toute commande ALTER TABLE supprime les contraintes de clé
étrangère définie dans la table. Vous de devez pas utiliser la commande
ALTER TABLE dans la table référencée non plus, mais utilisez plutôt
DROP TABLE et CREATE TABLE pour modifier le schéma.
Lorsque MySQL fait un ALTER TABLE il peut utiliser en interne la commande
RENAME TABLE, et cela va poser un problème à la clé étrangère à laquelle
la table se réfère. Une commande CREATE INDEX est traitée par MySQL
comme une commande ALTER TABLE, et les mêmes restrictions s'appliquent.
Lorsqu'il fait des vérifications de clé étrangères, InnoDB pose des
verrous de lignes sur les lignes filles et mères qu'il doit utiliser.
InnoDB vérifie les contraintes immédiatement : la vérification n'est pas
reportée à la validation de la transaction.
InnoDB vous permet d'effacer n'importe quelle table, même si cela
casse la cohérence d'une clé étrangère, qui fait référence à la table.
Lorsque vous effacez une table, les contraintes qui en faisaient parties
sont aussi effacées.
Si vous recréez une table qui a été effacée, elle doit avoir une définition qui satisfont les contraintes de clé étrangères. Elle doit avoir les bons noms et types de colonnes, ainsi que les index nécessaires. Si ces conditions ne sont pas remplies, MySQL retourne une erreur numéro 1005, et fait référence au numéro de message 150.
Depuis la version 3.23.50, InnoDB retourne la définition de contrainte
de clé étrangère lorsque vous appelez la commande
SHOW CREATE TABLE yourtablename
De plus, `mysqldump' produit la définition correcte pour les tables dans l'export, et n'oublie pas les clés étrangères.
Vous pouvez aussi lister les clés étrangères pour une table
T avec
SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'
Les contraintes de clé sont listées dans les commentaires de la table.
InnoDB
Depuis la version 3.23.50 et 4.0.2, vous pouvez spécifier le dernier fichier
de données InnoDB dans le fichier autoextend. Alternativement, vous pouvez
augmenter votre espace de données en spécifiant d'autres fichiers de données. Pour cela,
vous devez arrêter le serveur MYSQL, éditer le fichier
`my.cnf' pour y ajouter un nouveau nom de fichier dans
innodb_data_file_path, puis redémarrer le serveur MySQL.
Actuellement, vous ne pouvez pas retirer de fichier de données à InnoDB.
Pour réduire la taille de votre base de données, vous devez utiliser l'utilitaire
`mysqldump' pour exporter toutes vos données de tables, créer une
nouvelle base, et réimporter toutes vos données dans cette nouvelle base.
Si vous voulez changer le nombre ou la taille de vos fichiers de log InnoDB,
vous devez éteindre le serveur MYSQL et vous assurer qu'il s'est arrété sans
erreur. Puis, copiez les anciens fichiers de log dans une archive, car si
vous rencontrez un problème ultérieurement, vous en aurez besoin pour
restaurer votre base. Effacer les anciens fichiers de log du dossier de logs,
éditez le fichier `my.cnf', et redémarrez le serveur MySQL. InnoDB
vous indiquera au démarrage qu'il va créer de nouveaux fichiers de log.
InnoDBLe secret de la gestion de bases de données sereine réside dans les sauvegardes régulières.
InnoDB Hot Backup est un outil de sauvegarde que vous pouvez utiliser
pour faire des sauvegarde des bases de données InnoDB, lorsqu'elles
sont utilisées. InnoDB Hot Backup ne vous impose pas de stopper le
serveur, et ne pose aucun verrou ou ne perturbe votre utilisation
normale des tables. InnoDB Hot Backup est un outils supplémentaire,
qui n,est pas libre, et qui n'est pas inclus dans la distribution
standard de MySQL. Voyez le site web de InnoDB Hot Backup
http://www.innodb.com/hotbackup.html
pour plus de détails et des captures d'écran.
Si vous êtes capables d'arréter votre serveur MySQL, alors pour faire une sauvegarde binaire de vos bases de données, vous devez suivre les instructions suivantes :
InnoDB dans une archive.
InnoDB dans une archive.
En plus de réaliser des copies des fichiers binaires tels que décrit ci-dessus, il est recommandé de réaliser des sauvegardes textuelles de vos tables avec `mysqldump'. La raison est qu'un fichier binaire peut être corrompu sans que vous ne vous en rendiez compte. Les tables sauvées dans un fichier texte sont stockées dans un format humainement lisible, bien plus simple qu'un fichier de base. Il est alors facile de répérer une corruption des données dans ce fichier, et comme le format de fichier est simple, il est facile de le corriger.
C'est une bonne idée de prendre des copies de vos tables en même temps que vous faites une sauvegarde binaire. Vous devez éteindre tous les clients de votre base pour obtenir un bilan cohérent de vos tables. Puis, vous pouvez faire les sauvegardes binaires, et vous aurez ainsi une sauvegarde cohérente de votre base, en deux formats.
Pour être capable de restaurer des données de votre base InnoDB jusqu'à
présent, à partir des sauvegardes binaires ci-dessus, vous devez faire tourner
le serveur MySQL en ayant activé le log général et l'archivage des logs.
Ici, par "log général", nous considérons le mécanisme général de log de MySQL,
qui est indépendant des logs InnoDB.
Pour reconstruire une table après le crash d'un serveur MySQL, la seule
chose que vous devez faire est de le redémarrer. InnoDB va automatiquement
vérifier les fichiers d'historiques, et effectuer une mise à jour des données.
InnoDB va automatiquement annuler les transactions non validées, qui étaient
en cours au moment du crash. Durant la restauration de la table, InnoDB va
afficher des données comme celles-ci :
~/mysqlm/sql > mysqld InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 13674004 InnoDB: Doing recovery: scanned up to log sequence number 0 13739520 InnoDB: Doing recovery: scanned up to log sequence number 0 13805056 InnoDB: Doing recovery: scanned up to log sequence number 0 13870592 InnoDB: Doing recovery: scanned up to log sequence number 0 13936128 ... InnoDB: Doing recovery: scanned up to log sequence number 0 20555264 InnoDB: Doing recovery: scanned up to log sequence number 0 20620800 InnoDB: Doing recovery: scanned up to log sequence number 0 20664692 InnoDB: 1 uncommitted transaction(s) which must be rolled back InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx no 16745 InnoDB: Rolling back of trx no 16745 completed InnoDB: Rollback of uncommitted transactions completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Apply batch completed InnoDB: Started mysqld: ready for connections
Si votre base se corrompt ou que votre disque est en panne, vous devez faire une restauration depuis une sauvegarde. En cas de corruption, vous devez trouver d'abord une sauvegarde qui n'est pas corrompue. A partir de cette sauvegarde, effectuer la restauration avec les fichiers d'historique généraux de MySQL, suivant les instructions du manuel MySQL.
InnoDB utilise un mécanisme de contrôle appelé points de contrôle
flou. InnoDB va écrire des blocs de données modifiées depuis un buffer
vers le disque par petits paquets : il n'y a pas besoin de tout écrire
en une seule fois, ce qui en général, conduit à l'arrêt du traitement
des autres instructions pour quelques instants.
Durant une restauration de base, InnoDB recherche un point de contrôle écrit
dans les fichiers de log. Il sait que toutes les modifications de la base
placées avant ce point de contrôle sont aussi présentes sur le disque de
la base. Puis, InnoDB analyse le fichier de log, et applique les modifications
qui ont eu lieu depuis le point de contrôle.
InnoDB écrit dans les fichiers de log en mode circulaire. Toutes les
modifications validées qui font que le buffer de MySQL est différent de
la version sur le disque doivent être disponibles dans les fichiers de log,
au cas où InnoDB aurait besoin pour une restauration. Cela signifie que
lorsque InnoDB commence à réutiliser le fichier d'historique, il doit commencer
par s'assurer que le disque a reçu les modifications qui sont dans le buffer.
En d'autres termes, InnoDB doit placer un point de contrôle, et souvent,
cela se traduit par l'écriture de données du buffer sur le disque.
Ceci explique pourquoi utiliser de grands fichiers de log peut éviter des accès disques pour les points de contrôle. Il est recommandé d'utiliser une taille de fichier d'historique aussi grande que le buffer, voire même plus grande. L'inconvénient des grands fichiers est que la restauration dure alors plus longtemps, puisqu'il y a plus de modifications à appliquer.
Les fichiers de données et de logs de InnoDB sont compatibles en mode binaire
sur toutes les plate-formes si le format des nombre à virgule flottante est
le même. Vous pouvez déplacer une base de données InnoDB en copiant tous les
fichiers concernés, que nous avons déjà listés dans la section précédente
traitant de la sauvegarde d'une base de données. Si les formats des nombres
à virgules flottantes sont différents mais que vous n'avez pas utilisé les
types de données FLOAT ou DOUBLE dans vos tables alors la
procédure est là même : copiez juste les fichiers concernés.
Si les formats sont différents et que vous utilisez de tels types de données,
vous devez utiliser `mysqldump' et `mysqlimport' pour transférer
les tables.
Un bon moyen d'avoir de bonnes performances est de couper le mode auto-commit quand vous importez des données dans votre base de données, en supposant que votre tablespace possède assez d'espace pour la grande partie d'annulations (rollback) que la grande transaction importée génère. Ne faites le commit qu'après avoir importé une table entière, ou un segment de table.
InnoDB
Le modèle transactionnel d'InnoDB a pour but de combiner les avantages
des bases de données multi-version aux verrouillages traditionnels en
deux phases. InnoDB fait un verrouillage de ligne, et exécute les requêtes
par défaut avec des lectures cohérentes non bloquante, de la même
façon qu'Oracle. Les verrous InnoDB sont stockés de manière efficace,
pour que l'escalade de transaction ne soit pas nécessaire : typiquement,
plusieurs utilisateurs sont autorisés à verrouiller toutes les lignes dans
une base, ou un sous ensemble aléatoire de ligne, sans que InnoDB ne
soit à court de mémoire.
Avec InnoDB, toutes les activités des utilisateurs sont placées dans
des transactions. Si le mode de validation automatique de MySQL est utilisé,
chaque requête SQL représente une transaction. MySQL démarre
toujours une nouvelle connexion avec de mode activé.
Si le mode de validation automatique est désactivé, avec la commande
SET AUTOCOMMIT = 0, alors nous considérons que l'utilisateur
a toujours une transaction ouverte. Si il utilise les commandes
COMMIT ou ROLLBACK, cela va terminer la transaction
courante, et en démarrer une nouvelle. Les deux commandes vont
libérer les verrous posés durant la transaction courante.
Une validation COMMIT signifie que les modifications faites
durant la transaction sont rendus permanents, et accessibles
aux autres utilisateurs. Une annulation ROLLBACK,
d'un autre coté, annule toutes les modifications faites durant
la transaction.
Si la connexion est en mode AUTOCOMMIT = 1, alors l'utilisateur
peut réaliser une transaction à requêtes multiples en commençant par
la commande BEGIN et en terminant la transaction par COMMIT
ou ROLLBACK.
En termes de niveau d'isolations de transaction SQL-1992,
le mode par défaut d InnoDB est REPEATABLE READ.
Depuis la version 4.0.5, InnoDB offre 4 niveaux d'isolation de transaction
différents, tels que décrits dans le standard SQL 1992.
Vous pouvez choisir le niveau d'isolation par défaut du serveur
[mysqld] dans le fichier de configuration `my.cnf' :
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
Un utilisateur peut modifier le niveau d'isolation d'une transaction ou des nouvelles connexions avec la commande suivante :
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE}
Notez qu'il n'y a pas de tirets dans les noms de niveaux de la syntaxe
SQL. Si vous spécifiez le mot réservé GLOBAL dans la commande
ci-dessus, cela va configurer les valeurs pour les nouvelles connexions,
mais ne modifiera pas les valeurs des connexions déjà ouvertes.
Tout utilisateur est libre de changer le niveau d'isolation de sa
session, même durant la transaction.
Dans les versions inférieure à 3.23.50, SET TRANSACTION n'avait
aucun effet sur les tables InnoDB. Dans les versions inférieures à 4.0.5,
seuls REPEATABLE READ et SERIALIZABLE étaient disponibles.
Vous pouvez connaître les valeurs de niveaux d'isolations global et courant avec la commande :
SELECT @@global.tx_isolation; SELECT @@tx_isolation;
Durant le verrouillage de ligne, InnoDB utilise le verrouillage
de clé suivante. Cela signifie qu'en plus des lignes de l'index,
InnoDB verrouille aussi l'espace 'avant' un index, pour éviter
les insertions d'un autre utilisateur. Un verrou de ligne suivante
signifie le verrouillage de l'index et de l'intervalle entre
cet index et le précédent. Un verrouillage d'espacement indique
un verrouillage de l'espace entre deux index.
Voici une description plus détaillée des différents niveaux d'isolation
des transactions de InnoDB :
READ UNCOMMITTED, qui est aussi appelé la 'lecture incohérente' :
les commandes SELECT non bloquantes sont exécutées sans vérifier
la présence de versions antérieures de la ligne : par conséquent,
ces lectures ne sont pas cohérentes avec ce niveau d'isolation.
Sinon, ce niveau fonctionne exactement comme READ COMMITTED.
READ COMMITTED
Le niveau d'isolation qui se rapproche de celui d'Oracle.
TOutes les commandes SELECT ... FOR UPDATE et
SELECT ... LOCK IN SHARE MODE
ne verrouille que les lignes d'index, et non pas les espaces
qui les précèdent, et de cette manière, ils autorisent les insertions
entre deux lignes verrouillées.
Les commandes UPDATE et DELETE qui utilisent un index
unique avec une condition de recherche, ne vont verrouiller que les
index trouvés, et non pas les espaces précédent ces verrous.
Mais avec les commandes UPDATE et DELETE en
mode intervalle, InnoDB va poser des verrous de clé suivante,
ou verrous d'espacement, et bloquer les insertions d'autres
utilisateurs dans ces espaces. Ceci est nécessaire pour éviter
la présence de lignes 'fantômes', qui sont néfastes pour la réplication.
Les lectures cohérentes fonctionnent comme sous Oracle :
chaque lecture cohérente, même durant une transaction, fixe et lit
une version récente de données.
REPEATABLE READ est le niveau d'isolation par défaut d'InnoDB.
SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE,
UPDATE, et DELETE qui utilisent un index unique comme
condition de recherche, ne vont verrouiller que la ligne d'index trouvée,
et non pas l'espace qui la prépare. Sinon, ces opérations vont employer
le verrouillage de clés suivante, pour bloquer les insertions parasites.
En lectures cohérentes il y a une différence importante avec
le niveau d'isolation précédent : avec ce niveau, toutes les
lectures faites dans une même transaction sont faîtes avec la version
établit lors de la première lecture. Cette convention signifie que si
vous faites plusieurs lectures SELECT dans une même
transaction, ces SELECT seront cohérents entre eux.
SERIALIZABLE Ce niveau ressemble au précédent, mais toutes
les lectures SELECTs sont implicitement converties en
SELECT ... LOCK IN SHARE MODE.
Une lecture cohérente signifie que InnoDB utilise son système de
multi-versionage pour présenter à une requête, une photo de la base
à un moment donné. La requête va alors voir les différentses modifications
apportées par les transactions qui ont eu lieu avant cette date,
et masquera les transactions ont eu lieu depuis, ou n'ont pas été
archivées. L'exception à cette règle est que la requête verra les
modification apportées la requête qui a émis cette commande.
Si vous utilisez le niveau d'isolation REPEATABLE READ,
alors les lectures cohérentes dans une même transaction liront
le même bilan. Vous pouvez obtenir un bilan plus récent pour vos
requêtes en archivant la requête courante, et en démarrant une
autre.
Les lectures cohérentes sont le mode par défaut de traitement des
commandes SELECT par InnoDB avec les niveaux d'isolation
READ COMMITTED et REPEATABLE READ. Une lecture cohérente
ne pose aucun verrou sur les tables auxquelles elle accède, et par conséquent,
les autres utilisateurs peuvent librement modifier ces tables en même
temps qu'une lecture cohérente est exécutée.
Une lecture cohérente n'est pas toujours pratique, dans certaines
circonstances. Supposons que vous voulez ajouter une ligne dans
votre table CHILD, et vous assurer que l'enfant a déjà
un parent dans la table PARENT.
Supposez que vous utilisiez une lecture cohérente, pour lire la table
PARENT, et que vous découvrez le parent de l'enfant dans cette
table. Pouvez vous ajouter tranquillement la ligne fille dans la table
CHILD? Non, car il peut arriver que durant ce temps, un autre
utilisateur a effacé la ligne parente dans la table PARENT, et
vous n'en êtes pas conscient.
La solution est d'exécuter la commande SELECT en mode verrouillage,
avec LOCK IN SHARE MODE.
SELECT * FROM PARENT WHERE NAME = 'Jones' LOCK IN SHARE MODE;
Effectuer une lecture en mode partagé signifie que vous allons lire
les dernières données disponibles, et que nous allons poser un verrou
sur les lignes que nous lisons. Si les dernières données appartiennent
à une transaction non validée d'un autre utilisateur, nous allons attendre
que ce soit fait. Un verrou partagé évite que les autres utilisateurs
ne modifient ou n'effacent la ligne que nous lisons. Après que nous ayons
obtenu le nom du parent, nous pouvons tranquillement ajouter le nom
du fils dans la table CHILD, et valider notre transaction.
Cet exemple montre comment implenter l'intégrité référentielle
dans votre application.
Ajoutons un autre exemple : nous avons un champs compteur dans la table
CHILD_CODES que nous utilisons pour assigner un identifiant
unique à chaque enfant que nous ajoutons dans la table CHILD.
Evidemment, en utilisant une lecture cohérente ou une lecture
partagée pour lire la valeur courante du compteur n'est pas une
bonne idée, car deux utilisateurs de la base peuvent simultanément
lire la même valeur de compteur, et nous allons obtenir une erreur
de clé doublon lorsque nous ajouterons le second des deux fils.
Dans ce cas, il y a deux bonnes méthodes pour implémenter la lecture
et l'incrémentation du compteur : (1) modifiez le compteur d'une unité,
et lisez le après cela
ou (2) lisez le compteur d'abord, avec un verrou en mode
FOR UPDATE, puis incrémentez le :
SELECT COUNTER_FIELD FROM CHILD_CODES FOR UPDATE; UPDATE CHILD_CODES SET COUNTER_FIELD = COUNTER_FIELD + 1;
Une commande SELECT ... FOR UPDATE va lire les dernières données
disponibles pour chaque ligne, et pose un verrou dessus en même tant qu'il
lit. De cette façon, il pose le même verrou que la commande UPDATE.
Avec le verrouillage de ligne, InnoDB utilise un algorithme appelé
le verrouillage de la clé suivante. InnoDB fait un verrouillage de
telle sorte que lorsqu'il fait une recherche ou un scan d'index,
il pose des verrous partagés ou exclusifs sur les lignes d'index
qu'il rencontre. Par conséquent, les verrous de lignes sont plus
exactement appelés des verrous d'index.
Les verrous que InnoDB posent affectent aussi l'espace qui le
sépare de la ligne suivante. Si un utilisateur a un verrou partagé
ou exclusif sur une ligne L dans un index, alors un autre utilisateur
ne peut faire d'insertion immédiatement avant la ligne L, dans l'ordre
de l'index. Ce verrouillage est fait pour éviter le problème de la
ligne fantôme. Supposons que je veuille lire et verrouiller tous les
enfants ayant un identifiant supérieur à 100 dans la table
CHILD, puis modifier certains champs des lignes ainsi identifiées :
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;
Supposons qu'il y ait un index sur la table CHILD, sur la colonne
ID. Notre requête va scanner l'index à partir de la première ligne
où ID est plus grand que 100. Maintenant, si le verrou posé sur les
lignes d'index n'empêche pas l'utilisation des intervalles entre les lignes
d'index, un nouvel enfant peut être inséré dans la table durant la lecture.
Et maintenant, si ma transaction exécute la commande :
SELECT * FROM CHILD WHERE ID > 100 FOR UPDATE;
je vais trouver un nouvel enfant dans le résultat de ma requête. Ceci va à l'encontre du principe d'isolation des transactions : une transaction doit être capable de s'exécuter sans que les lectures soient affectées durant ce temps. Si vous considérons un intervalle de données, alors la nouvelle ligne 'fantôme' va casser le principe d'isolation.
Lorsque InnoDB scanne un index, il va aussi verrouille l'espace après
la dernière ligne de l'index. C'est ce qui est arrivé dans l'exemple
ci-dessus : le verrou posé par InnoDB va éviter qu'une insertion n'intervienne
dans la table où ID serait plus grand que 100.
Vous pouvez utiliser le verrouillage de la clé suivant pour implémenter des vérifications d'unicité dans votre application : si vous lisez des données en mode partagé, et que vous ne voyez pas de duplicata de la ligne que vous allez insérer, alors vous pouvez l'insérer sans problème, en sachant que le verrou de clé suivante va vous garantir que durant ce temps, personne ne pourra insérer de ligne, qui déboucherait sur un duplicata de la votre. Le verrou de clé suivante permet de verrouiller aussi la non-existence de ligne dans votre table.
InnoDBSELECT ... FROM ... : ceci est une lecture cohérente, qui lit
un bilan de la base, et ne pose aucun verrou.
SELECT ... FROM ... LOCK IN SHARE MODE : pose un verrou partagé sur
la prochaine clé sur tous les index que la lecture rencontre.
SELECT ... FROM ... FOR UPDATE : pose un verrou exclusif sur
la prochaine clé sur tous les index que la lecture rencontre.
INSERT INTO ... VALUES (...) : pose un verrou exclusif
sur la ligne insérée. Notez que ce verrou n'est pas un verrou de clé,
et il n'empêche pas les autres utilisateurs d'insérer des lignes.
Si une erreur de clé double apparaît, un verrou sera posé partagé
sera posé sur la ligne doublon.
INSERT INTO T SELECT ... FROM S WHERE ... : pose un verrou exclusif
sur chaque ligne inséré dans T. Effectue la recherche sur
S sous la forme d'une lecture cohérente, mais pose un verrou partagé
sur l'index de prochaine clé de S si MySQL a activé le log. InnoDB
doit poser un verrou dans cette dernière situation, car en cas d'exécution
des instructions dans une phase de restauration, toutes les requêtes
doivent être exécutées dans le même ordre.
CREATE TABLE ... SELECT ... effectue une commande SELECT
sous la forme d'une lecture cohérente, ou avec des verrous partagés,
comme précédemment.
REPLACE est similaire à une insertion, si il n'y a pas de collision
sur la clé unique. Sinon, une verrou exclusif sur l'index de prochaine clé
est posé sur la ligne qui sera modifiée.
UPDATE ... SET ... WHERE ... : pose un verrou exclusif sur l'index
de prochaine clé, à chaque ligne que la recherche trouve.
DELETE FROM ... WHERE ... : pose un verrou exclusif sur l'index de
prochaine clé à chaque ligne que la recherche trouve.
FOREIGN KEY est définie sur une table,
toute insertion, modification ou effacement qui requiert la vérification
de la contrainte va poser un verrou de ligne sur la ligne dont il doit
vérifier la contrainte. De plus, dans certains cas où la contrainte échoue,
InnoDB pose ces verrous.
LOCK TABLES ... : pose un verrou de table. L'implémentation
de la couche MySQL pose ce verrou. La détection automatique des blocages
de InnoDB ne peut détecter les blocages lorsque de tels verrous
sont posés. Voyez la section suivante.
De plus, comme MySQL ne connaît pas le verrouillage de lignes, il est possible
que vous posiez un verrou sur une table où un autre utilisateur a déjà
posé un verrou. Mais cela ne pose pas de problème quant
à l'intégrité de la requête.
See section 7.5.13 Restrictions sur les tables InnoDB.
InnoDB détecte automatiquement les blocages de transactions et annule une
ou plusieurs transactions pour l'éviter. Depuis la version 4.0.5, InnoDB
va essayer d'annuler les petites transactions. La taille de la transaction
est déterminée par le nombre de lignes qu'elle a inséré, modifié ou effacé.
Avant la version 4.0.5, InnoDB annulait toujours la transaction
qui avait posé le dernier verrou avant le blocage, c'est à dire, un cycle
dans le graphe des transactions.
InnoDB ne peut pas détecter les blocages causés par la commande MySQL
LOCK TABLES, ou si un verrou est posé par un autre gestionnaire
de table que InnoDB. Vous devez résoudre ces situations avec l'option
innodb_lock_wait_timeout du fichier de configuration `my.cnf'.
Lorsque InnoDB effectue une annulation de transaction, tous les verrous
de cette transaction sont libérés. Cependant, si une commande SQL est annulée
pour cause d'erreur, certains verrous de la transaction peuvent être conservés.
Deci est dû au fait que InnoDB enregistre les verrous dans un format qui
ne permet pas de savoir qui l'a posé.
InnoDB
Supposons que vous exécutez MySQL avec le niveau d'isolation des transactions
de REPEATABLE READ. Lorsque vous demandez une lecture cohérente avec une
commande SELECT ordinaire, InnoDB va donner à votre transaction une
date jalon, en fonction de laquelle votre requête va voir la base. Ainsi, si une
transaction B efface une ligne après l'assignation de ce jalon, vous ne verrez
pas cette ligne. De même pour une insertion ou une modification.
Vous pouvez faire avancer votre date jalon en validant votre transaction et
en exécutant un autre SELECT.
Cela s'appelle le contrôle simultané multi-versions.
User A User B
SET AUTOCOMMIT=0; SET AUTOCOMMIT=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set;
COMMIT;
SELECT * FROM t;
---------------------
| 1 | 2 |
---------------------
De cette façon, A voir la ligne insérée par B uniquement lorsque B a validé son insertion, et que A a validé sa propre transaction, de façon à ce que la date jalon soit plus récente que la validation de B.
Si vous voulez avoir une vision aussi ``fraîche'' que possible de votre base, vous devez utiliser le verrou en lecture :
SELECT * FROM t LOCK IN SHARE MODE;
Les blocages de verrous sont un problème classique des bases de données transactionnelles, mais ils ne sont pas dangeureux, à moins qu'ils ne se répètent si souvent que vous ne puissiez pas exécuter tranquillement certaines transactions. Normalement, vous devriez écrire vos applications de manière à ce qu'elles soient prêtes à tenter à nouveau une transaction si la transaction est annulée pour cause de blocage.
InnoDB utilise un verrouillage de lignes automatique. Vous pouvez obtenir
des blocages sur une ligne, même si votre transactions ne fait que modifier
ou insérer une seule ligne. Cela est dû au fait que les opérations ne sont
pas réellement 'atomiques' : elles posent automatiquement des verrous
(éventuellement plusieurs) sur les lignes d'index de l'enregistrement
concerné.
Vous pouvez gérer ces blocages et réduire leur nombre avec les trucs suivants :
SHOW INNODB STATUS avec MySQL version supérieure à 3.23.52
et 4.0.3, pour déterminer la cause du dernier blocage. Cela peut vous aider à
optimiser votre application pour les éviter.
SELECT ... FOR UPDATE
ou ... LOCK IN SHARE MODE, essayez d'utiliser un niveau d'isolation
plus bas comme READ COMMITTED.
EXPLAIN SELECT pour déterminer si MySQL choisit les
bons index pour vos requêtes.
SELECT des données un peu
anciennes, n'ajoutez pas la clause FOR UPDATE ou
LOCK IN SHARE MODE. Utiliser le niveau d'isolation
READ COMMITTED est bon ici, car chaque lecture cohérente
dans la même transaction lira avec des données aussi fraîches que possible
à chaque fois.
LOCK TABLES t1 WRITE, t2 READ, ... ;
[faire quelquechose avec les tables t1 et t2]; UNLOCK TABLES.
Les verrous de niveau de table forcent les transactions à se mettre
en ligne, et les blocages sont évités. Notez que LOCK TABLES
démarre implicitement une transaction, tout comme BEGIN,
et UNLOCK TABLES termine une transaction avec un COMMIT.
InnoDB de détection des blocages fonctionne, car
le sémaphore est un verrou de ligne. Avec les verrous de niveau de table
de MySQL, nous devons nous résoudre à une méthode de délai
d'expiration pour résoudre un verrou.