TencentDB for MySQL
- Release Notes and Announcements
- Announcements
- Product Introduction
- Database Architecture
- Database Instance
- Kernel Features
- Kernel Version Release Notes
- Functionality Features
- Performance Features
- Parallel Query
- Security Features
- Stability Features
- Purchase Guide
- Getting Started
- Connecting to MySQL Instance
- Database Audit
- Audit Rule Template
- MySQL Cluster Edition
- Maintenance Management Instance
- Operation Guide
- Instance Management and Maintenance
- Instance Upgrade
- CPU Elastic Expansion
- Read-Only/Disaster Recovery Instances
- Database Proxy
- Database Proxy Kernel Features
- Database Proxy Management
- Automatic Read/Write Separation Feature
- Connection Pool Feature
- Other Features
- Account Management
- Database Management Center (DMC)
- Parameter Configuration
- Network and Security
- Backup and Rollback
- Restoring Database from Backup File
- Monitoring and Alarms
- Operation Logs
- Practical Tutorial
- Calling MySQL APIs in Python
- White Paper
- Performance White Paper
- Performance Test Report
- Troubleshooting
- Connections
- API Documentation
- Data Import APIs
- Rollback APIs
- Parameter APIs
- Making API Requests
- Instance APIs
- Database Proxy APIs
- Database Audit APIs
- Security APIs
- Account APIs
- Backup APIs
- Database APIs
- Monitoring APIs
- Log-related API
- FAQs
- Backup
- Connection and Login
- Instance Upgrade
- Performance and Memory
- Service Agreement
- Reference
DocumentationTencentDB for MySQLKernel FeaturesPerformance FeaturesExecution Plan Cache for Optimizing UK/PK Queries
Execution Plan Cache for Optimizing UK/PK Queries
Last updated: 2024-07-22 12:35:46
Overview
In MySQL, SQL statement execution is divided into four stages: parsing, preparation, optimization, and execution. The execution plan cache feature is only available for prepared statements. After the feature is enabled, the first three stages will be skipped when executing a prepared statement, greatly boosting query performance.
In MySQL 8.0 20210830, the execution plan cache takes effect only for queries using unique keys (UKs) or primary keys (PKs). We will cover more types of queries in later versions.
Supported Versions
Kernel version: MySQL 8.0 20210830 and later.
Use Cases
This feature is mainly used to improve the query performance when executing short prepared statements with UKs or PKs on TencentDB instances. However, the extent to which performance may improved depends on your business.
Performance Impact
For UK and PK SQL statements, the delay is reduced by 20%-30% and the throughput is improved by 20%-30% after the execution plan cache is enabled (according to the sysbench test using the point_select.lua script).
Memory usage will increase after the execution plan cache is enabled.
Instructions
You can use the
cdb_plan_cache parameter to enable or disable the execution plan cache and the cdb_plan_cache_stats parameter to query information about cache hits. Note that only accounts with the tencentroot permission can use the two parameters.Parameter | Effective Immediately | Type | Default Value | Valid Values/Value Range | Description |
cdb_plan_cache | Yes | bool | false | true/false | Whether to enable the feature. Only accounts with the feature permission can use the parameter. |
Note:
Currently, you cannot directly modify the values of the above parameter. If needed, submit a ticket for assistance.
You can run the
show cdb_plan_cache command to query information about execution plan cache hits. The command will return the following fields:Field | Description |
sql | A SQL statement with the question mark (?) which represents that the execution plan of this statement has been cached. |
mode | SQL cache mode. Currently, only the prepare mode is supported. |
hit | Number of hits for this session. |
After
cdb_plan_cache_stats is enabled, cache hit information will be recorded, affecting database performance.SQL Execution Status
You can run
show profile to show the status at each stage of SQL statement execution. But when the execution plan cache is hit, the status of optimizing, statistics, and preparing will be omitted.Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No