詳細介紹Mysql效能最佳化之子查詢

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 '用户组表';

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);
}

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;