介绍临时表之前，我们首先来看这么一句语句：
CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
这是一个单词表，除了一个主键id之外，只有一个word字段，再来看下面这段sql：
select word from words order by rand() limit 3;
这段sql的语义其实就是按照随意的规则进行排序，然后取出前三个单词并返回，这段sql看起来很简单，但实际上要做的事还是比较繁琐的：
(1)创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。从 words 表中，按主键顺序取出所有的 word 值。
(2)对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
(3)现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表(memory引擎的特性之后再说)上，按照字段 R 排序。
(4)初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。从内存临时表中一行一行地取出 R 值和位置信息(我后面会和你解释这里为什么是“位置信息”)，分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
(5)在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值(这里还是有涉及到回表的操作，但是是回的内存表，所以没有磁盘io的消耗)，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。
当然除了内存临时表之外还有磁盘临时表，磁盘临时表是在内存不足时才会产生，所以尽可能的保证内存临时表的空间要足够大，否则磁盘的操作要比内存的操作要耗时的多！！
OK，我们再来看一个例子，首先定义一下表结构：
create table t1(id int primary key, a int, b int, index(a));
现在我们有这么一个sql：
explain select b,count(*) as c from t1 group by b
sql的语义很简单，对b字段进行分组，并且通过聚合函数统计每个组的个数并返回，但是需要注意的是b字段没有建立索引，于是返回的结果里：
可以看到有使用到临时表，但是后面居然还跟了个using filesort，这是为啥？？我们可以先来看一下这句sql的执行流程：
(1)创建内存临时表，表里有两个字段 b 和 c，主键是 m；扫描表 t1，依次取出b的值；
(2)如果临时表中没有对应的b的值的行，就插入一个记录 (x,1);如果表中有对应的b的值的行，就将这一行的 c 值加 1；遍历完成后，再根据字段 b 做排序，得到结果集返回给客户端；
注意到了没，最后有一步是对b做排序，这一步是在内存中的sort_buffer区域完成的，这一步的诞生导致了using filesort，但是我们的sql寓意里并不需要排序啊，那么可以使用order by null使得group by 之后不需要做任何排序，执行完成后你会发现using filesort消失了。。。但是using temporary还是在的，那么我们可以尝试在b字段加索引，继续执行上面的sql，如果b有索引的话，那么b索引树的肯定是这样子的：
1-2-2-2-5-5-5-5
是一个有序的链表，mysql在扫描这个索引时，直接轧过去，到2的时候自然知道1的个数只有一个，到5的时候自然知道2的个数只有3个，统计的复杂度就是n，但是如果m没有索引，那么就是无序的，在统计时，扫到2的时候，得去一个中间表看是否存在，存在+1，不存在添加。。。到这里也就清晰了，因为b有了索引，所以这个统计操作，只需要遍历一遍索引即可：
可以看到临时表没有再使用了，因为有了索引后，b字段不再需要临时表进行辅助统计，结果perfect！
最后再提一提关于distinct和group by
MySQL中的临时表到底什么是?
Author:极客小俊 一个专注于web技术的80后 我不用拼过聪明人,我只需要拼过那些懒人 我就一定会超越大部分人! CSDN@极客小俊,原创文章, B站技术分享 B站视频 : Bilibili.c ...
谈谈MySQL数据表的类型(转)
谈谈MySQL数据表的类型 通常意义上,数据库也就是数据的集合,具体到计算机上数据库可以是存储器上一些文件的集合或者一些内存数据的集合. 我们通常说的MySql数据库,sql server数据库等等其 ...
谈谈MySQL支持的事务隔离级别，以及悲观锁和乐观锁的原理和应用场景？
在日常开发中,尤其是业务开发,少不了利用 Java 对数据库进行基本的增删改查等数据操作,这也是 Java 工程师的必备技能之一.做好数据操作,不仅仅需要对 Java 语言相关框架的掌握,更需要对各种 ...
第36讲 谈谈MySQL支持的事务隔离级别，以及悲观锁和乐观锁的原理和应用场景
在日常开发中,尤其是业务开发,少不了利用 Java 对数据库进行基本的增删改查等数据操作,这也是 Java 工程师的必备技能之一.做好数据操作,不仅仅需要对 Java 语言相关框架的掌握,更需要对各种 ...
今天来学习一下MySQl的 临时表，变量，行转列，预处理的一些相关技术的使用！
先来简单了解一下MySQL数据库有意思的简介 MySQL这个名字,起源不是很明确.一个比较有影响的说法是,基本指南和大量的库和工具带有前缀“my”已经有10年以上, 而且不管怎样,MySQL AB创始 ...
MYSQL 磁盘临时表和文件排序
因为Memory引擎不支持BOLB和TEXT类型,所以,如果查询使用了BLOB或TEXT列并且需要使用隐式临时表,将不得不使用MyISAM磁盘临时表,即使只有几行数据也是如此. 这会导致严重的性能开销 ...
关于mysql的临时表并行的问题
mysql的临时表并行是没问题的 以为临时表是基于会话的 1.因为在mysql里面每个会话的sessionid 不一样 2.其实就是会话级别的临时表 DB2里面有会话级别 全局级别的临时表,Orac ...
谈谈MySQL中的锁
谈谈MySQL中的锁 锁的定义 在生活中锁的例子就非常多了,所以应该很容易理解锁的含义.在计算机领域,可以这样来概述,锁是计算机协调多个进行进程并发访问某一资源的机制. 在数据库中,锁也是一个 ...
Spark RDD到底是个什么东西
前言 用Spark有一段时间了,但是感觉还是停留在表面,对于Spark的RDD的理解还是停留在概念上,即只知道它是个弹性分布式数据集,其他的一概不知 有点略显惭愧.下面记录下我对RDD的新的理解. 官 ...
随机推荐
做办公用品、文具方面的 B2C 是否有前景呢？
企乐买现在正在做这方面的事,从市场角度来说需求是有的,客单价和重复购买率都还可以,但是也存在几个致命问题使得施展不开举步维艰: 1.中国特有的市场环境:在美国企业办公用品一般都是网上采购,避免灰色的东 ...
springmvc常用注解标签详解
1.@Controller 在SpringMVC 中,控制器Controller 负责处理由DispatcherServlet 分发的请求,它把用户请求的数据经过业务处理层处理之后封装成一个Model ...
unity3d中获得物体的尺寸(size)
1:获得诸如Plane.Cube的size. 1):可以为它们添加Collider,然后使用XXX.collider.bounds.size;该方法获得的size和缩放比例有关,是一一对应的,缩 ...
谈论C++当然结果
C++编程课程的考试已经结束.这是第一次OJCBT.摸着石头过河,考试没有给学生理解的说法.现在尘埃落定.一些交代. 先说大的成就的治疗原则.事实上,有很多的纠结. 按理说,合格的太,无法挂.但实际情 ...
XML约束
XML约束--能够看懂约束内容,根据约束内容写出符合规则的xml文件. DTD约束 1)导入dtd方式 内部导入
[SignalR]Self-Host
原文:[SignalR]Self-Host SignalR 的Self-Host,可以将客户端脚本需要调用的服务端后台代码寄宿在诸如控制台应用程序中,作为寄宿端需要.NET 4.5以及jquery.s ...
Sass与Compress实战：第五章
概要:第5章展示了Compass如何使你免去编写跨浏览器的CSS3的痛苦. 本章内容: ● 用Compass的CSS3模块创建跨浏览器的CSS3样式表 ● 在低版本IE中支持一些CSS3的特性 ● C ...
jquery的过滤学习
$("p").eq(1) 匹配下标为1的p标签 $("p").hasClass("a")匹配所有p标签class中包含a的 ...
Mybatis执行BaseExecutor(二)
BaseExecutor是Executor的一个子类,是一个抽象类,其实现了接口Executor的部分方法,并提供了三个抽象方法doUpdate.doFlushStatements和doQuery在他 ...
poj1562 Oil Deposits 深搜模板题
题目描述: Description The GeoSurvComp geologic survey company is responsible for detecting underground o ...
188
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
