Specify database shards to execute SQL statements
This topic describes the hint syntax that is used to specify one or more database shards on which you want to execute an SQL statement. This topic also provides examples.
When you execute SQL statements on a PolarDB-X instance, you may find that some SQL statements cannot be executed on the PolarDB-X instance. PolarDB-X provides custom NODE hints to resolve this issue. You can use these hints to route SQL statements to one or more database shards. If you need to query the data in a specified database shard or in a specified table shard of a database shard, you can also use these custom NODE hints to route the SQL statement to the specified database shard.
Syntax
NODE hints allow you to specify the database shards on which you want to execute an SQL statement by name. A shard name uniquely identifies a database shard in a PolarDB-X instance. You can execute the SHOW NODE statement to obtain the name of each shard of a database.
You can specify the names of one or more database shards on which you want to execute SQL statements. Important If a NODE hint is contained in an INSERT statement and this INSERT statement contains a sequence definition for the table on which the SQL statement is executed, the sequence does not take effect.
Specify one database shard on which you want to execute SQL statements.
/*+TDDL:node('node_name')*/
node_name specifies the name of the shard. PolarDB-X routes the SQL statements that contain this custom hint to the database shard that is specified by node_name.
Specify multiple database shards on which you want to execute SQL statements.
/*+TDDL:node('node_name'[,'node_name1','node_name2'])*/
You can specify multiple shard names and separate the shard names with commas (,). PolarDB-X routes the SQL statements that contain this custom hint to the specified database shards. Note
If an SQL statement contains a custom NODE hint, PolarDB-X routes the SQL statement to the specified database shards for execution. Therefore, you must specify a table that exists in the specified database shards in the SQL statement.
NODEhints can be used in DML statements, DDL statements, and Data Access Language (DAL) statements.
Additional considerations
PolarDB-X allows you to customize a hint by using one of the following formats:
/*+TDDL:hint_command*/and/!+TDDL:hint_command*/.In the official MySQL command-line client, if you execute SQL statements that contain PolarDB-X custom hints in the
/*+TDDL:hint_command*/format, add the-cparameter in the command that is used to log on to the client. If you do not add the -c parameter, the client deletes MySQL comments in SQL statements before it sends the SQL statements to servers for execution. The PolarDB-X custom hints that use the/*+TDDL:hint_command*/format are defined as MySQL comments. As a result, the PolarDB-X custom hints are deleted and cannot take effect. For more information, see mysql client options.
Examples
Execute the SHOW NODE statement to display the shard names of a PolarDB-X database that is named drds_test.
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)
The response shows that each database shard has the NAME attribute. This attribute indicates the name of the database shard. Each shard name in the response identifies a unique database shard. For example, the DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0003_RDS shard name identifies the drds_test_vtla_0003 database shard. After you obtain the shard names, you can use custom NODE hints of PolarDB-X to specify database shards on which you want to execute SQL statements.
Execute an SQL statement on the database shard whose ID is 0.
SELECT /*TDDL:node('DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS')*/ * FROM table_name;
Execute an SQL statement on multiple database shards.
SELECT /*TDDL:node('DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS','DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS')*/ * FROM table_name;
This SQL statement is executed on the database shards whose names are DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS and DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS.
Query the physical execution plan of an SQL statement on the database shard whose ID is 0.
/*TDDL:node('DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS')*/ EXPLAIN SELECT * FROM table_name; ```