dasini.net – Journal d'un expert MySQL

MySQL est le SGBDR Open Source le plus populaire au monde. Sa cinquième version, sortie en octobre 2005, permet de mieux répondre aux problématiques d’entreprise. Au menu des nouveautés fonctionnelles : les vues, les procédures stockées, les déclencheurs, de nouveaux moteurs de stockage, la base de données INFORMATION_SCHEMA et diverses petites améliorations.

http://dasini.net/blog/presentations/?#presentation_mysql

Le MUG.fr à le plaisir de vous inviter à une rencontre avec en guests stars:

• Giuseppe Maxia, (responsable communauté MySQL pour l’Europe)
• Nat Makarévitch (Expert des méthodes et des techniques du monde de l’opensource)

Cette rencontre aura lieu le mardi 18 novembre 2008, à partir de 19h, dans les locaux d’Alter Way à Saint Cloud

Au programme :

• Nat Makarévitch:

« Grandes bases de données: les entrées/sorties »

« amélioration des performances d’une instance de MySQL gérant des bases de données dont le volume total dépasse nettement la mémoire vive disponible, autrement dit dans un contexte où il convient d’optimiser les entrées/sorties »

• Giuseppe Maxia

« MySQL Proxy wizardry »

Lieu :

ALTER WAY GROUP

1 rue royale, 227 Bureaux de la Colline 92210 Saint-Cloud

Bâtiment D 9ème étage

Tel : 01 78 15 24 00

Date: mardi 18 novembre 2008

Heure: à partir de 19h

Tarif: gratuit

Une fonction de hash est une fonction qui prend une chaîne de caractères en entrée et qui renvoi une autre chaine de caractères. La chaîne de caractères résultat a toujours la même longueur et est strictement identique pour une même entrée.

Une des utilisations du hashage sert a masquer les mots de passes stockés dans une table.

Au lieu d’avoir une table utilisateur, faiblement sécurisée avec un mot de passe en clair:

+----------+------------------------+

| name     | password_en_clair      |

+----------+------------------------+

| freshdaz | mot_de_passe           |

+----------+------------------------+

Il est préférable d’avoir ceci, le même mot de passe mais hashé:

+----------+---------------------------------------+

| name     | password_hash                         |

+----------+---------------------------------------+

| freshdaz | 8b70bf2ffce34ced3223dfc9e4fa9cc7      |

+----------+---------------------------------------+

Comme vous pouvez le constater le mot de passe hashé est plus difficile à lire, d’autant plus que le hash est (sensé être) irréversible (en fait tout est une question de temps).

MySQL propose 5 fonctions de hashage:

CRC : (contrôle de redondance cyclique)

c’est une méthode pour contrôler l’intégrité des données, donc hors sujet 🙂

mysql> SELECT crc32('mot_de_passe');

+-----------------------+

| crc32('mot_de_passe') |

+-----------------------+

|             965676113 |

+-----------------------+

MD5 : (Message Digest 5)

très populaire mais n’est donc plus considéré comme sûr au sens cryptographique. Autrement dit, une petite recherche sur le net et vous trouverez facilement des algorithmes pour le cracker.

mysql> SELECT md5('mot_de_passe');

+----------------------------------+

| md5('mot_de_passe')              |

+----------------------------------+

| 8b70bf2ffce34ced3223dfc9e4fa9cc7 |

+----------------------------------+

SHA1 : (Secure Hash Algorithm )

Conçue par la National Security Agency (NSA) (est-ce une bonne nouvelle ?) 😀

Plus sécurisé que le md5.

mysql> SELECT sha1('mot_de_passe');

+------------------------------------------+

| sha1('mot_de_passe')                     |

+------------------------------------------+

| d10c988ca61b785f5a7756b5852683d798fe4d92 |

+------------------------------------------+

PASSWORD:

Algorithme « maison » de MySQL. Utilisé pour stocker les mots de passes des utilisateurs du serveur MySQL.

mysql> SELECT password('mot_de_passe');

