测试数据 :

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

union

union : 取这两个子查询结果的并集

• 重复的行只保留一行

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

explain 结果 :

• key=PRIMARY : 第二个子句用到索引 id
• Extra 字段 : Using temporary : union 时，用了临时表

union 执行流程 :

1. 创建内存临时表，有个整型字段 f (主键)
2. 执行第一个子查询，得到 1000 值，并存入临时表中
3. 执行第二个子查询：判断 id 是否存在临时表中 (唯一约束) ，不存在就插入
4. 从临时表取出数据，返回结果，并删除临时表

union all : 没有去重。不用临时表

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

explain 结果 :

• Extra : Using index : 用了覆盖索引，没有用临时表

group by

按 m 分组统计 :

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

explain 结果 :

• Using index : 用了覆盖索引，选择索引 a，不用回表
• Using temporary : 用了临时表
• Using filesort : 用了排序

执行流程 ：

1. 创建内存临时表，表中有 m (主键) , c
2. 扫描表 t1 的索引 a，并取 ID，计算 id%10 为 x
   1. 当临时表中没有 x ，就插入 (x, 1)
   2. 当表中有 x ，就将 x 的 c + 1
3. 遍历完成后，再根据 m 排序，并返回给客户端

sort_buffer 排序 :

对结果不排序 :

• 会跳过最后排序阶段，直接从临时表中返回数据
• 全程用内存临时表

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

当内存临时表存不下数据

• 会转成磁盘临时表 :

-- 内存临时表最大 1024 字节
set tmp_table_size=1024;

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

group by优化

索引

有序结构 :

• 当碰到 1 时，就累计 X 个 0，结果: (0, X)
• 当碰到 2 时，就累计 Y 个 1，结果: (1, Y)

MySQL 5.7 支持了 generated column 机制 : 实现列数据的关联更新

alter table t1 add column z int generated always as(id % 100), add index(z);

利用 z 索引 :

select z, count(*) as c 
from t1 group by z;

explain 结果 :

• Extra : 不用临时表，也不用排序

直接排序

临时表数据量较大 , 用 SQL_BIG_RESULT :

-- SQL_BIG_RESULT 提示 (hint) ,要用磁盘临时表
select SQL_BIG_RESULT id%100 as m, 
	count(*) as c 
from t1 group by m;

执行流程 :

1. 初始化 sort_buffer，确定放 m
2. 扫描表 t1 的索引 a，并取 ID，计算 id%100 值存入 sort_buffer 中
3. 扫描完成后，在 sort_buffer 中做排序 (当 sort_buffer 不够用，就用磁盘临时文件辅助排序)
4. 排序完成后，就得到了一个有序数组

explain 结果 :

• Extra : 没用临时表，用了排序算法

总结

group by 的总结：

• 对 group by 的结果没有排序要求，就加 order by null
• 尽量让 group by 用上表的索引，确认 explain 结果没有 Using temporary 和 Using filesort
• 当 group by 统计的数据量不大时，尽量用内存临时表；也可以适当调大 tmp_table_size ，避免用到磁盘临时表
• 当数据量实在太大，用 SQL_BIG_RESULT 提示，让优化器直接用排序算法