Y a-t-il une raison de s’inquiéter de l’ordre des colonnes dans un tableau?

Je sais que vous pouvez modifier l’ordre des colonnes avec MySQL avec FIRST et AFTER, mais pourquoi voudriez-vous vous en occuper? Étant donné que les bonnes requêtes nomment explicitement les colonnes lors de l’insertion de données, existe-t-il vraiment une raison de s’interroger sur l’ordre dans lequel se trouvent les colonnes dans la table?

L’ordre des colonnes a eu un impact considérable sur certaines des bases de données que j’ai configurées, notamment Sql Server, Oracle et MySQL. Ce post a de bonnes règles de base :

  • Colonnes de clé primaire en premier
  • Colonnes de clés étrangères ensuite.
  • Colonnes fréquemment recherchées ensuite
  • Colonnes mises à jour fréquemment plus tard
  • Les colonnes nullables durent.
  • Les colonnes nullables les moins utilisées après les colonnes nullables les plus fréquemment utilisées

Un exemple de différence de performance est une recherche d’index. Le moteur de firebase database trouve une ligne basée sur certaines conditions de l’index et récupère une adresse de ligne. Maintenant, disons que vous cherchez SomeValue, et c’est dans cette table:

SomeId int, SomeSsortingng varchar(100), SomeValue int 

Le moteur doit deviner où commence SomeValue, car SomeSsortingng a une longueur inconnue. Cependant, si vous modifiez la commande pour:

  SomeId int, SomeValue int, SomeSsortingng varchar(100) 

Maintenant, le moteur sait que SomeValue peut être trouvé 4 octets après le début de la ligne. L’ordre des colonnes peut donc avoir un impact considérable sur les performances.

EDIT: SQL Server 2005 stocke les champs de longueur fixe au début de la ligne. Et chaque ligne a une référence au début d’un varchar. Cela annule complètement l’effet que j’ai énuméré ci-dessus. Ainsi, pour les bases de données récentes, l’ordre des colonnes n’a plus d’impact.

Mettre à jour:

Dans MySQL , il peut y avoir une raison de le faire.

Comme les types de données variables (comme VARCHAR ) sont stockés avec des longueurs variables dans InnoDB , le moteur de firebase database doit parcourir toutes les colonnes précédentes de chaque ligne pour déterminer le décalage de celle-ci.

L’impact peut atteindre 17% pour 20 colonnes.

Voir cette entrée dans mon blog pour plus de détails:

  • Choisir l’ordre des colonnes

Dans Oracle , les colonnes NULL ne consumnt aucun espace, c’est pourquoi vous devez toujours les placer à la fin du tableau.

De même, dans Oracle et SQL Server , en cas de grande ligne, il se peut que ROW CHAINING se produise.

ROW CHANING divise une ligne qui ne rentre pas dans un bloc et la recouvre sur plusieurs blocs, connectés à une liste chaînée.

La lecture des colonnes qui ne rentrent pas dans le premier bloc nécessitera de parcourir la liste chaînée, ce qui entraînera une opération d’ I/O supplémentaire.

Voir cette page pour une illustration de ROW CHAINING dans Oracle :

C’est pourquoi vous devez placer les colonnes que vous utilisez souvent au début de la table, et les colonnes que vous n’utilisez pas souvent, ou les colonnes qui ont tendance à être NULL , à la fin de la table.

Note importante:

Si vous aimez cette réponse et que vous souhaitez voter, veuillez voter pour la @Andomar de @Andomar .

Il a répondu à la même chose, mais semble être abaissé sans raison.

Au cours de la formation Oracle à un travail précédent, notre DBA a suggéré que mettre toutes les colonnes non nulles avant les valeurs nullables était avantageux… bien que TBH je ne me souvienne pas des détails de la raison. Ou peut-être que c’étaient simplement ceux qui risquaient d’être mis à jour devraient aller à la fin? (Peut-être retarde-t-il le déplacement de la rangée s’il s’agrandit)

En général, cela ne devrait pas faire de différence. Comme vous le dites, les requêtes doivent toujours spécifier les colonnes elles-mêmes plutôt que de se fier à la commande “select *”. Je ne connais pas de firebase database qui leur permette de les modifier … eh bien, je ne savais pas que MySQL le permettait avant de l’avoir mentionné.

Certaines applications mal écrites peuvent dépendre de l’ordre / index de la colonne au lieu du nom de la colonne. Ils ne devraient pas l’être, mais ça arrive. Changer l’ordre des colonnes briserait ces applications.

Lisibilité de la sortie lorsque vous devez taper:

 select * from 

dans votre logiciel de gestion de firebase database?

C’est une raison très fallacieuse, mais pour le moment je ne peux penser à rien d’autre.

Non, l’ordre des colonnes dans une table de firebase database SQL n’a aucune importance, sauf pour l’affichage et l’impression. Il est inutile de réorganiser les colonnes – la plupart des systèmes ne permettent même pas de le faire (sauf en supprimant l’ancienne table et en la recréant avec le nouvel ordre des colonnes).

Marc

EDIT: à partir de l’entrée Wikipedia sur la firebase database relationnelle, voici la partie pertinente qui montre clairement que l’ordre des colonnes ne devrait jamais être un problème:

Une relation est définie comme un ensemble de n-uplets. En mathématiques comme dans le modèle de firebase database relationnelle, un ensemble est une collection non ordonnée d’éléments, bien que certains SGBD imposent un ordre à leurs données. En mathématiques, un tuple a un ordre et permet la duplication. EF Codd définissait à l’origine des tuples à l’aide de cette définition mathématique. Plus tard, EF Codd a compris que l’utilisation de noms d’atsortingbuts au lieu d’un classement serait beaucoup plus pratique (en général) dans un langage informatique basé sur les relations. Cette idée est encore utilisée aujourd’hui.

