Introduction à MYSQL

Et l’optimisation de requête ?

En principe, lorsqu’une requête SQL est envoyée au SGBD, celui-ci établit un plan d’exécution. Le module se charge d’établir un plan d’exécution s’appelle Optimizer.
Le fonctionnement de l’Optimizer globalement similaire pour l’ensemble des SGBDs (Oracle et SQL Server), en utilisant les étapes suivantes :
1. Validation syntaxique
2. Validation sémantique
3. Utilisation éventuelle d’un plan précédemment produit
4. Réécriture/Simplification de la requête
5. Exploration des chemins d’accès et estimation des coûts.
6. Désignation du chemin le moins coûteux, génération du plan d’exécution et mise en cache de ce dernier.
Mais, le développeur de bases de données doit connaître certaines règles qui permettent d’optimiser l’exécution de requêtes. En voici quelques-unes de ces règles (qui ne sont pas nécessairement d’ans l’ordre).
– R1 : éviter le SELECT * : écrire plutôt le nom des colonnes dont vous avez besoin pour la requête.
– R2 : Créez des indexes sur les colonnes que vous utilisez dans la clause WHERE. Pour plus de performances, ces indexes doivent-être créés après l’insertion des données dans la table.
Rappel : Un index est un objet permettant d’accélérer l’accès aux données La création d’un index sur une clé primaire se fait automatiquement par le système. Pour créer un index sur une colonne autre que la clé primaire on utilise la commande CREATE INDEX.
create index indexnom on joueurs(nom);
create index indexnomprenom on joueurs(nom,prenom);
Si la colonne sur laquelle l’index est créé a la contraint UNIQUE alors vous pouvez créer un index UNIQUE.
Create UNIQUE index indexAlias on joueurs(Pseudo);
Même si les indexes permettent d’accélérer les recherches, trop d’indexe a l’effet inverse (ralentit les recherches)
 Un index ne peut être créé que sur une table (pas une vue).
 Les index UNIQUE applique les contraintes d’unicité

MYSQL en bref

 Ne jamais créer trop d’index
 Créer des index sur une colonne ayant une petite plage de valeurs inutiles
 Un index se créer sur maximum 16 colonnes.
