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

Administration des Bases de Données
Performance
Louis-Claude Canon
[email protected]
Bureau 429C
Licence 3 informatique – Semestre 6
Louis-Claude Canon ABD – Performance 1 / 12
Index
IPrincipal mécanisme pour améliorer les performances d’une base de
données.
IStructure de données persistante, stockée dans la base de données.
IBeaucoup de spécifités propres à chaque implémentation.
Point de vue de l’application ou de l’utilisateur.
Louis-Claude Canon ABD – Performance 2 / 12
Fonction
A B C
1 ’chat’ 2 . . .
2 ’chien’ 5 . . .
3 ’vache’ 1 . . .
4 ’chien’ 9 . . .
5 ’chat’ 2 . . .
6 ’chat’ 8 . . .
7 ’vache’ 6 . . .
. . . . . . . . .
IIndex sur T.A
IT.A = ’vache’
IT.A = ’chat’
IIndex sur T.B
IT.B = 2
IT.B < 6
I4 < T.B <= 8
IIndex sur T.(A,B)
IT.A = ’chat’ AND T.B > 5
IT.A > ’c’ AND T.B = 1
Louis-Claude Canon ABD – Performance 3 / 12
Utilité
IIndex = différence entre une recherche intégrale sur la table et une
localisation immédiate des tuples.
IPlusieurs ordres de grandeurs.
ICas d’utilisation :
ISélections sur un ensemble d’attributs.
IJointures sur plusieurs tables (création automatique d’index sur les clés
primaires).
IStructures de données sous-jacentes.
ITable de hachage.
IArbre équilibré (arbres B et B+).
Louis-Claude Canon ABD – Performance 4 / 12
Table de hachage
IAccélère les sélections avec une contrainte d’égalité (T.A = V).
IAméliore les performances des jointures.
ICoût constant.
Louis-Claude Canon ABD – Performance 5 / 12
Arbre équilibré
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).
ICoût logarithmique.
Louis-Claude Canon ABD – Performance 6 / 12

Exemples
1SELECT id FROM É tu dian t
2W H E R E nom =’ M a r i e ’ AN D moyenne > 15
IIndex sur nom (table de hachage ou arbre).
IIndex sur moyenne (arbre).
IIndex sur (nom, moyenne).
1SELECT nom ,nomUniv FROM É tudi ant ,Candidature
2W H E R E É tudi ant .id =Candidature .id
IIndex sur Étudiant.id ou Candidature.id.
Louis-Claude Canon ABD – Performance 7 / 12
Inconvénients
ICoût mémoire (marginal).
ICoût lors de la création de l’index (médium).
ICoût d’indirection pour accéder aux données (peut dépasser le gain).
ICoût de maintenance (peut dépasser le gain).
Louis-Claude Canon ABD – Performance 8 / 12
Choix des index à créer
Le gain dépend de :
ILa taille de la table (et de sa structure).
ILa distribution des données.
ILe rapport lecture/écriture.
Louis-Claude Canon ABD – Performance 9 / 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 ?).
Optimisateur de requêtes
Identifie le meilleur plan d’exécution avec un coût estimé à partir de :
IStatistiques de la base de données.
IAccès en lecture et écriture.
IIndex.
Louis-Claude Canon ABD – Performance 10 / 12
Syntaxe SQL
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
Louis-Claude Canon ABD – Performance 11 / 12
Index
IPrincipal mécanisme pour améliorer les performances d’une base de
données.
IStructure de données persistante, stockée dans la base de données.
IBeaucoup de spécifités propres à chaque implémentation.
Point de vue de l’application ou de l’utilisateur.
Louis-Claude Canon ABD – Performance 12 / 12

Administration des Bases de Données
Sécurité
Louis-Claude Canon
[email protected]
Bureau 429C
Licence 3 informatique – Semestre 6
Louis-Claude Canon ABD – Sécurité 1 / 11
Introduction
IGarantir que les utilisateurs ne voient que les données qu’ils sont
supposés voir.
IProtéger la base de données des modifications d’utilisateurs
malveillants.
Louis-Claude Canon ABD – Sécurité 2 / 11
Contrôle des accès
Les utilisateurs ont des droits et ne peuvent accéder qu’aux données pour
lesquelles ils en ont l’autorisation.
ISELECT ON T ou SELECT(A1, ..., An) ON T
IINSERT ON T ou INSERT(A1, ..., An) ON T
IUPDATE ON T ou UPDATE(A1, ..., An) ON T
IDELETE ON T
Louis-Claude Canon ABD – Sécurité 3 / 11
Exemples de droits nécessaires
1UPDATE Candidature S E T dé ci sion =’ O ’
2W H E R E id IN (SELECT id FROM É tud iant W H E R E mo yen ne > 1 5)
Candidature UPDATE(décision),SELECT(id)
Étudiant SELECT(id,moyenne)
1D E L E T E F R O M É tu dian t
2W H E R E id N O T I N (SELECT id FROM Candidature)
Étudiant DELETE,SELECT(id)
Candidature SELECT(id)
Louis-Claude Canon ABD – Sécurité 4 / 11
Exemple d’accès restreints aux données (1)
Limiter les accès en lecture aux étudiants candidatant pour l’université de
Franche-Comté uniquement.
1C R E A T E V I E W U FC É tu dia nt AS
2SELECT ∗FROM É tu dian t W H E R E id I N
3 ( SELECT id FROM Candidature W H E R E no mUn iv =’ U F C ’ )
UFCÉtudiant SELECT
Louis-Claude Canon ABD – Sécurité 5 / 11
Exemple d’accès restreints aux données (2)
Limiter les suppressions aux candidatures pour l’université de Bourgogne.
1C R E A T E V I E W UBCandi da ture A S
2SELECT ∗FROM Candidature W H E R E no mUn iv =’ U B ’
UBCandidature DELETE
Nécessite des vues modifiables.
Louis-Claude Canon ABD – Sécurité 6 / 11

Obtenir des droits
ILe créateur d’une table en est le propriétaire.
IChaque propriétaire possède tous les droits sur leurs tables et peuvent
attribuer des droits.
Syntaxe SQL
GRANT droits ON TTO utilisateurs
[ WITH GRANT OPTION ]
Exemple
1G R A N T S E L E C T (id ) , DELETE ON É tud iant TO P UBLI C
Louis-Claude Canon ABD – Sécurité 7 / 11
Diagramme d’attribution de droits
IChaque utilisateur possédant des droits sur une table peut attribuer
certains de ces droits à d’autres utilisateurs (WITH GRANT OPTION).
IUn utilisateur peut avoir obtenu des droits de plusieurs utilisateurs.
U1
U2U3
U4U5
Louis-Claude Canon ABD – Sécurité 8 / 11
Révoquer des droits
Syntaxe SQL
REVOKE droits ON TFROM 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).
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
Niveaux où se situent les droits
ILes développeurs d’applications accèdant à la base de données ont
besoin de droits d’écriture.
ILes utilisateurs finaux accèdant aux données par l’intermédiaire
d’applications n’ont que des droits limités.
Louis-Claude Canon ABD – Sécurité 10 / 11
Conclusion
IGarantir que les utilisateurs ne voient que les données qu’ils sont
supposés voir.
IProtéger la base de données des modifications d’utilisateurs
malveillants.
ILes utilisateurs ont des droits et ne peuvent accéder qu’aux données
pour lesquelles ils en ont l’autorisation.
IInstructions GRANT et REVOKE.
IPour dépasser les droits d’accès sur les tables uniquement : utiliser les
vues.
Louis-Claude Canon ABD – Sécurité 11 / 11