---
name: tidb-query-tuning-autox
description: Deep SQL plan and query-workload diagnosis for confirmed Investibook sql_plan_or_query cases, adapted from AutoX.
---

# Investibook Adapter Boundary

This is a vendored AutoX SQL tuning skill adapted for Investibook diagnosis.
Load it only after the top-level diagnosis has identified `sql_plan_or_query`
as a primary or material contributing candidate and the SQL specialist causal
checks have passed.

When used by `diagnosis-analyst`:

- Consume existing Investibook evidence under `evidences/`, especially
  `evidence_summary_for_agent.json` or `.txt`, `queries.json`, decoded plans,
  TopSQL, SlowQuery, schema/statistics excerpts, and raw artifacts explicitly
  cited by the handoff.
- Do not run AutoX fleet discovery, AutoX evidence-collection scripts, Clinic
  collection commands, or production SQL. `diagnosis-analyst` is read-only and
  cannot use data-access skills.
- Treat upstream evidence-collection, batch, and local-validation workflows as
  future runner/data-access instructions. In the current Investibook adapter,
  they are references for evidence shape and validation vocabulary, not commands
  to execute.
- Preserve the production safety boundary: generated Binding, Index, TiFlash,
  or hint SQL is review-only and must not be executed.
- Output SQL-internal diagnosis as evidence-backed subtype candidates such as
  `scan_amplification`, `cardinality_error`, `wrong_access_path`,
  `wrong_join_plan`, `plan_instability`, `cop_seek_amplification`,
  `order_path_tradeoff`, or `non_optimizer_bottleneck`.

Use AutoX to adjudicate the SQL mechanism and optimizer-action direction; do
not use it to override the top-level causal classification or turn an
affected-only query into a root cause.

# AutoX Slow SQL Optimization

AutoX diagnoses TiDB Cloud slow SQL and returns one customer-facing recommendation:

- `Binding first`
- `Index first`
- `TiFlash / MPP first`
- `Investigate non-optimizer bottleneck`
- `No optimizer action`

Do not expose internal workflow states such as plan explore, local validation, evidence gaps,
or candidate comparison as the final recommended action.

## Safety Boundary

AutoX is read-only for the target cluster.

Never:

- create or drop production bindings;
- create or drop production indexes;
- execute production DDL;
- change production statistics;
- change production variables;
- apply TiFlash replica changes in production;
- execute generated SQL from `EXPLAIN EXPLORE`.

Generated SQL is review-only. AutoX never executes production SQL that creates or drops
bindings, indexes, statistics, TiFlash replicas, variables, or other cluster state.

Local-only DDL is allowed only inside an isolated local TiDB validation environment.

## Required First Reads

Before doing task work, read:

1. `SUBSKILLS_INDEX.md`
2. `references/case-contract.md`

Use `SUBSKILLS_INDEX.md` to choose the minimum required workflow subskills.

## Dependencies

Use the `$clinic-api` skill for read-only cluster metadata, Slow Query, TopSQL, schema,
statistics, metrics, and diagnostic files.

Do not copy Clinic authentication, API implementation, or endpoint details into AutoX.
Use the scripts and client supplied by `clinic-api` or the bundled AutoX collection scripts.

When TiDB behavior, SQL syntax, optimizer hints, statistics handling, TiFlash/MPP behavior, or
local validation commands require documentation, consult `pingcap/docs` or the TiDB official
website. Prefer documentation matching the target cluster version; record the exact branch, page, or
URL used and distinguish documentation evidence from source-code evidence.

## Cloud Evidence Gate

The Clinic Data Proxy schema describes observability tables such as `slow_query_logs` and
`topsql`; it is not user-table schema and does not prove which indexes exist.

After Slow Query identifies the involved user tables:

- use direct read-only TiDB status HTTP when the user supplied a reachable endpoint;
- otherwise, for a Dedicated cluster, run
  `scripts/collect_dashboard_debug_api_table.py` once for every involved user table to collect
  schema, existing indexes, and statistics through the Clinic Dashboard debug API;
- record `collected`, exact `failed` error, or `not_applicable` for schema and stats per table.

Do not continue to `problem_profile_built` while an involved user table has no collection record.
Running only `collect_slow_sql.py` does not satisfy this gate.

## Operating Model

Route a generic cluster-diagnosis request with no cluster ID, digest, SQL text, time range, or
top-N override to fleet batch mode. Discover every accessible active Dedicated cluster through
Clinic metadata, rank `(cluster_id, digest)` candidates globally by total slow-query latency over
the rolling last 24 hours, and run the complete focused workflow for the top 10. Treat 10 as a cap
when fewer candidates exist. Explicit cluster scope, top-N, time range, or ranking instructions
override these defaults.

Every fleet child must still resolve one exact `cluster_id`; fleet mode does not weaken focused
case identity, evidence, isolation, validation, or completion gates.

AutoX v0 diagnoses read-only `SELECT` statements only. Exclude write DML, transaction control,
DDL, administrative statements, and `SELECT ... FOR UPDATE` from automatic ranking. If the user
explicitly targets an ineligible statement, report that it is unsupported and do not generate an
optimizer recommendation.

For every diagnosis:

1. Generate a globally unique `diagnosis_id`.
2. Create a per-diagnosis workspace.
3. Resolve input, cluster, SQL digest, and time range.
4. Collect evidence from Cloud-side sources.
5. Build a problem profile.
6. Classify the root cause.
7. Generate ready-for-validation candidates when the diagnosis supports an optimizer action.
8. Prepare or reuse an isolated version-matched local TiDB, then validate candidates locally when
   schema and stats are available.