+-------------------------------------------+

| password('mot_de_passe')                  |

+-------------------------------------------+

| *C3D87F1C2FADE3F03484FC62E669276C2A37266F |

+-------------------------------------------+

OLD_PASSWORD :

Ancien algorithme pour stocker les mot de passes des utilisateurs du serveur MySQL. Utilisé jusqu’à la version 4.0.x de MySQL. Il a été changé lui aussi pour des raison de sécurité. Là encore vous trouverez très facilement le nécessaire pour le cracker.

mysql> SELECT old_password('mot_de_passe');

+------------------------------+

| old_password('mot_de_passe') |

+------------------------------+

| 684ec7590a2271b0             |

+------------------------------+

Quelles fonctions utiliser ?

Comme souvent en informatique, il faut faire un compromis entre meilleurs performances et sécurité optimale. En d’autre terme un sha1 plus sécurisé qu’un md5 mettra cependant plus de temps pour hasher une même chaine de caractères.

Vérifions tout ceci:

mysql> SELECT md5('olivierdasini');

+----------------------------------+

| md5('olivierdasini')             |

+----------------------------------+

| a42bc0139343fc758a414f70eda6f209 |

+----------------------------------+

1 row in set (0.00 sec)

mysql> SELECT sha1('olivierdasini');

+------------------------------------------+

| sha1('olivierdasini')                    |

+------------------------------------------+

| f2cdbfdf62d717ab96528235a1b1ff34671860d4 |

+------------------------------------------+

1 row in set (0.00 sec)

hum, hum pas très probant…

Aidons nous de la fonction benchmark():

mysql> SELECT benchmark(3000000,md5('olivierdasini'));

+-----------------------------------------+

| BENCHMARK(3000000,md5('olivierdasini')) |

+-----------------------------------------+

|                                       0 |

+-----------------------------------------+

1 row in set (12.27 sec)

mysql> SELECT benchmark(3000000,sha1('olivierdasini'));

+------------------------------------------+

| BENCHMARK(3000000,sha1('olivierdasini')) |

+------------------------------------------+

|                                        0 |

+------------------------------------------+

1 row in set (15.91 sec)

En exécutant ces 2 fonctions plusieurs fois, (grâce à benchmark()) on s’aperçoit qu’il faut environ 25% de temps supplémentaire pour l’algorithme sha1 que pour le md5. Ce qui est loin d’être négligeable.

Regardons comment se débrouillent les fonctions « maison » de MySQL:

mysql> SELECT benchmark(3000000,password('olivierdasini'));

+----------------------------------------------+

| BENCHMARK(3000000,password('olivierdasini')) |

+----------------------------------------------+

|                                            0 |

+----------------------------------------------+

1 row in set (7.24 sec)

17:08 root$test> SELECT benchmark(3000000,old_password('olivierdasini'));

+--------------------------------------------------+

| BENCHMARK(3000000,old_password('olivierdasini')) |

+--------------------------------------------------+

|                                                0 |

+--------------------------------------------------+

1 row in set (4.26 sec)

Le verdict est sans appels, avec ses 7 secondes (près de 50% de temps en moins que md5 ), la fonction password() est d’un bon rapport performance sécurité. Pour rappel, c’est cette fonction qu’utilise le serveur MySQL pour gérer les mots de passes de ses comptes utilisateurs. Inconvénient non négligeable, elle est spécifique (à ma connaissance) à MySQL (mais est-ce vraiment un inconvénient ?  🙂 ).

Quant à l’ancienne façon de hasher les mots de passes, elle est évidement très performante, mais on a vu que niveau sécurité, elle laisse à désirer…

Les fonctions présentées sont une bonne solution pour sécuriser ses mots de passes. Il est néanmoins possible d’augmenter encore un peu plus le niveau de sécurité. Une des techniques consiste à renforcer le mot de passe utilisateur en lui rajoutant une chaine de caractères et en combinant plusieurs algorithmes de hashage.

