Mysql数据库性能优化之子查询

记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的.

那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理.

当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划", 然后根据所得到的执行计划通过调用存储引擎接口来获取相应数据. 再对存储引擎返回的数据进行相关的处理, 并一Client端所要求的格式作为结果集, 返回给Client.

注 : 这里所说的统计数据, 是我们通过 Analyze table命令通知Mysql对表的相关数据作分析之后, 所获取到的一些数据统计量. 这些数据对Mysql优化器而言是非常重要的, 优化器所生成的执行计划的好坏, 主要是由这些统计数据所决定的.

1. 建表

									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. 准备数据

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

									}

这里我插入了5000条数据, group分了99个组, 随机的.

3. 查询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;