All Products
Search
Document Center

Specify a database shard to execute SQL statements

Last Updated: Aug 12, 2020

When you execute SQL statements in a DRDS instance, you may find that some SQL statements are not supported by the DRDS instance. In this case, you can use the NODE hint provided by DRDS, to route the SQL statements to one or more 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.

This topic is applicable to Distributed Relational Database Service (DRDS) 5.3 and later versions.

Limits

  • 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 client may delete the DRDS hint, which is used in the form of a MySQL comment, before sending the SQL statement to the server for execution. In this case, the DRDS hint is invalidated. For more information, see MySQL Client Options.
  • Since DRDS 5.4.1, a 4-character random string is added to the name of a physical table, which is also called a table shard. You must run the SHOW TOPOLOGY command to obtain the topology of the corresponding logical table and the actual physical table name.
  • DRDS 5.4.4 and later versions provide a switch to control whether to add a random string to a physical table name. The switch is enabled by default. You can disable the switch in the DRDS console as follows: 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)*/.
  • 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.

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 use a shard name to specify one database shard or use shard names to specify multiple database shards to execute an SQL statement.

  • 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 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 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_14*****************_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_14*****************_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_14*****************_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_14*****************_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_14*****************_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_14*****************_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_14*****************_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_14*****************_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 view that each database shard has the NAME attribute, which indicates the shard name corresponding to the database shard. Each shard name uniquely corresponds to one database shard name. For example, the shard name DRDS_TEST_14*****************_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 the SQL statement.

  • Specify database shard 0 to execute an SQL statement:
    1. SELECT /*TDDL:node('DRDS_TEST_14*****************_TEST_VTLA_0000_RDS')*/ * FROM table_name;
  • Specify multiple database shards to execute an SQL statement:
    1. SELECT /*TDDL:node('DRDS_TEST_14*****************_TEST_VTLA_0000_RDS','DRDS_TEST_14*****************_TEST_VTLA_0006_RDS')*/ * FROM table_name;
    This SQL statement will be executed in the database shards corresponding to the shard names DRDS_TEST_14*****************_TEST_VTLA_0000_RDS and DRDS_TEST_14*****************_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_14*****************_TEST_VTLA_0000_RDS')*/ EXPLAIN SELECT * FROM table_name;