使用LEADING hint 指定执行计划，join顺序错了

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】5.7.25-OceanBase_CE-v4.5.0.0
【问题描述】测试OB LEADING hint功能时候，发现没按预期Join，官方课程中说明是 先t1 join t2，结果再Join t0,实际测试下来是 先 t0 Join t1，再join t2

image
image1318×739 135 KB

【复现路径】

CREATE TABLE t0(c1 INT, c2 INT, c3 INT);
CREATE TABLE t1(c1 INT, c2 INT, c3 INT);
CREATE TABLE t2(c1 INT, c2 INT, c3 INT);

-- 如果想让 join 的顺序是 t0 join (t1 join t2), 且想让最外层的 join 为 nest loop join, 则应该这样写 hint:
EXPLAIN BASIC SELECT /*+ LEADING(t0 (t1 t2)) USE_NL((t1 t2)) */ * FROM t0, t1, t2 WHERE t0.c1 = t1.c1 AND t0.c1 = t2.c1;

image
image744×326 8.37 KB

3 个赞
#3

可以看下hint生效了吗。explain extended信息可以发下， 检查下连接串加没加-c

#4

image
image987×369 8.79 KB

image

本地442测试最外层确实为NLJ，截图看下这里hint是否生效

#5

登录方式

 mysql -h10.200.2.3 -P2881 -usbtest@tenant_bus -p'sbtest' sbtest -A

explain信息

