Pourquoi les requêtes MySQL affichent-elles « Utilisation de temporaire » et « Utilisation de tri de fichiers » et comment pouvez-vous les optimiser ?

Optimisation MySQL : Comprendre "Utiliser l'index, utiliser le temporaire, utiliser le tri de fichiers"

Lors de l'exécution d'une requête, MySQL peut recourir à l'utilisation de tables temporaires et le tri de fichiers, comme l'indiquent les messages « Utilisation de fichiers temporaires » et « Utilisation de tri de fichiers ». Explorons ce que cela signifie dans le contexte d'une requête spécifique :

EXPLAIN SELECT
    el.object_id,
    leo.object_desc,
    COUNT(el.object_id) AS count_rows
FROM
    event_log AS el
LEFT JOIN
    lookup_event_objects AS leo ON leo.object_id = el.object_id
GROUP BY
    el.object_id
ORDER BY
    count_rows DESC,
    leo.object_desc ASC

"Utilisation de temporaire"

Selon la documentation MySQL, les tables temporaires sont créées lorsque :

• Il existe une clause ORDER BY et une clause GROUP BY différente, ou ORDER BY ou GROUP BY contient des colonnes de tables autres que la première table de la file d'attente de jointure.

Dans la requête donnée, la clause ORDER BY trie selon un champ calculé "count_rows", qui ne figure pas dans la clause GROUP BY, et la clause GROUP BY utilise une colonne de la table jointe "lookup_event_objects". Par conséquent, MySQL crée une table temporaire pour effectuer le regroupement et le tri.

"Utilisation du tri de fichiers"

MySQL utilise le tri de fichiers lorsque l'opération de tri ne peut pas être effectuée à l'aide d'un index. Dans cette requête, le tri sur le champ calculé "count_rows" ne peut pas être indexé, MySQL utilise donc filesort pour effectuer le tri.

Optimisation

Pour optimiser cette requête, vous pouvez créer un index sur "count_rows". Cependant, puisqu'il s'agit d'un champ calculé, vous devrez utiliser une astuce MySQL :

CREATE TABLE lookup_event_objects_temp AS
SELECT
    object_id,
    object_desc,
    COUNT(DISTINCT event_id) AS count_rows
FROM
    event_log
GROUP BY
    object_id;

CREATE INDEX idx_count_rows ON lookup_event_objects_temp (count_rows);

ALTER TABLE lookup_event_objects_temp RENAME TO lookup_event_objects;

En créant cette table temporaire avec un champ "count_rows" indexé, la requête peut utiliser l'index pour effectuer efficacement la tri, éliminant le besoin de tri de fichiers et réduisant l'utilisation de la table temporaire.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!