用 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)
  8. mysql> EXPLAIN SELECT /*+ HASH_AGG() */ count(*) FROM t1;
  9. +-------------------------------------------+
  10. | QUERY PLAN |
  11. +-------------------------------------------+
  12. | Project |
  13. | -> Aggregate |
  14. | Aggregate Functions: starcount(1) |
  15. | -> Table Scan on db1.t1 |
  16. +-------------------------------------------+
  17. 4 rows in set (0.01 sec)