Statement Outline

Prerequisites

The kernel version of GaussDB(for MySQL) is 2.0.42.230600 or later.

Precautions

1. Statement Outline is disabled by default. To enable it, see Enabling Statement Outline.
2. If Statement Outline is disabled, the performance is not affected. If there are a large number of rules after Statement Outline is enabled, the performance deteriorates.

Description

Statement Outline supports the optimizer hints and index hints of MySQL 8.0.

Enabling Statement Outline

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB(for MySQL).
4. On the Instances page, click the instance name to go to the Basic Information page.
5. In the navigation pane, choose Parameters.
6. Search for rds_opt_outline_enabled in the search box and change its value to ON.

   Table 1 Parameter description

   Parameter	Description
   rds_opt_outline_enabled	Controls whether to enable Statement Outline. ON: Statement Outline is enabled. OFF: Statement Outline is disabled.

7. Click Save.

outline Table

GaussDB(for MySQL) has a built-in system table (outline) to store hints. This table is automatically created when the system is started. The SQL statements for creating the table are as follows.

CREATE TABLE `mysql`.`outline` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Digest` varchar(64) COLLATE utf8_bin NOT NULL,
  `Digest_text` longtext COLLATE utf8_bin,
  `Type` enum('IGNORE INDEX','USE INDEX','FORCE INDEX','OPTIMIZER') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Scope` enum('','FOR JOIN','FOR ORDER BY','FOR GROUP BY') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '',
  `State` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'Y',
  `Position` bigint(20) NOT NULL,
  `Hint` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB
 DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Statement outline'

Statement Outline Management

There are six local storage rules to manage Statement Outline.

• add_optimizer_outline

  Adding optimizer hints

  • Syntax

    dbms_outln.add_optimizer_outline(<Schema_name>,<Digest>,<Query_block>,<Hint>,<Query>);

    

    You can set either Digest or Query (original SQL statement). If you set Query, DBMS_OUTLN calculates Digest and Digest_text. You are advised to set Query directly.

  • Description

    Parameter	Mandatory	Type	Description
    Schema_name	Yes	VARCHAR	Name of the database to which the statement belongs. This parameter can be set to NULL or left blank, the statement cannot be matched.
    Digest	No	VARCHAR	Hash value of the statement. You can set this parameter or Query. If you do not want to set it to a specific value, set it to an empty string.
    Query_block	Yes	INT	Position of the object to which the hint applies. Value range: Greater than or equal to 1
    Hint	Yes	VARCHAR	Hint name.
    Query	No	VARCHAR	SQL statement. You can set either this parameter or Digest. If you do not want to set it to a specific value, set it to an empty string. If both of them are set, check whether Digest and Query match. If they do not match, the parameter verification fails and the execution fails.

  • Example

    

• add_index_outline

  Adding index hints

  • Syntax

    dbms_outln.add_index_outline(<Schema_name>,<Digest>,<Position>,<Type>,<Hint>,<Scope>,<Query>);

    

    You can set either Digest or Query (original SQL statement). If you set Query, DBMS_OUTLN calculates Digest and Digest_text. You are advised to set Query directly.

  • Description

    Parameter	Mandatory	Type	Description
    Schema_name	Yes	VARCHAR	Name of the database to which the statement belongs. This parameter can be set to NULL or left blank, the statement cannot be matched.
    Digest	No	VARCHAR	Hash value of the statement. Set either this parameter or Query. If you do not want to set it to a specific value, set it to an empty string.
    Position	Yes	INT	Position of the table to which the index hint applies in the statement. The value must be greater than or equal to 1.
    Type	Yes	ENUM	Hint type. Its value can be: OPTIMIZER USE INDEX FORCE INDEX IGNORE INDEX
    Hint	Yes	VARCHAR	Hint name or index name set. Use commas (,) to separate multiple index names.
    Scope	Yes	ENUM	Hint scope. Its value can be: FOR GROUP BY FOR ORDER BY FOR JOIN An empty string
    Query	No	VARCHAR	SQL statement. You can select either or Digest. If you do not want to set it to a specific value, set it to an empty string. If both of them are set, check whether Digest and Query match. If they do not match, the parameter verification fails and the execution fails.

  • Example

    call dbms_outln.add_index_outline('outline_db', '', 1, 'USE INDEX', 'ind_1', '',"select * from t1 where t1.col1 =1 and t1.col2 ='xpchild'");

• preview_outline

  Querying the status of the SQL statement matching the statement outline, which can be used for manual verification.

  • Syntax

    dbms_outln.preview_outline(<Schema_name>,<Query>);

  • Description

    Parameter	Mandatory	Data Type	Description
    Schema_name	Yes	VARCHAR	Database name.
    Query	Yes	VARCHAR	SQL statement.

  • Example

    

• show_outline

  Displaying the in-memory hit rate of statement outline

  • Syntax

    dbms_outln.show_outline();

  • Example

    

    HIT and OVERFLOW description

    1. HIT indicates the number of times that the statement outline finds the destination query block or table.
    2. OVERFLOW indicates the number of times that statement outline does not find the destination query block or table.
• del_outline

  Deleting a statement outline from the memory and table.

  • Syntax

    dbms_outln.del_outline(<id>);

  • Description

    Parameter	Mandatory	Type	Description
    id	Yes	INT	Statement outline ID, which is the value in the id column in the mysql.outline table. The value cannot be left blank.

  • Example

    

    Note: If the statement outline to be deleted does not exist, the system reports a warning. You can run the show warnings; command to view the warning content.

• flush_outline

  If you modify the statement outline in the outline table, you need to make the statement outline take effect again.

  • Syntax

    dbms_outln.flush_outline();

  • Example

    update mysql.outline set Position = 1 where Id = 18;
    call dbms_outln.flush_outline();

Function Verification

To check whether the statement outline takes effect, perform the following steps:

• Use the preview_outline interface.

  

• Run the EXPLAIN command.