全部产品

自定义 HINT

更新时间:2020-11-26 11:18:52

HINT 作为一种 SQL 补充语法,在关系型数据库中扮演着非常重要的角色。它允许用户通过相关的语法影响 SQL 的执行方式,从对 SQL 进行特殊的优化。

同样,数据访问代理也提供了自定义的 HINT 语法。HINT 使用基本语法:

  1. /*+DBP: hint 语句 */ SQL 语句

数据访问代理自定义 HINT 基于 MySQL 注释 实现,其 HINT 语句会位于 /**/ 之间,并且必须以 +DBP: 开头,用以识别为数据访问代理的 HINT 语法。HINT 语句是数据访问代理的 HINT 命令,目前仅提供路由相关的 HINT 命令,后续会提供更丰富的 HINT 使用场景。

自定义路由 HINT

分库分表场景下使用数据访问代理时,每一个 SQL 请求语句中都需要存在分库分表字段。在无法获得分库分表字段场景下,如果需要请求指定数据分片、分表执行 SQL 语句时,可以通过自定义路由 HINT 实现。

自定义路由 HINT 基本语法:

  1. /*+DBP: $ROUTE={GROUP_ID(分片位),TABLE_NAME(物理表名)}*/ SQL 语句

路由 HINT 使用说明:

  • GROUP_ID 表示指定的分片 ID,数值类型
  • TABLE_NAME 表示数据所在的物理表名。如果仅存在分库规则,无分表规则的话,TABLE_NAME 无需配置

    1. mysql> /*+DBP: $ROUTE={GROUP_ID(0),TABLE_NAME(user_00)}*/ SELECT * FROM user WHERE id = 1;
    2. +------+------+
    3. | id | name |
    4. +------+------+
    5. | 1 | foo |
    6. +------+------+
    7. 1 row in set (0.00 sec)

全表扫描 HINT

自定义路由 HINT 需要知道数据的路由结果,如果无法知道数据的路由结果可以通过全表扫描 HINT 来扫描所有的分库分表获取数据。

全表扫描 HINT 基本语法:

  1. /*+DBP: $ROUTE={SCAN_ALL()}*/ SQL 语句
  1. mysql> /*+DBP: $ROUTE={SCAN_ALL()}*/ SELECT * FROM user;
  2. +------+-------+
  3. | id | name |
  4. +------+-------+
  5. | 1 | foo1 |
  6. | 2 | foo2 |
  7. +------+-------+
  8. 1 row in set (0.00 sec)

虚拟字段路由 HINT

当进行过数据分片(即采取了分库分表)时,都是基于某一特定字段,或者某两个字段进行拆分。这样在执行所有的数据库访问时都需要提供这些字段的 Value,否则数据路由将报错。然而,有时在执行 INSERT、UPDATE、SELECT 时 SQL 中的参数并不包含分库分表字段,这时我们需要使用 虚拟字段路由 HINT 来解决该问题。

添加 SHARIND_KEY 关键字:$ROUTE={SHARDING_KEY(key1=value1,key2='value2')}
key 为虚拟字段名称,value 为虚拟字段值。

如:

  • id=’123’,类型为字符串,单引号和双引号均可。

    1. /_+DBP: $ROUTE={SHARDING_KEY(id='123')} _/
  • id=123,类型为数字,真实类型是 java.lang.Integer。这里暂时不处理 long 类型,全都视为integer。

    1. /_+DBP: $ROUTE={SHARDING_KEY(id=123)} _/
  • 多个 sharding key,注意可以两边加入空格。

    1. /_+DBP: $ROUTE={SHARDING_KEY(id='123', name = 'abc' )} _/

注意事项

MySQL 命令行客户端

由于数据访问代理自定义 HINT 是以 MySQL 注释 的形式使用的,在使用 MySQL 官方命令行客户端执行带有数据访问代理自定义 HINT 的 SQL 时,请在登录命令中加上 -c 参数。否则,该客户端会将注释语句删掉再发送到服务端执行,导致数据访问代理自定义 HINT 失效。具体请查看 MySQL 官方客户端命令

全表扫描

  • 要避免全表扫描对后端数据库的压力,请尽量减少全表扫描 HINT 使用。如需使用,也请加上合理的查询过滤条件。

  • 全表扫描会读取多个表的结果做合并,如果使用 LIMIT start,offset 得到的结果无法保证正确性。所以,全表扫描 HINT 仅支持 LIMIT offset 以及 LIMIT 0, offset 语法。

  • 全表扫描在超时情况可能会引起流式结果集问题。报错信息如下:

    1. Streaming result set com.mysql.jdbc.RowDataDynamic is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.

    此时,您可以在 ODP 连接参数的 connectionProperties 中添加 clobberStreamingResults=true 配置,并使其生效,即可解决该问题。