Toutes les bases de données modernes utilisent le CBO (Cost Based Optimization), l'optimisation des coûts. Son essence réside dans le fait que pour chaque opération son « coût » est déterminé, puis coût total La demande est réduite en utilisant les chaînes d'opérations les moins chères.

Pour mieux comprendre l'optimisation des coûts, nous examinerons trois manières courantes de joindre deux tables et verrons comment même une simple requête de jointure peut se transformer en cauchemar pour un optimiseur. Dans notre discussion, nous nous concentrerons sur la complexité temporelle, bien que l'optimiseur calcule le « coût » en termes de ressources processeur, de mémoire et d'opérations d'E/S. C'est juste que la complexité temporelle est un concept approximatif, et pour déterminer les ressources processeur requises, vous devez compter toutes les opérations, y compris l'addition, les instructions if, la multiplication, l'itération, etc.

En plus:

• Pour effectuer chaque opération de haut niveau, le processeur effectue un nombre différent d'opérations de bas niveau.
• Le coût des opérations du processeur (en termes de cycles) varie selon les différents types processeurs, c'est-à-dire que cela dépend de l'architecture spécifique du processeur.

Il nous sera donc plus facile d’évaluer sous forme de complexité. Mais rappelez-vous que les performances de la base de données sont le plus souvent limitées par le sous-système de disque et non par les ressources du processeur.

Nous en avons parlé lorsque nous avons examiné les arbres B. Comme vous vous en souvenez, les index sont déjà triés. À propos, il existe d'autres types d'index, par exemple l'index bitmap. Mais ils n'apportent aucun avantage en termes d'utilisation du processeur, de la mémoire et du disque par rapport aux index B-tree. De plus, de nombreuses bases de données modernes peuvent créer dynamiquement des index temporaires pour les requêtes en cours si cela permet de réduire le coût d'exécution du plan.

4.4.2. Modalités de recours

Avant de pouvoir utiliser les opérateurs syndicaux, vous devez d'abord obtenir les données requises. Vous pouvez le faire des manières suivantes.

• Scan complet. La base de données lit simplement l'intégralité de la table ou de l'index. Comme vous le comprenez, pour le sous-système de disque, il est moins coûteux de lire un index qu'une table.
• Balayage de portée. Utilisé par exemple lorsque vous utilisez des prédicats comme WHERE AGE > 20 AND AGE< 40. Конечно, для сканирования диапазона индекса вам нужно иметь индекс для поля AGE.

  Dans la première partie de l'article, nous avons déjà découvert que la complexité temporelle d'une requête de plage est définie comme M + log(N), où N est le nombre de données dans l'index et M est le nombre estimé de lignes dans la gamme. Nous connaissons les valeurs de ces deux variables grâce aux statistiques. Lors de l'analyse d'une plage, seule une partie de l'index est lue, donc cette opération coûte moins cher qu’une analyse complète.

• Recherchez des valeurs uniques. Utilisé dans les cas où vous n'avez besoin d'obtenir qu'une seule valeur de l'index.
• Accès par ID de ligne. Si la base de données utilise un index, elle recherchera la plupart du temps les lignes qui lui sont associées. Par exemple, nous faisons la demande suivante :

  SÉLECTIONNEZ LE NOM, LE PRÉNOM de LA PERSONNE OÙ AGE = 28
  Si nous avons un index sur la colonne âge, l'optimiseur utilisera l'index pour rechercher tous les 28 ans, puis interrogera les identifiants des lignes correspondantes dans le tableau, car l'index ne contient que des informations sur l'âge.

  Disons que nous avons une autre demande :

  SELECT TYPE_PERSON.CATEGORY à partir de PERSONNE, TYPE_PERSON OÙ PERSONNE.AGE = TYPE_PERSON.AGE
  Pour fusionner avec TYPE_PERSON, un index sur la colonne PERSON sera utilisé. Mais comme nous n'avons pas demandé d'informations à la table PERSON, personne n'y accédera par les ID de ligne.

  Cette approche n'est valable que pour un petit nombre d'appels car elle est coûteuse en termes d'E/S. Si vous devez accéder fréquemment à votre identifiant, il est préférable d’utiliser une analyse complète.

• Autres méthodes. Vous pouvez en savoir plus dans la documentation Oracle. Différentes bases de données peuvent utiliser des noms différents, mais les principes sont les mêmes partout.

4.4.3. Opérations syndicales

Nous savons donc comment obtenir les données, il est temps de les combiner. Mais d’abord, définissons quelques nouveaux termes : dépendances internes et dépendances externes. La dépendance pourrait être :

• tableau,
• indice,
• le résultat intermédiaire d'une opération précédente (par exemple, une jointure précédente).

Lorsque l’on combine deux dépendances, les algorithmes de fusion les gèrent différemment. Disons que A JOIN B est l'union de A et B, où A est une dépendance externe et B est une dépendance interne.

Le plus souvent, le coût de A JOIN B n’est pas égal au coût de B JOIN A.

Supposons que la dépendance externe contient N éléments et la dépendance interne contient M. Comme vous vous en souvenez, l'optimiseur connaît ces valeurs grâce aux statistiques. N et M sont des nombres cardinaux de dépendance.

• Union utilisant des boucles imbriquées. C'est la façon la plus simple de combiner.

  

  Cela fonctionne comme ceci : pour chaque chaîne d'une dépendance externe, des correspondances sont trouvées dans toutes les chaînes de la dépendance interne.

  Exemple de pseudocode :

  Nested_loop_join(array external, array inner) pour chaque ligne a en externe pour chaque ligne b en interne if (match_join_condition(a,b)) write_result_in_output(a,b) end if end for end for
  Puisqu'il s'agit d'une double itération, la complexité temporelle est définie comme O(N*M).

  Pour chacune des N lignes de dépendances externes, vous devez compter M lignes de dépendances externes. Autrement dit, cet algorithme nécessite N + N*M lectures à partir du disque. Si la dépendance interne est suffisamment petite, vous pouvez la mettre entièrement en mémoire, et le sous-système de disque n'aura alors que des lectures M + N. Il est donc recommandé de rendre la dépendance interne aussi compacte que possible afin de la tenir en mémoire.

  Du point de vue de la complexité temporelle, il n’y a aucune différence.

  Vous pouvez également remplacer la dépendance interne par un index, cela économisera les opérations d'E/S.
  Si la dépendance interne ne rentre pas entièrement dans la mémoire, vous pouvez utiliser un autre algorithme qui utilise le disque de manière plus économique.

  • Au lieu de lire les deux dépendances ligne par ligne, elles sont lues par groupes de lignes (bundle), un groupe de chaque dépendance étant stocké en mémoire.
  • Les chaînes de ces groupes sont comparées les unes aux autres et les correspondances trouvées sont stockées séparément.
  • Ensuite, les nouveaux groupes sont chargés en mémoire et comparés les uns aux autres.
  Et ainsi de suite jusqu'à épuisement des groupes.

  Exemple d'algorithme :

  // version améliorée pour réduire les E/S disque. nested_loop_join_v2(fichier externe, fichier interne) pour chaque groupe ba dans externe // ba est maintenant en mémoire pour chaque groupe bb dans interne // bb est maintenant en mémoire pour chaque ligne a dans ba pour chaque ligne b dans bb if (match_join_condition( a,b)) write_result_in_output(a,b) end if fin pour fin pour fin pour fin pour
  Dans ce cas, la complexité temporelle reste la même, mais le nombre d'accès disque est réduit : (nombre de groupes externes + nombre de groupes externes * nombre de groupes internes). À mesure que la taille du groupe augmente, le nombre d'accès au disque diminue encore plus.

  Remarque : dans cet algorithme, plus de données sont lues à chaque accès, mais cela n'a pas d'importance puisque les accès sont séquentiels.

• Rejoindre le hachage. Il s’agit d’une opération plus complexe, mais dans de nombreux cas, son coût est inférieur.

  

  L'algorithme est le suivant :

  1. Tous les éléments de la dépendance interne sont lus.
  2. Une table de hachage est créée en mémoire.
  3. Tous les éléments de la dépendance externe sont lus un par un.
  4. Pour chaque élément, un hachage est calculé (en utilisant la fonction correspondante de la table de hachage) afin que le bloc correspondant dans la dépendance interne puisse être trouvé.
  5. Les éléments du bloc sont comparés aux éléments de la dépendance externe.
  Pour évaluer cet algorithme en termes de complexité temporelle, plusieurs hypothèses doivent être faites :
  • La dépendance interne contient X blocs.
  • La fonction de hachage distribue les hachages presque également pour les deux dépendances. Autrement dit, tous les blocs ont la même taille.
  • Le coût pour trouver une correspondance entre les éléments d’une dépendance externe et tous les éléments à l’intérieur d’un bloc est égal au nombre d’éléments à l’intérieur du bloc.
  La complexité temporelle sera alors égale à :

  (M / X) * (N / X) + hash_table_creation_cost(M) + hash_function_cost * N

  Et si la fonction de hachage crée des blocs suffisamment petits, alors la complexité temporelle sera O(M + N).

  Il existe une autre méthode de jointure par hachage qui est plus efficace en mémoire et ne nécessite pas plus d'opérations d'E/S :

  1. Les tables de hachage sont calculées pour les deux dépendances.
  2. Placé sur disque.
  3. Et puis ils sont comparés étape par étape les uns aux autres (un bloc est chargé en mémoire et le second est lu ligne par ligne).
  Association par fusion. Il s'agit de la seule méthode de fusion qui aboutit à des données triées. Dans cet article, nous considérons un cas simplifié où les dépendances ne sont pas divisées en externes et internes, puisqu'elles se comportent de la même manière. Cependant, dans la vraie vie, ils peuvent différer, par exemple lorsque vous travaillez avec des doublons.

  L’opération de fusion peut être divisée en deux étapes :

  1. (Facultatif) une jointure de tri est effectuée en premier, où les deux ensembles de données d'entrée sont triés par les clés de jointure.
  2. La fusion a alors lieu.
  Tri

  L'algorithme de tri par fusion a déjà été évoqué ci-dessus, dans ce cas, il est tout à fait justifié si l'économie de mémoire est importante pour vous.

  Mais il arrive que des ensembles de données arrivent déjà triés, par exemple :

  • Si la table est organisée nativement.
  • Si la dépendance est un index avec une condition de jointure présente.
  • Si l'union se produit avec un résultat trié intermédiaire.
  Consolidation par fusion

  

  Cette opération est très similaire à l’opération de fusion dans le tri par fusion. Mais au lieu de sélectionner tous les éléments des deux dépendances, nous sélectionnons uniquement les éléments égaux.

  1. Les deux éléments actuels des deux dépendances sont comparés.
  2. S'ils sont égaux, ils sont alors inscrits dans le tableau résultant, puis les deux éléments suivants sont comparés, un pour chaque dépendance.
  3. S'ils ne sont pas égaux, la comparaison est répétée, mais au lieu du plus petit des deux éléments, l'élément suivant de la même dépendance est pris, car la probabilité d'une coïncidence dans ce cas est plus élevée.
  4. Les étapes 1 à 3 sont répétées jusqu'à épuisement des éléments de l'une des dépendances.
  Si les deux dépendances sont déjà triées, alors la complexité temporelle est O(N + M).

  Si les deux dépendances doivent encore être triées, alors la complexité temporelle est O(N * Log(N) + M * Log(M)).

  Cet algorithme fonctionne bien car les deux dépendances sont déjà triées et nous n'avons pas besoin de faire des allers-retours entre elles. Cependant, il y a ici une certaine simplification : l'algorithme ne gère pas les situations dans lesquelles les mêmes données apparaissent plusieurs fois, c'est-à-dire lorsque plusieurs correspondances se produisent. En réalité, une version plus complexe de l’algorithme est utilisée. Par exemple:

  MergeJoin(relation a, relation b) relation sortie entier a_key:=0; entier b_key:=0 ; while (a!=null et b!=null) si (a< b) a_key++; else if (a >b) b_clé++ ; else //Le prédicat de jointure est satisfait write_result_in_output(a,b) //Nous devons être prudents lorsque nous augmentons le nombre entier de pointeurs a_key_temp:=a_key; entier b_key_temp:=b_key; si (a != b) b_key_temp:= b_key + 1; end if if (b != a) a_key_temp:= a_key + 1; end if if (b == a && b == a) a_key_temp:= a_key + 1; b_key_temp := b_key + 1 ; terminer si a_key:= a_key_temp; b_key:= b_key_temp; fin si fin pendant

Quel algorithme choisir ?

S’il existait la meilleure façon de les combiner, toutes ces variétés n’existeraient pas. La réponse à cette question dépend donc de plusieurs facteurs :

• Quantité de mémoire disponible. Si cela ne suffit pas, oubliez une jointure de hachage puissante. Au moins, son exécution est entièrement en mémoire.
• La taille des deux ensembles de données d’entrée. Si vous avez une table grande et une autre très petite, une jointure utilisant des boucles imbriquées fonctionnera plus rapidement, car une jointure par hachage implique une procédure coûteuse de création de hachages. Si vous disposez de deux très grandes tables, la jointure à l’aide de boucles imbriquées consommera toutes les ressources CPU.
• Disponibilité des index. Si vous disposez de deux index B-tree, il est préférable d’utiliser une jointure par fusion.
• Dois-je trier les résultats ? Vous souhaiterez peut-être utiliser une jointure de fusion coûteuse (avec tri) si vous travaillez avec des ensembles de données non triés. Ensuite, à la sortie, vous recevrez des données triées, qu'il est plus pratique de combiner avec les résultats d'une autre union. Ou parce que la requête s'attend implicitement ou explicitement à recevoir des données triées par les opérateurs ORDER BY/GROUP BY/DISTINCT.
• Les dépendances de sortie sont-elles triées ?. Dans ce cas, il est préférable d’utiliser une jointure par fusion.
• Quels types de dépendances utilisez-vous ?. Rejoindre par équivalence (tableA.column1 = tableB.column2) ? Dépendances internes, dépendances externes, produit cartésien ou auto-jointure ? Dans différentes situations, certaines méthodes de fusion ne fonctionnent pas.
• Distribution des données. Si les données sont rejetées par la condition de jointure (par exemple, vous rejoignez des personnes par leur nom de famille, mais il y a souvent des homonymes), alors une jointure par hachage ne doit jamais être utilisée. Sinon, la fonction de hachage créera des buckets avec une très mauvaise distribution interne.
• Est-il nécessaire de fusionner en plusieurs processus/threads ?

