différence de tri MySQL

Parlons en détail du mode de tri MySQL, du moins profond au plus profond, de la manière dont il affecte la sélection par MySQL des différents modes de tri et de la manière d'optimiser le tri.

Cours recommandé : Tutoriel MySQL.

Le tri est une fonction de base dans les bases de données, et MySQL ne fait pas exception.

Les utilisateurs peuvent trier l'ensemble de résultats spécifié via l'instruction Order by. En fait, non seulement l'instruction Order by, mais également l'instruction Group by et l'instruction Distinct utiliseront implicitement le tri. Cet article présentera d'abord brièvement comment SQL utilise les index pour éviter le coût du tri, puis présentera les principes internes de MySQL pour implémenter le tri.

Résolvez les questions suivantes de tout le monde :

Où MySQL utilise-t-il le tri et comment juger que MySQL utilise le tri ?

MySQL a plusieurs modes de tri et comment les utiliser ; MySQL choisit différents modes de tri ;

Quelle est la relation entre le tri MySQL et read_rnd_buffer_size, et dans quelles circonstances l'augmentation de read_rnd_buffer_size peut-elle optimiser le tri

Comment juger que MySQL utilise le disque pour le tri, et comment ; pour l'éviter ou l'optimiser Tri des disques ;

Comment les données de champ de longueur variable (varchar) sont stockées en mémoire pendant le tri, et quelles améliorations y a-t-il dans la version 5.7

Dans ce cas, quelles améliorations ; est-ce qu'il y a du mode tri ;

Qu'est-ce que sort_merge_pass exactement ? Quel problème la valeur de statut indique-t-elle si elle est trop grande

Si MySQL utilise le tri, quelles méthodes ? peut-on l'analyser et l'optimiser pour rendre le tri plus rapide ?

2. Tri

Lorsque nous visualisons le plan d'exécution de MySQL via Explication, nous voyons souvent Utilisation du tri de fichiers affiché dans la colonne Extra.

Pour SQL qui ne peut pas utiliser d'index pour éviter le tri, la base de données doit implémenter la fonction de tri elle-même pour répondre aux besoins des utilisateurs. À ce stade, "Utilisation du tri de fichiers" apparaîtra dans le plan d'exécution SQL. ici, filesort ne signifie pas qu'il s'agit d'un tri de fichiers. En fait, il peut également s'agir d'un tri de mémoire. Ceci est principalement déterminé par le paramètre sort_buffer_size et la taille du jeu de résultats.

En fait, cette situation montre que MySQL utilise le tri. L'utilisation du tri de fichiers apparaît souvent par ordre, groupe par, distinct, jointure, etc.

Il existe trois manières principales d'implémenter le tri en interne dans MySQL : le tri régulier, le tri par optimisation et le tri par file d'attente prioritaire.

CREATE TABLE t1(id int, col1 varchar(64), col2 varchar(64), col3 varchar(64), PRIMARY KEY(id),key(col1,col2));
SELECT col1,col2,col3 FROM t1 WHERE col1>100 ORDER BY col2;

Veuillez voir la différence entre ces trois tris :

Tri conventionnel

(1) .Obtenez les enregistrements qui répondent à la condition WHERE de la table t1

(2) Pour chaque enregistrement, retirez la clé primaire + la clé de tri (id, col2) de l'enregistrement et placez-le dans le tampon de tri

.

( 3). Si le tampon de tri peut stocker toutes les paires (id, col2) qui remplissent les conditions, triez-les ; sinon, lorsque le tampon de tri est plein, triez-les et solidifiez-les dans un fichier temporaire. (L'algorithme de tri utilise l'algorithme de tri rapide)

(4) Si des fichiers temporaires sont générés lors du tri, vous devez utiliser l'algorithme de tri par fusion pour vous assurer que les enregistrements dans les fichiers temporaires sont en ordre

( 5). Exécutez le processus ci-dessus en boucle jusqu'à ce que tous les enregistrements qui remplissent les conditions soient impliqués dans le tri

(6) Scannez les paires triées (id, col2) et utilisez l'identifiant. pour obtenir les colonnes qui doivent être renvoyées par SELECT ( col1,col2,col3)

(7) Renvoie l'ensemble de résultats obtenu à l'utilisateur.

D'après le processus ci-dessus, l'utilisation du tri de fichiers dépend principalement de la capacité du tampon de tri à accueillir la paire (id, col2) qui doit être triée. La taille de ce tampon est contrôlée par le paramètre sort_buffer_size. De plus, un tri nécessite deux IO, l'une consiste à récupérer (id, col2) et la seconde à récupérer (col1, col2, col3). Puisque l'ensemble de résultats renvoyé est trié par col2, les identifiants sont dans le désordre. Une grande quantité d'E/S aléatoires sera générée lors de la pêche de (col1, col2, col3) avec l'identifiant. Une optimisation pour MySQL lui-même pour la deuxième fois consiste à trier les identifiants avant de les récupérer et à les mettre dans le tampon. La taille de ce tampon est contrôlée par le paramètre read_rnd_buffer_size, puis à récupérer les enregistrements de manière ordonnée, en convertissant les E/S aléatoires en. E/S séquentielles.

b. Tri optimisé

En plus du tri lui-même, la méthode de tri conventionnelle nécessite deux IO supplémentaires. Par rapport au tri conventionnel, la méthode de tri optimisée réduit la deuxième IO. La principale différence est que le tampon de tri n'est pas (id, col2), mais (col1, col2, col3). Étant donné que le tampon de tri contient tous les champs requis pour la requête, il peut être renvoyé directement une fois le tri terminé sans qu'il soit nécessaire de récupérer à nouveau les données. Le coût de cette méthode est que le nombre de (col1, col2, col3) pouvant être stockés dans un tampon de tri de même taille est inférieur à (id, col2). Si le tampon de tri n'est pas assez grand, des fichiers temporaires peuvent se produire. doivent être écrits, provoquant des E/S supplémentaires. Bien entendu, MySQL fournit le paramètre max_length_for_sort_data. Ce n'est que lorsque le tuple trié est plus petit que max_length_for_sort_data que la méthode de tri optimisée peut être utilisée. Sinon, la méthode de tri conventionnelle ne peut être utilisée.

c. Tri prioritaire des files d'attente

Afin d'obtenir le résultat final du tri, quoi qu'il arrive, nous devons trier tous les enregistrements qui remplissent les conditions avant de les restituer. Alors par rapport à l’optimisation du mode de tri, y a-t-il encore place à l’optimisation ? La version 5.6 a optimisé l'instruction Order by limit M, N au niveau spatial et a ajouté une nouvelle méthode de tri - la file d'attente prioritaire, qui est implémentée à l'aide du tri par tas. Les caractéristiques de l'algorithme de tri par tas peuvent résoudre le problème de tri de limite M, N. Bien que tous les éléments soient toujours tenus de participer au tri, seul l'espace tampon de tri des tuples M+N est nécessaire pour les scénarios où M et N sont. petit, fondamentalement, il n'y aura aucun problème d'exiger des fichiers temporaires pour le tri par fusion en raison d'un tampon de tri insuffisant. Pour l'ordre croissant, un grand tas supérieur est utilisé et les éléments du tas final forment les N éléments les plus petits. Pour l'ordre décroissant, un petit tas supérieur est utilisé et les éléments du tas final forment les N éléments les plus grands.

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!