--- title: "Optimizer Hint | OceanBase 文档中心" description: Optimizer Hint Hint 机制可以使优化器生成某种特定的计划。 一般情况下，优化器会为用户查询选择最佳的执行计划，不需要用户使用 Hint 指定，但在某些场景下，优化器生成的执行计划可能不满足用户的要求，这时就需要用户使用 Hint 来指定生成某种执行计划。 Hint 语法 Hint 从语法上看是一种特… image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.1.4 社区版 # Optimizer Hint 更新时间：2023-08-03 17:20:32 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V3.1.4/zh-CN/100.users-guide/900.performance-tuning-guide/500.sql-tuning-guide/400.sql-tuning/600.manage-execution-plans/100.optimizer-hints.md) Hint 机制可以使优化器生成某种特定的计划。 一般情况下，优化器会为用户查询选择最佳的执行计划，不需要用户使用 Hint 指定，但在某些场景下，优化器生成的执行计划可能不满足用户的要求，这时就需要用户使用 Hint 来指定生成某种执行计划。 ## Hint 语法 Hint 从语法上看是一种特殊的 SQL 注释，所不同的是在注释的左标记后（"/*"符号）增加了一个"+"。 既然是注释，如果服务器端无法识别 SQL 语句中的 Hint，优化器会选择忽略用户 Hint 而使用默认的计划生成逻辑结构。另外，Hint 只影响优化器生成计划的逻辑结构，而不影响 SQL 语句的语义。 ```javascript {DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text] [hint_text]... */ ``` > **注意** > > > > 如果使用 MySQL 的 C 客户端执行带 Hint 的 SQL 语句，需要使用 `-c` 选项登录，否则 MySQL 客户端会将 Hint 作为注释从用户 SQL 语句中去除，导致系统无法收到用户 Hint。 ### Hint 参数 Hint 相关参数名称、语义和语法如下表所示。 | **名称** | **语法** | **语义** | | --- | --- | --- | | NO_REWRITE | NO_REWRITE | 禁止 SQL 改写。 | | READ_CONSISTENCY | READ_CONSISTENCY (WEAK[STRONGFROZEN]) | 读一致性设置（弱/强）。 | | INDEX_HINT | /*+ INDEX(table_name index_name) */ | 设置表索引。 | | QUERY_TIMEOUT | QUERY_TIMEOUT(INTNUM) | 设置超时时间。 | | LOG_LEVEL | LOG_LEVEL([']log_level[']) | 设置日志级别，当设置模块级别语句时候，以第一个单引号（'）作为开始，第二个单引号（'）作为结束；例如'DEBUG'。 | | LEADING | LEADING([qb_name] TBL_NAME_LIST) | 设置联接顺序。 | | ORDERED | ORDERED | 设置按照 SQL 中的顺序进行联接。 | | FULL | FULL([qb_name] TBL_NAME) | 设置表访问路径为主表等价于 `INDEX(TBL_NAME PRIMARY)`。 | | USE_PLAN_CACHE | USE_PLAN_CACHE(NONE[DEFAULT]) | 设置是否使用计划缓存： - `NONE`：表示不使用计划缓存。 - `DEFAULT`：表示按照服务器本身的设置来决定是否使用计划缓存。 | | USE_MERGE | USE_MERGE([qb_name] TBL_NAME_LIST) | 设置指定表在作为右表时使用 Merge Join。 | | USE_HASH | USE_HASH([qb_name] TBL_NAME_LIST) | 设置指定表在作为右表时使用 Hash Join。 | | NO_USE_HASH | NO_USE_HASH([qb_name] TBL_NAME_LIST) | 设置指定表在作为右表时不使用 Hash Join。 | | USE_NL | USE_NL([qb_name] TBL_NAME_LIST) | 设置指定表在作为右表时使用 Nested Loop Join。 | | USE_BNL | USE_BNL([qb_name] TBL_NAME_LIST) | 设置指定表在作为右表时使用 Block Nested Loop Join | | USE_HASH_AGGREGATION | USE_HASH_AGGREGATION([qb_name]) | 设置聚合算法为 Hash。例如 Hash Group By 或者 Hash Distinct。 | | NO_USE_HASH_AGGREGATION | NO_USE_HASH_AGGREGATION([qb_name]) | 设置 Aggregate 方法不使用 Hash Aggregate，使用 Merge Group By 或者 Merge Distinct。 | | USE_LATE_MATERIALIZATION | USE_LATE_MATERIALIZATION | 设置使用晚期物化。 | | NO_USE_LATE_MATERIALIZATION | NO_USE_LATE_MATERIALIZATION | 设置不使用晚期物化。 | | TRACE_LOG | TRACE_LOG | 设置收集 Trace 记录用于 `SHOW TRACE` 展示。 | | QB_NAME | QB_NAME( NAME ) | 设置 Query Block 的名称。 | | PARALLEL | PARALLEL(INTNUM) | 设置分布式执行并行度。 | | TOPK | TOPK(PRECISION MINIMUM_ROWS) | 设置模糊查询的精度和最小行数。 其中 `PRECSION` 为整型，取值范围[0，100]，表示模糊查询的行数百分比；`MINIMUM_ROWS` 为最小返回行数。 | > **说明** > > > > - `QB_NAME` 语法是: `@NAME` > - `TBL_NAME` 语法是: `[db_name.]relation_name [qb_name]` ## QB_NAME 参数 在 DML 语句中，每一个 `query_block` 都会有一个 `QB_NAME`（Query Block Name），可以由用户指定，也可以由系统自动生成。在用户没有用 Hint 指定 `QB_NAME` 的时候，系统会按照 `SEL$1`、`SEL$2`、`UPD$1`、`DEL$1` 方式从左到右（实际也是 Resolver 的解析顺序）依次生成。 通过 `QB_NAME` 可以精确定位到每一个表，也可以在某处指定任意 Query Block 的行为。`TBL_NAME` 中的 `QB_NAME` 用于定位表，在 Hint 中最前面的 `QB_NAME` 用于定位 Hint 作用于哪一个 `query_block`。 如下例所示，按照默认规则，会为 `SEL$1` 中的 `t` 表选择 `t_c1` 路径，为 `SEL$2` 中的 `t` 表选择主表（Primary）访问。如果 SQL 通过 Hint 来指定 `SEL$1` 的 `t` 表走主表，则 `SEL$2` 的 `t` 表走索引。 ```javascript obclient>CREATE TABLE t(c1 INT, c2 INT, KEY t_c1(c1)); Query OK, 0 rows affected (0.31 sec) obclient>EXPLAIN SELECT * FROM t , (SELECT * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G； *************************** 1. row *************************** Query Plan: ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------ |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |1895| |1 | TABLE SCAN |t(t_c1)|1 |472 | |2 | TABLE SCAN |t |1 |1397| ============================================================ Outputs & filters: ------------------------------------- 0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil), conds(nil), nl_params_(nil) 1 - output([t.c1], [t.c2]), filter(nil), access([t.c1], [t.c2]), partitions(p0) 2 - output([t.c2], [t.c1]), filter([t.c2 = 1]), access([t.c2], [t.c1]), partitions(p0) ``` > **注意** > > > > 因为改写后 `SEL$2` 被提升到 `SEL$1`，所以这里不用指定 Hint 所作用的 Query Block。 ```javascript obclient>EXPLAIN SELECT/*+INDEX(t@SEL$1 PRIMARY) INDEX(t@SEL$2 t_c1)*/ * FROM t , (SELECT * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G； *************************** 1. row *************************** Query Plan: ============================================================= |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------------- |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |16166| |1 | TABLE SCAN |t |1 |1397 | |2 | TABLE SCAN |t(t_c1)|1 |14743| ============================================================= Outputs & filters: ------------------------------------- 0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil), conds(nil), nl_params_(nil) 1 - output([t.c1], [t.c2]), filter([t.c1 = 1]), access([t.c1], [t.c2]), partitions(p0) 2 - output([t.c2], [t.c1]), filter([t.c2 = 1]), access([t.c2], [t.c1]), partitions(p0) ``` 上述示例中 SQL 也可以写成如下方式： ```javascript obclient>SELECT/*+INDEX(t@SEL$1 PRIMARY) INDEX(@SEL$2 t@SEL$2 t_c1)*/ * FROM t , (SELECT * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G； <==> obclient>SELECT/*+INDEX(t@SEL$1 PRIMARY)*/ * FROM t , (SELECT/*+INDEX(t@SEL$2 t_c1)*/ * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G； <==> obclient>SELECT/*+INDEX(@SEL$1 t@SEL$1 PRIMARY) INDEX(@SEL$2 t@SEL$2 t_c1)*/ * FROM t , (SELECT * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G； ``` 对于 该Hint，可以通过 `EXPLAIN EXTENDED` 命令执行结果中的 `Outline Data` 来查看它的所有信息。 ```javascript obclient>EXPLAIN EXTENDED SELECT * FROM t , (SELECT * FROM t WHERE c2 = 1) ta WHERE t.c1 = 1\G; *************************** 1. row *************************** Query Plan: ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------ |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |1895| |1 | TABLE SCAN |t(t_c1)|1 |472 | |2 | TABLE SCAN |t |1 |1397| ============================================================ Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$1" "test.t"@"SEL$2") LEADING(@"SEL$1" "test.t"@"SEL$1" "test.t"@"SEL$2") INDEX(@"SEL$1" "test.t"@"SEL$1" "t_c1") FULL(@"SEL$2" "test.t"@"SEL$2") END_OUTLINE_DATA */ ``` ## Hint 一般规则 - 对于没有指定 Query Block 的 Hint 表示作用于本 Query Block。 示例 1：由于 `t1` 表在 Query Block2，并且无法通过改写提升到 Query Block1，所以 Hint 无法生效。 ```javascript obclient>CREATE TABLE t1(c1 INT, c2 INT, INDEX t1_c1(c1), INDEX t1_c2(c2)); Query OK, 0 rows affected (0.31 sec) obclient>EXPLAIN SELECT/*+INDEX(t1 t1_c2)*/ * FROM t, (SELECT * FROM t1 GROUP BY c1) ta WHERE t.c1 = 1\G; *************************** 1. row *************************** Query Plan: ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------ |0 |NESTED-LOOP INNER JOIN CARTESIAN| |666 |5906| |1 | TABLE SCAN |t(t_c1)|1 |472 | |2 | SUBPLAN SCAN |ta |666 |5120| |3 | HASH GROUP BY | |666 |4454| |4 | TABLE SCAN |t1 |1000 |1397| ============================================================ Outputs & filters: ------------------------------------- 0 - output([t.c1], [t.c2], [ta.c1], [ta.c2]), filter(nil), conds(nil), nl_params_(nil) 1 - output([t.c1], [t.c2]), filter(nil), access([t.c1], [t.c2]), partitions(p0) 2 - output([ta.c1], [ta.c2]), filter(nil), access([ta.c1], [ta.c2]) 4 - output([t1.c1], [t1.c2]), filter(nil), group([t1.c1]), agg_func(nil) 5 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p0) ``` 示例 2：SQL 可以通过改写将 `t1` 表提升到 `SEL$1`，则 Hint 生效。 ```javascript obclient>EXPLAIN SELECT/*+INDEX(t1 t1_c2)*/ * FROM t, (SELECT * FROM t1) ta WHERE t.c1 = 1\G; *************************** 1. row *************************** Query Plan: =============================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | --------------------------------------------------------------- |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1000 |15674| |1 | TABLE SCAN |t(t_c1) |1 |472 | |2 | TABLE SCAN |t1(t1_c2)|1000 |14743| =============================================================== Outputs & filters: ------------------------------------- 0 - output([t.c1], [t.c2], [t1.c1], [t1.c2]), filter(nil), conds(nil), nl_params_(nil) 1 - output([t1.c1], [t1.c2]), filter(nil), access([t1.c1], [t1.c2]), partitions(p0) 2 - output([t.c1], [t.c2]), filter(nil), access([t.c1], [t.c2]), partitions(p0) ``` - 如果指定表行为，但在本 Query Block 中没有找到该表，或者发生冲突，那么 Hint 无效。 对于没有找到表的情况可以参考规则 1 中的示例 1。如下示例为同时找到两个冲突的情况。 ```javascript obclient>EXPLAIN EXTENDED SELECT/*+INDEX(t PRIMARY)*/ * FROM t , (SELECT * FROM t WHERE c1 = 1) ta WHERE t.c1 = 1\G; *************************** 1. row *************************** Query Plan: ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------ |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |970 | |1 | TABLE SCAN |t(t_c1)|1 |472 | |2 | TABLE SCAN |t(t_c1)|1 |472 | ============================================================ Outputs & filters: ------------------------------------- 0 - output([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)], [t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), filter(nil), conds(nil), nl_params_(nil), inner_get=false, self_join=false, batch_join=false 1 - output([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)]), filter(nil), access([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)]), partitions(p0), is_index_back=true, range_key([t.c1(0x7f7b7cdd3e60)], [t.__pk_increment(0x7f7b7cde86e0)]), range(1,MIN ; 1,MAX), range_cond([t.c1(0x7f7b7cdd3e60) = 1(0x7f7b7cdd3800)]) 2 - output([t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), filter(nil), access([t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), partitions(p0), is_index_back=true, range_key([t.c1(0x7f7b7cdd2bd0)], [t.__pk_increment(0x7f7b7cdf41b0)]), range(1,MIN ; 1,MAX), range_cond([t.c1(0x7f7b7cdd2bd0) = 1(0x7f7b7cdd2570)]) Used Hint: ------------------------------------- /*+ */ ``` - 联接方法的 Hint 中指定的表如果找不到，则忽略该表，其他的指定依然生效；如果优化器不能生成指定的联接方法，就会选择其他方法，Hint 无效。 - 联接顺序的 Hint 中如果存在表无法找到的情况，则该 Hint 完全失效。 ### Hint 主要语法 与其他数据库的行为相比，OceanBase 数据库优化器是动态规划的，已经考虑了所有可能的最优路径，Hint 主要作用是指定优化器的行为，并按照 Hint 执行。 #### **INDEX Hint** - `INDEX` Hint 的 MySQL 语法如下： ```javascript tbl_name [[AS] alias] [index_hint_list] index_hint_list: index_hint [, index_hint] ... index_hint: USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) | IGNORE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) | FORCE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) index_list: index_name [, index_name] ... ``` Oracle 语法中一个表只能指定一个 `INDEX`，而 MySQL 语法可以指定多个。但是 OceanBase 数据库中 MySQL 语法虽然支持指定多个 `INDEX`，但是对于 `USE` 和 `FORCE` 方式，只会用第一个 `INDEX` 生成 `PATH`，即使 SQL 语句中没有该 `INDEX` 的 `filter` 而导致全部扫描同时回表（即 OceanBase 数据库当前设计是认为写 Hint 的人比程序更明白那条路径是更好的）。`IGNORE` 类型会忽略所有指定的 `INDEX`。`USE`、`FORCE` 方式和 Oracle Hint 方式实际是一样的，如果该方式的 `INDEX` 不存在或者处于 `invalid` 状态，则 Hint 无效。对于 `IGNORE` 方式，如果将包括主表在内的所有 `INDEX` 忽略，则 Hint 无效。 #### **FULL Hint** `FULL` Hint 的语法是用于指定表使用主表扫描，语法为 `/*+ FULL(table_name)*/`。 `FULL` Hint 用于指定表选择主表扫描等价于 `INDEX` Hint `/*+ INDEX(table_name PRIMARY)*/`。 #### **ORDERED Hint** `ORDERED` Hint 可以指定按照 `FROM` 后面的表顺序作为联接顺序，语法为 `/*+ ORDERED*/`。 如果指定该 Hint 后发生 SQL 改写，那么就按照改写后的 `stmt` 中 From Items 的顺序联接，因为改写时候 `sub_query` 会在 From Items 中对应位置填放新的 Table Item。 #### **LEADING Hint** `LEADING` Hint 可以指定表的联接顺序，语法为 `/*+ LEADING(table_name_list)*/`。 其中，`table_name_list` 语法如下： ```javascript table_name table_name_list table_name table_name_list, table_name ``` `table_name_list` 中 `table_name` 比较特殊，语法如下： ```javascript db_name. relation_name relation_name ``` 其他 `table_name` 语法如下： ```javascript db_name. relation_name relation_name .relation_name ``` 为确保按照用户指定的顺序进行联接，`LEADING` Hint 的检查比较严格，如果发现 Hint 指定的 `table_name` 不存在，`LEADING` Hint 失效；如果发现 Hint 中存在重复表，`LEADING` Hint 失效。如果在 Optimizer 联接期间，按 `table_id` 无法在 From Items 中找到对应的表，则可能发生改写，那么该表及后面的表指定的 `JOIN` 顺序失效，该表前面的 `JOIN` 顺序依然有效。 #### USE_MERGE `USE_MERGE`可以指定表在联接时候使用 Merge Join 算法，将所指定的表作为右表。语法为 `/*+ USE_MERGE(table_name_list) */`。 > **注意** > > > > OceanBase 数据库中 Merge Join 必须有等值条件的 `join-condition`，因此无等值条件的两个表联接，`use_merge` 会无效。 关于 Merge Join 算法是否认为 A Merge Join B 等效于 B Merge Join A， 当前并没有最后结论。按照代价模型，Merge Join 计算代价时是区分左右表的，这也增加了 Hint 的灵活性，所以当前 Merge Join 区分左右表，即 `use_merge` 仅对表作为右表的时候生效。 #### USE_NL `USE_NL`指定表作为右表，在联接的时候使用 Nested Loop Join 算法，语法为：`/*+ USE_NL(table_name_list) */`。 #### USE_HASH `USE_HASH`指定表作为右表，在联接的时候使用 Hash Join 算法，语法为：`/*+ USE_HASH(table_name_list) */`。 #### PARALLEL `PARALLEL` 指定语句级别的并发度。当指定该 Hint时，会忽略系统变量 `ob_stmt_parallel_degree` 的设置。语法为：`/*+ PARALLEL(4) */`。 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线