本文介绍了在5.2及以下版本适用HINT的方法。
基本语法
/!TDDL:hint command*/
PolarDB-X 1.0自定义HINT是借助于MySQL 注释实现的,也就是PolarDB-X 1.0的自定义HINT语句位于/!
与*/
之间,并且必须以TDDL:
开头。其中hint command
是PolarDB-X 1.0自定义HINT命令,与具体的操作相关。
例如下面的SQL语句通过PolarDB-X 1.0的自定义HINT展示每个分库的表名。
/!TDDL:SCAN*/SHOW TABLES;
该SQL语句中/!TDDL:SCAN*/
为PolarDB-X 1.0自定义HINT部分,以TDDL:
开头,SCAN
为PolarDB-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
在使用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_test
的PolarDB-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_RDS
,DRDS_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;
这条语句会被下发到
t1
的ID
为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)
扫描全部分库分表
除了可以将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_name
是PolarDB-X 1.0数据库的某个逻辑表名。该自定义HINT是为分库分表提供的,请尽量确保table_name
为分库分表。