Specify database shards where an SQL statement is to be executed - PolarDB

Information provided in this topic is applicable to PolarDB-X 1.0 V5.3 and later.

When you are using PolarDB-X 1.0, if you encounter anSQL statement that is not supported by PolarDB-X 1.0, you can useNODE HINT provided by PolarDB-X 1.0. NODE HINT can route an SQL statement to one or more database shards on which you want to execute the SQL statement. If you need to query the data in a specified database shard or in a specified table shard of a database shard, you can use NODE HINT to route the SQL statement to the database shard.

Syntax

NODE HINT allows you to specify the names of the database shards on which you want to execute an SQL statement. A shard name isthe unique identifier of a database shard in a PolarDB-X 1.0 database. To query the names of the database shards in a database, you can execute the SHOW NODE statement.

You can use two methods to specify the names of the database shards on which an SQL statement is executed. One of the methods is to specify only one database shard on which you want to execute the SQL statement. The other method is to specify multiple database shards on which you want to execute the SQL statement.

Notice If the hint statement that is used to specify database shards 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. For more information, see Limits.

Specify a database shard on which you want to execute an SQL statement.
```
/*+TDDL:node('node_name')*/            
```
node_name specifies the name of a database shard. You can customize a PolarDB-X 1.0 hint to route an SQL statement to a database shard that is specified by node_name for execution.
Specify multiple database shards on which you want to execute an SQL statement.
```
/*+TDDL:node('node_name'[,'node_name1','node_name2'])*/               
```
You can specify multiple shard names and separate the shard names with commas (,). The SQL statement that contains the specified hint is routed to the specified database shards.
Note
- When you execute an SQL statement that contains a DRDS hint, PolarDB-X 1.0 routes the SQL statement to the database shards for execution. The table names that are specified in the SQL statement must exist in the specified database shards.
- NODE HINT can be used in DML statements, DDL statements, and Data Access Language (DAL) statements.

Note

In DRDS V5.4.1 and later, PolarDB-X 1.0 adds a four-character random string to each of the names of the physical tables that correspond to table shards. You must execute the SHOW TOPOLOGY statement to obtain the topological relationships of logical tables and the names of physical tables.
In DRDS V5.4.4 and later, PolarDB-X 1.0 provides a switch to control whether the name of each physical table for table shards contains a random string. By default, this switch is turned on. To turn off the switch, you can log on to the DRDS console and then click the ID of the instance that you want to manage. In the left-side navigation pane of the instance details page, click Parameter Settings. On the page that appears, click the Database tab, and set the value of ENABLE_RANDOM_PHY_TABLE_NAME to false. You can also use the following hint to turn off the switch for the tables that are specified in an SQL statement: /*+TDDL:cmd_extra(ENABLE_RANDOM_PHY_TABLE_NAME=FALSE)*/.
You can specify PolarDB-X 1.0 hints in the following formats: /*+TDDL:hint_command*/ and /!+TDDL:hint_command*/.
If you specify hints in the /*+TDDL:hint_command*/ format, add the -c parameter to the command used to log on to the MySQL command-line client: mysql. This way, you can execute SQL statements that contain the PolarDB-X 1.0 hints on the client. If you do not add the -c parameter, the client deletes comments in the SQL statements before it sends the SQL statements to servers for execution. PolarDB-X 1.0 hints in this format are defined as MySQL comments. Therefore, the PolarDB-X 1.0 hints are deleted and cannot take effect. For more information, see mysql client options.

Examples

The following example shows the result that is returned by SHOW NODE for a PolarDB-X 1.0 database 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 result shows that each database shard has the NAME attribute. This attribute indicates the name of the database shard. Each shard name in the returned result corresponds to one unique database shard. For example, the shard name DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0003_RDS corresponds to the database shard drds_test_vtla_0003. After you obtain the shard names, you can use a PolarDB-X 1.0 hint to specify the database shards on which you want to execute an SQL statement.

Execute an SQL statement on database shard 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;
              
```
The SQL statement is executed on the DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS shard and the DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS shard.

View the physical execution plan of an SQL statement on database shard 0.

/*TDDL:node('DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS')*/ EXPLAIN SELECT * FROM table_name; ```