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.
SELECT /*+TDDL:node('node_name')*/ * FROM table_name;
In the preceding SQL statement, the part between
+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.
DRDS hints can be in the formats of
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
-cparameter 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.
/*+TDDL: hint_command [hint_command ...]*/
/!+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
hint_command parameter indicates a DRDS hint command related to the specific operation. Multiple
hint_command parameters are separated with spaces.
# Query the names of physical tables in each database shard
# Route the query to database shard 0000 of a read-only ApsaraDB RDS for MySQL instance
/*+TDDL:node(0) slave()*/SELECT * FROM t1;
In the example,
/*+TDDL:node(0) slave()*/ are DRDS hints that begin with
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:
/*+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 behind the first keyword of the statements, as shown in the following example:
SELECT /*+TDDL: ... */ ...
INSERT /*+TDDL: ... */ ...
REPLACE /*+TDDL: ... */ ...
UPDATE /*+TDDL: ... */ ...
DELETE /*+TDDL: ... */ ...
Use multiple hint commands in an SQL statement:
DRDS allows you to use multiple hint commands in a hint.
SELECT /*+TDDL:node(0) slave()*/ ...;
DRDS has the following limitations on the use of multiple hint commands in an SQL statement:
# 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)*/ ...;
DRDS hints are classified into the following major categories according to operation types:
- Read/write splitting
- Customize a time-out period for an SQL statement
- Specify a database shard to execute an SQL statement
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|