詳細介紹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;

第二句和第三句都使用到了子查詢, 不同之處再與, 第二句是先得到20條資料, 然後以此來與user表關聯的

4. 分析

100000條資料狀況下: 

先看第一句

#再看第二句

## #########第三句#​​##############從上面三幅圖看, 好像能看出點什麼了.######首先看他們的rows, 第二句最多, 加起來有1000多了, 另兩句加起來都是996. 但是我想說的是, 這裡並不是看rows的和是多少. 正確的方式是, 從id大的語句開始看, id相同的語句, 從上到下依次執行.######那先看第二句的id=2的語句和第一句的id=1的語句, 一模一樣的. 他們都是從usergroup表中篩選資料, 並且能得到相同的結果集A.######看來他們都是基於相同的結果集去進行操作, 接下來就有區別了.##### #先看第一句, 再結果集A的基礎上, 去左連接表user, 並篩選出最後的資料, 回傳給客戶端.######那第二句呢, 是在A的基礎上, 再次篩選資料, 得到需要的資料, 然後拿這些資料, 去與user表左連接, 得到最終結果.######從上面來看, 執行計劃中, 第二種執行計劃, 更加高效. ###### 如果能夠透過子查詢, 大幅縮小查詢範圍, 可以考慮使用子查詢語句. ###

以上是詳細介紹Mysql效能最佳化之子查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！