Il semble que toutes les questions concernant ce sujet soient très spécifiques et que j'apprécie des exemples spécifiques, je suis intéressé par les bases de l'optimisation SQL. Je suis très à l'aise de travailler dans SQL et je suis en arrière-plan dans un logiciel de matériel / bas niveau.
Ce que je veux, c'est que les outils sont à la fois des logiciels tangibles et une méthode pour examiner les bases de données MySQL que je regarde régulièrement et savoir quelle est la différence entre les ordres des instructions de rejoindre et où des déclarations.
Je veux savoir pourquoi un index aide, comme, exactement pourquoi. Je veux connaître spécifiquement ce qui se passe différemment, et je veux savoir comment je peux réellement regarder ce qui se passe. Je n'ai pas besoin d'un outil qui va briser chaque étape de ma SQL, je veux juste pouvoir fouiller et si quelqu'un ne peut pas me dire quelle colonne à indexer, je pourrai sortir une feuille de papier et dans une période de temps, peut venir avec les réponses.
Les bases de données sont compliquées, mais elles ne sont pas si compliquées, et il doit y avoir un excellent matériau pour apprendre les bases afin de savoir comment trouver les réponses aux problèmes d'optimisation que vous rencontrez, même si vous pouviez chasser exactement Réponse sur un forum.
Veuillez recommander une certaine lecture qui est concise, intuitive et n'a pas peur de descendre dans les écrous et les boulons de bas niveau. Je préfère les ressources gratuites en ligne, mais si une recommandation de livre démolit la tête de clou, il frappe que je envisagerais de l'accepter.
5 Réponses :
Disons que vous recherchez un ami dans une autre ville. Une solution serait d'aller de la porte à la porte et de demander si c'est la maison que vous recherchez. Une autre façon est de regarder la carte.
L'index est la carte à une table. Il peut dire au moteur DB exactement où la chose que vous recherchez est. Ainsi, vous indexez chaque colonne que vous penserez que vous devrez rechercher et laisser les colonnes que vous recherchez simplement des données à partir de et ne jamais rechercher.
bonne lecture technique sur Indices et < un href = "http://dev.mysql.com/doc/refman/4.1/fr/ordfman/4.1/f/order-by-optimization.html" rel = "nOfollow noreferrer"> sur commander par Optimisation . Et si vous voulez voir ce qui se passe exactement, vous voulez le Expliquez Déclaration.
En outre, le journal lent MySQL vaut la peine d'être observé. dev.mysql.com/doc/refman/5.0/ EN / SLOW-QUERY-LOG.HTML
Je suis intéressé en particulier dans la manière dont les indices affecteront les jointures, j'utilise beaucoup de joints et ne comprennent pas vraiment comment ils travaillent à un niveau bas. Par exemple, cela importe-t-il si vous avez deux colonnes indexées qui peuvent être très importantes qui se joignent à l'autre? Comment l'espace pour les jointures est-il alloué et traversé? Et si elles sont toutes deux indexées, qu'est-ce que cela n'est pas indexé?
Fondamentalement, tout le chapitre 7.2 du manuel MySQL est intéressant. Si une colonne n'est pas indexée, vous devez au maximum N comparaisons à trouver quelque chose. Si c'est le cas, vous avez besoin chez la plupart des comparaisons de journal (n). La longueur de la donnée est définitivement un facteur, mais l'indice est plus important. Cependant, je trouve que je ne joint presque jamais à des champs non entiers. Ma politique est, si elle a une chance non triviale de se répéter, elle devrait avoir une table et une clé primaire. Et "Et si" des questions comme la vôtre aient la meilleure réponse en construisant le modèle et en exécutant expliquer sur des requêtes d'échantillons.
Pour développer un peu, une jointure est normalement une double recherche; Tout ce qui se rapporte à la recherche concerne doublement de jointures; Ainsi, tout ce que vous adhérez, il est préférable d'être indexé.
Je n'ai pas encore eu la chance de regarder à travers la documentation que vous avez fournie, je vais mettre à jour quand je le ferai
Ne pensez pas à optimiser les bases de données. Pensez à optimiser les requêtes.
Généralement, vous optimisez un cas au détriment des autres. Vous devez juste décider quels cas vous êtes intéressé.
"Je suis intéressé en particulier dans la manière dont les indices vont affecter les jointures"
À titre d'exemple, je vais prendre le cas d'Equijoin (sélectionnez de A, B où A.x = B.Y).
S'il n'y a pas de index du tout (ce qui est possible en théorie, mais je ne pense pas en SQL), alors fondamentalement le seul moyen de calculer la jointure est de prendre toute la table A et de la partitionner sur x, prenez la totalité de la table. Y et partitionez-le sur Y, puis faites correspondre les partitions et enfin pour chaque paire de partitions correspondantes calculer les lignes de résultat. C'est coûteux (ou même impossible en raison des restrictions de mémoire) pour toutes les plus petites tables.
Même histoire s'il existe des index sur A et / B, mais aucun d'entre eux n'a x resp. y comme son premier attribut.
S'il existe un index sur x, mais pas sur Y (ou inversement), une autre possibilité peut s'ouvrir: balayer la table B, pour chaque valeur de sélection de ligne Y, recherchez cette valeur dans l'index et récupérez les lignes correspondantes calculer la jointure. Notez que cela ne vous gagne toujours pas beaucoup si aucune autre restriction supplémentaire ne s'applique (et z = ...) - sauf dans le cas où il n'y a que peu de correspondances entre les valeurs X et Y.
Si des index commandés (index basés sur des hachages ne sont pas commandés) existent sur X et Y, une troisième possibilité s'ouvre: faites une analyse correspondante sur les index eux-mêmes (les index eux-mêmes sont susceptibles d'être plus petites que les tables elles-mêmes. Ainsi, la numérisation de l'index lui-même prendra une heure plus courte) et pour les valeurs X / Y correspondantes, calculez la jointure des lignes correspondantes.
C'est la ligne de base. Les variations se produisent pour des jointures sur x> y etc.
Je ne sais pas sur les outils MySQL, mais dans MS SQLSERVER, vous avez un outil qui affiche toutes les opérations qu'une requête prendrait et quelle quantité de temps de traitement de la requête entière prendrait.
L'utilisation de cet outil m'a aidé à comprendre comment les requêtes sont optimisées par l'optimiseur de requête bien plus que ce que tout livre pourrait aider, car ce que l'optimiseur est souvent pas facile à comprendre. En modifiant la requête et éventuellement la base de données de soulignement, je pouvais voir comment chaque changement a affecté le plan de requête. Il y a certains points clés dans les requêtes d'écriture, mais cela semble que vous avez déjà une idée de ceux-ci afin d'optimiser dans votre cas, c'est bien plus que toutes les règles générales. Après quelques années de DB Development, j'ai examiné quelques livres spécifiquement destinés à l'optimisation de la base de données sur le serveur SQL et trouvés très peu d'informations utiles.
Rapide Googling est proposé avec ceci: http://www.mysql.com/ Produits / Enterprise / Query.html qui ressemble à un outil similaire.
Ceci était bien sûr sur un niveau de requête, les optimisations de niveau de la base de données sont à nouveau une bouilloire différente de poisson, mais vous examinez des paramètres tels que la manière dont votre base de données est divisée sur les disques durs, etc. Au moins dans SQLServer, vous pouvez sélectionner Pour diviser des tables sur différents plaques de disque dur et même de disque, ce qui peut avoir un grand effet car les têtes de conduite et d'entraînement peuvent fonctionner en parallèle. Une autre est la manière dont vous pouvez construire vos requêtes afin que la base de données puisse les exécuter dans plusieurs threads et processeurs en parallèle, mais les deux problèmes dépendent à nouveau du moteur de base de données et même de la version que vous utilisez.
Vous devez rechercher tous les endroits où condition et chaque jointure ... à condition. Les deux fonctionnent de la même manière.
Supposons que nous écrivions d'une manière ou d'une autre, la SGBD doit trouver l'enregistrement ou les enregistrements avec CustomerID = 37. S'il n'y a pas d'index, le seul moyen de le faire est de lire tous les enregistrements du tableau comparant le CustomerID à 37. Même lorsqu'il en trouve un, il n'a aucun moyen de savoir qu'il n'y en a qu'un, de sorte qu'il doit continuer à chercher d'autres. Si vous créez un index sur CustomerID, le SGBD a des moyens de rechercher très rapidement l'index. Ce n'est pas une recherche séquentielle, mais, en fonction de la base de données, une recherche binaire ou une autre méthode efficace. Exactement comment cela importe, accepter qu'il est beaucoup plus rapide que séquentiel. L'index prend ensuite directement à l'enregistrement ou aux enregistrements appropriés. En outre, si vous spécifiez que l'Index est "unique", la base de données sait que la base de données ne peut en être une fois que cela ne perd pas de temps à la recherche d'une seconde. (Et le DBMS vous empêchera d'ajouter une seconde.) considère maintenant cette requête: Nous avons deux conditions. Si vous avez un index sur un seul de ces champs, le SGBD utilisera cet index pour trouver un sous-ensemble des enregistrements, puis effectuez des recherches séquentielles. Par exemple, si vous avez un indice sur l'état, le SGBD trouvera rapidement le premier enregistrement pour NY, puis recherchez de manière séquentielle à la recherche de Ville = 'Albany' et arrêtez de regarder quand il atteint le dernier enregistrement pour NY. < p> Si vous avez un index qui inclut les deux champs, c'est-à-dire "Créer un index sur le client (état, ville)", alors le SGBD peut immédiatement zoomer sur les bons enregistrements. Si vous avez deux index distincts, Un sur chaque champ, le SGBD aura diverses règles qu'il applique pour décider de l'index à utiliser. Encore une fois, comment cela est fait dépend de la SGBD particulière que vous utilisez, mais essentiellement, il essaie de conserver des statistiques sur le nombre total d'enregistrements, le nombre de valeurs différentes et la distribution des valeurs. Ensuite, il recherchera ces enregistrements de manière séquentielle pour ceux qui satisfont à l'autre condition. Dans ce cas, les SGBD observeraient probablement qu'il y a beaucoup plus de villes que d'états, alors en utilisant l'indice de la ville, il peut rapidement zoomer sur les enregistrements «Albany». Ensuite, il les recherchera séquentiellement, en vérifiant l'état de chacun contre «NY». Si vous avez des enregistrements pour Albany, California, ceux-ci seront ignorés. Chaque join nécessite une sorte de recherche. dire que nous écrivons Le DBMS doit décider de la table à lire en premier, sélectionnez les enregistrements appropriés de là, puis recherchez les enregistrements correspondants dans l'autre table. Si vous aviez un index sur la transaction. TransactionDate et client.Customéride, le meilleur plan serait probablement de trouver toutes les transactions à cette date, puis pour chacun de ceux qui trouvent le client avec le client correspondant, puis vérifiez que le client a le bon type. Si vous n'avez pas d'index sur client.customéride, la SGBD pourrait trouver rapidement la transaction, mais pour chaque transaction, il faudrait effectuer une recherche de manière séquentielle dans la table des clients à la recherche d'une clientèle correspondante. (Cela serait probablement très lent.) Supposons plutôt que les seuls index que vous avez sont sur transaction.Customérid et client.Type. Ensuite, le SGBD utiliserait probablement un plan complètement différent. Il scinderait probablement la table du client pour tous les clients avec le type correct, puis pour chacune de ces transactions de toutes les transactions pour ce client et les recherchez de manière séquentielle pour la bonne date. La clé la plus importante pour l'optimisation est Pour déterminer ce que les indices vont vraiment aider et créer ces index. Des indices supplémentaires non utilisés constituent un fardeau de la base de données car il prendra du travail pour les maintenir, et s'ils ne sont jamais utilisés, ce sont des efforts gaspillés. Vous pouvez dire quels index utiliseront les indices pour une requête donnée. avec la commande d'explication. J'utilise tout le temps pour déterminer si mes requêtes sont bien optimisées ou si je devrais créer des index supplémentaires. (Lisez la documentation sur cette commande pour une explication de sa sortie.) CAVEAT: N'oubliez pas que j'ai dit que le SGBD conserve des statistiques sur le nombre d'enregistrements et le nombre de valeurs différentes et ainsi de suite dans chaque table . Expliquez peut vous donner un plan complètement différent aujourd'hui que ce qu'il a donné hier si les données ont changé. Par exemple, si vous avez une requête qui se joint à deux tables et qu'une de ces tables est très petite, tandis que l'autre est grande, elle sera enrichée vers la lecture de la petite table, puis de trouver des enregistrements correspondants dans la grande table. L'ajout d'enregistrements à une table peut modifier ce qui est plus grand et dirige ainsi le SGBD pour modifier son plan. Ainsi, vous devriez essayer de faire expliquer une base de données avec des données réalistes. En cours d'exécution sur une base de données de test avec 5 enregistrements de chaque table, est de beaucoup moins de valeur que de courir contre une base de données en direct. Eh bien, il y a beaucoup plus que cela pourrait être dit, mais je ne veux pas écrire un livre ici.
Wow, c'est beaucoup d'informations, merci, j'ai appris quelques choses de lire ceci que je peux immédiatement utiliser