Avant de pouvoir analyser les requêtes lentes, vous devez les trouver.
MySQL possède un log intégré des requêtes lentes. Pour l'utiliser, ouvrez le fichier my.cnf et définissez la variable slow_query_log sur "On". Définissez long_query_time sur le nombre de secondes qu'une requête devrait prendre pour être considérée comme lente, disons 0.2. Définissez slow_query_log_file sur le chemin où vous souhaitez enregistrer le fichier. Ensuite, exécutez votre code et toute requête au-dessus du seuil spécifié sera ajoutée à ce fichier.
Une fois que vous savez quelles sont les requêtes lentes, vous pouvez commencer à explorer ce qui les ralentit. Un outil que MySQL offre est le mot-clé EXPLAIN. Il fonctionne avec les instructions SELECT, DELETE, INSERT, REPLACE et UPDATE. Vous préférez simplement la requête comme ceci:
EXPLAIN SELECT picture.id, picture.title FROM picture LEFT JOIN album ON picture.album_id = album.id WHERE album.user_id = 1;
Le résultat que vous obtenez est une explication de la façon dont les données sont accessibles. Vous voyez une ligne pour chaque table impliquée dans la requête:
Les éléments importants ici sont le nom de la table, la clé utilisée et le nombre de lignes scannées lors de l'exécution de la requête.
Il lit 2 000 000 images, puis, pour chaque image, elle balaie 20 000 albums. Cela signifie qu'il lit réellement 40 milliards de lignes pour la table des albums. Cependant, vous pouvez rendre ce processus beaucoup plus efficace.
Les index
Vous pouvez augmenter considérablement les performances en utilisant des index. Pensez aux données comme étant des noms dans un carnet d'adresses. Vous pouvez soit feuilleter toutes les pages, soit vous pouvez tirer sur le bon onglet de la lettre pour localiser rapidement le nom dont vous avez besoin.
Utilisez des index pour éviter les passages inutiles dans les tables. Par exemple, vous pouvez ajouter un index sur picture.album_id comme ceci:
ALTER TABLE picture ADD INDEX(album_id);
Maintenant, si vous exécutez la requête, le processus ne nécessite plus la lecture de la liste complète des images. Tout d'abord, tous les albums sont lus pour trouver ceux qui appartiennent à l'utilisateur. Ensuite, les images sont rapidement localisées à l'aide de la colonne index_distribution d'album. Cela réduit le nombre de lignes lues à 200 000. La requête est également environ 317 fois plus rapide que l'original.
Cette fois, la table des albums n'est pas entièrement balayée, mais les albums exacts sont rapidement identifiés à l'aide de la clé user_id. Lorsque ces 100 albums sont lus, les images associées sont identifiées à l'aide de la clé album_id. Chaque table utilise une clé pour une performance optimale, ce qui rend la requête 380 fois plus rapide que l'original.
Cela ne signifie pas que vous devez ajouter des index partout parce que chaque index rend plus long l'écriture dans la base de données. Vous gagnez en lecture mais perdent sur l'écriture.
Donc, ajoutez uniquement des index qui augmentent réellement les performances de lecture.
Utilisez EXPLAIN pour confirmer et supprimer tout index qui n'est pas utilisé dans les requêtes.
Commentaires