Ceux qui ont soif de connaissances peuvent se plonger dans la documentation de DB2, ORACLE et SQL Server.

4.4.4. Exemples simplifiés

Disons que nous devons joindre cinq tables pour obtenir une image complète de certaines personnes. Chaque personne peut avoir :

• Plusieurs numéros de téléphone portable.
• Plusieurs adresses e-mail.
• Plusieurs adresses physiques.
• Plusieurs numéros de comptes bancaires.

Autrement dit, vous devez répondre rapidement à cette demande :

SELECT * parmi PERSONNE, MOBILES, MAILS, ADRESSES, BANK_ACCOUNTS OÙ PERSON.PERSON_ID = MOBILES.PERSON_ID ET PERSON.PERSON_ID = MAILS.PERSON_ID ET PERSON.PERSON_ID = ADRESSES.PERSON_ID ET PERSON.PERSON_ID = BANK_ACCOUNTS.PERSON_ID
L'optimiseur doit trouver meilleur moyen traitement de l'information. Mais il y a deux problèmes:

1. Quelle méthode de jointure dois-je utiliser ? Il existe trois options (jointure par hachage, jointure par fusion, jointure par boucle imbriquée), avec la possibilité d'utiliser 0, 1 ou 2 index. Sans compter que les index peuvent aussi être différents.
2. Dans quel ordre la fusion doit-elle être effectuée ?

Par exemple, vous trouverez ci-dessous des plans possibles pour trois jointures de quatre tables :

Sur la base de ce qui a été décrit, quelles sont vos options ?

1. Utilisez une approche par force brute. À l'aide de statistiques, calculez le coût de chacun des plans d'exécution de requêtes possibles et sélectionnez le moins cher. Mais il existe de nombreuses options. Pour chaque ordre d'adhésion, trois peuvent être utilisés différentes façons syndicats, un total de 34=81 plans d’exécution possibles. Dans le cas d'un arbre binaire, le problème du choix de l'ordre de jointure devient un problème de permutation, et le nombre d'options est de (2 * 4) ! / (4 + 1)!.. De ce fait, dans cet exemple très simplifié, le nombre total de plans d'exécution de requêtes possibles est de 34 * (2 * 4) ! / (4+1) ! = 27 216. Si l'on ajoute à cela les options où une jointure par fusion utilise 0, 1 ou 2 index B-tree, le nombre de plans possibles s'élève à 210 000. Avons-nous mentionné qu'il s'agit d'une requête TRÈS SIMPLE ?
2. Pleure et arrête. Très tentant, mais improductif et il faut de l’argent.
3. Essayez plusieurs forfaits et choisissez le moins cher. Calculez simplement le coût de chacun options possibles Si cela ne fonctionne pas, vous pouvez utiliser un ensemble de données de test arbitraire et y exécuter tous les types de plans afin d’estimer leur coût et de choisir le meilleur.
4. Appliquez des règles intelligentes pour réduire le nombre de plans possibles.
   Il existe deux types de règles :
   1. Des options « logiques », à l’aide desquelles vous pouvez éliminer les options inutiles. Mais ils ne sont pas toujours applicables. Par exemple : « Lors d'une jointure à l'aide de boucles imbriquées, la dépendance interne doit être le plus petit ensemble de données. »
   2. Vous n'êtes pas obligé de rechercher la solution la plus rentable et d'appliquer des règles plus strictes pour réduire le nombre de forfaits possibles. Dites : « si la dépendance est petite, utilisez des jointures en boucle imbriquées, mais jamais de jointure par fusion ou de jointure par hachage ».

Même un exemple aussi simple nous laisse devant un choix immense. Et dans les requêtes réelles, il peut y avoir d'autres opérateurs relationnels : OUTER JOIN, CROSS JOIN, GROUP BY, ORDER BY, PROJECTION, UNION, INTERSECT, DISTINCT, etc. Autrement dit, le nombre de plans d'exécution possibles sera encore plus grand.

Alors, comment la base de données fait-elle le choix ?

4.4.5. Programmation dynamique, algorithme glouton et heuristique

Une base de données relationnelle utilise différentes approches mentionnées ci-dessus. Et la tâche de l'optimiseur est de trouver solution adaptée Pour un temps limité. Dans la plupart des cas, l’optimiseur ne recherche pas la meilleure solution, mais simplement une bonne solution.

La force brute peut convenir à de petites demandes. Et grâce aux moyens d'éliminer les calculs inutiles, même les requêtes de taille moyenne peuvent utiliser la force brute. C'est ce qu'on appelle la programmation dynamique.

L’essentiel est que de nombreux plans d’exécution sont très similaires.

Dans cette illustration, les quatre plans utilisent le sous-arbre A JOIN B. Au lieu de calculer son coût pour chaque plan, nous pouvons le calculer une seule fois, puis utiliser ces données aussi longtemps que nécessaire. En d’autres termes, en utilisant la mémorisation, nous résolvons le problème de chevauchement, c’est-à-dire que nous évitons les calculs inutiles.

Grâce à cette approche, au lieu de la complexité temporelle (2*N)!/(N+1)! nous obtenons « seulement » 3 N. Dans l'exemple précédent avec quatre jointures, cela signifie réduire le nombre de cas de 336 à 81. Si nous prenons une requête avec 8 jointures (une petite requête), alors la réduction de complexité passera de 57 657 600 à 6 561.

Si vous êtes déjà familier avec la programmation dynamique ou l’algorithmique, vous pouvez jouer avec cet algorithme :

Procédure findbestplan(S) si (bestplan[S].cost infinite) renvoie bestplan[S] // sinon bestplan[S] n'a pas été calculé plus tôt, calculez-le maintenant si (S ne contient qu'une seule relation) définissez bestplan[S]. plan et bestplan[S].cost basés sur la meilleure façon d'accéder à S /* Utilisation de sélections sur S et d'indices sur S */ else pour chaque sous-ensemble non vide S1 de S tel que S1 != S P1= findbestplan(S1) P2= findbestplan(S - S1) A = meilleur algorithme pour joindre les résultats de P1 et P2 coût = P1.cost + P2.cost + coût de A si coût< bestplan[S].cost bestplan[S].cost = cost bestplan[S].plan = “execute P1.plan; execute P2.plan; join results of P1 and P2 using A” return bestplan[S]
La programmation dynamique peut être utilisée pour des requêtes plus volumineuses, mais vous devrez saisir règles supplémentaires(heuristique) pour réduire le nombre de plans possibles :


Algorithmes gourmands

Mais si la demande est très volumineuse ou si nous devons obtenir une réponse extrêmement rapidement, une autre classe d'algorithmes est utilisée : les algorithmes gloutons.

Dans ce cas, le plan d'exécution de la requête est construit étape par étape à l'aide d'une certaine règle (heuristique). Grâce à lui, l'algorithme gourmand recherche meilleure solution pour chaque étape séparément. Le plan commence par une instruction JOIN puis à chaque étape un nouveau JOIN est ajouté selon la règle.

Regardons un exemple simple. Prenons une requête avec 4 jointures de 5 tables (A, B, C, D et E). Simplifions quelque peu le problème et imaginons que la seule option consiste à combiner à l'aide d'algorithmes imbriqués. Nous utiliserons la règle « utiliser la jointure au moindre coût ».

• Nous commençons par l'un des tableaux, par exemple A.
• On calcule le coût de chaque union avec A (ce sera à la fois une dépendance externe et interne).
• Nous constatons que A JOIN B nous coûtera le moins cher.
• Ensuite, nous calculons le coût de chaque jointure avec le résultat A JOIN B (nous le considérons également dans deux rôles).
• Nous constatons que l’option la plus rentable sera (A JOIN B) JOIN C.
• Encore une fois, nous évaluons les options possibles.
• A la fin, nous obtenons le plan d'exécution de la requête suivant : (((A JOIN B) JOIN C) JOIN D) JOIN E)/

Le même algorithme peut être appliqué pour évaluer les options commençant par le tableau B, puis C, etc. En conséquence, nous obtenons cinq forfaits parmi lesquels nous choisissons le moins cher.

Cet algorithme est appelé « algorithme du plus proche voisin ».

Nous n'entrerons pas dans les détails, mais avec une bonne simulation de la complexité du tri N*log(N) ce problème Peut être . La complexité temporelle de l'algorithme est O(N*log(N)) au lieu de O(3 N) pour la version complète avec programmation dynamique. Si vous avez une requête volumineuse avec 20 jointures, cela équivaudrait à 26 contre 3 486 784 401. Grande différence, n'est-ce pas ?

Mais il y a une nuance. Nous supposons que si nous trouvons le meilleur moyen de joindre deux tables, nous obtiendrons le coût le plus bas en joignant le résultat des jointures précédentes avec les tables suivantes. Cependant, même si A JOIN B est l’option la moins chère, alors (A JOIN C) JOIN B peut avoir un coût inférieur à (A JOIN B) JOIN C.

Donc, si vous avez désespérément besoin de trouver le forfait le moins cher de tous les temps, nous pouvons vous conseiller d’exécuter à plusieurs reprises des algorithmes gloutons utilisant des règles différentes.

Autres algorithmes

Si vous en avez déjà marre de tous ces algorithmes, vous pouvez sauter ce chapitre. Il n’est pas nécessaire de maîtriser le reste de la matière.

De nombreux chercheurs travaillent sur le problème de trouver le meilleur plan d’exécution des requêtes. Ils essaient souvent de trouver des solutions à des problèmes et à des modèles spécifiques. Par exemple, pour les jointures en étoile, l'exécution de requêtes parallèles, etc.

Des options sont recherchées pour remplacer la programmation dynamique pour l'exécution de requêtes volumineuses. Les mêmes algorithmes gloutons sont un sous-ensemble d’algorithmes heuristiques. Ils agissent selon une règle, mémorisent le résultat d'une étape et l'utilisent pour rechercher Meilleure option pour la prochaine étape. Et les algorithmes qui n'utilisent pas toujours la solution trouvée à l'étape précédente sont appelés heuristiques.

Un exemple est celui des algorithmes génétiques :

• Chaque solution représente un plan pour l'exécution complète de la demande.
• Au lieu d’une solution (plan), l’algorithme génère X solutions à chaque étape.
• Tout d’abord, des plans X sont créés, cela se fait de manière aléatoire.
• Parmi ceux-ci, seuls les plans dont la valeur satisfait à un certain critère sont sauvegardés.
• Ces plans sont ensuite mixés pour créer X nouveaux plans.
• Certains des nouveaux plans sont modifiés de manière aléatoire.
• Les trois étapes précédentes sont répétées Y fois.
• Des plans du dernier cycle, nous obtenons le meilleur.

Plus il y a de cycles, moins le plan peut être calculé. Sélection naturelle, consolidation des traits, c’est tout.
D'ailleurs, les algorithmes génétiques sont intégrés dans PostgreSQL.

La base de données utilise également des algorithmes heuristiques tels que le recuit simulé, l'amélioration itérative et l'optimisation en deux phases. Mais ce n'est pas un fait qu'ils soient utilisés dans systèmes d'entreprise, peut-être que leur destin est celui des produits de la recherche.

4.4.6. De vrais optimiseurs

Également un chapitre facultatif, vous pouvez le sauter.

Laissons de côté la théorie et considérons exemples réels. Par exemple, comment fonctionne l'optimiseur SQLite. Il s'agit d'une base de données « légère » qui utilise optimisation simple basé sur un algorithme glouton avec des règles supplémentaires :

• SQLite ne modifie jamais l'ordre des tables dans une opération CROSS JOIN.
• L'union utilisant des boucles imbriquées est utilisée.
• Les jointures externes sont toujours évaluées dans l'ordre dans lequel elles ont été effectuées.
• Jusqu'à la version 3.8.0, l'algorithme glouton du plus proche voisin est utilisé pour trouver le meilleur plan d'exécution des requêtes. Et depuis la version 3.8.0, « N voisins les plus proches » (N3, N Nearest Neighbours) est utilisé.

Voici un autre exemple. Si l'on lit la documentation IBM DB2, on découvre que son optimiseur est utilisé 7 différents niveaux optimisations :

• Algorithmes gourmands :
  • 0 - optimisation minimale. Utilise l'analyse d'index, les jointures de boucles imbriquées et évite la réécriture de certaines requêtes.
  • 1 - faible optimisation
  • 2 - optimisation complète
• Programmation dynamique:
  • 3 - optimisation moyenne et approximation grossière
  • 5 - optimisation complète, toutes les techniques heuristiques sont utilisées
  • 7 - la même chose, mais sans heuristique
  • 9 - optimisation maximaleà tout prix, sans égard aux efforts déployés. Tout le monde est évalué moyens possibles syndicats, y compris les produits cartésiens.

Le niveau par défaut est 5. Cela inclut :

• Collecte de toutes les statistiques possibles, y compris les distributions de fréquences et les quantiles.
• Application de toutes les règles de réécriture des requêtes, y compris la création d'une route de table pour les requêtes matérialisées). L'exception concerne les règles qui nécessitent des calculs intensifs et sont utilisées pour un nombre très limité de cas.
• Lors de l'itération des options de jointure à l'aide de la programmation dynamique :
  • L'utilisation de la dépendance interne composée est limitée.
  • Pour les circuits en étoile avec tables de recherche, les produits cartésiens sont utilisés dans une mesure limitée.
• Un large éventail de méthodes d'accès sont couvertes, y compris la prélecture de liste (plus d'informations ci-dessous), l'opération ET spéciale des index et le routage de table pour les requêtes matérialisées.

Bien entendu, les développeurs ne partagent pas de détails sur les heuristiques utilisées dans leur produit, car l'optimiseur est peut-être la partie la plus importante de la base de données. Or, on sait que par défaut, une programmation dynamique, limitée par des heuristiques, est utilisée pour déterminer l'ordre de jointure.

D'autres conditions (GROUP BY, DISTINCT, etc.) sont traitées par des règles simples.

4.4.7. Cache du plan de requête

