This topic describes the usage and the syntax of custom hints.

Information provided in this topic is applicable to Distributed Relational Database Service (DRDS) V5.3 and later.

Overview

Hints are supplementary to the SQL syntax and play a crucial role in relational databases. Hints allow you to choose execution methods for SQL statements by using the corresponding syntax. This way, you can optimize the execution of SQL statements.

DRDS provides special hint syntax. For example, if you already know data is stored in some table shards in specific database shards and you need to route an SQL statement to the database shards for execution,you can use DRDS custom hints.

Syntax of DRDS custom hints

Syntax

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

Note

  • DRDS custom hints can be specified in the /*+TDDL:hint_command*/ format or in the /!+TDDL:hint_command*/ format.
  • A hint is a string that is placed between /* and */ or between /!and */. The string begins with +TDDL:. The hint_command parameter specifiesone or more DRDS custom hint commands that are used to affect specific operations. If you specify multiple hint commands for the hint_command parameter, separate them with spaces.
  • If you specify custom hints in the /*+TDDL:hint_command*/ format,add the -c parameter to the command used to log on to the MySQL command-line client: mysql. This way, you can execute SQL statements that contain the DRDS custom hints on the client. If you do not add the -c parameter,the client deletes comments in SQL statements before it sends the SQL statements to servers for execution. DRDS custom hints in this format are defined as MySQL comments. Therefore,the DRDS custom hints cannot take effect. For more information, see mysql client options.

Examples

# Query the names of physical tables in each database shard.
/*+TDDL:scan()*/SHOW TABLES;

# Route the query to database shard 0000 of a read-only ApsaraDB RDS instance.
/*+TDDL:node(0) slave()*/SELECT * FROM t1;  

In the examples, /*+TDDL:scan()*/ and /*+TDDL:node(0) slave()*/ are custom hints provided byDRDS. The two hints begin with +TDDL:. scan(), node(0), and slave() areDRDS hint commands and are separated with spaces.

Use a hint in an SQL statement

DRDS allows you to use hints in DML, DDL, and Data Access Language (DAL) statements. The following list describes the syntax:

  • For a DML statement, you can specify a hint at the end of the first keyword of the statement, as shown in the following examples:
    /*+TDDL: ... */ SELECT ...
    /*+TDDL: ... */ INSERT ...
    /*+TDDL: ... */ REPLACE ...
    /*+TDDL: ... */ UPDATE ...
    /*+TDDL: ... */ DELETE ...
    /*+TDDL: ... */ CREATE TABLE ...
    /*+TDDL: ... */ ALTER TABLE ...
    /*+TDDL: ... */ DROP TABLE ...
    /*+TDDL: ... */ SHOW ...
    ...
                
  • For DML statements, you can specify a hint at the end of the first keyword of the statements, as shown in the following example:
    SELECT /*+TDDL: ... */  ...
    INSERT /*+TDDL: ... */  ...
    REPLACE /*+TDDL: ... */  ...
    UPDATE /*+TDDL: ... */  ...
    DELETE /*+TDDL: ... */  ...
    ...
                
    Note Different hints may be applicable to different statements. For more information about the applicable statements, see the topics that describe hint commands.

Use multiple hints in an SQL statement

DRDS allows you to use a hint that contains multiple hint commands in an SQL statement.

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

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

# A single SQL statement cannot contain multiple hints.
SELECT /*+TDDL:node(0)*/ /*+TDDL:slave()*/ ...;

# A hint cannot contain duplicate hint commands.
SELECT /*+TDDL:node(0) node(1)*/ ...;      

Categories of DRDS custom hints