This topic describes hints for read/write splitting.

Information provided in this topic is applicable to PolarDB-X 1.0 V5.3 and later.

PolarDB-X 1.0 provides read/write splitting that is transparent to the application layer. A latency of several milliseconds exists when data is synchronized between primary and read-only ApsaraDB RDS instances. If you need to read data changes immediately after the data in the primary ApsaraDB RDS instance is changed, you must make sure that the SQL request for reading data is routed to the primary ApsaraDB RDS instance. To meet this demand, PolarDB-X 1.0 provides custom hints for read/write splitting. These custom hints allow you to route SQL statements to a specified primary or read-only ApsaraDB RDS instance.

Syntax

/*+TDDL:
    master()
    | slave()
*/

You can use the custom hints to specify whether to execute an SQL statement on a primary or read-only ApsaraDB RDS instance. If you use /*+TDDL:slave()*/ in an SQL statement and a primary ApsaraDB RDS instance is associated with multiple read-only ApsaraDB RDS instances, PolarDB-X 1.0 randomly selects a read-only ApsaraDB RDS instance based on the assigned weight to execute the SQL statement.

Note

  • PolarDB-X 1.0 custom hints can be specified in the /*+TDDL:hint_command*/ format or in the /!+TDDL:hint_command*/ format.
  • 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 PolarDB-X 1.0 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. PolarDB-X 1.0 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

  • Execute an SQL statement on your primary ApsaraDB RDS instance:
    SELECT /*+TDDL:master()*/ * FROM table_name;

    After the custom hint /*+TDDL:master()*/ is added at the end of the first keyword in the SQL statement, this SQL statement is routed to the primary ApsaraDB RDS instance.

  • Execute an SQL statement on a specified read-only ApsaraDB RDS instance:

    SELECT /*+TDDL:slave()*/ * FROM table_name;

    After the custom hint /*+TDDL:slave()*/ is added at the end of the first keyword in the SQL statement, this SQL statement is randomly routed to a read-only ApsaraDB RDS instance based on the assigned weight.

    Note
    • The custom hints for read/write splitting are only applicable to read-only SQL statements that are not included in transactions. SQL statements that are executed to write data or are included in transactions are still routed to the primary ApsaraDB RDS instance.
    • If you use the /*+TDDL:slave()*/ hint in an SQL statement, PolarDB-X 1.0 randomly routes the SQL statement to a read-only ApsaraDB RDS instance based on the assigned weight. If no read-only ApsaraDB RDS instances are available, no error is reported. Instead, the primary ApsaraDB RDS instance is selected to execute the SQL statement.