Question Erreurs de développement de base de données effectuées par les développeurs d'applications [fermé]


Quelles sont les erreurs de développement de base de données communes faites par les développeurs d'applications?


566
2018-03-29 13:38


origine


Réponses:


1. Ne pas utiliser d'indices appropriés

C'est relativement facile mais ça arrive tout le temps. Les clés étrangères doivent avoir des index sur eux. Si vous utilisez un champ dans un WHERE vous devriez (probablement) avoir un index dessus. Ces index doivent souvent couvrir plusieurs colonnes en fonction des requêtes que vous devez exécuter.

2. Ne pas appliquer l'intégrité référentielle

Votre base de données peut varier ici mais si votre base de données prend en charge l'intégrité référentielle - ce qui signifie que toutes les clés étrangères sont censées pointer vers une entité existante - vous devriez l'utiliser.

Il est assez fréquent de voir cet échec sur les bases de données MySQL. Je ne crois pas que MyISAM le supporte. InnoDB fait. Vous trouverez des personnes qui utilisent MyISAM ou celles qui utilisent InnoDB mais qui ne l'utilisent pas de toute façon.

Plus ici:

3. Utiliser des clés primaires naturelles plutôt que substitutives (techniques)

Les clés naturelles sont des clés basées sur des données significatives externes (ostensiblement) uniques. Les exemples courants sont les codes de produit, les codes d'état à deux lettres (US), les numéros de sécurité sociale, etc. Les clés primaires de substitution ou techniques sont celles qui n'ont absolument aucune signification en dehors du système. Ils sont inventés uniquement pour identifier l'entité et sont généralement des champs auto-incrémentés (SQL Server, MySQL, autres) ou des séquences (notamment Oracle).

À mon avis, vous devriez toujours utiliser des clés de substitution. Ce problème est apparu dans ces questions:

C'est un sujet un peu controversé sur lequel vous n'obtiendrez pas un accord universel. Bien que vous puissiez trouver certaines personnes, qui pensent que les clés naturelles sont dans certaines situations OK, vous ne trouverez aucune critique des clés de substitution autre que d'être sans doute inutiles. C'est un petit inconvénient si vous me demandez.

Rappelez-vous, même les pays peuvent cesser d'exister (par exemple, Yougoslavie).

4. Écrire des requêtes qui nécessitent DISTINCT travailler

Vous voyez souvent cela dans les requêtes générées par ORM. Regardez la sortie du journal d'Hibernate et vous verrez toutes les requêtes commencer par:

SELECT DISTINCT ...

C'est un peu un raccourci pour vous assurer de ne pas retourner les lignes en double et ainsi obtenir des objets en double. Vous verrez parfois des gens faire cela aussi. Si vous le voyez trop, c'est un vrai drapeau rouge. Pas ça DISTINCT est incorrect ou n'a pas d'applications valides. C'est le cas (sur les deux points) mais ce n'est pas un substitut ou un stoppap pour écrire des requêtes correctes.

De Pourquoi je déteste DISTINCT:

Où les choses commencent à devenir aigres dans mon   opinion est quand un développeur est   construire une requête substantielle, rejoindre   tables ensemble, et tout d'un coup   il se rend compte qu'il regards comme il est   obtenir des lignes en double (ou même plus)   et sa réponse immédiate ... son   "solution" à ce "problème" est de   lancer sur le mot-clé DISTINCT et POOF   tous ses ennuis disparaissent.

5. Favoriser l'agrégation sur les jointures

Une autre erreur courante des développeurs d'applications de base de données est de ne pas réaliser à quel point l'agrégation est plus coûteuse GROUP BY clause) peut être comparé à des jointures.

Pour vous donner une idée de l'ampleur de ce phénomène, j'ai écrit plusieurs fois sur ce sujet et je me suis beaucoup déprécié pour cela. Par exemple:

De Instruction SQL - "joindre" vs "groupe par et ayant":

Première requête:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

Temps de requête: 0.312 s

Deuxième requête:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Temps de requête: 0.016 s

C'est vrai. La version jointe I   proposé est vingt fois plus vite que   la version agrégée.

6. Ne pas simplifier les requêtes complexes à travers les vues

Tous les fournisseurs de bases de données ne prennent pas en charge les vues, mais pour celles qui le font, elles peuvent considérablement simplifier les requêtes si elles sont utilisées judicieusement. Par exemple, sur un projet, j'ai utilisé un modèle Party générique pour CRM. C'est une technique de modélisation extrêmement puissante et flexible mais qui peut conduire à de nombreuses jointures. Dans ce modèle, il y avait:

  • Fête: les personnes et les organisations;
  • Rôle du partiles choses que ces parties ont faites, par exemple, l'employé et l'employeur;
  • Relation de rôle de partie: comment ces rôles liés les uns aux autres.

