SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qtyFROM lineitemWHERE l_shipdate <= '1998-09-02'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;
EXPLAIN SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qtyFROM lineitemWHERE l_shipdate <= '1998-09-02'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;
MySQL [tpch100g]> explain SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty FROM lineitem WHERE l_shipdate <= '1998-09-02' GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus;+----+-------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------------------------------------+| 1 | SIMPLE | lineitem | NULL | ALL | i_l_shipdate | NULL | NULL | NULL | 593184480 | 50.00 | Parallel scan (4 workers); Using where; Using temporary || 1 | SIMPLE | <sender1> | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | Send to (<receiver1>) || 1 | SIMPLE | <receiver1> | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00 | Receive from (<sender1>); Using temporary; Using filesort |+----+-------------+-------------+------------+------+---------------+------+---------+------+-----------+----------+-----------------------------------------------------------+3 rows in set, 1 warning (0.00 sec)
EXPLAIN format=tree query SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qtyFROM lineitemWHERE l_shipdate <= '1998-09-02'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;
MySQL [tpch100g]> explain format=tree SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty FROM lineitem WHERE l_shipdate <= '1998-09-02' GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus\\G*************************** 1. row ***************************EXPLAIN: -> Sort: lineitem.L_RETURNFLAG, lineitem.L_LINESTATUS-> Table scan on <temporary>-> Final Aggregate using temporary table-> PX Receiver (slice: 0; workers: 1)-> PX Sender (slice: 1; workers: 4)-> Table scan on <temporary>-> Aggregate using temporary table-> Filter: (lineitem.L_SHIPDATE <= DATE'1998-09-02') (cost=65449341.10 rows=296592240)-> Parallel table scan on lineitem (cost=65449341.10 rows=593184480)1 row in set (0.00 sec)
SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qtyFROM lineitemWHERE l_shipdate <= '1998-09-02'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;
mysql> show processlist;+--------+-------------+-----------------+-----------+---------+-------+------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+-------------+-----------------+-----------+---------+-------+------------+------------------------------------------------------------------------------------------------------+| 7 | tencentroot | 127.0.0.1:49238 | NULL | Sleep | 0 | | NULL || 11 | tencentroot | 127.0.0.1:49262 | NULL | Sleep | 0 | | NULL || 13 | tencentroot | 127.0.0.1:49288 | NULL | Sleep | 1 | | NULL || 237062 | tencentroot | localhost | tpch100g | Query | 24 | Scheduling | SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty FROM lineitem WHERE l_shipdate <= '199 || 237107 | tencentroot | localhost | NULL | Query | 0 | init | show processlist |+--------+-------------+-----------------+-----------+---------+-------+------------+------------------------------------------------------------------------------------------------------+6 rows in set (0.00 sec)
mysql> show parallel processlist;+--------+-------------+-----------+----------+---------+------+-------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+--------+-------------+-----------+----------+---------+------+-------------+------------------------------------------------------------------------------------------------------+| 237062 | tencentroot | localhost | tpch100g | Query | 18 | Scheduling | SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty FROM lineitem WHERE l_shipdate <= '199 || 237110 | | | | Task | 18 | Task runing | connection 237062, worker 0, task 1 || 237111 | | | | Task | 18 | Task runing | connection 237062, worker 1, task 1 || 237112 | | | | Task | 18 | Task runing | connection 237062, worker 2, task 1 || 237113 | | | | Task | 18 | Task runing | connection 237062, worker 3, task 1 |+--------+-------------+-----------+----------+---------+------+-------------+------------------------------------------------------------------------------------------------------+5 rows in set (0.00 sec)
피드백