本文介绍了OceanBase数据库中SQL Hint的使用。阐述了Hint在语句注释中的语法格式、常用SQL Hint类型，如弱一致性读、强制走索引、设定超时时间等。还强调多个Hint可叠加使用，命令行需加参数“-c”，并给出四个使用示例及执行计划展示。
Hint 在语句注释中的语法格式如下：
{DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text]... */
常用 SQL Hint 如下：
-
READ_CONSISTENCYHint 弱一致性读，指定某条 SQL 所读取的表模式为弱一致性（指定参数WEAK）或强一致性（指定参数STRONG），指引 SQL 读取相关表的分区的备副本。说明
READ_CONSISTENCYHint 仅仅是使当前查询可以进行弱一致性读，完全的读写分离还是要有路由等的支持。OceanBase 数据库的读写分离功能依赖 ODP 的 LDC 功能，如果需要使用读写分离功能，建议先进行 LDC 配置。有关读写分离的详细信息，请参见 读写分离 。 -
INDEXHint 指示优化器对指定的表使用索引扫描。 -
QUERY_TIMEOUTHint 指示服务器设定某条 SQL 执行时的超时时间，单位是 us。
有关其他常用 SQL Hints 的详细信息，请参考 注释 章节。
使用 SQL Hint
SQL Hint 通常用在 SQL 语句里，并不限于查询 SQL。这里以查询 SQL 为例，简单的语法格式如下：
SELECT /*+ hint_text [, hint_text] */ select_items FROM table_name;
说明
- 多个 SQL Hint 可以叠加使用，注意功能不要冲突。
- 在
obclient命令行环境下，默认会忽略注释语法，导致 SQL Hint 不起作用，所以启动obclient时需要增加参数"-c"。
示例 1：指定 SQL 所读取的表模式为弱一致性
-
查询
dws_ny表id为1011的数据。obclient> SELECT /*+ READ_CONSISTENCY(WEAK) */ * FROM dws_ny WHERE dws_ny.id = 1011; +------+------+--------+-------+-----------+ | ID | AGE | NAME | SAL | C_DATE | +------+------+--------+-------+-----------+ | 1011 | 22 | 张三 | 13.11 | 09-SEP-19 | +------+------+--------+-------+-----------+ 1 row in set -
展示执行计划。
obclient> EXPLAIN EXTENDED SELECT /*+ READ_CONSISTENCY(WEAK) */ * FROM dws_ny WHERE dws_ny.id = 1011\G *************************** 1. row *************************** Query Plan: ==================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------ |0 |TABLE GET|DWS_NY|1 |46 | ==================================== Outputs & filters: ------------------------------------- 0 - output([DWS_NY.ID(0x7f9d9cfe02e0)], [DWS_NY.USER_ID(0x7f9d9cfe0690)], [DWS_NY.NAME(0x7f9d9cfe0980)], [DWS_NY.SAL(0x7f9d9cfe0c70)], [DWS_NY.C_DATE(0x7f9d9cfe0f60)]), filter(nil), access([DWS_NY.ID(0x7f9d9cfe02e0)], [DWS_NY.USER_ID(0x7f9d9cfe0690)], [DWS_NY.NAME(0x7f9d9cfe0980)], [DWS_NY.SAL(0x7f9d9cfe0c70)], [DWS_NY.C_DATE(0x7f9d9cfe0f60)]), partitions(p0), is_index_back=false, range_key([DWS_NY.ID(0x7f9d9cfe02e0)]), range[1011 ; 1011], range_cond([DWS_NY.ID(0x7f9d9cfe02e0) = 1011(0x7f9d9cfdfbc0)]) Used Hint: ------------------------------------- /*+ READ_CONSISTENCY("WEAK") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "SYS.DWS_NY"@"SEL$1") READ_CONSISTENCY("WEAK") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- DWS_NY:table_rows:9, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback Parameters ------------------------------------- 1 row in set
示例 2：指定 SQL 强制走索引
-
通过指定 SQL 强制走索引，查询出
id > 1018的数据。obclient> SELECT /*+ INDEX (dws_ny idx7)*/ id, age FROM dws_ny WHERE id > 1018; +------+------+ | ID | AGE | +------+------+ | 1027 | 22 | | 1022 | 22 | | 1033 | 22 | | 1035 | 22 | | 1021 | 22 | | 1026 | 22 | +------+------+ 6 rows in set -
展示执行计划。
obclient> EXPLAIN EXTENDED SELECT /*+ INDEX (dws_ny idx7)*/ id, age FROM dws_ny WHERE id > 1018\G *************************** 1. row *************************** Query Plan: =========================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------- |0 |TABLE SCAN|DWS_NY(IDX7)|1 |92 | =========================================== Outputs & filters: ------------------------------------- 0 - output([DWS_NY.ID(0x7f9de45e0a90)], [DWS_NY.AGE(0x7f9de45e1480)]), filter([DWS_NY.ID(0x7f9de45e0a90) > 1018(0x7f9de45e0370)]), access([DWS_NY.ID(0x7f9de45e0a90)], [DWS_NY.AGE(0x7f9de45e1480)]), partitions(p0), is_index_back=true, filter_before_indexback[true], range_key([DWS_NY.C_DATE(0x7f9de4621fb0)], [DWS_NY.ID(0x7f9de45e0a90)]), range(MIN,MIN ; MAX,MAX)always true Used Hint: ------------------------------------- /*+ INDEX(@"SEL$1" "SYS.DWS_NY"@"SEL$1" "IDX7") */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "SYS.DWS_NY"@"SEL$1" "IDX7") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- DWS_NY:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX7], pruned_index_name[DWS_NY], estimation info[table_id:1100611139453852, (table_type:1, version:0-1654452010335323-1654452010335323, logical_rc:0, physical_rc:0), (table_type:0, version:1654452010335323-1654452010335323-9223372036854775807, logical_rc:9, physical_rc:9)] Parameters ------------------------------------- 1 row in set
示例 3：指定数据库按照表在 FROM 子句中出现的顺序进行表联接
-
通过表联接，查询出
dws_ny与dws_yn表的id相同的数据。obclient> SELECT /*+ ORDERED */ * FROM dws_ny a,dws_yn b WHERE a.id = b.id; +------+------+--------+-------+-----------+------+------+--------+-------+-----------+ | ID | AGE | NAME | SAL | C_DATE | ID | AGE | NAME | SAL | C_DATE | +------+------+--------+-------+-----------+------+------+--------+-------+-----------+ | 1011 | 22 | 张三 | 13.11 | 09-SEP-19 | 1011 | 22 | 张三 | 13.11 | 09-SEP-19 | | 1014 | 22 | 张三 | 22.21 | 08-AUG-20 | 1014 | 22 | 张三 | 22.21 | 08-AUG-20 | | 1022 | 22 | 李四 | 23.34 | 06-JUN-19 | 1022 | 22 | 李四 | 23.34 | 06-JUN-19 | | 1027 | 22 | 李四 | 12.22 | 05-MAY-19 | 1027 | 22 | 李四 | 12.22 | 05-MAY-19 | +------+------+--------+-------+-----------+------+------+--------+-------+-----------+ 4 rows in set -
展示执行计划。
obclient> EXPLAIN EXTENDED SELECT /*+ ORDERED */ * FROM dws_ny a,dws_yn b WHERE a.id = b.id\G *************************** 1. row *************************** Query Plan: ==================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ------------------------------------ |0 |MERGE JOIN | |9 |96 | |1 | TABLE SCAN|A |9 |46 | |2 | TABLE SCAN|B |9 |46 | ==================================== Outputs & filters: ------------------------------------- 0 - output([A.ID(0x7f9de45e0970)], [A.AGE(0x7f9de45e1010)], [A.NAME(0x7f9de45e1300)], [A.SAL(0x7f9de45e15f0)], [A.C_DATE(0x7f9de45e18e0)], [B.ID(0x7f9de45e0c60)], [B.AGE(0x7f9de45e1bd0)], [B.NAME(0x7f9de45e1ec0)], [B.SAL(0x7f9de45e21b0)], [B.C_DATE(0x7f9de45e24a0)]), filter(nil), equal_conds([A.ID(0x7f9de45e0970) = B.ID(0x7f9de45e0c60)(0x7f9de45e0250)]), other_conds(nil), merge_directions([ASC]) 1 - output([A.ID(0x7f9de45e0970)], [A.AGE(0x7f9de45e1010)], [A.NAME(0x7f9de45e1300)], [A.SAL(0x7f9de45e15f0)], [A.C_DATE(0x7f9de45e18e0)]), filter(nil), access([A.ID(0x7f9de45e0970)], [A.AGE(0x7f9de45e1010)], [A.NAME(0x7f9de45e1300)], [A.SAL(0x7f9de45e15f0)], [A.C_DATE(0x7f9de45e18e0)]), partitions(p0), is_index_back=false, range_key([A.ID(0x7f9de45e0970)]), range(MIN ; MAX)always true 2 - output([B.ID(0x7f9de45e0c60)], [B.AGE(0x7f9de45e1bd0)], [B.NAME(0x7f9de45e1ec0)], [B.SAL(0x7f9de45e21b0)], [B.C_DATE(0x7f9de45e24a0)]), filter(nil), access([B.ID(0x7f9de45e0c60)], [B.AGE(0x7f9de45e1bd0)], [B.NAME(0x7f9de45e1ec0)], [B.SAL(0x7f9de45e21b0)], [B.C_DATE(0x7f9de45e24a0)]), partitions(p0), is_index_back=false, range_key([B.ID(0x7f9de45e0c60)]), range(MIN ; MAX)always true Used Hint: ------------------------------------- /*+ ORDERED */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("SYS.A"@"SEL$1" "SYS.B"@"SEL$1" )) USE_MERGE(@"SEL$1" ("SYS.B"@"SEL$1" )) PQ_DISTRIBUTE(@"SEL$1" ("SYS.B"@"SEL$1" ) LOCAL LOCAL) FULL(@"SEL$1" "SYS.A"@"SEL$1") FULL(@"SEL$1" "SYS.B"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- A:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[DWS_NY], pruned_index_name[IDX7], estimation info[table_id:1100611139453851, (table_type:1, version:0-1654452010335323-1654452010335323, logical_rc:0, physical_rc:0), (table_type:0, version:1654452010335323-1654452010335323-9223372036854775807, logical_rc:9, physical_rc:9)] B:table_rows:9, physical_range_rows:9, logical_range_rows:9, index_back_rows:0, output_rows:9, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[DWS_YN], estimation info[table_id:1100611139453853, (table_type:1, version:0-1654452010335323-1654452010335323, logical_rc:0, physical_rc:0), (table_type:0, version:1654452010335323-1654452010335323-9223372036854775807, logical_rc:9, physical_rc:9)] Parameters ------------------------------------- 1 row in set
示例 4：使用 SQL Hint 指定查询超时时间为 10 秒
-
通过 SQL Hint 指定
dws_ny表查询超时时间为 10 秒的数据。obclient> SELECT /*+ query_timeout(10000000) */ id,age,name,sal FROM dws_ny WHERE id=1011 and age=22 AND sal=13.11; +------+------+--------+-------+ | ID | AGE | NAME | SAL | +------+------+--------+-------+ | 1011 | 22 | 张三 | 13.11 | +------+------+--------+-------+ 1 row in set -
展示执行计划。
obclient> EXPLAIN EXTENDED SELECT /*+ query_timeout(10000000) */ id,age,name,sal FROM dws_ny WHERE id=1011 and age=22 AND sal=13.11; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ==================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------ |0 |TABLE GET|DWS_NY|1 |46 | ==================================== Outputs & filters: ------------------------------------- 0 - output([DWS_NY.ID(0x7f9de45e1b80)], [DWS_NY.AGE(0x7f9de45e2c20)], [DWS_NY.NAME(0x7f9de45e49d0)], [DWS_NY.SAL(0x7f9de45e3cc0)]), filter([DWS_NY.AGE(0x7f9de45e2c20) = 22(0x7f9de45e2500)], [DWS_NY.SAL(0x7f9de45e3cc0) = 13.11(0x7f9de45e35a0)]), access([DWS_NY.ID(0x7f9de45e1b80)], [DWS_NY.AGE(0x7f9de45e2c20)], [DWS_NY.SAL(0x7f9de45e3cc0)], [DWS_NY.NAME(0x7f9de45e49d0)]), partitions(p0), is_index_back=false, filter_before_indexback[false,false], range_key([DWS_NY.ID(0x7f9de45e1b80)]), range[1011 ; 1011], range_cond([DWS_NY.ID(0x7f9de45e1b80) = 1011(0x7f9de45e1460)]) Used Hint: ------------------------------------- /*+ QUERY_TIMEOUT(10000000) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$1" "SYS.DWS_NY"@"SEL$1") QUERY_TIMEOUT(10000000) END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- DWS_NY:table_rows:9, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback Parameters ------------------------------------- | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set
扫一扫
247
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
