tencent cloud

TDSQL-C for MySQL

Release Notes and Announcements
Release Notes
Product Announcements
Beginner's Guide
Product Introduction
Overview
Strengths
Use Cases
Architecture
Product Specifications
Instance Types
Product Feature List
Database Versions
Regions and AZs
Common Concepts
Use Limits
Suggestions on Usage Specifications
Kernel Features
Kernel Overview
Kernel Version Release Notes
Optimized Kernel Version
Functionality Features
Performance Features
Security Features
Stability Feature
Analysis Engine Features
Inspection and Repair of Kernel Issues
Purchase Guide
Billing Overview
Product Pricing
Creating Cluster
Specification Adjustment Description
Renewal
Payment Overdue
Refund
Change from Pay-as-You-Go to Yearly/Monthly Subscription
Change from Pay-as-You-Go to Serverless Billing
Value-Added Services Billing Overview
Viewing Billing Statements
Getting Started
Database Audit
Overview
Viewing Audit Instance List
Enabling Audit Service
Viewing Audit Logs
Log Shipping
Post-Event Alarm Configuration
Modifying Audit Rule
Modifying Audit Service
Disabling Audit Service
Audit Rule Template
Viewing Audit Task
Authorizing Sub-User to Use Database Audit
Serverless Service
Serverless Introduction
Creating and Managing a Serverless Cluster
Elastic Scaling Management Tool
Serverless Resource Pack
Multi-AZ Deployment
Configuration Change
FAQs
Serverless Cost Estimator
Operation Guide
Operation Overview
Switching Cluster Page View in Console
Database Connection
Instance Management
Configuration Adjustment
Instance Mode Management
Cluster Management
Scaling Instance
Database Proxy
Account Management
Database Management
Database Management Tool
Parameter Configuration
Multi-AZ Deployment
GD
Backup and Restoration
Operation Log
Data Migration
Parallel Query
Columnar Storage Index (CSI)
Analysis Engine
Database Security and Encryption
Monitoring and Alarms
Basic SQL Operations
Connecting to TDSQL-C for MySQL Through SCF
Tag
Practical Tutorial
Classified Protection Practice for Database Audit of TDSQL-C for MySQL
Upgrading Database Version from MySQL 5.7 to 8.0 Through DTS
Usage Instructions for TDSQL-C MySQL
New Version of Console
Implementing Multiple RO Groups with Multiple Database Proxy Connection Addresses
Strengths of Database Proxy
Selecting Billing Mode for Storage Space
Creating Remote Disaster Recovery by DTS
Creating VPC for Cluster
Data Rollback
Solution to High CPU Utilization
How to Authorize Sub-Users to View Monitoring Data
White Paper
Security White Paper
Performance White Paper
Troubleshooting
Connection Issues
Performance Issues
API Documentation
History
Introduction
API Category
Making API Requests
Instance APIs
Multi-Availability Zone APIs
Other APIs
Audit APIs
Database Proxy APIs
Backup and Recovery APIs
Parameter Management APIs
Billing APIs
serverless APIs
Resource Package APIs
Account APIs
Performance Analysis APIs
Data Types
Error Codes
FAQs
Basic Concepts
Purchase and Billing
Compatibility and Format
Connection and Network
Features
Console Operations
Database and Table
Performance and Log
Database Audit
Between TDSQL-C for MySQL and TencentDB for MySQL
Service Agreement
Service Level Agreement
Terms of Service
TDSQL-C Policy
Privacy Policy
Data Privacy and Security Agreement
General References
Standards and Certifications
Glossary
Contact Us
DocumentationTDSQL-C for MySQLKernel FeaturesStability FeatureStatement Outline

Statement Outline

PDF
Focus Mode
Font Size
Last updated: 2025-11-28 10:54:18

Overview

SQL tuning is a crucial step in improving database performance. To avoid the impact when the optimizer fails to select an appropriate execution plan, TXSQL provides the outline feature for you to bind execution plans. TDSQL-C for MySQL allows you to use hints to manually bind execution plans. The hint information contains the optimization rule for SQL statements, algorithm to be used, and index for data scan. An outline relies on hints to specify execution plans. ‌TDSQL-C for MySQL provides the mysql.outline system table for you to add plan binding rules and the cdb_opt_outline_enabled switch for you to enable/disable the outline feature.

Supported Versions

‌TDSQL-C for MySQL 8.0 (kernel version 3.1.10) or later.

Use Cases

This feature is suitable for scenarios where an execution plan in the production environment has poor performance (for example, the index in the execution plan is incorrect), but you don't want to modify SQL statements and release a new version to fix this problem.

Use Limits

