Tables et index

Une table est la structure qui va stocker les informations par colonnes. Chaque colonne disposant d’un type particulier.

Creer une table

CREATE TALE nom_table (
	definition_colonne[,
	...]
) options_table ;

Une table doit contenir au minimum une colonne.

Une table peut contenir au minimum 0 ligne.

Définition de colonnes

Syntaxe :

nom type options

Il existe trois grands type de données en MySQL :

• les nombres
• les chaines
• le temporel

Les nombres

Les nombres se subdivisent en deux catégories : les entiers et les décimaux

Entiers

ATTENTION : Pour le type BIT, le nombre d’octets alloué a la colonne est spécifié par la valeur mise entre parenthese apres BIT(n)

Les décimaux

ATTENTION : Pour le type DECIMAL(m,n), on définit m et n les limites. m : le nombre de chiffre total et n : le nombres de chiffres apres la virgule

Ex : DECIMAL(5,2) => 666.23

ATTENTION : Les FLOAT et DOUBLE sont victimes d’erreurs : C’est le processeur qui calcul.

Les chaines

Il exsite deux catégories de chaines : les chaines textuelles et les chaines binaires.

N = Taille max de la chaine en nombre de caracteres

C = Taille de la chaine à stocker en nombre de caracteres

T = Taille du plus grand caractere du jeu de caractere

T’ = Taille réelle du caractere du jeu de caractere

Les types Texte

Les types binaires

Les chaines spéciales

• ENUM : Permet de spécifier les valeurs autorisées dans la colonne. Attention : 0 étant réservé pour la chaine vide

  Sa capacité de stockage varie entre 1 et 2 octets. Il est possible de spécifier 65535 valeurs possibles

  Lors d’un ORDER BY, MYSQL les classes par ordre de référence et non par ordre de valeur.

  Si on veut les classer par ordre de valeur, il faudra spécifier : CAST( champs VARCHAR( x ) )

  ENUM('enum1','enum2',...)

• SET : Permet de spécifier les valeurs autorisées dans la colonne. A contrario de ENUM, SET peut mettre plusieurs valeurs par colonne.

  Sa capacité de stockage varien entre 1 et 8 octets. Il est possible de spécifier 64 valeurs possibles.

  SET('set1','set2',...)

Les types Temporels

Les options

Les options pour les numériques

ALTER TABLE t1 AUTO_INCREMENT x;

Les options pour les chaines

Les options pour les temporels

ATTENTION : Dans la version >4.0, le premier TIMESTAMP de chaque tables fonctionnait comme si on avait un DEFAULT CURRENT_TIMESTAMP. Et un ON UPDATE CORRENT_TIMESTAMP

Les deux options ne sont pas cumulables.

Pour contourner ce probleme, il suffira de mettre à NULL la valeur d’une colonne de type TIMESTAMP NOT NULL. Et ceci permettra un TIMESTAMP courant.

Depuis MySQL 5.0, on peut ajouter un déclencheur (trigger) permettant de mettre à jour les differentes insertions

Avec la variable systeme TIME_ZONE, il est possible d’afficher la date dans un fuseau horaire différent.

Les options tous types

Definition des options de table

Les options de table permettent de définir le moteur de stockage d’une table. On utiilisera la mot clé ENGINE qui est remplacé par TYPE dans la version 5.0

CREATE TABLE table (
...
) {ENGINE|TYPE} = moteur;

Si on ne spécifie pas de moteur de stockage à la création de table, ce dernier sera MyISAM

Les données concernant les tables se retrouvent dans un ou plusieurs fichiers dans le repertoire de la base de données. Dans MySQL, chaque table dispose au minimum d’un fichier *.frm et ce quelque soit le moteur de stockage.

Ce fichier contient le format de la table : liste des colonnes, types, index, options

Sur MySQL, il existe différents types de moteurs de stockages :

• MyISAM
• InnoDB
• Memory
• Merge
• Archive
• BerkeleyDB (transactionnel, jusqu’a 5.0)/li>
• Falcon (transactionnel, à partir de 6.0)
• Maria (transactionnel, basé sur MyISAM)
• FEDERATED

MyISAM

C’est le moteur de stockage par défaut sur MySQL. Il ne peut etre désactivé

Lors de la création d’une table MyISAM, MySQL crée trois fichiers

Que peut on dire de MyISAM ?

Moteur efficace quand le taux d’ecriture est faible.

Supporte les techniques de verrouillage de tables : plusieurs personnes peuventlire la table en meme temps.

Avec ce moteur, si un utilisateur veux ecrire, tout les autres utilisateurs ne peuvent plus ecrire. L’interblocage est possible : MyISAM bloque toute les tables afin d’eviter de les bloquer les unes apres les autres.

MyISAM est le seul moteur de stockage supportant les index FULL TEXT

Il supporte :les données spatiales,les auto_increment

les tables de type MyISAM peuvent etre compressées (avec myisampack : passera les tables en lecture seule)

