本文介绍了在5.2及以下版本适用HINT的方法。

基本语法

 /!TDDL:hint command*/

PolarDB-X 1.0自定义HINT是借助于MySQL 注释实现的,也就是PolarDB-X 1.0的自定义HINT语句位于/!*/之间,并且必须以TDDL:开头。其中hint commandPolarDB-X 1.0自定义HINT命令,与具体的操作相关。

例如下面的SQL语句通过PolarDB-X 1.0的自定义HINT展示每个分库的表名。

/!TDDL:SCAN*/SHOW TABLES;

该SQL语句中/!TDDL:SCAN*/PolarDB-X 1.0自定义HINT部分,以TDDL:开头,SCANPolarDB-X 1.0自定义HINT命令。

读写分离

由于RDS主实例与只读实例之间数据的同步存在着毫秒级别的延迟,如果在主库中变更后需要马上读取变更的数据,则需要保证将读取数据的SQL下发到主实例中。针对这种需求,PolarDB-X 1.0提供了读写分离自定义HINT,指定将SQL下发到主实例或者只读实例。

语法

/!TDDL:MASTER|SLAVE*/

在该自定义HINT中可以指定SQL是在主实例上执行还是在只读实例上执行。对于/!TDDL:SLAVE*/这个自定义HINT,如果一个主RDS实例存在多个只读实例,那么PolarDB-X 1.0会根据所分配的权重随机选择一个只读实例执行SQL语句。

示例

  • 指定SQL在主实例上执行:
    /!TDDL:MASTER*/SELECT * FROM table_name;

    在SQL语句前添加/!TDDL:MASTER*/这个自定义HINT后,这条SQL将被下发到主实例上执行。

  • 指定SQL在只读实例上执行:
    /!TDDL:SLAVE*/SELECT * FROM table_name;

    在SQL语句前添加/!TDDL:SLAVE*/这个自定义HINT后,这条SQL将会根据所分配的权重被随机下发到某个只读实例上执行。

注意
  • 此读写分离自定义HINT仅仅针对非事务中的读SQL语句生效,如果SQL语句是写SQL或者SQL语句在事务中,那么还是会下发到RDS的主实例执行。
  • PolarDB-X 1.0针对/!TDDL:SLAVE*/自定义HINT,会从只读实例中按照权重随机选取一个下发SQL语句执行。若只读实例不存在时,不会报错,而是选取主实例执行。

只读实例延迟切断

正常情况下,如果给PolarDB-X 1.0数据库的RDS主实例配置了只读实例,并且给主实例和只读实例都设置了读流量,那么PolarDB-X 1.0会根据读写比例将SQL下发到主实例或者是只读实例执行。但是如果主实例与只读实例的异步数据复制存在较大的延迟,将SQL下发到只读实例执行就会导致出错或者返回错误结果。

只读实例延时切断会根据主备复制最大延时时间判断将所执行的SQL下发到主实例还是只读实例。

语法

/!TDDL:SQL_DELAY_CUTOFF=time*/

在自定义HINT中指定SQL_DELAY_CUTOFF的值,当备库的 SQL_DELAY值(MySQL主备复制延迟)达到或超过time的值(单位秒)时,查询语句会被下发到主实例。

示例

  • 指定主备复制延迟时间为5秒:
    /!TDDL:SQL_DELAY_CUTOFF=5*/SELECT * FROM table_name;

    在SQL语句指定了SQL_DELAY_CUTOFF的值为5,当备库的SQL_DELAY值达到或超过5秒时,查询语句会下发到主实例执行。

  • 配合其他自定义HINT使用:
    /!TDDL:SLAVE AND SQL_DELAY_CUTOFF=5*/SELECT * FROM table_name;

    备库延迟切断注释也可以配合其他注释使用,该SQL查询请求默认会被下发到只读实例,但是当出现主备复制延时达到或超过5秒时,会下发到主实例。

自定义SQL超时时间

PolarDB-X 1.0中,PolarDB-X 1.0节点与RDS的默认的SQL执行超时时间是900秒(可以调整),但是对于某些特定的慢SQL,其执行时间可能超过了900秒 。针对这种慢SQL,PolarDB-X 1.0提供了调整超时时间的自定义HINT。通过这个自定义HINT可以任意调整SQL执行时长。

语法

/!TDDL:SOCKET_TIMEOUT=time*/

其中,SOCKET_TIMEOUT的单位是毫秒。通过该 HINT 用户可以根据业务需要,自由调整SQL语句的超时时间。

示例

设置SQL超时时间为40秒:

/!TDDL:SOCKET_TIMEOUT=40000*/SELECT * FROM t_item;
说明 超时时间设置得越长,占用数据库资源的时间就会越长。如果同一时间长时间执行的SQL过多,可能消耗大量的数据库资源,从而导致无法正常使用数据库服务。所以,对于长时间执行的SQL语句,尽量对SQL语句进行优化。