MySQL [sbtest]> explain extended SELECT /*+ LEADING(t0 (t1 t2)) USE_NL((t1 t2)) */ * FROM t0, t1, t2 WHERE t0.c1 = t1.c1 AND t0.c1 = t2.c1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =======================================================                                                                                                                                    |
| |ID|OPERATOR               |NAME|EST.ROWS|EST.TIME(us)|                                                                                                                                    |
| -------------------------------------------------------                                                                                                                                    |
| |0 |EXCHANGE IN REMOTE     |    |1       |12          |                                                                                                                                    |
| |1 |└─EXCHANGE OUT REMOTE  |    |1       |11          |                                                                                                                                    |
| |2 |  └─HASH JOIN          |    |1       |8           |                                                                                                                                    |
| |3 |    ├─TABLE FULL SCAN  |t2  |1       |3           |                                                                                                                                    |
| |4 |    └─HASH JOIN        |    |1       |5           |                                                                                                                                    |
| |5 |      ├─TABLE FULL SCAN|t0  |1       |3           |                                                                                                                                    |
| |6 |      └─TABLE FULL SCAN|t1  |1       |3           |                                                                                                                                    |
| =======================================================                                                                                                                                    |
| Outputs & filters:                                                                                                                                                                         |
| -------------------------------------                                                                                                                                                      |
|   0 - output([t0.c1(0x7f05eba22de0)], [t0.c2(0x7f05eba23940)], [t0.c3(0x7f05eba23ad0)], [t1.c1(0x7f05eba22f70)], [t1.c2(0x7f05eba23c60)], [t1.c3(0x7f05eba23df0)],                         |
|        [t2.c1(0x7f05eba236c0)], [t2.c2(0x7f05eba23f80)], [t2.c3(0x7f05eba24110)]), filter(nil)                                                                                             |
|   1 - output([t0.c1(0x7f05eba22de0)], [t0.c2(0x7f05eba23940)], [t0.c3(0x7f05eba23ad0)], [t1.c1(0x7f05eba22f70)], [t1.c2(0x7f05eba23c60)], [t1.c3(0x7f05eba23df0)],                         |
|        [t2.c1(0x7f05eba236c0)], [t2.c2(0x7f05eba23f80)], [t2.c3(0x7f05eba24110)]), filter(nil)                                                                                             |
|   2 - output([t0.c1(0x7f05eba22de0)], [t0.c2(0x7f05eba23940)], [t0.c3(0x7f05eba23ad0)], [t1.c1(0x7f05eba22f70)], [t1.c2(0x7f05eba23c60)], [t1.c3(0x7f05eba23df0)],                         |
|        [t2.c1(0x7f05eba236c0)], [t2.c2(0x7f05eba23f80)], [t2.c3(0x7f05eba24110)]), filter(nil), rowset=16                                                                                  |
|       equal_conds([t0.c1(0x7f05eba22de0) = t2.c1(0x7f05eba236c0)(0x7f05eba23570)]), other_conds(nil)                                                                                       |
|   3 - output([t2.c1(0x7f05eba236c0)], [t2.c2(0x7f05eba23f80)], [t2.c3(0x7f05eba24110)]), filter(nil), rowset=16                                                                            |
|       access([t2.c1(0x7f05eba236c0)], [t2.c2(0x7f05eba23f80)], [t2.c3(0x7f05eba24110)]), partitions(p0)                                                                                    |
|       is_index_back=false, is_global_index=false,                                                                                                                                          |
|       range_key([t2.__pk_increment(0x7f05eba24d90)]), range(MIN ; MAX)always true                                                                                                          |
|   4 - output([t0.c1(0x7f05eba22de0)], [t0.c2(0x7f05eba23940)], [t0.c3(0x7f05eba23ad0)], [t1.c1(0x7f05eba22f70)], [t1.c2(0x7f05eba23c60)], [t1.c3(0x7f05eba23df0)]), filter(nil), rowset=16 |
|       equal_conds([t0.c1(0x7f05eba22de0) = t1.c1(0x7f05eba22f70)(0x7f05eba22c90)]), other_conds(nil)                                                                                       |
|   5 - output([t0.c1(0x7f05eba22de0)], [t0.c2(0x7f05eba23940)], [t0.c3(0x7f05eba23ad0)]), filter(nil), rowset=16                                                                            |
|       access([t0.c1(0x7f05eba22de0)], [t0.c2(0x7f05eba23940)], [t0.c3(0x7f05eba23ad0)]), partitions(p0)                                                                                    |
|       is_index_back=false, is_global_index=false,                                                                                                                                          |
|       range_key([t0.__pk_increment(0x7f05eba24a70)]), range(MIN ; MAX)always true                                                                                                          |
|   6 - output([t1.c1(0x7f05eba22f70)], [t1.c2(0x7f05eba23c60)], [t1.c3(0x7f05eba23df0)]), filter(nil), rowset=16                                                                            |
|       access([t1.c1(0x7f05eba22f70)], [t1.c2(0x7f05eba23c60)], [t1.c3(0x7f05eba23df0)]), partitions(p0)                                                                                    |
|       is_index_back=false, is_global_index=false,                                                                                                                                          |
|       range_key([t1.__pk_increment(0x7f05eba24c00)]), range(MIN ; MAX)always true                                                                                                          |
| Used Hint:                                                                                                                                                                                 |
| -------------------------------------                                                                                                                                                      |
|   /*+                                                                                                                                                                                      |
|                                                                                                                                                                                            |
|   */                                                                                                                                                                                       |
| Qb name trace:                                                                                                                                                                             |
| -------------------------------------                                                                                                                                                      |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                                                                                           |
|   stmt_id:1, SEL$1                                                                                                                                                                         |
| Outline Data:                                                                                                                                                                              |
| -------------------------------------                                                                                                                                                      |
|   /*+                                                                                                                                                                                      |
|       BEGIN_OUTLINE_DATA                                                                                                                                                                   |
|       LEADING(@"SEL$1" ("sbtest"."t2"@"SEL$1" ("sbtest"."t0"@"SEL$1" "sbtest"."t1"@"SEL$1")))                                                                                              |
|       USE_HASH(@"SEL$1" ("sbtest"."t1"@"SEL$1" "sbtest"."t0"@"SEL$1"))                                                                                                                     |
|       FULL(@"SEL$1" "sbtest"."t2"@"SEL$1")                                                                                                                                                 |
|       USE_HASH(@"SEL$1" "sbtest"."t1"@"SEL$1")                                                                                                                                             |
|       FULL(@"SEL$1" "sbtest"."t0"@"SEL$1")                                                                                                                                                 |
|       FULL(@"SEL$1" "sbtest"."t1"@"SEL$1")                                                                                                                                                 |
|       OPTIMIZER_FEATURES_ENABLE('4.5.0.0')                                                                                                                                                 |
|       END_OUTLINE_DATA                                                                                                                                                                     |
|   */                                                                                                                                                                                       |
| Optimization Info:                                                                                                                                                                         |
| -------------------------------------                                                                                                                                                      |
|   t2:                                                                                                                                                                                      |
|       table_rows:1                                                                                                                                                                         |
|       physical_range_rows:1                                                                                                                                                                |
|       logical_range_rows:1                                                                                                                                                                 |
|       output_rows:1                                                                                                                                                                        |
|       table_dop:1                                                                                                                                                                          |
|       dop_method:Table DOP                                                                                                                                                                 |
|       avaiable_index_name:[t2]                                                                                                                                                             |
|       stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0]                                                                                                           |
|       dynamic sampling level:1                                                                                                                                                             |
|       estimation method:[STORAGE, DYNAMIC SAMPLING FULL]                                                                                                                                   |
|   t0:                                                                                                                                                                                      |
|       table_rows:1                                                                                                                                                                         |
|       physical_range_rows:1                                                                                                                                                                |
|       logical_range_rows:1                                                                                                                                                                 |
|       output_rows:1                                                                                                                                                                        |
|       table_dop:1                                                                                                                                                                          |
|       dop_method:Table DOP                                                                                                                                                                 |
|       avaiable_index_name:[t0]                                                                                                                                                             |
|       stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0]                                                                                                           |
|       dynamic sampling level:1                                                                                                                                                             |
|       estimation method:[STORAGE, DYNAMIC SAMPLING FULL]                                                                                                                                   |
|   t1:                                                                                                                                                                                      |
|       table_rows:1                                                                                                                                                                         |
|       physical_range_rows:1                                                                                                                                                                |
|       logical_range_rows:1                                                                                                                                                                 |
|       output_rows:1                                                                                                                                                                        |
|       table_dop:1                                                                                                                                                                          |
|       dop_method:Table DOP                                                                                                                                                                 |
|       avaiable_index_name:[t1]                                                                                                                                                             |
|       stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0]                                                                                                           |
|       dynamic sampling level:1                                                                                                                                                             |
|       estimation method:[STORAGE, DYNAMIC SAMPLING FULL]                                                                                                                                   |
|   Plan Type:                                                                                                                                                                               |
|       REMOTE                                                                                                                                                                               |
|   Note:                                                                                                                                                                                    |
|       Degree of Parallelisim is 1 because of table property                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
95 rows in set (0.14 sec)
#6

