Comment mettre à jour des milliers de lignes dans une base de données mysql ?

Question

J'essaie de mettre à jour 100.000 lignes dans ma base de données, le code suivant devrait le faire mais j'obtiens toujours une erreur :

Erreur : Les commandes ne sont pas synchronisées ; vous ne pouvez pas exécuter cette commande maintenant.

Comme il s'agit d'une mise à jour, je n'ai pas besoin du résultat et je veux juste m'en débarrasser. La variable $count est utilisée pour que ma base de données reçoive des morceaux de mises à jour au lieu d'une seule grosse mise à jour. (Une seule grosse mise à jour ne fonctionne pas à cause de certaines limitations de la base de données).

J'ai essayé beaucoup de choses différentes comme mysqli_free_result et ainsi de suite... rien n'a fonctionné.

    global $mysqliObject;

    $count = 0;    
    $statement = "";

    foreach ($songsArray as $song) {

        $id = $song->getId();
        $treepath = $song->getTreepath();

        $statement = $statement."UPDATE songs SET treepath='".$treepath."' WHERE id=".$id."; ";
        $count++;

        if ($count > 10000){

            $result = mysqli_multi_query($mysqliObject, $statement);

            if(!$result) {
                 die('<br/><br/>Error1: ' . mysqli_error($mysqliObject));    
            }

            $count = 0;

            $statement = ""; 
        }

    }

Answer 1

Utilisation d'une requête préparée volonté réduire la charge CPU du processus mysqld comme le suggèrent DaveRandom et StevenVI. Cependant, dans ce cas, je doute que l'utilisation de requêtes préparées ait un impact matériel sur votre temps d'exécution. Le problème que vous rencontrez est que vous essayez de mettre à jour 100K lignes dans le fichier songs et cela va impliquer beaucoup d'E/S physiques sur votre sous-système de disque physique. Ce sont ces délais physiques (disons ~10 mSec par PIO) qui domineront les durées d'exécution. Des facteurs tels que le contenu de chaque ligne, le nombre d'index que vous utilisez sur la table (en particulier ceux qui impliquent un chemin d'accès) se mêleront à ce mélange.

Les coûts réels de l'unité centrale pour la préparation d'une simple déclaration telle que

UPDATE songs SET treepath="some treepath" WHERE id=12345;

sera perdue dans ce délai d'E/S physique global, et l'importance relative de ce délai dépendra matériellement de la nature du sous-système physique dans lequel vous stockez vos données : un simple disque SATA ; un disque SSD ; certains NAS avec de grandes mémoires cache et un support SSD ...

Vous devez repenser votre stratégie globale, en particulier si vous utilisez également l'option songs en même temps qu'une ressource pour les demandes interactives par le biais d'une interface web. La mise à jour de 100 000 lignes va prendre un certain temps - moins si vous mettez à jour 100 000 lignes sur 100 000 dans l'ordre de stockage, car cela sera plus conforme à l'organisation du MYD et la mise en cache en écriture sera meilleure ; plus si vous mettez à jour 100 000 lignes dans un ordre aléatoire sur 1 million de lignes, où le nombre de PIO sera beaucoup plus élevé.

Dans ce cas, les performances globales de votre D/B vont se dégrader fortement.

• Souhaitez-vous minimiser l'impact sur l'utilisation parallèle de votre base de données ou essayez-vous simplement d'effectuer cette opération par lots avec d'autres services hors ligne ?

• Votre objectif est-il de minimiser le temps total écoulé ou de le garder raisonnablement court sous réserve d'une certaine contrainte d'impact global, ou même simplement de terminer sans mourir ?

Je pense que vous avez deux approches raisonnables : (i) faire cela comme une activité de lot appropriée avec le D/B hors ligne pour d'autres services. Dans ce cas, vous voudrez probablement verrouiller la table et mettre entre parenthèses les mises à jour avec ALTER TABLE ... DÉSACTIVER/ACTIVER LES CLÉS. (ii) procéder à une mise à jour au compte-gouttes avec des ensembles de mises à jour beaucoup plus petits et un délai entre chaque ensemble pour permettre à la base de données de se décharger sur le disque.

Quoi qu'il en soit, je diminuerais la taille des lots. Le multi_query optimise essentiellement les têtes RPC impliquées dans l'appel à mysqld hors processus. Un lot de 10, par exemple, réduit cela de 90%. Vous avez des rendements décroissants après cela - en particulier si les mises à jour sont intensives en E/S physiques.

Answer 2

Essayez ce code en utilisant des instructions préparées :

// Create a prepared statement
$query = "
  UPDATE `songs`
  SET `treepath` = ?
  WHERE `id` = ?
";
$stmt = $GLOBALS['mysqliObject']->prepare($query); // Global variables = bad

// Loop over the array
foreach ($songsArray as $key => $song) {

  // Get data about this song
  $id = $song->getId();
  $treepath = $song->getTreepath();

  // Bind data to the statement
  $stmt->bind_param('si', $treepath, $id);

  // Execute the statement
  $stmt->execute();

  // Check for errors
  if ($stmt->errno) {
    echo '<br/><br/>Error: Key ' . $key . ': ' . $stmt->error;
    break;
  } else if ($stmt->affected_rows < 1) {
    echo '<br/><br/>Warning: No rows affected by object at key ' . $key;
  }

  // Reset the statment
  $stmt->reset();

}

// We're done, close the statement
$stmt->close();

Answer 3

Je ferais quelque chose comme ça :

  $link = mysqli_connect('host');
  if ( $stmt = mysqli_prepare($link, "UPDATE songs SET treepath=? WHERE id=?") ) {

    foreach ($songsArray as $song) {

        $id = $song->getId();
        $treepath = $song->getTreepath();

        mysqli_stmt_bind_param($stmt, 's', $treepath); // Assuming it's a string...
        mysqli_stmt_bind_param($stmt, 'i', $id);
        mysqli_stmt_execute($stmt);
    }
    mysqli_stmt_close($stmt);
  }
  mysqli_close($link);

Ou bien, bien sûr, vous pouvez utiliser des mysql_query normaux, mais dans le cadre d'une transaction.

Answer 4

J'ai trouvé un autre moyen...

Comme il ne s'agit pas d'un serveur de production, le moyen le plus rapide de mettre à jour 100 000 lignes est de les supprimer toutes et d'en insérer 100 000 à partir de zéro avec les nouvelles valeurs calculées. Cela semble un peu étrange de tout supprimer et de tout insérer au lieu de mettre à jour, mais c'est beaucoup plus rapide.

Avant : heures Maintenant : secondes !

Answer 5

Je suggère de verrouiller la table et de désactiver les clés avant d'exécuter des mises à jour multiples. Cela éviterait que le moteur de la base de données ne s'arrête (du moins dans mon cas de mise à jour de 300 000 lignes).

LOCK TABLES `TBL_RAW_DATA` WRITE;
/*!40000 ALTER TABLE `TBL_RAW_DATA` DISABLE KEYS */;

UPDATE TBL_RAW_DATA SET CREATION_DATE = ADDTIME(CREATION_DATE,'01:00:00') WHERE ID_DATA >= 1359711;

/*!40000 ALTER TABLE `TBL_RAW_DATA` ENABLE KEYS */;
UNLOCK TABLES;