JOIN关联SQL语句执行计划分析详细案例

一、LEFT JOIN 执行计划分析

A left join B：A作为主表，关联B表

结论为：
1) gcluster_hash_redistribute_join_optimize=1时：强制使用hash重分布
2) gcluster_hash_redistribute_join_optimize=2时：两表差距不超20%，使用hash重分布
3) gcluster_hash_redistribute_join_optimize=2，gcluster_hash_redist_threshold_row > 0时：两表差距超过20%，右表行数 > row , 使用hash重分布
4) gcluster_hash_redistribute_join_optimize=2，gcluster_hash_redist_threshold_row > 0时：两表差距超过20%，右表行数 <= row, 不使用hash重分布（拉复制表）

## 1.1 table_a:100    table_b:101
#a: 100 b:101  gcluster_hash_redistribute_join_optimize=2，两表差距不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 1.2 table_a:100    table_c:500
#a: 100 c:500  gcluster_hash_redistribute_join_optimize=2，两表差距超过20%，不使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a left join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
+------------------------------------------+

## 2.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=1，强制使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=1; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 2.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=1，强制使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=1; explain partitions select a.id,c.id from table_a a left join table_b c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 3.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=99;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 3.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 右表c表行数500 > gcluster_hash_redist_threshold_row99，使用hash重分布
set gcluster_hash_redist_threshold_row=99;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a left join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_c as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 4.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=100;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 4.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 右表c表行数500 > gcluster_hash_redist_threshold_row100，使用hash重分布
set gcluster_hash_redist_threshold_row=100;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a left join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_c as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 5.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=101;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 5.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 右表c表行数500 > gcluster_hash_redist_threshold_row101，使用hash重分布
set gcluster_hash_redist_threshold_row=101;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a left join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_c as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 6.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=102;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 6.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 右表c表行数500 > gcluster_hash_redist_threshold_row102，使用hash重分布
set gcluster_hash_redist_threshold_row=102;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a left join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_c as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 7.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=499;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 7.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 右表c表行数500 > gcluster_hash_redist_threshold_row499，使用hash重分布
set gcluster_hash_redist_threshold_row=499;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a left join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |    
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_c as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 8.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=500;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 8.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 右表c表行数500 = gcluster_hash_redist_threshold_row500，不使用hash重分布
set gcluster_hash_redist_threshold_row=500;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a left join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
+------------------------------------------+

## 9.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=501;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a left join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 9.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 右表c表行数500 < gcluster_hash_redist_threshold_row501，不使用hash重分布
set gcluster_hash_redist_threshold_row=501;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a left join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
+------------------------------------------+

二、RIGHT JOIN 执行计划分析

A right join B：B作为主表，关联A表

结论为：
1) gcluster_hash_redistribute_join_optimize=1时：强制使用hash重分布
2) gcluster_hash_redistribute_join_optimize=2时：两表差距不超20%，使用hash重分布
3) gcluster_hash_redistribute_join_optimize=2，gcluster_hash_redist_threshold_row > 0时：两表差距超过20%，左表行数 > row , 使用hash重分布
4) gcluster_hash_redistribute_join_optimize=2，gcluster_hash_redist_threshold_row > 0时：两表差距超过20%，左表行数 <= row, 不使用hash重分布（拉复制表）

## 1.1 table_a:100    table_b:101
#a: 100 b:101  gcluster_hash_redistribute_join_optimize=2，两表差距不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 1.2 table_a:100    table_c:500
#a: 100 c:500  gcluster_hash_redistribute_join_optimize=2，两表差距超过20%，不使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a right join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 2.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=1，强制使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=1; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 2.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=1，强制使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=1; explain partitions select a.id,c.id from table_a a right join table_b c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as c  |
|                 Hash Key : id             |
+------------------------------------------+

## 3.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=99;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 3.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100 > gcluster_hash_redist_threshold_row99，使用hash重分布
set gcluster_hash_redist_threshold_row=99;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a right join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_c as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 4.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=100;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 4.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100 = gcluster_hash_redist_threshold_row100，不使用hash重分布
set gcluster_hash_redist_threshold_row=100;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a right join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 5.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=101;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 5.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100 < gcluster_hash_redist_threshold_row101，不使用hash重分布
set gcluster_hash_redist_threshold_row=101;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a right join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 6.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=102;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 6.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100 < gcluster_hash_redist_threshold_row102，不使用hash重分布
set gcluster_hash_redist_threshold_row=102;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a right join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 7.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=499;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 7.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100 < gcluster_hash_redist_threshold_row499，不使用hash重分布
set gcluster_hash_redist_threshold_row=499;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a right join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 8.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=500;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 8.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100 < gcluster_hash_redist_threshold_row500，不使用hash重分布
set gcluster_hash_redist_threshold_row=500;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a right join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 9.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=501;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a right join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         LEFT JOIN                         |        
|         ON (id = concat_ws('-', id))      |
|             Step : <00>                   |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 9.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100 < gcluster_hash_redist_threshold_row501，不使用hash重分布
set gcluster_hash_redist_threshold_row=501;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a right join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         LEFT JOIN                        |        
|         ON (id = concat_ws('-', id))     |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
|             Step : <00>                  |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