La taille maximum est de 65536To par tables.

Les fichiers sont portables d’une plateforme à une autre

InnoDB

InnoDB à été developpé par Oracle. Ce syteme de fichier supporte les transaction, les clés etrangères et respecte la norme ACID.

Ce type de fichiers permet une auto-réparation lors d’un crash grace au journal de requetes.

La taille maximum pour ce type de moteur est de 64To par tables.

Les tables de type InnoDB sont performantes dans les environnements mixte : lecture/ecriture

Les fichiers sont portables d’une plateforme à une autre

InnoDB peut etre désactivé. En désactivant InnoDB, on augmente les performances du serveur (skip-innodb)

Memory

Le moteur Memory stocke les données et les index dans la memoire vive.

C’est le moteur de stockage le plus performant pour la lecture verouillage de table mais pas à l’écriture.

Très interressant pour un volume de données peut volumineux.

Les données stockées par la table seront perdues au redémarrage.

On peut choisir le type d’index à créer : HASH ou BTREE.

La capacité de stockage est variable en fonction de la quantité de RAM.

ATTENTION : On ne peut utiliser les types TEXT et BLOB.

Merge

Table virtuelle regrouppant une collection de tables MyISAM

Ce comporte de la meme facon que les tables MyISAM

Dispose d’une syntaxe différente :

CREATE TABLE t1 (
...
) {ENGINE|TYPE} = MERGE UNION (T2,T3...);

ATTENTION : Les différentes tables doivent avoir la meme structure sur touts points.

Contient un *.frm et un *.mrg qui contient les chemins de chaques tables rattachées

ATTENTION : Lors de l’insertion : il faudra spécifier lors de la création du merge la méthode d’insertion avec : INSERT_METHOD={FIRST|LAST|NO} et ceci permettra d’ajouter les données dans l’ordre de création de table.

Archive

Permet de stocker de grande quantité d’informations.

Les données sont donc compressées (taux ~40%)

Les seules opérations possible dans ses tables sont SELECT et INSERT

Contient un *.frm, un *.arm (ARchive Metadata), un *.arz (ARchive Zip)

Ce moteur de stockage est beaucoup plus performant dans la compression que celui de myisampack.

BerckleyDB

BerckelyDB à été crée par Oracle, supporte les transactions et respecte les normes ACID et l’auto-recuperation

Sont systeme de vérouillage est un vérouillage par table, page etant ensemble des lignes successives dans la table

Ce fichier génére un *.frm et un *.db.

Il n’est pas compatible de plate-forme à plate forme.

Berckley enregistre sont propre chemin

FEDERATED

FEDERATED permet d’acceder à des tables se trouvant sur d’autres serveurs avec la syntaxe :

CREATE TABLE t1 (
...
) {ENGINE|TYPE} = FEDERATED CONNECTION ="chaine de connexion"
//chaine de connexion
mysql://user:password@host:port/db/table

Les tables temporaires

Elles sont visibles unquement par les createurs

Sont suprimées automatiquement lors de la cloture de la session

Peut porter le meme nom qu’une table existente (non temporaire), elle primera par rapport à cette derniere

Il est aussi possible de spécifier l’auto increment

CREATE TEMPORARY TABLE myTable (
...
) AUTO_INCREMENT = 100;

Il est aussi possible de spécifier un commentaire, son jeu de caracter et son ordre de tri

Les options de tables sont séparées par des espaces

CREATE TABLE myTable (
...
) AUTO_INCREMENT=100 COMMENT="blaaaa" CHARSET=utf8 COLLATE=utf8_bin;

Supprimer une table

DROP TABLE t1[,t2,t3...];

Modifier une table

On peut modifier une table sur différents contextes (ajouter une colonne, supprimer une colonne, modifier les options d’une colonne, changer le nom d’une colonne,renommer une colonne)

On utilisera constament les mots clés ALTER

Ajouter un champ

//En début de table
ALTER TABLE table ADD colonne type options FIRST;
//En fin par défaut
ALTER TABLE table ADD colonne type options;
//Apres une certaine colonne
ALTER TABLE table ADD colonne type options AFTER colonne;

Modifier un champ

//Modifier les types et options UNIQUEMENT
ALTER TABLE table MODIFY colonne type options;
//Modifier les noms, types et options
ALTER TABLE table CHANGE colonne nouveau_nom_colonne type options;

Supprimer un champ

ALTER TABLE table DROP colonne;

Renommer une table

ALTER TABLE table RENAME TO table_renomme;
// OU
RENAME table TO table_renomme [,table2 TO table2_renomme, ...];

Modifier les options de table

SET storage_engine = nouveau_moteur;
// OU
ALTER TABLE table OPTION=opt1 opt2 ...;

Index

Les index ont deux roles. Ils ont dans un premier temps le rôle d’assurer l’unicité des valeurs dans une colonne et

accelere les recherches mais ralentis les opérations de modifications.