Par exemple, avec comme mot de passe: olivierdasini, on peut concaténer, avec la fonction concat(), la date de naissance: 121174_ et combiner différents algorithmes:

mysql> SELECT benchmark(3000000,md5(concat(sha1('121174_'), 'olivierdasini')));

+------------------------------------------------------------------+

| benchmark(3000000,md5(concat(sha1('121174_'), 'olivierdasini'))) |

+------------------------------------------------------------------+

|                                                                0 |

+------------------------------------------------------------------+

1 row in set (29.30 sec)

mysql> SELECT benchmark(3000000,password(concat(old_password('121174_'), 'olivierdasini')));

+-------------------------------------------------------------------------------+

| benchmark(3000000,password(concat(old_password('121174_'), 'olivierdasini'))) |

+-------------------------------------------------------------------------------+

|                                                                             0 |

+-------------------------------------------------------------------------------+

1 row in set (12.06 sec)

Avec de telles combinaisons, vos mots de passes sont plus à l’abri, par contre les temps de hashage sont doublés. Comme ont le disait précédemment, tout est une histoire de compromis.

Mercredi 19 novembre 2008, MySQL/SUN organise sa deuxième conférence française.

Pourquoi y aller ?

• Découvrir les meilleures pratiques pour déployer et gérer les applications MySQL à travers votre entreprise
• Comprendre comment bénéficier des nouvelles technologies telles que memcached et MySQL Proxy
• Obtenir des informations détaillées sur les nouvelles fonctions, produits et services MySQL
• Bénéficier de conseils de spécialistes sur l’optimisation des performances de MySQL
• Développer vos compétences pour bâtir des applications MySQL fortement évolutives, fiables, sécurisées et à haute disponibilité
• Écouter des témoignages clients sur la mise en place d’applications MySQL
• Apprendre comment mettre en place un entrepôt de données de plusieurs téra-octets avec MySQL et Infobright
• Établir des relations et échanger avec d’autres utilisateurs MySQL et avec les membres de l’équipe MySQL de SUN

Au programme
Les conférences

• Le Futur de MySQL: Ce que vous devez savoir sur les fonctionnalités et services à venir
• Délivrer des applications Web 2.0 avec MySQL et memcached
• Meilleures pratiques pour déployer MySQL sur Solaris
• Haute disponibilité avec load balancing & MySQL Proxy
• Choisir la bonne solution HA pour MySQL
• Le datawarehouse multi-téraoctets avec MySQL et Infobright
• Les performances MySQL sous microscope
• Stratégies de Backup pour MySQL
• Scale Up, Scale Out, Virtualisation—Que devez-vous faire avec MySQL ?

Présentations clients

• Kewego
• Virgin Mobile

Intervenants

• Bertrand Matthelié, Directeur Marketing EMEA MySQL
• Bruno Hourdel, Directeur Marketing Sun France
• Robin Schumacher, Directeur Product Management MySQL
• Serge Frezefond, Ingénieur avant-vente
• Eric Bezille, Chief Technologist
• Philippe Campos, consultant sénior
• Max Mether, instructeur
• Miriam Tuerk, CEO Infobright
• Stéphane Varoqui, consultant sénior

Date
mercredi 19 novembre 2008

Lieu
Hotel Le Méridien Etoile
81 Boulevard Gouvion Saint-Cyr
75017 Paris
Tel : 01 40 68 34 34
Prix
Le prix de la conférence est de 199 EUR incluant le déjeuner.

Alléchant tout ça, moi j’y serai 🙂

La clause, AUTO_INCREMENT, permet à MySQL de générer un entier unique pour tout nouvel enregistrement d’une table. Cette clause ne peut se mettre que sur les champs de type entier, indexé et non nul. Elle est donc souvent utilisée comme clé primaire.

Cependant, sont comportement n’est pas tout à fait identique sur une table MyISAM et sur une table InnoDB.

mysql> CREATE TABLE table_myisam (id INT AUTO_INCREMENT PRIMARY KEY) engine=MyISAM;

