In high-concurrency point query workloads, SQL compilation and optimization become a performance bottleneck. PlanCache addresses this by caching the execution plan the first time a query runs, so subsequent queries with the same SQL pattern reuse the cached plan instead of recompiling. This eliminates repeated compilation overhead and reduces query latency at scale.
PlanCache is designed for high-concurrency point queries only. It is not recommended for other query types.
How it works
Point queries (for example, SELECT * FROM tbl WHERE id = ?) have stable execution plans: the optimal access path does not change based on the specific parameter value. PlanCache exploits this property by caching the plan on first execution and reusing it for all subsequent queries that match the same SQL pattern. For queries with complex predicates, joins, or aggregations, the optimal plan typically varies with the data distribution, so PlanCache does not apply.
Prerequisites
Before you begin, ensure that:
The cluster minor version is 3.1.10.0 or later
To check or update the minor version, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page. For details, see Update the minor version of a cluster.
Enable or disable PlanCache
PlanCache is disabled by default. Run the following commands to enable or disable it.
Enable PlanCache:
SET ADB_CONFIG O_SPC_SCOPE=POINT_QUERY;Disable PlanCache:
SET ADB_CONFIG O_SPC_SCOPE=NONE;Example
The following example shows how PlanCache reduces compilation overhead for a high-concurrency point query.
Assume the query pattern is:
SELECT * FROM tbl0 WHERE col0 = ?;Enable PlanCache.
SET ADB_CONFIG O_SPC_SCOPE=POINT_QUERY;Run the query. AnalyticDB for MySQL compiles the execution plan and caches it.
SELECT * FROM tbl0 WHERE col0 = 666;Run a query with the same SQL pattern. The cached execution plan is reused automatically, skipping compilation and optimization.
SELECT * FROM tbl0 WHERE col0 = 777;