dasini.net – Journal d'un expert MySQL

Restrictions

Lors de la création d’une vue, certaines contraintes doivent être prises en compte :

• Il n’est pas possible de créer un index sur une vue
• La vue ne peut pas contenir de sous-requêtes dans la clause FROM du SELECT.
• Il n’est pas possible d’utiliser de variables dans une vue.
• Les objets (tables et vues) nécessaires à la création de la vue doivent exister avant de la créer.
• Si un objet référencé par la vue est effacé, la vue n’est alors plus accessible.
• Une vue ne peut référencer une table temporaire (TEMPORARY TABLE)
• Il n’est pas possible de créer des vues temporaires.
• Il n’est pas possible d’associer un trigger à une vue.
• La définition d’une vue est « gelée » dans une requête préparée.

Exemple:

mysql> CREATE VIEW ma_vue AS SELECT 'première valeur';
Query OK, 0 rows affected (0.24 sec)

mysql> desc ma_vue;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| première valeur | varchar(15) | NO   |     |         |       |
+-----------------+-------------+------+-----+---------+-------+
1 row in set (0.50 sec)

mysql> PREPARE req_prepare FROM 'SELECT * FROM ma_vue';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE req_prepare;
+-----------------+
| première valeur |
+-----------------+
| première valeur |
+-----------------+
1 row in set (0.01 sec)

ALTER VIEW ma_vue AS SELECT 'deuxième valeur';
Query OK, 0 rows affected (0.05 sec)

mysql> desc ma_vue;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| deuxième valeur | varchar(15) | NO   |     |         |       |
+-----------------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> EXECUTE req_prepare;
+-----------------+
| première valeur |
+-----------------+
| première valeur |
+-----------------+
1 row in set (0.00 sec)

Il faut en fait recréer la requête préparée :

mysql> DEALLOCATE PREPARE req_prepare;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE req_prepare FROM 'SELECT * FROM ma_vue';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE req_prepare;
+-----------------+
| deuxième valeur |
+-----------------+
| deuxième valeur |
+-----------------+
1 row in set (0.00 sec)

(à suivre… utiliser les vues)

Que de chemin parcouru depuis ce 5 juillet 1999, date de lancement de MySQL 3.23.0. En plus des objectifs de simplicité d’administration, de hautes performances et de fiabilité, se sont greffées, au fur et à mesure, les fonctionnalités les plus demandées par les utilisateurs. La nouvelle version du SGBDR open-source le plus utilisé, sortie en GA le jeudi 27 novembre 2008, ne déroge pas à ces règles d’or. Numéro un pour les applications en ligne, MySQL se positionne maintenant sur le secteur des applications d’entrepôts de données et d’informatique décisionnelle.

Le but de cet article est de brasser un large panorama des principales nouveautés de MySQL 5.1, telles que le partitionnement, le programmateur d’évènements, la réplication par les données, ainsi que le support des données sur disque avec MySQL Cluster et le support xml/xpath.

Le partitionnement

Une des fonctionnalités phare de cette nouvelle mouture, est la possibilité de partitionner ses tables MySQL. Le partitionnement consiste à fragmenter une table en fonction des données qu’elle contient. Le but principal est d’optimiser les performances : le partitionnement peut permettre d’améliorer les réponses de certaines requêtes. Les données étant stockées dans différentes partitions, l’optimiseur ne prendra pas en compte les partitions non impactées par la requête (PRUNING).

Un autre objectif est de faciliter certaines tâches de maintenance : les données d’une partition peuvent être facilement et rapidement effacées, simplement en supprimant la partition.

Enfin, le partitionnement permet de mettre les données et les index sur des disques différents : il est possible de stocker les données et les index sur des disques séparés, pour les tables MyISAM.

Le partitionnement avec MySQL

La commande SHOW VARIABLES LIKE ‘have_partitioning’ est un moyen simple de savoir si votre version de MySQL supporte le partitionnement.

mysql> SHOW VARIABLES LIKE 'have_partitioning';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.10 sec)

L’opération de partitionnement proprement dite se réalise lors de la création de la table (avec la commande CREATE TABLE) ou lors de la modification de sa structure (avec la commande ALTER TABLE) en utilisant la clause PARTITION BY.

