Entretien avec le développeur : analyse des questions de base de données

Salut tout le monde! Nous travaillons tous ici vers un seul objectif : devenir des développeurs Java . L’entretien technique est peut-être l’étape la plus importante pour devenir professionnel. En règle générale, l'intervieweur passe en revue les sujets principaux en posant quelques questions. Dans cet article, nous parlerons d'un de ces sujets clés : les bases de données . Regardons les questions les plus fréquemment posées et essayons d'y répondre sans plonger profondément dans la matière, car dans ce cas le volume du livre ne nous suffira pas ! Alors allons-y.

1. Que sont les bases de données ? En quels types sont-ils divisés ?

Qu’entend-on par SGBD ?

Une base de données (DB) est une structure organisée conçue pour stocker, modifier et traiter des informations interdépendantes, principalement de gros volumes. En d’autres termes, une base de données est un stockage de données structuré. Par exemple, un annuaire téléphonique.

Types de bases de données

1. Une base de données relationnelle est un ensemble de données avec des relations prédéfinies entre elles. Les données sont stockées sous forme d'un ensemble de tableaux composés de colonnes et de lignes. Les tables stockent des informations sur les objets représentés dans la base de données. Chaque colonne du tableau stocke un type de données spécifique et chaque cellule stocke une valeur d'attribut.
2. Les systèmes non relationnels (NoSQL) sont des systèmes conçus pour des modèles de données spécifiques avec des schémas flexibles. En d'autres termes, il s'agit de bases de données qui stockent les données non pas sous forme de tableaux, de lignes et de colonnes, mais dans d'autres formats.

Vous pouvez en savoir plus sur les bases de données non relationnelles dans cet article : Un guide de NoSQL pour les développeurs . Un système de gestion de bases de données (SGBD) est un ensemble de logiciels avec lesquels l'utilisateur peut créer des bases de données (DB) et y effectuer diverses opérations : compléter, mettre à jour, supprimer, sélectionner, etc. Le SGBD garantit la sûreté, l'intégrité, la sécurité des stockage des données et vous permet d'accorder l'accès à l'administration de la base de données. A titre d'exemple, MySql est un SGBD qui donne accès à une base de données relationnelle ou MongoDB pour une base de données non relationnelle.

2. Qu'est-ce que la normalisation ? Forme normalisée ? Combien de formes de normalisation existe-t-il ? Nommez les trois premiers.

La normalisation est le processus d'organisation et de structuration des données dans une base de données, qui offre une plus grande flexibilité à la base de données en éliminant la redondance et l'incohérence des dépendances. La forme normale est une propriété d'un tableau, considérée dans le contexte de la normalisation, qui caractérise le tableau en termes de simplicité et d'exactitude de la structure. La forme normale est définie comme un ensemble d'exigences auxquelles une table doit satisfaire. Il existe six formes normales au total, mais en pratique, seules les trois premières sont utilisées :

