Requête MYSQL très lente pour une table de 2,5 millions de lignes
j'ai vraiment du mal à obtenir un temps de requête en baisse, il est actuellement à la requête de 2,5 millions de lignes et il prend plus de 20 secondes
voici la requête
SELECT play_date AS date, COUNT(DISTINCT(email)) AS count
FROM log
WHERE play_date BETWEEN '2009-02-23' AND '2020-01-01'
AND type = 'play'
GROUP BY play_date
ORDER BY play_date desc;
`id` int(11) NOT NULL auto_increment,
`instance` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`type` enum('play','claim','friend','email') NOT NULL,
`result` enum('win','win-small','lose','none') NOT NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`play_date` date NOT NULL,
`email_refer` varchar(255) NOT NULL,
`remote_addr` varchar(15) NOT NULL,
PRIMARY KEY (`id`),
KEY `email` (`email`),
KEY `result` (`result`),
KEY `timestamp` (`timestamp`),
KEY `email_refer` (`email_refer`),
KEY `type_2` (`type`,`timestamp`),
KEY `type_4` (`type`,`play_date`),
KEY `type_result` (`type`,`play_date`,`result`)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE log ref type_2,type_4,type_result type_4 1 const 270404 Using where
la requête utilise l'index type_4.
est-ce que quelqu'un sait comment je pourrais accélérer cette requête?
Merci Tom
8 réponses
c'est déjà relativement bon. La performance de l'évier, c'est que la requête a comparer 270404 varchars pour l'égalité pour l' COUNT(DISTINCT(email)), ce qui signifie que 270404 lignes à lire.
vous pourriez être en mesure de faire le compte plus rapidement en créant un indice de couverture. Cela signifie que les lignes n'ont pas besoin d'être lu parce que toutes les informations requises sont présentes dans l'index lui-même.
KEY `type_4` (`type`,`play_date`, `email`)
je serais surpris si ce ne serait pas accélérer les choses un peu.
Votre indexation est probablement aussi bon que vous pouvez obtenir. Vous avez un index composé sur les 2 colonnes de votre where la clause et le explain vous avez posté indique qu'il est utilisé. Malheureusement, il y a 270,404 lignes qui correspondent aux critères de votre where l'article et ils doivent tous être considérés. En outre, vous ne retournez pas les lignes inutiles dans votre select liste.
mon conseil serait d'agréger les données quotidiennement (ou toutes les heures ou ce qui a du sens) et de mettre en cache résultat. De cette façon, vous pouvez accéder aux données légèrement périmées instantanément. J'espère que c'est acceptable pour vos fins.
essayez un index sur play_date, tapez (identique à type_4, champs inversés) et voyez si cela aide
il y a 4 types possibles, et je suppose qu'il y a une centaine de dates possibles. Si la requête utilise le type, l'index play_date, il dit fondamentalement (pas 100% précis, mais idée générale).
(A) Find all the Play records (about 25% of the file) (B) Now within that subset, find all of the requested dates
En inversant l'index, l'approche est
> (A) Find all the dates within range > (Maybe 1-2% of file) (B) Now find all > PLAY types within that smaller portion > of the file
J'espère que cela aidera
extraire le courrier électronique pour séparer la table devrait être un bon boost de performance puisque compter les champs varchar distincts devrait prendre un certain temps. Autre que cela - l'index correct est utilisé et la requête elle-même est aussi optimisé qu'il pourrait être (sauf pour l'email, bien sûr).
COUNT(DISTINCT(email)) une partie est le morceau qui vous tue. Si vous avez seulement vraiment besoin des premiers 2000 résultats de 270.404, peut-être qu'il serait utile de faire le compte d'email seulement pour les résultats au lieu de pour l'ensemble.
SELECT date, COUNT(DISTINCT(email)) AS count
FROM log,
(
SELECT play_date AS date
FROM log
WHERE play_date BETWEEN '2009-02-23' AND '2020-01-01'
AND type = 'play'
ORDER BY play_date desc
LIMIT 2000
) AS shortlist
WHERE shortlist.id = log.id
GROUP BY date
à Long terme, je recommande la construction d'un tableau récapitulatif avec une clé primaire de play_date et le nombre d'e-mails distincts.
selon la façon dont up to date vous avez besoin qu'il soit - soit permettre qu'il soit mis à jour quotidiennement (par play_date) ou vivre via un déclencheur sur la table de journal.
il y a de bonnes chances qu'un scan de table soit plus rapide qu'un accès aléatoire à plus de 200 000 lignes:
SELECT ... FROM log IGNORE INDEX (type_2,type_4,type_result) ...
aussi, pour les grandes requêtes groupées vous pouvez voir de meilleures performances en forçant un tri de fichier plutôt qu'un groupe basé sur un hashtable (puisque si cela s'avère nécessaire plus que tmp_table_size ou max_heap_table_size rendement s'effondre):
SELECT SQL_BIG_RESULT ...