TiDB query plans and statistics

For each query, the optimizer estimates how many rows every candidate step will touch and chooses the cheapest plan: which index, whether to read the index and then the table (IndexLookUp) or scan the table, which join algorithm and in what order, and how much to push down to TiKV or TiFlash.

Those row estimates come from statistics, not from the data itself. Correct statistics give good plans; wrong statistics give confidently wrong ones.

Statistics are collected by ANALYZE TABLE, and automatically when the share of modified rows passes tidb_auto_analyze_ratio (0.5 by default), within the configured auto-analyze time window.

• — Table level — total row count and how many rows have changed since the last ANALYZE.
• — Column and index level — histograms of the value distribution, the most frequent values (TopN), the number of distinct values, and null counts.

EXPLAIN shows the plan the optimizer intends: each operator, its estimated rows (estRows) and where it runs — root on the TiDB server, cop[tikv] or cop[tiflash] on storage. EXPLAIN ANALYZE runs the query and adds what actually happened: actRows, time and memory per operator.

Compare estRows with actRows from the bottom of the plan up. Where they diverge by orders of magnitude, the optimizer was working from a wrong picture, and every decision above that point is suspect.

-- What the optimizer expected vs what happened
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

-- Which tables have drifted since their last ANALYZE
SHOW STATS_HEALTHY WHERE Healthy < 80;

-- Refresh statistics after a bulk load or large delete
ANALYZE TABLE orders;

• — A bulk load, delete or backfill changed the data, and auto-analyze has not run yet — or ran in the middle of it.
• — Skewed data: a few values account for most rows, and the query hits one of them.
• — Different parameter values make the same query shape touch very different row counts.
• — An upgrade changed the optimizer's cost model or defaults.

• — Run ANALYZE as part of any bulk data change, not afterwards by chance.
• — Bind the known-good plan for critical queries with SQL plan management (CREATE GLOBAL BINDING … USING …), or fix it with optimizer hints in the query.
• — Lock statistics on tables whose statistics are known to be good and whose data should not move them.
• — Find the queries worth attention from the slow query log and the statements summary tables (INFORMATION_SCHEMA.STATEMENTS_SUMMARY), not from guesswork — and track their latency over time.