The outline syntax uses a new syntax form:
Configure outline information: outline "sql" set outline_info "outline";
Clear outline information: outline reset ""; outline reset all;
Refresh outline information: outline flush;
Below are the outline use methods with the following schemas as examples:
create table t1(a int, b int, c int, primary key(a));
create table t2(a int, b int, c int, unique key idx2(a));
create table t3(a int, b int, c int, unique key idx3(a));
Parameter
Effective Immediately
Type
Default Value
Valid Values
Description
cdb_opt_outline_enabled
yes
bool
false
true/false
Whether to enable the outline feature.
Note:
Currently, you cannot directly modify the values of the above parameter. If needed, submit a ticket for assistance.

Binding an outline

To bind an outline directly, you can replace one SQL statement with another, without changing the syntax of the SQL. This simply adds some HINT information to instruct the optimizer on how to execute the statement. The syntax is in the format of outline "sql" set outline_info "outline";. Note that the string after outline_info must start with "OUTLINE:", which is followed by the SQL statement with the hint information added. For example, you can add the index in column a to table t2 in the SQL statement select * from t1, t2 where t1.a = t2.a as follows:
outline "select * from t1, t2 where t1.a = t2.a" set outline_info "OUTLINE:select * from t1, t2 use index(idx2) where t1.a = t2.a";

Binding optimizer hint

To make the feature more flexible, TXSQL allows you to add optimizer hints incrementally to SQL statements. You can also implement the same feature by directly binding an outline. The syntax is in the format of outline "sql" set outline_info "outline";. Note that the string after outline_info must start with "OPT:", which is followed by the optimizer hint information to be added. For example, you can specify SEMIJOIN of MATERIALIZATION/DUPSWEEDOUT for the SQL statement select *from t1 where t1.a in (select b from t2) as follows:
outline "select * from t1 where t1.a in (select b from t2)" set outline_info "OPT:2#qb_name(qb2)";
outline "select * from t1 where t1.a in (select b from t2)" set outline_info "OPT:1#SEMIJOIN(@qb2 MATERIALIZATION, DUPSWEEDOUT)";
You can add only one optimizer hint to the original SQL statement at a time and must comply with the following rules:
The OPT keyword must follow ".
':' must be placed before the new statement to be bound.
You must add two fields (query block number#optimizer hint string), which must be separated with "#" (e.g., "OPT:1#max_execution_time(1000)").

Binding index hint

To make the feature more flexible, TXSQL allows you to add index hints incrementally to SQL statements. You can also implement the same feature by directly binding an outline. The syntax is in the format of outline "sql" set outline_info "outline";. Note that the string after outline_info must start with "INDEX:", which is followed by the index hint information to be added. For example, you can add the index idx1 of USE INDEX in FOR JOIN type to the table t1 in the database test in query block 3 for the SQL statement select * from t1 where t1.a in (select t1.a from t1 where t1.b in (select t1.a from t1 left join t2 on t1.a = t2.a)) as follows:
outline "select * from t1 where t1.a in (select t1.a from t1 where t1.b in (select t1.a from t1 left join t2 on t1.a = t2.a))" set outline_info "INDEX:3#test#t1#idx1#1#0";
You can add only one index hint to the original SQL statement at a time and must comply with the following rules:
The INDEX keyword must follow ".
':' must be placed before the new statement to be bound.
You must add five fields (query block number#db_name#table_name#index_name#index_type#clause).
Here, index_type has three valid values (0: INDEX_HINT_IGNORE; 1: INDEX_HINT_USE; 2: INDEX_HINT_FORCE), and clause also has three valid values (1: FOR JOIN; 2: FOR ORDER BY; 3: FOR GROUP BY), which must be separated by "#" (e.g., "INDEX:2#test#t2#idx2#1#1", indicating to bind the index idx2 in USE INDEX FOR JOIN type to the table test.t2 in the second query block).

Deleting the outline information of a SQL statement

TXSQL allows you to delete the outline binding information from a SQL statement. The syntax is in the format of outline reset "sql";. For example, to delete the outline information from select * from t1, t2 where t1.a = t2.a, run the following statement: outline reset "select* from t1, t2 where t1.a = t2.a";.

Clearing all outline information

TXSQL allows you to clear all outline binding information in the kernel. The syntax is outline reset all, and the execution statement is outline reset all;.
There may be some specific problems in the production environment where you must bind an index. In this case, you can directly configure an outline for binding. You should analyze the possible performance compromise after configuring an outline and bind an outline only if the compromised performance is acceptable. You can consult kernel engineers if necessary.
Previous Topic: Database AuditNext Topic: Hotspot Update Protection

Help and Support

Was this page helpful?

Help us improve! Rate your documentation experience in 5 mins.

Feedback