All Products
Search
Document Center

Overview

Last Updated: Aug 18, 2020

As a supplement to the syntax of SQL statements, hints are critical to relational databases. They allow you to change execution plans of SQL statements by using relevant syntax, to specially optimize the SQL statements. DRDS also provides special hint syntax.

Assume that you know the target data is stored in a table shard in a database shard and you need to route an SQL statement directly to the database shard for execution, you can add a custom DRDS hint. For example, you can execute the following SQL statement:

  1. SELECT /*+TDDL:node('node_name')*/ * FROM table_name;

In the preceding SQL statement, the part between /* and */, namely, +TDDL:node('node_name'), is a DRDS hint. The hint specifies the ApsaraDB RDS for MySQL database shard where the SQL statement is to be executed.

Note:

  • 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 of DRDS hints

Basic syntax:

  1. /*+TDDL: hint_command [hint_command ...]*/
  2. /!+TDDL: hint_command [hint_command ...]*/

DRDS hints are used in the form of MySQL comments. DRDS hints supports /* hint */ and /! hint */ two types, and must begin with +TDDL:.The hint_command parameter indicates a DRDS hint command related to the specific operation. Multiple hint_command parameters are separated with spaces.

Example:

  1. # Query the names of physical tables in each database shard.
  2. /*+TDDL:scan()*/SHOW TABLES;
  3. # Route the query to database shard 0000 of a read-only ApsaraDB RDS for MySQL instance.
  4. /*+TDDL:node(0) slave()*/SELECT * FROM t1;

In the example, /*+TDDL:scan()*/ and /*+TDDL:node(0) slave()*/ are DRDS hints that begin with +TDDL:. scan(), node(0), and slave() are DRDS hint commands. Multiple hint commands are separated with spaces.

Use only one hint command in an SQL statement:

DRDS allows you to use hints in data manipulation language (DML), data definition language (DDL), and data access language (DAL) statements. The following examples show the syntax:

  • For all statements that support hints, you can specify a hint at the beginning of the statements, for example:
  1. /*+TDDL: ... */ SELECT ...
  2. /*+TDDL: ... */ INSERT ...
  3. /*+TDDL: ... */ REPLACE ...
  4. /*+TDDL: ... */ UPDATE ...
  5. /*+TDDL: ... */ DELETE ...
  6. /*+TDDL: ... */ CREATE TABLE ...
  7. /*+TDDL: ... */ ALTER TABLE ...
  8. /*+TDDL: ... */ DROP TABLE ...
  9. /*+TDDL: ... */ SHOW ...
  10. ...
  • For DML statements, you can specify a hint behind the first keyword of the statements, for example:
  1. SELECT /*+TDDL: ... */ ...
  2. INSERT /*+TDDL: ... */ ...
  3. REPLACE /*+TDDL: ... */ ...
  4. UPDATE /*+TDDL: ... */ ...
  5. DELETE /*+TDDL: ... */ ...
  6. ...

Different hints are applicable to different statements. For information about the applicable statements, see the documentation of hint commands.

Use multiple hint commands:

DRDS allows you to use multiple hint commands in a hint statement.

  1. SELECT /*+TDDL:node(0) slave()*/ ...;

DRDS has the following limits on the use of multiple hint commands:

  1. # A single SQL statement cannot contain multiple hint statements.
  2. SELECT /*+TDDL:node(0)*/ /*+TDDL:slave()*/ ...;
  3. # A hint cannot contain duplicate hint commands.
  4. SELECT /*+TDDL:node(0) node(1)*/ ...;

Classification of DRDS hints

DRDS hints are classified into the following major categories based on operation types:

Compatibility of DRDS hints

DRDS 5.3 and later are compatible with most custom hints provided in DRDS 5.2. The following table lists the details.

DRDS 5.2 HINT Supported Corresponding hint in DRDS 5.3
Read/write splitting Supported. Read/write splitting
Specify a timeout period for an SQL statement Supported. Specify a timeout period for an SQL statement
Specify a database shard to execute an SQL statement Partially supported. The function of specifying a database shard by adding a database shard key to execute an SQL statement is moved to Scan all database shards and table shards. Specify a database shard to execute an SQL statement
Scan all database shards and table shards Supported. Functions of calculating physical table names based on conditions and explicitly specifying physical table names are added in DRDS 5.3. Scan all database shards and table shards