mysql> SHOW CREATE TABLE table_myisam;

CREATE TABLE `table_myisam` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> INSERT INTO table_myisam (id) VALUES (NULL),(NULL),(NULL),(100);

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT id FROM table_myisam;

+—–+

| id |

+—–+

| 1 |

| 2 |

| 3 |

| 100 |

+—–+

mysql> SHOW CREATE TABLE table_myisam;

CREATE TABLE `table_myisam` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

mysql> DELETE FROM table_myisam WHERE id=100;

mysql> SELECT id FROM table_myisam;

+—-+

| id |

+—-+

| 1 |

| 2 |

| 3 |

+—-+

mysql> SHOW CREATE TABLE table_myisam;

CREATE TABLE `table_myisam` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

mysql> CREATE TABLE table_innodb (id INT AUTO_INCREMENT PRIMARY KEY) engine=InnoDB;

mysql> SHOW CREATE TABLE table_innodb;

CREATE TABLE `table_innodb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> INSERT INTO table_innodb (id) VALUES (NULL),(NULL),(NULL),(100);

Query OK, 4 rows affected (0.22 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT id FROM table_innodb;

+—–+

| id |

+—–+

| 1 |

| 2 |

| 3 |

| 100 |

+—–+

mysql> SHOW CREATE TABLE table_innodb;

CREATE TABLE `table_innodb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

mysql> DELETE FROM table_innodb WHERE id=100;

mysql> SELECT id FROM table_innodb;

+—-+

| id |

+—-+

| 1 |

| 2 |

| 3 |

+—-+

mysql> SHOW CREATE TABLE table_innodb;