指定分库执行SQL

在使用PolarDB-X 1.0的过程中,如果遇到某个PolarDB-X 1.0不支持的SQL语句,可以通过PolarDB-X 1.0提供的自定义HINT,直接将SQL下发到一个或多个分库上去执行。此外如果需要单独查询某个分库或者已知分库的某个分表中的数据,也可以使用该自定义HINT,直接将SQL语句下发到分库中执行。

指定分库执行SQL自定义HINT有两种使用方式,即通过分片名指定SQL在分库上执行或者通过分库键值指定SQL在分库上执行。其中分片名是PolarDB-X 1.0中分库的唯一标识,可以通过SHOW NODE控制指令得到。

语法

  • /!TDDL:NODE='node_name'*/

    node_name为分片名,通过这个PolarDB-X 1.0自定义HINT,就可以将SQL下发到node_name对应的分库中执行。

  • /!TDDL:NODE IN ('node_name'[,'node_name1','node_name2'])*/

    使用in关键字指定多个分片名,将SQL下发到多个分库上执行,括号内分片名之间使用逗号分隔。

    说明 使用该自定义HINT时,PolarDB-X 1.0会将SQL直接下发到分库上执行,所以在SQL语句中,表名必须是该分库中已经存在的表名。
  • /!TDDL:table_name.partition_key=value [and table_name1.partition_key=value1]*/

    在这个PolarDB-X 1.0自定义HINT中table_name为逻辑表名,该表是一张拆分表,partition_key是拆分键,value为指定的拆分键的值。在该自定义注释中,可以使用and关键字指定多个拆分表的拆分键。通过这个PolarDB-X 1.0自定义HINT,PolarDB-X 1.0会计算出SQL语句应该在哪些分库和分表上执行,进而将SQL语句下发到相应的分库。

示例

对于名为drds_testPolarDB-X 1.0数据库,SHOW NODE的结果如下:

mysql> SHOW NODE\G
*************************** 1. row ******************
                 ID: 0
               NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS
  MASTER_READ_COUNT: 212
   SLAVE_READ_COUNT: 0
MASTER_READ_PERCENT: 100%
 SLAVE_READ_PERCENT: 0%
*************************** 2. row ******************
                 ID: 1
               NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0001_RDS
  MASTER_READ_COUNT: 29
   SLAVE_READ_COUNT: 0
MASTER_READ_PERCENT: 100%
 SLAVE_READ_PERCENT: 0%
*************************** 3. row ******************
                 ID: 2
               NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0002_RDS
  MASTER_READ_COUNT: 29
   SLAVE_READ_COUNT: 0
MASTER_READ_PERCENT: 100%
 SLAVE_READ_PERCENT: 0%
*************************** 4. row ******************
                 ID: 3
               NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0003_RDS
  MASTER_READ_COUNT: 29
   SLAVE_READ_COUNT: 0
MASTER_READ_PERCENT: 100%
 SLAVE_READ_PERCENT: 0%
*************************** 5. row ******************
                 ID: 4
               NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0004_RDS
  MASTER_READ_COUNT: 29
   SLAVE_READ_COUNT: 0
MASTER_READ_PERCENT: 100%
 SLAVE_READ_PERCENT: 0%
*************************** 6. row ******************
                 ID: 5
               NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0005_RDS
  MASTER_READ_COUNT: 29
   SLAVE_READ_COUNT: 0
MASTER_READ_PERCENT: 100%
 SLAVE_READ_PERCENT: 0%
*************************** 7. row ******************
                 ID: 6
               NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS
  MASTER_READ_COUNT: 29
   SLAVE_READ_COUNT: 0
MASTER_READ_PERCENT: 100%
 SLAVE_READ_PERCENT: 0%
*************************** 8. row ******************
                 ID: 7
               NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0007_RDS
  MASTER_READ_COUNT: 29
   SLAVE_READ_COUNT: 0
MASTER_READ_PERCENT: 100%
 SLAVE_READ_PERCENT: 0%
8 rows in set (0.02 sec)

