Add session-level plan cache for prepared SELECT statements

Problem

MariaDB prepared statements still perform optimization on every EXECUTE. For
high-frequency OLTP workloads that repeatedly execute the same prepared SELECT
shape with different parameter values, this repeated optimization work can
become visible CPU overhead.

MariaDB already keeps prepared statement state and can reprepare statements
when metadata changes, but it does not cache and reuse optimizer access
decisions for ordinary prepared SELECT execution. A full JOIN-plan cache would
have a large optimizer/executor state surface, including Item ownership, QEP
state, range state, temporary-table state, and upper executor state.

Proposed solution

Add a default-off, session-level plan cache for eligible prepared SELECT
statements.

The cache is local to one prepared statement in one session. It is not shared
across sessions, statements, or transactions.

The first stage intentionally uses a conservative recipe-and-validation model
instead of caching and restoring a full JOIN tree. It stores a compact
description of the chosen access path and rebuilds the executable state on each
hit. For an eligible prepared SELECT:

• on the first eligible execution, capture a compact access recipe and
  validation signature
• on a later EXECUTE, validate the cached state against the current environment
• on a valid cache hit, rebuild the narrow executable access state needed by
  the recipe
• continue through the normal SELECT execution path
• fall back to normal optimization when caching is unsupported, unsafe, or
  invalidated

The implementation adds:

• session_plan_cache, disabled by default
• session_plan_cache_allow_change_ratio for optional row-count-change
  invalidation
• Cached_plan_count
• Cached_plan_hits
• Cached_plan_prevalidations
• Cached_plan_invalidations
• diagnostic status counters for review and benchmarking

Supported first-stage recipes include:

• field = ? or ? = field on a non-null single-column unique key
• field = ? or ? = field on a non-null single-column indexed key
• field BETWEEN ? AND ? on a non-null single-column indexed key
• limited single-table range recipe covering SUM(field)
• limited single-table range recipe covering ORDER BY
• limited single-table range recipe covering DISTINCT ... ORDER BY

Unsupported or fallback cases

Unsupported shapes continue to use the existing optimizer and executor path.
Examples include:

• non-prepared statements
• non-SELECT statements
• multi-table joins
• multiple query blocks
• derived tables and views
• UNION / EXCEPT / INTERSECT
• correlated and scalar subqueries
• general GROUP BY
• general aggregate support beyond the limited SUM(field) range recipe
• general ORDER BY and DISTINCT support beyond the limited single-table range
  recipes
• window functions
• EXPLAIN and ANALYZE
• locking clauses
• user variables and side-effect expressions
• nondeterministic expressions such as RAND()
• SQL_CALC_FOUND_ROWS
• nullable key parts
• composite keys
• temporary, system, and schema tables

Ordinary unsupported shapes fall back to normal optimization. Internal rebuild
or allocation failures also fall back unless execution state has already been
partially changed; in that case the code returns an error rather than
continuing through an unsafe partially rebuilt state.

Correctness

The cached state does not retain handler runtime objects or old optimizer
runtime structures. It stores a bounded recipe and validation signature, then
rebuilds the required access state on each cache hit.

Before a cached plan can be used, the implementation validates:

• session_plan_cache state
• prepared-statement execution context
• table version
• optimizer_switch
• client character set
• parameter signature and runtime null/no-value state
• access recipe compatibility
• access signature compatibility
• optional row-count change ratio when session_plan_cache_allow_change_ratio is
  positive

Reprepare, DEALLOCATE PREPARE, disconnect, and COM_CHANGE_USER release or
invalidate cached state.

Unsupported shapes are rejected from cache eligibility and continue through the
existing MariaDB optimizer/executor path, preserving current semantics.

Validation

The current MariaDB plan cache branch has focused MTR coverage for:

• session_plan_cache and session_plan_cache_allow_change_ratio system variables
• prepared-statement lifecycle cleanup
• status counters
• no-op behavior when the feature is disabled
• unique equality hits
• ref equality hits
• range BETWEEN hits
• limited SUM range shape
• limited ORDER BY range shape
• limited DISTINCT ORDER BY range shape
• parameter-shape invalidation
• optimizer_switch invalidation
• client character set invalidation
• table-version invalidation after DDL/key metadata changes
• row-count-ratio invalidation
• unsupported non-SELECT shapes
• unsupported views and information_schema tables
• binary protocol smoke coverage
• disconnect and COM_CHANGE_USER cleanup
• debug fault injection for allocation and rebuild failure paths

The branch also includes a design proposal, known limitations, invalidation
matrix, test report, and benchmark harnesses.

Performance evidence

Plan cache is intended as a performance feature for workloads that repeatedly
execute the same prepared SELECT shape with different parameter values. The
main expected benefit is reducing repeated optimizer work during EXECUTE.

The branch includes supporting documentation with release-build benchmark
methods, raw summaries, and analysis scripts. The benchmark harnesses compare
session_plan_cache=off and session_plan_cache=on with prepared statements,
record Cached_plan_* counters, check final Cached_plan_count cleanup, and
collect QPS/TPS, latency, CPU, invalidation, and hit-count data.

Current local engineering evidence includes:

• a 2026-06-26 primary check with three 60-second repeats where local
  measurements showed QPS increases of about 29.64%, 21.38%, 13.47%, and 14.21%
  at 1, 2, 4, and 8 threads, respectively, with 3/3 positive repeats and zero
  invalidations
• a longer in-memory release benchmark where oltp_read_only showed positive
  QPS/TPS deltas at 1, 2, 4, 8, and 16 threads across three repeats
• a focused DISTINCT range attribution benchmark where local measurements
  showed QPS increases of about 80.99% at 1 thread and 94.41% at 4 threads, with
  3/3 positive repeats, no rejected benchmark samples, and zero invalidations

These results are engineering evidence from a local macOS host and should be
treated as directional until they are repeated on a dedicated Linux host with
release binaries, separated server and sysbench CPU sets, longer warmup and run
windows, repeated off/on pairs, captured system environment, result consistency
checks, and ASAN/LSAN evidence.

Implementation branch / pull request

Current contribution branch:
https://github.com/ZhuQingping/mariadb_server/tree/support_plan_cache

Licensing

I submit this contribution under the New BSD License, also known as the
3-clause BSD License.