一、前言

在执行语句时候，记得多“食用”explian看看语句性能如何？

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();

二、正文

2.1.什么情况下会使用内部临时表？举个例子？例子的执行过程是什么？解释为什么需要使用内部临时表？

  • 使用union语句；2）(select 1000 as f) union (select id from t1 order by id desc limit 2)；3）先创建一个临时表，字段为f并且为主键。接着，执行前半句查询，得到 1000 这个值，并存入临时表中。执行后半句得到1000准备插入发现主键冲突，在拿到999插入成功。最后，从临时表中返回这两条记录，并删除临时表。4）因为要去重，如果使用union all语句就不需要使用内部临时表了
  • group by语句；2）select id%10 as m, count(*) as c from t1 group by m；3）先创建一个临时表，插入字段m和c；接着，扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 m；如果临时表中没有主键为 m的行，就插入一个记录 (m,1);如果表中有，就将 m 这一行的 c 值加 1。最后，再根据字段 m 做排序。4）因为要计算id%10

2.2.group by语句排序过程是什么？如何避免排序？8.0以后如何排序？如何避免建立临时表（有哪两种方式）？

  • 将内存临时表中的主键值和c字段取出放入到sort buffer中排序后直接返回给客户端。此处内存临时表大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。如果超过会转为磁盘临时表，默认Innodb引擎
  • 在后面追加order by null
  • 8.0以后对group by语句不在排序
  • 在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引。举例：alter table t1 add column z int generated always as(id % 100), add index(z)。
  • 使用SQL_BIG_RESULT参数，目的是为了不使用临时表直接在内存中排序后返回。主要使用场景是：一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，数据不够放再转成磁盘临时表”。内部原理：1）初始化 sort_buffer，确定放入一个整型字段，记为 m；2）扫描表 t1 的索引 a，依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中；3）扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用磁盘临时文件辅助排序）；4）排序完成后，就得到了一个有序数组；5）统计次数。

2.3.group by语句使用注意事项。（前面可以记不住，这里必须要记住）

  • 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
  • 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
  • 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
  • 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

三、评论点

3.1.内存表的数据组织结构是什么样的？时而数组时而主键索引？

--自己也母鸡，下一篇学习完总结。