查询产生临时表的调优

我们都知道,SQL查询时若产生了临时表,一般要消耗更多的内存,降低查询的效率。因此,当查询时产生了临时表,要进行优化,使引擎在查询时不用创建临时表就能完成查询。


示例使用的是MySQL示例数据库sakila。actor表中有一个主键actor_id,演员的first_name与last_name。film_actor表中外键列actor_id,与actor表进行连接,其中actor_id有很多重复的值,同时actor_id也是主键列(不自增)。


执行语句:SELECT first_name,last_name,COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;

结果(返回了很多演员的名字与演过的电影数量):

这里写图片描述

执行计划:

这里写图片描述

由于在检索actor表时产生了临时表(文件排序一般也伴随出现)。该SQL效率与性能有待提高。

产生临时表的原因有很多,一般有五种情况:

  1. 如果GROUP BY 的列没有索引,产生临时表.
  2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
  3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
  4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
  5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.

下面就来优化SQL语句使临时表不再产生就能完成查询,准则就是尽量把大查询分开为小查询

调优后的执行语句:SELECT first_name,last_name,c.cnt FROM actor INNER JOIN (SELECT actor_id,COUNT(*) AS cnt FROM film_actor GROUP BY actor_id) AS c USING(actor_id);

执行结果(同上):

这里写图片描述

执行计划:

这里写图片描述

可见不再有临时表的产生以及文件排序。


两次慢查询日志的比较:

# Time: 160718 15:28:05
# [email protected]: root[root] @ localhost [127.0.0.1]  Id:    37
# Query_time: 0.013001  Lock_time: 0.000000 Rows_sent: 200  Rows_examined: 6062
SET timestamp=1468826885;
SELECT first_name,last_name,COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;

# Time: 160718 15:37:19
# [email protected]: root[root] @ localhost [127.0.0.1]  Id:    37
# Query_time: 0.006000  Lock_time: 0.000000 Rows_sent: 200  Rows_examined: 5862
SET timestamp=1468827439;
SELECT first_name,last_name,c.cnt FROM actor INNER JOIN(SELECT actor_id,COUNT(*) AS cnt FROM film_actor GROUP BY actor_id) AS c USING(actor_id);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

优化后的查询没有临时表的产生,时间效率更高,性能更好。

版权声明:本文为azhegps原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/azhegps/article/details/72683226

智能推荐

FFMPEG完美入门资料---002---FFmpeg 支持能力说明

接着上文写: 2.3.1 FFmpeg 对编码解码器的支持 ffmpeg 支持的编解码器种类共有 280 多种, 涵盖了几乎所有常见音视频编码格式, 能解码几乎所有的音视频, 每种音视频编解码器的实现都在 libavcodec 目录下有具体的 C 语言实现。 * 注:编码器和解码器的名称不是完全匹配的,因此有些编码器没有对应相同名称的解码器,反之, 解码器也一样。即使编码和解码都支持也不一定是完全...

20145107 《Java程序设计》第五次实验报告

实验简述: 在本周,我们进行了Java的第五次试验,本次实验的主要内容是结对编程。本次实验的大体过程是: 1.先进行Java的客户端与服务端的代码编写。结对是两个人,一人负责客户端,一人负责服务端。 2.利用加解密代码包,编译运行代码,客户端加密,服务器解密。 3.客户端加密明文后将密文通过TCP发送。 4.在本次的代码编写上,要求代码可以实现两者之间的数据传输,在代码传输的基础上加上一定的加密过...

更改springboot启动拼成的字母

1.更改springboot启动拼成的字母 其实很好改,只需要在resources下新建一个txt文件就可以,命名为banner.txt,那这种字符该怎么拼出来呢,下面推荐一个网址,有这种工具 传送门 2.集成...

Node.js安装配置

好久都没更新博客了,今天心血来潮,决定是时候更新一篇了,首先我们来认识一下node.js。 什么是node.js? 简单的说 Node.js 就是运行在服务端的 JavaScript。 Node.js 是一个基于Chrome JavaScript 运行时建立的一个平台。 Node.js是一个事件驱动I/O服务端JavaScript环境,基于Google的V8引擎,V8引擎执行Javascript的...

RocketMQ之双Master集群搭建笔记记录

一:RocketMQ双master集群部署 服务器环境(我采用的虚拟机,centos6 .5【特别注意:安装的虚拟机centos系统一定得是64位的,32位的会启动不起来。即便起来了也会有很多问题,深坑勿踩】)  ip       用户名    密码        角色     模式 192.168.197.101   root        nameServer1,brokerServer1  ...

猜你喜欢

蓝桥杯试题集-基础练习题-数列特征(Java)

//做题笔记,仅自己看得懂 题目: 正确姿势:...

多线程爬取4k超高清美图壁纸

多线程爬取4k美图壁纸 前言:看完此篇文章你可以更加深入的了解多线程的使用,并且最重要的你能够下载你自己想要的超高清4k壁纸 爬取结果: 1. 分析网站 要爬取的url :http://pic.netbian.com/ a) 判断网页是动态加载还是静态加载页面。右击查看网页源代码,按Ctrl + f在源代码中搜索网站的详情页地址,从而判断整个网页是静态加载的 b) 明确爬取的目标。我们要爬取的目标...

elementUI-添加自定义图标

elementui的小图标有限,跟UI给的不一样,这个时候咋办呢?百度走起。。。。参考了两篇博主分享的 自定义elementui中的图标 和 建立图标库,这里主要用到第一种 实际中: elementUI导航栏 具体代码: 汉字转换Unicode编码: 直接打开控制台: 汉字.chatCodeAt().toString(16); 然后回车; 至于三角形的图标,我直接把箭头的 unicode 值改成了...

[Linux]——文件缓冲区

文件缓冲区 提到文件缓冲区这个概念我们好像并不陌生,但是我们对于这个概念好像又是模糊的存在脑海中,之间我们在介绍c语言文件操作已经简单的提过这个概念,今天我们不妨深入理解什么是文件缓冲区。 为什么需要文件缓冲区 当我们在程序中写下一条printf语句时,我们希望将这条语句的内容打印到屏幕上。但是如果你将语句放在循环中,难道你执行一次循环那么操作系统就要打印一次这条数据么?答案当然不是 我们对于程序...

基于FPGA的IIC协议详解——EEPROM控制器(1)

IIC协议举例 常用IIC协议使用地方 常见IIC协议的注意点 24LC64芯片读写命令的时序图 eeprom控制器的系统框图 时序图设计 代码设计 EEPROM控制器测试模块的代码 结束语 常用IIC协议使用地方 熟悉一个协议一定要知道这个协议应该用到什么地方,IIC协议作为飞利浦公司定义的一个慢速传输协议,常用于: 1、芯片寄存器的配置; 2、eeprom的读写; 本次实验我们将使用eepro...