Je suis raisonnablement nouveau à MySQL et j'essaie de sélectionner un ensemble distinct de lignes à l'aide de cette instruction: Cependant, l'instruction SELECT prend environ 10 minutes, alors quelque chose est clairement à pied. Un facteur significatif est que le tableau Les index semblent être configurés correctement; Toutes les jointures ci-dessus utilisent des colonnes indexées. Les tailles de table sont, à peu près: Le serveur dispose de 22 Go de mémoire, j'ai défini le pool de mémoire tampon InnoDB sur 8G et j'utilise MySQL 5.6. Quelqu'un peut-il voir une façon de faire courir plus vite? Ou en effet, du tout! est-il important que la table des points d'arrêt soit dans un schéma différent? EDIT:
Expliquez Sélectionner ... renvoie ceci: gtfsstop_times est énorme. (~ 250 millions d'enregistrements) 
< / p>
4 Réponses :
Avoir des enregistrements de 250m, je devrais tondre la table GTFSSTOP_Times sur une colonne. Ensuite, chaque table fronde peut être jointe dans une requête séparée pouvant fonctionner parallèlement dans des threads séparés, il vous suffira de fusionner les ensembles de résultats.
Pouvez-vous expliquer un peu plus dans votre réponse ce que vous entendez par «Sharding»? Merci.
Il désigne cette xeround.com/2011/11/mysql -sharding-vs-mysql-partitionnéin g et ici en.wikipedia.org / wiki / shard_ (base de données_architecture)
Hors de curiosité, comment cela fonctionnerait-il? Fusionnerait-il les ensembles de résultats non en soi être aussi fidèles que le travail original, car nous recherchons des articles distincts?
Non, car les requêtes fonctionnent sur des tables plus petites et leurs ensembles de résultats sont également plus petits.
On dirait que vous essayez de trouver une collection de points d'arrêt, sur la base de certains critères. Et, vous utilisez On dirait que ATCocode est une clé unique pour votre table des points d'arrêt. Est-ce correct? Si tel est le cas, essayez ceci: Cela fait quelques choses: il élimine une table (agence) que vous ne faites pas semblent avoir besoin. Il modifie la recherche sur agency_id à partir de (< code> rejoindre et Ceci devrait vous accélérer un peu. Mais cela doit être dit, une table de Gigarow quart est une grande table. Sélectionnez Distinct pour éviter les points d'arrêt en double. Est-ce correct? dans (a, b, c) à une recherche de plage, qui peut ou non aider. Et enfin, il déménage le traitement distinct à partir d'une situation où il doit gérer une tonne globale de données à une situation de sous-requête où il suffit de gérer les valeurs d'identifiant. rejoindre interne sont les mêmes. J'ai utilisé joindre pour rendre la requête un peu plus facile à lire.)
+1 pour penser à la SQL réelle et repérer ces optimisations. Je ne savais même pas que vous pouviez utiliser rejoindre (sélectionnez ...) comme syntaxe valide. Cela a coupé mes temps de requête en deux, merci. Je vais juste vérifier l'autre réponse proposée.
C'est en effet une optimisation significative sur ce que j'avais, merci pour la grande réponse.
Au fait, @Carlos P, laissez tout ce dont vous n'avez pas besoin de la liste des colonnes que vous Sélectionnez . Utilisez-vous réellement le nom et ATCocode dans votre application? Sinon, ne demandez pas-leur: ce jeu de résultats est un tiers d'un mégare environ, et il faut du temps pour mélanger autant de données de serveur au client.
Je les utilise éventuellement, oui - et il y a beaucoup plus de colonnes que je ne récupère pas!
+1 belle explication. Ma requête est passée de ne pas réagir et de laisser tomber la connexion pour renvoyer 1000 rangées (sous limite) en moins de 1,5. Acclamations
L'astuce consiste à réduire le nombre de lignes de Cependant, le groupe en gache rapidement toutes les lignes correspondantes ensemble, sans évaluer chacun. J'utilise normalement des jointures pour réduire rapidement le nombre de lignes dont la requête doit être évaluée, puis je regarde mon groupe. Dans ce cas, vous voulez remplacer distinct avec le regroupement. Essayez ceci; TransportData .stoppoints , non? Combien de lignes TransportData a-t-elle? Ensuite, SQL évalue la clause WHERE, puis elle évalue distincte. Comment cela fait-il distinct? En regardant chaque rangée à plusieurs reprises pour déterminer s'il y a d'autres rangées comme celle-ci. Cela prendrait pour toujours, non?
Merci, j'ai essayé cela mais c'est en fait a augmenté le temps de requête. J'ai essayé avec une requête beaucoup plus petite ( où agence.agency_id = 1 ) qui prend normalement 4-5sec et il a fallu environ 8 secondes. SP.Name , sp.longitude , sp.Latitude n'est pas indexé, pourrait-il être la raison? Je ne suis pas sûr de comprendre la logique de regrouper les quatre de ces colonnes; Est-ce nécessaire et, si oui, devrais-je les indexer tous? Je crains que le succès de la performance de l'indexation puisse l'emporter sur l'avantage.
À votre avis, c'est cette façon de le faire mieux que @ollie Jones Répondre, car sa suggestion semble exécuter beaucoup plus rapidement.
Puis-je aussi ajouter que j'ai fait une erreur dans ma question, la colonne distincte est SP.ATCOCODE - cela peut expliquer l'anomalie?
En fait, je pense que @ollie Jones a la meilleure réponse. Il réduit rapidement le nombre de lignes évaluées: "... Il déménage le traitement distinct d'une situation où il doit gérer une tonne globale de données à une situation de sous-requête où il suffit de gérer les valeurs d'identification" et que vous êtes correct, Vous devez seulement regrouper les colonnes qui doivent être distinctes; Dans ce cas, mon groupe a ralenti. J'ai utilisé avec succès le groupement, moins de colonnes, une augmentation des clauses, etc. pour réduire le temps d'exécution. Je travaillais sur ma réponse pendant que Ollie postait, sinon j'aurais reconnu la meilleure réponse :)
Il y a d'autres réponses précieuses à votre question et à votre mine est une addition. J'assume Si vous pouvez valider et vous assurer que les ID de l'agence dans le sp.atcocode et st.fk_atco_code sont des colonnes indexées dans leur table. où < / Code> La clause est valide, vous pouvez éliminer la jointure `vehicledata .gtfsagencys` dans les jointures, car vous n'agissez pas d'enregistrements de la table.
Merci, bien que je crois que @ollie Jones a déjà repéré cela? Apprécier la réponse cependant.
Comment cela fonctionne-t-il si vous omettez le qualificatif distinct ? Qu'est-ce que vous obtenez lorsque vous utilisez
expliquer sur la requête?Quel est le plan d'explication? Collez-le dans Pastebin ou un gist
Je ne sais pas comment je vais tester cela, puisque si j'étant omis le qualificatif, environ 250 millions de rangées seront retournées. Désolé si cela semble être absurde, je suis un peu nouveau pour tester / déboguer des requêtes.
Essayez d'ajouter un index sur (SP.Name, Sp.Longitude, Sp.Latitude, SP.ATCOCODE); Voir Groupe par optimisation dans le manuel pour Certaines raisons pour lesquelles (groupe par conseil d'optimisation est souvent applicable à Optimisation distincte .) Mais oui, voyons une explication aussi, s'il vous plaît.
Quel système de fichiers est utilisé? Quelles sont les raids utilisés? Quels sont les lecteurs utilisés? par exemple. SSDS?
@Carlosp veuillez poster le
Expliquer de votre relevé.Ont édité la question à inclure l'explication. @Sergey Je préférerais ne pas entrer dans le système de fichiers à moins que cela ne soit probablement le problème; Ce n'est rien particulièrement spécial cependant; HDD standard et sans tableau RAID.
Combien y a-t-il de la requête? (environ)
En regardant le plan d'explication, il revient moins de 10 000
Il retourne environ 350 000 rangées
Aussi, avec cette quantité de données, l'IO peut être un problème réel, même pour la lecture d'index, donc je supposerais que le Sharding est définitivement un moyen d'aller ici