用 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)