记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的.
那在这之前, 需要介绍一些概念性东西和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数据库性能优化之子查询，希望对大家有所帮助
如对本文有疑问， 点击进行留言回复！！
服务器Centos部署MySql并连接Navicat过程详解
网友评论