用 EXPLAIN 查看聚合查询执行计划

SQL 查询中可能会使用聚合计算，可以通过 EXPLAIN 语句来查看聚合查询的执行计划。

示例

我们这里准备一个简单的示例，帮助你理解使用 EXPLAIN 解读聚合查询的执行计划。

  1. > drop table if exists t1;
  2. > create table t1 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
  3. > insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
  4. > insert into t1 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
  5. > insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
  6. > insert into t1 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
  7. > insert into t1 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
  8. > insert into t1 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
  9. > insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
  10. > insert into t1 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
  11. > insert into t1 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');

Hash Aggregation

Hash Aggregation 算法在执行聚合时使用 Hash 表存储中间结果。此算法采用多线程并发优化，执行速度快，但与 Stream Aggregation 算法相比会消耗较多内存。

下面是一个使用 Hash Aggregation 的例子：

  1. > SELECT /*+ HASH_AGG() */ count(*) FROM t1;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |        9 |
  6. +----------+
  7. 1 row in set (0.01 sec)
  9. mysql> EXPLAIN SELECT /*+ HASH_AGG() */ count(*) FROM t1;
  10. +-------------------------------------------+
  11. | QUERY PLAN                                |
  12. +-------------------------------------------+
  13. | Project                                   |
  14. |   ->  Aggregate                           |
  15. |         Aggregate Functions: starcount(1) |
  16. |         ->  Table Scan on db1.t1          |
  17. +-------------------------------------------+
  18. 4 rows in set (0.01 sec)