CREATE TABLE ... ENGINE = <engine>
      PARTITION BY <type> ( <expression> )

Il est a noter que les moteurs Merge, Federated, CSV ne permettent pas le partitionnement.

<engine> = MyIsam | InnoDB | Archive | Falcon | NDBCluster | Memory

MySQL propose 4 types de partitionnement, par intervalle (RANGE), par liste (LIST), et par hachage (HASH ou KEY).

<type> = RANGE | LIST | HASH | KEY

Quelques exemples de partitionnements

Partitionner sa table est une opération très simple. Le plus délicat est finalement de savoir pourquoi partitionner et comment le faire. Le critère de partitionnement est donc essentiel pour espérer gagner en performance.

-- Partitionnement par intervalle
CREATE TABLE 'City_part_range' (
   'ID' int(11) NOT NULL AUTO_INCREMENT,
   'Population' int(11) NOT NULL DEFAULT '0',  KEY 'ID' ('ID')
) ENGINE=MyISAM
   PARTITION BY RANGE (population) (
   PARTITION p0 VALUES LESS THAN (100),
   PARTITION p1 VALUES LESS THAN (1000),
   PARTITION p2 VALUES LESS THAN (10000),
   PARTITION p3 VALUES LESS THAN (100000),
   PARTITION p4 VALUES LESS THAN (1000000),
   PARTITION p5 VALUES LESS THAN MAXVALUE
);

-- Partitionnement par liste
CREATE TABLE 'Country_part_list' (
   'Code' char(3) NOT NULL DEFAULT '',
   'Continent' tinyint UNSIGNED DEFAULT 1,
   KEY ('Code')
) ENGINE=MEMORY
   PARTITION BY LIST(Continent) (
   PARTITION pCateg1 VALUES IN (1),
   PARTITION pCateg2 VALUES IN (4),
   PARTITION pCateg3 VALUES IN (2,6),
   PARTITION pCateg4 VALUES IN (3,5,7)
);

-- Partitionnement par hash
CREATE TABLE 'City_part_hash' (
   'ID' int(11) NOT NULL AUTO_INCREMENT,
   'Name' char(35) NOT NULL DEFAULT '',
   'CountryCode' char(3) NOT NULL DEFAULT '',
   'District' char(20) NOT NULL DEFAULT '',
   'Population' int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY ('ID')
) ENGINE=InnoDB PARTITION BY HASH (id) PARTITIONS 4;

-- Partitionnement par key
CREATE TABLE 'City_part_key' (
   'ID' int(11) NOT NULL AUTO_INCREMENT,
   'Name' char(35) NOT NULL DEFAULT '',
   'CountryCode' char(3) NOT NULL DEFAULT '',
   'District' char(20) NOT NULL DEFAULT '',
   'Population' int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY ('ID')
) ENGINE=Archive PARTITION BY KEY () PARTITIONS 6;

(à suivre... event scheduler & row based Replication)

Enfin !

🙂

MySQL 5.1 est enfin disponible pour la production

Parmi les nouveautés:

MySQL 5.1 provides a number of new enhancements including:

– Le partitionnement
– La réplication Row-based
– Les events scheduler
– Amélioration du support XML et XPath

…

http://dasini.net/blog/2008/11/28/les-nouveautes-de-mysql-51-part-15/

http://dev.mysql.com/downloads/

Syntaxe d’une vue

CREATE VIEW

La commande MySQL pour créer une vue est assez proche de la syntaxe du standard SQL.

CREATE VIEW nom_de_la_vue AS requête_select

CREATE TABLE etudiant (

id_etudiant int unsigned primary key,

nom char(30),

prenom char(30),

age tinyint unsigned,

cursus enum('Licence', 'Master', 'Doctorat'));

CREATE VIEW v_etudiant_liste AS SELECT nom, prenom FROM etudiant;

Après avoir créé la table etudiant, on crée la vue v_etudiant_liste qui contient le nom et le prénom des étudiants.

Il est possible d’ajouter d’autres informations lors de la création de la vue:

CREATE

[OR REPLACE]

