MySQL 5.0 : Les fonctionnalités de la maturité

• Par Emacs
• 0 commentaire
• 19738 lectures
• RSS -  Atom

MySQL est le principal SGBD Open Source avec PostgreSQL. Cette cinquième version est très attendue car elle promet 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, un nouveau moteur de stockage et diverses petites améliorations.

Pour autant MySQL 5.0 aura encore quelques petits manques fonctionnels (principalement un support complet des déclencheurs) par rapport à ses principaux concurrents commerciaux. Manques que son prix (voir encadré) et sa rapidité feront vite oublier pour qui n'a pas besoin de trop de fioritures. Avec toutes ces nouveautés la philosophie de MySQL reste la même : « rester simple et rapide ».

Modèle économique de MySQL et coût

Le modèle économique de la société MySQL AB est axé vers la vente de licences, le consulting ainsi que la formation.
MySQL est disponible sous deux types de licences :

• La licence propriétaire,
• La licence GPL (General Public License) qui est dite virale et qui limite donc l'utilisation de MySQL dans un contexte propriétaire (la licence GPL doit pouvoir s'appliquer à tout le projet).

Pour les clients qui ne souhaitent pas ouvrir leur code, il est possible d'acheter des licences propriétaires. Le prix constaté de ces licences est bien moins élevé que celui de ses principaux concurrents.

Installation de MySQL5

L'installation de MySQL 5 se fait de façon très simple que ce soit sous Linux ou sous Windows par le biais de binaires prés compilés ou de l'installeur microsoft.

Après avoir installé la base de données, se lance un utilitaire qui va optimiser la configuration en fonction du type de serveur (Machine de développement, serveur, serveur dédié) et des fonctionnalités nécessaires (multi fonctionnalités, SGBD transactionnel seulement, SGBD non transactionnelle).

L'écran suivant permet de définir ou les tablespace InnoDB doivent être placées puis on indique le nombre approximatif de connexions simultanées auxquelles MySQL répondra.

Enfin pour finir, contrairement aux précédentes versions de MySQL l'utilitaire demande par défaut de donner un mot de passe à l'utilisateur root.

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.

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ésents.
• 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 par la pratique

Création d'une vue
CREATE 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 :

Création de la vue du personnel informatique
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 MySQL 5

Optimisation des vues à la création
CREATE [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] 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. Pour améliorer les performances il est généralement plus intéressant de le définir statiquement afin d'éviter à l'optimiser de le découvrir à chaque exécution.

L'autre 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 dispose 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

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

Procédures stockées et fonctions

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

Une procédure stockée est une liste de commandes qui peuvent être compilées et stockées sur le serveur. Une fois que cela est fait, les clients n’ont pas besoin de soumettre à nouveau toute la commande, mais font simplement référence à la procédure stockée.

Cela se traduit par des performances bien meilleures, car les commandes n’ont pas à être analysées plusieurs fois.

Des procédures stockées pour limiter le trafic réseau

Bien moins d’informations transitent sur le réseau. Il y a aussi un gain réel d’efficacité et de clarté puisqu’on évite de répliquer ces commandes plusieurs fois.

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.

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

Les procédures stockées de MySQL 5 respectent les 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 SQL Serveur 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

Syntaxe de création d'une procédure stockée
CREATE PROCEDURE sp_name([parametres]) [Caractéristiques] routines

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ée des déclencheurs dans la version 5.0.

Syntaxe de base

Syntaxe de création d'un trigger
CREATE TRIGGER trigger_nom { 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 activé avant ou après l'évènement.

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

Exemple de création d'un trigger
CREATE TRIGGER livre_bi BEFORE INSERT

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

CREATE TRIGGER livre_bi BEFORE INSERT ON livre

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

CREATE TRIGGER livre_bi 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 en 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.

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. Lors de nos tests avec la version bêta (5.0.10) nous n'avons eu aucun plantage à déplorer, ce qui est plutôt encourageant. En revanche, nous avons atteint rapidement les limites de la version light des déclencheurs. La migration de nos données n'a pas non plus posée de problèmes.

D'une certaine façon MySQL AB avec cette nouvelle version élargit son public en ne s'adressant plus principalement aux architectes d'applications web mais à tous les développeurs.

Les dix ans de MySQL fêté en Juin (?) dernier et cette nouvelle version sont un signe de maturité important dans la croissance de MySQL AB.

Fiche signalétique

Nom outil : MySQL 5
Editeur : MySQL AB
Date de sortie estimée : Octobre 2005
Configuration : Linux ou Windows.

Les plus :

• Support des vues, des procédures stockées, des déclencheurs;
• Amélioration des calculs mathématiques;
• Ajout d'un nouveau moteur de stockage;
• Le prix.

Les moins :

• Fonctionnalités des déclencheurs encore limitées;

Auteur

Cet article a été rédigé par Cyril PIERRE de GEYER, Directeur Technique adjoint chez Anaska (www.anaska.com). Anaska est un organisme de formation spécialisé dans l'Open Source. Militant pour la philosophie OpenSource Cyril PIERRE de GEYER est également l'auteur du livre « PHP5 Avancé », membre du club mandriva, de l'ASS2L et du club Sénat. Merci encore à lui pour cet écrit.

Soyez le premier à réagir !