9. Produce the final report.
10. Clean up raw temporary artifacts, or report the retained path if cleanup fails.

Prefer this workspace layout:

```text
${AUTOX_WORKDIR:-${TMPDIR:-/tmp}/autox/<diagnosis_id>}
```

Never reuse local TiDB data directories, ports, generated schema, stats files, or report
workspaces across different diagnosis IDs.

## Workflow Routing

Follow this order for a focused digest or SQL:

1. `workflow/input-resolution/SUBSKILL.md`
2. `workflow/evidence-collection/SUBSKILL.md`
3. `workflow/diagnosis-classification/SUBSKILL.md`
4. `workflow/local-validation/SUBSKILL.md`
5. `workflow/final-report/SUBSKILL.md`

For top-N or batch diagnosis, read `workflow/batch-diagnosis/SUBSKILL.md` first. The batch
subskill must still run the full focused workflow for each selected digest.

Do not present a batch as finished while any selected digest is queued, running, retryable, or
invalid. Treat quota, transport, authentication, and agent interruption as pause-and-resume events,
not batch completion. Run `scripts/validate_batch.py` before claiming completion. Reduce an explicit
top-N only after the user authorizes the new scope and retain that scope change in the batch
manifest.

## Diagnosis Loading Rule

After `problem_profile_built`, read `workflow/diagnosis-classification/SUBSKILL.md`.
Root-cause classes such as `scan_amplification`, `wrong_access_path`, and
`non_optimizer_bottleneck` are labels inside that workflow, not separate subskills.

## Candidate Routing

`workflow/diagnosis-classification/SUBSKILL.md` owns candidate design. It must generate concrete
ready-for-validation candidates from full evidence before local validation starts.

General routing:

- Known better historical plan, plan regression, join/order/access path stabilizable by hints
  -> generate Binding or hinted-SQL candidate.
- Missing or ineffective access path with clear scan reduction mechanism
  -> check existing indexes and generate Index candidate DDL plus hypothetical-index SQL.
- Large read volume, weak selectivity, mixed-engine issue, or MPP-suitable shape
  -> generate TiFlash / MPP candidate with target aliases, hints, and hypothetical TiFlash SQL.
- Evidence points outside optimizer behavior
  -> generate no optimizer candidate and recommend `Investigate non-optimizer bottleneck`.
- Cloud-side evidence does not justify an optimizer action
  -> generate no optimizer candidate and recommend `No optimizer action`.

Do not set `Index first` unless schema was collected, existing indexes were checked, and a
concrete Index candidate is ready for validation. When local validation is unavailable, an Index
candidate may still become an inferred `Index first` recommendation only after it passes the
evidence-backed Index advisory gate in `references/case-contract.md`.

`autox-explore-sql` is an internal fallback after local reproduction is checked. It is not a
customer-facing recommendation.

`autox-compare-plans` is used by validation or final decision work when multiple candidate plans
need normalized comparison.

## Validation Gate

If the matching TiDB version, schema, and statistics are available, local validation is mandatory
before presenting an optimizer recommendation as plan-verified.

Local validation must use the full production SQL shape. Do not simplify joins, predicates,
subqueries, UNION arms, or parameter structure and then claim the result validates the original SQL.

If validation cannot run, record the exact blocker. Do not ask the customer for more evidence
when Cloud-side evidence was available to AutoX. A local TiDB preparation failure keeps the
validation level at `inferred`; it does not by itself demote an Index candidate. Use `No optimizer
action` or `Investigate non-optimizer bottleneck` only when the collected evidence and applicable
advisory gate do not justify an optimizer action.

For a plan-changing `plan_verified` recommendation, require a material semantic delta that
addresses the diagnosed mechanism. Do not compare only operator names or tree shape: identical
trees may still improve through access conditions, ranges, residual filters, lookup behavior,
pruning, pushdown, ordering, join semantics, or task/store placement. Operator-ID, plan-digest,
`estRows`, or estimated-cost changes alone are insufficient.

## Final Report

The final report is owned by `workflow/final-report/SUBSKILL.md`.
Write it to the canonical artifact path `report/report.md`; do not choose another filename.

Plan-changing reports must include `## Conclusion`, `## Plans Before & After`, and `## Analysis`.
Non-plan-changing reports must omit the plans section. Do not add extra top-level headings.

Write every customer-facing focused report and batch main report in English only. Every focused
report must include its exact SQL digest in `Observed evidence`; keep the batch main report at the
contracted six columns and link to the focused report for digest details.

Keep `Conclusion` compact. Plan-changing actions include only `Action` and concrete review-only
SQL. Non-plan-changing actions include only `Action`. Put complete plans only in `Plans Before &
After`, and put `Why`, evidence, inference, validation, and risks in `Analysis`.

`Plan before` must use production runtime evidence when available, preferably the slow-log
`decoded_plan`, rendered as a TiDB EXPLAIN-style operator table/tree. Never embed raw decoded-plan
JSON in a customer report.

`Plan after` must use complete local `EXPLAIN FORMAT='verbose'` output when local validation
was run.

Do not claim runtime improvement from local static `EXPLAIN` alone.

Use `plan_verified` only when a target-version local standalone TiDB reproduces the intended plan
from the full SQL with schema and stats loaded. Use `observed` when production runtime evidence
directly supports the diagnosis but no candidate plan was reproduced. `observed` does not mean an
optimization was executed or verified in production.