Optimiser un ALTER
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)
|
1 |
mysql5.5> ALTER TABLE lien |
|
1 |
-> ADD KEY `IdxIdMembre` (`IdMembre`), |
|
1 |
-> ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100)), |
|
1 |
-> ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`); |
|
1 |
Query OK, 0 rows affected (<strong>2 min 26.43 sec</strong>) |
|
1 |
Records: 0 Duplicates: 0 Warnings: 0 |
Ce qui fait un total de 146.43 sec
Suppression de index (en une seule commande)
|
1 |
mysql5.5> ALTER TABLE lien |
|
1 |
-> DROP KEY `IdxIdMembre`, |
|
1 |
-> DROP KEY `IdxCreationTitre`, |
|
1 |
-> DROP KEY `IdxPaysLangueCreation`; |
|
1 |
Query OK, 0 rows affected (<span style="color: #355e00;"><strong>1.78 sec</strong></span>) |
|
1 |
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)
|
1 |
mysql5.5> ALTER TABLE lien ADD KEY `IdxIdMembre` (`IdMembre`); |
|
1 |
Query OK, 0 rows affected (<strong>26.34 sec</strong>) |
|
1 |
Records: 0 Duplicates: 0 Warnings: 0 |
|
1 |
mysql5.5> ALTER TABLE lien ADD KEY `IdxCreationTitre` (`Creation`,`Titre`(100)); |
|
1 |
Query OK, 0 rows affected (<strong>57.58 sec</strong>) |
|
1 |
Records: 0 Duplicates: 0 Warnings: 0 |
|
1 |
mysql5.5> ALTER TABLE lien ADD KEY `IdxPaysLangueCreation` (`Pays`,`Langue`,`Creation`); |
|
1 |
Query OK, 0 rows affected (<strong>1 min 7.24 sec</strong>) |
|
1 |
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)
|
1 |
mysql5.5> ALTER TABLE lien Drop index `IdxIdMembre`; |
|
1 |
Query OK, 0 rows affected (<strong>0.29 sec</strong>) |
|
1 |
Records: 0 Duplicates: 0 Warnings: 0 |
|
1 |
mysql5.5> ALTER TABLE lien DROP KEY `IdxCreationTitre`; |
|
1 |
Query OK, 0 rows affected (<strong>1.06 sec</strong>) |
|
1 |
Records: 0 Duplicates: 0 Warnings: 0 |
|
1 |
mysql5.5> ALTER TABLE lien DROP KEY `IdxPaysLangueCreation`; |
|
1 |
Query OK, 0 rows affected (<strong>0.42 sec</strong>) |
|
1 |
Records: 0 Duplicates: 0 Warnings: 0 |
Total: 1.77 sec
CQFD
Divers
|
1 |
CREATE TABLE `lien` ( |
|
1 |
`id` int(11) unsigned NOT NULL AUTO_INCREMENT, |
|
1 |
... |
|
1 |
PRIMARY KEY (`id`) |
|
1 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci |
|
1 |
show table status like 'lien'\G |
|
1 |
*************** 1. row *************** |
|
1 |
Name: lien |
|
1 |
Engine: InnoDB |
|
1 |
Version: 10 |
|
1 |
Row_format: Compact |
|
1 |
Rows: 8427138 |
|
1 |
Avg_row_length: 609 |
|
1 |
Data_length: 5139070976 |
|
1 |
Max_data_length: 0 |
|
1 |
Index_length: 0 |
|
1 |
Data_free: 4194304 |
|
1 |
Auto_increment: 8901321 |
|
1 |
... |
|
1 |
Collation: utf8_swedish_ci |
|
1 |
Checksum: NULL |
|
1 |
Create_options: |
|
1 |
Comment: |
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
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.
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 🙂
++