HINT syntax and examples for executing SQL on specific database shards-PolarDB(PolarDB)-阿里云帮助中心

PolarDB-X NODE hints let you route SQL statements directly to one or more database shards by name. Use them when a SQL statement cannot run on the PolarDB-X instance layer, or when you need to query or inspect data in a specific shard.

If hints are not taking effect, see Usage notes.

Use cases

Route unsupported statements: Some SQL statements cannot run on the PolarDB-X instance layer. Use a NODE hint to push them directly to a database shard.
Query a specific shard: Read data from a particular database shard or a table shard within it.
Inspect execution plans: Run EXPLAIN against a specific shard to view its physical execution plan.

Syntax

All NODE hints follow the /*+TDDL:hint_command*/ format and embed directly in your SQL statement.

Single shard

/*+TDDL:node('node_name')*/

Multiple shards

/*+TDDL:node('node_name'[,'node_name1','node_name2'])*/

Separate multiple shard names with commas. PolarDB-X routes the statement to each named shard.

NODE hints work in DML (Data Manipulation Language), DDL (Data Definition Language), and DAL (Data Access Language) statements.

Important

If a NODE hint is in an INSERT statement that contains a sequence definition for the target table, the sequence does not take effect.

Route SQL statements to a shard

Follow these steps to find shard names and then route your statements.

Step 1: Get shard names

Run SHOW NODE to list all shards in the database and their names.

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)

Each row's NAME value uniquely identifies a database shard. For example, DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0003_RDS identifies the drds_test_vtla_0003 shard. The table you reference in your SQL statement must exist in the target shard.

Step 2: Route statements

Use the shard name from SHOW NODE in your NODE hint.

Query a single shard (shard ID 0)

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

Query multiple shards

SELECT /*TDDL:node('DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS','DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS')*/ * FROM table_name;

This statement runs on both DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS and DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS.

Step 3: Verify with EXPLAIN

Check the physical execution plan for a statement on a specific shard.

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

Usage notes

Supported hint formats

PolarDB-X accepts two hint formats:

/*+TDDL:hint_command*/
/!+TDDL:hint_command*/

MySQL command-line client strips hints

If you use the /*+TDDL:hint_command*/ format in the official MySQL command-line client, add the -c flag when connecting. Without it, the client strips MySQL comments — including PolarDB-X custom hints — before sending statements to the server.

mysql -h <host> -P <port> -u <username> -p -c

For more information, see mysql client options and MySQL comments.