Skip to content

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

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

示例

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

> drop table if exists t1;
> 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);
> insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
> 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');
> insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
> 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');
> 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');
> 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');
> insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
> 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');
> 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 的例子:

> SELECT /*+ HASH_AGG() */ count(*) FROM t1;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT /*+ HASH_AGG() */ count(*) FROM t1;
+-------------------------------------------------+
| QUERY PLAN                                      |
+-------------------------------------------------+
| Project                                         |
|   ->  Aggregate                                 |
|         Aggregate Functions: starcount(1)       |
|         ->  Table Scan on db1.t1                |
|               Aggregate Functions: starcount(1) |
+-------------------------------------------------+
5 rows in set (0.00 sec)