AnalyticDB for MySQLprovides PlanCache, which caches execution plans for SQL statements with the same pattern. This avoids repeated compilation and optimization, improving query performance. This topic explains how to enable PlanCache and shows usage examples.
Prerequisites
The minor version of the cluster must be 3.1.10.0 or later.
To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.
Overview
When query concurrency is high, SQL compilation and optimization can become a performance bottleneck. The PlanCache feature addresses this by caching execution plans for SQL statements with the same pattern. Subsequent matching queries reuse the cached plan—eliminating recompilation and improving query performance.
Note: PlanCache is designed only for high-concurrency point queries and is not recommended for non-point query scenarios.
Enable or disable the PlanCache feature
By default, the PlanCache feature is disabled. You can execute the following commands to enable or disable the PlanCache feature.
Enable PlanCache:
SET ADB_CONFIG O_SPC_SCOPE=POINT_QUERY;Disable PlanCache:
SET ADB_CONFIG O_SPC_SCOPE=NONE;
Examples
Assume that you have the following high-concurrency point query request:
SELECT * FROM tbl0 WHERE col0 = ?;Enable the PlanCache feature:
SET ADB_CONFIG O_SPC_SCOPE=POINT_QUERY;Execute an SQL query statement. The execution plan for this statement is then cached.
SELECT * FROM tbl0 WHERE col0 = 666;When you execute a statement with the same SQL pattern, the execution plan cached in the previous step is used automatically. This reduces SQL compilation and optimization time.
SELECT * FROM tbl0 WHERE col0 = 777;