– R3 : Lorsque c’est possible, utilisez le WHERE à la place du Having.
– R4 : Éviter les jointures dans le WHERE, utilisez plutôt le INNER JOIN.
Exemple :Select e.deptno, d.dname, e.ename
From (select deptno, ename from emp) e inner join (select deptno, dname from dept) d On e.deptno = d.deptno;
Rq: Dans la clause FROM on respecte la règle R1.
– R5 : Lorsque c’est possible, utilisez une jointure à la place d’une sous-requête. Les jointures sont l’essentiel des SGBDRs alors ils sont optimisés pour l’écriture des jointures.
– R6 : Utilisez le SELECT count(*) à la place de compter les colonnes. (Select count(*) from etudiants à la place de SELECT count(numad) from etudiants
– R7 : Évitez les fourchettes < et >; utilisez le BETWEEN
– Si possible, utilisez le BETWEEN à la place du Like
– R8 : transformer l’INTERSECT en jointure
– R9 : Utilisez le IN à la place du ANY et le <> ALL en NOT IN
– R10 : Éviter la clause DISTINCT dans le SELECT sauf si c’est absolument nécessaire
– R11 : Ordonnez par nom des colonnes plutôt que par les numéros

Règles de sécurité sur MySQL (partie 1)

Comme tout SGBD, MySQL dispose d’un système de sécurité assez avancé, mais malheureusement non standardisé (exemple la notion de rôle n’existe pas dans MySQL).
Rôle du système de privilèges
La fonction première du système de privilèges de MySQL est d’authentifier les utilisateurs se connectant à partir d’un hôte donné, et de leur associer des privilèges sur une base de données comme SELECT, UPDATE, DELETE, CREATE VIEW, CREATE TABLE, etc….
Le système de droits de MySQL s’assure que les utilisateurs font exactement ce qu’ils sont supposés pouvoir faire dans la base de données. Lorsque vous vous connectez au serveur, votre identité est déterminée par l’hôte d’où vous vous connectez et le nom d’utilisateur que vous spécifiez. Le système donne les droits en fonction de votre identité et de ce que vous voulez faire
Le contrôle d’accès de MySQL se fait en deux étapes :
1. Le serveur vérifie que vous êtes autorisé à vous connecter.
2. En supposant que vous pouvez vous connecter, le serveur vérifie chaque requête que vous soumettez, pour vérifier si vous avez les droits suffisants pour l’exécuter. Par exemple, si vous sélectionnez des droits dans une table, ou effacez une table, le serveur s’assure que vous avez les droits de SELECT pour cette table, ou les droits de DROP respectivement
Si vos droits ont changé (par vous-mêmes ou bien par un administrateur), durant votre connexion, ces changements ne prendront peut-être effets qu’à la prochaine requête
Le serveur stocke les droits dans des tables de droits, situées dans la base
MySQL. Il lit le contenu de ces tables en mémoire lorsqu’il démarre, et les relit dans différentes circonstances.
Quand les modifications de privilèges prennent-ils effets ?
Lorsque mysqld est lancé, toutes les tables de droits sont lues, et sont utilisées. Les modifications aux tables de droits que vous faites avec GRANT, REVOKE sont immédiatement prises en compte par le serveur.
Lorsque le serveur remarque que les tables de droits ont été modifiées, les connexions existantes avec les clients sont modifiées comme ceci :
 Les droits de table et colonnes prennent effet à la prochaine requête du client.
 Les droits de bases prennent effet à la prochaine commande USE nom_de_base
 Les droits globaux et les modifications de droits prennent effets lors de la prochaine connexion.
Les commandes GRANT et REVOKE (à partir de la version 3.22.11 de MySQL)
Ces commandes permettent à l’administrateur système de créer et supprimer des comptes utilisateur et de leur donner ou retirer des droits.
Les informations sur les comptes MySQL sont stockées dans la base MySQL
Les commandes GRANT et REVOKE sont utilisées pour contrôler les accès à MySQL. Ne donnez pas plus de droits que nécessaire. Ne donnez jamais de droits à tous les serveurs hôte
Les droits sont donnés à 4 niveaux :

Niveau global

Les droits globaux s’appliquent à toutes les bases de données d’un serveur. Ces droits sont stockés dans la table mysql.user
REVOKE ALL ON *.* retirera seulement les privilèges globaux.
Niveau base de données
Les droits de niveau de base de données s’appliquent à toutes les tables d’une base de données. Ces droits sont stockés dans les tables mysql.db et mysql.host
REVOKE ALL ON db.*
Retirera seulement les privilèges de base de données.
Niveau table
Les droits de table s’appliquent à toutes les colonnes d’une table. Ces droits sont stockés dans la table mysql.tables_priv
REVOKE ALL ON db.table
Retirera seulement les privilèges de table.
Niveau colonne
Les droits de niveau de colonnes s’appliquent à des colonnes dans une table. Ces droits sont stockés dans la table mysql.columns_priv

Exemples:

En tant que ROOT
create user user1 identified by ‘user1’;
grant all on *.* to user1;
et surtout pas:
grant all privileges on *.* to user1 with grant option;
Après la création de l’utilisateur user1, il a tous les droits, donc de créer sa propre base de données. Éviter de donner ce droit à n’importe qui.
Conseils : si vous administrez un serveur MySQL, créer la base de données puis donner les droits sur celle-ci.
Toujours en tant que root : (le user2 aura le droit de créer et de gérer ses propres objets dans la base de données dbuser2.
create user user2 identified by ‘user2’;
create database dbuser2;
grant all on dbuser2.* to user2 ;
Si l’option with grant option est précisée alors il pourra donner les mêmes droits sur sa bd à un autre utilisateur.
Cette commande permet de donner tous les droits à ruby mais uniquement sur la table livres de la base de données labo1 du User root
grant all on labo1.livres to ruby;
Voici une façon plus intéressante d’attribuer les droits (on va les restreindre au maximum).
grant select on Gestion.departements to ruby;
grant insert on Gestion.departements to ruby;
grant update on Gestion.departements to ruby;
grant select on Gestion.abscences to ruby;
grant update(adresse) on Gestion.employes to ruby; grant create view on Gestion.abscences to ruby;
Pour le update et le insert on peut préciser les colonnes autorisées.
grant insert(num,nom,prenom) on Gestion.employes to ruby;
On peut attribuer plusieurs droits en une seule commande.
grant create view , select, update(salaire) on Gestion.employes to ruby;
Pour enlever les droits, utilisez REVOKE.
revoke select on Gestion.departements from ruby; revoke all on Gestion.departements from ruby;
PDO (PHP Data Object) par l’exemple
Il existe 3 façons d’accéder aux bases de données par PHP : Mysql qui utilise des requêtes mais pas de procédures stockées, Mysqli très performant puisque du côté serveur; elle est propre à MYSQL(un peu comme OCI de Oracle) et enfin PDO qui est du côté PHP (peut-être moins performant mais très standard puis que cette méthode est du côté application (un peu comme le JDBC)
PDO est donc une API d’accès aux données (n’importe qu’elle SGBD), orienté objet utilisé par PHP. Il est disponible avec les versions PHP 5 et plus.
PDO présente plusieurs avantages (mis à part qu’il est objet) il s’utilise avec les procédures stockées et les requêtes paramétrées ce qui permet de se protéger contre les injections SQL.
Les classes sont :
PDO : une instance de PDO représente la connexion à une base de données. Le plus souvent une seule instance de PDO par exécution de PHP. Cette classe contient entre autre les méthodes exec(), query() et prepare()
PDOStatement : une instance de PDOStatement représente une requête vers la base de données. Permet de préparer la requête puis de consulter son résultat. Cette classe contient entre autre les methodes fetch(), bindParmeter(), rowCount(),execute()
PDOException pour la gestion des Exceptions.
Étapes pour le traitement des commandes SQL :
Étape 0 : Activer PDO :
Normalement, PDO est activé par défaut. Pour le vérifier (voir la figure suivante), faites un clic gauche sur l’icône de WAMP dans la barre des tâches, puis allez dans le menu PHP / Extensions PHP et vérifiez que php_pdo_mysql est bien coché.

Introduction
Les types de données
Les entiers
NUMERIC et DECIMAL
FLOAT, DOUBLE et REAL
Les Dates
Pour les chaines de caractères
Le type ENUM
Et les procédures et fonctions?
Et l’optimisation de requête ?
Rôle du système de privilèges
Quand les modifications de privilèges prennent-ils effets ?
Les commandes GRANTet REVOKE ( à partir de la version 3.22.11 de MySQL)
Tableau des Privillèges pour GRANT and REVOKE
Exemples
Application : Exercice
PDO (PHP Data Object) par l’exemple
Étapes pour le traitement des commandes SQL
Étape 2 : envoyer des requêtes à la base de données
Cas1 : Requêtes exécutées une seule fois
Cas 2 : Requêtes avec paramètres
Cas3 : Appel de procédures
Exemple 1
Exemple 2
Exemple 3
Compléments
PDOStatement::fetch
Description
Liste de paramètres
Sources