All Products
Search
Document Center

Introduction

Last Updated: Sep 07, 2020

Overview of DRDS hints

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.

For example, if you know the target data is stored in table shards of certain database shards and you need to route the SQL statement directly to the database shards for execution, you can use custom hints provided by DRDS.

  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.

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

Precautions

  • 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.

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 one hint 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 describes the syntax in detail:

  • For all SQL statements that support hints, you can specify a hint at the beginning of the statements, as shown in the following 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, as shown in the following example:
  1. SELECT /*+TDDL: ... */ ...
  2. INSERT /*+TDDL: ... */ ...
  3. REPLACE /*+TDDL: ... */ ...
  4. UPDATE /*+TDDL: ... */ ...
  5. DELETE /*+TDDL: ... */ ...
  6. ...

Use multiple hint commands in an SQL statement:

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

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

DRDS has the following limitations on the use of multiple hint commands in an SQL statement:

  1. # A single SQL statement cannot contain multiple hints.
  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 according to operation types:

Compatibility of DRDS hints

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

Custom hint provided in DRDS 5.2 Support in DRDS 5.3 and later versions Corresponding custom hint provided in DRDS 5.3
Read/write splitting Supported. Read-write splitting
Customize a time-out period for an SQL statement Supported. Customize a time-out period for an SQL statement
Specify a database shard to execute an SQL statement Partially supported. The function of specifying a database shard based on the value of 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