加上 -c 后 正常了

MySQL [sbtest]> explain extended SELECT /*+ LEADING(t0 (t1 t2)) USE_NL((t1 t2)) */ * FROM t0, t1, t2 WHERE t0.c1 = t1.c1 AND t0.c1 = t2.c1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================================                                                                                                                                  |
| |ID|OPERATOR                 |NAME|EST.ROWS|EST.TIME(us)|                                                                                                                                  |
| ---------------------------------------------------------                                                                                                                                  |
| |0 |EXCHANGE IN REMOTE       |    |1       |12          |                                                                                                                                  |
| |1 |└─EXCHANGE OUT REMOTE    |    |1       |11          |                                                                                                                                  |
| |2 |  └─NESTED-LOOP JOIN     |    |1       |8           |                                                                                                                                  |
| |3 |    ├─TABLE FULL SCAN    |t0  |1       |3           |                                                                                                                                  |
| |4 |    └─MATERIAL           |    |1       |5           |                                                                                                                                  |
| |5 |      └─HASH JOIN        |    |1       |5           |                                                                                                                                  |
| |6 |        ├─TABLE FULL SCAN|t1  |1       |3           |                                                                                                                                  |
| |7 |        └─TABLE FULL SCAN|t2  |1       |3           |                                                                                                                                  |
| =========================================================                                                                                                                                  |
| Outputs & filters:                                                                                                                                                                         |
| -------------------------------------                                                                                                                                                      |
|   0 - output([t0.c1(0x7f05b26244a0)], [t0.c2(0x7f05b2625000)], [t0.c3(0x7f05b2625190)], [t1.c1(0x7f05b2624630)], [t1.c2(0x7f05b2625320)], [t1.c3(0x7f05b26254b0)],                         |
|        [t2.c1(0x7f05b2624d80)], [t2.c2(0x7f05b2625640)], [t2.c3(0x7f05b26257d0)]), filter(nil)                                                                                             |
|   1 - output([t0.c1(0x7f05b26244a0)], [t0.c2(0x7f05b2625000)], [t0.c3(0x7f05b2625190)], [t1.c1(0x7f05b2624630)], [t1.c2(0x7f05b2625320)], [t1.c3(0x7f05b26254b0)],                         |
|        [t2.c1(0x7f05b2624d80)], [t2.c2(0x7f05b2625640)], [t2.c3(0x7f05b26257d0)]), filter(nil)                                                                                             |
|   2 - output([t0.c1(0x7f05b26244a0)], [t0.c2(0x7f05b2625000)], [t0.c3(0x7f05b2625190)], [t1.c1(0x7f05b2624630)], [t1.c2(0x7f05b2625320)], [t1.c3(0x7f05b26254b0)],                         |
|        [t2.c1(0x7f05b2624d80)], [t2.c2(0x7f05b2625640)], [t2.c3(0x7f05b26257d0)]), filter(nil), rowset=16                                                                                  |
|       conds([t0.c1(0x7f05b26244a0) = t1.c1(0x7f05b2624630)(0x7f05b2624350)]), nl_params_(nil), use_batch=false                                                                             |
|   3 - output([t0.c1(0x7f05b26244a0)], [t0.c2(0x7f05b2625000)], [t0.c3(0x7f05b2625190)]), filter(nil), rowset=16                                                                            |
|       access([t0.c1(0x7f05b26244a0)], [t0.c2(0x7f05b2625000)], [t0.c3(0x7f05b2625190)]), partitions(p0)                                                                                    |
|       is_index_back=false, is_global_index=false,                                                                                                                                          |
|       range_key([t0.__pk_increment(0x7f05b2626130)]), range(MIN ; MAX)always true                                                                                                          |
|   4 - output([t1.c1(0x7f05b2624630)], [t1.c2(0x7f05b2625320)], [t1.c3(0x7f05b26254b0)], [t2.c1(0x7f05b2624d80)], [t2.c2(0x7f05b2625640)], [t2.c3(0x7f05b26257d0)]), filter(nil), rowset=16 |
|   5 - output([t1.c1(0x7f05b2624630)], [t1.c2(0x7f05b2625320)], [t1.c3(0x7f05b26254b0)], [t2.c1(0x7f05b2624d80)], [t2.c2(0x7f05b2625640)], [t2.c3(0x7f05b26257d0)]), filter(nil), rowset=16 |
|       equal_conds([t1.c1(0x7f05b2624630) = t2.c1(0x7f05b2624d80)(0x7f05b26c6a30)]), other_conds(nil)                                                                                       |
|   6 - output([t1.c1(0x7f05b2624630)], [t1.c2(0x7f05b2625320)], [t1.c3(0x7f05b26254b0)]), filter(nil), rowset=16                                                                            |
|       access([t1.c1(0x7f05b2624630)], [t1.c2(0x7f05b2625320)], [t1.c3(0x7f05b26254b0)]), partitions(p0)                                                                                    |
|       is_index_back=false, is_global_index=false,                                                                                                                                          |
|       range_key([t1.__pk_increment(0x7f05b26262c0)]), range(MIN ; MAX)always true                                                                                                          |
|   7 - output([t2.c1(0x7f05b2624d80)], [t2.c2(0x7f05b2625640)], [t2.c3(0x7f05b26257d0)]), filter(nil), rowset=16                                                                            |
|       access([t2.c1(0x7f05b2624d80)], [t2.c2(0x7f05b2625640)], [t2.c3(0x7f05b26257d0)]), partitions(p0)                                                                                    |
|       is_index_back=false, is_global_index=false,                                                                                                                                          |
|       range_key([t2.__pk_increment(0x7f05b2626450)]), range(MIN ; MAX)always true                                                                                                          |
| Used Hint:                                                                                                                                                                                 |
| -------------------------------------                                                                                                                                                      |
|   /*+                                                                                                                                                                                      |
|                                                                                                                                                                                            |
|       LEADING(("t0" ("t1" "t2")))                                                                                                                                                          |
|       USE_NL(("t1" "t2"))                                                                                                                                                                  |
|   */                                                                                                                                                                                       |
| Qb name trace:                                                                                                                                                                             |
| -------------------------------------                                                                                                                                                      |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                                                                                           |
|   stmt_id:1, SEL$1                                                                                                                                                                         |
| Outline Data:                                                                                                                                                                              |
| -------------------------------------                                                                                                                                                      |
|   /*+                                                                                                                                                                                      |
|       BEGIN_OUTLINE_DATA                                                                                                                                                                   |
|       LEADING(@"SEL$1" ("sbtest"."t0"@"SEL$1" ("sbtest"."t1"@"SEL$1" "sbtest"."t2"@"SEL$1")))                                                                                              |
|       USE_NL(@"SEL$1" ("sbtest"."t2"@"SEL$1" "sbtest"."t1"@"SEL$1"))                                                                                                                       |
|       USE_NL_MATERIALIZATION(@"SEL$1" ("sbtest"."t2"@"SEL$1" "sbtest"."t1"@"SEL$1"))                                                                                                       |
|       FULL(@"SEL$1" "sbtest"."t0"@"SEL$1")                                                                                                                                                 |
|       USE_HASH(@"SEL$1" "sbtest"."t2"@"SEL$1")                                                                                                                                             |
|       FULL(@"SEL$1" "sbtest"."t1"@"SEL$1")                                                                                                                                                 |
|       FULL(@"SEL$1" "sbtest"."t2"@"SEL$1")                                                                                                                                                 |
|       OPTIMIZER_FEATURES_ENABLE('4.5.0.0')                                                                                                                                                 |
|       END_OUTLINE_DATA                                                                                                                                                                     |
|   */                                                                                                                                                                                       |
| Optimization Info:                                                                                                                                                                         |
| -------------------------------------                                                                                                                                                      |
|   t0:                                                                                                                                                                                      |
|       table_rows:1                                                                                                                                                                         |
|       physical_range_rows:1                                                                                                                                                                |
|       logical_range_rows:1                                                                                                                                                                 |
|       output_rows:1                                                                                                                                                                        |
|       table_dop:1                                                                                                                                                                          |
|       dop_method:Table DOP                                                                                                                                                                 |
|       avaiable_index_name:[t0]                                                                                                                                                             |
|       stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0]                                                                                                           |
|       dynamic sampling level:1                                                                                                                                                             |
|       estimation method:[STORAGE, DYNAMIC SAMPLING FULL]                                                                                                                                   |
|   t1:                                                                                                                                                                                      |
|       table_rows:1                                                                                                                                                                         |
|       physical_range_rows:1                                                                                                                                                                |
|       logical_range_rows:1                                                                                                                                                                 |
|       output_rows:1                                                                                                                                                                        |
|       table_dop:1                                                                                                                                                                          |
|       dop_method:Table DOP                                                                                                                                                                 |
|       avaiable_index_name:[t1]                                                                                                                                                             |
|       stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0]                                                                                                           |
|       dynamic sampling level:1                                                                                                                                                             |
|       estimation method:[STORAGE, DYNAMIC SAMPLING FULL]                                                                                                                                   |
|   t2:                                                                                                                                                                                      |
|       table_rows:1                                                                                                                                                                         |
|       physical_range_rows:1                                                                                                                                                                |
|       logical_range_rows:1                                                                                                                                                                 |
|       output_rows:1                                                                                                                                                                        |
|       table_dop:1                                                                                                                                                                          |
|       dop_method:Table DOP                                                                                                                                                                 |
|       avaiable_index_name:[t2]                                                                                                                                                             |
|       stats info:[version=1970-01-01 08:00:00.000000, is_locked=0, is_expired=0]                                                                                                           |
|       dynamic sampling level:1                                                                                                                                                             |
|       estimation method:[STORAGE, DYNAMIC SAMPLING FULL]                                                                                                                                   |
|   Plan Type:                                                                                                                                                                               |
|       REMOTE                                                                                                                                                                               |
|   Note:                                                                                                                                                                                    |
|       Degree of Parallelisim is 1 because of table property                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
100 rows in set (0.00 sec)
#7

非常感谢！