Administration des Bases de Données Index Fonction Utilité Table

Index
Administration des Bases de Données
Performance
Louis-Claude Canon
[email protected]
Bureau 429C
I
Principal mécanisme pour améliorer les performances d’une base de
données.
I
Structure de données persistante, stockée dans la base de données.
I
Beaucoup de spécifités propres à chaque implémentation.
Point de vue de l’application ou de l’utilisateur.
Licence 3 informatique – Semestre 6
Louis-Claude Canon
ABD – Performance
Fonction
1
2
3
4
5
6
7
A
’chat’
’chien’
’vache’
’chien’
’chat’
’chat’
’vache’
...
ABD – Performance
2 / 12
Utilité
B
2
5
1
9
2
8
6
...
Louis-Claude Canon
C
...
...
...
...
...
...
...
...
I
Index sur T.A
I
I
I
I
I
I
T.B = 2
T.B < 6
4 < T.B <= 8
I
I
T.A = ’chat’ AND T.B > 5
T.A > ’c’ AND T.B = 1
ABD – Performance
I
Plusieurs ordres de grandeurs.
Cas d’utilisation :
I
Index sur T.(A,B)
I
Index = différence entre une recherche intégrale sur la table et une
localisation immédiate des tuples.
I
Index sur T.B
I
I
I
T.A = ’vache’
T.A = ’chat’
Sélections sur un ensemble d’attributs.
Jointures sur plusieurs tables (création automatique d’index sur les clés
primaires).
Structures de données sous-jacentes.
I
I
Table de hachage.
Arbre équilibré (arbres B et B+).
Louis-Claude Canon
3 / 12
Table de hachage
ABD – Performance
4 / 12
Arbre équilibré
I
Accélère les sélections avec une contrainte d’égalité (T.A = V).
I
Améliore les performances des jointures.
I
Coût constant.
Louis-Claude Canon
Louis-Claude Canon
1 / 12
ABD – Performance
5 / 12
I
Prend en compte l’égalité et les comparaisons (T.A < V et V1 <= T.A
<= V2).
I
Accélère la recherche de minimum (MIN) ou le tri de l’affichage (ORDER
BY).
I
Coût logarithmique.
Louis-Claude Canon
ABD – Performance
6 / 12
Exemples
1
2
Inconvénients
SELECT id FROM É tudiant
WHERE nom = ’ Marie ’ AND moyenne > 15
I
I
I
1
2
Index sur nom (table de hachage ou arbre).
Index sur moyenne (arbre).
Index sur (nom, moyenne).
I
Coût mémoire (marginal).
I
Coût lors de la création de l’index (médium).
I
Coût d’indirection pour accéder aux données (peut dépasser le gain).
I
Coût de maintenance (peut dépasser le gain).
SELECT nom , nomUniv FROM É tudiant , C a n d i d a t u r e
WHERE É tudiant . id = C a n d i d a t u r e . id
I
Index sur Étudiant.id ou Candidature.id.
Louis-Claude Canon
ABD – Performance
Louis-Claude Canon
7 / 12
Choix des index à créer
ABD – Performance
8 / 12
Conception physique assistée
Entrées Base de données (statistiques) et accès.
Sorties Recommandations de création d’index (les avantages
compensent-ils les inconvénients ?).
Le gain dépend de :
I
La taille de la table (et de sa structure).
I
La distribution des données.
I
Le rapport lecture/écriture.
Louis-Claude Canon
ABD – Performance
Optimisateur de requêtes
Identifie le meilleur plan d’exécution avec un coût estimé à partir de :
I
Statistiques de la base de données.
I
Accès en lecture et écriture.
I
Index.
Louis-Claude Canon
9 / 12
Syntaxe SQL
ABD – Performance
10 / 12
Index
CREATE INDEX nomIndex ON T(A) AS
CREATE INDEX nomIndex ON T(A1,A2,...,An) AS
CREATE UNIQUE INDEX nomIndex ON T(A) AS
DROP INDEX nomIndex
I
Principal mécanisme pour améliorer les performances d’une base de
données.
I
Structure de données persistante, stockée dans la base de données.
I
Beaucoup de spécifités propres à chaque implémentation.
Point de vue de l’application ou de l’utilisateur.
Louis-Claude Canon
ABD – Performance
11 / 12
Louis-Claude Canon
ABD – Performance
12 / 12
Introduction
Administration des Bases de Données
Sécurité
Louis-Claude Canon
[email protected]
Bureau 429C
I
Garantir que les utilisateurs ne voient que les données qu’ils sont
supposés voir.
I
Protéger la base de données des modifications d’utilisateurs
malveillants.
Licence 3 informatique – Semestre 6
Louis-Claude Canon
ABD – Sécurité
1 / 11
Contrôle des accès
Louis-Claude Canon
ABD – Sécurité
2 / 11
Exemples de droits nécessaires
Les utilisateurs ont des droits et ne peuvent accéder qu’aux données pour
lesquelles ils en ont l’autorisation.
I
SELECT ON T ou SELECT(A1, ..., An) ON T
I
INSERT ON T ou INSERT(A1, ..., An) ON T
I
UPDATE ON T ou UPDATE(A1, ..., An) ON T
I
DELETE ON T
1
2
UPDATE C a n d i d a t u r e SET d é cision = ’O ’
WHERE id IN ( SELECT id FROM É tudiant WHERE moyenne > 1 5 )
Candidature UPDATE(décision), SELECT(id)
Étudiant SELECT(id,moyenne)
1
2
DELETE FROM É tudiant
WHERE id NOT IN ( SELECT id FROM C a n d i d a t u r e )
Étudiant DELETE, SELECT(id)
Candidature SELECT(id)
Louis-Claude Canon
ABD – Sécurité
3 / 11
Louis-Claude Canon
ABD – Sécurité
4 / 11
Exemple d’accès restreints aux données (1)
Exemple d’accès restreints aux données (2)
Limiter les accès en lecture aux étudiants candidatant pour l’université de
Franche-Comté uniquement.
Limiter les suppressions aux candidatures pour l’université de Bourgogne.
1
2
3
CREATE VIEW UFC É tudiant AS
SELECT ∗ FROM É tudiant WHERE id IN
( SELECT id FROM C a n d i d a t u r e WHERE nomUniv = ’ UFC ’ )
CREATE VIEW U B C a n d i d a t u r e AS
SELECT ∗ FROM C a n d i d a t u r e WHERE nomUniv = ’ UB ’
UBCandidature DELETE
Nécessite des vues modifiables.
UFCÉtudiant SELECT
Louis-Claude Canon
1
2
ABD – Sécurité
5 / 11
Louis-Claude Canon
ABD – Sécurité
6 / 11
Obtenir des droits
Diagramme d’attribution de droits
I
Le créateur d’une table en est le propriétaire.
I
Chaque propriétaire possède tous les droits sur leurs tables et peuvent
attribuer des droits.
I
Chaque utilisateur possédant des droits sur une table peut attribuer
certains de ces droits à d’autres utilisateurs (WITH GRANT OPTION).
I
Un utilisateur peut avoir obtenu des droits de plusieurs utilisateurs.
U1
Syntaxe SQL
GRANT droits ON T TO utilisateurs
[ WITH GRANT OPTION ]
U2
Exemple
1
U3
GRANT SELECT ( id ) , DELETE ON É tudiant TO PUBLIC
U4
Louis-Claude Canon
ABD – Sécurité
7 / 11
Révoquer des droits
Louis-Claude Canon
U5
ABD – Sécurité
8 / 11
Niveaux où se situent les droits
Syntaxe SQL
REVOKE droits ON T FROM utilisateurs
[ CASCADE | RESTRICT ]
CASCADE Révoque les droits attribués transitivement sauf si attribués
par un autre utilisateur : le diagramme d’attribution doit
rester cohérent (fonctionne toujours).
I
Les développeurs d’applications accèdant à la base de données ont
besoin de droits d’écriture.
I
Les utilisateurs finaux accèdant aux données par l’intermédiaire
d’applications n’ont que des droits limités.
RESTRICT S’arrête si CASCADE aurait révoqué d’autres attribution
(défaut) : une seule arrête doit être retirée en laissant le
diagramme d’attribution cohérent.
Louis-Claude Canon
ABD – Sécurité
9 / 11
Conclusion
I
Garantir que les utilisateurs ne voient que les données qu’ils sont
supposés voir.
I
Protéger la base de données des modifications d’utilisateurs
malveillants.
I
Les utilisateurs ont des droits et ne peuvent accéder qu’aux données
pour lesquelles ils en ont l’autorisation.
I
Instructions GRANT et REVOKE.
I
Pour dépasser les droits d’accès sur les tables uniquement : utiliser les
vues.
Louis-Claude Canon
ABD – Sécurité
11 / 11
Louis-Claude Canon
ABD – Sécurité
10 / 11