[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

[DEFINER = { user | CURRENT_USER }]

[SQL SECURITY { DEFINER | INVOKER }]

VIEW nom_de_la_vue [(colonne(s))]

AS requête_select

[WITH [CASCADED | LOCAL] CHECK OPTION]

Voici dans le détail les différentes clauses.

OR REPLACE

Si une vue du même nom existe, elle est alors supprimée et remplacée par la nouvelle.

ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}

Clause non standard, qui prend les valeurs suivantes:

• UNDEFINED : C’est la valeur par défaut. MySQL décide lui-même quel algorithme choisir entre MERGE et TEMPTABLE.
• MERGE : utilise la requête SQL ayant servie à la création de la vue comme base d’opération. En d’autres termes, faire une requête sur la vue revient à faire la même requête sur la ou les tables sous-jacentes.
• TEMPTABLE : utilise une table temporaire créée pour stocker (temporairement) les résultats. Un intérêt de cet algorithme est de libérer plus rapidement les verrous sur les tables sous-jacentes. Les autres requêtes sont alors moins pénalisées.

Il faut noter également qu’une vue avec pour valeur MERGE sera modifiable alors qu’avec la valeur TEMPTABLE elle ne le sera pas.

DEFINER = { user | CURRENT_USER }

Clause non standard qui permet d’assigner un créateur à la vue. Par défaut, le créateur de la vue est DEFINER = current_user, c’est-à-dire, l’utilisateur qui exécute la commande CREATE VIEW. Il est cependant possible d’assigner la vue à un autre compte utilisateur, à condition d’avoir le droit SUPER.

SQL SECURITY { DEFINER | INVOKER }

Clause non standard qui permet de définir quels seront les droits de l’utilisateur, lors de l’exécution de la vue. Deux valeurs sont possibles:

• DEFINER qui permet d’exécuter la vue avec les droits du créateur. C’est la valeur par défaut.
• INVOKER qui permet d’exécuter la vue avec ses propres droits.

WITH [CASCADED | LOCAL] CHECK OPTION

Permet de vérifier les contraintes spécifiées dans la clause WHERE d’une vue modifiable lorsque l’on y modifie ses données. Deux valeurs sont possibles:

• CASCADED, la valeur par défaut. Elle permet de vérifier la contrainte pour la vue ainsi que pour les vues sous-jacentes dont elle dérive.
• LOCAL qui permet de vérifier seulement la contrainte de la vue.

ALTER VIEW

Une fois la vue créée, il est bien évidement possible de la modifier avec la commande ALTER VIEW.

ALTER definer='secretaire'@'localhost' VIEW v_etudiant_liste AS SELECT nom, prenom, cursus FROM etudiant;

Cette commande modifie la clause DEFINER en lui assignant le compte secretaire@localhost et modifie la définition de la vue en rajoutant le champ cursus.

DROP VIEW

Permet d’effacer une vue.

DROP VIEW v_etudiant_liste, v_prof_liste;

Supprime les vues v_etudiant_liste et v_prof_liste. Il est possible d’ajouter la clause IF EXISTS qui retourne un avertissement au lieu d’une erreur si la vue à effacer n’existe pas.

(à suivre… les restrictions pour créer une vue)

Le langage SQL acronyme de Structured Query Language (Langage Structuré de Requêtes), a été conçu pour gérer les données dans un SGBDR. A l’aide des DML (Data Manipulation Language ie les requêtes SELECT, INSERT, UPDATE, DELETE) il est possible de manipuler ces données qui sont stockées dans des tables. SQL nous propose une autre interface pour accéder à cette information: les vues.

Dans cet article, nous verrons comment créer et se servir des vues, puis avec quelques exemples pratiques, nous allons voir comment les utiliser le mieux possible.

Qu’est ce qu’une vue ?

Les vues sont des tables virtuelles issues de l’assemblage d’autres tables en fonction de critères. Techniquement les vues sont créées à l’aide d’une requête SELECT. Elles ne stockent pas les données quelles contiennent mais conservent juste la requête permettant de les créer.

La requête SELECT qui génère la vue référence une ou plusieurs tables. La vue peut donc être, par exemple, une jointure entre différentes tables, l’agrégation ou l’extraction de certaines colonnes d’une table. Elle peut également être créée à partir d’une autre vue.

