2500 UPDATE sont lents

Version imprimable

08/05/2023, 22h46
encoremoi21258

2500 UPDATE sont lents

Bonjour,

Via une tâche cron PHP je fais une boucle et des UPDATE à l'intérieur:

Code:

UPDATE ListeComptes SET Date = :DATE, Fin = :DateFin WHERE NumeroCompte= :NUMERO;

NumeroCompte je l'ai mis en KEY et en UNIQUE
NumeroCompte + Date + Fin je l'ai mis en UNIQUE

Mais 2500 UPDATE pour 2500 comptes me prennent environ 2 minutes et je ne comprends pas comment ça peut être aussi lent.

Est-ce qu'il y a quelque chose à optimiser selon vous ?

Pour voir ce que ça donne j'ai fais un echo microtime(); à chaque fois que la boucle est utilisée, et j'ai limité le nombre de comptes mis à jour pour que ça va plus vite pour le test. Voici le résultat:
0.12579900 1683579201
0.42690000 1683579201
0.63944400 1683579201
0.98475200 1683579201
0.10391300 1683579202
0.24174100 1683579202
0.35886800 1683579202
0.50194800 1683579202
0.66137500 1683579202
0.77081000 1683579202
0.94490400 1683579202
0.18574500 1683579203
0.38349700 1683579203
0.64333800 1683579203
0.78562500 1683579203
0.91331400 1683579203
0.06466200 1683579204
0.19227400 1683579204
0.44172600 1683579204
0.69590600 1683579204
0.85088200 1683579204
0.08171300 1683579205
0.22471100 1683579205
0.43278200 1683579205
0.88040700 1683579205
0.14956700 1683579206
0.29942800 1683579206
0.45686700 1683579206
0.71519500 1683579206
0.86803400 1683579206
0.19141500 1683579207
0.27764500 1683579207
0.33453400 1683579207
0.39365300 1683579207
0.50289200 1683579207
0.63595600 1683579207
0.70582700 1683579207
0.78410700 1683579207
0.84185800 1683579207
0.20601700 1683579208
0.25203500 1683579208
0.31129700 1683579208
0.42359900 1683579208
0.46564700 1683579208
0.63245400 1683579208
0.76195000 1683579208
0.84086200 1683579208
0.87102600 1683579208
0.93442600 1683579208
0.02611100 1683579209
0.09678200 1683579209
0.14105300 1683579209
0.20216700 1683579209
0.26043800 1683579209
0.34057100 1683579209
0.39617800 1683579209
0.46743900 1683579209
0.55918000 1683579209
0.78681500 1683579209
0.92227200 1683579209
0.99306900 1683579209
0.14606900 1683579210
0.25561900 1683579210
0.33228600 1683579210
0.38852500 1683579210
0.42090600 1683579210
0.48129200 1683579210
0.53611600 1683579210
0.61625600 1683579210
0.68102600 1683579210
0.80141400 1683579210
0.91512600 1683579210
0.98905900 1683579210
0.06168000 1683579211
0.13428400 1683579211
0.23870000 1683579211
0.34194000 1683579211
0.43987900 1683579211
0.50348400 1683579211
0.62932400 1683579211
0.72802800 1683579211
0.80767500 1683579211
0.95871400 1683579211
0.05416600 1683579212
0.12825600 1683579212
0.50845100 1683579212
0.57360100 1683579212
0.62282400 1683579212

Merci d'avance
09/05/2023, 07h05
Séb.

Citation:

Est-ce qu'il y a quelque chose à optimiser selon vous ?

Tu dis 2500 UPDATE en 2 minutes => Donc une 20aine à la seconde
Or ton log en montre 3 à 12 à la seconde, ce qui dans tous les cas n'est pas bien rapide, en effet

Donne-nous la boucle d'UPDATE pour commencer ;)
09/05/2023, 12h53
binarygirl

Le problème peut venir de la boucle effectivement et de tout ce qu'il y a autour, mais aussi de la DB elle-même. Les index ne sont peut-être pas aussi performants pas qu'espéré.
Pensez à tirer un plan d'exécution sur une requête représentative. Vous pouvez publier le résultat ici.
09/05/2023, 14h30
CosmoKnacki
Effectivement, il faut bien que la préparation de la requête soit en dehors de la boucle qui ne doit contenir que l'envoi des variables. Aussi veille à ce que la préparation de la requête soit en mode réel et pas émulée (avec PDO, régler l'attribut PDO::ATTR_EMULATE_PREPARES à false, avec MySQLi c'est toujours le cas).

