收藏本站 收藏本站
积木网首页 - 软件测试 - 常用手册 - 站长工具 - 技术社区
首页 > 数据库 > MySQL > 正文

首页 - PHP - 数据库 - 操作系统 - 游戏开发 - JS - Android - MySql - Redis - MongoDB - Win8 - Shell编程 - DOS命令 - jQuery - CSS样式 - Python - Perl

Access - Oracle - DB2 - SQLServer - MsSql2008 - MsSql2005 - Sqlite - PostgreSQL - node.js - extjs - JavaScript vbs - Powershell - Ruby

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数据库性能优化之子查询,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对积木网网站的支持!

解析windows下使用命令的方式安装mysql5.7的方法
解压zip压缩包,创建my.ini文件内容如下这里注意一下sql_mode这里写的是让MySQL使用习惯类似Oracle,具体哪些什么意思大家很容易百度查到[mysql]#设置mysql客

如何查看MySQL连接的root密码
本文给大家分享的是查看MySQL连接的root密码的方法,下面话不多说来来看正文:1.首先我们进到MySQL的bin目录下cd/usr/local/mysql/bin2.切换成root身份binsudosu3.

MySQL中联表更新与删除的语法介绍
前言相信大家在日常使用mysql,可能会遇到需要同时更新两张表时,我会采用在同一个事务中使用2句sql语句分别进行更新。其实,这种需要发送2句sql语

本周排行

更新排行

强悍的草根IT技术社区,这里应该有您想要的! 友情链接:b2b电子商务
Copyright © 2010 Gimoo.Net. All Rights Rreserved  京ICP备05050695号