MySQL内部临时表的具体使用(2)-木庄网络博客

Using filesort：表示需要排序

执行过程

创建内存临时表，表里有两个字段m和c，m为主键
扫描t1的索引a，依次取出叶子节点上的id值，计算id%10，记为x
- 如果内存临时表中没有主键为x的行，插入一行记录(x,1)
- 如果内存临时表中有主键为x的行，将x这一行的c值加1
遍历完成后，再根据字段m做排序，得到结果集返回给客户端

排序过程

ORDER BY NULL

-- 跳过最后的排序阶段，直接从临时表中取回数据
mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY,a     | a    | 5       | NULL | 1000 | Using index; Using temporary |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+

-- t1中的数据是从1开始的
mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
+------+-----+
| m    | c   |
+------+-----+
|    1 | 100 |
|    2 | 100 |
|    3 | 100 |
|    4 | 100 |
|    5 | 100 |
|    6 | 100 |
|    7 | 100 |
|    8 | 100 |
|    9 | 100 |
|    0 | 100 |
+------+-----+

内存不足

SET tmp_table_size=1024;

执行语句

-- 内存临时表的上限为1024 Bytes，但内存临时表不能完全放下100行数据，内存临时表会转成磁盘临时表，默认采用InnoDB引擎
-- 如果t1很大，这个查询需要的磁盘临时表就会占用大量的磁盘空间
mysql> SELECT id%100 AS m, count(*) AS c FROM t1 GROUP BY m ORDER BY NULL LIMIT 10;
+------+----+
| m    | c  |
+------+----+
|    1 | 10 |
|    2 | 10 |
|    3 | 10 |
|    4 | 10 |
|    5 | 10 |
|    6 | 10 |
|    7 | 10 |
|    8 | 10 |
|    9 | 10 |
|   10 | 10 |
+------+----+

优化方案

优化索引

不论使用内存临时表还是磁盘临时表，GROUP BY都需要构造一个带唯一索引的表，执行代价较高

需要临时表的原因：每一行的id%100是无序的，因此需要临时表，来记录并统计结果

如果可以确保输入的数据是有序的，那么计算GROUP BY时，只需要
从左到右顺序扫描，依次累加即可

当碰到第一个1的时候，已经累积了X个0，结果集里的第一行为(0,X)
当碰到第一个2的时候，已经累积了Y个1，结果集里的第一行为(1,Y)
整个过程不需要临时表，也不需要排序

-- MySQL 5.7上执行
ALTER TABLE t1 ADD COLUMN z INT GENERATED ALWAYS AS(id % 100), ADD INDEX(z);
-- 使用了覆盖索引，不需要临时表，也不需要排序
mysql> EXPLAIN SELECT z, COUNT(*) AS c FROM t1 GROUP BY z;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | z             | z    | 5       | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
2

直接排序

一个GROUP BY语句需要放到临时表的数据量特别大，还是按照先放在内存临时表，再退化成磁盘临时表

可以直接用磁盘临时表的形式，在GROUP BY语句中SQL_BIG_RESULT（告诉优化器涉及的数据量很大）

磁盘临时表原本采用B+树存储，存储效率还不如数组，优化器看到SQL_BIG_RESULT，会直接用数组存储

即放弃使用临时表，直接进入排序阶段

执行过程

-- 没有再使用临时表，而是直接使用了排序算法
mysql> EXPLAIN SELECT SQL_BIG_RESULT id%100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | index | PRIMARY,a     | a    | 5       | NULL | 1000 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+

初始化sort_buffer，确定放入一个整型字段，记为m

扫描t1的索引a，依次取出里面的id值，将id%100的值放入sort_buffer

扫描完成后，对sort_buffer的字段m做排序（sort_buffer内存不够时，会利用磁盘临时文件辅助排序）

排序完成后，得到一个有序数组，遍历有序数组，得到每个值出现的次数（类似上面优化索引的方式）

对比DISTINCT

-- 标准SQL，SELECT部分添加一个聚合函数COUNT(*)
SELECT a,COUNT(*) FROM t GROUP BY a ORDER BY NULL;
-- 非标准SQL
SELECT a FROM t GROUP BY a ORDER BY NULL;

SELECT DISTINCT a FROM t;

标准SQL：按照字段a分组，计算每组a出现的次数

非标准SQL：没有了COUNT(*)，不再需要执行计算总数的逻辑

按照字段a分组，相同的a的值只返回一行，与DISTINCT语义一致

如果不需要执行聚合函数，DISTINCT和GROUP BY的语义、执行流程和执行性能是相同的

创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引
遍历表t，依次取出数据插入临时表中
- 如果发现唯一键冲突，就跳过
- 否则插入成功
遍历完成后，将临时表作为结果集返回给客户端

小结

用到内部临时表的场景
- 如果语句执行过程中可以一边读数据，一边得到结果，是不需要额外内存的
- 否则需要额外内存来保存中间结果
join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构
如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表如果对GROUP BY语句的结果没有明确的排序要求，加上ORDER BY NULL（MySQL 5.6）
尽量让GROUP BY过程用上索引，确认EXPLAIN结果没有Using temporary和Using filesort
如果GROUP BY需要统计的数据量不大，尽量使用内存临时表（可以适当调大tmp_table_size）
如果数据量实在太大，使用SQL_BIG_RESULT来告诉优化器直接使用排序算法（跳过临时表）