Organisation et méthode d’accès pour les SGBD

1. Organisation et méthode d’accès pour les SGBD D ’après les supports des cours de J. Ullman (Stanford), Ph Rigaux (CNAM), R Godin (Montréal)

2. Plan du cours • Organisation sur les disques • Un modèle d’organisation des données : les B-Arbres • Optimisation des requêtes SQL

3. Organisation des données sur disque • Le domaine du SGF (Système de Gestion de Fichiers, «  file system ») qui fait partie du système d’exploitation • Stockage et accès à de gros volumes de données • Abstraction des mémoires secondaires sous forme d'un ensemble de fichiers • Différents modèles selon l’OS

4. Gestion des données • Données persistantes en mémoire secondaire • unité de disque • Organisation physique des données • façon dont les données sont structurées en mémoire secondaire • Méthode d'organisation des données • structure de données particulière utilisée pour organiser les données en mémoire secondaire

5. Caractéristiques d’un disque

6. Capacité d’un disque • capacitéDisque = nbSurfacesnbCylindresnbSecteursParPistetailleSecteur • = 20 surfaces  1000 cylindres  50 secteurs par piste  512 octets • = 512,000,000 octets  500,000 kilooctets(K)  500 mégaoctets (M)

7. Transfert d’un secteur • Secteur • unité d'adressage et de transfert minimal • Adresse physique de secteur • numéro de surface (noSurface), numéro de cylindre (noCylindre), numéro de secteur dans la piste (noSecteur) • Adresse relative de secteur (noSecteurRelatif) • dans l'intervalle [0..n-1] • Tampon (buffer ) = zone de transit

8. Coûts d’Entrée/Sortie • Temps de transfert (E/S) de n octets • TempsESDisque(n) = TempsPosDébut+ TempsTrans (n) • TempsPosDébut= TempsDépBras + TempsRotation TempsDépBras : 6-25 ms (6ms) • TempsRotation : 4,18 à 8,35 ms = 60 à 120 tours/sec (4ms) • TempsTrans(n) = n / TauxTransVrac • TauxTransVrac = NombreOctetsPiste / TempsRotationComplète (2M/sec) • ex: TempsTrans(2K) = 2K / 2M/sec = 1ms • ex:TempsESDisque(2K) = 10ms+ 1ms = 11ms • Minimiser le nombre d'entrées/sorties en mémoire secondaire

9. Impact de la continuité physique • Ex: transfert de 2000 secteurs de 512 octets (1M) • Secteurs consécutifs • TempsESDisque(1M) = 10ms+ 500ms = 510ms • Secteurs dispersés aléatoirement • TempsESDisque(un secteur) = 10ms+ 0,25ms = 10,25ms • Total = 2000  10,25 = 20 500 ms = 20,5 secs • Effet de grappe (clustering) • regrouper physiquement selon patrons d ’accès logiques

10. Autres types de mémoire

11. Critères de comparaison • Temps d'accès aux données par rapport à différentes méthodes d'accès • sériel, sélection, ... • Délai d'insertion et de suppression • Occupation mémoire

12. Plan du cours • Organisation sur les disques • Un modèle d’organisation des données : les B-Arbres • Optimisation des requêtes SQL

13. Organisation des données • Allocation en vrac à la création du fichier • Croissance de la taille du fichier ??? • Fragmentation externe

14. Organisation des données • Granule d'allocation d'espace (segment, cluster, extent) • unité d'allocation d'espace • ensemble de blocs consécutifs • Fragmentation du fichier (file fragmentation) Défragmentation

15. Indexation et hachage • Sélection basée sur une clé d'accès • recherche associative • Sériel • lire tout le fichier en pire cas • O(N) • Indexage • O(log(N)) • sélection par intervalle • Hachage • ~O(1)

16. Indexation • Index et clé d'index (index key) • valeur de la clé =>adresse de(s) l'enregistrement

17. Fichier séquentiel indexé • Non dense • Index plus petit • Accès séquentiel rapide • Primaire

18. B-Arbre • Arbre-B (B-arbre,B-tree) • forme d ’index hiérarchique • équilibré • O(log(N)) en pire cas • Ré-organisation dynamique • division/fusion des blocs • taux d ’occupation minimum de 50%

19. B-Arbre

