Terminal
Terminal/Skill
Diagnoses and fixes slow TiDB queries using optimizer hints, statistics refresh, join strategies, and index tuning.
By PingCAP (TiDB)
Works with
npx skills add pingcap/agent-rules --skill tidb-query-tuningUse this skill to diagnose and resolve TiDB query performance issues. It follows a rigorous workflow from symptom identification to verified solution.
Capture the current plan & clues:
EXPLAIN ANALYZE <query> to get actual execution stats.estRows vs actRows — large divergence means stale or missing statistics.PLAN REPLAYER DUMP EXPLAIN [ANALYZE] <query>; to export comprehensive on-site information (version, config, stats, plan) to a ZIP file./debug/pprof, /stats/dump/{db}/{table}, or /schema/{db}/{table}.SHOW GLOBAL BINDINGS; to see if existing plan baselines are affecting the query.Check statistics health:
SHOW STATS_HEALTHY WHERE Db_name = '<db>' AND Table_name = '<table>';actRows diverges significantly from estRows, run ANALYZE TABLE <table>; (or with ALL COLUMNS if @@tidb_analyze_column_options is not ALL) and re-check the plan.ANALYZE TABLE <table> INDEX <index_name>;Identify the bottleneck pattern:
references/join-strategies.mdIndexJoin/IndexHashJoin picked the wrong probe index → see references/join-strategies.md, references/index-selection.md, and references/slow-plan-optimization/cases/use-correct-probe-index-for-index-join.mdreferences/subquery-optimization.mdreferences/index-selection.mdreferences/optimizer-hints.md and references/session-variables.mdreferences/stats-health-and-auto-analyze.mdreferences/stats-loading-and-startup.mdreferences/stats-version-and-analyze-configuration.mdreferences/optimizer-oncall-experiences-redacted/references/tidb-customer-planner-issues/Reproduce & Investigate locally:
tiup playground and PLAN REPLAYER LOAD to reproduce the issue locally. See references/plan-replayer-testing.md.nightly.pingcap/tidb and analyze the source code. Read AGENTS.md and .agents/skills in the TiDB repo first.Apply the fix:
CREATE GLOBAL BINDING FOR <stmt> USING <hinted_stmt>;.IndexJoin or IndexHashJoin, inspect the probe-side access object, pushed conditions, and whether the path is covering. Do not assume the chosen probe index is correct just because the join type is index-based. If another existing index better matches join keys, selective filters, or covering needs, compare it with USE_INDEX/IGNORE_INDEX and stabilize the winner with a hint or SQL binding.DELETE on mysql.bind_info directly. Use DROP GLOBAL BINDING ... for each target binding instead.ADMIN RELOAD BINDINGS may still leave stale entries in the in-memory binding cache. In this case, restart the TiDB server to fully clear and rebuild binding cache state.references/optimizer-oncall-experiences-redacted/direct-delete-bind-info-leaves-stale-binding-cache.md for a full reproduction and recovery checklist.references/bug-report.md. Anonymize all sensitive data before reporting.Verify the improvement:
EXPLAIN ANALYZE with the fix applied.actRows and execution time improved.When the customer reports high TiDB CPU usage, use the following workflow before jumping to optimizer conclusions:
clinic-api as the default entry point. If clinic-api is unavailable, require the user to provide the equivalent raw evidence first, including CPU profiles, query history, plans, TopSQL, statement summary, and slow query samples.Use this workflow to decide whether the next step should be query tuning, plan inspection, stats diagnosis, internal SQL investigation, or a product bug report.
AUTO ANALYZE cannot keep up with stats decay, plan quality will drift even when SQL does not change.EXPLAIN ANALYZE is the ground truth. EXPLAIN alone shows estimates; ANALYZE shows what actually happened.references/optimizer-oncall-experiences-redacted/ is useful for symptom matching, investigation signals, and fix-version lookup.references/tidb-customer-planner-issues/ is useful when you need linked PRs, merge times, and still-open customer gaps.NO_DECORRELATE() often wins. See references/subquery-optimization.md.references/join-strategies.md.IndexJoin and IndexHashJoin can still be slow because the optimizer picked the wrong inner probe index. Always inspect the probe-side access object, pushed predicates, and whether another existing index better matches join keys and covering needs.mysql.bind_info directly; use DROP GLOBAL BINDING instead.mysql.bind_info was modified directly and reload does not clear bindings, restart TiDB. ADMIN RELOAD BINDINGS might not fully remove stale in-memory binding cache entries after direct table deletes.READ_FROM_STORAGE(TIFLASH[<table>]). See references/session-variables.md.references/slow-plan-optimization/guide.md — Slow-plan optimization sub-module with workflow, guardrails, and case catalog.references/clues.md — Detailed SQLs and metrics for clue collection.references/reproduction.md — Baseline investigation and known version issues.references/plan-replayer-testing.md — Local reproduction using TiUP and PLAN REPLAYER.references/bug-report.md — Standard bug report template and anonymization workflow.references/optimizer-hints.md — Optimizer hints: syntax, catalog, and when to use each.references/session-variables.md — Session/global variables that affect plan choice.references/join-strategies.md — Join algorithms, when TiDB picks each, and how to override.references/subquery-optimization.md — Decorrelation, semi-join, EXISTS/IN patterns and NO_DECORRELATE.references/index-selection.md — Index hints, invisible indexes, index advisor, composite index guidance.references/slow-plan-optimization/cases/use-correct-probe-index-for-index-join.md — Validate and fix wrong probe-index selection for IndexJoin and IndexHashJoin.references/explain-patterns.md — Reading EXPLAIN ANALYZE output to identify bottlenecks.references/stats-health-and-auto-analyze.md — Statistics health, auto analyze backlog diagnosis, and safe concurrency tuning.references/stats-loading-and-startup.md — Init stats, sync load, restart-time plan instability, and version-based mitigation.references/stats-version-and-analyze-configuration.md — Stats versioning, analyze coverage, and memory-safe stats collection settings.references/optimizer-oncall-experiences-redacted/ — Redacted optimizer oncall case corpus with user symptoms, investigation signals, workarounds, and fixed versions.references/tidb-customer-planner-issues/README.md — Generated GitHub issue corpus with one file per customer-driven planner issue, including linked PRs and merge timestamps.scripts/collect_diag_info.sql — SQL script to collect baseline tuning metadata.