Le langage SQL : tables, contraintes et droits
Partie 3 sur 4
5Les vues
Une vue est une table virtuelle : ses données ne sont pas stockées dans la base mais calculées à chaque consultation à partir d’une requête SELECT enregistrée. On l’appelle « vue » parce qu’elle représente les données dans le but d’une exploitation, comme une fenêtre qui n’en laisse voir qu’une partie. On l’interroge ensuite comme une table.
CREATE VIEW vue_clients_paris (id, nom_complet) AS
SELECT id, prenom || ' ' || nom
FROM client
WHERE ville = 'Paris';
SELECT * FROM vue_clients_paris WHERE nom_complet LIKE 'A%';
On écrit CREATE VIEW, le nom de la vue, éventuellement le nom des colonnes de sortie (il en faut autant que dans la requête), puis AS et la requête. Le double trait vertical || sert à coller des textes ; son équivalent varie selon les produits (CONCAT chez MySQL).
5.1À quoi servent les vues
La vue joue le rôle d’intermédiaire entre la base de données et l’utilisateur. Trois bénéfices :
- Simplifier. Une requête complexe (avec plusieurs jointures) est écrite une fois, dans la vue, puis utilisée par des personnes qui n’ont qu’à écrire
SELECT * FROM vue_.... Cela regroupe aussi des informations venant de plusieurs tables en un tout cohérent. - Masquer. Une vue peut ne montrer que certaines colonnes (pas le salaire ni le numéro de carte) et que certaines lignes (seulement les clients de la région de l’utilisateur). Combinée avec les droits (partie 8), qui permettent d’autoriser la vue sans autoriser la table, elle donne une « sécurité des données accrue ». C’est aussi un outil de protection des données personnelles : une équipe d’analyse reçoit une vue sans les noms, sans accès à la table de départ (voir le règlement européen sur la protection des données, ou RGPD).
- Stabiliser. Si la structure des tables change, on peut adapter la vue pour qu’elle garde la même forme : les programmes qui l’utilisent n’ont pas à être modifiés.
5.2Ce que la vue ne fait pas
- Elle n’accélère rien. Une vue ordinaire n’est qu’une requête enregistrée ; chaque consultation relance le calcul. Pour garder les résultats, on emploie une vue matérialisée (proposée par plusieurs produits), qui stocke le résultat et se rafraîchit à la demande ou à intervalles réguliers.
- On ne peut pas toujours écrire à travers elle. Une vue simple, sur une seule table, accepte souvent
INSERT,UPDATE,DELETE. Une vue qui regroupe, fait une jointure ou calcule ne le permet généralement pas. L’optionWITH CHECK OPTIONévite qu’on écrive par la vue une ligne que la vue ne montrerait pas ensuite. - Elle n’est pas un coffre-fort. Une vue bien combinée avec des droits limite l’accès, mais mal réglée elle peut en laisser (voir « Le coin cyber »).
Pour s’exercer
Exercice 4 : une vue pour masquer
La table employe contient id, nom, service, salaire. Écrivez une vue destinée à l’annuaire interne, qui ne montre ni le salaire, et qui limite l’accès à la table d’origine.
Voir la réponse
CREATE VIEW annuaire AS
SELECT id, nom, service FROM employe;
GRANT SELECT ON annuaire TO PUBLIC;
REVOKE ALL ON employe FROM PUBLIC;
L’annuaire montre trois colonnes sur quatre ; les utilisateurs ordinaires interrogent la vue, pas la table. Il faut vérifier que les droits sur employe sont bien retirés pour ces comptes : une vue ne masque rien si la table reste lisible. (Le PUBLIC est ici acceptable parce que la vue ne contient que des informations d’annuaire destinées à tous les comptes internes.)
6Les index
Un index est un objet complémentaire de la base, non indispensable, qui permet d’accélérer l’accès aux données. On le compare à l’index d’un livre : vous pouvez vous en passer, mais il vous fait gagner un temps considérable quand vous cherchez un passage précis. Sans index, la base lit toute la table, ligne après ligne, pour trouver celles qui répondent ; avec index, elle va directement aux bonnes lignes, comme on va à la page indiquée par l’index d’un livre. Sur une table de dix millions de lignes, la différence est celle de quelques millisecondes contre plusieurs secondes.
CREATE INDEX idx_client_nom ON client (nom); CREATE UNIQUE INDEX uq_client_email ON client (email); CREATE INDEX idx_cmd_client_date ON commande (client_id, date_cmd DESC);
On écrit CREATE INDEX, le nom de l’index, ON la table, puis les colonnes concernées entre parenthèses. L’option UNIQUE interdit les doublons (un index unique fait le même travail qu’une contrainte d’unicité, et c’est d’ailleurs ainsi qu’elle est mise en œuvre), et ASC ou DESC choisit l’ordre croissant ou décroissant de classement des valeurs. La plupart des systèmes créent automatiquement un index pour la clé primaire et pour les contraintes d’unicité.
6.1Comment ça marche, en gros
La forme d’index la plus courante est l’arbre équilibré (appelé « B-arbre » ou, en anglais, B-tree). Imaginez un dictionnaire : on ouvre au milieu, on regarde si le mot cherché est avant ou après, on divise l’intervalle par deux, et ainsi de suite. En quelques pas on trouve, même parmi des millions d’entrées. L’arbre garde les valeurs triées, ce qui sert aussi aux tris (ORDER BY) et aux recherches par intervalle (« les commandes de mars »). D’autres formes existent pour des besoins particuliers (recherche de texte, données géographiques, valeurs peu variées).
6.2Le coût d’un index
L’index n’est pas gratuit, et c’est un point à ne pas négliger : il utilise de l’espace dans la base, et comme il est mis à jour à chaque modification de la table, il alourdit les écritures. Chaque ajout, modification ou suppression doit aussi mettre à jour tous les index concernés. La création d’index doit donc être justifiée. Il faut choisir des colonnes « de telle façon à minimiser les doublons » : un index sur une colonne qui n’a que deux valeurs (« oui » et « non ») sert à peu de chose, car il ne permet pas de réduire la recherche. En revanche, un index sur un courriel (valeur presque unique) est très efficace.
Quelques règles pratiques :
- Indexer les colonnes qui servent souvent à rechercher (
WHERE), à relier des tables (clés étrangères dans les jointures, voir l’article sur le langage SQL) ou à trier ; - Ne pas indexer à tout hasard : trop d’index ralentissent les écritures et gonflent la base ;
- Pour un index sur plusieurs colonnes, l’ordre compte : un index sur (client, date) sert aux recherches par client, ou par client et date, mais pas à celles par date seule ;
- Mesurer plutôt que deviner. La plupart des produits ont une commande qui montre comment ils exécutent une requête (le plus souvent
EXPLAIN) : elle indique si l’index est utilisé. Un index jamais utilisé se supprime ; - Savoir que certaines écritures de requêtes empêchent l’usage d’un index, par exemple appliquer une fonction à la colonne (
WHERE UPPER(nom) = 'DUPONT') sans index adapté.
Pour s’exercer
Exercice 5 : faut-il un index ?
Une table de commandes (dix millions de lignes) est souvent interrogée par client_id et par plage de dates, très rarement par le champ statut (trois valeurs possibles). Que proposez-vous ?
Voir la réponse
Un index sur (client_id, date_cmd) : il sert aux recherches par client seul ou par client et date. Pas d’index sur statut : trois valeurs seulement, donc peu sélectif, et l’index alourdirait les écritures pour rien. Après création, vérifier avec la commande d’explication de la base (EXPLAIN) que l’index est bien utilisé, et mesurer l’effet sur les insertions.
7Quand la base contient des programmes : PL/SQL et déclencheurs
PL/SQL est le langage de programmation d’Oracle (l’un des grands systèmes de gestion de bases de données) intégré à SQL. SQL décrit ce que l’on veut obtenir ; il n’a ni boucles ni variables ni tests dans sa forme de base. Les produits ajoutent donc un langage procédural, avec lequel on écrit de petits programmes qui tournent dans la base elle-même : procédures stockées (qu’on appelle par leur nom), fonctions et déclencheurs (triggers), exécutés automatiquement quand une table change.
Chaque produit a son dialecte : PL/SQL chez Oracle, PL/pgSQL chez PostgreSQL, un langage de procédures stockées chez MySQL ; la norme SQL en décrit une forme commune, peu suivie à la lettre. L’avantage de faire tourner des programmes dans la base est de rapprocher le traitement des données (moins d’échanges sur le réseau) et de centraliser des règles. L’inconvénient est de rendre la logique moins visible, plus difficile à tester et à versionner que le reste du programme, et de lier fortement l’application à un produit.
7.1Le traitement des erreurs : les exceptions
Une idée, mais importante : PL/SQL offre un moyen d’identifier et de traiter les erreurs, grâce au mécanisme des exceptions. Quand une erreur survient dans un bloc de programme (une division par zéro, une recherche qui ne trouve rien, une contrainte violée), elle est automatiquement transmise à une partie spéciale du bloc, EXCEPTION, qui permet de la traiter proprement au lieu de planter. PL/SQL définit en standard un grand nombre d’exceptions (par exemple celle qui signale une recherche sans résultat), et l’on peut définir les siennes. La forme générale d’un bloc :
DECLARE
v_nom VARCHAR(80);
BEGIN
SELECT nom INTO v_nom FROM client WHERE id = 42;
-- ... suite du traitement ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- aucun client n'a l'identifiant 42 : on le signale proprement
NULL;
END;
On retrouve la même idée dans la plupart des langages de programmation (voir les exceptions) : séparer le cheminement normal du traitement des cas d’erreur, pour que celles-ci ne passent pas inaperçues et ne laissent pas la base dans un état incohérent. Dans une base, cette idée est renforcée par les transactions : un groupe d’opérations réussit en entier ou est annulé en entier.
7.2Les déclencheurs
Un déclencheur est une procédure qui s’exécute automatiquement avant ou après une insertion, une modification ou une suppression. On s’en sert pour tenir un journal des changements (« qui a modifié ce prix, et quand ? »), pour appliquer une règle qu’une simple contrainte ne sait pas exprimer, ou pour calculer une valeur dérivée. Précaution : un déclencheur agit en coulisses, parfois en chaîne avec d’autres, et peut surprendre ceux qui ne le connaissent pas. À réserver aux cas où il n’y a pas d’autre moyen simple, et à documenter.