Les vues sont souvent en lecture seule et ne permettent donc que de lire des données. Cependant MySQL permet la création de vues modifiables sous certaines conditions :

• La requête qui génère la vue doit permettre à MySQL de retrouver la trace de l’enregistrement à modifier dans la ou les tables sous-jacentes ainsi que celle de toutes les valeurs de chaque colonne. La requête SELECT créant la vue ne doit donc pas contenir de clause DISTINCT, GROUP BY, HAVING… et autres fonctions d’agrégation. La liste complète est disponible dans la documentation de MySQL.
• L’autre condition est que sa clause ALGORITHM ne doit pas être de valeur TEMPTABLE. Nous reviendrons sur ce point.

A quoi servent les vues ?

Les vues peuvent être utilisées pour différentes raisons, elles permettent de :

• Contrôler l’intégrité en restreignant l’accès aux données pour améliorer la confidentialité
  • Partitionnement vertical et/ou horizontal pour cacher des champs aux utilisateurs, ce qui permet de personnaliser l’affichage des informations suivant le type d’utilisateur.
• Masquer la complexité du schéma
  • Indépendance logique des données, utile pour donner aux utilisateurs l’accès à un ensemble de relations représentées sous la forme d’une table. Les données de la vue sont alors des champs de différentes tables regroupées, ou des résultats d’opérations sur ces champs.
• Modifier automatiquement des données sélectionnées (sum(), avg(), max(),…)
  • Manipuler des valeurs calculées à partir d’autres valeurs du schéma.
• Conserver la structure d’une table si elle doit être modifiée
  • Le schéma peut ainsi être modifié sans qu’il ne soit nécessaire de changer les requêtes du côté applicatif.

Les droits nécessaires

Pour créer une vue l’utilisateur doit avoir le droit CREATE VIEW. Il faut également avoir la permission de sélectionner toutes les colonnes qui apparaissent dans la commande SELECT spécifiant ce qu’est la vue.

De plus si la clause REPLACE est utilisée, le droit DROP est également nécessaire.

Le droit SHOW VIEW donne la possibilité d’exécuter la commande SHOW CREATE VIEW. Cette commande permet d’obtenir les informations de création d’une vue. Une autre façon d’obtenir ces informations est d’interroger la table view du schéma information_schema. Cette information sera exhaustive seulement pour les vues que vous avez créées.

GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON `projet`.* TO 'secretaire'@'localhost';

(à suivre… syntaxe d’une vue)

mysqldump est un client texte qui permet de faire une sauvegarde (dump) logique des données (au sens large) d’un serveur MySQL.
Pour pouvoir utiliser un client, il faut disposer d’un compte utilisateur (user, host et password), que le client utilisera pour se connecter au serveur MySQL.
Si vous devez faire régulièrement des sauvegardes, ce qui est (devrait être) la norme, une bonne pratique est d’avoir un utilisateur dédié pour cette tache, et par conséquent que cet utilisateur ait les droits qui vont bien. Toujours dans cet esprit bonnes pratiques, le but là est d’appliquer le principe du moindre privilège (droit). En d’autres termes, l’utilisateur doit avoir les droits pour faire ce qu’il doit faire (logique) mais surtout pas plus (finalement ça aussi c’est logique).

ne surtout pas avoir:

mysql> GRANT ALL PRIVILEGES ON *.* TO user_dump@localhost ;

=> qui donne tout les droits sur toutes les bases de données à l’utilisateur user_dump@localhost

mais plutôt:

mysql> GRANT SELECT, LOCK TABLES ON *.* TO user_dump@localhost;

=> les droits SELECT et LOCK TABLES sur tout le serveur pour l’utilisateur user_dump@localhost

Le droit SELECT permet de lire les données à récupérer dans le fichier de dump.
Le droit LOCK TABLES permet la pose de verrous sur les tables, nécessaire au maintient de la cohérence.

et pis c’est tout !

Et donc pour faire une sauvegarde logique de toute la base de données (la commande minimale est) :

 shell> mysqldump -u user_dump -p --all-database

=>lance le client mysqldump avec l’utilisateur user_dump.

P.S. ??Pour utiliser la requête SELECT … INTO OUFILE il vous faudra le droit FILE.

