This topic describes the outline feature and how to use this feature.

Background

When you use a PolarDB-X 1.0 database, execution plans generated by SQL optimizers may not meet your business requirements. For example, some JOIN or AGGREGATE functions that can be processed by the underlying ApsaraDB RDS for MySQL instances are not pushed down. The outline feature provides a method to specify an execution plan for the SQL statement. You can use hints to create an SQL execution plan, and use the outline feature to ensure that your SQL statement is executed based on the SQL execution plan.

The outline feature allows you to create and manage outlines in the system by executing the CREATE, DROP, RESYNC, DISABLE, ENABLE, and SHOW statements. The following sections describe these statements.

Limits

  • An SQL query that includes multiple statements is not supported.
  • The question mark (?) cannot be usedas a bind variable in the GROUP BY and ORDER BY clauses.
  • In parameter-based match mode, origin_stmt cannot contain constants.
  • In parameter-based match mode, the number of bind variables in origin_stmt and the number of bind variables in target_stmt must be the same.
  • In exact match mode, target_stmt cannot contain bind variables.
  • When you create an outline, origin_stmt cannot be the same as that of an existing outline in the system.
  • When you create an outline, the syntax of target_stmt must be correct so that the desired execution plan can be generated.

Create an outline

The CREATE statement is used to create an outline. By default, an outline takes effect after it is created.
CREATE outline name ON origin_stmt TO target_stmt

Parameter description:

  • name indicates the name of the outline that you want to create.
  • origin_stmt specifies the SQL statement for which you want to create an outline. If the SQL statement does not contain the question mark (?) variable,an exact match is performed.
  • If the SQL statement contains the question mark (?)variable, the SQL statement cannot contain constants and a parameter-based match is performed after the SQL statement is formatted.
  • target_stmt is the statement that uses hints to generate a logical plan.

Example 1: Create an outline in exact match mode

mysql> create outline t1 on select 1 to select 2;
Query OK, 1 row affected (1.09 sec)

mysql> select 1;
+------+
| ?    |
+------+
|    2 |
+------+
            

When the SQL statement is being executed, the select 1 clause is replaced by the select 2 clause.

Example 2: Create an outline in parameter-based match mode

mysql> create outline t2 on select ? to select /*+TDDL:slave()*/ * from ms10 where c1=?;
Query OK, 1 row affected (0.16 sec)

mysql> explain select 1;
+----------------------------------------------------------------------------+
| LOGICAL PLAN                                                               |
+----------------------------------------------------------------------------+
| LogicalView(tables="01.ms10", sql="SELECT * FROM `ms10` WHERE (`c1` = ?)") |
| HitCache:false                                                             |
| UsingOutline: T2                                                           |
+----------------------------------------------------------------------------+
            

Delete an outline

The DROP statement is used to delete a specified outline.

DROP OUTLINE name  #name specifies the name of the outline that you want to delete.                   

Synchronize an outline again

A PolarDB-X 1.0 instance runs on multiple servers. After you create an outline on a server, the outline is synchronized to other servers. An error may occur when the outline is being synchronized. In this case, you must synchronize the outline again.

RESYNC OUTLINE name  #name specifies the name of the outline that you want to synchronize again.         

Disable a specified outline

The DISABLE statement is used to disable a specified outline.

DISABLE OUTLINE name #name specifies the name of the outline that you want to disable.

Enable a specified outline

The ENABLE statement is used to enable a specified outline.

ENABLE OUTLINE name  #name specifies the name of the outline you want to enable.

Query the outlines in the system

The SHOW statement is used to query the outlines in the system.

SHOW OUTLINES