记一个特定 sql 的执行流程

缘由

先前群里同事抛出了个问题，

select cnt, count(cnt) as num from test where status = 1 group by cnt having num > 0 order by num desc limit 10;

这条 sql 的执行顺序是什么，最近刚三刷完 MySQL实战45讲，趁着春节没回家，今天就写成一篇博客记录下。

准备

drop table if exists test;
create table test(id int primary key, cnt int, `status` tinyint);
drop procedure idata;
delimiter ;;

create procedure idata()
begin
  declare i int;

  set i=1;
  while(i<=1000) do
    insert into test values(i, i%10, i%2);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

执行计划

mysql> EXPLAIN select cnt, count(cnt) as num from test where status = 1 group by cnt having num > 0 order by num desc limit 10;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)

先看看执行计划，type 为 ALL 说明是全表扫描，因为 where 条件那个列没有建索引，所以后面 key 相关的列都为 NULL。着重看下 Extra，

• Using where，表示本次查询要进行筛选过滤，也就是此次搜索需要在 server 层进行判断。
• Using temporary，表示的是需要使用临时表。
• Using filesort，本次查询语句中有 order by，且排序依照的字段不在本次使用的索引中，不能自然有序，需要进行额外的排序工作。

执行流程

这个语句的大体逻辑是把表 test 里的数据，按照 cnt 进行分组统计，并按照 cnt 的结果排序后输出。

1. 创建内存临时表，表里有两个字段 cnt 和 num，主键是 cnt；
2. 全表扫描 test，依次取出叶子节点上的 cnt 值，并过滤 status 值为非 1 的行；
   • 如果临时表中没有主键为 cnt 的行，就插入一个记录 (cnt,1);
   • 如果表中有主键为 cnt 的行，就将 cnt 这一行的 num 值加 1；
3. 遍历完成后，再在内存表里过滤 num 小于 0 的行，最后根据字段 cnt 做排序，得到结果集并返回前 10 条记录给客户端。

几个参数

tmp_table_size

这个参数的值决定了执行计划里的 Using temporary 用的是内存临时表还是磁盘临时表。

mysql> show variables like '%tmp_table_size%';
+----------------+-----------+
| Variable_name  | Value     |
+----------------+-----------+
| tmp_table_size | 268435456 |
+----------------+-----------+
1 row in set (0.00 sec)

可以看到，我这台服务器上 MySQL 的内存临时表的大小为 256M，放入 test 表的数据都绰绰有余了。

注： MySQL 的 tmp_table_size 默认为 16M，可以依据服务器的性能适当调整。

max_heap_table_size

其实 tmp_table_size 这个参数还受到 max_heap_table_size 的影响，因为 max_heap_table_size 是内存引擎的表大小。

mysql> show variables like '%max_heap_table_size%';
+---------------------+-----------+
| Variable_name       | Value     |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
+---------------------+-----------+
1 row in set (0.00 sec)

一句话，是否生成磁盘临时表，取决于数据是否超过这两个参数的较小者。

sort_buffer_size

Extra 这个字段中的 Using filesort 表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，而控制这块内存大小的就是 sort_buffer_size 参数。

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

一旦要排序的数据超出了这个值，那么内存放不下时，就需要使用外部排序，也就是会使用临时文件进行排序了。

参考

MySQL实战45讲

max_heap_table_size

tmp_table_size