mysql版本:8.0.19
create table t1(id int primary key,gender varchar(20),age int);
insert into t1 values(1,'f',10);
insert into t1 values(2,'f',11);
insert into t1 values(3,'f',9);
insert into t1 values(4,'m',10);
insert into t1 values(5,'m',11);
insert into t1 values(6,'m',9);
select gender,group_concat(age order age desc) from t1 group by gender;
mysql的执行计划
mysql> explain analyze select gender,group_concat(age order by age desc) from t1 group by gender;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Group aggregate: group_concat(t1.age order by t1.age DESC separator ',') (actual time=0.085..0.087 rows=2 loops=1)
-> Sort: t1.gender (cost=0.85 rows=6) (actual time=0.071..0.071 rows=6 loops=1)
-> Table scan on t1 (actual time=0.037..0.042 rows=6 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> explain select gender,group_concat(age order by age desc) from t1 group by gender;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
对于要对gender字段排序有点困惑？
mysql> explain analyze select gender,group_concat(age order by age desc) from t1 group by gender;
+-----------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_4 | 8000.00 | 2 | root | | time:6.15ms, loops:2, Concurrency:5 | test.t1.gender, Column#4 | 12.4 KB | N/A |
| └─HashAgg_5 | 8000.00 | 2 | root | | time:6.06ms, loops:2 | group by:Column#8, funcs:group_concat(Column#5 order by Column#6 desc separator ",")->Column#4, funcs:firstrow(Column#7)->test.t1.gender | 4.09 KB | 0 Bytes |
| └─Projection_12 | 10000.00 | 6 | root | | time:3.87ms, loops:2, Concurrency:5 | cast(test.t1.age, var_string(20))->Column#5, test.t1.age, test.t1.gender, test.t1.gender | 16.1 KB | N/A |
| └─TableReader_9 | 10000.00 | 6 | root | | time:1.33ms, loops:2, cop_task: {num: 1, max: 1.45ms, proc_keys: 6, rpc_num: 1, rpc_time: 1.34ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_8 | 339 Bytes | N/A |
| └─TableFullScan_8 | 10000.00 | 6 | cop[tikv] | table:t1 | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 6, total_process_keys_size: 246, total_keys: 7, get_snapshot_time: 34.9µs, rocksdb: {key_skipped_count: 6, block: {cache_hit_count: 1}}} | keep order:false, stats:pseudo | N/A | N/A |
+-----------------------------+----------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
5 rows in set, 1 warning (0.01 sec)
tidb的执行计划不需要的
