Mettre à jour des millions de lignes dans un SGBDR
L'objectif de cet article est d'expliquer comment gérer des mises à jour sur des tables de base de données extrêmement volumineuses.
Prérequis
Les modifications ne sont pas atomiques, ce qui signifie que vous n'avez pas besoin de vous assurer que toutes les modifications sont terminées, ou qu'aucune ne l'est.
Quand faut-il l'utiliser ?
- 1.000 lignes : à garder en tête
- 1.000.000 lignes : on commence à solliciter le système de verrouillage, ce qui introduit de la latence et consomme de l'espace disque libre ou des fichiers temporaires. Cela commence à coûter de l'argent.
- >100.000.000 : vous allez rencontrer des problèmes techniques.
- >1.000.000.000 : il est bien trop tard
Pourquoi s'en soucier ?
Les ressources (temps, espace disque, mémoire, $ € £...) ne sont jamais illimitées, à moins que vous ne souhaitiez que votre changement se termine avant votre retraite, sans empêcher les utilisateurs d'utiliser l'application, jusqu'à ce que le changement soit terminé.
Comment faire
Effectuez une requête qui récupère les N premières lignes à modifier, modifiez-les, puis relancez-la tant qu'il y a des résultats.
| Base de données | Solution SQL | Requête d'update avec pagination | Requête de suppression avec pagination |
|---|---|---|---|
| MySQL | sql SELECT * FROM table ORDER BY column LIMIT 10 OFFSET 20; | sql UPDATE table_to_update JOIN ( SELECT id FROM table_to_update ORDER BY some_column LIMIT 10 OFFSET 20 ) AS subquery ON table_to_update.id = subquery.id SET some_column = 'new_value'; | sql DELETE FROM table_to_update WHERE id IN (SELECT id FROM table_to_update ORDER BY some_column LIMIT 10 OFFSET 20); |
| PostgreSQL | sql SELECT * FROM table ORDER BY column LIMIT 10 OFFSET 20; | sql UPDATE table_to_update SET some_column = 'new_value' FROM ( SELECT id FROM table_to_update ORDER BY some_column LIMIT 10 OFFSET 20 ) AS subquery WHERE table_to_update.id = subquery.id; | sql DELETE FROM table_to_update WHERE id IN (SELECT id FROM table_to_update ORDER BY some_column LIMIT 10 OFFSET 20); |
| SQL Server | sql SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY column) AS row_num FROM table) AS numbered_rows WHERE row_num BETWEEN 21 AND 30; | sql UPDATE table_to_update SET some_column = 'new_value' FROM ( SELECT id, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num FROM table_to_update ) AS numbered_rows WHERE numbered_rows.row_num BETWEEN 21 AND 30 AND table_to_update.id = numbered_rows.id; | sql DELETE FROM table_to_update WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num FROM table_to_update) AS numbered_rows WHERE row_num BETWEEN 21 AND 30); |
| Oracle | sql SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY column) AS row_num FROM table) WHERE row_num BETWEEN 21 AND 30; | sql UPDATE ( SELECT id, some_column FROM table_to_update ORDER BY some_column OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY ) SET some_column = 'new_value'; | sql DELETE FROM table_to_update WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (ORDER BY some_column) AS row_num FROM table_to_update) WHERE row_num BETWEEN 21 AND 30); |
| DB2 | sql SELECT * FROM table ORDER BY column FETCH FIRST 10 ROWS ONLY OFFSET 20; | sql UPDATE ( SELECT id, some_column FROM table_to_update ORDER BY some_column FETCH FIRST 10 ROWS ONLY OFFSET 20 ) SET some_column = 'new_value'; | sql DELETE FROM table_to_update WHERE id IN (SELECT id FROM table_to_update ORDER BY some_column FETCH FIRST 10 ROWS ONLY OFFSET 20); |
Boucle
Exécuter une requête tant qu'il y a des résultats implique de boucler, donc une procédure stockée ou tout autre langage de programmation disposant de boucles.
- assurez-vous que votre instruction de modification exclut les lignes déjà modifiées
- ne bouclez pas indéfiniment, limitez le nombre maximal d'itérations. D'autant plus si vous êtes sur un système Cloud où le CPU se paie !
Optimiser
Il n'y a pas de règle sur la taille de la page. Je dirais qu'entre 1.000 et 10.000 serait un bon premier choix. Gardez cette valeur en dehors du SQL et ajustez-la en fonction de la durée de votre requête.