Comment calculer efficacement des moyennes à partir d'une grande table ?

Question

J'ai une table appelée ratings avec les champs suivants:

+-----------+------------+------+-----+---------+----------------+
| Champ     | Type       | Null | Clé | Défaut  | Extra          |
+-----------+------------+------+-----+---------+----------------+
| rating_id | bigint(20) | NON  | PRI | NULL    | auto_increment |
| user_id   | int(11)    | NON  | MUL | NULL    |                |
| movie_id  | int(11)    | NON  |     | NULL    |                |
| rating    | float      | NON  |     | NULL    |                |
+-----------+------------+------+-----+---------+----------------+

Index sur cette table:

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Nom_clé  | Seq_in_index | Nom_colonne | Collation | Cardinalité | Sub_part | Emballé | Null | Type_index | Comment | Commentaire_index |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ratings |          0 | PRIMARY  |            1 | rating_id   | A         |      100076 |     NULL | NULL   |      | BTREE      |         |               |
| ratings |          0 | user_id  |            1 | user_id     | A         |         564 |     NULL | NULL   |      | BTREE      |         |               |
| ratings |          0 | user_id  |            2 | movie_id    | A         |      100092 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

J'ai une autre table appelée movie_average_ratings qui a les champs suivants:

+----------------+---------+------+-----+---------+-------+
| Champ          | Type    | Null | Clé | Défaut  | Extra |
+----------------+---------+------+-----+---------+-------+
| movie_id       | int(11) | NON  | PRI | NULL    |       |
| average_rating | float   | NON  |     | NULL    |       |
+----------------+---------+------+-----+---------+-------+

Comme il est évident à ce stade, je veux calculer la note moyenne des films à partir de la table ratings et mettre à jour la table movie_average_ratings. J'ai essayé la requête SQL suivante.

UPDATE movie_average_ratings
SET average_rating = (SELECT AVG(rating)
                            FROM ratings
                            WHERE ratings.movie_id = movie_average_ratings.movie_id);

Actuellement, il y a environ 10 000 enregistrements de films et 100 000 enregistrements de notes et j'obtiens une erreur Lock wait timeout exceeded; try restarting transaction. Le nombre d'enregistrements peut augmenter considérablement donc je ne pense pas qu'augmenter le temps d'attente soit une bonne solution.

Alors, comment puis-je écrire une requête 'évolutive' pour y parvenir? Est-ce que parcourir les enregistrements de la table movie_average_ratings et calculer les moyennes individuellement est la solution la plus efficace?

Answer 1

Sans explication, il est difficile d'être clair sur ce qui vous retient. Il n'est pas non plus clair que vous obtiendrez une amélioration des performances en stockant ces données agrégées dans une table dénormalisée - si la requête pour calculer les notes s'exécute en 0,04 secondes, il est peu probable que la requête sur votre table dénormalisée soit beaucoup plus rapide.

En général, je recommande de dénormaliser seulement si vous savez que vous avez un problème de performance.

Mais ce n'est pas la question.

Je ferais ce qui suit:

supprimer de movie_average_ratings;

insérer dans movie_average_ratings
Sélectionner movie_ID, avg(rating) 
de notes 
regrouper par movie_id;

Answer 2

J'ai trouvé quelque chose dans un autre post:

Ce qui se passe, c'est qu'un autre thread détient un verrou d'enregistrement sur un enregistrement (vous mettez à jour chaque enregistrement de la table!) depuis trop longtemps, et votre thread expiré.

Cela signifie que certains de vos enregistrements sont verrouillés et vous pouvez les déverrouiller de force dans la console:

1) Entrez MySQL mysql -u votre_utilisateur -p

2) Voyons la liste des tables verrouillées mysql> show open tables where in_use>0;

3) Voyons la liste des processus actuels, l'un d'eux verrouille votre(es) table(s) mysql> show processlist;

4) Tuez un de ces processus mysql> kill put_process_id_here;

Answer 3

Vous pourriez redessiner la table des notes moyennes de films comme suit :

movie_id (int)
sum_of_ratings (int)
num_of_ratings (int)

Ensuite, si une nouvelle note est ajoutée, vous pouvez l'ajouter à movie_average_ratings et calculer la moyenne si nécessaire