一.union执行流程

union：取两个查询结果的并集，并去重

(select 1000 as f) union (select id from t1 order by id desc limit 2)

执行流程：

  • 创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段
  • 执行第一个子查询，查到1000这个值，存入临时表
  • 执行第二个子查询：
  • 拿到第一行id=1000，试图插入临时表中。违反唯一性约束，所以插入失败
  • 然后继续执行

内存临时表起到暂存数据。计算规程中用上临时表主键id的唯一约束

二.group by执行流程

select id%10 as m,count(*) as c from t1 group by m;

执行流程：

  • 创建内存临时表，表里两个字段m和c，主键是m
  • 扫描表t1的索引a,依次取出叶子接待的,进行计算，计为X
  • 临时表没有主键x就插入
  • 临时表有X，将X这一行的c值加一
  • 根据字段m排序，将结果集返回给客户端

三.group by优化方法---索引

   group by语义：统计不同的值出现的个数

 根据分组调价创建索引，这样在索引中时有序的就不需要临时表

四.group by优化方法---直接排序

直接排序即可

五.总结

MySQL什么时候会使用内部临时表

  • 语句一边读数，一边直接得到结果，不需要额外内存来保存中间结果
  • join_buffer是无序数据，sort_buffer是有序数组，临时表是二维表结构
  • 执行逻辑需要二维表特性，有限考虑使用临时表，union需要用唯一索引约束，group by需要额外字段累计存数

group by几种实现算法

没有排序要求

语句后面加 order by null

group by -表上使用索引

确认方法是 epxplain 中没有使用 Using temporary 和 Using filesort 

统计数据量不大

只使用内部临时表，调大 tmp_table_size 参数，避免使用磁盘临时表

数据量太大

直接使用SQL_BIG_RESULT提示，让优化器直接使用排序算法