Augmenter la vitesse de vos requêtes SQL en changeant le moteur de stockage

Il y a quelque jours et comme à mon habitude je me suis retrouvé face à un problème.

Une fonction complexe de mon application prenait un temps considérable. Pour le contexte c’est une fonction qui exécute un très grand nombre de requête à la base de donnée. Des SELECT à tout va sur plusieurs tables ne contenant pas plus d’une centaine de lignes chacune, mais surtout des SELECT, DELETE, INSERT et UPDATE des plusieurs milliers de lignes sur une seule et même table. Pour vous dire la fonction mettais plus de 50 secondes à se terminer sur la prod.

Après plusieurs essaies d’optimisation comme la modification de la conf mysql sur la prod, ou un gros refact sur ma fonction en utilisant au maximum le cache système de mon application pour diminuer le nombre de requête SQL. Rien n’y fait.

On me donne comme piste de me renseigner les moteurs de stockage de ma base de donnée MySQL. Pour l’instant l’intégralité de ma base est en InnoDB.

Après lecture de ce fabuleux article qui compare ces deux moteurs. J’ai écrit une migration pour changer le moteur de stockage de ma table  sur laquelle l’application effectue grand nombre de requête. J’ai donc passé le moteur de cette table de InnoDB à MyISAM, car oui, une même base de donnée peut avoir plusieurs moteurs de stockage en fonction de nos besoins.

La migration en question.

class ConvertVotreTableIntoMyisam extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement('ALTER TABLE ' . 'votreTable' . ' ENGINE = MyISAM');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement('ALTER TABLE ' . 'votreTable' . ' ENGINE = InnoDB');
    }
}

Attention, comme indiqué dans l’article précédemment cité, les table avec l’engine MyISAM ne doivent lié à aucune autre table part des foreign_key. Pensez à effectué une migration avant pour les supprimer si vous le pouvez.

90% ! C’est le gain de perf que j’ai obtenu en production sur ma fonction en passant ma table de InnoDB en MyISAM!

Notes à savoir: L’engine par default des tables de votre base de donnée dépends de votre version de MySQL, avant la 5.5.5, c’est MyISAM, après c’est InnoDB.

Dans Laravel vous pouvez changer ça dans config/database.php, pour mysql il est de base à ‘engine’ => null, donc vos tables seront soit en MyISAM ou en InnoDB en fonction de votre version MySQL. Vous pouvez fixer le moteur par défaut ‘engine’ => ‘InnoDB’, ou ‘engine’ => ‘MyISAM’,