案例二：group by 分组语句会进行排序吗？
看下面这条sql
select id,max(user_name) from test1 group by id;
问：group by 分组语句会进行排序吗
直接来看 explain 之后结果 (为了适应页面展示，仅截取了部分输出信息)
TableScan alias: test1 Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: int), user_name (type: string) outputColumnNames: id, user_name Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE Group By Operator aggregations: max(user_name) keys: id (type: int) mode: hash outputColumnNames: _col0, _col1 Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: int) sort order: + Map-reduce partition columns: _col0 (type: int) Statistics: Num rows: 9 Data size: 108 Basic stats: COMPLETE Column stats: NONE value expressions: _col1 (type: string) ...
我们看 Group By Operator，里面有 keys: id (type: int) 说明按照 id 进行分组的，再往下看还有 sort order: + ，说明是按照 id 字段进行正序排序的。
案例三：哪条sql执行效率高呢？
观察两条sql语句
SELECT a.id, b.user_name FROM test1 a JOIN test2 b ON a.id = b.id WHERE a.id > 2;
SELECT a.id, b.user_name FROM (SELECT * FROM test1 WHERE id > 2) a JOIN test2 b ON a.id = b.id;
这两条sql语句输出的结果是一样的，但是哪条sql执行效率高呢？
有人说第一条sql执行效率高，因为第二条sql有子查询，子查询会影响性能；
有人说第二条sql执行效率高，因为先过滤之后，在进行join时的条数减少了，所以执行效率就高了。
到底哪条sql效率高呢，我们直接在sql语句前面加上 explain，看下执行计划不就知道了嘛！
在第一条sql语句前加上 explain，得到如下结果
hive (default)> explain select a.id,b.user_name from test1 a join test2 b on a.id=b.id where a.id >2; OK Explain STAGE DEPENDENCIES: Stage-4 is a root stage Stage-3 depends on stages: Stage-4 Stage-0 depends on stages: Stage-3 STAGE PLANS: Stage: Stage-4 Map Reduce Local Work Alias -> Map Local Tables: $hdt$_0:a Fetch Operator limit: -1 Alias -> Map Local Operator Tree: $hdt$_0:a TableScan alias: a Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (id > 2) (type: boolean) Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: int) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator keys: 0 _col0 (type: int) 1 _col0 (type: int) Stage: Stage-3 Map Reduce Map Operator Tree: TableScan alias: b Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (id > 2) (type: boolean) Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: int), user_name (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE Map Join Operator condition map: Inner Join 0 to 1 keys: 0 _col0 (type: int) 1 _col0 (type: int) outputColumnNames: _col0, _col2 Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col0 (type: int), _col2 (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Local Work: Map Reduce Local Work Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink
在第二条sql语句前加上 explain，得到如下结果
hive (default)> explain select a.id,b.user_name from(select * from test1 where id>2 ) a join test2 b on a.id=b.id; OK Explain STAGE DEPENDENCIES: Stage-4 is a root stage Stage-3 depends on stages: Stage-4 Stage-0 depends on stages: Stage-3 STAGE PLANS: Stage: Stage-4 Map Reduce Local Work Alias -> Map Local Tables: $hdt$_0:test1 Fetch Operator limit: -1 Alias -> Map Local Operator Tree: $hdt$_0:test1 TableScan alias: test1 Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (id > 2) (type: boolean) Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: int) outputColumnNames: _col0 Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE HashTable Sink Operator keys: 0 _col0 (type: int) 1 _col0 (type: int) Stage: Stage-3 Map Reduce Map Operator Tree: TableScan alias: b Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (id > 2) (type: boolean) Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: int), user_name (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 2 Data size: 25 Basic stats: COMPLETE Column stats: NONE Map Join Operator condition map: Inner Join 0 to 1 keys: 0 _col0 (type: int) 1 _col0 (type: int) outputColumnNames: _col0, _col2 Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: _col0 (type: int), _col2 (type: string) outputColumnNames: _col0, _col1 Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 2 Data size: 27 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Local Work: Map Reduce Local Work Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink
大家有什么发现，除了表别名不一样，其他的执行计划完全一样，都是先进行 where 条件过滤，在进行 join 条件关联。说明 hive 底层会自动帮我们进行优化，所以这两条sql语句执行效率是一样的。
以上仅列举了3个我们生产中既熟悉又有点迷糊的例子，explain 还有很多其他的用途，如查看stage的依赖情况、排查数据倾斜、hive 调优等，小伙伴们可以自行尝试。
