Mysql - 性能优化之子查询

时间:2022-10-23 16:11:09

记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的.

那在这之前, 需要介绍一些概念性东西和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 = ; i < ; i++)
{
var param = new { NickName = names[ran.Next()] + names[ran.Next()] + i, Sign = names[ran.Next()] + names[ran.Next()], CreateTime = DateTime.Now, Birthday = DateTime.Now.AddYears(ran.Next(, )), Sex = i % , GroupId = ran.Next(, ) };
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条数据情况下 :

先看第一句

Mysql - 性能优化之子查询

再看第二句

Mysql - 性能优化之子查询

第三句

Mysql - 性能优化之子查询

从上面三幅图看, 好像能看出点什么了.

首先看他们的 rows, 第二句最多, 加起来有1000多了, 另两句加起来都是996. 但是我想说的是, 这里并不是看rows的和是多少. 正确的方式是, 从id大的语句开始看, id相同的语句, 从上到下依次执行.

那先看第二句的id=2的语句和第一句的id=1的语句, 一模一样的. 他们都是从usergroup表中筛选数据, 并且能得到相同的结果集A.

看来他们都是基于相同的结果集去进行操作, 接下来就有区别了.

先看第一句, 再结果集A的基础上, 去左连接表user, 并筛选出最后的数据, 返回给客户端.

那第二句呢, 是在A的基础上, 再次筛选数据, 得到需要的数据, 然后拿这些数据, 去与user表左连接, 得到最终结果.

从上面来看, 执行计划中, 第二种执行计划, 更加高效.

如果能够通过子查询, 大幅度缩小查询范围, 可以考虑使用子查询语句.

参考:

  Mysql性能优化explain

Mysql - 性能优化之子查询的更多相关文章

  1. MySQL 性能优化之慢查询

    性能优化的思路 首先需要使用慢查询功能,去获取所有查询时间比较长的SQL语句 其次使用explain命令去查询由问题的SQL的执行计划(脑补链接:点我直达1,点我直达2) 最后可以使用show pro ...

  2. mysql性能优化-慢查询分析、优化索引和配置 (慢查询日志,explain,profile)

    mysql性能优化-慢查询分析.优化索引和配置 (慢查询日志,explain,profile) 一.优化概述 二.查询与索引优化分析 1性能瓶颈定位 Show命令 慢查询日志 explain分析查询 ...

  3. MySQL性能优化总结

    一.MySQL的主要适用场景 1.Web网站系统 2.日志记录系统 3.数据仓库系统 4.嵌入式系统 二.MySQL架构图: 三.MySQL存储引擎概述 1)MyISAM存储引擎 MyISAM存储引擎 ...

  4. mysql性能优化-简易版

    mysql性能优化 sql语句优化 如何发现有问题的sql? 开启mysql慢查询 show variables like 'slow_query_log' set global slow_query ...

  5. MySQL性能优化总结(转)https&colon;&sol;&sol;yq&period;aliyun&period;com&sol;articles&sol;24249

    摘要: 一.MySQL的主要适用场景 1.Web网站系统 2.日志记录系统 3.数据仓库系统 4.嵌入式系统 二.MySQL架构图:   三.MySQL存储引擎概述 1)MyISAM存储引擎 MyIS ...

  6. MySQL &&num;183&semi; 性能优化 &&num;183&semi; 条件下推到物化表

    MySQL · 性能优化 · 条件下推到物化表 http://mysql.taobao.org/monthly/2016/07/08/ 背景 MySQL引入了Materialization(物化)这一 ...

  7. MySQL性能优化总结&lowbar;&lowbar;&lowbar;本文乃《MySQL性能调优与架构设计》读书笔记!

    一.MySQL的主要适用场景 1.Web网站系统 2.日志记录系统 3.数据仓库系统 4.嵌入式系统 二.MySQL架构图: 三.MySQL存储引擎概述 1)MyISAM存储引擎 MyISAM存储引擎 ...

  8. Mysql 性能优化教程

    Mysql 性能优化教程 目录 目录 1 背景及目标 2 Mysql 执行优化 2 认识数据索引 2 为什么使用数据索引能提高效率 2 如何理解数据索引的结构 2 优化实战范例 3 认识影响结果集 4 ...

  9. mysql索引优化比普通查询速度快多少

    mysql索引优化比普通查询速度快多少 一.总结 一句话总结:普通查询全表查询,速度较慢,索引优化的话拿空间换时间,一针见血,所以速度要快很多. 索引优化快很多 空间换时间 1.软件层面优化数据库查询 ...

随机推荐

  1. Objective-C中的类目,延展,协议

    Objective-C中的类目(Category),延展(Extension),协议(Protocol)这些名词看起来挺牛的,瞬间感觉OC好高大上.在其他OOP语言中就没见过这些名词,刚看到这三个名词 ...

  2. MMORPG大型游戏设计与开发(part5 of net)

    上一部分将服务器的具体代码的实现介绍给了大家,想必大家也了解到了服务器处理一次消息的复杂度.如果大家能够将各个过程掌握清楚,就会发觉其实整个逻辑与交互过程是比较清晰的.那么服务器与服务器之间的通讯,其 ...

  3. MyEclipse定位class文件

    upolfind.bat :: 读取参数 例子 E:\workspaces\common-ws\xm_upol\WebRoot\WEB-INF\classes\${java_type_name} se ...

  4. 【BZOJ】【1045&sol;1465】【HAOI2008】糖果传递

    思路题/神奇的转化…… orz hzwer 或许这个思路可以从单行而非环形的递推中找到?(单行的时候,从左往右直接递推即可…… 感觉好神奇>_<脑残患者想不出…… P.S.话说在$n\le ...

  5. 我的学习笔记之API-Guides翻译------AppComponent&lowbar;Activites

    10.26第一天开始:贵在坚持,边看遍整理 一个应用程序通常由多个Activity组成,它们之间是松耦合的关系.特别的,有一个Activity作为app的主Activity,当app首次启动时呈现给用 ...

  6. MVC详解

    模型-视图-控制器(Modal View Controler,MVC)是Xerox PARC在八十年代为编程语言Smalltalk-80发明的一种软件设计模式,至今已被广泛使用.最近几年被推荐为Sun ...

  7. monodb分片集群部署

    本文档基于MongoDB版本3.6.2 下载地址: 建议使用最新版本 https://www.mongodb.com/download-center#community 安装文件 集群ip及端口设计方 ...

  8. 使用ERStudio创建数据表与ER图

    内容中包含 base64string 图片造成字符过多,拒绝显示

  9. 项目总结&lpar;一&rpar;-&gt&semi;项目的七宗罪

    大半夜来这一份总结,心中夹杂着各种各样的心情,酸甜苦辣都有,今天为止,整个项目终于完结了,对于这样一个本可以正而八经吃吃薯片,看看毛片就可以完成项目,最后演变成一个一月之内连续加班105个小时的项目, ...

  10. Java 关于容器集合等数据结构详情图解,一目了然

    建议把图片下载下来保存之,网页展示不开... watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvY3NtX3F6/font/5a6L5L2T/fontsize/ ...