Exemple:

  • Ted est une personne, étant un sous-type de parti;
  • Ted a de nombreux rôles, dont l'un est employé;
  • Intel est une organisation, étant un sous-type d'un parti;
  • Intel a de nombreux rôles, dont l'un est Employeur;
  • Intel emploie Ted, ce qui signifie qu'il existe une relation entre leurs rôles respectifs.

Il y a donc cinq tables jointes pour relier Ted à son employeur. Vous supposez que tous les employés sont des personnes (pas des organisations) et fournissent cette vue d'assistance:

CREATE VIEW vw_employee AS
SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name
FROM person p
JOIN party py ON py.id = p.id
JOIN party_role child ON p.id = child.party_id
JOIN party_role_relationship prr ON child.id = prr.child_id AND prr.type = 'EMPLOYMENT'
JOIN party_role parent ON parent.id = prr.parent_id = parent.id
JOIN party p2 ON parent.party_id = p2.id

Et soudain, vous avez une vue très simple des données que vous voulez mais sur un modèle de données très flexible.

7. Pas d'entrée d'assainissement

C'est énorme. Maintenant, j'aime PHP, mais si vous ne savez pas ce que vous faites, il est vraiment facile de créer des sites vulnérables aux attaques. Rien ne résume mieux que le histoire des petites tables Bobby.

Données fournies par l'utilisateur au moyen d'URL, de données de formulaire et les cookies devrait toujours être traité comme hostile et aseptisé. Assurez-vous que vous obtenez ce que vous attendez.

8. Ne pas utiliser les instructions préparées

Les instructions préparées sont lorsque vous compilez une requête moins les données utilisées dans les insertions, mises à jour et WHERE clauses et ensuite fournir cela plus tard. Par exemple:

SELECT * FROM users WHERE username = 'bob'

contre

SELECT * FROM users WHERE username = ?

ou

SELECT * FROM users WHERE username = :username

en fonction de votre plate-forme.

J'ai vu des bases de données mises à genoux en faisant cela. Fondamentalement, chaque fois qu'une base de données moderne rencontre une nouvelle requête, elle doit la compiler. S'il rencontre une requête déjà vue, vous donnez la possibilité à la base de données de mettre en cache la requête compilée et le plan d'exécution. En faisant beaucoup de requêtes, vous donnez à la base de données la possibilité de comprendre et d'optimiser en conséquence (par exemple, en épinglant la requête compilée en mémoire).

L'utilisation d'instructions préparées vous fournira également des statistiques significatives sur la fréquence d'utilisation de certaines requêtes.

Les instructions préparées vous protégeront également mieux contre les attaques par injection SQL.

9. Ne pas assez normaliser

Normalisation de base de données est essentiellement le processus d'optimisation de la conception de base de données ou comment vous organisez vos données dans des tableaux.