1. Première forme normale :
   • Tous les attributs sont simples (c'est-à-dire atomiques et indivisibles) ;
   • Toutes les données sont scalaires (c'est-à-dire positives) ;
   • Il n'y a pas de lignes en double (pour cela, une clé primaire est créée pour chaque ligne).
2. Deuxième forme normale :
   • Les conditions de la première forme normale sont remplies ;
   • Chaque attribut non-clé fait référence à une clé primaire.
3. Troisième forme normale :
   • Les conditions du deuxième groupe normal sont remplies ;
   • Les champs non clés sont indépendants des autres champs non clés : ils ne peuvent être associés qu'à une clé primaire.

3. Dénormalisation

La dénormalisation est la réduction ou la violation délibérée des formes de normalisation d'une base de données, généralement pour accélérer la lecture de la base de données en ajoutant des données redondantes. En général, il s'agit d'un processus inverse de la normalisation. Cela se produit parce que la théorie des formes normales n’est pas toujours applicable dans la pratique. Par exemple, les valeurs non atomiques ne sont pas toujours « mauvaises » : parfois même le contraire. Dans certains cas, des jointures supplémentaires sont nécessaires lors de l'exécution de requêtes, notamment lors du traitement d'une grande quantité d'informations. Cela peut finalement améliorer les performances. Les bases de données destinées à l'analyse sont souvent dénormalisées pour accélérer l'exécution des requêtes. Par exemple, vous échantillonnerez souvent certaines données pour des rapports dans lesquels les colonnes non clés seront liées les unes aux autres. Vous supprimez intentionnellement la troisième forme de normalisation et combinez tout dans une seule table pour faciliter l'échantillonnage - afin de ne pas avoir à effectuer de requêtes supplémentaires sur d'autres tables.

4. Index

Un index est un ensemble trié de valeurs associées à une table ou une vue avec une colonne spécifique qui accélère la récupération des données. C'est-à-dire qu'il s'agit d'une sorte d'index : comme l'alphabet dans un annuaire téléphonique, qui nous aide lors de la recherche par nom de famille. Si elle est utilisée correctement, cette fonctionnalité peut améliorer considérablement les performances lorsque vous travaillez avec de grandes bases de données. Ou vous pouvez le baisser beaucoup. Pour accélérer la recherche, ces clés sont stockées dans une arborescence équilibrée à travers laquelle la recherche est effectuée. En règle générale, les index doivent être saisis sur les champs les plus souvent recherchés. Vous devriez penser à créer un index au plus tôt lorsque vous disposez d'au moins 10 000 enregistrements. Sinon, vous ne verrez pas de résultat visible, car une optimisation prématurée est MAL . Et comment un index peut-il affecter les performances du système, demandez-vous ? Lorsque de nouvelles données sont insérées ou que d'anciennes données sont supprimées, la structure arborescente équilibrée sera recalculée. En fait, plus il y a de données et d’index, plus il faut compter d’arbres. Imaginez la situation : vous avez environ 20 000 enregistrements et 7 index sur cette table. Autrement dit, lors de l'insertion de données, vous devez recalculer 7 arbres, chacun contenant 20 000 enregistrements. À proprement parler, l’utilisation d’index pour les tables auxquelles des données seront fréquemment ajoutées/supprimées n’est pas du tout recommandée. Enfin, je voudrais noter que les index des colonnes dans lesquelles la valeur se trouve souvent nullne seront pas aussi efficaces, cela ne vaut donc pas la peine de les ajouter à ces colonnes.

Quelle est la différence entre les index clusterisés et non clusterisés dans SQL ?

En cluster :

• Fournit un ordre physique pour le champ sélectionné ;
• Si une table a un index clusterisé, elle est dite clusterisée ;
• Pas plus d’un index par table n’est nécessaire ;
• Dans MySQL, un index clusterisé n'est pas explicitement spécifié par l'utilisateur, car si vous ne définissez pas de PRIMARY KEY sur votre table, MySQL trouve le premier index UNIQUEoù se trouvent toutes les colonnes clés NOT NULLet InnoDB l'utilise comme index clusterisé.

Non clusterisé :

• Jusqu'à 999 index non clusterisés sont possibles par table ;
• Contient un pointeur vers les lignes contenant des données réelles dans le tableau ;
• Ne fournit pas d'ordre physique ;
• Pour les index non clusterisés, il existe des tables distinctes avec des données triées, à savoir une table pour une colonne sur laquelle se trouve l'index. Par conséquent, lors de la demande de données qui ne font pas partie d'un champ donné, la requête sera d'abord effectuée sur le champ dans cette table, et ensuite seulement la requête supplémentaire sur une ligne de la table d'origine.

Création d'un index non clusterisé :

CREATE INDEX index_name ON table_name(column_name)

6. Qu'est-ce qu'un indice composite ?

Index composite - construit avec envoi vers plusieurs colonnes en même temps. Autrement dit, il s’agit d’un index complexe composé de plusieurs colonnes. De tels index sont utilisés lorsque plusieurs colonnes apparaissent dans une requête. Création d'un index composite :

CREATE INDEX index_name ON table_name(first_column_name, second_column_name, third_column_name)

En règle générale, ces index sont utilisés lorsque les données de plusieurs colonnes sont logiquement liées.

7. Qu'est-ce qu'un indice de couverture ? Un indice unique ?

Un index de couverture est un index suffisant pour répondre à une requête sans accéder à la table elle-même. En utilisant cet index, vous pouvez obtenir la ligne entière de données, mais en réalité, ce n'est tout simplement pas nécessaire. Étant donné que vous n'avez pas besoin d'accéder directement à la table source et que vous pouvez répondre en utilisant uniquement l'index, les index de couverture sont légèrement plus rapides à utiliser. Dans le même temps, n'oubliez pas que plus il y a de colonnes, plus l'index lui-même devient encombrant et lent. Il ne faut donc pas en abuser. Ci-dessus, nous avons parlé des index clusterisés et non clusterisés, qui peuvent être uniques . Cela signifie qu'il n'y a pas deux champs ayant la même valeur pour la clé d'index. Sinon, l'index ne sera pas unique, car plusieurs lignes peuvent contenir la même valeur. Un exemple de création d'un index unique non clusterisé :

CREATE UNIQUE INDEX index_name ON table_name(column_name)

8. Qu'est-ce qu'une clé primaire

La clé primaire est un champ d'une table qui identifie chaque ligne d'une table de base de données. Il ne peut y avoir qu'un seul champ de ce type dans une table et toutes les valeurs doivent être uniques. Cela ne vous a rappelé rien ? Après tout, une clé primaire n’est rien de plus qu’un index clusterisé unique . En règle générale, les clés primaires sont créées lors de la création d'une table :

CREATE TABLE table_name(
column_name int PRIMARY KEY,
…..)

Une restriction sera automatiquement ajoutée à cette colonne - NOT NULL. Vous pouvez également définir une clé pour une table déjà créée :

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

Si une clé primaire est ajoutée de la manière décrite ci-dessus, alors les valeurs des champs spécifiés comme clé primaire ( column_name) sont vérifiées pour s'assurer qu'elles ne contiennent pas de valeurs nulles (une contrainte sera également ajoutée - NOT NULL).

Qu'est-ce qu'une clé étrangère ?

Une clé étrangère est une propriété créée pour fournir une relation entre des tables. En règle générale, une clé étrangère est définie sur les colonnes d'une sous-table et pointe vers l'une des colonnes de la table principale. Peut être spécifié comme lors de la création d'une table :

CREATE TABLE table_name{
column_name int,
…..
FOREIGN KEY(column_name) REFERENCES another_table_name(another_table_column_name) }

Donc après avoir créé le tableau :

ALTER TABLE table_name
ADD FOREIGN KEY(column_name) REFERENCES another_table_name(another_table_column_name));

Vous pouvez définir le comportement d'une clé étrangère lors de la manipulation du champ auquel elle fait référence. Les manipulations peuvent être des ON DELETEtypes suivants ON UPDATE: Options de comportement possibles :

• CASCADE— avec cette propriété, les lignes de la table dépendante seront automatiquement supprimées ou modifiées lorsque les lignes associées sont supprimées ou modifiées dans la table principale ;
• SET NULL— avec cette propriété, lorsqu'une ligne associée est supprimée ou mise à jour de la table principale, la valeur NULLde la colonne de clé étrangère sera définie ;
• NO ACTION— rejette les tentatives de suppression ou de modification de lignes dans la table principale s'il existe des lignes associées dans la table dépendante ;
• RESTRICT- équivalent à NO ACTION;
SET DEFAULT- avec cette propriété, lorsqu'une ligne associée est supprimée ou mise à jour de la table principale, la valeur par défaut (le cas échéant) pour la colonne de clé étrangère sera définie.

Exemple d'utilisation :

CREATE TABLE table_name{
column_name int,
…..
FOREIGN KEY(column_name) REFERENCES another_table_name(another_table_column_name) ON UPDATE CASCADE ON DELETE CASCADE }

ON DELETESi le comportement de et n'est pas explicitement défini ON UPDATE, le comportement sera défini sur RESTRICT.

10. Types de connexions entre les tables (Join)

La connexion entre les tables est assurée sur la base de données communes (champs). Cela se produit à l'aide d'un opérateur JOIN, une opération qui fait correspondre les lignes d'une table avec les lignes d'une autre. Le mappage est effectué de manière à ce que les colonnes des deux tables soient adjacentes, bien qu'elles puissent être obtenues à partir de tables distinctes. Et si nous avons des champs communs pour trois tables, nous pouvons afficher leurs données comme une seule table commune. Cependant, il convient de considérer que moins il y a de tables jointes, plus la requête s'exécutera rapidement. Donc les types JOIN:

• INNER JOIN- une connexion qui affiche uniquement les données de la première table qui correspondent à certaines données de la deuxième table. Le reste descend.
• LEFT JOIN- une connexion qui affiche toutes les données de la première table et les données correspondantes de la seconde, le cas échéant. S'il n'y a pas de données correspondantes, les champs pour les données de la deuxième table seront vides.
• RIGHT JOIN- une connexion qui affiche toutes les données de la deuxième table et les données correspondantes de la première, le cas échéant. S'il n'y a pas de données correspondantes, les champs pour les données de la première table seront vides.
• FULL JOIN- une connexion qui affiche toutes les données des première et deuxième tables. S'il n'y a aucune donnée associée dans l'autre table, les champs de ces données seront vides.
• CROSS JOIN- une jointure croisée dans laquelle chaque ligne du premier tableau est jointe à chaque ligne du deuxième tableau (chacun à chacun). Autrement dit, si deux tables ont chacune 3 lignes, après cette jointure, nous obtiendrons un résultat de 9 lignes.

Exemple Join(inner):

SELECT *
FROM first_table
INNER JOIN second_table ON first_table.some_column = second_table.some_column

11. Qu'est-ce qu'une propriété ACID dans une base de données ?

A - Atomicité , garantit qu'aucune transaction n'est partiellement validée dans le système. Soit toutes ses sous-opérations sont effectuées, soit aucune. Par exemple, transférer de l’argent d’une banque vers un autre compte implique deux opérations :

1. Transférer de l'argent sur un compte bancaire.
2. Transférer de l'argent d'un compte bancaire vers un compte spécifique.

Mais tout peut arriver. Par exemple, ils iront à la banque, puis une erreur se produira et la deuxième opération ne sera pas terminée. Ou vice versa : seule la deuxième opération sera effectuée. Par conséquent, ces actions sont effectuées au sein d’une seule transaction et le résultat est tout ou rien. C - Cohérence : Chaque transaction réussie enregistre toujours uniquement des résultats résolubles. Cela garantit que toutes les restrictions sont respectées (par exemple, NOT NULL), sinon la transaction sera annulée. Et - l'isolement : lors de l'exécution d'une transaction, les transactions parallèles ne doivent pas affecter son résultat. Cela nous donne la possibilité de cacher à tout le monde les états de données non finaux. En fait, c’est pourquoi les transactions infructueuses ne peuvent rien casser. Un peu plus bas, nous nous familiariserons avec les niveaux d'isolement des transactions. D - Durabilité : Si une transaction est terminée, alors vous pouvez être sûr que les modifications apportées ne seront pas annulées en raison d'un échec.

12. Niveaux d'isolement des transactions

Chaque niveau d'isolement autorise/interdit certaines actions (opportunités) :

• lecture fantôme - au sein d'une même transaction, la même demande de données donne des résultats différents, ce qui se produit en raison de l'ajout de données par une autre transaction (parallèle).
• lecture non répétitive - au sein d'une même transaction, la même demande de données donne des résultats différents, ce qui se produit en raison de la modification ou de la suppression des données par une autre transaction (parallèle).
• lecture « sale » - lecture de données ajoutées ou modifiées par une transaction qui ne seront pas annulées par la suite ;
• mise à jour perdue - lorsque différentes transactions modifient le même bloc de données en même temps, toutes les modifications sauf la dernière sont perdues (semblable à une « condition de concurrence critique » en multithreading).

Pour plus de commodité, nous considérons les niveaux d'isolement et leurs capacités dans le tableau :

13. Qu'est-ce que l'injection SQL ?

L'injection SQL est l'une des méthodes de piratage d'un site Web, dont l'essence est l'injection de code SQL dans les données via GETdes requêtes POSTou des cookies. Si un site Web effectue de telles injections, il est possible d'accéder à la base de données et de pirater l'application. Par exemple, nous connaissons le nom d'une variable. Disons column_nameavec type boolean. Si le système est sensible aux injections, nous pouvons ajouter OR column_name=truepuis écrire tout ce dont nous avons besoin à partir de la base de données. ORcréera une condition OR, et notre expression après celle-ci sera toujours true, ce qui nous mènera plus loin. Une attaque sur un site Web telle que l'injection SQL est possible en raison d'un traitement inapproprié des données entrantes utilisées dans les requêtes SQL. Lors de la connexion à une base de données à l'aide de JDBC , vous utilisez différents fichiers Statements. Pour augmenter la sécurité, il est nécessaire d'utiliser PreparedStatementà la place de l'habituel Statement, car lorsqu'elles sont utilisées, Statementles chaînes de requête et les valeurs sont simplement additionnées, rendant les injections possibles. À son tour, il PreparedStatementexiste un modèle de demande spécifique dans lequel les données sont insérées avec les guillemets reflétés. En conséquence, les injections SQL seront perçues uniquement comme une représentation sous forme de chaîne d'un champ. Pour vous protéger contre les injections SQL, vous pouvez utiliser des contrôles basés sur des expressions régulières (vous pouvez en savoir plus sur les expressions régulières dans cet article ). Une autre option consiste à définir une limite sur le nombre de caractères des paramètres entrants : par exemple, si vous devez recevoir un nombre ne dépassant pas 9999, une limite de quatre caractères entrants fera l'affaire. Cela réduira le risque de piratage à l'aide d'injections SQL. Vous pouvez en apprendre davantage sur la sécurité en Java dans l'article « Sécurité en Java : bonnes pratiques » .

14. Que sont les procédures stockées ? Fonctions stockées ? Déclenchement?

Les procédures stockées dans SQL sont une entité dans la base de données, qui est un ensemble d'instructions SQL compilées une fois et stockées sur le serveur. En un mot, c'est un analogue des méthodes Java. Les procédures stockées peuvent effectuer des actions sur les données, à la fois des requêtes normales et certaines actions qui ne sont pas disponibles pour les requêtes normales. Une procédure est une entité SQL créée une seule fois puis appelée en passant des arguments. L’avantage de cette approche est que ces instructions peuvent être réutilisées plusieurs fois. Les procédures stockées améliorent les performances, améliorent les capacités de programmation et prennent en charge les fonctionnalités de sécurité des données. Envisageons de créer une procédure :

CREATE PROCEDURE procedure_name (first_param some_type, second_param some_type..)
 begin
……...
 end

Appel de la procédure :

CALL procedure_name (first_param, second_param…..);

Une fonction stockée est un type de procédure stockée. La différence entre une fonction est qu'elle ne renvoie toujours qu'une seule valeur, tandis qu'une procédure renvoie toujours un ensemble de valeurs. Les procédures stockées ne peuvent pas être mélangées avec du SQL standard, alors qu'une fonction stockée le peut - et c'est son avantage. D’un autre côté, les fonctions stockées ont bien plus de limitations que les procédures. Création d'une fonction stockée :

CREATE FUNCTION function_name (first_param, second_param…..)
RETURNS some_type
 begin
……...
RETURN some_value;
end

Appel d'une fonction stockée :

SELECT function_name(first_param, second_param…..);

Un déclencheur est un autre type de procédure stockée qui n'est pas appelée directement par l'utilisateur, mais qui est activée lorsque les données sont modifiées. Autrement dit, cette procédure est activée lorsque certaines conditions sont remplies, telles que, INSERTou DELETE, ou UPDATEdes données dans une certaine colonne d'une table donnée. Le moment où un déclencheur est déclenché est déterminé à l'aide des mots-clés BEFORE(le déclencheur se déclenche avant l'événement associé) ou AFTER(après l'événement).

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT
 begin
……...
 end

15. Pratique

Quoi qu'il en soit, la question SQL la plus courante lors d'un entretien sera la pratique : la résolution de problèmes. Cela ne sert à rien d'essayer de deviner à quelles tâches vous serez confronté, car tout dépend de la sophistication de l'imagination de la personne d'en face. Par conséquent, la seule option efficace serait de s’améliorer dans les requêtes SQL de complexité variable. sql-ex.ru peut servir de ressource pour s'entraîner sur diverses tâches . Après les vingt premières tâches terminées, il sera assez difficile pour votre interlocuteur de vous faire peur avec une quelconque tâche SQL. C'est tout pour aujourd'hui : j'espère qu'après avoir lu cet article, les questions sur les bases de données ne poseront ni difficultés ni problèmes. Merci de votre attention et à bientôt !