All Products
Search
Document Center

Specify a database shard to execute an SQL statement

Last Updated: Aug 18, 2020

When you execute SQL statements in a DRDS database, you may find that some SQL statements are not supported by the DRDS database. In this case, you can use the NODE hint provided by DRDS, to route the SQL statements to one or more ApsaraDB RDS for MySQL database shards for execution. In addition, if you need to query the data in a specified database shard or the data in a specified table shard in a known database shard, you can use the NODE hint to directly route the SQL statement to the database shard for execution.

Precautions

  • Since DRDS 5.4.1, a 4-character random string is added to the name of a physical table of a partitioned logical table. You must execute the SHOW TOPOLOGY statement to obtain the topology of the logical table and the actual physical table name.

  • DRDS 5.4.4 and later provide a switch to control whether to add a random string to the name of a physical table for a partitioned logical table. The switch is turned on by default. Follow these steps to turn off the switch in the DRDS console: In the left-side navigation pane, click Instances. Click the name of the target instance. On the page that appears, choose Diagnostics and Optimization > Parameter Settings in the left-side navigation pane. Click the Database tab. Find the ENABLE_RANDOM_PHY_TABLE_NAME variable and set Whether to enable random physical table names to false in the Parameter Value column. Alternatively, you can use the following hint to implement statement-level control: /*+TDDL:cmd_extra(ENABLE_RANDOM_PHY_TABLE_NAME=FALSE)*/.

  • DRDS hints can be in the formats of /*+TDDL:hint_command*/ and /! +TDDL:hint_command*/.

  • In the MySQL Command-Line Client, if you need to execute an SQL statement that contains a DRDS hint in the format of /*+TDDL:hint_command*/, add the -c parameter to the logon command. Otherwise, the DRDS hint may become invalid, because the DRDS hint is used in the form of a MySQL comment and the MySQL Command-Line Client deletes all comments before sending the SQL statement to the server for execution. For more information, see MySQL Client Options.

Syntax

The NODE hint allows you to specify a database shard by using a shard name, to execute the SQL statement in the database shard. A shard name uniquely identifies a database shard in a DRDS instance. You can execute the SHOW NODE statement to obtain a shard name.

This custom hint allows you to specify one or more database shards to execute an SQL statement.

If the hint for specifying a database shard is used in an INSERT statement where a target table contains a DRDS sequence, the DRDS sequence will not take effect. For more information, see Limits and notes.

  • Specify one database shard to execute an SQL statement:

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

    Specifically, node_name indicates the shard name. This DRDS hint allows you to route the SQL statement to the database shard specified by node_name.

  • Specify multiple database shards to execute an SQL statement:

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

    You can specify multiple shard names in the node_name parameter to route the SQL statement to multiple database shards for execution. Separate the shard names with commas (,).

    • When this custom hint is used, the DRDS instance directly routes the SQL statement to the specified database shards for execution. Therefore, the specified shard names in the SQL statement must correspond to existing database shards.
    • The NODE hint can be used in data manipulation language (DML), data definition language (DDL), and data access language (DAL) statements.

Examples

The following information shows the output of the SHOW NODE statement for a logical database named drds_test in a DRDS instance.

  1. mysql> SHOW NODE\G
  2. *************************** 1. row ******************
  3. ID: 0
  4. NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS
  5. MASTER_READ_COUNT: 212
  6. SLAVE_READ_COUNT: 0
  7. MASTER_READ_PERCENT: 100%
  8. SLAVE_READ_PERCENT: 0%
  9. *************************** 2. row ******************
  10. ID: 1
  11. NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0001_RDS
  12. MASTER_READ_COUNT: 29
  13. SLAVE_READ_COUNT: 0
  14. MASTER_READ_PERCENT: 100%
  15. SLAVE_READ_PERCENT: 0%
  16. *************************** 3. row ******************
  17. ID: 2
  18. NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0002_RDS
  19. MASTER_READ_COUNT: 29
  20. SLAVE_READ_COUNT: 0
  21. MASTER_READ_PERCENT: 100%
  22. SLAVE_READ_PERCENT: 0%
  23. *************************** 4. row ******************
  24. ID: 3
  25. NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0003_RDS
  26. MASTER_READ_COUNT: 29
  27. SLAVE_READ_COUNT: 0
  28. MASTER_READ_PERCENT: 100%
  29. SLAVE_READ_PERCENT: 0%
  30. *************************** 5. row ******************
  31. ID: 4
  32. NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0004_RDS
  33. MASTER_READ_COUNT: 29
  34. SLAVE_READ_COUNT: 0
  35. MASTER_READ_PERCENT: 100%
  36. SLAVE_READ_PERCENT: 0%
  37. *************************** 6. row ******************
  38. ID: 5
  39. NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0005_RDS
  40. MASTER_READ_COUNT: 29
  41. SLAVE_READ_COUNT: 0
  42. MASTER_READ_PERCENT: 100%
  43. SLAVE_READ_PERCENT: 0%
  44. *************************** 7. row ******************
  45. ID: 6
  46. NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS
  47. MASTER_READ_COUNT: 29
  48. SLAVE_READ_COUNT: 0
  49. MASTER_READ_PERCENT: 100%
  50. SLAVE_READ_PERCENT: 0%
  51. *************************** 8. row ******************
  52. ID: 7
  53. NAME: DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0007_RDS
  54. MASTER_READ_COUNT: 29
  55. SLAVE_READ_COUNT: 0
  56. MASTER_READ_PERCENT: 100%
  57. SLAVE_READ_PERCENT: 0%
  58. 8 rows in set (0.02 sec)

You can see that each database shard has the NAME attribute, which indicates the shard name corresponding to the database shard. Each shard name corresponds to a unique database shard name. For example, the shard name DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0003_RDS corresponds to the database shard name drds_test_vtla_0003. After you obtain the shard name, you can use the DRDS hint to specify the corresponding database shard to execute an SQL statement.

  • Execute an SQL statement in database shard 0:

    1. SELECT /*TDDL:node('DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS')*/ * FROM table_name;
  • Execute an SQL statement in multiple database shards:

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

    The SQL statement are executed on shards DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0000_RDS and DRDS_TEST_1473471355140LRPRDRDS_TEST_VTLA_0006_RDS.

  • View the physical execution plan of an SQL statement that is to be executed in database shard 0:

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