Comment améliorer les performances de sélection sur une table MyISAM de 15 millions de lignes convertie en InnoDB

Question

MySQL version 8.0.32-0ubuntu0.20.04.2

J'essaie d'améliorer les performances de SELECT, et non de contourner l'exécution de SELECT.

CREATE TABLE big_table (
pk INT AUTO_INCREMENT PRIMARY KEY,
field1 VARCHAR(255),
field2 VARCHAR(255),
field3 mediumtext,
field4 BIGINT,
KEY idx_field4 (field4)
) ENGINE=MyISAM CHARSET=utf8mb3;

Insérez 15 millions de lignes.

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (0.57 sec)

Changer la table big_table ENGINE=INNODB;

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (10.23 sec)

**Définissez innodb_buffer_pool_size=8G (à partir de 128 Mo) (redémarrez MySQL)**

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (1 min 18.67 sec)

Answer 1

Il s'avère que l'exécution de SELECT COUNT(PK) ou SELECT COUNT(*) sur une table MyISAM sans clause Where est un cas très particulier, car elle ne compte pas chaque ligne et est donc très rapide. La même requête sur InnoDB est lente car elle compte littéralement chaque ligne.

Cependant, une fois la clause Where entrée en jeu sur les champs indexés, InnoDB semble surpasser MyIsam. On dirait que cela représente 99 % des cas d'utilisation...