詳細介紹Mysql性能優(yōu)化之子查詢

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?'創(chuàng)建時間')?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?'創(chuàng)建時間',  　　--?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?<p>這里我插入了5000條數(shù)據(jù), group分了99個組, 隨機的.?</p><p>3. 查詢sql</p><pre class="brush:sql;toolbar:false">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;