Dans la littérature consacrée, les lignes sont enregistrées dans des pages (Oracle pour se distinguer parle de "bloc"
dont la taille est estimée en tenant compte :- du type du système d'exploitation
- du file système sous-jacent
Par exemple
- sous Windows c'est 8 Ko
- Sous Linux c'est souvent du 8 Ko aussi pour la plupart des FS
- Pour ZOS / DB2 c'est de 4 à 32 Ko en fonction de la teille des partitions de stockage...
Cette métrique est calculée par des abaques... Page trop petite = beaucoup d'IO (goulet d'étranglement de la fille d'attente pour accès concurrentiel au disque), trop grande = moins rapide à lire/écrire (encombrement de la mémoire, augmentation du temps d'accès).
Pour les SGBDR d'entreprise qui gèrent leur propre stockage comme Microsoft SQL Server ou Oracle (mais ni MySQL ni PostgreSQL), c'est le moteur de stockage qui assure directement les entrées/sorties (appelées IO en anglais pour Input/Output) dans ce cas la plupart du temps ces pages sont elle même mise dans des groupes de pages contiguës (voire figure 1). Par exemple pour MS SQL Server ce sont des blocs de 8 pages, soit 64 Ko, appelés "extensions" (extents en anglais). Ceci toujours pour des raisons d'efficacité...
Figure 1 - les blocs de 8 pages contigües de SQL Server sont appelées EXTENSIONS (extents en anglais) et peuvent être uniforme (les 8 pages appartiennent à un seul objet : table ou index) ou mixtes (chaque page peut appartenir à un objet différent).
En effet, les SGBDR manipulent les données uniquement en mémoire... Toute lecture (SELECT...), comme toute écriture INSERT, UPDATE, DELETE, MERGE, TRUNCATE, CREATE, ALTER, DROP... est effectuée en mémoire, mais les écritures seront reportées de la mémoire vers les fichiers de données, de temps en temps, de manière asynchrone, par le biais d'une commande interne nommée CHECKPOINT. Cette commande parcoure la mémoire à la recherche des pages "sales" (dirty pages) c'est à dire des pages désynchronisées par rapport aux fichiers de données. Toute écriture physique nécessite la réécriture de l'intégralité de la page, car dans chaque page il y a des métadonnées connexes (par exemple dans MS SQL Server chaque page contient, en sus des lignes et des identifiants de l'objet auquel appartient la page, le nombre d'octets libre, le nombre de ligne vivante et une somme de contrôle pour vérifier l'intégrité du stockage).
Autrement dit chaque écriture mémoire vers disque (IO) est une page.
Au niveau lecture disque vers mémoire, c'est par blocs de page que cela se passe, ce qui permet de faire des lectures anticipées (Par exemple pour MS SQL Server mettre en mémoire 8 pages, même si une seule a été demandée du fait de la lecture par extension).
À l'intérieur de la page (voire figure 2), les lignes sont mises à la queue-leu-leu, sans ordre particulier si c'est une table, avec un placement spécifique pour un index (BTree, hash, bitmap....)
Figure 2 - Structure interne d'une page de données
- L'entête de page contient les métadonnées (par exemple pour SQL Server id de la base, id du fichier, id de la page, id de la table, id de l'index, nombre de lignes vivantes, nombre d'octets libres... en tout 96 octets),
- le bas de la page contient un tableau des offsets de ligne qui indique à quel offset en octet commence telle ou telle ligne (le tableau se lit à l'envers...)
- Entre l'entête et le tableau des offsets de ligne figurent les lignes qui sont généralement de longueur variable (VARCHAR, VARBINARY...).
Cette disposition permet de restreindre l'espace libre par le fait que les lignes croissent vers le bas tandis que que le tableau croit vers le haut.... À la fin il ne reste quasiment plus de place !
Enfin en ce qui concerne les données qui sont dans les lignes, ces dernières sont mise à la queue-leu-leu, soit par leur position ordinale dans la définition de la table (voir dans SELECT * FROM INFORMATION_SCHEMA.COLUMNS) mais dans certains SGBDR haut de gamme comme MS SQL Server une disposition particulière permet d'en accélérer les accès (voire figure 3)...
Figure 3 - Disposition particulière des informations dans SQL Server afin d'optimiser les accès aux données
En effet dans le cas de SQL Server une colonne de taille fixe verra sa données toujours stocké à un offset identique par rapport au début de la ligne et une colonne de taille variable, nécessitera une seule lecture supplémentaire. Dans PostGreSQL, les informations étant stockées les unes après les autres, à l'aide d'un code séparateur, il faut lire successivement toutes les valeurs précédentes pour accéder à la bonne. Par exemple si l'information est cherché dans la 10e colonne, alors il faut lire la ligne octets par octets et s'arrêter après le 9e octets séparateur pour lire le début de l'information recherchée, et s'arrêter au séparateur suivant... ce qui est plus long...
Autre optimisation SQL Server pour les données de taille variables, si l'on cherche un littéral dont la longueur est de 23 octets par exemple, inutile d'aller lire les zones de plus de moins de 23 octets... Donc, une seule lecture...
Enfin, pour les mises à jour, c'est dernières sont écrites d'abord dans le journal de transaction qui assure la persistance pour pouvoir :
- revenir à l'état initial des données en cas d'annulation de la transaction (toute écriture est une transaction journalisée)
- permettre de récupérer les dernières mise à jours non encore répercutées au niveau des fichiers de données en cas de crash du système.
L'ensemble des commandes est décrit en figure 4... depuis la mise à jour d'une information par l'utilisateur jusqu'au CHECKPOINT.
Figure 4 - Gestion des données entre cache (RAM) et disques lors des transactions d'écriture
Tout ceci est extrait de mon livre sur MS SQL Server...
A +
Envoyé par SQLpro
