Récupérer le dernier enregistrement de chaque groupe - MySQL
P粉464088437
2023-08-24 15:06:23
<p>Il existe un tableau <code>messages</code> qui contient les données comme indiqué ci-dessous :</p>
<pre class="brush:php;toolbar:false;">Id Nom Other_Columns
-----------------------
1 A A_données_1
2 A A_données_2
3A A_data_3
4 B B_data_1
5 B B_data_2
6 C C_data_1</pre>
<p>Si j'exécute une requête <code>select * dans le groupe de messages par nom</code>, j'obtiendrai le résultat suivant :</p>
<pre class="brush:php;toolbar:false;">1 A A_data_1
4 B B_data_1
6 C C_data_1</pre>
<p>Quelle requête renverrait les résultats suivants ? </p>
<pre class="brush:php;toolbar:false;">3 A A_data_3
5 B B_data_2
6 C C_data_1</pre>
<p>En d’autres termes, le dernier enregistrement de chaque groupe doit être renvoyé. </p>
<p>Actuellement, voici la requête que j'utilise : </p>
<pre class="brush:php;toolbar:false;">SELECT
*
DE (SÉLECTIONNER
*
DE messages
ORDRE PAR id DESC) AS x
GROUPE PAR nom</pre>
<p>Mais cela semble inefficace. Existe-t-il d'autres moyens d'obtenir le même résultat ? </p>
UPD : 31/03/2017, version 5.7.5 MySQL active le commutateur ONLY_FULL_GROUP_BY par défaut (les requêtes GROUP BY non déterministes sont donc désactivées). De plus, ils ont mis à jour l'implémentation GROUP BY et la solution peut ne pas fonctionner comme prévu même avec le commutateur désactivé. Il faut le vérifier.
La solution de Bill Karwin ci-dessus fonctionne bien lorsque le nombre d'éléments au sein des groupes est plutôt faible, mais les performances de la requête deviennent mauvaises lorsque les groupes sont plutôt grands, car la solution nécessite environ
n*n/2 + n/2
of onlyIS NULL
comparaisons.J'ai fait mes tests sur une table InnoDB de
18684446
rows with1182
groups. The table contains testresults for functional tests and has the(test_id, request_id)
as the primary key. Thus,test_id
is a group and I was searching for the lastrequest_id
for eachtest_id
.La solution de Bill tourne déjà depuis plusieurs heures sur mon Dell e4310 et je ne sais pas quand elle va se terminer même si elle fonctionne sur un indice de couverture (d'où
using index
dans EXPLAIN).J'ai quelques autres solutions basées sur la même idée :
(group_id, item_value)
pair is the last value within eachgroup_id
, that is the first for eachgroup_id
si on parcourt l'indice par ordre décroissant ;3 façons dont MySQL utilise les index est un excellent article pour vous aider à comprendre certains détails.
Solution 1
C'est incroyablement rapide, prenant environ 0,8 seconde sur mes plus de 18 millions de lignes :
Si vous souhaitez changer l'ordre en ASC, placez-le dans une sous-requête qui renvoie uniquement les identifiants et utilisez-le comme sous-requête pour rejoindre le reste des colonnes :
Cela prend environ 1,2 seconde pour mes données.
Solution 2
Voici une autre solution qui a pris environ 19 secondes pour ma montre :
Il renvoie également les tests par ordre décroissant. C'est beaucoup plus lent car il effectue une analyse complète de l'index, mais cela vous donne une idée de la façon de générer les N lignes maximales pour chaque groupe.
L'inconvénient de cette requête est que le cache des requêtes ne peut pas mettre en cache ses résultats.
MySQL 8.0 prend désormais en charge les fonctions de fenêtre, comme presque toutes les implémentations SQL populaires. En utilisant cette syntaxe standard, nous pouvons écrire jusqu'à n requêtes par groupe :
Cette méthode et d'autres méthodes pour trouver le nombre maximum de lignes groupées sont décrites dans le manuel MySQL.
Voici la réponse originale que j'ai écrite à cette question en 2009 :
J'ai écrit la solution comme ceci :
Concernant les performances, une solution peut être meilleure selon la nature des données. Par conséquent, vous devez tester les deux requêtes et utiliser celle avec les meilleures performances en fonction de votre base de données.
Par exemple, j'ai une copie du Dump des données d'août de StackOverflow. Je vais l'utiliser pour l'analyse comparative. Il y a 1 114 357 lignes dans la
Posts
table. Elle fonctionne sur MySQL 5.0.75 sur mon Macbook Pro 2,40 GHz. .J'écrirai une requête pour trouver les derniers messages pour un identifiant utilisateur donné (le mien).
Première utilisation de la technique montrée par @Eric avec le
GROUP BY
dans une sous-requête :Même l'
EXPLAIN
analyse prend plus de 16 secondes :Produisez maintenant le même résultat de requête en utilisant ma technique avec
LEFT JOIN
:L'analyse
EXPLAIN
montre que les deux tables sont capables d'utiliser leurs index :Voici le DDL pour ma
Posts
table :Note aux commentateurs : si vous souhaitez exécuter un autre benchmark en utilisant une version différente de MySQL, un ensemble de données différent ou une conception de table différente, n'hésitez pas à le faire vous-même. J'ai démontré la technique ci-dessus. Stack Overflow est là pour vous montrer comment effectuer un travail de développement logiciel, et non pour faire tout le travail à votre place.