La création d'un plan prenant du temps, la plupart des bases de données stockent le plan dans un cache de plan de requête. Cela permet d'éviter des calculs inutiles des mêmes étapes. La base de données doit savoir exactement quand elle doit mettre à jour les plans obsolètes. Pour cela, un certain seuil est fixé, et si l'évolution des statistiques le dépasse, alors le plan lié à cette table est supprimé du cache.

Exécuteur de requêtes

Sur à ce stade notre plan a déjà été optimisé. Il est recompilé en code exécutable et, s'il y a suffisamment de ressources, exécuté. Les opérateurs contenus dans le plan (JOIN, SORT BY, etc.) peuvent être traités soit séquentiellement, soit en parallèle ; la décision est prise par l'exécuteur. Il interagit avec le gestionnaire de données pour recevoir et écrire des données.

5. Gestionnaire de données

Le gestionnaire de requêtes exécute la requête et a besoin des données des tables et des index. Il les demande au gestionnaire de données, mais il y a deux difficultés :

• Les bases de données relationnelles utilisent un modèle transactionnel. Il est impossible d'obtenir les données souhaitées à un moment précis, car à ce moment-là, elles peuvent être utilisées/modifiées par quelqu'un.
• La récupération de données est l'opération la plus lente dans une base de données. Par conséquent, le gestionnaire de données doit être capable de prédire son travail afin de remplir la mémoire tampon en temps opportun.

5.1. Gestionnaire de cache

Comme cela a été dit plus d'une fois, le goulot d'étranglement dans la base de données est le sous-système disque. Par conséquent, un gestionnaire de cache est utilisé pour améliorer les performances.

Au lieu de recevoir des données directement de système de fichiers, l'exécuteur de la requête contacte le gestionnaire de cache pour eux. Il utilise un pool de mémoire tampon contenu dans la mémoire, ce qui peut radicalement augmenter les performances de la base de données. Il est difficile de chiffrer cela car cela dépend beaucoup de ce dont vous avez besoin :

• Accès séquentiel (analyse complète) ou aléatoire (accès par ID de ligne).
• Lire ou écrire.

Le type de disques utilisés dans le système de disques est également d'une grande importance : « disques durs » avec différentes vitesses de broche, SSD, présence de RAID dans différentes configurations. Mais nous pouvons dire que l’utilisation de la mémoire est 100 à 100 000 fois plus rapide que celle du disque.

Cependant, nous sommes ici confrontés à un autre problème. Le gestionnaire de cache doit mettre les données en mémoire AVANT que l'exécuteur de requêtes en ait besoin. Sinon, il devra attendre qu'ils soient reçus du disque lent.

5.1.1. Préemption

L'exécuteur de requêtes sait de quelles données il aura besoin car il connaît l'intégralité du plan, les données qui se trouvent sur le disque et les statistiques.

Lorsque l'exécuteur traite le premier bloc de données, il demande au gestionnaire de cache de précharger le bloc suivant. Et lorsqu'il commence à le traiter, il demande au DC de charger la troisième et confirme que la première partie peut être supprimée du cache.

Le gestionnaire de cache stocke ces données dans un pool de mémoire tampon. Il leur ajoute également des informations de service(trigger, latch) pour savoir s'ils sont toujours nécessaires dans le buffer.

