All Products
Search
Document Center

Instructions

Last Updated: Jul 31, 2020

Background

When you use a Distributed Relational Database Service (DRDS) database, execution plans generated by SQL optimizers are not as expected or are not optimal. For example, certain JOIN or AGGREGATE functions that can be pushed down to the underlying ApsaraDB RDS for MySQL instance are not pushed down. The outline feature provides a method to specify an execution plan for SQL statements. You can use hints to manually create an SQL execution plan, and use the outline to specify the execution plan you created for SQL statements.

Instructions

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 examples describe these statements in detail.

Create an outline

The CREATE statement is used to create an outline. After the outline is created, it takes effect by default.

  1. CREATE OUTLINE name ON origin_stmt TO target_stmt
  2. name indicates the name of the outline.
  3. origin_stmt is used to match the SQL statement. When the SQL statement does not contain the variable "?", exact match is performed.
  4. Otherwise, the SQL statement cannot contain constants and the parameter-based matching is performed after the SQL statement is formatted.
  5. target_stmt is the statement that uses hints to generate a logical plan.

Example 1: Create an outline in the exact match mode.

  1. mysql> create outline t1 on select 1 to select 2;
  2. Query OK, 1 row affected (1.09 sec)
  3. mysql> select 1;
  4. +------+
  5. | ? |
  6. +------+
  7. | 2 |
  8. +------+

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

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

  1. mysql> create outline t2 on select ? to select /*+TDDL:slave()*/ * from ms10 where c1=?;
  2. Query OK, 1 row affected (0.16 sec)
  3. mysql> explain select 1;
  4. +----------------------------------------------------------------------------+
  5. | LOGICAL PLAN |
  6. +----------------------------------------------------------------------------+
  7. | LogicalView(tables="01.ms10", sql="SELECT * FROM `ms10` WHERE (`c1` = ?)") |
  8. | HitCache:false |
  9. | UsingOutline: T2 |
  10. +----------------------------------------------------------------------------+

Delete an outline

The DROP statement is used to delete a specified outline.

  1. DROP OUTLINE name
  2. name indicates the name of the outline you want to delete.

Synchronize an outline again

A DRDS instance is composed of multiple DRDS Server nodes. When you create an outline, a SYNC error may occur when the outline is synchronized to other DRDS Server nodes. In this case, you must synchronize the outline again.

  1. RESYNC OUTLINE name
  2. name indicates the name of the outline you want to synchronize again.

Disable a specified outline

The DISABLE statement is used to disable a specified outline.

  1. DISABLE OUTLINE name
  2. name indicates the name of the outline you want to disable.

Enable a specified outline

The ENABLE statement is used to enable a specified outline.

  1. ENABLE OUTLINE name
  2. name indicates 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.

  1. SHOW OUTLINES

Limits

  1. Multiple statements are not supported.
  2. The GROUP BY and ORDER BY clauses do not support binding the “?” variable.
  3. In the parameter-based match mode, origin_stmt cannot contain constants.
  4. In the parameter-based match mode, the numbers of variables bound in origin_stmt and target_stmt must be the same.
  5. In the exact match mode, target_stmt cannot contain bound variables.
  6. The origin_stmt used for creating an outline cannot be the same as that of an existing outline in the system.
  7. When you create an outline, the syntax of target_stmt must be correct so that the execution plan can be generated.