Il ne faut pas indexer trop/toutes les colonnes dans une table.

MySQL met en cache les index, les index consomme donc des ressources : memoire et processeur.

Il faut placer 4 à 5 index dans une table au maximum.

On ajoute un index si une table contient plus de 100 lignes.

Les types d’index

Il existe différents types d’index tels que :

• PRIMARY KEY : Index à valeur unique n’acceptant pas les NULL (UNE par table)
• UNIQUE : Index à valeur unique acceptant les NULL
• INDEX : Index à valeur multiple pouvant accepter les NULL
• FULLTEXT : Index à mettre uniquement pour les types TEXT, spécialisé dans la recherche textuelle (MyISAM uniquement)
• [ SPATIAL : Pour les données spatiales : données géometriques (MyISAM uniquement)]

Création d’index

Il est possible de crée un index de deux manieres différentes : lors de la création d’une table ou apres sa création.

CREATE TABLE maTable(
	I smallint PRIMARY KEY,
	C char(200) UNIQUE
);

Ou :

CREATE TABLE maTable(
	I smallint,
	C char(200),
	PRIMARY KEY (I),
	UNIQUE [`myIndex`] (C)
);

Ou :

CREATE {UNIQUE|INDEX|FULLTEXT|SPATIAL} nomIndex ON table

Ou encore :

INDEX `monIndex` ON table.champ

Le nom de l’index est unique pour l’ensemble de la base

ALTER TABLE table
ADD PRIMARY KEY||FULLTEXT||SPATIALINDEX (nomColonne);

ATTENTION : Ne jamais créer d’index sur un serveur en production car MySQL prend beaucoup de temps pour créer un index. Pendant ce temps, MySQL pause un vérrou sur cette table ce qui rend indisponible la table pour lecture/ecriture

Supprimer un Index

DROP INDEX monIndex ON table
//Ou
ALTER TABLE table DROP PRIMARY KEY
ALTER TABLE table DROP INDEX monIndex

Alogorythme d’index

Il existe deux algorythmes d’index :

• Le HASH
• Le BTREE

Seul Memory accepte l’algorythme HASH

HASH

C’est une fonction de hachage utilisé pour obtenir le résumé d’une information

Prends une informations relativement grande, la réduit, il obtiendra 2 informations relativement différente et forcément deux resumé différents

HASH Index est implémenté à l’aide d’une table de hachage telle que md5.

Ce type d’algo est utile uniquement lors de la comparaison.

//C index hash
SELECT * FROM C=2

BTREE

Pour tout les moteurs de stockage. Index implémenté sous forme d’arbre équilibré.

Mieux adapté pour les recherche sur des plages de valeurs

Syntaxe :

//Nouveauté MySQL5
CREATE TABLE t1 (
	i int,
	INDEX USING{BTREE|HASH}(i)
);

Particularité d’index

//ERREUR :
CREATE TABLE t1 (
	i int,
	c int,
	INDEX USING BTREE(i)
	INDEX USING BTREE(c)
);
//BON :
CREATE TABLE t1 (
	i int,
	c int,
	INDEX USING BTREE(i,c)
);

ATTENTION :

• La taille d’un enregistrement d’index est limité à 767octets sur InnoDB et 1000octets sur MyISAM. Il est donc impossible d’indexer des colonnes ayant un type de données dépassant cette taille.

//Permet de prendre les 100 premiers octets de la colonne
//pour eviter le dépassement de limite
CREATE TABLE maTable(
	c varchar(50000),
	INDEX(c(100))
);

Visualiser les Index

Cf. DESCRIBE. Il existe un alias de DESCRIBE : DESC.

SHOW INDEX FROM table

NOTE

• Est-il possible de créer pour une seule colonne divers index?
Oui

• Sur une table possedant les index PRIMARY KEY, UNIQUE, INDEX, lors de du premiers DESC, visualiserons nous les memes informations apres un drop de l’index PRIMARY KEY sur le second DESC?

Oui, car dans notre exemple, notre colonne est un entier NOT NULL, l’index UNIQUE devient automatiquement clé primaire.

FOREIGN KEY (pas au programme de la certification)

FOREIGN KEY se crée et se supprime comme une clé primaire. FOREIGN KEY n’est pas au programme de la certification MySQL5. Disponible uniquement pour les tables InnoDB

Pour assurer l’atomicité d’une base de données, il existe les options

• ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}
• ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}

CREATE TABLE parent (
	id int PRIMARY KEY
);
CREATE TABLE enfants (
	id int PRIMARY KEY,
	id_parent int,
	FOREIGN KEY (id_parent) REFERENCES parent(id)
	ON DELETE CASCADE
	ON UPDATE CASCADE
)ENGINE=InnoDB;

CASCADE : effectue les modifications en cascade

RESTRICT : on ne peut pas modifier les enregistrements qui sont liés

SET NULL : met a NULL les colonnes liés