Récupérer le dernier enregistrement de chaque groupe - MySQL

Question

Il existe un tableau messages qui contient les données comme indiqué ci-dessous :

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

Si j'exécute une requête select * dans le groupe de messages par nom, j'obtiendrai le résultat suivant :

1 A A_data_1
4 B B_data_1
6 C C_data_1

Quelle requête renverrait les résultats suivants ?

3 A A_data_3
5 B B_data_2
6 C C_data_1

En d’autres termes, le dernier enregistrement de chaque groupe doit être renvoyé.

Actuellement, voici la requête que j'utilise :

SELECT
  *
DE (SÉLECTIONNER
  *
DE messages
ORDRE PAR id DESC) AS x
GROUPE PAR nom

Mais cela semble inefficace. Existe-t-il d'autres moyens d'obtenir le même résultat ?

Answer 1

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 only IS NULL comparaisons.

J'ai fait mes tests sur une table InnoDB de 18684446 rows with 1182 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 last request_id for each test_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 :

• si l'indice sous-jacent est l'indice BTREE (ce qui est généralement le cas), le plus grand (group_id, item_value) pair is the last value within each group_id, that is the first for each group_id si on parcourt l'indice par ordre décroissant ;
• Si on lit la valeur couverte par l'index, les valeurs sont lues dans l'ordre de l'index
;
• Chaque index contient implicitement les colonnes de clé primaire ajoutées à cet index (c'est-à-dire que la clé primaire est dans un index de couverture). Dans la solution ci-dessous j'opère directement sur la clé primaire, dans votre cas il vous suffit d'ajouter la colonne de clé primaire au résultat.
• Dans de nombreux cas, il est beaucoup moins coûteux de collecter les ID de ligne requis dans l'ordre requis dans une sous-requête et de concaténer les résultats de la sous-requête aux ID. Puisque pour chaque ligne du résultat de la sous-requête, MySQL devra effectuer une récupération basée sur la clé primaire, la sous-requête sera placée en premier dans la jointure et les lignes seront affichées dans l'ordre de l'identifiant dans la sous-requête (si nous omettez le ORDER BY explicite pour la jointure)

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 :

SELECT test_id, MAX(request_id) AS request_id
FROM testresults
GROUP BY test_id DESC;

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 :

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id) AS request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

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 :

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

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.

Answer 2

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 :

WITH ranked_messages AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn
  FROM messages AS m
)
SELECT * FROM ranked_messages WHERE rn = 1;

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 :

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

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 :

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

Même l'EXPLAINanalyse prend plus de 16 secondes :

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)

Produisez maintenant le même résultat de requête en utilisant ma technique avec LEFT JOIN:

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

L'analyse EXPLAIN montre que les deux tables sont capables d'utiliser leurs index :

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)

---

Voici le DDL pour ma Posts table :

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;

---

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.