正文

union 执行流程

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

通过执行 explain 命令可以看到 union 使用到了临时表。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	t1	NULL	index	NULL	PRIMARY	4	NULL	2	100.00	Backward index scan; Using index
3	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary

• 创建一个内存临时表只有一个字段 f。
• 执行第一个子查询，把 1000 放入。
• 执行第二个子查询，搜到 1000 和 999，因为 1000 已经存在了，违反了唯一性约束所以 999 入库成功。
• 把结果集返回，删除临时表。

但是 union all 就不需要创建临时表了，union all 不去重，直接把两个子查询的结果一起返回就行了。

group by 执行流程

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

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	PRIMARY,a	a	5	NULL	1000	100.00	Using index; Using temporary; Using filesort

注意

在新版本中是没有 Using filesort 的，老版本要是想要不排序后面需要加上 order by null。

用到了覆盖索引 a，用到了临时表，用到了排序。

执行流程是

• 创建一个临时表，有两个字段 m 和 c，m 为主键。
• 覆盖索引，扫描 a 的索引树，得到 id 然后 %10 的 x，如果临时表中不存在就入表 (x,1) 如果存在就在基础上 c+1。遍历操作。
• 排序，返回。

这个例子里由于临时表只有 10 行，内存可以放得下，因此全程只使用了内存临时表。但是，内存临时表的大小是有限制的，参数 tmp_table_size 就是控制这个内存大小的，默认是 16M。

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

这里例子中就会有 100 行记录，内存临时表就放不下了，这个时候就会把内存临时表改成磁盘表引擎为 InnoDb。

索引优化 group by

需要先弄明白执行 group by 语句为什么需要临时表？

group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的（而真实情况中也是这样的，比如根据性别或者什么字段去分组，都是无序的），所以我们就需要有一个临时表，来记录并统计结果。

不让 group by 产生临时表的方法就是让查询出来的数据有序。还拿这个 id%100 来说。

• 当碰到第一个 1 的时候，已经知道累积了 X 个 0，结果集里的第一行就是 (0,X);
• 当碰到第一个 2 的时候，已经知道累积了 Y 个 1，结果集里的第二行就是 (1,Y);

MySQL 5.7 版本支持了 generated column 机制，可以把 id%100 的结果当做这个列的值用来计算。

点击查看

在 MySQL 中，Generated Column 也被称为虚拟列或计算列。这种列的值是在列定义时通过一个计算表达式得到的。

定义 Generated Column 列的语法如下：

列名 类型 [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string']

其中：

• AS (expr) 用于生成计算列值的表达式。
• VIRTUAL 或 STORED 关键字表示是否存储计算列的值：
  • VIRTUAL ：列值不存储，虚拟列不占用存储空间，默认设置为 VIRTUAL。
  • STORED ：在添加或更新行时计算并存储列值。存储列需要存储空间，并且可以创建索引。
  • 没有明确指定 z 列是 VIRTUAL 还是 STORED。在这种情况下，MySQL 默认将其设置为 VIRTUAL。

计算列表达式必须遵循以下规则：

1. 允许使用文本、内置函数和运算符，但不能使用返回值不确定的函数，比如 NOW ()。
2. 不允许使用存储函数和用户定义函数。
3. 不允许使用存储过程和函数参数。
4. 不允许使用变量（系统变量、用户定义变量和存储程序的局部变量）。
5. 不允许子查询。
6. 计算列在定义时可以引用其他的计算列，但只能引用表定义中较早出现的列。
7. 可以在计算列上创建索引，但不能在 VIRTUAL 类型的计算列上创建聚集索引。

例如，假设我们有一个销售表，其中包含商品 ID、商品名称、单价和数量。我们可以定义一个 Generated Column 列 amount ，其值为 unit_price * quantity ：

CREATE TABLE sales(
    goods_id INT PRIMARY KEY,
    goods_name CHAR(20),
    unit_price INT,
    quantity INT,
    amount INT GENERATED ALWAYS AS (unit_price * quantity)
);

当我们插入数据时， amount 列的值会自动计算：

INSERT INTO sales(goods_id, goods_name, unit_price, quantity) VALUES(100101, 'Apple', 2, 4);
SELECT * FROM sales;

结果为：

+----------+------------+------------+----------+--------+
| goods_id | goods_name | unit_price | quantity | amount |
+----------+------------+------------+----------+--------+
| 100101   | Apple      | 2          | 4        | 8      |
+----------+------------+------------+----------+--------+

alter table t1 add column z int generated always as(id % 100), add index(z);
select z, count(*) as c from t1 group by z;

再通过 explain 得到的结果中就没有了临时表和排序了。

直接排序优化 group by

如果碰上不适合创建索引的场景，我们还是要老老实实做排序的，一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照 “先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，这个操作显然不如直接放在磁盘临时表中。

在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。而 MySQL 的优化器会使用数组来存储这些数据，因为从磁盘的角度来考虑，临时表用 B + 树来存储比如数组效率高。

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

这个 SQL 的流程是

1. 创建 sort_buffer，然后根据 t1 的索引字段 a，得到 id 值。
2. 然后把 id%100 的结果放到 sort_buffer 中记为 m，根据 m 进行排序。
3. 排序完成后形成一个数组，返回给客户端。

通过 explain 看到结果只用到了覆盖索引和排序。

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

1. 如果语句执行一边读取数据，读取到的数据就是结果，这样就不用使用到内部临时表。否则就需要。
2. 排序，分组，去重等可能会使用内存临时表。
3. 利用到二维表特性的也可能会用到临时表，比如 union 有去重操作，group by 会用到一个字段积累计数。