Comparatfi des Bases de Données : Création d'Index en Concurrency avec Mises à Jour en Lot
Aperçu des Tests
Ce test vise à comparer la capacité d'Oracle, MySQL et PostgreSQL à créer des index sur des tables en cours de mise à jour par lots, en analysant les mécanismes de DDL concurrent de chaque système.
Environnement de Test
| Base de Données |
Version |
Système d'Exploitation |
| Oracle |
AI Database 26ai Enterprise Edition 23.26.1.2.0 |
CentOS Stream 9 |
| MySQL |
8.0.45 (InnoDB) |
Ubuntu 24.04.4 LTS |
| PostgreSQL |
16.13 |
Ubuntu 24.04.4 LTS |
Scénarios de Test
| Session |
Opération |
Description |
| Session 1 |
Mises à jour par lots |
Transaction non validée, reste active |
| Session 2 |
Création d'index |
Tentative de création pendant les mises à jour de la session 1 |
I. Résultats des Tests Oracle
1.1 Test : Mode ONLINE
Instructions Exécutées
CREATE INDEX idx_test_valeur ON test_concurrent(valeur) ONLINE;
Chronologie du Test
| Heure |
Événement |
| 09:22:55 |
Session 1 : Début des mises à jour par lots |
| 09:22:56 |
Session 1 : 50 000 lignes mises à jour, transaction non validée |
| 09:23:04 |
Session 2 : Début de la création d'index |
| 09:23:26 |
Session 2 : Création d'index réussie ! (durée : 22,46 secondes) |
| 09:23:27 |
Session 1 : Transaction validée |
Résultats du Test
NOM_INDEX STATUT TYPE_INDEX
─────────────────────────────────────────
SYS_C0030176 VALIDE NORMAL (index primaire)
IDX_TEST_VALEUR VALIDE NORMAL (index nouvellement créé)
Conclusion : ✅ Création d'index en mode ONLINE réussie !
1.2 Test : Mode Standard (sans ONLINE)
Instructions Exécutées
CREATE INDEX idx_test_valeur2 ON test_concurrent2(valeur);
Résultats du Test
ORA-00054 : Échec de l'acquisition d'un verrou (Type : "TM", Nom : "DML", Description :
"Synchronise les accès à un objet") car il est actuellement détenu par une autre session.
La ressource verrouillée peut être identifiée par 136643 ("Table")
Conclusion : ❌ Création d'index en mode standard échouée, erreur ORA-00054 !
1.3 Comparaison des Mécanismes de Verrouillage Oracle
┌─────────────────────────────────────────────────────────────────────┐
│ CREATE INDEX standard │
├─────────────────────────────────────────────────────────────────────┤
│ Session 1 : UPDATE → Détient le verrou TM (Row Exclusive) │
│ Session 2 : CREATE INDEX → Nécessite le verrou TM (Share/Exclusive)│
│ ↓ │
│ ❌ Conflit de verrous → Erreur ORA-00054 │
└─────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────┐
│ CREATE INDEX ... ONLINE │
├─────────────────────────────────────────────────────────────────────┤
│ Session 1 : UPDATE → Exécution normale │
│ Session 2 : CREATE INDEX ONLINE → Utilise des verrous de bas niveau │
│ ↓ │
│ ✅ Exécution parallèle, création d'index réussie │
└─────────────────────────────────────────────────────────────────────┘
II. Résultats des Tests MySQL
2.1 Environnement de Test
innodb_version: 8.0.45
innodb_online_alter_log_max_size: 134217728 (128MB)
2.2 Test : Création d'index en mode par défaut
Instructions Exécutées
CREATE INDEX idx_test_valeur ON test_concurrent(valeur);
Chronologie du Test
| Heure |
Événement |
| 09:34:22 |
Session 1 : Début des mises à jour par lots de 25 000 lignes |
| 09:34:27 |
Session 2 : Début de la création d'index |
| 09:35:21 |
Session 2 : Création d'index réussie ! (durée : 54 secondes) |
| - |
Session 1 : Transaction validée |
Résultats du Test
Table Nom_Cle Colonne Type_Index
────────────────────────────────────────────────────────────
test_concurrent PRIMAIRE id BTREE
test_concurrent idx_test_valeur valeur BTREE
Conclusion : ✅ MySQL supporte nativement la création d'index en concurrency (Online DDL) !
2.3 Mécanisme Online DDL de MySQL
Le stockage InnoDB de MySQL 8.0 supporte nativement le Online DDL pour la création d'index :
| Étape |
Description |
| 1. Initialisation |
Acquisition brève d'un verrou exclusif (instantané) |
| 2. Scan des données |
Autorisation des opérations DML concurrentes |
| 3. Fusion des changements |
Intégration des journaux DML dans le nouvel index |
| 4. Finalisation |
Acquisition brève d'un verrou exclusif pour terminer la construction |
Paramètres clés :
innodb_online_alter_log_max_size : Contrôle la taille des journaux DDL pendant Online DDL
III. Résultats des Tests PostgreSQL
3.1 Test : Création d'index en mode standard
Instructions Exécutées
CREATE INDEX idx_test_valeur ON test_concurrent(valeur);
Chronologie du Test
| Heure |
Événement |
| 09:36:31 |
Session 1 : Début des mises à jour par lots de 25 000 lignes |
| 09:36:36 |
Session 2 : Début de la création d'index |
| 09:37:31 |
Session 2 : Création d'index réussie ! (durée : 55 secondes) |
| - |
Session 1 : Transaction validée |
Résultats du Test
nom_index definition_index
─────────────────────────────────────────────────────────────────────
test_concurrent_pkey CREATE UNIQUE INDEX ... ON test_concurrent (id)
idx_test_valeur CREATE INDEX ... ON test_concurrent (valeur)
Conclusion : ✅ Création d'index en mode standard réussie !
3.2 Test : Mode CONCURRENTLY
Instructions Exécutées
CREATE INDEX CONCURRENTLY idx_pg_valeur ON test_concurrent_pg(valeur);
Chronologie du Test
| Heure |
Événement |
| 09:38:42 |
Session 1 : Début des mises à jour par lots de 25 000 lignes |
| 09:38:45 |
Session 2 : Début de la création d'index (CONCURRENTLY) |
| 09:39:25 |
Session 2 : Création d'index réussie ! (durée : 40 secondes) |
| - |
Session 1 : Transaction validée |
Conclusion : ✅ Création d'index en mode CONCURRENTLY réussie !
3.3 Comparaison des Modes de Création d'Index PostgreSQL
| Mode |
Type de Verrou |
Blocage DML |
Description |
CREATE INDEX |
Verrou SHARE |
Blocage partiel |
Attend la fin des transactions existantes, mais ne bloque pas les nouvelles transactions |
CREATE INDEX CONCURRENTLY |
Verrou minimal |
Aucun blocage |
Exécution entièrement concurrente, mais plus longue |
IV. Synthèse Comparative des Trois Bases de Données
4.1 Comparaison des Capacités de Création d'Index en Concurrency
| Base de Données |
Comportement par défaut |
Syntaxe Spéciale |
Résultat |
| Oracle |
Erreur ORA-00054 |
ONLINE |
Nécessite la spécification explicite d'ONLINE |
| MySQL |
Support Online DDL |
Par défaut ONLINE |
Pas de syntaxe spéciale nécessaire |
| PostgreSQL |
Support (attente des transactions existantes) |
CONCURRENTLY |
Par défaut partiellement concurrent, CONCURRENTLY entièrement concurrent |
4.2 Comparaison des Mécanismes de Verrouillage
| Base de Données |
Verrou Mode Standard |
Verrou Mode Concurrecny |
| Oracle |
Verrou TM de table (exclusif) |
Verrous de bas niveau, sans blocage DML |
| MySQL |
Verrou exclusif bref + DML autorisé |
Par défaut Online |
| PostgreSQL |
Verrou SHARE (attente des transactions) |
Verrou minimal, entièrement concurrent |
4.3 Schéma des Comportements Différents
┌─────────────────────────────────────────────────────────────────────────────┐
│ Comparatif des Comportements de Création d'Index │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ Oracle (mode standard) Oracle (ONLINE) │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ UPDATE détient le verrou │ UPDATE en cours │ │
│ │ CREATE INDEX │ │ CREATE INDEX │ │
│ │ ↓ │ │ ↓ │ │
│ │ ❌ ORA-00054 │ │ ✅ Création │ │
│ └─────────────────┘ │ réussie │ │
│ └─────────────────┘ │
│ │
│ MySQL (par défaut) PostgreSQL (par défaut) │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ UPDATE en cours │ │ UPDATE en cours │ │
│ │ CREATE INDEX │ │ CREATE INDEX │ │
│ │ ↓ │ │ ↓ │ │
│ │ ✅ Création │ │ ✅ Création │ │
│ │ (Online DDL) │ │ (après fin des │ │
│ └─────────────────┘ │ transactions) │ │
│ └─────────────────┘ │
│ │
│ PostgreSQL (CONCURRENTLY) │
│ ┌─────────────────┐ │
│ │ UPDATE en cours │ │
│ │ CREATE INDEX │ │
│ │ CONCURRENTLY │ │
│ │ ↓ │ │
│ │ ✅ Pleinement │ │
│ │ concurrent │ │
│ └─────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
V. Recommandations de Bonnes Pratiques
5.1 Oracle
| Scénario |
Syntaxe Recommandée |
| Environnement de production |
CREATE INDEX idx ON table(col) ONLINE; |
| Grandes tables |
CREATE INDEX idx ON table(col) ONLINE PARALLEL 4; |
| Fenêtre de maintenance |
CREATE INDEX idx ON table(col); (plus rapide) |
5.2 MySQL
| Scénario |
Syntaxe Recommandée |
| Environnement de production |
CREATE INDEX idx ON table(col); (Online par défaut) |
| Grandes tables |
Ajuster innodb_online_alter_log_max_size |
| Surveillance |
SHOW PROCESSLIST; ou Performance Schema |
5.3 PostgreSQL
| Scénario |
Syntaxe Recommandée |
| Environnement de production |
CREATE INDEX CONCURRENTLY idx ON table(col); |
| Fenêtre de maintenance |
CREATE INDEX idx ON table(col); (plus rapide) |
| Précautions |
CONCURRENTLY ne peut pas être exécuté dans un bloc de transaction |
VI. Conclusion des Tests
6.1 Découvertes Principales
- Oracle : L'utilisation explicite du mot-clé
ONLINE est nécessaire pour créer des index en concurrency, sinon une erreur ORA-00054 due au conflit de verrouillages se produit.
- MySQL : Le stockage InnoDB supporte nativement le Online DDL, permettant les opérations DML concurrentes lors de la création d'index sans syntaxe spéciale.
- PostgreSQL : En mode par défaut, l'index est créé après la fin des transactions existantes, l'utilisation de
CONCURRENTLY permet une exécution entièrement concurrente.
6.2 Recommandations en Production
| Base de Données |
Recommandation en Production |
| Oracle |
OBLIGATOIREMENT ONLINE pour éviter le blocage des opérations métier |
| MySQL |
Comportement sécurisé par défaut, surveiller la taille des journaux Online DDL |
| PostgreSQL |
Utiliser CONCURRENTLY pour éviter le verrouillage de table prolongé |
6.3 Alertes sur les Risques
| Risque |
Description |
| Transactions longues |
Peut entraîner un attente ou un échec de création d'index |
| Consommation de ressources |
La création d'index en concurrency augmente la charge système |
| Espace insuffisant |
La création d'index nécessite un espace de stockage supplémentaire |
Annexe : Scripts de Test
A.1 Script de Test Oracle
-- Session 1 : Mises à jour par lots
BEGIN
FOR i IN 1..50000 LOOP
UPDATE test_concurrent SET valeur = valeur + 1 WHERE id = i;
END LOOP;
END;
/
-- Ne pas valider
-- Session 2 : Création d'index (mode ONLINE)
CREATE INDEX idx_test_valeur ON test_concurrent(valeur) ONLINE;
A.2 Script de Test MySQL
-- Session 1 : Mises à jour par lots
START TRANSACTION;
UPDATE test_concurrent SET valeur = valeur + 1 WHERE id <= 25000;
-- Ne pas valider
-- Session 2 : Création d'index (mode ONLINE par défaut)
CREATE INDEX idx_test_valeur ON test_concurrent(valeur);
A.3 Script de Test PostgreSQL
-- Session 1 : Mises à jour par lots
BEGIN;
UPDATE test_concurrent SET valeur = valeur + 1 WHERE id <= 25000;
-- Ne pas valider
-- Session 2 : Création d'index (mode CONCURRENTLY)
CREATE INDEX CONCURRENTLY idx_test_valeur ON test_concurrent(valeur);