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

Information provided in this topic is applicable to PolarDB-X 1.0 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.

PolarDB-X 1.0 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 PolarDB-X 1.0 custom hints.

Syntax of PolarDB-X 1.0 custom hints

Syntax

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

Note

  • PolarDB-X 1.0 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 PolarDB-X 1.0 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, PolarDB-X 1.0 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 custom hints on the client. If you do not add the -c parameter, PolarDB-X 1.0 the client deletes comments in SQL statements before it sends the SQL statements to servers for execution. custom hints in this format are defined as MySQL comments. Therefore, PolarDB-X 1.0 the 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 by PolarDB-X 1.0. The two hints begin with +TDDL:. scan(), node(0), and slave() are PolarDB-X 1.0 hint commands and are separated with spaces.

Use a hint in an SQL statement

PolarDB-X 1.0 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

PolarDB-X 1.0 allows you to use a hint that contains multiple hint commands in an SQL statement. 

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

PolarDB-X 1.0 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 PolarDB-X 1.0 custom hints