La seule raison pour laquelle je peux penser concerne le débogage et la lutte contre les incendies. Nous avons une table dont la colonne “name” apparaît environ 10ème sur la liste. Lorsque vous effectuez une sélection rapide * à partir de la table où id dans (1,2,3), vous rencontrez un problème, puis vous devez faire défiler les noms pour les parcourir.

Mais c’est à peu près tout.

Comme souvent, le facteur le plus important est le prochain qui doit travailler sur le système. J’essaie d’avoir les colonnes de clé primaire en premier, les colonnes de clé étrangère en second, puis le rest des colonnes en ordre décroissant d’importance / de signification pour le système.

Au-delà du réglage évident des performances, je suis tombé sur un cas en coin où la réorganisation des colonnes a provoqué l’échec d’un script SQL (auparavant fonctionnel).

A partir de la documentation “Les colonnes TIMESTAMP et DATETIME n’ont pas de propriétés automatiques, sauf si elles sont spécifiées explicitement, à cette exception: Par défaut, la première colonne TIMESTAMP a à la fois DEFAULT CURRENT_TIMESTAMP et ON UPDATE CURRENT_TIMESTAMP si aucune n’est explicitement spécifiée” .com / doc / refman / 5.6 / fr / timestamp-initialization.html

Ainsi, une commande ALTER TABLE table_name MODIFY field_name timestamp(6) NOT NULL; fonctionnera si ce champ est le premier horodatage (ou date-heure) dans une table, mais pas autrement.

De toute évidence, vous pouvez corriger cette commande alter afin d’inclure une valeur par défaut, mais le fait qu’une requête qui a fonctionné a cessé de fonctionner en raison d’une réorganisation de la colonne m’a fait mal à la tête.

Le seul moment où vous devez vous préoccuper de l’ordre des colonnes est que votre logiciel utilise spécifiquement cette commande. Typiquement, cela est dû au fait que le développeur est devenu paresseux et a fait une select * , puis il a fait référence aux colonnes par index plutôt que par nom dans leur résultat.

Si vous utilisez beaucoup UNION, cela facilite la mise en correspondance des colonnes si vous avez une convention concernant leur classement.

En général, ce qui se passe dans SQL Server lorsque vous modifiez l’ordre des colonnes via Management Studio, c’est qu’il crée une table temporaire avec la nouvelle structure, déplace les données vers cette structure depuis l’ancienne table, supprime l’ancienne table et renomme la nouvelle. Comme vous pouvez l’imaginer, c’est un très mauvais choix pour la performance si vous avez une grande table. Je ne sais pas si mon SQL fait la même chose, mais c’est une des raisons pour lesquelles beaucoup d’entre nous évitent de réorganiser les colonnes. Comme select * ne doit jamais être utilisé dans un système de production, l’ajout de colonnes à la fin n’est pas un problème pour un système bien conçu. L’ordre des colonnes dans la table ne devrait pas être gâché avec.

Comme indiqué, il existe de nombreux problèmes de performance potentiels. J’ai déjà travaillé sur une firebase database où la mise en place de colonnes très volumineuses améliorait les performances si vous ne référencez pas ces colonnes dans votre requête. Apparemment, si un enregistrement couvrait plusieurs blocs de disque, le moteur de firebase database pourrait cesser de lire les blocs une fois qu’il aurait obtenu toutes les colonnes nécessaires.

Bien sûr, toute implication de performance dépend fortement non seulement du fabricant que vous utilisez, mais également de la version. Il y a quelques mois, j’ai remarqué que nos Postgres ne pouvaient pas utiliser un index pour une comparaison “similaire”. En d’autres termes, si vous écriviez “une colonne comme” M% “”, ce n’était pas assez intelligent pour passer au M et quitter quand il a trouvé le premier N. Je comptais modifier un groupe de requêtes pour utiliser “between”. Ensuite, nous avons eu une nouvelle version de Postgres et elle a géré intelligemment les choses. Heureux de ne jamais pouvoir changer les requêtes. De toute évidence, ce n’est pas directement pertinent ici, mais mon point est que tout ce que vous faites pour des considérations d’efficacité pourrait être obsolète avec la prochaine version.

L’ordre des colonnes est presque toujours très pertinent pour moi, car j’écris régulièrement du code générique qui lit le schéma de la firebase database pour créer des écrans. Par exemple, mes écrans “edit a record” sont presque toujours construits en lisant le schéma pour obtenir la liste des champs, puis en les affichant dans l’ordre. Si je changeais l’ordre des colonnes, mon programme fonctionnerait toujours, mais l’affichage pourrait être étrange pour l’utilisateur. Comme, vous vous attendez à voir le nom / adresse / ville / état / zip, pas la ville / adresse / zip / nom / état. Bien sûr, je pourrais mettre l’ordre d’affichage des colonnes dans le code ou un fichier de contrôle ou quelque chose, mais chaque fois que nous avons ajouté ou supprimé une colonne, nous devons nous rappeler d’aller mettre à jour le fichier de contrôle. J’aime dire des choses une fois. De plus, lorsque l’écran d’édition est construit uniquement à partir du schéma, l’ajout d’une nouvelle table peut signifier écrire des lignes de code nulles pour créer un écran d’édition, ce qui est très cool. (Eh bien, en pratique, en général, je dois append une entrée au menu pour appeler le programme d’édition générique, et j’ai généralement abandonné le générique “sélectionner un enregistrement à mettre à jour” car il y a trop d’exceptions pour le rendre pratique .)