可以看到每个分库都有NAME这个属性,这就是分库的分片名。每个分片名都唯一对应一个分库名,比如DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0003_RDS这个分片名对应的分库名是drds_test_vtla_0003。得到了分片名,就可以使用PolarDB-X 1.0的自定义HINT指定分库执行SQL语句了。

  • 指定SQL在第0个分库上执行:
    /!TDDL:NODE='DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS'*/SELECT * FROM table_name;
  • 指定SQL在多个分库上执行:
    /!TDDL:NODE  IN('DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS','DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS')*/SELECT * FROM table_name;

    这条SQL语句将在DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDSDRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS这两个分片上执行。

  • 查看某个分库的执行计划:
    /!TDDL:NODE='DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS'*/EXPLAIN SELECT * FROM table_name;

    这条SQL语句将会展示SELECT语句在分片DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS中的执行计划。

  • 通过键值指定SQL在分库上执行:

    对于UPDATE语句,PolarDB-X 1.0不支持SET子句中的子查询,由于UPDATE语句在PolarDB-X 1.0中必须指定拆分键,所以可以使用PolarDB-X 1.0的自定义HINT将该语句直接下发到分库上执行。

    比如有两张逻辑表,分别是t1和t2,它们都是分库分表,建表语句如下:

      CREATE TABLE `t1` (
        `id` bigint(20) NOT NULL,
        `name` varchar(20) NOT NULL,
        `val` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`id`) tbpartition by hash(`name`) tbpartitions 3
    
      CREATE TABLE `t2` (
        `id` bigint(20) NOT NULL,
        `name` varchar(20) NOT NULL,
        `val` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`id`) tbpartition by hash(`name`) tbpartitions 3

    需要执行的语句是:

    UPDATE t1 SET val=(SELECT val FROM t2 WHERE id=1) WHERE id=1;

    这条语句直接在PolarDB-X 1.0上执行会报不被支持的错误,但是可以给这条语句加上PolarDB-X 1.0的自定义HINT,再提交到PolarDB-X 1.0执行。具体SQL语句如下:

    /!TDDL:t1.id=1 and t2.id=1*/UPDATE t1 SET val=(SELECT val FROM t2 WHERE id=1) WHERE id=1;

    这条语句会被下发到t1ID为1的分库上执行。通过explain命令可以看到执行这条SQL语句的执行计划:

      mysql> explain /!TDDL:t1.id=1 and t2.id=1*/UPDATE t1 SET val=(SELECT val FROM t2 WHERE id=1) WHERE id=1\G
      *************************** 1. row ***************************
      GROUP_NAME: TEST_DRDS_1485327111630IXLWTEST_DRDS_IGHF_0001_RDS
             SQL: UPDATE `t1_2` AS `t1` SET `val` = (SELECT val FROM `t2_2` AS `t2` WHERE `id` = 1) WHERE `id` = 1
          PARAMS: {}
      *************************** 2. row ***************************
      GROUP_NAME: TEST_DRDS_1485327111630IXLWTEST_DRDS_IGHF_0001_RDS
             SQL: UPDATE `t1_1` AS `t1` SET `val` = (SELECT val FROM `t2_1` AS `t2` WHERE `id` = 1) WHERE `id` = 1
          PARAMS: {}
      *************************** 3. row ***************************
      GROUP_NAME: TEST_DRDS_1485327111630IXLWTEST_DRDS_IGHF_0001_RDS
             SQL: UPDATE `t1_0` AS `t1` SET `val` = (SELECT val FROM `t2_0` AS `t2` WHERE `id` = 1) WHERE `id` = 1
          PARAMS: {}
      3 rows in set (0.00 sec)

    explain命令的结果集可以看到,SQL语句被改写成3条语句下发到分库上执行。还可以继续指定分表键值,将SQL执行范围缩小到一张分表:

      mysql> explain /!TDDL:t1.id=1 and t2.id=1 and t1.name='1'*/UPDATE t1 SET val=(SELECT val FROM t2 WHERE id=1) WHERE id=1\G
      *************************** 1. row ***************************
      GROUP_NAME: TEST_DRDS_1485327111630IXLWTEST_DRDS_IGHF_0001_RDS
             SQL: UPDATE `t1_1` AS `t1` SET `val` = (SELECT val FROM `t2_1` AS `t2` WHERE `id` = 1) WHERE `id` = 1
          PARAMS: {}
      1 row in set (0.00 sec)
说明 使用该自定义注释需要保证两张表的分库和分表数量一致,否则PolarDB-X 1.0计算出的两个键值对应的分库不一致,就会报错。

扫描全部分库分表

除了可以将SQL单独下发到一个或多个分库执行,PolarDB-X 1.0还提供了扫描全部分库与分表的自定义HINT。通过这个自定义HINT,您可以一次将SQL下发到每一个分库执行。比如通过这个自定义HINT,可以查看某个分库上的所有分表。还可以通过这个自定义HINT,查看某个逻辑表的每个分库的分表数据量。

扫描全部分片的PolarDB-X 1.0自定义HINT有两种使用方式,第一种方式是将SQL语句下发到每个分库执行,第二种方式是将SQL语句下发到每个分库上对某个逻辑表进行操作。

  • 将SQL下发到全部分库上执行:
    /!TDDL:SCAN*/
  • 对某个逻辑表进行操作:
    /!TDDL:SCAN='table_name'*/

    其中table_namePolarDB-X 1.0数据库的某个逻辑表名。该自定义HINT是为分库分表提供的,请尽量确保table_name为分库分表。