Citation:

NumeroCompte je l'ai mis en KEY et en UNIQUE
NumeroCompte + Date + Fin je l'ai mis en UNIQUE

C'est peut-être de là que vient le problème, ta deuxième contrainte ne sert pas à grand chose (vu que NumeroCompte est ta clef primaire et est donc unique, NumeroCompte + Date + Fin le sera fatalement).
Par contre, il est possible qu'en précisant cette seconde contrainte d'unicité, que tu forces le serveur MySQL, à chaque update, à scanner toute la table pour qu'elle soit vérifiée, d'où la lenteur. Celà dit ce n'est qu'une hypothèse, et je ne sais pas si MySQL est suffisament malin pour détecter ce genre de chose et zapper ce scan (d'une manière générale un programme informatique est bête). Donc à ta place j'enleverai cette deuxième contrainte d'unicité qui de toute manière ne sert à rien.

Citation:

NumeroCompte je l'ai mis en KEY et en UNIQUE

Attention à ne pas être ambiguë avec le terme KEY, car suivant le contexte il peut désigner deux choses avec MySQL:
- une clef primaire: c'est alors un raccourci de PRIMARY KEY qu'on peut utiliser lors de la creation d'une table:CREATE TABLE matable ( id int KEY );
- un index:
  Code:
  
  1 2 3 4 5 6 CREATE TABLE matable ( id int PRIMARY KEY, `name` VARCHAR(30), KEY (`name`) );
  est équivalent à
  Code:
  
  1 2 3 4 5 6 CREATE TABLE matable ( id int PRIMARY KEY, `name` VARCHAR(30), INDEX (`name`) );
09/05/2023, 20h51
encoremoi21258

Citation:

Envoyé par Séb.

Tu dis 2500 UPDATE en 2 minutes => Donc une 20aine à la seconde
Or ton log en montre 3 à 12 à la seconde, ce qui dans tous les cas n'est pas bien rapide, en effet

Donne-nous la boucle d'UPDATE pour commencer ;)

Effectivement mon estimation de 2500 UPDATE était totalement fausse c'est pourquoi j'avais édité mon message afin de rajouter le temps écoulé à chaque fois que la boucle s'exécute.

NumeroCompte est à présent UNIQUE et c'est tout. J'avais testé plein de choses en ne sachant plus quoi essayer, d'habitude je résous ce genre de petit soucis moi même depuis pas mal de temps.

ChatGPT m'a résumé ce que je ne savais pas:

Citation:

- KEY est utilisé pour créer une clé non unique pour une colonne ou un groupe de colonnes. Les clés non uniques permettent d'optimiser les requêtes en permettant de chercher des enregistrements plus rapidement.

- PRIMARY est utilisé pour définir une clé primaire pour une table. La clé primaire doit être unique pour chaque enregistrement de la table et elle est utilisée pour identifier de manière univoque chaque enregistrement.

- UNIQUE est utilisé pour créer une clé unique pour une colonne ou un groupe de colonnes. Les clés uniques empêchent la duplication des enregistrements dans une table.

Il est important de noter que chaque table ne peut avoir qu'une seule clé primaire et que toutes les autres clés doivent être des clés uniques ou des clés non uniques. En général, il est recommandé de définir une clé primaire pour chaque table afin d'optimiser les performances et d'assurer l'intégrité des données.

Donc NumeroCompte ne devrait pas plutôt être PRIMARY selon vous ?

Si j'ai bien compris, UNIQUE c'est quand une donnée ne doit être présente que dans une seule ligne dans la même colonne et que sa présence en double provoquerait des soucis donc on met ça pour générer une erreur MYSQL si ça arrive ?

KEY si je comprends bien, ça sert juste à accélérer les requêtes par exemple sur une colonne dont le nom figure après le mot clé WHERE.

J'ai finalement résolu le problème moi même, en modifiant les KEY et UNIQUE de ma table j'ai remarqué qu'elle est en InnoDB alors que j'utilise toujours MyISAM. Je ne sais pas pourquoi elle était en InnoDB ça n'a aucun sens je n'ai jamais eu besoin de changer. InnoDB sert pour les transactions et l'intégrité référentielle et vu que je ne sais pas ce que c'est, je n'en ai jamais eu besoin. A présent les 2500 requêtes se font en moins de 5 secondes. En faite c'était si long que j'avais un message d'erreur 504 (désolé j'avais oublié de le préciser) car ça mettait vraiment très longtemps, d'où la nécessité d'accélérer la chose.

Je vois que j'ai encore beaucoup à apprendre sur une chose qui semble pourtant si simple : effectuer des requêtes vers une base de données.
09/05/2023, 21h25
binarygirl

Il y aussi beaucoup de différences entre les tables MyISAM et Innodb, et ce ne sont pas les seuls types de tables mais néanmoins les plus courants.
C'est propre à Mysql/MariaDB mais il serait bon de lire la doc à ce sujet pour connaître les avantages et limitations de chaque type, et choisir en connaissance de cause.
Pour obtenir de bonnes performances, il est aussi important que la table soit bien structurée.
Accessoirement, avoir trop d'index peut être contre-productif.
La doc de Mysql est assez bien faite je pense à condition de lire l'anglais: https://dev.mysql.com/doc/refman/8.0/en/tutorial.html
09/05/2023, 21h49
CosmoKnacki

Citation:

Envoyé par encoremoi21258

InnoDB sert pour les transactions et l'intégrité référentielle et vu que je ne sais pas ce que c'est, je n'en ai jamais eu besoin.

Une petite recherche peut-être? Mieux encore, les tutoriels du site: comme celui-là par exemple.

Citation:

Je vois que j'ai encore beaucoup à apprendre sur une chose qui semble pourtant si simple

Je t'invite à te promener dans la section bases de données du forum, tu verras qu'il n'en est rien et qu'il est peuplé d'intervenants trés calés dans leur domaine.
09/05/2023, 21h50
Séb.

MyISAM est obsolète depuis des années, rien qu'en terme de fiabilité il est à la ramasse comparé à InnoDB.

Citation:

Donc NumeroCompte ne devrait pas plutôt être PRIMARY selon vous ?

Faut voir comment sont structurées ta base et ta table. Mais *a priori* puisque ça passait déjà en UNIQUE alors la colonne est candidate :)

Citation:

Si j'ai bien compris, UNIQUE c'est quand une donnée ne doit être présente que dans une seule ligne dans la même colonne et que sa présence en double provoquerait des soucis donc on met ça pour générer une erreur MYSQL si ça arrive ?

Oui, UNIQUE est une contrainte, et pour pouvoir être vérifiée rapidement cette contrainte entraîne la création d'un index.
Accessoirement la contrainte d'unicité peut porter sur plusieurs colonnes, auquel cas le groupe de colonnes doit former un ensemble unique.
10/05/2023, 09h01
escartefigue

Pour compléter ce qui précède une clef primaire (contrainte PRIMARY KEY) est par définition NOT NULL et UNIQUE, mais aussi, elle est irréductible.
À l'inverse une simple contrainte UNIQUE autorise les marqueurs "NULL" pour la colonne.
Les contraintes UNIQUE comme PRIMARY KEY génèrent toutes deux la création implicite d'un index.

À ce propos :

Citation:

Envoyé par encoremoi21258

InnoDB sert pour les transactions et l'intégrité référentielle et vu que je ne sais pas ce que c'est, je n'en ai jamais eu besoin.

Ces deux notions sont fondamentales.
La transaction c'est ce qui permet, avec la notion de verrous, de gérer les accès concurrents de sorte à ce que plusieurs tâches qui s'exécutent en parallèle ne provoquent pas des incohérences dans les données.
L'intégrité référentielle c'est ce qui permet de s'assurer de l'absence d'orphelins, par exemple d'éviter d'avoir des lignes de factures dont l'entête de facture n'existe pas, ou à l'inverse, de facture sans ses lignes détail.
Il faut impérativement connaître ces fondamentaux, au moins dans leurs grands principes !
Il existe des tutos sur ces sujets dans ce forum, prenez le temps de les lire ;)