Juste cette semaine, j'ai couru à travers un code où quelqu'un avait implosé un tableau et l'avait inséré dans un seul champ dans une base de données. Normaliser cela consisterait à traiter l'élément de ce tableau comme une ligne distincte dans une table enfant (c'est-à-dire une relation un-à-plusieurs).

Cela est également venu dans Meilleure méthode pour stocker une liste d'ID utilisateur:

J'ai vu dans d'autres systèmes que la liste est stockée dans un tableau PHP sérialisé.

Mais le manque de normalisation prend plusieurs formes.

Plus:

10. Normaliser trop

Cela peut sembler une contradiction avec le point précédent mais la normalisation, comme beaucoup de choses, est un outil. C'est un moyen pour une fin et non une fin en soi. Je pense que de nombreux développeurs oublient cela et commencent à traiter un "moyen" comme une "fin". Les tests unitaires en sont un excellent exemple.

J'ai déjà travaillé sur un système qui avait une énorme hiérarchie pour les clients qui ressemblait à:

Licensee ->  Dealer Group -> Company -> Practice -> ...

de sorte que vous deviez joindre environ 11 tables ensemble avant que vous puissiez obtenir des données significatives. C'était un bon exemple de normalisation prise trop loin.

Plus précisément, une dénormalisation consciencieuse et réfléchie peut avoir d'énormes avantages sur le plan des performances, mais vous devez être très prudent lorsque vous faites cela.

Plus:

11. Utiliser des arcs exclusifs

Un arc exclusif est une erreur courante où une table est créée avec deux clés étrangères ou plus où une et une seule peut être non nulle. Grosse erreur.  D'une part, il devient beaucoup plus difficile de maintenir l'intégrité des données. Après tout, même avec l'intégrité référentielle, rien n'empêche le paramétrage de deux ou plusieurs de ces clés étrangères (nonobstant les contraintes de vérification complexes).

De Un guide pratique pour la conception de bases de données relationnelles:

Nous avons fortement déconseillé la construction exclusive d'arc partout   possible, pour la bonne raison qu'ils peuvent être gênant d'écrire du code   et posent plus de difficultés d'entretien.

12. Ne pas faire d'analyse de performance sur les requêtes du tout

Le pragmatisme règne en maître, en particulier dans le monde des bases de données. Si vous vous en tenez aux principes au point qu'ils sont devenus un dogme, vous avez probablement fait des erreurs. Prenons l'exemple des requêtes agrégées ci-dessus. La version agrégée peut sembler "gentille" mais ses performances sont déplorables. Une comparaison des performances aurait dû mettre un terme au débat (mais elle ne l'a pas fait), mais plus précisément: faire jaillir des opinions aussi mal informées en premier lieu est ignorant, voire dangereux.

13. Trop compter sur UNION ALL et en particulier des constructions UNION

Un UNION en termes SQL concatène simplement des ensembles de données congruents, ce qui signifie qu'ils ont le même type et le même nombre de colonnes. La différence entre eux est que UNION ALL est une concaténation simple et devrait être préférée dans la mesure du possible alors qu'une UNION fera implicitement un DISTINCT pour supprimer les tuples en double.

Les UNIONs, comme DISTINCT, ont leur place. Il y a des applications valides. Mais si vous vous trouvez en train de faire beaucoup d'entre eux, en particulier dans les sous-requêtes, alors vous faites probablement quelque chose de mal. Cela peut être un cas de mauvaise construction de requêtes ou un modèle de données mal conçu vous forçant à faire de telles choses.

Les UNIONs, en particulier lorsqu'ils sont utilisés dans des jointures ou des sous-requêtes dépendantes, peuvent paralyser une base de données. Essayez de les éviter autant que possible.

14. Utilisation de conditions OR dans les requêtes

Cela peut sembler inoffensif. Après tout, les AND sont OK. OU devrait être OK trop raison? Faux. Fondamentalement une condition ET restreint l'ensemble de données alors qu'une condition OU grandit mais pas d'une manière qui se prête à l'optimisation. Particulièrement lorsque les différentes conditions d'OR peuvent se croiser, obligeant ainsi l'optimiseur à effectuer une opération DISTINCT sur le résultat.

Mal:

... WHERE a = 2 OR a = 5 OR a = 11

Meilleur:

... WHERE a IN (2, 5, 11)

Maintenant, votre optimiseur SQL peut effectivement transformer la première requête en seconde. Mais ça pourrait ne pas être le cas. Ne fais pas ça.

15. Ne pas concevoir leur modèle de données pour se prêter à des solutions performantes

C'est un point difficile à quantifier. Il est généralement observé par son effet. Si vous vous trouvez à écrire des requêtes gnarly pour des tâches relativement simples ou que les requêtes pour trouver des informations relativement simples ne sont pas efficaces, alors vous avez probablement un mauvais modèle de données.

À certains égards, ce point résume tous les précédents, mais il s'agit plutôt d'un récit édifiant selon lequel l'optimisation des requêtes est souvent effectuée en premier, alors qu'elle devrait l'être en deuxième. Tout d'abord, vous devez vous assurer d'avoir un bon modèle de données avant d'essayer d'optimiser les performances. Comme l'a dit Knuth:

L'optimisation prématurée est la racine de tout Mal

16. Utilisation incorrecte des transactions de base de données

Toutes les modifications de données pour un processus spécifique doivent être atomiques. C'est à dire. Si l'opération réussit, elle le fait complètement. En cas d'échec, les données restent inchangées. - Il ne devrait pas y avoir de possibilité de changements «à moitié accomplis».

Idéalement, le moyen le plus simple d'y parvenir est que toute la conception du système devrait s'efforcer de prendre en charge toutes les modifications de données au moyen d'instructions INSERT / UPDATE / DELETE uniques. Dans ce cas, aucune manipulation de transaction spéciale n'est nécessaire, car votre moteur de base de données devrait le faire automatiquement.

Toutefois, si des processus nécessitent que plusieurs instructions soient exécutées en tant qu'unité pour conserver les données dans un état cohérent, un contrôle des transactions approprié est nécessaire.

  • Commencez une transaction avant la première déclaration.
  • Commettre la transaction après la dernière déclaration.
  • En cas d'erreur, annulez la transaction. Et très NB! N'oubliez pas d'ignorer / annuler toutes les instructions qui suivent l'erreur.

Il est également recommandé de prêter une attention particulière aux subtelties de la manière dont votre couche de connectivité de base de données et le moteur de base de données interagissent à cet égard.

17. Ne pas comprendre le paradigme «basé sur l'ensemble»

Le langage SQL suit un paradigme spécifique adapté à des types spécifiques de problèmes. En dépit de diverses extensions spécifiques au constructeur, le langage a du mal à gérer les problèmes qui sont triviaux dans des langues comme Java, C #, Delphi, etc.

Ce manque de compréhension se manifeste de plusieurs façons.

  • Imposer de manière inappropriée trop de logique procédurale ou impérative à la base de données.
  • Utilisation inappropriée ou excessive des curseurs. Surtout quand une seule requête suffirait.
  • En supposant de manière incorrecte que les déclencheurs se déclenchent une fois par ligne affectée dans les mises à jour à plusieurs lignes.

Déterminer clairement la répartition des responsabilités et s'efforcer d'utiliser l'outil approprié pour résoudre chaque problème.


1003



Erreurs de conception et de programmation de base de données clés faites par les développeurs

  • Conception et utilisation de base de données égoïstes.  Les développeurs traitent souvent la base de données comme leur magasin d'objets persistant personnel sans tenir compte des besoins des autres parties prenantes dans les données. Cela vaut également pour les architectes d'applications. La mauvaise conception de la base de données et l'intégrité des données compliquent la tâche des tiers qui travaillent avec les données et peuvent augmenter considérablement les coûts du cycle de vie du système. Le reporting et le MIS ont tendance à être un cousin pauvre dans la conception d'application et seulement fait après coup.

  • Abuser des données dénormalisées. Trop de données dénormalisées et essayer de le maintenir dans l'application est une recette pour les problèmes d'intégrité des données. Utilisez la dénormalisation avec parcimonie. Ne pas vouloir ajouter une jointure à une requête n'est pas une excuse pour dénormaliser.

  • Peur de l'écriture SQL.  SQL n'est pas sorcier et est en fait assez bon pour faire son travail. Les couches de mappage O / R sont assez efficaces pour traiter 95% des requêtes qui sont simples et s'intègrent bien dans ce modèle. Parfois, le SQL est la meilleure façon de faire le travail.

  • Les procédures Dogmatic 'No Stored Procedures'.  Peu importe si vous croyez que les procédures stockées sont mauvaises, ce genre d'attitude dogmatique n'a pas sa place sur un projet logiciel.

  • Ne pas comprendre la conception de la base de données  La normalisation est votre ami et c'est pas la science des fusées.  L'association et la cardinalité sont des concepts assez simples - si vous êtes impliqué dans le développement d'applications de base de données, il n'y a vraiment aucune excuse pour ne pas les comprendre.


110



  1. Ne pas utiliser le contrôle de version sur le schéma de base de données
  2. Travailler directement contre une base de données active
  3. Ne pas lire et comprendre des concepts de base de données plus avancés (index, index clusterisés, contraintes, vues matérialisées, etc.)
  4. A défaut de tester l'évolutivité ... les données de test de seulement 3 ou 4 lignes ne vous donneront jamais l'image réelle de la performance réelle

80



Surutilisation et / ou dépendance aux procédures stockées.

Certains développeurs d'applications considèrent les procédures stockées comme une extension directe du code intermédiaire / frontal. Cela semble être un trait commun dans les développeurs de pile Microsoft (j'en suis un, mais j'en ai grandi) et produit de nombreuses procédures stockées qui exécutent une logique métier et un traitement de workflow complexes. C'est beaucoup mieux fait ailleurs.

Les procédures stockées sont utiles lorsqu'il a été prouvé que certains facteurs techniques réels nécessitent leur utilisation (par exemple, la performance et la sécurité). Par exemple, maintenir l'agrégation / le filtrage des grands ensembles de données «proches des données».

J'ai récemment dû aider à maintenir et améliorer une grande application de bureau Delphi dont 70% de la logique métier et des règles ont été implémentées dans 1400 procédures stockées SQL Server (le reste dans les gestionnaires d'événements de l'interface utilisateur). Ce fut un cauchemar, principalement dû à la difficulté d'introduire des tests unitaires efficaces sur le TSQL, le manque d'encapsulation et les mauvais outils (Débogueurs, éditeurs).

En travaillant avec une équipe Java dans le passé, j'ai rapidement découvert que l'inverse était souvent vrai dans cet environnement. Un architecte Java m'a dit une fois: "La base de données est pour les données, pas le code.".

Ces jours-ci, je pense que c'est une erreur de ne pas considérer du tout les procédures stockées, mais elles devraient être utilisées avec parcimonie (pas par défaut) dans des situations où elles offrent des avantages utiles (voir les autres réponses).


46



Numéro un problème? Ils ne testent que sur des bases de données de jouets. Donc, ils n'ont aucune idée que leur SQL va explorer lorsque la base de données devient grande, et quelqu'un doit venir et réparer plus tard (ce son que vous pouvez entendre est mon grincement des dents).


41



Ne pas utiliser d'index.


31



Mauvaise performance causée par des sous-requêtes corrélées

La plupart du temps, vous voulez éviter les sous-requêtes corrélées. Une sous-requête est corrélée si, dans la sous-requête, il existe une référence à une colonne de la requête externe. Lorsque cela se produit, la sous-requête est exécutée au moins une fois pour chaque ligne renvoyée et peut être exécutée plusieurs fois si d'autres conditions sont appliquées après l'application de la condition contenant la sous-requête corrélée.

Pardonnez l'exemple inventé et la syntaxe Oracle, mais disons que vous vouliez trouver tous les employés qui ont été embauchés dans l'un de vos magasins depuis la dernière fois que le magasin a fait moins de 10 000 $ de ventes en une journée.

select e.first_name, e.last_name
from employee e
where e.start_date > 
        (select max(ds.transaction_date)
         from daily_sales ds
         where ds.store_id = e.store_id and
               ds.total < 10000)

La sous-requête de cet exemple est corrélée à la requête externe par le paramètre store_id et sera exécutée pour chaque employé de votre système. Une façon d'optimiser cette requête consiste à déplacer la sous-requête vers une vue en ligne.

select e.first_name, e.last_name
from employee e,
     (select ds.store_id,
             max(s.transaction_date) transaction_date
      from daily_sales ds
      where ds.total < 10000
      group by s.store_id) dsx
where e.store_id = dsx.store_id and
      e.start_date > dsx.transaction_date

Dans cet exemple, la requête dans la clause from est maintenant une vue en ligne (encore une fois une syntaxe spécifique à Oracle) et n'est exécutée qu'une seule fois. Selon votre modèle de données, cette requête s'exécutera probablement beaucoup plus rapidement. Il fonctionnerait mieux que la première requête que le nombre d'employés a augmenté. La première requête pourrait effectivement mieux fonctionner s'il y avait peu d'employés et de nombreux magasins (et peut-être que beaucoup de magasins n'avaient pas d'employés) et que la table daily_sales était indexée sur store_id. Ce n'est pas un scénario probable, mais montre comment une requête corrélée pourrait mieux fonctionner qu'une alternative.

J'ai vu des développeurs juniors corréler des sous-requêtes plusieurs fois et cela a généralement eu un impact sévère sur les performances. Cependant, lors de la suppression d'une sous-requête corrélée, assurez-vous de regarder expliquer le plan avant et après pour s'assurer que vous ne faites pas la performance pire.


28



Dans mon expérience:
Ne pas communiquer avec les administrateurs de base de données expérimentés.


21



Utiliser Access au lieu d'une base de données "réelle". Il y a beaucoup de grandes bases de données petites et même gratuites comme SQL Express, MySQL, et SQLite cela fonctionnera et évoluera beaucoup mieux. Les applications doivent souvent évoluer de manière inattendue.


17



Oublier de mettre en place des relations entre les tables. Je me souviens avoir dû nettoyer cela quand j'ai commencé à travailler chez mon employeur actuel.


16



Utiliser Excel pour stocker (énormément de) données.

J'ai vu des entreprises détenant des milliers de lignes et utilisant plusieurs feuilles de calcul (en raison de la limite de 65535 lignes sur les versions précédentes d'Excel).


Excel est bien adapté pour les rapports, la présentation de données et d'autres tâches, mais ne doit pas être traité comme une base de données.


14