CREATE TABLE `table_innodb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

/*************************************/

/**** reboot du serveur MySQL ****/

/*************************************/

mysql> SHOW CREATE TABLE table_myisam;

CREATE TABLE `table_myisam` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=latin1

mysql> INSERT INTO table_myisam (id) VALUES (NULL);

mysql> SELECT id FROM table_myisam;

+—–+

| id |

+—–+

| 1 |

| 2 |

| 3 |

| 101 |

+—–+

mysql> SHOW CREATE TABLE table_innodb;

CREATE TABLE `table_innodb` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

mysql> INSERT INTO table_innodb (id) VALUES (NULL);

mysql> SELECT id FROM table_innodb;

+—-+

| id |

+—-+

| 1 |

| 2 |

| 3 |

| 4 |

+—-+

(<- précédent)

Déclencheurs

Les déclencheurs (triggers) sont des ordres de déclenchement d’opérations quand un évènement survient sur une table.

Des déclencheurs pour maintenir la cohérence des données

Ils sont souvent utilisés pour assurer la cohérence des données dans la base, en réalisant des contraintes qui doivent porter sur plusieurs tables.

Les déclencheurs combinés aux transactions permettent de créer tous les mécanismes d’intégrité référentielle. La norme SQL 3 a d’ailleurs imposé l’utilisation des déclencheurs.

Initialement prévu pour la version 5.1, l’équipe de développement à finalement profité d’une avance sur le calendrier de développement pour proposer une version simplifié des déclencheurs dans la version 5.0.

Syntaxe de base

CREATE TRIGGER trigger_nom
[DEFINER = { user | CURRENT_USER }]
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON table name
FOR EACH ROW
triggered SQL statement

Le premier élément entrant en compte est le nom (trigger_nom). L’action qui est déclenchée l’est à la suite d’un évènement (ex : insertion d’un nouvel enregistrement dans une table). Le second paramètre (BEFORE ou AFTER) indique si le déclencheur doit être lancé avant ou après l’évènement.

Les déclencheurs peuvent être activé durant l’appel à un INSERT, un UPDATE ou un DELETE.

CREATE TRIGGER trig_livre
BEFORE INSERT

Le déclencheur est lié à une table que nous définissons avec le mot clef ON :

CREATE TRIGGER trig_livre
BEFORE INSERT
ON livre

On définit alors les instructions à effectuer une fois le déclencheur activé.

CREATE TRIGGER trig_livre
  BEFORE INSERT
  ON livre
  FOR EACH ROW
  BEGIN
     INST1;
     INST2;
  END;

Prenons un cas pratique :

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

Limitation des déclencheurs liées à la version 5.0

Comme nous l’avons indiqué plus amont le support des déclencheurs est un plus dans la version 5.0 de MySQL : C’était prévu pour la version 5.1. Il n’en reste pas moins que c’est une version light qui souffre à ce jour de quelques limitations :

• On ne peut associer un déclencheur à une vue ou à une table temporaire,

• Les déclencheurs ne peuvent pas non plus faire appel à une procédure stockée ou à des fonctions.

INFORMATION_SCHEMA

La base INFORMATION_SCHEMA, standard du SQL:2003, fournit un accès aux métadonnées du serveur MySQL.

Les métadonnées sont les informations sur les données, telles que le nom des bases de données, le nom des tables, le type des données, d’index, les droits d’accès, etc.

INFORMATION_SCHEMA est une base de données virtuelle, vous ne pouvez y accéder qu’en lecture. En effet, les tables qui la composent, ne sont en fait que des vues. Vous ne verrez donc pas sur le disque dur de fichiers associés.

Par exemple, pour récupérer le code
des procédures stockées, de la base de données FRESHDAZ,  il faut
pour cela, sélectionner les colonnes ROUTINE_NAME et
ROUTINE_DEFINITION de la table  information_schema.ROUTINES:

mysql> SELECT R.ROUTINE_NAME, R.ROUTINE_DEFINITION
-> FROM information_schema.ROUTINES R
-> WHERE R.ROUTINE_TYPE = ‘PROCEDURE’ AND R.ROUTINE_SCHEMA = ‘FRESHDAZ’;

Goodies and co

Amélioration des traitements mathématiques

Avec MySQL 4.1 le résultat de la commande SELECT .01 * .01 renvoyait 0. MySQL 5 utilise une bibliothèque permettant des calculs plus précis. Ainsi 0.0001 est traité comme une valeur exacte et non plus comme une valeur approximative.

Nouveau moteur de stockage

Un nouveau moteur de stockage a été ajouté depuis MySQL 5.0.3 : FEDERATED Storage Engine. Il permet d’accéder à des données présentes sur des bases de données distantes.

Conclusion

MySQL 5 apporte un réel confort en terme de fonctionnalités par rapport à ses versions antérieures. Les déclencheurs, les vues et les procédures stockées propulsent MySQL dans le monde des SGBD matures et permet de soutenir la comparaison avec Oracle, PostgreSQL et autres.

Avec cette nouvelle version MySQL élargit son public en ne s’adressent plus principalement aux architectes d’applications Web mais à tous les développeurs.

Le Forum PHP 2008 se déroulera les 8 et 9 décembre 2008, à l’ASIEM dans le VIIème arrondissement à Paris

Les principaux thèmes seront:

Web services professionnels et Grands projets en PHP : organisation, méthodes et bonnes pratiques.

Au programme également des conférences, dont la mienne:

Les solutions « Haute disponibilité » avec MySQL

Présentation des solutions de haute disponibilité offertes par MySQL Sujet abordés :

• les solutions de clustering (MySQL Cluster)
• réplication (MySQL Replication)
• architecture Shared Disk Clustering

http://afup.org/pages/forumphp2008/sessions.php#79

(<- précédent)

Procédures stockées et fonctions

Les procédures stockées sont des listes de commandes qui peuvent être compilées et stockées sur le serveur. Elles permettent de déplacer une partie de la logique métier d’une application de base de données du client vers le serveur. Les clients n’ont plus besoin de soumettre à nouveau toute la commande, mais font simplement référence à la procédure stockée.

Cela se traduit par une amélioration de la sécurité, une diminution de la redondance du code, et une augmentation des performances.

Des procédures stockées pour améliorer la sécurité

Elles peuvent fournir une protection contre les attaques d’injection SQL, principalement contre celles qui utilisent un opérateur AND ou OR pour ajouter des commandes à une valeur de paramètre d’entrée valide. Les programmes clients n’accédant plus directement aux tables. Toutes les opérations de gestion des données sont effectuées via des procédures stockées.

Des procédures stockées pour centraliser les requêtes

Différentes applications peuvent accéder à la même base de données et avoir les mêmes fonctionnalités. Les procédures stockées peuvent alors servir à factoriser ce code SQL commun ce qui permet de diminuer la redondance et facilite la maintenance du code.

Des procédures stockées pour augmenter les performances

Les commandes n’ont pas à être analysées plusieurs fois, elles sont (pré)compilées sur le serveur et bien moins d’informations transitent sur le réseau, le trafic y est donc limité.

Les procédures stockées sont particulièrement utiles quand les clients qui accèdent à la base de données ne sont pas sur le même serveur.

Le principal inconvénient des procédures stockées, et qu’elles nous rendent complètement dépendantes de l’éditeur de la base de données, puisqu’il n’existe pas de langage universel de développement de procédures stockées.

Une migration simplifiée par le respect du standard SQL 2003

Les procédures stockées de MySQL 5 respectent certaines recommandations du standard SQL 2003. La syntaxe est donc très proche de la syntaxe de DB2 ce qui permet une migration facile entre les deux outils. La migration en provenance d’Oracle ou de MS SQL Server impliquera plus de travail manuel étant donné que leurs bases de données ne respectent pas autant le standard.

Astuce : Pour migrer d’Oracle vers MySQL on peut utiliser les outils de migration Oracle vers DB2 puis passer de DB2 à MySQL qui sont très proches.

Syntaxe

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name([parametres])
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
routines

(suite ->)

(<- précédent)

Vues

Des vues pour faciliter la visibilité

Les vues sont la plupart du temps utiles pour donner aux utilisateurs l’accès à un ensemble de relations représentées sous la forme d’une table. Une vue est une table virtuelle ; les données de la vue sont en fait des champs de différentes tables regroupées, ou des résultats d’opérations sur ces champs.

Des vues pour améliorer la confidentialité

Une vue n’est pas forcément un regroupement de plusieurs tables mais peut être un sous ensemble d’une table (ou de plusieurs) ce qui permet de cacher des champs aux utilisateurs.

Par exemple il ne sera pas forcément utile à tout le monde d’accéder aux champs indiquant les bénéfices réalisés sur un projet dans votre base comptable. Vous pouvez donc créer une vue contenant tous les champs de la table projet sauf le champs bénéfice.

L’approche avec MySQL 5 sera donc plus souple car elle ne force plus un découpage de table pour gérer la confidentialité et les droits donnés aux utilisateurs. Les vues permettront de remplir ce rôle.

Les vues compliquent les mises à jour

Insérer ou modifier des données dans une vue n’est pas aussi simple que de faire un update dans une table. Pour pouvoir le faire il faut réfléchir de façon plus poussée au modèle conceptuel de données :

Une vue n’est pas forcément accessible en insertion / modification. Pour cela il faut qu’il n’y ait pas d’incompatibilité logique à ce qu’elle le soit.

• Tous les champs des tables possédant des contraintes d’intégrités (index unique, clés primaires ..) doivent être présent

• La vue ne doit pas posséder de regroupement ou d’exclusion ( GROUP BY , DISTINCT, UNION)

• Le plan d’exécution de la vue ne doit pas passer par une table temporaire

Les vues de MySQL 5 par la pratique

CREATE
[OR REPLACE]
VIEW view-name
[(column-list)]
AS select-statement

Créer une vue revient à appliquer un filtre à une ou plusieurs tables. Pour schématiser prenons une entreprise normale. Dans celle-ci il y a des employés regroupés sous le terme ‘personnel’. On regroupe ces employés par ‘catégorie’ en fonction de leur activité (administratif, informatique, commercial,…).

On peut créer une vue contenant l’ensemble des informaticiens avec le code suivant :

CREATE VIEW personnelinformatique

AS SELECT a.nom AS nom,a.prenom AS prenom,b.service AS service

from categorie b, personnel a

where a.fkCategorie = 1 and a.fkCategorie = b.pkCategorie;

Optimisation des vues de MySQL 5

CREATE

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = { user | CURRENT_USER }]

[SQL SECURITY { DEFINER | INVOKER }]

VIEW view-name

[(column-list)]

AS select-statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

• La clause facultative «ALGORITHM» n’est pas standard. Elle permet d’optimiser votre code.

• MERGE utilise la requête SQL ayant servie à la création de la vue comme base d’opération.

• TEMPTABLE utilise une table temporaire créée pour stocker les résultats.

Par défaut l’optimiseur MySQL décide lui-même quelle option choisir (UNDEFINED). Pour améliorer les performances, il est généralement plus intéressant de le définir statiquement afin d’éviter à l’optimiseur de le découvrir à chaque exécution.

«DEFINER» assigne un créateur à la vue.

«SQL SECURITY» définit quelles seront les droits de l’utilisateur, lors de l’exécution de la vue. Deux valeurs sont possibles:

– DEFINER permet d’exécuter la vue avec les droits du créateur.

– INVOKER permet d’exécuter la vue avec ses propres droits.

Pour finir, la clause facultative CHECK OPTION permet de ne modifier que la vue ou du moins des informations qui respectent les contraintes de la vue. Ainsi dans notre exemple, avec la clause CHECK OPTION, il ne sera pas possible de modifier au travers de la vue « personnelInformatique » une personne ne travaillant pas dans le service informatique.

Manque

Le seul léger bémol est que la version 5.0 de MySQL ne disposera pas des vues matérialisées. Les vues matérialisées sont des données physiquement dupliquées dans le SGDB. Par exemple le résultat d’un calcul n’est plus à refaire pour chaque accès. Leur utilité se fait particulièrement sentir lors de traitements de tables très volumineuses .

Comparaison avec d’autres SGBD

	MySQL	IBM DB2	Oracle	SQL Server
Basic	Oui	Oui	Oui	Oui
UNION ALL	Oui	Oui	Oui	Oui
JOINS	Oui	Oui	Oui	Oui
INSTEAD OF	Non	Oui	Oui	Oui
UPDATEABLE_KEY	Oui	Non	Non	Non

Possibilité des vues avec MySQL 5.0

UPDATEABLE_KEY est une fonctionnalité de MySQL permettant de modifier une clef primaire par le biais d’une vue.

(suite ->)

MySQL est le SGBD Open Source le plus populaire au monde. Sa cinquième version, sortie en octobre 2005, permet de mieux répondre aux problématiques d’entreprise. Au menu des nouveautés fonctionnelles : les vues, les procédures stockées, les déclencheurs, de nouveaux moteurs de stockage, la base de données INFORMATION_SCHEMA et diverses petites améliorations.

Avec toutes ces nouveautés la philosophie de MySQL reste la même : « simplicité et hautes performances ».

Fonctionnalités

Déjà, avant même la version 5, MySQL supportait de nombreuses fonctionnalités avancées lui permettant de répondre à un grand nombre de problématiques d’entreprise : Les requêtes imbriquées depuis MySQL 4.1, les transactions depuis MySQL 3.23 ainsi que les clés étrangères et l’intégrité référentielle.

	MySQL 4.1	MySQL 5.0	MySQL 5.1
Clés étrangères / Intégrité référentielle	Avec le moteur innoDB	Avec le moteur innoDB	Avec le moteur innoDB
Réplication	Oui	Oui	Oui
Requêtes imbriquées	Oui	Oui	Oui
Vues		Oui	Oui
Procédures stockées		Oui	Oui
Déclencheurs		Oui	Oui
Partitionnement			Oui

(suite ->)