从SQL执行原理看调优本质：group by | IT宅-帅旋的专业知识分享

1、完全走索引

我们给t30加一个索引：

alter table t30 add index idx_c(c);

执行以下group bysql：

select c, count(*) from t30 group by c;

执行计划如下：

发现这里只用到了索引，原因是idx_c索引本身就是按照c排序好的，那么直接顺序扫描idx_c索引，可以直接统计到每一个c值有多少条记录，无需做其他的统计了。

2、临时表

现在我们把刚刚的idx_c索引给删掉，执行以下sql：

select c, count(*) from t30 group by c order by null;

为了避免排序，所以我们这里添加了 order by null，表示不排序。

执行计划如下：

可以发现，这里用到了内存临时表。其执行流程如下：

1. 扫描t30聚集索引；
2. 建立一个临时表，以字段c为主键，依次把扫描t30的记录通过临时表的字段c进行累加；
3. 把最后累加得到的临时表返回给客户端。

3、临时表 + 排序

如果我们把上一步的order by null去掉，默认情况下，group by的结果是会通过c字段排序的。我们看看其执行计划：

可以发现，这里除了用到临时表，还用到了排序。

我们进一步看看其执行的OPTIMIZER_TRACE日志：

"steps": [
  {
    "creating_tmp_table": {
      "tmp_table_info": {
        "table": "intermediate_tmp_table",  // 创建中间临时表
        "row_length": 13,
        "key_length": 4,
        "unique_constraint": false,
        "location": "memory (heap)",
        "row_limit_estimate": 1290555
      }
    }
  },
  {
    "filesort_information": [
      {
        "direction": "asc",
        "table": "intermediate_tmp_table",
        "field": "c"
      }
    ],
    "filesort_priority_queue_optimization": {
      "usable": false,
      "cause": "not applicable (no LIMIT)" // 由于没有 limit，不采用优先级队列排序
    },
    "filesort_execution": [
    ],
    "filesort_summary": {
      "rows": 7,
      "examined_rows": 7,
      "number_of_tmp_files": 0,
      "sort_buffer_size": 344,
      "sort_mode": "<sort_key, rowid>"  // rowid排序模式
    }
  }
]

通过日志也可以发现，这里用到了中间临时表，由于没有limit限制条数，这里没有用到优先级队列排序，这里的排序模式为sort_key, rowid。其执行流程如下：

1. 扫描t30聚集索引；
2. 建立一个临时表，以字段c为主键，依次把扫描t30的记录通过临时表的字段c进行累加；
3. 把得到的临时表放入sort buffer进行排序，这里通过rowid进行排序；
4. 通过排序好的rowid回临时表查找需要的字段，返回给客户端。

临时表是存放在磁盘还是内存？

tmp_table_size 参数用于设置内存临时表的大小，如果临时表超过这个大小，那么会转为磁盘临时表：

可以通过以下sql设置当前session中的内存临时表大小：SET tmp_table_size = 102400;

4、直接排序

查看官方文档的 SELECT Statement^[1]，可以发现SELECT后面可以使用许多修饰符来影响SQL的执行效果：

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [into_option]
    [FOR UPDATE | LOCK IN SHARE MODE]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

这里我们重点关注下这两个：

• SQL_BIG_RESULT：可以在包含group by 和distinct的SQL中使用，提醒优化器查询数据量很大，这个时候MySQL会直接选用磁盘临时表取代内存临时表，避免执行过程中发现内存不足才转为磁盘临时表。这个时候更倾向于使用排序取代二维临时表统计结果。后面我们会演示这样的案例；
• SQL_SMALL_RESULT：可以在包含group by 和distinct的SQL中使用，提醒优化器数据量很小，提醒优化器直接选用内存临时表，这样会通过临时表统计，而不是排序。

当然，在平时工作中，不是特定的调优场景，以上两个修饰符还是比较少用到的。

接下来我们就通过例子来说明下使用了SQL_BIG_RESULT修饰符的SQL执行流程。

有如下SQL：

select SQL_BIG_RESULT c, count(*) from t30 group by c;

执行计划如下：

可以发现，这里只用到了排序，没有用到索引或者临时表。这里用到了SQL_BIG_RESULT修饰符，告诉优化器group by的数据量很大，直接选用磁盘临时表，但磁盘临时表存储效率不高，最终优化器使用数组排序的方式来完成这个查询。（当然，这个例子实际的结果集并不大，只是作为演示用）

其执行结果如下：

1. 扫描t30表，逐行的把c字段放入sort buffer；
2. 在sort buffer中对c字段进行排序，得到一个排序好的c数组；
3. 遍历这个排序好的c数组，统计结果并输出。

5、group by 优化建议

• 尽量让group by走索引，能最大程度的提高效率；
• 如果group by结果不需要排序，那么可以加上order by null，避免进行排序；
• 如果group by的数据量很大，可以使用SQL_BIG_RESULT修饰符，提醒优化器应该使用排序算法得到group的结果。

References

---

1. 13.2.9 SELECT Statement. Retrieved from https://dev.mysql.com/doc/refman/5.7/en/select.html ↩︎