記得在做項目的時候, 聽到過一句話, 盡量不要使用子查詢, 那么這一篇就來看一下, 這句話是否是正確的.
那在這之前, 需要介紹一些概念性東西和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?<p>這里我插入了5000條數據, 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;
第二句和第三句都使用到了子查詢, 不同之處再與, 第二句是先得到20條數據, 然后以此來與user表關聯的
4. 分析
100000條數據情況下 :?
先看第一句
再看第二句
第三句
從上面三幅圖看, 好像能看出點什么了.
首先看他們的 rows, 第二句最多, 加起來有1000多了, 另兩句加起來都是996. 但是我想說的是, 這里并不是看rows的和是多少. 正確的方式是, 從id大的語句開始看, id相同的語句, 從上到下依次執行.
那先看第二句的id=2的語句和第一句的id=1的語句, 一模一樣的. 他們都是從usergroup表中篩選數據, 并且能得到相同的結果集A.
看來他們都是基于相同的結果集去進行操作, 接下來就有區別了.
先看第一句, 再結果集A的基礎上, 去左連接表user, 并篩選出最后的數據, 返回給客戶端.
那第二句呢, 是在A的基礎上, 再次篩選數據, 得到需要的數據, 然后拿這些數據, 去與user表左連接, 得到最終結果.
從上面來看, 執行計劃中, 第二種執行計劃, 更加高效.?
?如果能夠通過子查詢, 大幅度縮小查詢范圍, 可以考慮使用子查詢語句.?
