Optimiser un ALTER

mai 30, 2012

Lors d’une discussion à la cafèt, la question suivante fut posée : « Faire un ALTER TABLE avec plusieurs instructions est il plus rapide qu’un ALTER TABLE par instruction » ?

Les 2 protagonistes n’étant pas d’accord entre eux, c’est tout naturellement que je fus invité à donner mon avis.

La réponse me semble assez intuitive, mais comme des chiffres valent mieux qu’un longs discours…

 

 

Le contexte :

  • MySQL Community Server 5.5.22
  • Une table InnoDB de 8 539 238 enregistrements
  • 17 colonnes avec INT, CHAR, VARCHAR, TEXT, DATETIME, …

 

 

Ajout de 3 index (en une seule commande)

 

mysql5.5> ALTER TABLE lien
-> ADD KEY `IdxIdMembre` (`IdMembre`),
-> ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100)),
-> ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`);
Query OK, 0 rows affected (2 min 26.43 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

Ce qui fait un total de 146.43 sec

 

 

Suppression de index (en une seule commande)

 

mysql5.5> ALTER TABLE lien
-> DROP KEY `IdxIdMembre`,
-> DROP KEY `IdxCreationTitre`,
-> DROP KEY `IdxPaysLangueCreation`;
Query OK, 0 rows affected (1.78 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

Au passage on peut remarquer que depuis MySQL 5.5, supprimer des index secondaires (non clé primaire) ne coûte rien.

 

 

 

Ajout de 3 index (en 3 commandes)

 

mysql5.5> ALTER TABLE lien ADD KEY `IdxIdMembre` (`IdMembre`);
Query OK, 0 rows affected (26.34 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

mysql5.5> ALTER TABLE lien ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100));
Query OK, 0 rows affected (57.58 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

mysql5.5> ALTER TABLE lien ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`);
Query OK, 0 rows affected (1 min 7.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

Ce qui fait un total de 151.16 sec (4 % plus lent)

 

Bien entendu, plus le volume de donnée à déplacer est important, plus l’écart entre les 2 façons de faire risque d’être important.

 

Suppression de index (en 3 commandes)

 

mysql5.5> ALTER TABLE lien Drop index `IdxIdMembre`;
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

mysql5.5> ALTER TABLE lien DROP KEY `IdxCreationTitre`;
Query OK, 0 rows affected (1.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

mysql5.5> ALTER TABLE lien DROP KEY `IdxPaysLangueCreation`;
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0

 

Total: 1.77 sec

 

CQFD

 

 

Divers

 

CREATE TABLE `lien` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

 

 

show table status like 'lien'\G
*************** 1. row ***************
Name: lien
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 8427138
Avg_row_length: 609
Data_length: 5139070976
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 8901321
...
Collation: utf8_swedish_ci
Checksum: NULL
Create_options:
Comment:

 

Filed under:
Astuce, bench, optimisation by freshdaz

2 Responses to “Optimiser un ALTER”

  1. NicoD.
    mai 31st, 2012 at 13:01

    La différence est nette rien qu’en se contentant d’ajouter des indexes. Si je ne m’abuse, elle aurait probablement encore été plus flagrante en ajoutant des colonnes.

  2. freshdaz
    mai 31st, 2012 at 14:49

    Bonjour,
    oui tout à fait !

    ALTER TABLE sbtest ADD COLUMN d char(120) COLLATE utf8_swedish_ci NOT NULL DEFAULT  »;
    Query OK, 10000000 rows affected (1 min 50.76 sec)
    Records: 10000000 Duplicates: 0 Warnings: 0

    ALTER TABLE sbtest ADD COLUMN e char(120) COLLATE utf8_swedish_ci NOT NULL DEFAULT  »;
    Query OK, 10000000 rows affected (2 min 18.31 sec)
    Records: 10000000 Duplicates: 0 Warnings: 0

    ALTER TABLE sbtest ADD COLUMN f char(120) COLLATE utf8_swedish_ci NOT NULL DEFAULT  »;
    Query OK, 10000000 rows affected (2 min 48.80 sec)
    Records: 10000000 Duplicates: 0 Warnings: 0

    ALTER TABLE sbtest DROP COLUMN d; ALTER TABLE sbtest DROP COLUMN e; ALTER TABLE sbtest DROP COLUMN f;
    Query OK, 10000000 rows affected (2 min 23.38 sec)
    Records: 10000000 Duplicates: 0 Warnings: 0

    Query OK, 10000000 rows affected (1 min 59.28 sec)
    Records: 10000000 Duplicates: 0 Warnings: 0

    Query OK, 10000000 rows affected (1 min 31.99 sec)
    Records: 10000000 Duplicates: 0 Warnings: 0
    ***************************************************************
    ALTER TABLE sbtest ADD COLUMN d char(120) COLLATE utf8_swedish_ci NOT NULL DEFAULT  », ADD COLUMN e char(120) COLLATE utf8_swedish_ci NOT NULL DEFAULT  », ADD COLUMN f char(120) COLLATE utf8_swedish_ci NOT NULL DEFAULT  »;
    Query OK, 10000000 rows affected (2 min 40.77 sec)

    ALTER TABLE sbtest DROP COLUMN d, DROP COLUMN e, DROP COLUMN f;
    Query OK, 10000000 rows affected (1 min 36.89 sec)
    Records: 10000000 Duplicates: 0 Warnings: 0

    Il n’y a pas photo :)

    ++

Leave a Reply