Introduction détaillée à la sous-requête d'optimisation des performances Mysql

Je me souviens que lorsque je travaillais sur un projet, j'ai entendu un dicton, essayez de ne pas utiliser de sous-requêtes, alors jetons un œil à cet article pour voir si cette phrase est correcte.

Avant cela, il est nécessaire d'introduire quelques éléments conceptuels et le traitement général des instructions par MySQL

Lorsque le thread de connexion du serveur Mysql reçoit la requête SQL envoyée par le client, il passera par une série d'analyses de décomposition, effectuera les analyses correspondantes. analyse, puis Mysql utilisera le module d'optimisation de requêtes pour effectuer des calculs et des analyses basés sur les informations statistiques pertinentes des tables de données impliquées dans SQL. Ensuite, il dérivera une méthode d'accès aux données que Mysql considère comme étant. la manière la plus raisonnable et la plus optimale, c'est-à-dire que nous disons souvent "Plan d'exécution", puis obtenons les données correspondantes en appelant l'interface du moteur de stockage selon le plan d'exécution obtenu. Effectuons ensuite le traitement approprié sur les données renvoyées. par le moteur de stockage et le traiter comme requis par le client. Le format est utilisé comme ensemble de résultats et renvoyé au client.

Remarque : les données statistiques mentionnées ici sont des statistiques de données obtenues après que nous en ayons informé Mysql. pour analyser les données pertinentes de la table via la commande Analyser la table . Ces données sont très importantes pour l'optimiseur Mysql. La qualité du plan d'exécution généré par l'optimiseur est principalement déterminée par ces données statistiques.

1. . Créer un tableau

create table User(
　　Id int not null PRIMARY key auto_increment ,
　　NickName varchar(50) comment '用户昵称',
　　Sex int comment '性别',
　　Sign varchar(50) comment '用户签名',
　　Birthday datetime comment '用户生日',
　　CreateTime datetime comment '创建时间') default charset=utf8 comment '用户表';create table UserGroup(
　　Id int not null PRIMARY key auto_increment ,
　　UserId int not null comment 'user Id',
　　GroupId int not null comment '用户组Id',
　　CreateTime datetime comment '创建时间',
　　-- key index_groupid(GroupId) using btree,　　key index_userid(groupid, UserId) using btree
) default charset=utf8 comment '用户组表';

2. Préparer les données

var conStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
using (IDbConnection conn = new MySqlConnection(conStr))
{
    Stopwatch watch = new Stopwatch();
    var sql = string.Empty;
    var names = new string[] { "非", "想", "红", "帝", "德", "看", "梅", "插", "兔" };
    Random ran = new Random();  
    var insertSql = @" insert into User(NickName,Sex,Sign, Birthday, CreateTime) values(@NickName,@Sex,@Sign, @Birthday, @CreateTime); 
    INSERT INTO usergroup  (UserId,  GroupId,  CreateTime )  VALUES (LAST_INSERT_ID() ,   @GroupId,  @CreateTime);";
    watch.Start();
    if (conn.State == ConnectionState.Closed)
    {
        conn.Open();
    }

    var tran = conn.BeginTransaction();
    for (int i = 0; i < 100000; i++)
    {
        var param = new { NickName = names[ran.Next(9)] + names[ran.Next(9)] + i, Sign = names[ran.Next(9)] + names[ran.Next(9)], CreateTime = DateTime.Now, Birthday = DateTime.Now.AddYears(ran.Next(10, 30)), Sex = i % 2, GroupId = ran.Next(1, 100) };
        conn.Execute(insertSql, param, tran);
    }
    tran.Commit();

    conn.Dispose();
    watch.Stop();
    Console.WriteLine(watch.ElapsedMilliseconds);
}

Ici, j'ai inséré 5000 éléments de données, et le groupe a été divisé en 99 groupes, au hasard. 3. Requête sql

explain
select user.id, user.nickname from usergroup 
left join user  on usergroup.UserId = user.Id
where  usergroup.groupid = 1 
order by usergroup.UserId desc
limit 100, 20;

 explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid limit 100, 20) t
left join  user on t.UserId = user.id ;

 explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid ) t
left join  user on t.UserId = user.id 
limit 100, 20;

4. . Lors de l'analyse de

100 000 éléments de données :

Lisez d'abord la première phrase

La troisième phrase

En regardant les trois photos ci-dessus, nous semblons pouvoir dire quelque chose.

Regardez d'abord leurs lignes. La deuxième phrase en contient le plus, totalisant plus de 1 000. Les deux autres phrases totalisent 996. Mais ce que je veux dire, c'est que ici, ce n'est pas pour regarder. à la somme des lignes. Correct La méthode consiste à commencer par l'instruction avec l'identifiant le plus grand, puis à exécuter les instructions avec le même identifiant de haut en bas

Ensuite, regardez l'instruction avec l'identifiant = 2 dans. la deuxième phrase et l'instruction avec id=1 dans la première phrase. Les instructions sont exactement les mêmes. Elles filtrent toutes les données de la table des groupes d'utilisateurs et peuvent obtenir le même ensemble de résultats A.

Il semble qu'elles le soient. tous fonctionnant sur la base du même ensemble de résultats. Il y aura ensuite des différences.

Regardez d'abord la première phrase, puis en fonction de l'ensemble de résultats A, accédez à l'utilisateur de la table de jointure de gauche, filtrez la finale. données et les renvoyer au client.

Qu'en est-il de la deuxième phrase ? , basée sur A, les données sont à nouveau filtrées pour obtenir les données requises, puis les données sont laissées connectées à la table utilisateur pour obtenir le résultat final.

Du point de vue ci-dessus, dans le plan d'exécution, le deuxième Ce plan d'exécution est plus efficace

Si vous pouvez réduire considérablement la portée de la requête via une sous-requête. , vous pouvez envisager d'utiliser une instruction de sous-requête

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!