Comment optimiser l'instruction de jointure dans MySQL

Jointure simple en boucle imbriquée

Jetons un coup d'œil au fonctionnement de MySQL lors de l'exécution d'une opération de jointure. Quelles sont les méthodes de jointure courantes ?

Comme le montre l'image, lorsque nous effectuons l'opération de connexion, la table de gauche est Driven table , et la table de droite est # 🎜🎜#driven table

Simple Nested-Loop Join Cette opération de jointure consiste à prendre un enregistrement de la table pilote, puis à faire correspondre les enregistrements de la table pilotée tableau un par un. Si les conditions correspondent, le résultat sera renvoyé. Ensuite, continuez à faire correspondre l'enregistrement suivant dans la table des pilotes jusqu'à ce que toutes les données de la table des pilotes aient été mises en correspondance

Parce qu'il faut du temps pour récupérer les données de la table des pilotes à chaque fois , MySQL n'utilise pas Cet algorithme est utilisé pour effectuer des opérations de connexion Afin d'éviter de démarrer à partir du pilote à chaque fois, la récupération des données de la table prend du temps. Nous pouvons récupérer un lot de données de la table du pilote en une seule fois et effectuer des opérations de correspondance en mémoire. Une fois ce lot de données mis en correspondance, un lot de données est extrait de la table des pilotes et placé dans la mémoire jusqu'à ce que toutes les données de la table des pilotes correspondent.La récupération de données par lots peut réduire considérablement les E/S. opérations, donc l'efficacité d'exécution est relativement élevée, cette opération de connexion est également utilisée par MySQL

D'ailleurs, cette mémoire a un nom propre dans MySQL, appelé join buffer Nous pouvons exécuter l'instruction suivante pour. afficher la taille du tampon de jointure#🎜🎜 #

show variables like '%join_buffer%'

Sortez la table single_table que nous avons utilisée auparavant, créez 2 tables basées sur la table single_table et insérez 1w aléatoire enregistrements dans chaque table

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

create table t1 like single_table;
create table t2 like single_table;

# 🎜🎜#Si vous utilisez directement l'instruction de jointure, l'optimiseur MySQL peut sélectionner la table t1 ou t2 comme table pilote, ce qui affectera notre processus d'analyse de l'instruction SQL, nous utilisons donc Straight_join pour permettre à mysql d'utiliser une méthode de connexion fixe pour exécuter la requête

select * from t1 straight_join t2 on (t1.common_field = t2.common_field)

Le temps d'exécution est de 0,035s

Le plan d'exécution est le suivant

# 🎜🎜# J'ai vu Using join buffer dans la colonne Extra, indiquant que l'opération de connexion est basée sur

Block Nested-Loop Join

algorithme

Index Nested-Loop Join# 🎜🎜#Après avoir compris l'algorithme

Block Nested-Loop Join

, vous pouvez voir que chaque enregistrement dans la table pilote fera correspondre tous les enregistrements de la table pilotée. Cela prend-il beaucoup de temps. Peut-il être amélioré. Qu'en est-il de l'efficacité de la correspondance des tables pilotées ?

Je suppose que vous avez aussi pensé à cet algorithme, qui consiste à ajouter des index aux colonnes connectées par la table pilotée, afin que le processus de correspondance soit très rapide, comme le montre l'image

# 🎜🎜#

Voyons à quelle vitesse il est nécessaire d'effectuer des requêtes basées sur des jointures basées sur des colonnes d'index ?

select * from t1 straight_join t2 on (t1.id = t2.id)

Le temps d'exécution est de 0,001 seconde, ce qui peut être considéré comme plus d'un niveau plus rapide que la connexion basée sur des colonnes ordinaires

# 🎜🎜# Le plan d'exécution est le suivant

Toutes les colonnes des enregistrements de la table pilote ne seront pas placées dans le tampon de jointure, seulement les colonnes dans la liste de requêtes et le filtrage Seules les colonnes de la condition seront placées dans le tampon de jointure, nous n'utilisons donc pas * comme liste de requêtes, nous avons seulement besoin de mettre les colonnes qui nous intéressent dans la liste de requêtes, donc que plus d'enregistrements peuvent être placés dans le tampon de jointure#🎜🎜 #

Comment choisir la table des pilotes ?

Maintenant que nous connaissons l'implémentation spécifique de join, parlons d'une question courante, à savoir, comment choisir la table des pilotes ?

S'il s'agit de l'algorithme Block Nested-Loop Join :

Lorsque le tampon de jointure est suffisamment grand, qui le fait ? La table pilote n'a aucun impact

Lorsque le tampon de jointure n'est pas assez grand, vous devez choisir une petite table comme table pilote (la petite table a moins de données et le nombre de fois où elles sont placées dans le tampon de jointure est petit, ce qui réduit le nombre d'analyses de la table)

Si c'est le cas un algorithme de jointure à boucle imbriquée d'index

On suppose que les lignes de la table de pilotage Le nombre est M, donc M lignes de la table de pilotage doivent être numérisées

# 🎜🎜#Chaque fois qu'une ligne de données est obtenue à partir de la table pilotée, l'index a doit d'abord être recherché, puis l'index de clé primaire est recherché. Le nombre de lignes dans la table pilotée est N. La complexité approximative de la recherche d'un arbre à chaque fois est le logarithme de base 2 N, donc la complexité temporelle de la recherche d'une ligne sur la table pilotée est de 2 ∗ #Chaque ligne de données de la table pilotée doit être recherchée une fois dans la table pilotée. La complexité approximative de l'ensemble du processus d'exécution est M + M ∗ 2∗log2N

• Évidemment, M a un plus grand impact sur le nombre de lignes numérisées, donc une petite table doit être utilisée comme table de conduite. Bien entendu, la prémisse de cette conclusion est que l'index de la table pilotée peut être utilisé

En bref, nous pouvons laisser la petite table être la table pilote

Lorsque l'instruction de jointure est exécutée lentement, nous pouvons l'optimiser grâce aux méthodes suivantes

• Lors de l'exécution de l'opération de jointure, la table pilotée peut être utilisé Index

• Utiliser une petite table comme table pilote

• Augmenter la taille du tampon de jointure

• Ne pas utiliser * comme liste de requêtes, renvoyer uniquement les colonnes requises

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!