20. Structure d’une feuille • 1. Remplie à moitié au minimum FBMf/2 ≤ n = nombre de clés ≤ FBMf • 2. Clés triées : i < j Ci < Cj • 3. Clés d'une feuille < clés de la suivante • 4. Au même niveau (équilibré) • Ci : Clé • Ri : reste de l'enregistrement ou référence • S : Pointeur sur le bloc suivant dans la liste des feuilles

21. Structure d’un noeud • 1. Remplie à moitié au minimum: • OrdreI /2 ≤ n = nombre de pointeurs ≤ OrdreI • 2. Clés triées : i < j Ci < Cj • 3. Ci-1 <= Clés sous Pi-1 < Ci

22. B-Arbre • Algorithmes complexes et intéressants pour insérer / détruire une valeur • Respecter l’équilibre • Sources des algorithmes sur http://deptinfo.cnam.fr/Enseignement/CycleA/SD/source_demo/Demo_j.htm • Démos sur http://deptinfo.cnam.fr/Enseignement/CycleA/SD/demonstration/B-arbre.html

23. Hachage • Hachage ou adressage dispersé (hashing) • Fonction h(clé de hachage) => l'adresse d'un paquet • Habituellement paquet = bloc • Pas d ’index à traverser : O(1) en meilleur cas • Sélection par égalité (pas intervalle)

24. Hachage statique

25. Hachage statique • Problème de débordement dû aux collisions • Zone de débordement nécessaire • Pire cas vs. meilleur cas • Répartition uniforme des clés dans [0..TH-1]

26. Plan du cours • Organisation sur les disques • Un modèle d’organisation des données : les B-Arbres et fonction de hachage • Optimisation des requêtes SQL

27. Optimisation • Les langages de requêtes de haut niveau comme SQL sont déclaratifs. L'utilisateur: • indique ce qu'il veut obtenir. • n'indique pas comment l'obtenir. • Donc le système doit faire le reste: • Déterminer le (ou les) chemin(s) d'accès aux données, • les stratégies d'évaluation de la requête • Choisir la meilleure. Ou une des meilleures …

28. Optimisation • $CINEMA (Cinéma, Adresse, Gérant) • $SALLE (Cinéma, NoSalle, Capacité) • avec les hypothèses suivantes : • Il y a 300 n-uplets dans CINEMA, occupant 30 pages. • Il y a 1200 n-uplets dans SALLE, occupant 120 pages.

29. Optimisation • Adresse des cinémas ayant des salles de plus de 150 places • On suppose que 5% des cinémas ont cette capacité • En SQL, cette requête s'exprime de la manière suivante : • SELECT Adresse FROM CINEMA, SALLE WHERE capacité >150 AND CINEMA.cinéma = Salle.cinéma

30. Adresse capacité > 150 (boucles) cinéma=cinéma Salle Cinema Jointure d’abord Cout : Jointure : 30 * 120 = 3600 pages Sélection à rajouter

31. Sélection d’abord Cout : Sélection : 120 * 5% = 6 pages Lectures : 120 + 6 Jointure : 30 * 6 = 180 pages Lectures : 180 Total = 306 pages Adresse cinéma=cinéma Capacité > 150 Cinéma Salles

32. Comment optimiser ? • traduire une requête exprimée avec un langage déclaratif en une suite d'opérations (typiquement opérateurs de l'algèbre relationnelle). • En fonction (i) des coûts de chaque opération (ii) des caractéristiques de la base, (iii) des algorithmes utilisés, • estimer la meilleure stratégie. • On obtient le plan d'exécution de la requête. • L'exécuter.

33. Paramètres de l’optimisation • Comme on l'a vu sur l'exemple, l'optimisation s'appuie sur des : • règles de ré-écriture des expressions de l'algèbre. • connaissances sur l'organisation physique de la base (index, hachage, …) • statistiques sur la base (taille des relations par exemple). • Un modèle de coût permet de classer les différentes stratégies envisagées.

34. Conclusions à ce cours • Un domaine critique pour l’exécution et l’évaluation des requêtes en un temps raisonnable • Un impératif associant l’utilisateur, le SGBD et le compilateur / optimisateur de requêtes

35. Conclusions à ce module • Un domaine que nous avons examiné rapidement • Une vision opérationnelle voulue et surtout beaucoup de TPs qui devraient vous permettre d’utiliser une BD • Il y a aussi beaucoup de points à aborder …