MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43178 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

37 | 什么时候会使用内部临时表?

林晓斌 2019-02-06
今天是大年初二,在开始我们今天的学习之前,我要先和你道一声春节快乐!
第 16第 34篇文章中,我分别和你介绍了 sort buffer、内存临时表和 join buffer。这三个数据结构都是用来存放语句执行过程中的中间数据,以辅助 SQL 语句的执行的。其中,我们在排序的时候用到了 sort buffer,在使用 join 语句的时候用到了 join buffer。
然后,你可能会有这样的疑问,MySQL 什么时候会使用内部临时表呢?
今天这篇文章,我就先给你举两个需要用到内部临时表的例子,来看看内部临时表是怎么工作的。然后,我们再来分析,什么情况下会使用内部临时表。

union 执行流程

为了便于量化分析,我用下面的表 t1 来举例。
create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t1 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
然后,我们执行下面这条语句:
(select 1000 as f) union (select id from t1 order by id desc limit 2);
这条语句用到了 union,它的语义是,取这两个子查询结果的并集。并集的意思就是这两个集合加起来,重复的行只保留一行。
下图是这个语句的 explain 结果。
图 1 union 语句 explain 结果
可以看到:
第二行的 key=PRIMARY,说明第二个子句用到了索引 id。
第三行的 Extra 字段,表示在对子查询的结果集做 union 的时候,使用了临时表 (Using temporary)。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(42)

  • 老杨同志
    请教一个问题:如果只需要去重,不需要执行聚合函数,distinct 和group by那种效率高一些呢?

    课后习题:
    图8,把统计结果存内存临时表,不排序。id是从1到1000,模10的结果顺序就是1、2、3、4、5。。。
    图9,老师把tmp_table_size改小了,内存临时表装不下,改用磁盘临时表。根据老师讲的流程,id取模的结果,排序后存入临时表,临时的数据应该是0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,2,......
    从这个磁盘临时表读取数据汇总的结果的顺序就是0,1,2,3,4,5。。。

    作者回复: 新年好

    好问题,我加到后面文章中。
    简单说下结论,只需要去重的话,如果没有limit,是一样的;
    有limit的话,distinct 快些。

    漂亮的回答👍

    2019-02-06
    19
  • 长杰
    图九使用的是磁盘临时表,磁盘临时表使用的引擎是innodb,innodb是索引组织表,按主键顺序存储数据,所以是按照m字段有序的。

    作者回复: 👍🏿
    春节快乐

    2019-02-06
    12
  • 天王
    内部临时表,和sort buffer,join buffer一样,都用来存放语句执行过程中的中间数据,辅助语句的执行。
    使用用法 using temporary。

    使用场景:1 数据一边查询,一边直接得到结果,不需要额外内存。比如:group by 需要计算。

              2 join_buffer 是无序数组(单字段,可以重复),sort_buffer 有序数组,内部临时表是二维结构

    3 用到二维表的特性需要用到内部临时表,比如 distinct ,group by

    优化:1 group by 字段加索引

          2 扩大临时表内存大小

          3 如果数据量比较大,直接使用磁盘临时表

          4 如果分组之后,不需要排序,手工 order by null



    group by 的顺序

    1 创建一个内部临时表,初始化字段 a(分组字段) b(数量)

    2 扫描表,一个个的判断,a字段值在临时表不存在,则插入(2,1),如果存在,比如(2,1),则加1,成为(2,2)

    3 分组之后,默认会进行排序,如果不需要顺序,用 order by null。

    作者回复: 👍很好的总结

    2019-02-28
    9
  • Sinyo
    老师好,
    我用infobright列式存储引擎explain出来一个group by语句,
    发现也会用到内存临时表和文件排序、然后使用SQL_BIG_RESULT也有同样优化效果;
    这是不是说明sort_buffer、join_buffer、内存临时表和磁盘临时表与数据库引擎类型其实是独立开的呢?

    作者回复: 是的,非常好的验证和思考👍

    sort_buffer、join_buffer、内存临时表和磁盘临时表 都是server层的,引擎间共用

    2019-02-28
    6
  • IceGeek17
    文中说,SQL_BIG_RESULT这个hint,用来告诉优化器直接用磁盘临时表,
    对于文中的例子 select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
    则没有使用临时表,直接用了排序算法,这里有点不太明白,SQL_BIG_RESULT这个hint到底用来提示的是什么(是用磁盘临时表,还是就是直接使用排序,不用临时表)?

    文中 “MySQL优化器一看,磁盘临时表B+数存储,存储效率不如数据来的高。那从磁盘空间考虑,还是直接用数组来存”,这段怎么理解?
    是指MySQL优化器,直接忽略B+数的磁盘临时表(这里忽略的依据是什么?),并且从文中后续的分析来看,也没有用内存临时表,而是直接sort buffer排序,老师再详细解释下?

    对于InnoDB的磁盘临时表,索引结构是B+数;那对于内存临时表,是不是就是数组结构?数组临时表的主键就是每个数组元素?

    作者回复: 1. 最后的结果是“直接使用排序”
    2. 就是认为用sort_buffer直接排序性能更好,所以就没有使用内存临时表或磁盘临时表
    3. 没有索引的内存表可以认为就是数组; 主键不是,内存表的表结构,可以看一下38篇

    2019-02-21
    6
  • Long
    老师,新年好! :-)

    有几个版本差异的问题:
    (1)图1中的执行计划应该是5.7版本以后的吧,貌似没找到说在哪个环境,我在5.6和5.7分别测试了,id = 2的那个rows,在5.6版本(5.6.26)是1000,在5.7版本是2行。应该是5.7做的优化吧?

    (2)图 9 group + order by null 的结果(此盘临时表),这里面mysql5.6里面执行的结果是(1,10),(2,10)...(10,10),执行计划都是只有一样,没找到差异。
    跟踪下了下optimizer trace,发现问题应该是在临时表空间满的的时候,mysql5.7用的是:converting_tmp_table_to_ondisk "location": "disk (InnoDB)",,而mysql 5.6用的是converting_tmp_table_to_myisam "location": "disk (MyISAM)"的原因导致的。
    查了下参数:
    default_tmp_storage_engine。(5.6,5.7当前值都是innodb)
    internal_tmp_disk_storage_engine(只有5.7有这个参数,当前值是innodb),5.6应该是默认磁盘临时表就是MyISAM引擎的了,由于本地测试环境那个临时表的目录下找不到临时文件,也没法继续分析了。。。

    至于为什么MySQL 5.6中结果展示m字段不是0-9而是1-10,还得请老师帮忙解答下了。


    还有几个小问题,为了方便解答,序号统一了:
    (3)在阅读mysql执行计划的时候,看了网上有很多说法,也参考了mysql官网对id(select_id)的解释:
    id (JSON name: select_id)
    The SELECT identifier. This is the sequential number of the SELECT within the query.(感觉这个读起来也有点歧义,这个sequential字面解释感觉只有顺序的号码,并咩有说执行顺序)
    比如图1,文中解释就是从ID小的往大的执行的,网上有很多其他说法,有的是说ID从大到小执行,遇到ID一样的,就从上往下执行。有的说是从小往大顺序执行。不知道老师是否可以官方讲解下。

    (4)我发现想搞懂一个原理,并且讲清楚让别人明白,真的是很有难度,非常感谢老师的分享。这次专栏结束,还会推出的新的专栏吗? 非常期待。

    作者回复: 1. 是的,我默认是用5.7做的验证,这里5.7做了优化。
    2. 其实你已经找到原因了,就是因为5.6的默认临时表是myisam表,而myisam表是堆表,这里的堆表,跟38篇介绍的memory引擎是类似的,你看了那篇应该就能知道为什么堆表是最后显示0那行了
    3. 好问题,不同id从大到小,相同id从上到下
    4. 多谢你的鼓励,还没定,先休息下^_^

    不好意思,你发的第一天就看到了,回复晚了😆

    2019-02-10
    5
  • Li Shunduo
    请问Group By部分的第一个语句 explain select id%10 as m, count(*) as c from t1 group by m;为什么选择的是索引a,而不是primary key?如果字段a上有空值,使用索引a岂不是就不能取到所有的id值了?

    作者回复: 因为索引c的信息也足够,而且比主键索引小,使用索引c更会好。

    “如果字段a上有空值,使用索引a岂不是就不能取到所有的id值了?”,不会的

    2019-02-07
    5
  • 梦康
    实践发现文中描述的 group by 执行过程中解释不通。案例如下

    select `aid`,sum(`pv`) as num from article_rank force index(idx_day_aid_pv) where `day`>20190115 group by aid order by num desc LIMIT 10;

    内存临时表不够,需要写入磁盘

    select `aid`,sum(`pv`) as num from article_rank force index(idx_aid_day_pv) where `day`>20190115 group by aid order by num desc LIMIT 10;

    内存临时表足够。

    选的索引不一样,但是最后筛选出来的总行应该是一样的呀,所以现在更加困惑了。

    作者回复: 看索引的名字猜测, idx_aid_day_pv 就是(adid, day,pv)这三个字段的联合索引?

    这样的话,第二个语句就会顺序遍历索引,只要找到10个不同的aid就可以了,这个索引不用全部遍历;这样可以减少很多写入临时表的数据;

    但是第一个语句是day开头的, 这样就必须将所有`day`&gt;20190115的记录都存到临时表中,这个要写入内存表的数据行数肯定比第二个语句多的

    所以核心就是这两个语句需要放入临时表的行数不同。

    2019-02-11
    2
    3
  • 兔斯基
    老师,关于排序有几个问题。
    order by id,主键
    order by null,
    不加order by
    这三种写法哪种执行效率更高一些?后面两者是不是等价的?

    作者回复: 这三种写法语义上不一样。。

    如果对返回结果没有顺序要求,那写上order by null肯定是好的。

    “order by null”和“不加order by”不等价,咱们文中有说哈

    2019-02-11
    3
  • null
    疑问:
    set tmp_table_size=1024;
    select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

    结果不需要排序,为什么不像第 17 章《如何正确显示随机消息》,采用优先队列排序算法。这里只需要维护一个 10 个元素的堆就可以了。

    ----

    课后思考题:
    使用磁盘临时表(engine=innodb),排首位的是 0,而内存临时表(engine=memory)却是 1。为何会有这种区别?

    扫描计算的过程都是一样的,扫描 a 索引的时候,都是先扫描 1,再扫描 10。
    m 字段是磁盘临时表和内存临时表的主键。

    磁盘临时表是以 B+ 树存储数据,插入 m=0 时,会被动态调整到首位。而内存临时表是以数组保存数据,每次都是往数组末端添加元素,不会动态调整。因此产生了这个差异。
    2019-08-09
    1
  • 进阶的码农
    select id%10 as m, count(*) as c from t1 group by m;
    我执行这条语句 第一行是1 没有经过排序,我explain 这条语句也没有显示sort 但是显示用了临时表,可能是什么原因的,和mysql版本有关吗?还是配置?
    2019-03-07
    1
    1
  • Sinyo
    老师好,好奇怪我用infobright列式存储引擎explain一条union语句,发现是不用临时表的,测试了很多次:
    比如:
    EXPLAIN
    (SELECT 111)
    UNION
    (SELECT 222)

    EXPLAIN
    (SELECT 111)
    UNION
    (SELECT id from aaa)

    EXPLAIN
    (SELECT id from bbb)
    UNION
    (SELECT id from aaa)
    以上都没有用到临时表;
    这应该说明不同引擎的逻辑层还是存在差异的把。
    2019-03-07
    1
  • Sinyo
    谢谢老师解惑,
    再次请教老师,那其他引擎数据库在server层建的磁盘临时表是什么引擎呢?

    作者回复: 需要创建临时表的时候,与当前访问数据的引擎无关,都是默认创建内存临时表,内存不够了转磁盘临时表(默认是innodb 表)

    2019-03-01
    1
  • 黄文韬
    老师,有两个问题不是很清楚:
    1. 图三显示union all没有使用临时表,但是我本地explain看到union all和union的执行计划是一样的,不知道为什么?是和版本有关系还是其他设置?本地版本5.6
    2.图四 不太理解为什么会用filesort,因为我看你后面执行流程是说会创建一个临时表,主键是m,那为啥不可以直接根据m来排序,而要用到filesort外部排序,还是这个filesort是指的不是按表中索引的排序都会是filesort??
    感谢

    作者回复: 1. 啊 比较奇怪,你可否给一下截图,(比如发个微博,贴图at我),带上表结构哈
    2. 好问题,其实这个是要结合下一篇文章的知识点,内存表的主键不是保证有序的

    2019-02-15
    1
    1
  • Smile
    当碰到第一个 2 的时候,已经知道累积了 Y 个 1,结果集里的第一行就是 (1,Y);
    ----
    应该是 结果集里的第 二 行 吧



    作者回复: 对的,👍🏿细致

    发起勘误了,新年快乐

    2019-02-11
    1
  • 牛牛
    新年快乐~、感谢有您~^_^~

    作者回复: 新年快乐~🤝

    2019-02-06
    1
  • poppy
    老师,春节快乐,过年还在更新,辛苦辛苦。
    关于思考题,我的理解是图8中的查询是使用了内存临时表,存储的顺序就是id%10的值的插入顺序,而图9中的查询,由于内存临时表大小无法满足,所以使用了磁盘临时表,对于InnoDB来说,就是对应B+树这种数据结构,这里会按照id%100(即m)的大小顺序来存储的,所以返回的结果当然也是有序的

    作者回复: 新年好~

    👍

    2019-02-06
    1
  • 八百
    春节快乐,老师。谢谢你让我学到不少知识

    作者回复: 新年快乐🤝

    2019-02-06
    1
  • 某、人
    老师春节快乐,辛苦了

    作者回复: 春节快乐,🤝

    2019-02-06
    1
  • Garen
    老师您好:
    看了文章,group by 不会使用磁盘临时表,会使用排序算法。我原以为不使用磁盘临时表是因为磁盘临时表就能获得count(*)了,如果多一些查询结果,应该会使用磁盘临时表。但是我执行下面的语句,依旧使用了排序算法。为什么会这样呢?

    explain select SQL_BIG_RESULT id%100 as m, count(*) as c, max(id),min(id),id,a,b from t1 group by m;
    2019-10-31
收起评论
42
返回
顶部