三、INNER JOIN 执行计划分析

A inner join B：即join内连接，以两个表的交集为主，查出来是两个表有交集的部分

结论为：
1) gcluster_hash_redistribute_join_optimize=1时：强制使用hash重分布
2) gcluster_hash_redistribute_join_optimize=2时：两表差距不超20%，使用hash重分布
3) gcluster_hash_redistribute_join_optimize=2，gcluster_hash_redist_threshold_row > 0时：两表差距超过20%，左右表行数取小表 > row , 使用hash重分布
4) gcluster_hash_redistribute_join_optimize=2，gcluster_hash_redist_threshold_row > 0时：两表差距超过20%，左右表行数取小表 <= row, 不使用hash重分布（拉复制表）

## 1.1 table_a:100    table_b:101
#a: 100 b:101  gcluster_hash_redistribute_join_optimize=2，两表差距不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 1.2 table_a:100    table_c:500
#a: 100 c:500  gcluster_hash_redistribute_join_optimize=2，两表差距超过20%，不使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         INNER JOIN                       |        
|         ON (id = concat_ws('-', id))     |
|             Step : <00>                  |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 2.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=1，强制使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=1; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 2.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=1，强制使用hash重分布
set gcluster_hash_redist_threshold_row=0;set gcluster_hash_redistribute_join_optimize=1; explain partitions select a.id,c.id from table_a a join table_b c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 3.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=99;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 3.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100&右b表行数500 > gcluster_hash_redist_threshold_row99，使用hash重分布
set gcluster_hash_redist_threshold_row=99;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_c as c  |
|                 Hash Key : id             |
+-------------------------------------------+

## 4.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=100;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 4.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100&右b表行数500 = gcluster_hash_redist_threshold_row100，不使用hash重分布
set gcluster_hash_redist_threshold_row=100;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         INNER JOIN                       |        
|         ON (id = concat_ws('-', id))     |
|             Step : <00>                  |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 5.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=101;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 5.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100&右b表行数500 < gcluster_hash_redist_threshold_row101，不使用hash重分布
set gcluster_hash_redist_threshold_row=101;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         INNER JOIN                       |        
|         ON (id = concat_ws('-', id))     |
|             Step : <00>                  |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 6.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=102;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 6.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100&右b表行数500 < gcluster_hash_redist_threshold_row102，不使用hash重分布
set gcluster_hash_redist_threshold_row=102;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         INNER JOIN                       |        
|         ON (id = concat_ws('-', id))     |
|             Step : <00>                  |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 7.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=499;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 7.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100&右b表行数500 < gcluster_hash_redist_threshold_row499，不使用hash重分布
set gcluster_hash_redist_threshold_row=499;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         INNER JOIN                       |        
|         ON (id = concat_ws('-', id))     |
|             Step : <00>                  |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 8.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=500;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 8.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100&右b表行数500 < gcluster_hash_redist_threshold_row500，不使用hash重分布
set gcluster_hash_redist_threshold_row=500;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         INNER JOIN                       |        
|         INNER JOIN                       |
|         ON (id = concat_ws('-', id))     |
|             Step : <00>                  |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+

## 9.1 table_a:100    table_b:101
#gcluster_hash_redistribute_join_optimize=2，两表差不超20%，使用hash重分布
set gcluster_hash_redist_threshold_row=501;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,b.id from table_a a join table_b b on a.id=concat_ws('-',b.id);
| 01: [RESULT]                              |        
|         INNER JOIN                        |        
|         ON (id = concat_ws('-', id))      |
|             Table : vc1.tdb.table_a as a  |
|                 Hash Key : id             |
|             Step : <00>                   |
| --->00: [REDIST]                          |
|         Redist Key : (concat_ws('-', id)) |
|             Table : vc1.tdb.table_b as b  |
|                 Hash Key : id             |
+-------------------------------------------+

## 9.2 table_a:100    table_c:500
#gcluster_hash_redistribute_join_optimize=2，两表差超过20% then 左表a表行数100&右b表行数500 < gcluster_hash_redist_threshold_row501，不使用hash重分布
set gcluster_hash_redist_threshold_row=501;set gcluster_hash_redistribute_join_optimize=2; explain partitions select a.id,c.id from table_a a join table_c c on a.id=concat_ws('-',c.id);
| 01: [RESULT]                             |        
|         INNER JOIN                       |        
|         ON (id = concat_ws('-', id))     |
|             Step : <00>                  |
|             Table : vc1.tdb.table_c as c |
|                 Hash Key : id            |
| --->00: [BROADCAST]                      |
|             Table : vc1.tdb.table_a as a |
|                 Hash Key : id            |
+------------------------------------------+