System Design - How TiDB Auto Analyze Works

This explanation is for engineers new to TiDB who want to follow one background statistics job from its scheduler to the storage layer. It focuses on the priority-queue path in TiDB’s current source; the source also retains a legacy candidate-selection path behind a setting.

Table of contents

Why a database analyzes tables

Before a SQL optimizer chooses a plan, it estimates how many rows each operation will read. If the optimizer thinks a filter returns ten rows but it actually returns ten million, it may choose a poor join order or an expensive access method.

TiDB stores statistics about tables and indexes so the optimizer can make those estimates. Inserts, updates, and deletes make existing statistics less representative. ANALYZE TABLE refreshes them. Auto analyze is TiDB’s background process for deciding when a refresh is useful and running it without waiting for a user to issue the statement manually.

The process crosses several components. In the diagram, the feature-flagged priority-queue branch is expanded; the alternate branch is noted for context.

           TiDB cluster
  +----------------------------------------------+
  | Stats-owner TiDB instance                    |
  |                                              |
  | Domain.autoAnalyzeWorker (periodic tick)      |
  |        | enable check + owner check           |
  |        v                                     |
  | statsAnalyze.HandleAutoAnalyze()             |
  |        |                                     |
  |        +-- priority queue enabled             |
  |        |      v                              |
  |        |   Refresher -> priority queue        |
  |        |      -> worker -> analysis job      |
  |        |                                     |
  |        +-- disabled -> legacy candidate scan  |
  +------------------------+---------------------+
                           |
                     ANALYZE TABLE
                           v
  +----------------------------------------------+
  | TiDB AnalyzeExec / AnalyzeColumnsExec        |
  | build tasks, send DistSQL Analyze requests   |
  +------------------------+---------------------+
                           | coprocessor requests
                           v
  +----------------------------------------------+
  | TiKV Coprocessor: AnalyzeContext             |
  | scan ranges, sample rows, build partial stats|
  +------------------------+---------------------+
                           | partial results
                           +---------------------> TiDB merges and stores stats

From a timer tick to an analysis job

Only the stats owner schedules work

The Domain starts autoAnalyzeWorker, which wakes on a ticker based on the statistics lease. On each tick, it checks that auto analyze is enabled, shutdown has not started, and this TiDB instance owns statistics work. This avoids every TiDB server in a cluster independently launching the same background work. The code is in Domain.autoAnalyzeWorker.

The stats handle delegates to statsAnalyze.HandleAutoAnalyze. That method obtains a session context, then handleAutoAnalyze chooses the scheduling path. When tidb_enable_auto_analyze_priority_queue is enabled, it asks a Refresher to analyze the highest-priority work. When it is disabled, the code falls back to a legacy routine that randomly scans candidates and tries one table at a time. The switch is visible in statsAnalyze.handleAutoAnalyze.

The refresher applies the scheduling rules

The priority-queue path uses Refresher.AnalyzeHighestPriorityTables. It initializes the queue when needed and rebuilds it when relevant inputs such as the auto-analyze ratio or partition-pruning mode change. It also:

• checks whether the configured auto-analyze time window is open;
• refreshes the concurrency limit from the current setting;
• counts running jobs and only takes work for available slots;
• skips a table that already has an analysis job running; and
• validates each popped job before submitting it to the worker.

The worker records running table IDs and calls job.Analyze. A representative NonPartitionedTableAnalysisJob builds an ANALYZE TABLE statement for its table and delegates to exec.AutoAnalyze. Partitioned tables use their own job implementations, but they follow the same idea: the scheduler chooses the work, and the normal analyze execution path performs it. See the worker’s submission and execution methods and the job’s SQL construction.

TiDB executes the same analyze pipeline

exec.AutoAnalyze calls RunAnalyzeStmt, which executes the generated statement through TiDB’s restricted SQL executor. That reuse matters: auto analyze is not a second statistics engine. It is a scheduler that decides when to invoke the normal ANALYZE TABLE machinery. The wrapper and statement execution are in autoanalyze/exec.

The SQL plan creates an AnalyzeExec. It filters work that cannot run, starts worker goroutines, and sends column or index tasks to them. Those workers call the appropriate pushdown implementation. After the tasks finish, TiDB handles the returned results and updates its statistics handle. The orchestration is in AnalyzeExec.Next and analyzeWorker and the result workers.

For column statistics, AnalyzeColumnsExec.buildResp builds an analyze request and calls distsql.Analyze. The request carries the table ranges and analysis options to TiKV rather than pulling every row into the TiDB SQL layer first. See AnalyzeColumnsExec.buildResp.

TiKV scans and returns partial statistics

On TiKV, the coprocessor endpoint recognizes an ANALYZE request, decodes the request type, and creates an AnalyzeContext. That context dispatches column, index, mixed, and full-sampling work to the matching handler. The request routing is in endpoint.rs; the handler’s type-specific dispatch is in AnalyzeContext::handle_request.

For a column request, TiKV uses a sample builder to scan the requested ranges and collect column summaries. For an index request, the handler builds structures such as a histogram and count-min sketch while scanning index values. A TiKV response is a partial result for its assigned ranges; TiDB coordinates the tasks, combines results, and updates statistics used by the optimizer.

This split keeps the large data scan near the storage nodes. TiDB coordinates the work and owns the SQL-level lifecycle, while TiKV scans the key ranges and produces partial summaries.

Settings that shape auto analyze

These settings control different parts of the decision. The values below describe the TiDB source snapshot linked here; defaults can vary by release, so check the current system-variable reference before changing a cluster.

The variable names and source defaults are defined in vardef/tidb_vars.go and the analyze-related settings block, with defaults in the default constants and the feature defaults. The official statistics guide and ANALYZE TABLE reference explain the SQL-facing behavior.

Trade-offs and debugging clues

Auto analyze spends CPU and I/O to keep optimizer estimates useful. A lower modification threshold can refresh statistics sooner, but it may run more analyses. A narrow time window or low concurrency can reduce pressure during peak hours, but it can also leave stale statistics waiting in the queue. These controls are operational trade-offs, not correctness switches for query results.

When an execution plan becomes unexpectedly slow, check whether its table statistics are stale before assuming the SQL text is the only problem. When auto analyze is not running, first check whether it is enabled, whether the current TiDB instance is the stats owner, whether the time window is open, and whether concurrency is already occupied.

References

1. TiDB statistics.
2. TiDB ANALYZE TABLE statement.
3. TiDB system-variable reference.
4. TiDB auto-analyze scheduler and priority-queue path.
5. TiDB analysis worker and SQL execution.
6. TiDB pushdown and TiKV AnalyzeContext and TiKV request dispatch.