J'ai une table qui contient plus d'un million d'enregistrements. Je veux trouver l'enregistrement maximum de chaque groupe. Voici mon sql:
+-------------+-------+-------+---------------+--------+---------+----------+--------------------------+ | select_type | table | type | possible_keys | key | rows | filtered | Extra | +-------------+-------+-------+---------------+--------+---------+----------+--------------------------+ | PRIMARY | t | ALL | NULL | NULL | 9926024 | 100.00 | Using where | | SUBQUERY | t | index | idx_1 | idex_1 | 9926024 | 1.00 | Using where; Using index | +-------------+-------+-------+---------------+--------+---------+----------+--------------------------+
La table déclare comme suit.
CREATE TABLE `t` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `a` varchar(32) NOT NULL COMMENT 'a', `b` tinyint(3) unsigned NOT NULL COMMENT 'b', `c` bigint(20) unsigned NOT NULL COMMENT 'c', `d` varchar(32) NOT NULL COMMENT 'd', PRIMARY KEY (`id`), KEY `idx_c_d` (`c`,`d`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='test table';
J'ai un index d'union sur c et d. Ainsi, la deuxième instruction ( SELECT max (id) AS id FROM t WHERE a = 'some' AND b = 0 GROUP BY c, d ) s'exécute en 200ms. Mais la déclaration totale coûte près de 6 secondes (le résultat contient 5000 lignes).
Voici les émissions expliquer (certaines colonnes sont omises).
SELECT *
FROM t
WHERE id IN (SELECT max(id) AS id
FROM t
WHERE a = 'some' AND b = 0
GROUP BY c, d);
4 Réponses :
vous pouvez essayer en utilisant une sous-requête corrélée et en créant un index dans la colonne c et d
SELECT t1.* FROM table_name t1
WHERE id = (SELECT max(id) AS id FROM table_name t2 where
t1.c=t2.c and t1.d=t2.d
) and t1.a = 'some' AND t1.b = 0
Group by c, d est manquant dans la sous-requête.
@mkRabbani vai ici pas besoin de grouper par :)
@Strawberry yap vous êtes là où il manquait merci
Comme OP veut MAX enregistrements de chaque groupe, je pense donc que GROUP BY est requis et que la condition doit être WHERE ID IN (.....). Votre requête renverra une seule ligne de toutes les lignes.
@mkRabbani nope vai il retournera pour chaque groupe le maximum 1, pas pour tous les célibataires. vous pouvez essayer d'utiliser un violon :)
Je lance votre SQL et je ne peux pas obtenir de résultat en 3 minutes. C'est beaucoup plus lent que le mien.
@weaver vous devez créer un index avant d'exécuter cette requête
@mkRabbani bpr na vai amara amrai :)
Encore très lent après avoir ajouté l'index.
Éviter la nécessité d'une sous-requête
SELECT t1.* FROM t t1 LEFT OUTER JOIN t t2 ON t1.c = t2.c AND t1.d = t2.d AND t1.id < t2.id AND t2.id IS NULL AND t2.a = 'some' AND t2.b = 0
Il y a quelques erreurs dans le sql. Et ne satisfait toujours pas ma demande. C'est très lent aussi.
@weaver, mettez en place le tableau déclare et je peux le tester. Mais devrait être plus rapide si vous avez des index utiles (index sur a, b, c, d et id dans cet ordre)
Je viens d'ajouter le tableau déclare. Je pense que peut-être dans est le moyen efficace pour ce problème.
@weaver - s'est rendu compte que j'avais fait une erreur sur les colonnes a et b. Cependant, IN avec une sous-requête n'est probablement pas efficace pour cela, car je forcerai une jointure sans clé entre 5000 enregistrements de votre sous-requête et 10 millions d'enregistrements de la table. L'ajout d'un index approprié aidera BEAUCOUP lorsque vous n'utilisez pas de sous-requête.
Je recommande d'utiliser une sous-requête corrélée:
SELECT t.*
FROM t
WHERE t.id IN (SELECT MAX(t2.id)
FROM t t2
WHERE t2.c = t.c AND t2.d = t.d AND
t2.a = 'some' AND t2.b = 0
);
Cela suppose que id est unique dans le tableau.
Pour les performances, vous voulez un index sur (c, d, a, b, id) .
Je veux trouver l'ID maximum de chaque groupe, j'ai peur que votre sql ne satisfasse pas la demande.
@weaver. . . Comment définissez-vous «groupe»? Cela suit la définition que vous impliquez dans votre question.
Toutes les manières différentes de "skin-a-cat", mais voici un peu différent ... Puisque vous recherchez IN, je placerais cette requête en première position. En outre, cela PEUT aider à utiliser le mot-clé spécifique au langage de MySQL "STRAIGHT_JOIN" indiquant à MySQL de faire dans l'ordre que vous avez listé. Encore une fois, cela PEUT aider
SELECT STRAIGHT_JOIN
T.* ( ... rest of query)
J'aurais également un index spécifiquement dans l'ordre de
(b, a, c, d, id )
Évidemment, conserver la clé d'ID primaire, et si vous utilisez STRAIGHT_JOIN , serait
SELECT
T.*
FROM
(SELECT max(id) AS id
FROM t
WHERE b = 0
AND a = 'some'
GROUP BY c, d) PQ
JOIN T
on PQ.ID = T.ID
MySQL est très susceptible de faire la sous-requête en premier, quel que soit l'ordre, et sans avoir besoin de dire STRAIGHT_JOIN . Et, oui, cet index à 5 colonnes est bénéfique pour la table dérivée. À moins que cela ne donne pas la «bonne» réponse, je prédis que c'est la «plus rapide».
Qu'est-ce que 1000W? ..
Watt. 1000W - c'est un énorme ampli de guitare!
Bien que le résultat ne contienne que 5 000 lignes La durée d'une requête ne dépend généralement pas de la quantité de résultats qu'elle obtient, mais de la quantité de données que vous devez consulter. Si vous avez 1000000 livres sans ordre (index) et que vous voulez en trouver un que vous n'avez pas, vous devrez vous tourner vers les 1000000 livres. Il vous faudra donc beaucoup de temps pour obtenir 0 résultat
@nacho Oui, je le sais. Je veux juste fournir plus d'informations sur cette question. Merci quand même.
La sous-requête utilisant IN comme ça n'utilise effectivement pas l'index lors de la vérification si un identifiant est l'un des 5000 enregistrements. D'où la cause probable de la lenteur.
Voir la balise que j'ai ajoutée.