tencent cloud

Feedback

Optimization of Plan Caching Point Query

Last updated: 2024-04-25 11:10:02

    Overview

    In TDSQL-C for 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.

    Supported Versions

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

    Use Cases

    This feature is mainly used to improve the query performance when executing many online short point queries with prepared statements. The specific extent of performance improvement depends on the online business.

    Use Limits

    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.
    Parameter
    Status
    Type
    Default Value
    Valid Values
    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.
    Upon enabling the cdb_plan_cache_stats parameter, one can then view related data through the show cdb_plan_cache_stat command.
    You can run the show cdb_plan_cache_stat 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
    Note:
    When the cdb_plan_cache_stats switch is enabled, it essentially functions as an information record, which will have an impact on performance.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support