Parfois, l'interprète ne sait pas de quelles données il aura besoin, ou certaines bases de données ne disposent pas d'une telle fonctionnalité. Ensuite, la prélecture spéculative est utilisée (par exemple, si l'exécuteur demande les données 1, 3, 5, alors il demandera probablement 7, 9, 11 à l'avenir) ou la prélecture séquentielle (dans ce cas, le DC charge simplement la suivante à partir de disque commande un morceau de données.

Il ne faut pas oublier que le buffer est limité par la quantité de mémoire disponible. Autrement dit, pour charger certaines données, nous devons périodiquement en supprimer d’autres. Le remplissage et l'effacement du cache consomment une partie du sous-système disque et des ressources réseau. Si vous avez une requête qui s’exécute fréquemment, il serait contre-productif de charger et de nettoyer les données qu’elle utilise à chaque fois. Pour résoudre ce problème, les bases de données modernes utilisent une stratégie de remplacement de tampon.

5.1.2. Stratégies de remplacement de tampon

La plupart des bases de données (au moins serveur SQL, MySQL, Oracle et DB2) utilisent pour cela l'algorithme LRU (Least Récemment utilisé). Il est conçu pour conserver dans le cache les données qui ont été récemment utilisées, ce qui signifie qu'il y a une forte probabilité qu'elles soient à nouveau nécessaires.

Pour mieux comprendre le fonctionnement de l'algorithme, nous supposerons que les données dans le tampon ne sont pas bloquées par des déclencheurs (latch), et peuvent donc être supprimées. Dans notre exemple, le tampon peut stocker trois données :

1. Le gestionnaire de cache utilise les données 1 et les place dans un tampon vide.
2. Il utilise ensuite les données 4 et les envoie également au tampon.
3. La même chose est faite avec les données 3.
4. Ensuite, la donnée 9 est récupérée, mais le tampon est déjà plein. Par conséquent, la donnée 1 en est supprimée car elle est restée inutilisée pendant la plus longue période. Après cela, les données 9 sont placées dans le tampon.
5. Le gestionnaire de cache utilise à nouveau les données 4. Elles sont déjà dans le tampon, elles sont donc marquées comme dernière utilisation.
6. La donnée 1 redevient demandée. Pour la placer dans le tampon, la donnée 3 est supprimée car elle n'a pas été utilisée depuis le plus longtemps.

Ce bon algorithme, mais il a certaines limites. Et si nous effectuions une analyse complète d’une grande table ? Que se passe-t-il si la taille de la table/index dépasse la taille du tampon ? Dans ce cas, l'algorithme supprimera tout son contenu du cache, de sorte que les données d'analyse complètes ne seront probablement utilisées qu'une seule fois.

Améliorations de l'algorithme

Pour éviter que cela ne se produise, certaines bases de données utilisent des règles spéciales. D'après la documentation Oracle :

« Pour les très grandes tables, l'accès direct est généralement utilisé, ce qui signifie que les blocs de données sont lus directement pour éviter les débordements de tampon de cache. Pour les tables de taille moyenne, l'accès direct et les lectures en cache peuvent être utilisés. Si le système décide d'utiliser le cache, la base de données place les blocs de données à la fin de la liste LRU pour empêcher le vidage du tampon."

Une version améliorée de LRU, LRU-K, est également utilisée. SQL Server utilise LRU-K avec K = 2. L'essence de cet algorithme est que lors de l'évaluation de la situation, il prend en compte Plus d'information sur les transactions passées, et ne se souvient pas seulement des dernières données utilisées. La lettre K dans le nom signifie que l'algorithme prend en compte les données qui ont été utilisées les K dernières fois. Un certain poids leur est attribué. Lorsque de nouvelles données sont chargées dans le cache, les données anciennes mais fréquemment utilisées ne sont pas supprimées car leur poids est plus élevé. Bien entendu, si les données ne sont plus utilisées, elles seront quand même supprimées. Et plus les données restent longtemps non réclamées, plus leur poids diminue avec le temps.

Le calcul du poids étant assez coûteux, SQL Server utilise LRU-K avec K égal à seulement 2. À mesure que la valeur de K augmente légèrement, l'efficacité de l'algorithme s'améliore. Vous pourrez mieux le connaître grâce à.

Autres algorithmes

Bien entendu, LRU-K n’est pas la seule solution. Il existe également 2Q et CLOCK (tous deux similaires à LRU-K), MRU (Most Récemment utilisé, qui utilise la logique LRU mais applique une règle différente, LRFU (Least Récemment et Fréquemment utilisé), etc. Dans certaines bases de données, vous pouvez choisir ce que un algorithme sera utilisé.

5.1.3. Tampon d'écriture

Nous n'avons parlé que du tampon de lecture, mais les bases de données utilisent également des tampons d'écriture, qui accumulent les données et les transfèrent sur le disque par portions, au lieu d'une écriture séquentielle. Cela enregistre les opérations d’E/S.
N'oubliez pas que les tampons stockent pages(unités de données indivisibles), plutôt que des lignes de tableaux. Une page du pool de mémoire tampon est dite sale si elle est modifiée mais pas écrite sur le disque. Il existe de nombreux algorithmes différents utilisés pour sélectionner le temps d'écriture des pages sales. Mais cela a beaucoup à voir avec le concept de transactions.

5.2. Gestionnaire de transactions

Sa responsabilité est de s'assurer que chaque demande est exécutée en utilisant sa propre transaction. Mais avant de parler du répartiteur, clarifions le concept de transactions ACID.

5.2.1. "On Acid" (jeu de mots, si quelqu'un ne comprend pas)

Une transaction ACID (Atomicité, Isolation, Durabilité, Cohérence) est une opération élémentaire, une unité de travail qui satisfait 4 conditions :

• Atomicité. Il n’y a rien de « plus petit » qu’une transaction, pas de petite opération. Même si la transaction dure 10 heures. Si une transaction échoue, le système revient à l'état « avant », c'est-à-dire que la transaction est annulée.
• Isolement. Si deux transactions A et B sont exécutées en même temps, leur résultat ne devrait pas dépendre du fait que l'une d'elles soit terminée avant, pendant ou après l'exécution de l'autre.
• Durabilité. Lorsqu'une transaction est validée, c'est-à-dire terminée avec succès, les données qu'elle a utilisées restent dans la base de données, quels que soient les incidents possibles (erreurs, plantages).
• Cohérence. Seules les données valides (du point de vue des connexions relationnelles et fonctionnelles) sont enregistrées dans la base de données. La cohérence dépend de l'atomicité et de l'isolement.

Lors de l'exécution de n'importe quelle transaction, vous pouvez exécuter diverses requêtes SQL pour lire, créer, mettre à jour et supprimer des données. Les problèmes commencent lorsque deux transactions utilisent les mêmes données. Un exemple classique est le transfert d’argent du compte A vers le compte B. Disons que nous avons deux transactions :

• T1 prélève 100 $ du compte A et les envoie au compte B.
• T2 prélève 50 $ du compte A et l'envoie également au compte B.

Examinons maintenant cette situation du point de vue des propriétés ACID :

• Atomicité vous permet d'être sûr que quel que soit l'événement qui se produit pendant T1 (panne du serveur, panne du réseau), il ne peut pas arriver que 100 $ soient débités de A, mais ne reviennent pas à B (c sinon parler d’un « État incohérent »).
• Isolement dit que même si T1 et T2 sont effectués simultanément, 100 $ seront débités de A et le même montant ira à B. Dans tous les autres cas, ils parlent à nouveau d'un état incohérent.
• Fiabilité vous permet de ne pas vous inquiéter de la disparition de T1 si la base de données plante immédiatement après la validation de T1.
• Cohérence empêche la possibilité que de l'argent soit créé ou détruit dans le système.

Vous n’êtes pas obligé de lire ci-dessous, ce n’est plus important pour comprendre le reste du matériel.

De nombreuses bases de données ne fournissent pas une isolation complète par défaut, car cela impose une énorme surcharge de performances. SQL utilise 4 niveaux d'isolement :

• Transactions sérialisables. Le plus haut niveau d’isolement. Par défaut dans SQLite. Chaque transaction est exécutée dans son propre environnement complètement isolé.
• Lecture répétable. Par défaut dans MySQL. Chaque transaction possède son propre environnement, sauf dans une situation : si la transaction ajoute de nouvelles données et se termine avec succès, elles seront visibles par les autres transactions encore en cours. Mais si la transaction modifie les données et se termine avec succès, ces modifications ne seront pas visibles pour les transactions encore en cours. Autrement dit, pour les nouvelles données, le principe d'isolement est violé.

  Par exemple, la transaction A exécute

  SELECT count(1) à partir de TABLE_X
  Ensuite, la transaction B ajoute la table X et valide de nouvelles données. Et si après cette transaction A exécute à nouveau count(1), alors le résultat sera différent.

  C'est ce qu'on appelle la lecture fantôme.

• Lire les données validées. Utilisé par défaut dans Oracle, PostgreSQL et SQL Server. C'est la même chose qu'une lecture répétable, mais avec violation supplémentaire isolement. Disons que la transaction A lit des données ; ils sont ensuite modifiés ou supprimés par la transaction B, qui valide ces actions. Si A relit ces données, elle verra les modifications (ou le fait de suppression) apportées par B.

  C'est ce qu'on appelle une lecture non répétable.

• Lire les données non validées. La plupart niveau faible isolement. Une nouvelle violation d'isolement est ajoutée à la lecture des données validées. Disons que la transaction A lit des données ; puis ils sont modifiés par la transaction B (les modifications ne sont pas validées, B est toujours en cours d'exécution). Si A relit les données, il verra les modifications apportées. Si B est annulé, lors d'une nouvelle lecture, A ne verra aucun changement, comme si de rien n'était.

  C’est ce qu’on appelle une lecture sale.

La plupart des bases de données ajoutent leurs propres niveaux d'isolation (par exemple, basés sur des instantanés, comme dans PostgreSQL, Oracle et SQL Server). De plus, de nombreuses bases de données n'implémentent pas les quatre niveaux décrits ci-dessus, notamment la lecture des données non validées.

L'utilisateur ou le développeur peut remplacer le niveau d'isolement par défaut immédiatement après l'établissement de la connexion. Tout ce que vous avez à faire est d’ajouter une ligne de code très simple.

5.2.2. Contrôle de la concurrence

La principale chose pour laquelle nous avons besoin d'isolation, de cohérence et d'atomicité est la possibilité d'effectuer des opérations d'écriture sur les mêmes données (ajouter, mettre à jour et supprimer).

Si toutes les transactions lisent uniquement des données, elles peuvent fonctionner simultanément sans affecter les autres transactions.
Si au moins une transaction modifie les données lues par d'autres transactions, la base de données doit alors trouver un moyen de leur cacher ces modifications. Vous devez également vous assurer que les modifications apportées ne seront pas supprimées par d'autres transactions qui ne voient pas les données modifiées.

C’est ce qu’on appelle le contrôle de concurrence.

Le moyen le plus simple consiste simplement à effectuer les transactions une par une. Mais cette approche est généralement inefficace (un seul cœur d'un processeur est utilisé) et la capacité d'évolutivité est également perdue.

La manière idéale de résoudre le problème ressemble à ceci (à chaque fois qu'une transaction est créée ou annulée) :

• Surveillez toutes les opérations de chaque transaction.
• Si deux transactions ou plus entrent en conflit en raison de la lecture/modification des mêmes données, modifiez l'ordre des opérations au sein des parties au conflit afin de minimiser le nombre de raisons.
• Exécutez les parties contradictoires des transactions dans un ordre spécifique. Les transactions non conflictuelles sont exécutées en parallèle à ce moment-là.
• Veuillez noter que les transactions peuvent être annulées.

Si nous abordons la question de manière plus formelle, il s'agit alors d'un problème de conflits d'horaire. Le résoudre est très difficile et l'optimisation nécessite de grandes ressources processeur. Les bases de données d'entreprise ne peuvent pas se permettre de passer des heures à rechercher le meilleur calendrier pour chaque nouvelle transaction. C’est pourquoi des approches moins sophistiquées sont utilisées, dans lesquelles davantage de temps est consacré aux conflits.

5.2.3. Gestionnaire de verrouillage

Pour résoudre le problème décrit ci-dessus, de nombreuses bases de données utilisent des verrous et/ou une gestion des versions des données.
Si une transaction nécessite des données, elle les bloque. Si une autre transaction les nécessitait également, elle devra alors attendre que la première transaction libère le verrou.

C'est ce qu'on appelle un verrou exclusif.

Mais c’est trop inutile d’utiliser des verrous exclusifs dans les cas où les transactions n’ont besoin que de lire des données. Pourquoi interférer avec la lecture des données ? Dans de tels cas, des verrous partagés sont utilisés. Si une transaction doit lire des données, elle lui applique un verrou partagé et les lit. Cela n'empêche pas d'autres transactions de partager également les verrous et de lire les données. Si l'une d'entre elles doit modifier des données, elle devra alors attendre que tous les verrous communs soient libérés. Ce n'est qu'alors qu'elle pourra appliquer un verrouillage exclusif. Et puis toutes les autres transactions devront attendre son retrait pour pouvoir lire ces données.

Un gestionnaire de verrous est un processus qui applique et libère des verrous. Elles sont stockées dans une table de hachage (les clés sont les données à verrouiller). Le gestionnaire sait pour toutes les données quelles transactions ont appliqué des verrous ou attendent leur libération.

Impasse

L'utilisation de verrous peut conduire à une situation dans laquelle deux transactions attendent indéfiniment que les verrous soient libérés :

Ici, la transaction A a exclusivement verrouillé les données 1 et attend la libération des données 2. Dans le même temps, la transaction B a exclusivement verrouillé les données 2 et attend la libération des données 1.

En cas de blocage, le répartiteur choisit la transaction à annuler (rollback). Et la décision n’est pas si simple à prendre :

• Serait-il préférable de tuer la transaction qui a modifié le dernier ensemble de données (et donc la restauration serait la moins douloureuse) ?
• Serait-il préférable de tuer la transaction la plus récente puisque les utilisateurs des autres transactions ont attendu plus longtemps ?
• Serait-il préférable de tuer une transaction qui prend moins de temps ?
• Combien d’autres transactions seront affectées par la restauration ?

Mais avant de prendre une décision, le répartiteur doit vérifier si une impasse s'est réellement produite.

Imaginons une table de hachage sous forme de diagramme, comme dans l'illustration ci-dessus. S'il existe une relation cyclique dans le diagramme, alors l'impasse est confirmée. Mais comme la vérification des cycles est assez coûteuse (après tout, le diagramme montrant tous les verrous sera assez grand), une approche plus simple est souvent utilisée : utiliser des délais d'attente. Si le verrou n’est pas libéré dans un certain délai, la transaction est entrée dans un état de blocage.

Avant d'appliquer un verrou, le répartiteur peut également vérifier si cela entraînera un blocage. Mais pour répondre sans ambiguïté à cette question, vous devrez également dépenser de l'argent en calculs. C’est pourquoi ces contrôles préalables sont souvent présentés comme un ensemble de règles de base.

Blocage biphasé

Le moyen le plus simple d’obtenir une isolation complète consiste à appliquer un verrou au début et à le libérer à la fin de la transaction. Cela signifie qu'une transaction doit attendre que tous les verrous soient libérés avant de démarrer, et tous les verrous qu'elle a appliqués ne sont libérés qu'une fois terminés. Cette approche peut être utilisée, mais on perd alors beaucoup de temps à attendre que tous les verrous soient libérés.

DB2 et SQL Server utilisent un protocole de verrouillage en deux phases, qui divise une transaction en deux phases :

• Phase de croissance, lorsqu'une transaction peut uniquement appliquer des verrous, mais pas les libérer.
• Phase de rétrécissement, lorsqu'une transaction ne peut que libérer des verrous (sur des données qui ont déjà été traitées et ne seront pas traitées à nouveau), mais pas en appliquer de nouveaux.

Un conflit courant qui se produit en l'absence de verrouillage à deux phases est :

Avant la transaction A, X = 1 et Y = 1. Elle traite les données Y = 1, qui ont été modifiées par la transaction B après le début de la transaction A. En raison du principe d'isolement, la transaction A doit traiter Y = 2.

Objectifs atteints grâce à ces deux règles simples:

• Libérez les verrous qui ne sont plus nécessaires pour réduire les temps d’attente pour d’autres transactions.
• Évitez les cas où une transaction reçoit des données modifiées par une transaction précédemment exécutée. Ces données ne correspondent pas aux données demandées.

Ce protocole fonctionne très bien, sauf dans le cas où la transaction modifie les données et en supprime le verrou, puis est annulée. Dans ce cas, une autre transaction peut lire et modifier les données, qui seront ensuite annulées. Pour éviter cette situation, tous les verrous exclusifs doivent être libérés une fois la transaction terminée.

Bien entendu, les bases de données réelles utilisent des systèmes plus complexes, plus de types verrous et avec une plus grande granularité (verrouillage des lignes, pages, partitions, tables, espaces de table), mais l'essence est la même.

Versionnement des données

Une autre façon de résoudre le problème des conflits de transactions consiste à utiliser la gestion des versions des données.

• Toutes les transactions peuvent modifier les mêmes données en même temps.
• Chaque transaction fonctionne avec sa propre copie (version) des données.
• Si deux transactions modifient les mêmes données, alors une seule des modifications sera acceptée, l'autre sera rejetée et la transaction qui l'a réalisée sera annulée (et éventuellement redémarrée).

Cela permet d’augmenter la productivité car :

• Les transactions de lecture ne bloquent pas les transactions d'écriture, et vice versa.
• Le gestionnaire de verrouillage maladroit n'a aucun impact.

En général, tout vaudra mieux que des verrous, à moins que deux transactions n'écrivent les mêmes données. De plus, cela peut entraîner un énorme gaspillage d’espace disque.

Les deux approches - verrouillage et gestion des versions - présentent des avantages et des inconvénients, tout dépend de la situation dans laquelle elles sont utilisées (plus de lectures ou plus d'écritures). Vous pouvez étudier une très bonne présentation sur l'implémentation du contrôle de concurrence multiversion dans PostgreSQL.

Certaines bases de données (DB2 avant version 9.7, SQL Server) utilisent uniquement des verrous. D'autres, comme PostgreSQL, MySQL et Oracle, utilisent des approches combinées.

Remarque : le versioning a un effet intéressant sur les index. Parfois, il y a des doublons dans un index unique, l'index peut contenir plus d'enregistrements que de lignes dans la table, etc.

Si une partie des données est lue à un niveau d'isolement, puis augmente, le nombre de verrous augmente, ce qui signifie que plus de temps est perdu à attendre les transactions. Par conséquent, la plupart des bases de données n'utilisent pas le niveau d'isolement maximum par défaut.

Comme toujours, référez-vous à la documentation pour plus de détails : MySQL, PostgreSQL, Oracle.

5.2.4. Gestionnaire de journaux

Comme nous le savons déjà, afin d'augmenter les performances, la base de données stocke une partie des données dans la mémoire tampon. Mais si le serveur plante lors d'une validation de transaction, les données en mémoire seront perdues. Et cela viole le principe de fiabilité des transactions.

Bien sûr, vous pouvez tout écrire sur le disque, mais en cas de panne, vous vous retrouverez avec des données non écrites, ce qui constitue une violation du principe d'atomicité.

Toutes les modifications écrites par la transaction doivent être annulées ou complétées.

Cela se fait de deux manières :

• Clichés instantanés/pages. Chaque transaction crée sa propre copie de la base de données (ou une partie de celle-ci) et fonctionne avec cette copie. Si une erreur se produit, la copie est supprimée. Si tout s'est bien passé, la base de données passe instantanément aux données de la copie en utilisant une astuce au niveau du système de fichiers, puis supprime les « anciennes » données.
• Journal des transactions. Il s'agit d'une installation de stockage spéciale. Avant chaque écriture sur le disque, la base de données écrit des informations dans le journal des transactions. Ainsi en cas d'échec, la base de données saura supprimer ou finaliser la transaction en attente.

WAL

Dans les grandes bases de données comportant de nombreuses transactions, les clichés instantanés/pages occupent une quantité incroyablement importante d’espace disque. Par conséquent, les bases de données modernes utilisent un journal des transactions. Il doit être placé dans un stockage de sécurité.

La plupart des produits (notamment Oracle, SQL Server, DB2, PostgreSQL, MySQL et SQLite) fonctionnent avec les journaux de transactions via le protocole WAL (Write-Ahead Logging). Ce protocole contient trois règles :

1. Chaque modification de la base de données doit être accompagnée d'une entrée de journal et doit être effectuée AVANT que les données ne soient écrites sur le disque.
2. Les entrées dans le journal doivent être organisées conformément à l'ordre des événements pertinents.
3. Lorsqu'une transaction est validée, un enregistrement de celle-ci doit être écrit dans le journal AVANT que la transaction ne soit terminée avec succès.

Le gestionnaire de journaux surveille la mise en œuvre de ces règles. Il se situe logiquement entre le gestionnaire de cache et le gestionnaire d'accès aux données. Le gestionnaire de journaux enregistre chaque opération effectuée par les transactions jusqu'à ce qu'elle soit écrite sur le disque. Cela semble vrai ?

FAUX! Après tout ce que nous avons vécu dans cet article, il est temps de rappeler que tout ce qui touche à la base de données est soumis à la malédiction de « l’effet base de données ». Sérieusement, le problème est que vous devez trouver un moyen d'écrire dans le journal tout en conservant de bonnes performances. Après tout, si le journal des transactions est lent, il ralentit tous les autres processus.

BÉLIER

En 1992, des chercheurs d'IBM ont créé une version étendue de WAL, qu'ils ont appelée ARIES. Sous une forme ou une autre, ARIES est utilisé par la plupart des bases de données modernes. Si vous souhaitez approfondir ce protocole, vous pouvez étudier l’ouvrage correspondant.

Ainsi, ARIES signifie UN algorithmes pour R. l'économie et je sollicitation E exploiter S mantiques. Cette technologie a deux tâches :

1. Fournit de bonnes performances lors de l’écriture des journaux.
2. Garantissez une récupération rapide et fiable.

Il existe plusieurs raisons pour lesquelles une base de données doit annuler une transaction :

1. L'utilisateur l'a annulé.
2. Erreur de serveur ou de réseau.
3. La transaction a violé l'intégrité de la base de données. Par exemple, vous avez appliqué la condition UNIQUE à une colonne et la transaction a ajouté un doublon.
4. Présence de blocages.

Mais parfois, la base de données peut également restaurer une transaction, par exemple en cas d'erreur réseau.

Comment est-ce possible? Pour répondre à cette question, vous devez d'abord comprendre quelles informations sont stockées dans le journal.

Journaux
Chaque opération (ajout/suppression/modification) lors de l'exécution d'une transaction entraîne l'apparition d'une entrée dans le log. L'entrée contient :

• LSN (numéro de séquence du journal). Il s'agit d'un numéro unique dont la signification est déterminée par ordre chronologique. Autrement dit, si l'opération A s'est produite avant l'opération B, le LSN de A sera inférieur au LSN de B. En réalité, la méthode de génération d'un LSN est plus compliquée, car elle est également liée à la manière dont le journal est stocké.
• TransID. ID de la transaction qui a effectué l'opération.
• ID de page. Emplacement sur le disque où se trouvent les données modifiées.
• PrécédentLSN. Un lien vers une entrée de journal précédente créée par la même transaction.
• ANNULER. Une méthode pour annuler une opération.

  Par exemple, si une opération de mise à jour a été effectuée, alors la valeur/état précédent de l'élément modifié (UNDO physique) ou l'opération inverse qui vous permet de revenir à l'état précédent (UNDO logique) est écrite dans UNDO. BÉLIER n'utilise que des logiques ; travailler avec des logiques physiques est très difficile.

• REFAIRE. Méthode de répétition de l'opération.

De plus, chaque page du disque (pour stocker les données, pas les journaux) contient un LSN dernière opération, données modifiées contenues dans le présent document.

Pour autant que nous le sachions, UNDO n'est pas utilisé uniquement dans PostgreSQL. Au lieu de cela, un garbage collector est utilisé pour nettoyer les anciennes versions des données. Cela est dû à la mise en œuvre du versionnage des données dans ce SGBD.

Pour vous permettre d'imaginer plus facilement la composition d'une entrée de journal, voici un exemple visuellement simplifié dans lequel la requête UPDATE FROM PERSON SET AGE = 18; est exécutée. Qu'il soit exécuté dans la transaction numéro 18 :

Chaque journal possède un LSN unique. Les journaux liés font référence à la même transaction et sont liés par ordre chronologique (le dernier journal de la liste fait référence à la dernière opération).

Tampon de journal
Pour éviter que la journalisation ne devienne un goulot d'étranglement du système, un tampon de journalisation est utilisé.

Lorsque l'exécuteur de requête demande des données modifiées :

1. Le gestionnaire de cache les stocke dans un tampon.
2. Le gestionnaire de journaux stocke le journal correspondant dans son propre tampon.
3. L'exécuteur de requêtes détermine si l'opération est terminée et, par conséquent, si les données modifiées peuvent être demandées.
4. Le gestionnaire de journaux enregistre information nécessaire au journal des transactions. Le moment de cette saisie est déterminé par l'algorithme.
5. Le gestionnaire de cache écrit les modifications sur le disque. Le moment de l'enregistrement est également défini par l'algorithme.

Lorsqu'une transaction est validée, cela signifie que toutes les étapes 1 à 5 sont terminées. L'écriture dans le journal des transactions est rapide car elle représente « l'ajout du journal quelque part dans le journal des transactions ». Dans le même temps, l'écriture de données sur le disque est une procédure plus complexe, étant donné que les données doivent ensuite être lues rapidement.

Politiques STEAL et FORCE

Pour améliorer les performances, l'étape numéro 5 doit être effectuée après le commit, car en cas d'échec, il est toujours possible de restaurer la transaction en utilisant REDO. C’est ce qu’on appelle la politique SANS FORCE.

Mais la base de données peut également choisir la politique FORCE pour réduire la charge lors de la récupération. Ensuite, l'étape numéro 5 est exécutée avant le commit.

La base de données choisit également d'écrire les données sur le disque de manière incrémentielle (politique STEAL) ou, si le gestionnaire de tampon doit attendre une validation, de tout écrire en même temps (NO-STEAL). Le choix dépend de vos besoins : enregistrement rapide avec longue convalescence ou une récupération rapide ?

Comment les politiques mentionnées affectent le processus de récupération :

• STEAL/NO-FORCE nécessite UNDO et REDO. Les performances sont les plus élevées, mais la structure des journaux et des processus de récupération (comme ARES) est plus complexe. La plupart des bases de données utilisent cette combinaison de stratégies.
• Pour STEAL/FORCE, vous n'avez besoin que d'UNDO.
• Pour NO-STEAL/NO-FORCE - uniquement REDO.
• Pour NO-STEAL/FORCE, vous n’avez besoin de rien du tout. Dans ce cas, les performances sont les plus faibles et une énorme quantité de mémoire est requise.

Récupération

Alors, comment pouvons-nous utiliser nos incroyables journaux ? Supposons qu'un nouvel employé ait détruit la base de données (règle n°1 : c'est toujours la faute du débutant !). Vous le redémarrez et le processus de récupération commence.
ARIES restaure en trois étapes :

1. Analyse. L'intégralité du journal des transactions est lue afin de restituer la chronologie des événements survenus lors de la chute de la base de données. Cela permet de déterminer quelle transaction doit être annulée. Toutes les transactions sans ordre de validation sont annulées. Le système décide également quelles données auraient dû être écrites sur le disque lors de la panne.
2. Répéter. REDO est utilisé pour mettre à jour la base de données vers l'état avant le crash. Ses journaux sont traités par ordre chronologique. Pour chaque journal, le LSN de la page sur disque contenant les données à modifier est lu.

   Si LSN(pages_on_disk)>=LSN(entries_in_log), alors les données étaient déjà écrites sur le disque avant l'échec. Mais la valeur a été écrasée par une opération effectuée après l'écriture dans le journal et avant l'échec. Donc rien n’a vraiment été fait.

   Si LSN(pages_on_disk)

   La relecture est effectuée même pour les transactions qui seront annulées car elle simplifie le processus de récupération. Mais les bases de données modernes ne le font probablement pas.

3. Annuler. A ce stade, toutes les transactions qui n'étaient pas terminées au moment de l'échec sont annulées. Le processus commence par les derniers journaux de chaque transaction et traite les annulations dans l'ordre chronologique inverse à l'aide de PrevLSN.

Pendant le processus de récupération, le journal des transactions doit connaître les actions effectuées pendant la récupération. Ceci est nécessaire pour synchroniser les données enregistrées sur le disque avec celles enregistrées dans le journal des transactions. Il est possible de supprimer les enregistrements de transactions annulés, mais cela est très difficile à faire. Au lieu de cela, ARIES crée des entrées compensatoires dans le journal des transactions qui invalident logiquement les entrées des transactions annulées.

Si la transaction est annulée « manuellement », ou par un gestionnaire de verrous, ou en raison d'une panne de réseau, alors l'étape d'analyse n'est pas nécessaire. Après tout, les informations sur REDO et UNDO sont contenues dans deux tables situées en mémoire :

• Dans la table des transactions (les états de toutes les transactions en cours sont stockés ici).
• Le tableau des pages sales (celui-ci contient des informations sur les données qui doivent être écrites sur le disque).

Dès qu'une nouvelle transaction apparaît, ces tables sont mises à jour par le gestionnaire de cache et le gestionnaire de transactions. Et comme les tables sont stockées en mémoire, si la base de données plante, elles disparaissent.

L'étape d'analyse est nécessaire uniquement pour restaurer les deux tables à l'aide des informations du journal des transactions. Pour accélérer cette phase, ARIES utilise des points de contrôle. Le contenu des deux tables, ainsi que le dernier LSN au moment de la rédaction, sont écrits sur le disque de temps en temps. Ainsi lors de la restauration, seuls les logs suivant ce LSN sont analysés.

6. Conclusion

Comme lecture supplémentaire sur les bases de données, nous pouvons recommander l’article Architecture d’un système de base de données. Il s'agit d'une bonne introduction au sujet, rédigée dans un langage assez clair.

Si vous lisez attentivement tout ce qui précède, vous avez probablement une idée de l'ampleur des capacités des bases de données. Cependant, cet article n’aborde pas d’autres questions importantes :

• Comment gérer des bases de données en cluster et des transactions globales.
• Comment obtenir un instantané si la base de données fonctionne.
• Comment stocker et compresser efficacement les données.
• Comment gérer la mémoire.

Réfléchissez donc à deux fois avant de choisir entre un NoSQL bogué et une base de données relationnelle solide. Ne vous méprenez pas, certaines bases de données NoSQL sont très bonnes. Mais ils sont encore loin d’être parfaits et ne peuvent que contribuer à résoudre des problèmes spécifiques liés à certaines applications.

Ainsi, si quelqu’un vous demande comment fonctionnent les bases de données, au lieu d’abandonner et de vous en aller, vous pouvez répondre :

Balises : Ajouter des balises

BASE DE DONNÉES RELATIONNELLES ET SES CARACTÉRISTIQUES. TYPES DE RELATIONS ENTRE TABLES RELATIONNELLES

Base de données relationnelle est une collection de tables interconnectées, chacune contenant des informations sur des objets d'un certain type. Une ligne du tableau contient des données sur un objet (par exemple, un produit, un client), et les colonnes du tableau décrivent diverses caractéristiques de ces objets - attributs (par exemple, nom, code produit, informations client). Les enregistrements, c'est-à-dire les lignes du tableau, ont la même structure : ils sont constitués de champs qui stockent les attributs des objets. Chaque champ, c'est-à-dire colonne, ne décrit qu'une seule caractéristique de l'objet et possède un type de données strictement défini. Tous les enregistrements ont les mêmes champs, sauf qu'ils affichent différentes propriétés d'information de l'objet.

Dans une base de données relationnelle, chaque table doit avoir une clé primaire : un champ ou une combinaison de champs qui identifie de manière unique chaque ligne de la table. Si une clé est composée de plusieurs champs, elle est dite composite. La clé doit être unique et identifier de manière unique l'entrée. En utilisant la valeur clé, vous pouvez trouver un seul enregistrement. Les clés servent également à organiser les informations dans la base de données.

Les tables de bases de données relationnelles doivent répondre aux exigences de normalisation des relations. La normalisation des relations est un appareil formel de restrictions sur la formation de tables, qui élimine la duplication, garantit la cohérence des données stockées dans la base de données et réduit les coûts de main-d'œuvre pour la maintenance de la base de données.

Créez une table Étudiant contenant les champs suivants : numéro de groupe, nom complet, numéro de dossier de l'étudiant, date de naissance, nom de la spécialité, nom de la faculté. Une telle organisation du stockage des informations présentera un certain nombre d'inconvénients :

• duplication d'informations (le nom de la spécialité et de la faculté est répété pour chaque étudiant), par conséquent, le volume de la base de données augmentera ;
• la procédure de mise à jour des informations dans le tableau est compliquée en raison de la nécessité de modifier chacun entrées de tableau.

La normalisation des tables est conçue pour remédier à ces lacunes. Disponible trois formes normales de relations.

Première forme normale. Une table relationnelle est réduite à la première forme normale si et seulement si aucune de ses lignes ne contient plus d'une valeur dans aucun de ses champs et qu'aucun de ses champs clés n'est vide. Ainsi, si vous devez obtenir des informations de la table Étudiant par le nom de l'étudiant, le champ Nom complet doit être divisé en parties Nom, Prénom et Patronyme.

Deuxième forme normale. Une table relationnelle est définie sous la deuxième forme normale si elle satisfait aux exigences de la première forme normale et si tous ses champs qui ne sont pas inclus dans la clé primaire ont une dépendance fonctionnelle totale à l'égard de la clé primaire. Pour réduire un tableau à la seconde forme normale, il est nécessaire de déterminer la dépendance fonctionnelle des champs. Une dépendance fonctionnelle de champs est une dépendance dans laquelle, dans une instance d'un objet d'information, une certaine valeur d'un attribut clé correspond à une seule valeur d'un attribut descriptif.

Troisième forme normale. Une table est sous la troisième forme normale si elle satisfait aux exigences de la deuxième forme normale selon laquelle aucun de ses champs non clés ne dépend fonctionnellement d'un autre champ non clé. Par exemple, dans la table Étudiant (N° de groupe, Nom complet, N° du carnet de notes, Date de naissance, Responsable), trois champs - N° du carnet de notes, N° de groupe, Directeur sont en dépendance transitive. Le numéro de groupe dépend du numéro du carnet de notes et le chef dépend du numéro de groupe. Pour éliminer la dépendance transitive, il est nécessaire de transférer certains champs de la table Student vers une autre table Groupe. Les tableaux prendront la forme suivante : Etudiant (numéro de groupe, nom complet, numéro de carnet de notes, date de naissance), Groupe (numéro de groupe, Chef).

Les opérations suivantes sont possibles sur les tables relationnelles :

• Fusionnez des tables avec la même structure. Le résultat est une table commune : d'abord la première, puis la seconde (concaténation).
• Intersection de tables de même structure. Résultat - les enregistrements qui se trouvent dans les deux tables sont sélectionnés.
• Soustraire des tables avec la même structure. Résultat - les enregistrements sont sélectionnés qui ne sont pas dans celui soustrait.
• Échantillon (sous-ensemble horizontal). Résultat - les enregistrements qui remplissent certaines conditions sont sélectionnés.
• Projection (sous-ensemble vertical). Le résultat est une relation contenant certains des champs des tables source.
• Produit cartésien de deux tables Les enregistrements de la table résultante sont obtenus en combinant chaque enregistrement de la première table avec chaque enregistrement de l'autre table.

Les tables relationnelles peuvent être liées les unes aux autres, les données peuvent donc être récupérées simultanément à partir de plusieurs tables. Les tables sont liées les unes aux autres afin de réduire à terme la taille de la base de données. Chaque paire de tables est connectée si elles ont des colonnes identiques.

Il existe les types de liens d'information suivants :

• Un par un;
• un à plusieurs ;
• plusieurs à plusieurs.

Communication individuelle suppose qu'un attribut de la première table correspond à un seul attribut de la deuxième table et vice versa.

Communication un-à-plusieurs suppose qu'un attribut de la première table correspond à plusieurs attributs de la deuxième table.

Communication plusieurs-à-plusieurs suppose qu'un attribut de la première table correspond à plusieurs attributs de la deuxième table et vice versa.

Systèmes de gestion de bases de données et systèmes experts. Concepts de base des bases de données relationnelles. Travailler avec des demandes. Formes. Rapports. Création de base de données.

Systèmes de gestion de bases de données et leurs fonctions

Dans la technologie moderne des bases de données, des logiciels spécialisés - les systèmes de gestion de bases de données - sont utilisés pour créer, prendre en charge et maintenir des bases de données. Un SGBD est un ensemble de logiciels et d'outils linguistiques nécessaires à la création et à l'exploitation de bases de données.

Au stade du développement de la base de données, le SGBD permet de décrire la structure de la base de données : définition des tables ; déterminer le nombre de champs ; le type de données qui y sont affichées ; tailles de champ ; définir les relations entre les tables. En plus des tableaux, la plupart des SGBD prévoient la création d'outils spéciaux pour travailler avec des données - formulaires, requêtes.

Lors du fonctionnement des bases de données, le SGBD permet d'éditer la structure de la base de données, de la remplir de données, de rechercher, de trier, de sélectionner les données selon des critères spécifiés et de générer des rapports.

Dans les systèmes d'information qui fonctionnent sur des ordinateurs personnels compatibles IBM, les systèmes de gestion de bases de données dits de type dBASE, par exemple dBASE, FoxPro et Clipper, se sont répandus. Ce qui est important pour les utilisateurs, c'est que, bien qu'ils diffèrent par les langages de commande et les formats de fichiers d'index, tous ces SGBD utilisent les mêmes fichiers de base de données avec l'extension .DBF, dont le format est devenu depuis un certain temps une sorte de standard de base de données.

Dans les bases de données de type dBASE, une approche relationnelle de l'organisation des données est en fait utilisée, c'est-à-dire Chaque fichier .DBF est un tableau bidimensionnel composé d'un nombre fixe de colonnes et d'un nombre variable de lignes (enregistrements). En termes acceptés dans la documentation technique, chaque colonne correspond à un champ de l'un des cinq types (N - numérique, C - symbolique, D - date, L - logique, M - note), et chaque ligne correspond à un enregistrement de longueur fixe. composé d'un nombre fixe de champs. À l'aide des langages de commande de ces SGBD, des présentations de fichiers .DBF (descriptions de tables) sont créées et corrigées, des fichiers d'index sont créés, des procédures de travail avec des bases de données sont décrites (lecture, recherche, modification des données, reporting et bien plus encore). Une caractéristique du fichier .DBF est sa simplicité et sa clarté : la représentation physique des données sur disque correspond exactement à la présentation du tableau sur papier. Cependant, en général, les systèmes basés sur des fichiers .DBF doivent être considérés comme obsolètes.

D'autres SGBD (avec un format de fichier différent) sont également très populaires - Paradox, Clarion, etc. Il convient de souligner que les systèmes répertoriés trouvent leurs origines dans MS-DOS, mais maintenant presque tous ont été améliorés et disposent de versions pour Windows.

Parmi les systèmes relationnels modernes, le SGBD le plus populaire pour Windows est Access de Microsoft, Approach de Lotus et Paradox de Borland. Beaucoup de ces systèmes prennent en charge la technologie OLE et peuvent manipuler non seulement des informations numériques et textuelles, mais également des images graphiques (dessins, photographies) et même des fragments sonores et des clips vidéo.

Les SGBD répertoriés sont souvent appelés ordinateurs de bureau, ce qui signifie la quantité relativement faible de données servies par ces systèmes. Cependant, non seulement des utilisateurs individuels, mais aussi des équipes entières (en particulier dans les réseaux informatiques locaux) travaillent souvent avec eux.

Dans le même temps, des SGBD relationnels plus puissants avec accès dit SQL se déplacent progressivement vers le centre des technologies de l'information modernes. Ces SGBD sont basés sur la technologie client-serveur. Parmi les principaux fabricants de tels systèmes figurent Oracle, Centura (Gupta), Sybase, Informix, Microsoft et d'autres.

Types de données dans les bases de données

Les systèmes d’information fonctionnent avec les principaux types de données suivants.

Données texte. La valeur de chaque donnée texte (caractère) est représentée par un ensemble de caractères alphanumériques arbitraires, dont la longueur ne dépasse le plus souvent pas 255 (par exemple, 5, 10, 140). Les données textuelles représentent les noms et fonctions de personnes, les noms d'entreprises, de produits, d'appareils, etc. dans le SI. Dans un cas particulier, la valeur d'une donnée texte peut être le nom d'un fichier contenant des informations non structurées de longueur arbitraire (par exemple, une biographie ou une photographie d'un objet). En fait, il s'agit d'un lien structuré qui vous permet d'élargir considérablement le contenu informatif de votre tableau.

Données numériques. Les données de ce type sont généralement utilisées pour représenter des attributs dont les valeurs doivent être utilisées pour des opérations arithmétiques (poids, prix, coefficients, etc.). En règle générale, une donnée numérique a des caractéristiques supplémentaires, par exemple : un entier de 2 octets de long, un nombre à virgule flottante (4 octets) dans un format fixe, etc. Le séparateur entre les parties entières et fractionnaires est généralement un point.

Données de type date et/ou heure. Les données de type date sont spécifiées dans un format connu par la machine, par exemple JJ.MM.AA (jour, mois, année). À première vue, il s’agit d’un cas particulier de données textuelles. Cependant, l’utilisation d’un type de date spécial dans un IC présente les avantages suivants. Premièrement, le système a la possibilité de maintenir un contrôle strict (par exemple, la valeur du mois ne peut être discrète que dans la plage 01-12). Deuxièmement, il devient possible de représenter automatiquement le format de date en fonction des traditions d'un pays particulier (par exemple, le format MM-DD-GT est adopté aux USA). Troisièmement, lors de la programmation, les opérations arithmétiques avec les dates sont grandement simplifiées (essayez, par exemple, de calculer manuellement une date 57 jours après une date donnée). Utiliser ce type de temps présente les mêmes avantages.

Données logiques. Une donnée de ce type (parfois appelée booléenne) ne peut prendre qu'une des deux valeurs mutuellement exclusives - Vrai ou Faux (sous condition : 1 ou 0). Il s'agit en fait d'un interrupteur dont la valeur peut être interprétée comme "Oui" et "Non" ou comme "Vrai" et "Faux". Le type booléen est pratique à utiliser pour les attributs qui peuvent prendre l'une des deux valeurs mutuellement exclusives, par exemple avoir un permis de conduire (oui ou non), le service militaire (oui ou non), etc.

Champs d'objet OLE. La valeur de ces données peut être n'importe quel objet OLE disponible sur l'ordinateur (graphique, son, vidéo). En particulier, la liste des étudiants peut comprendre non seulement une photographie statique de l'étudiant, mais également sa voix.

Types personnalisés. Dans de nombreux systèmes, les utilisateurs ont la possibilité de créer leurs propres types de données, par exemple : « Jour de la semaine » (lundi, mardi, etc.), « Adresse » (code postal - ville - ...), etc.

Dans un cas particulier, la valeur d'une donnée texte peut être une collection d'espaces et la valeur d'une donnée numérique peut être nulle. Si aucune information n'est saisie dans le tableau, la valeur sera vide (Null). Null ne doit pas être confondu avec zéro ou des espaces. Dans de nombreux systèmes, il est important que l'utilisateur enregistre l'absence de données pour certaines instances d'un objet (par exemple, l'absence d'adresse, « L'adresse est nulle »). Si vous entrez accidentellement un espace dans une telle ligne du tableau, le système considérera que l'adresse a été spécifiée et cette instance ne sera pas incluse dans la liste des objets avec des adresses manquantes.

Bases de données relationnelles

Le moyen le plus pratique pour l'utilisateur et l'ordinateur est de présenter les données sous la forme d'un tableau à deux dimensions - la plupart des systèmes d'information modernes fonctionnent uniquement avec de tels tableaux. Les bases de données constituées de tables bidimensionnelles sont appelées relationnel , (en anglais « relation » - relation). L'idée principale de l'approche relationnelle est de représenter une structure de données arbitraire sous la forme d'un simple tableau bidimensionnel.

Un exemple de mise en œuvre d'un modèle de données relationnel pourrait être un tableau contenant des informations sur les étudiants.

Comme le montre l'exemple ci-dessus, une table relationnelle a les propriétés suivantes :

· chaque ligne du tableau correspond à un élément de données (informations sur un élève) ;

· toutes les colonnes du tableau sont homogènes, c'est-à-dire Tous les éléments d'une colonne sont du même type et de la même longueur (par exemple, la colonne Nom affiche les noms d'étudiants de type caractère comportant jusqu'à 17 caractères) ;

· chaque colonne a un nom unique (par exemple, il n'y a pas deux colonnes Nom dans la table) ;

· les lignes identiques dans le tableau ne sont pas autorisées (une entrée pour chaque élève n'est effectuée qu'une seule fois) ;

· l'ordre des lignes et des colonnes du tableau peut être arbitraire (une inscription concernant l'élève dans le tableau est effectuée lors de l'admission à l'école, et l'ordre des colonnes n'a pas d'importance).

Éléments structurels d'une base de données relationnelle

En utilisant une table relationnelle comme exemple, examinons les principaux éléments structurels d'une base de données.

1. Dans les bases de données relationnelles, toute collection de données est présentée sous forme de tableaux bidimensionnels (relations), similaires à la liste d'étudiants décrite ci-dessus. De plus, chaque tableau se compose d'un nombre fixe de colonnes et d'un certain nombre (variable) de lignes. La description des colonnes est généralement appelée disposition du tableau.

2. Chaque colonne du tableau représente un champ - une unité élémentaire d'organisation logique des données, qui correspond à une unité d'information indivisible - les détails d'un objet de données (par exemple, le nom de famille, l'adresse de l'étudiant).

Les caractéristiques suivantes sont utilisées pour décrire le domaine :

· nom du champ (par exemple, numéro de dossier personnel, nom de famille) ;

· type de champ (par exemple, caractère, date) ;

· caractéristiques supplémentaires (longueur du champ, format, précision).

Par exemple, le champ Date de naissance peut être de type « date » et de longueur 8 (6 chiffres et 2 points séparant le jour, le mois et l’année dans l’enregistrement de date).

3. Chaque ligne d'un tableau est appelée un enregistrement. L'enregistrement combine logiquement tous les champs qui décrivent un objet de données, par exemple, tous les champs de la première ligne du tableau ci-dessus décrivent des données sur l'étudiant Ivan Vasilyevich Petrov, né le 12 mars 1989, résidant à l'adresse : st. Gorki, 12-34 ans, étudiant en classe 4A, numéro de dossier personnel - P-69. Le système numérote les enregistrements dans l'ordre : 1,2, ..., n, où n est le nombre total d'enregistrements (lignes) dans le tableau à l'heure actuelle. Contrairement au nombre de champs (colonnes) dans une table, le nombre d'enregistrements lors du fonctionnement de la base de données peut varier à volonté (de zéro à des millions). Le nombre de champs, leurs noms et types peuvent également être modifiés, mais il s'agit d'une opération spéciale appelée changer la disposition de la table .

4. La structure d'enregistrement du fichier spécifie des champs dont les valeurs sont une simple clé qui identifie l'instance d'enregistrement. Un exemple d'une clé aussi simple dans la table Étudiants est le champ du numéro de dossier personnel, dont la valeur identifie de manière unique un objet de la table - un étudiant, puisqu'il n'y a pas deux étudiants dans la table avec le même numéro de dossier personnel.

5. Chaque champ peut être inclus dans plusieurs tables (par exemple, un champ Nom de famille peut être inclus dans le tableau Liste des élèves de la troupe de théâtre).

II. Modèle de réseau

III. Modèle relationnel

enregistrerchamp

hiérarchique Et modèles de réseau clés étrangères

4. Modèle de données relationnel

Base de données relationnelle

* Attitude

* Attribut colonne (champ) les tables.

* Type de données

* Connexion clé.

* Une association

Principal les fonctions Les SGBDR sont :

· Définition des données

· Traitement de l'information

· Gestion de données

Microsoft Access

Fenêtre de base de données dans Access

Modes de travail avec des objets

Les boutons permettant de travailler avec les objets de la base de données se trouvent dans la barre d'outils de la fenêtre de la base de données :

Ouvrir– permet de passer en mode édition d'un tableau, exécution d'une requête, chargement d'un formulaire, construction d'un rapport, exécution d'une macro.

Constructeur– permet de passer au mode de configuration de l'objet sélectionné.

Créer– vous permet de commencer à créer un nouvel objet du type sélectionné.

7. Travailler avec des tableaux

Pour créer un tableau, vous devez vous rendre dans la liste des tableaux et cliquer sur le bouton Créer . Une nouvelle boîte de dialogue apparaîtra Nouveau tableau:

Vous pouvez créer un tableau dans Access de plusieurs manières :

· construire une nouvelle table « à partir de zéro » en utilisant Designer;

· lancement Assistant de table– un programme spécial qui propose de créer un tableau pas à pas à partir des solutions standards disponibles dans Access ;

· importer une table de base de données à partir d'un fichier programme, par exemple FoxPro ou Excel.

Spécification d'un nom de champ

Le nom du champ est précisé dans la colonne Nom de domaine. Le nom peut contenir un maximum de 64 caractères, tous les caractères étant autorisés à l'exception des points, des points d'exclamation et des crochets angulaires. La répétition des noms de champs n'est pas autorisée.

Définition du type de données

Pour chaque champ, vous devez préciser le type de données qu'il contient. Le type de données est sélectionné dans une liste accessible en cliquant sur la colonne Type de données. Access fonctionne sur les types de données suivants :

Ø Texte– pour stocker du texte brut avec un nombre maximum de caractères de 255.

Ø Champ MÉMO– pour stocker de grandes quantités de texte jusqu'à 65 535 caractères.

Ø Numérique– pour stocker des nombres réels.

Ø Date Heure– pour stocker les dates du calendrier et l’heure actuelle.

Ø Monétaire– ces champs contiennent des montants monétaires.

Ø Comptoir– pour déterminer la clé système unique de la table. Généralement utilisé pour la numérotation séquentielle des enregistrements. Lorsqu'un nouvel enregistrement est ajouté à la table, la valeur de ce champ augmente de 1 (un). Les valeurs de ces champs ne sont pas mises à jour.

Ø Logique – pour stocker des données qui prennent des valeurs : Oui ou Non.

Ø Champ d'objet OLE– pour stocker des objets créés dans d’autres applications.

Description des propriétés du champ

Comme déjà indiqué, les caractéristiques des champs individuels sont définies dans la zone des propriétés du champ (onglet Sont communs). Chaque champ possède un ensemble spécifique de propriétés, en fonction du type de champ. Certains types de champs ont des ensembles similaires de propriétés de champ. Les principales propriétés des champs sont listées ci-dessous.

Ø Taille du champ– longueur maximale d'un champ texte (50 caractères par défaut) ou type de données d'un champ numérique. Il est recommandé de définir cette propriété sur la valeur minimale possible, car les données de plus petite taille sont traitées plus rapidement.

Si le type de données est numérique, les valeurs de propriété suivantes sont autorisées : Taille du champ:

Commentaire. Si le champ est converti en une taille plus petite, une perte de données peut se produire.

Ø Format du champ– format d'affichage des données sur écran ou impression. Généralement, le format par défaut est utilisé.

Ø Nombre de décimales– définit le nombre de décimales après la virgule pour les types de données numériques et monétaires.

Ø Masque de saisie– définit la forme sous laquelle les données sont saisies dans le champ (outil d'automatisation de la saisie des données).

Ø Signature– la désignation du champ qui sera utilisé pour afficher le champ dans un tableau, un formulaire ou un rapport. Si cette valeur n'est pas définie, le nom du champ sera pris comme signature.

Ø Valeur par défaut– valeur standard qui est automatiquement saisie dans le champ lors de la création d'un nouvel enregistrement de données.

Ø Condition sur la valeur– définit des restrictions sur les valeurs saisies, permettant ainsi de contrôler l'exactitude de la saisie des données.

Ø Message d'erreur– définit le texte du message affiché à l'écran si la condition sur la valeur n'est pas respectée.

Ø Champ obligatoire– détermine si ce champ peut contenir des valeurs Null (c'est-à-dire rester vide), ou si des données doivent être saisies dans ce champ.

Ø Champ indexé– utilisé pour les opérations de recherche et le tri des enregistrements en fonction de la valeur stockée dans ce champ, ainsi que pour éliminer automatiquement les enregistrements en double. Champs de type NOTE, Objet OLE Et Lien hypertexte ne peut pas être indexé.

Définition du champ clé

Après avoir précisé les caractéristiques de tous les champs, vous devez sélectionner au moins un champ clé. En règle générale, les champs contenant des données non répétitives sont spécifiés comme champs clés ou des champs avec le type de données sont créés Comptoir . Dans tous les cas, le champ clé ne doit pas contenir de données en double. Pour définir une clé, vous devez sélectionner le ou les champs souhaités et cliquer sur le bouton Champ clé Modifier . Une image d'une clé apparaîtra à gauche du marqueur.

Enregistrer une table

Avant de saisir des informations, le tableau conçu doit être sauvegardé : appuyer sur le bouton Sauvegarder sur la barre d'outils ou la commande correspondante en p.m. Déposer et saisissez le nom de la table, après quoi la question « Créer un champ clé maintenant ? » apparaît à l'écran. (Oui ou non)

Si la réponse est « Oui", alors Access créera automatiquement un champ avec le nom "Code" et le type de données Comptoir , Si " Non", alors la table sera créée sans champ clé. Dans ce cas, vous devez ouvrir la table créée en mode Designer et définissez manuellement le champ clé.

Entrée de données

Pour passer le tableau en mode saisie d'informations, vous devez passer en mode les tables. Les champs sont remplis séquentiellement. Il est pratique de passer d'un champ à un autre à l'aide de la touche Languette(ou une combinaison Maj+Tabulation- dans la direction opposée). Si le tableau a été conçu avec des valeurs par défaut pour certains champs, ces valeurs apparaîtront automatiquement dans les champs correspondants. Les enregistrements d'un tableau peuvent être déplacés, copiés et supprimés de la même manière que dans les feuilles de calcul, c'est-à-dire qu'il faut d'abord sélectionner les lignes, puis effectuer l'opération nécessaire. Une colonne peut être sélectionnée en cliquant sur l'en-tête. Les colonnes peuvent être déplacées vers la gauche et la droite en utilisant la méthode glisser déposer(glisser déposer).

Si nécessaire, vous pouvez revenir au mode Designer. Cela permet de corriger n'importe quoi dans la structure du tableau.

Trier des données dans un tableau

Les données du tableau peuvent être triées par ordre croissant ou décroissant. Pour ce faire, vous devez placer le curseur de la souris dans n'importe quelle cellule de la colonne dont les valeurs seront triées de p.m. Des postes sélectionner une équipe Tri ou cliquez sur le bouton correspondant sur le panneau.

8. Création de connexions entre les tables de base de données

La relation entre les tables est établie en définissant dans une table ( subalterne) champ correspondant à la clé d'une autre table ( principal). La relation établie reliera les enregistrements contenant les mêmes valeurs dans le champ spécifié. Access utilisera ultérieurement les connexions que vous créez dans les requêtes, les formulaires ou les rapports.

Remarques.

Ø Les deux champs à lier doivent avoir le même Type de données.

Ø Propriétés Taille du champ pour les deux champs liés type numérique devrait être le même.

Ø Si le champ clé de la table principale est un champ avec un type de données Comptoir, alors ce champ peut être associé à un champ numérique dans la sous-table. Dans ce cas, pour le champ numérique de la table liée pour la propriété Taille du champ la valeur doit être spécifiée Entier long .

Intégrité des données

Intégrité des données est un ensemble de règles qui maintiennent l'exactitude des relations entre les enregistrements dans les tables liées et protègent les données contre les modifications ou suppressions accidentelles.

Ces règles comprennent :

Ø Vous ne pouvez pas saisir d'enregistrements dans une sous-table qui ne sont pas liés à un enregistrement de la table principale.

Ø Dans la table principale, vous ne pouvez pas modifier la valeur d'un champ clé s'il y a des enregistrements dans la sous-table qui lui sont associés.

Ø Vous ne pouvez pas supprimer des enregistrements dans une table principale s'il y a des enregistrements qui lui sont associés dans une sous-table.

Opérations en cascade

L'intégrité des données dans les tables associées est assurée par deux types d'opérations en cascade :

Ø opérations de mise à jour en cascade ;

Ø opérations de suppression en cascade.

Ces opérations peuvent être activées ou désactivées en cochant les cases appropriées : « Mise à jour en cascade des champs associés » et « Suppression en cascade des champs associés ».

Si la case « Mise à jour en cascade des champs associés » est cochée, alors toute modification de la valeur d'un champ clé dans la table principale qui se trouve du côté « un » d'une relation 1:M met automatiquement à jour les valeurs correspondantes dans tous les domaines. dossiers connexes.

Cocher la case « Suppression en cascade des tables associées » lors de la suppression d'un enregistrement de la table principale garantit que les enregistrements associés dans les tables subordonnées sont automatiquement supprimés.

Supprimer (modifier) ​​des connexions

ØOuvrir la fenêtre Schéma de données;

Ø activer avec le bouton gauche de la souris la connexion qui doit être supprimée (modifiée) ;

Ø faites un clic droit pour ouvrir un menu contextuel et sélectionnez une commande Supprimer (Changement) respectivement.

9. Types de relations entre les tables

Il existe trois types de relations entre les tables :

Un à un (1:1). Une valeur clé dans chaque enregistrement de la table principale ne peut avoir une valeur correspondante dans le champ associé que dans un seul enregistrement de la table enfant. Dans ce cas, la relation entre les tables ne peut être établie qu'à travers les champs clés des deux tables.

Un-à-plusieurs (1:M). La valeur d'une clé dans chaque enregistrement de la table principale peut avoir des valeurs correspondantes dans le(s) champ(s) associé(s) dans plusieurs enregistrements de la sous-table. Ce type de relation est assez souvent utilisé dans les bases de données relationnelles.

Plusieurs à plusieurs (M:M). Se produit entre deux tables lorsqu'un enregistrement de la première table A (relation de sortie) peut être associé à plusieurs enregistrements d'une autre table B (relation de réception), à son tour, un enregistrement d'une autre table peut être associé à plusieurs enregistrements dans le premier tableau. Ce schéma est mis en œuvre uniquement à l'aide d'une troisième table de jointure dont la clé de lien est constituée d'au moins deux champs. Ces champs sont les champs de clé étrangère des tables A et B. La clé primaire d'une table de jointure est généralement une combinaison de clés étrangères.

S'il existe des relations de type M:M entre les tables, une table d'intersection supplémentaire est créée, à l'aide de laquelle la relation M:M sera réduite à deux relations de type 1:M. Access ne vous permet pas de définir une relation M:M directe entre deux tables.

10. Formation des demandes

Exécuter une requête

Pour lancer une requête d'exécution depuis une fenêtre Designer Vous devez cliquer sur le bouton de la barre d'outils Lancement» ! ou exécutez la commande Demander/Exécuter. Les résultats de la sélection des données de requête sont affichés à l'écran en mode tableau.

Formation des conditions de sélection

La liste des opérateurs utilisés lors de la spécification d'expressions est la suivante :

Ø opérateurs comparaisons:

= (équivaut à)

<> (inégal)

> (plus)

>= (pas moins)

< (moins)

<= (pas plus)

ENTRE– vous permet de définir une plage de valeurs. Syntaxe: Entre"Expression" Et"Expression" (par exemple : ENTRE 10 Et 20 signifie la même chose qu'une expression booléenne >= 10 ET<= 20).

DANS– permet de préciser une liste de valeurs utilisées pour la comparaison (l'opérande est une liste entre parenthèses). Par exemple: DANS(« Brest », « Minsk », « Grodno ») signifie la même chose que l'expression logique « Brest » OU"Minsk" OU"Grodno".

Ø casse-tête les opérateurs:

ET(par exemple : >=10 ET<=20)

OU(Par exemple:<50 OR >100)

PAS(par exemple : Is Not Null – un champ contenant une valeur).

Ø opérateur COMME– vérifie la conformité texte ou Champs mémo selon un modèle de caractère donné.

Table de caractères du modèle

Exemples d'utilisation de l'opérateur Comme:

COMME "C*"– les lignes commençant par le symbole C ;

COMME "[ A - Z ] #"– n'importe quel symbole de A à Z et un chiffre ;

COMME "[! 0 - 9 ABC] * # #"– les lignes commençant par tout caractère autre qu'un chiffre ou les lettres A, B, C et se terminant par 2 chiffres ;

Critères d'échantillonnage complexes

Souvent, vous devez sélectionner des enregistrements selon une condition définie pour plusieurs champs de table ou selon plusieurs conditions pour un champ. Dans ce cas, appliquez "Je-requêtes"(sélectionner les enregistrements uniquement si toutes les conditions sont remplies) et "Requêtes OU"(sélectionner les enregistrements lorsqu'au moins une des conditions est remplie).

Lors du réglage de " Requête OU» chaque condition de sélection doit être placée sur une ligne distincte Formulaire de demande.

Lors du réglage de " Je-requête» chaque condition de sélection doit être placée sur la même ligne, mais dans des champs différents Formulaire de demande.

Ces opérations peuvent être spécifiées explicitement à l'aide des opérateurs OU Et ET respectivement.

Fonctions Iif() et Format()

Fonction IIf(condition ; ifTrue ; ifFalse)– renvoie l'un des deux arguments en fonction du résultat de l'évaluation de l'expression.

Fonction Format(expression ; instruction de formatage)– renvoie une chaîne contenant une expression formatée selon les instructions de formatage.

Pour les expressions date/heure Vous pouvez utiliser les caractères suivants dans les instructions de formatage :

I. Modèle hiérarchique

II. Modèle de réseau

III. Modèle relationnel

Dans le modèle relationnel, les informations sont présentées sous forme de tableaux rectangulaires. Chaque table est composée de lignes et de colonnes et porte un nom unique dans la base de données. Tour à tour, chaque ligne ( enregistrer) d'un tel tableau contient des informations relatives à un seul objet spécifique, et chaque colonne ( champ) la table a un nom unique pour sa table.

Bases de données relationnelles (RDB), contrairement hiérarchique Et modèles de réseau, vous permettent d'organiser les connexions entre les tables à tout moment. A cet effet, le RDB implémente un mécanisme clés étrangères. Chaque table de base de données possède au moins un champ qui sert de lien vers une autre table. Dans la terminologie RDB, ces champs sont appelés champs de clé étrangère. À l'aide de clés étrangères, vous pouvez lier n'importe quelle table de base de données à n'importe quelle étape de l'utilisation de la base de données.

4. Modèle de données relationnel

Base de données relationnelle (RDB) est un ensemble de tables de relations bidimensionnelles logiquement interconnectées les plus simples, composées de nombreux champs et enregistrements reflétant un certain domaine.

Le modèle de données relationnelles a été proposé par E. Codd, célèbre spécialiste américain des bases de données. Les concepts de base de ce modèle ont été publiés pour la première fois en 1970. Mathématicien de formation, Codd a proposé d'utiliser les appareils de la théorie des ensembles (union, intersection, différence, produit cartésien) pour le traitement des données. Il a montré que toute représentation de données se réduit à un ensemble de tableaux bidimensionnels d'un type particulier, connu en mathématiques sous le nom de relation (en anglais - relation, d'où le nom - bases de données relationnelles).

L'une des idées principales de Codd était que les connexions entre les données devaient être établies conformément à leurs relations logiques internes. Le deuxième principe important proposé par Codd est que dans les systèmes relationnels, une seule commande peut traiter des fichiers de données entiers, alors qu'auparavant, un seul enregistrement pouvait être traité par une seule commande.

Concepts de base des bases de données relationnelles (RDB)

* Attitude– des informations sur des objets du même type, par exemple sur les clients, les commandes, les employés. Dans une base de données relationnelle, une relation est stockée sous forme de table.

* Attribut– une certaine information sur un objet – par exemple, l’adresse d’un client ou le salaire d’un employé. L'attribut est généralement stocké sous la forme colonne (champ) les tables.

* Type de données– un concept qui dans le modèle relationnel est tout à fait équivalent au concept correspondant dans les langages algorithmiques. L'ensemble des types de données pris en charge est déterminé par le SGBD et peut varier considérablement d'un système à l'autre.

* Connexion– la manière dont les informations d'un tableau sont liées aux informations d'un autre tableau. Les relations sont établies à l'aide de champs correspondants appelés clé.

* Une association– Le processus de jointure de tables ou de requêtes basées sur les valeurs correspondantes de certains attributs.

Règles (normalisation) pour construire une base de données relationnelle

Normalisation est un processus de réorganisation des données en éliminant les groupes répétitifs et autres contradictions afin de donner aux tableaux une forme permettant une édition cohérente et correcte des données. Le but final de la normalisation est d'obtenir une conception de base de données dans laquelle chaque fait apparaît à un seul endroit, c'est-à-dire la redondance des informations est exclue.

1. Chaque champ d'une table doit être unique.

2. Chaque table doit avoir un identifiant unique ( clé primaire), qui peut être constitué d'un ou plusieurs champs de table.

3. Pour chaque valeur de clé primaire, il doit y avoir une et une seule valeur pour chacune des colonnes de données, et cette valeur doit être liée à l'objet de la table (c'est-à-dire qu'il ne doit y avoir aucune donnée dans la table qui ne soit liée à l'objet de la table). objet défini par la clé primaire, mais également, les informations contenues dans le tableau doivent décrire entièrement l'objet).

4. Il devrait être possible de modifier la valeur de n'importe quel champ (non inclus dans la clé primaire), et cela ne devrait pas impliquer de modifier un autre champ (c'est-à-dire qu'il ne devrait y avoir aucun champ calculé).

5. Systèmes de gestion de bases de données (SGBD)

Les bases de données sont maintenues dans un environnement informatique par des logiciels - systèmes de gestion de bases de données, qui sont un ensemble de logiciels et d'outils linguistiques généraux ou spécialisés nécessaires à la création de bases de données sur support machine, à leur mise à jour et à l'organisation de l'accès à celles-ci par les différents utilisateurs de l'environnement informatique. conditions de la technologie de traitement des données adoptée.

SGBD - ce sont des programmes de contrôle qui assurent toutes les manipulations des bases de données : création d'une base de données, sa maintenance, son utilisation par de nombreux utilisateurs, etc., c'est-à-dire qu'ils mettent en œuvre un ensemble complexe de fonctions de gestion centralisée des bases de données et servent les intérêts des utilisateurs.

Un SGBD peut être considéré comme un shell logiciel situé entre la base de données et l'utilisateur. Il fournit un contrôle centralisé de la protection et de l'intégrité des données, de l'accès aux données, du traitement, des rapports basés sur une base de données et d'autres opérations et procédures.

Système de gestion de base de données relationnelle (SGBDR)

L'ensemble des outils de gestion du RDB s'appelle Système de gestion de base de données relationnelle , qui peut contenir des utilitaires, des applications, des services, des bibliothèques, des outils de création d'applications et d'autres composants. En étant liées via des champs clés communs, les informations d'un RDB peuvent être combinées à partir de plusieurs tables en un seul ensemble de résultats.

Principal les fonctions Les SGBDR sont :

· Définition des données– quelles informations seront stockées, définissez la structure de la base de données et leur type.

· Traitement de l'information– vous pouvez sélectionner n’importe quel champ, trier et filtrer les données. Vous pouvez combiner des données et résumer.

· Gestion de données– corriger et ajouter des données.

6. Caractéristiques générales du SGBD ACCESS

Microsoft Access est un SGBD relationnel fonctionnellement complet, qui fournit tous les outils nécessaires pour définir et traiter les données, ainsi que pour les gérer lorsque vous travaillez avec de grands volumes d'informations. Ses différentes versions font partie du progiciel MS Office et fonctionnent sous l'environnement Windows (3.11/95/98/2000/XP).

Fenêtre de base de données dans Access

Après avoir créé un nouveau fichier de base de données ou ouvert un fichier existant dans l'espace de travail de la fenêtre Access, la fenêtre de base de données apparaît :

Un modèle de données est un ensemble de structures de données et d'opérations pour leur traitement. À l'aide d'un modèle de données, vous pouvez représenter visuellement la structure des objets et les relations établies entre eux. La terminologie des modèles de données est caractérisée par les concepts d'« élément de données » et de « règles contraignantes ». Un élément de données décrit tout ensemble de données et les règles d'association définissent des algorithmes pour interconnecter les éléments de données. À ce jour, de nombreux modèles de données différents ont été développés, mais en pratique, trois principaux sont utilisés. Il existe des modèles de données hiérarchiques, réseau et relationnels. On parle ainsi de SGBD hiérarchiques, réseau et relationnels.

O Modèle de données hiérarchique. Les données organisées hiérarchiquement sont très courantes dans la vie quotidienne. Par exemple, la structure d’un établissement d’enseignement supérieur est une structure hiérarchique à plusieurs niveaux. Une base de données hiérarchique (arborescente) se compose d'un ensemble ordonné d'éléments. Dans ce modèle, les éléments initiaux donnent naissance à d’autres éléments, et ces éléments donnent à leur tour naissance à d’autres éléments. Chaque élément enfant n'a qu'un seul élément parent.

Les structures organisationnelles, les listes de matériel, les tables des matières des livres, les plans de projet et de nombreux autres ensembles de données peuvent être présentés sous une forme hiérarchique. L'intégrité des liens entre ancêtres et descendants est automatiquement maintenue. Règle de base : aucun enfant ne peut exister sans son parent.

Le principal inconvénient de ce modèle est la nécessité d’utiliser la hiérarchie qui constituait la base de la base de données lors de la conception. La nécessité d'une réorganisation constante des données (et souvent l'impossibilité de cette réorganisation) a conduit à la création d'un modèle plus général : le modèle de réseau.

O Modèle de données réseau. L'approche réseau de l'organisation des données est une extension de l'approche hiérarchique. Ce modèle diffère du modèle hiérarchique en ce que chaque élément généré peut avoir plus d'un élément générateur. ■

Étant donné qu'une base de données réseau peut représenter directement toutes sortes de relations inhérentes aux données de l'organisation correspondante, ces données peuvent être parcourues, explorées et interrogées de différentes manières, c'est-à-dire que le modèle de réseau n'est pas lié par une seule hiérarchie. Cependant, pour faire une requête à une base de données réseau, il est nécessaire d'approfondir sa structure (avoir le schéma de cette base de données à portée de main) et de développer un mécanisme de navigation dans la base de données, ce qui est un inconvénient important de ce modèle de base de données. .

O Modèle de données relationnel. L'idée de base d'un modèle de données relationnel est de représenter n'importe quel ensemble de données sous forme de tableau bidimensionnel. Dans sa forme la plus simple, un modèle relationnel décrit une seule table bidimensionnelle, mais le plus souvent, le modèle décrit la structure et les relations entre plusieurs tables différentes.

Modèle de données relationnel

Ainsi, la finalité du système d'information est de traiter donnéesà propos objets monde réel, en tenant compte Connexions entre les objets. Dans la théorie des bases de données, les données sont souvent appelées attributs, et objets - entités. L'objet, l'attribut et la connexion sont des concepts fondamentaux du S.I.

Un objet(ou essence) est quelque chose qui existe et distinguable, c'est-à-dire qu'un objet peut être appelé « quelque chose » pour lequel il existe un nom et un moyen de distinguer un objet similaire d'un autre. Par exemple, chaque école est un objet. Les objets sont aussi une personne, une classe à l'école, une entreprise, un alliage, un composé chimique, etc. Les objets peuvent être non seulement des objets matériels, mais aussi des concepts plus abstraits qui reflètent le monde réel. Par exemple, des événements, des régions, des œuvres d'art ; livres (non pas sous forme de produits imprimés, mais sous forme d'œuvres), représentations théâtrales, films ; normes juridiques, théories philosophiques, etc.

Attribut(ou donné)- il s'agit d'un certain indicateur qui caractérise un certain objet et prend une certaine valeur numérique, textuelle ou autre pour une instance spécifique de l'objet. Le système d'information fonctionne avec des ensembles d'objets conçus en relation avec un domaine donné, à l'aide de valeurs d'attribut(données) de certains objets. Par exemple, prenons les cours dans une école comme un ensemble d'objets. Le nombre d’élèves dans une classe est une donnée qui prend une valeur numérique (une classe en a 28, une autre en a 32). Le nom de la classe est un nom donné qui prend une valeur textuelle (l'une a 10A, l'autre 9B, etc.).

Le développement des bases de données relationnelles a commencé à la fin des années 60, lorsque sont apparus les premiers travaux traitant de : la possibilité d'utiliser des méthodes familières et naturelles de présentation des données - les modèles datalogiques tabulaires - lors de la conception de bases de données.

Le fondateur de la théorie des bases de données relationnelles est considéré comme un employé d'IBM, le Dr E. Codd, qui a publié un article le 6 juin 1970. Un modèle relationnel de données pour les banques de données à grande échelle(Modèle de données relationnelles pour les grandes banques de données collectives). Cet article a été le premier à utiliser le terme « modèle de données relationnel ». La théorie des bases de données relationnelles, développée dans les années 70 aux États-Unis par le Dr E. Codd, repose sur une base mathématique puissante qui décrit les règles permettant d'organiser efficacement les données. Le cadre théorique développé par E. Codd est devenu la base du développement de la théorie de la conception de bases de données.

E. Codd, étant mathématicien de formation, a proposé d'utiliser les appareils de la théorie des ensembles (union, intersection, différence, produit cartésien) pour le traitement des données. Il a prouvé que n'importe quel ensemble de données peut être représenté sous la forme de tableaux bidimensionnels d'un type particulier, appelés « relations » en mathématiques.

Relationnel Une base de données est considérée comme une base de données dans laquelle toutes les données sont présentées à l'utilisateur sous la forme de tableaux rectangulaires de valeurs de données, et toutes les opérations sur la base de données sont réduites à des manipulations avec les tables.

Le tableau se compose de colonnes (champs) Et lignes (enregistrements); porte un nom unique dans la base de données. Tableau reflète Type d'objet monde réel (entité), et chacune d'elle la chaîne est un objet spécifique. Chaque colonne du tableau est une collection de valeurs pour un attribut spécifique d'un objet. Les valeurs sont sélectionnées parmi l'ensemble de toutes les valeurs possibles pour un attribut d'objet, appelé domaine.

Dans sa forme la plus générale, un domaine est défini en spécifiant un type de données de base auquel appartiennent les éléments du domaine et une expression booléenne arbitraire appliquée aux éléments de données. Si vous évaluez une condition booléenne sur un élément de données et que le résultat est vrai, cet élément appartient au domaine. Dans le cas le plus simple, un domaine est défini comme un ensemble potentiel valide de valeurs du même type. Par exemple, la collection des dates de naissance de tous les employés constitue le « domaine de date de naissance » et les noms de tous les employés constituent le « domaine des noms d'employé ». Le domaine de date de naissance doit avoir un type de données ponctuel et le domaine de nom d'employé doit avoir un type de données caractère.

Si deux valeurs proviennent du même domaine, alors une comparaison peut être faite entre les deux valeurs. Par exemple, si deux valeurs sont extraites du domaine des dates de naissance, vous pouvez les comparer et déterminer quel employé est le plus âgé. Si les valeurs proviennent de domaines différents, leur comparaison n'est pas autorisée car, selon toute vraisemblance, cela n'a aucun sens. Par exemple, la comparaison du nom et de la date de naissance d'un employé n'aboutira à rien de précis.

Chaque colonne (champ) a un nom, qui est généralement écrit en haut du tableau. Lors de la conception de tables au sein d'un SGBD spécifique, il est possible de sélectionner pour chaque champ son taper, c'est-à-dire définir un ensemble de règles pour son affichage, ainsi que déterminer les opérations qui peuvent être effectuées sur les données stockées dans ce champ. Les ensembles de types peuvent varier selon les différents SGBD.

Le nom du champ doit être unique dans la table, mais différentes tables peuvent avoir des champs portant le même nom. Toute table doit avoir au moins un champ ; Les champs sont disposés dans le tableau selon l'ordre dans lequel leurs noms sont apparus lors de sa création. Contrairement aux champs, les chaînes n’ont pas de nom ; leur ordre dans le tableau n'est pas défini, et leur nombre est logiquement illimité.

Étant donné que les lignes du tableau ne sont pas ordonnées, il est impossible de sélectionner une ligne par sa position - il n'y a pas de « premier », « deuxième » ou « dernier » parmi elles. Toute table comporte une ou plusieurs colonnes dont les valeurs identifient de manière unique chacune de ses lignes. Une telle colonne (ou combinaison de colonnes) est appelée clé primaire. Un champ artificiel est souvent introduit pour numéroter les enregistrements dans un tableau. Un tel champ, par exemple, pourrait être son champ ordinal, qui peut garantir l'unicité de chaque enregistrement de la table. La clé doit avoir les propriétés suivantes.

Unicité.À un moment donné, deux tuples de relation différents n'ont pas la même valeur pour la combinaison d'attributs inclus dans la clé. Autrement dit, il ne peut pas y avoir deux lignes dans le tableau comportant le même numéro d’identification ou le même numéro de passeport.

Minimalisme. Aucun des attributs inclus dans la clé ne peut être exclu de la clé sans violer l'unicité. Cela signifie que vous ne devez pas créer une clé comprenant à la fois le numéro de passeport et le numéro d’identification. Il suffit d'utiliser n'importe lequel de ces attributs pour identifier de manière unique un tuple. Vous ne devez pas non plus inclure d’attribut non unique dans la clé, c’est-à-dire qu’il est interdit d’utiliser une combinaison d’un numéro d’identification et du nom d’un employé comme clé. En excluant le nom de l'employé de la clé, chaque ligne peut toujours être identifiée de manière unique.

Chaque relation a au moins une clé possible, puisque la totalité de tous ses attributs satisfait à la condition d'unicité - cela découle de la définition même de la relation.

L'une des clés possibles est sélectionnée au hasard dans comme clé primaire. Les clés possibles restantes, le cas échéant, sont considérées comme clés alternatives. Par exemple, si vous sélectionnez un numéro d'identification comme clé primaire, le numéro de passeport sera la clé alternative.

La relation entre les tables est l'élément le plus important du modèle de données relationnelles. Il est soutenu clés étrangères.

Lors de la description d'un modèle de base de données relationnelle, différents termes sont souvent utilisés pour désigner le même concept, selon le niveau de description (théorie ou pratique) et le système (Access, SQL Server, dBase). Dans le tableau 2.3 fournit un résumé des termes utilisés.

Tableau 2.3. Terminologie de la base de données

Théorie des bases de données____________ Bases de données relationnelles__________ SQL Server __________

Tableau des relations Tableau

Ligne d'enregistrement de tuple

AttributeField_______________Colonne

Bases de données relationnelles

Base de données relationnelle est un ensemble de relations contenant toutes les informations qui doivent être stockées dans la base de données. Autrement dit, la base de données représente un ensemble de tables nécessaires pour stocker toutes les données. Les tables d'une base de données relationnelle sont logiquement liées les unes aux autres. Les exigences de conception d'une base de données relationnelle en général peuvent être réduites à plusieurs règles.

О Chaque table a un nom unique dans la base de données et est constituée de lignes du même type.

O Chaque tableau se compose d'un nombre fixe de colonnes et de valeurs. Plus d’une valeur ne peut pas être stockée dans une seule colonne. Par exemple, s'il existe un tableau contenant des informations sur l'auteur, la date de publication, le tirage, etc., la colonne avec le nom de l'auteur ne peut pas stocker plus d'un nom de famille. Si le livre est écrit par deux auteurs ou plus, vous devrez utiliser des tableaux supplémentaires.

O À aucun moment, deux lignes du tableau ne se dupliqueront. Les lignes doivent différer par au moins une valeur afin de pouvoir identifier de manière unique n'importe quelle ligne du tableau.

О Chaque colonne se voit attribuer un nom unique au sein du tableau ; un type de données spécifique lui est défini afin que des valeurs homogènes soient placées dans cette colonne (dates, noms de famille, numéros de téléphone, montants monétaires, etc.).

O Le contenu informatif complet d'une base de données est représenté sous forme de valeurs explicites des données elles-mêmes, et c'est la seule méthode de représentation. Par exemple, les relations entre les tables sont basées sur les données stockées dans les colonnes correspondantes, et non sur des pointeurs définissant artificiellement les relations.

О Lors du traitement des données, vous pouvez accéder librement à n'importe quelle ligne ou n'importe quelle colonne du tableau. Les valeurs stockées dans le tableau n'imposent aucune restriction sur l'ordre dans lequel les données sont accessibles. Description des colonnes,