P.S.2 dans la vraie vie, vous aurez besoins de renseigner plus de paramètres, et surtout de récupérer la sortie du dump car par défaut c’est… l’écran 😮 (cependant avec un bel écran monochrome vert, cela peut faire de beaux effets à la MATRIX) 🙂

P.S.3 Il est quand même possible de se passer du droit LOCK TABLES, il faut alors dire à mysqldump de ne pas mettre de verrous sur les tables

C’est dans un grand hotel parisien qu’a eu lieu la 2ème conférence MySQL française. Pour résumer, que du bonheur 🙂
La première conf nous à permis d’avoir un peu plus d’informations sur le futur de MySQL, en vrac (sauvegarde à chaud physique (START BACKUP), les moteurs FALCON (& MARIA ?), checksum pour la réplication, les rôles, cryptage des données,…).

On a pu également apprendre que la 5.1 GA doit sortir très très bientôt (en même temps cela fait un an que l’on nous dit ça 🙂 ), mais là c’est vraiment brulant, c’est à dire début décembre voir même, fin novembre (le 28 exactement). Quoiqu’il en soit, je mettrai un article en ligne, présentant les nouveautés de la 5.1, pour fêter ça !

Je n’ai malheureusement pas pu assister à toutes les conférences, mais celles que j’ai suivit étaient de haute facture.
L’excellent Serge Frezefond nous a parlé des applications Web 2.0 avec MySQL et memcached. J’ai aussi assisté aux conférences: Choisir la bonne solution HA pour MySQL (Max Mether), les performances MySQL sous microscope (Stéphane Varoqui), Scale Up, Scale Out, Virtualisation (Serge Frezefond).

A noté également que la nourriture était raffinée… ce qui ne gâche rien.

Comme je l’ai dit précedemment, que du bonheur. Vivement l’année prochaine !

Mardi 18 novembre 2008 Giuseppe Maxia et Nat Makarévitch étaient les invités du MySQL User Group, dans les locaux d’Alter Way. La soirée fut aussi passionnante qu’instructive.

Nat nous a expliqué comment améliorer les performances d’un sgbdr (notamment MySQL 🙂 ) optimisation vu sous l’axe des I/O

Vous pouvez télécharger sa présentation: Nat Makarévitch: Grandes bases de données: les entrées/sorties

Giuseppe lui nous parlé de MySQL Proxy, se petit soft capable de faire de très grandes choses comme du load balancing, de la réécriture de requêtes à la volé,…

L’April a pour objectifs de :

• Promouvoir le logiciel libre dans toutes les sphères de la société ;
• Sensibiliser le plus grand nombre aux enjeux des standards ouverts et de l’interopérabilité ;
• Obtenir des décisions politiques, juridiques et règlementaires favorables au développement du logiciel libre et aux biens communs informationnels ;
• Favoriser le partage du savoir et des connaissances.

Actions:

• Sensibilise de nouveaux publics au logiciel libre
• Informe les élus et les décideurs politiques et promeut une législation progressiste en matière de NTIC, brevets et de droit d’auteur
• Participe à des comités de programme ou des jurys (« Solutions Linux », Paris Capitale du Libre, Trophées du Libre)
• S’implique dans l’organisation des Ren­con­tres Mondiales du Logiciel Libre
• Noue des partenariats avec des institutions (Région Île de France, Caisse des Dépôts), des ONG (Fondation pour le Progrès de l’Homme), des établissements publics (Cité des Sciences, Université du Littoral)
• Donne une quarantaine de conférences par an
• Relaye l’information et les actions dans les médias
• Prend part à une vingtaine d’évènements par an
• Est présente dans les structures qui influent sur la politique gouvernementale (Forum des Droits sur l’Internet, CSPLA…)
• Sensibilise les décideurs et institutions aux enjeux du passage au logiciel libre et les accompagne dans leur démarche

adhérer ici

Optimiser sa base de donnée est l’obsession principale du DBA. Savoir identifier, anticiper et régler les problèmes de performances constituent une vrai valeur ajoutée et cela n’est pas donné à tout le monde.  Satisfaction personnelle, satisfaction des décideurs mais surtout satisfaction du client…

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