Je sais que l'on peut modifier l'ordre des colonnes dans MySQL avec FIRST et AFTER, mais pourquoi s'en préoccuper ? Puisque les bonnes requêtes nomment explicitement les colonnes lors de l'insertion des données, y a-t-il vraiment une raison de se soucier de l'ordre de vos colonnes dans la table ?
L'ordre des colonnes a un impact important sur les performances de certaines des bases de données que j'ai optimisées, notamment Sql Server, Oracle et MySQL. Ce post a de bonnes règles de base :
Un exemple de différence de performance est la consultation d'un index. Le moteur de la base de données trouve une ligne en fonction de certaines conditions dans l'index, et renvoie une adresse de ligne. Imaginons maintenant que vous recherchez une certaine valeur, et qu'elle se trouve dans cette table :
SomeId int,
SomeString varchar(100),
SomeValue intLe moteur doit deviner où commence SomeValue, car SomeString a une longueur inconnue. Cependant, si vous changez l'ordre en :
SomeId int,
SomeValue int,
SomeString varchar(100)Maintenant, le moteur sait que SomeValue peut être trouvée 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. Donc pour les bases de données récentes, l'ordre des colonnes n'a plus aucun impact.
Tous les moteurs de base de données que je connais réservent 100 octets pour SomeString, même si elle est nulle.
Wow, je ne connaissais pas ça. N'est-il pas nécessaire d'aller chercher le bloc entier de toute façon, donc vous ne gagnez pas vraiment de temps en IO, juste la vitesse de calcul de l'offset.
Cela aurait donc un impact sur "select SomeValue from t"... (il s'agit sûrement plus de renvoyer cette valeur à partir d'un grand nombre de lignes que de consulter des index). Mais quel impact ?
Mise à jour :
En MySQL il peut y avoir une raison de le faire.
Puisque les types de données variables (comme VARCHAR ) sont stockés avec des longueurs variables dans InnoDB le moteur de base de données doit parcourir toutes les colonnes précédentes de chaque ligne pour trouver le décalage de la colonne donnée.
L'impact peut être aussi important que 17% pour 20 colonnes.
Voir cet article dans mon blog pour plus de détails :
En Oracle , suivi de NULL Les colonnes ne consomment pas d'espace, c'est pourquoi vous devez toujours les placer en fin de tableau.
Également dans Oracle et en SQL Server dans le cas d'une grande rangée, une ROW CHAINING peut se produire.
ROW CHANING consiste à diviser une ligne qui ne tient pas dans un seul bloc et à la répartir sur plusieurs blocs, reliés par une liste chaînée.
La lecture des colonnes de queue qui ne rentrent pas dans le premier bloc nécessitera de parcourir la liste chaînée, ce qui entraînera un temps de réponse supplémentaire. I/O fonctionnement.
Ver cette page pour illustrer ROW CHAINING sur Oracle :
C'est pourquoi vous devriez placer les colonnes que vous utilisez souvent au début du tableau, et les colonnes que vous n'utilisez pas souvent, ou les colonnes qui ont tendance à être NULL jusqu'au bout de la table.
Remarque importante :
Si vous aimez cette réponse et souhaitez voter pour elle, veuillez également voter pour @Andomar Réponse de la Commission .
Il a répondu à la même chose, mais semble avoir été descendu sans raison.
Vous dites donc que cela serait lent : select tinyTable.id, tblBIG.firstColumn, tblBIG.lastColumn from tinyTable inner join tblBIG on tinyTable.id = tblBIG.fkID Si les enregistrements de tblBIG font plus de 8KB (auquel cas il y aurait un chaînage de lignes) et la jointure serait synchrone... Mais ceci serait rapide : select tinyTable.id, tblBIG.firstColumn from tinyTable inner join tblBIG on tinyTable.id = tblBIG.fkID Puisque je n'utiliserais pas la colonne dans d'autres blocs, il n'y aurait pas besoin de parcourir la liste liée Ai-je bien compris ?
Lors d'une formation Oracle dans un emploi précédent, notre DBA a suggéré que mettre toutes les colonnes non nulles avant les colonnes nulles était avantageux... bien que je ne me souvienne pas des détails de la raison. Ou peut-être que celles qui étaient susceptibles d'être mises à jour devaient être placées à la fin ? (Cela évite peut-être d'avoir à déplacer la ligne si elle s'étend).
En général, cela ne devrait pas faire de différence. Comme vous le dites, les requêtes devraient toujours spécifier les colonnes elles-mêmes plutôt que de s'appuyer sur l'ordre de "select *". Je ne connais aucune BD qui permette de les modifier... enfin, je ne savais pas que MySQL le permettait jusqu'à ce que vous le mentionniez.
Il avait raison, Oracle n'écrit pas les colonnes NULL sur le disque, ce qui permet d'économiser quelques octets. Voir dba-oracle.com/oracle_tips_ault_nulls_values.htm
C'est le lien dont vous parliez ? C'est lié à la non-indexation des null dans les indices plutôt que dans l'ordre des colonnes.
Non, l'ordre des colonnes dans une table de base de données SQL n'est absolument pas pertinent, sauf à des fins d'affichage ou d'impression. Il n'y a aucun intérêt à réorganiser les colonnes - la plupart des systèmes ne proposent même pas de moyen de le faire (à part supprimer l'ancienne table et la recréer avec le nouvel ordre des colonnes).
Marc
EDIT : à partir de l'entrée Wikipedia sur les bases de données relationnelles, voici la partie pertinente qui, selon moi, montre clairement que l'ordre des colonnes doit être le suivant jamais être préoccupant :
Une relation est définie comme un ensemble de n-tuples. Tant en mathématiques que dans le modèle de base de données relationnelle, un ensemble est un non ordonné collection 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. E.F. Codd a initialement défini les tuples en utilisant cette définition mathématique. Plus tard, E.F. Codd a compris que l'utilisation de noms d'attributs au lieu d'un ordre 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.
J'ai vu de mes propres yeux que la différence de colonne avait un impact important, donc je ne peux pas croire que ce soit la bonne réponse. Même si le vote le place en premier. Hrm.
L'impact le plus important que j'ai vu était sur Sql Server 2000, où le déplacement d'une clé étrangère vers l'avant a accéléré certaines requêtes de 2 à 3 fois. Ces requêtes comportaient de grands balayages de tables (1M+ lignes) avec une condition sur la clé étrangère.
Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.
