Mastering TiDB Performance Tuning: A Comprehensive Guide

핵심 요약

• Start with EXPLAIN. Full table scans, stale statistics, and the wrong join strategy account for most slow queries.
• Index the columns you filter, join, and sort on, but watch the write cost on write-heavy tables.
• Refresh statistics with ANALYZE TABLE before judging any plan the optimizer chooses.
• Route analytical queries to TiFlash so they stop competing with transactional load.

A slow query rarely stays slow for one reason: a missing index, a bad join strategy, and stale statistics often compound into the same 10-second response time. This guide walks through the query-level fixes, assuming your 티DB cluster’s hardware and network are already sized correctly. If they are not, start with the infrastructure-level tuning guide first.

How Do You Tune Query Performance in TiDB?

Tune query performance by indexing frequently filtered or joined columns, reading execution plans with EXPLAIN to catch full table scans, keeping table statistics current, and routing analytical queries to TiFlash so they do not compete with transactional load. All of this assumes the underlying cluster is already sized correctly.

Key Metrics to Watch While Tuning

A few metrics tell you whether a query-level change actually helped: query latency (time from request to response), throughput in queries or transactions per second, and CPU and memory use on the TiDB Server layer specifically, since that is where query parsing and optimization happen. Track these across a full load cycle before and after each change. Load varies enough hour to hour that a single before-and-after snapshot can mislead you.

Query Optimization: Indexes, Partitioning, Execution Plans

Indexes

Indexes carry most query-level gains. Apply them to columns that appear frequently in WHERE, JOIN, and ORDER BY clauses:

CREATE INDEX idx_title ON books (title);

Primary keys and unique indexes enforce constraints efficiently while speeding up lookups, but every extra index adds work to each write. On write-heavy tables, add indexes one at a time and measure write latency after each.

Partitioning

Partitioning splits a large table into smaller pieces so a query scans only the partitions it needs. TiDB supports range, hash, and list partitioning. For a date column, use RANGE COLUMNS so boundaries compare as dates:

ALTER TABLE orders
PARTITION BY RANGE COLUMNS (order_date) (
    PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
    PARTITION p2021 VALUES LESS THAN ('2022-01-01'),
    PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);

Three details decide whether this works. The dates carry quotes, because an unquoted 2021-01-01 evaluates as arithmetic rather than a date. The MAXVALUE partition catches every later row, which would otherwise fail to insert. And every unique key on the table, including the primary key, must include order_date, or the statement fails. Converting an existing table rewrites all of its data, so schedule it outside peak hours.

Execution Plans

Execution plans show where a query spends its time. EXPLAIN returns the plan the optimizer intends to use:

EXPLAIN SELECT title, price FROM books WHERE title = 'TiDB in Action';

EXPLAIN ANALYZE goes further: it runs the query and reports actual row counts and execution time for each operator next to the estimates. A large gap between estimated and actual rows usually means stale statistics, which you refresh with ANALYZE TABLE:

EXPLAIN ANALYZE SELECT title, price FROM books WHERE title = 'TiDB in Action';
 
ANALYZE TABLE books;

Look first for full table scans where an index should apply, then for estimate gaps. Most slow-query fixes start from one of the two.

Configuration Tuning: Optimizer Hints and System Variables

Optimizer Hints

Hints steer the planner toward a specific strategy when its own choice is wrong for your data:

SELECT /*+ HASH_JOIN(t1, t2) */ t1.name, t2.salary FROM employees t1 JOIN salaries t2 ON t1.id = t2.id;

Treat a hint as a patch, not a fix. If the optimizer picks the wrong join repeatedly, refresh statistics first: a hint pins the plan even after your data changes enough that a different plan would win.

System Variables

Two session variables control most query-level concurrency. tidb_distsql_scan_concurrency sets how many scan tasks run in parallel across TiKV. tidb_executor_concurrency sets concurrency for executors such as hash joins, index lookup joins, and aggregations; current versions route the older per-operator variables, including tidb_index_lookup_join_concurrency, through it.

SET SESSION tidb_distsql_scan_concurrency = 20;
SET SESSION tidb_executor_concurrency = 8;

Example values only. Raise either variable gradually from its default and measure: higher concurrency trades memory for latency.

How TiFlash Solves the OLTP/OLAP Contention Problem

Running analytical queries directly against transactional tables slows the transactional path, because both compete for the same storage engine. TiFlash removes that contention by replicating TiKV data into a separate columnar store in real time, so analytical queries run against their own copy and never touch the row-based transactional path. Its MPP (Massively Parallel Processing) engine spreads large aggregations across nodes, keeping analytical latency low as data volume grows without adding load to OLTP throughput.

Enabling TiFlash for a table takes one statement:

ALTER TABLE orders SET TIFLASH REPLICA 1;

Once the replica finishes building, the optimizer routes eligible analytical queries to it based on cost estimates, with no change to the SQL an application sends. Transactional queries keep routing to TiKV exactly as before, so enabling TiFlash is additive rather than a migration. See Use TiFlash for replica status checks and engine selection.

Real-World Performance Results

MNC Bank moved its core transaction processing to TiDB after high transaction volumes degraded performance on its previous system, and measured 10x higher throughput, 50% lower latency, and 85% faster backups afterward.

Flipkart needed fast analytical processing without disrupting transactional operations. Consolidating onto TiDB cut the operational complexity of running separate transactional and analytical systems while keeping both current.

Query Tuning Is the Second Half of the Job

Indexing, execution plan analysis, current statistics, and TiFlash for analytics close the gap that infrastructure tuning alone cannot. None of them helps much on an undersized cluster, and all of them compound on a well-sized one.

Start a free TiDB Cloud Starter cluster to run EXPLAIN ANALYZE against your own queries and see where the time actually goes.

TiDB Query Performance Tuning FAQs

What’s the First Thing to Check When a TiDB Query Is Slow?

• Run EXPLAIN ANALYZE to see the plan and actual row counts.
• Look for full table scans where an index should apply.
• Check for large gaps between estimated and actual rows, a sign of stale statistics.
• Refresh statistics with ANALYZE TABLE.
• Check whether a nested subquery would run faster as a JOIN.

How Does TiFlash Speed Up Analytical Queries?

• TiFlash replicates TiKV data into a columnar store in real time.
• Analytical queries run against that copy, not the transactional path.
• Its MPP engine parallelizes large aggregations across nodes.
• OLTP throughput stays unaffected by concurrent analytics.

Can I Use MySQL Query Optimization Techniques With TiDB?

• Mostly, yes: TiDB’s SQL layer is compatible with the MySQL wire protocol.
• Standard indexing and query-rewriting techniques apply directly.
• TiDB adds its own optimizer hints and system variables on top.
• Some MySQL-specific extensions have no direct equivalent.

What Is the Difference Between EXPLAIN and EXPLAIN ANALYZE in TiDB?

• EXPLAIN shows the intended plan with estimated row counts, without running the query.
• EXPLAIN ANALYZE runs the query and adds actual row counts, execution time, and memory use per operator.
• Use EXPLAIN on queries too expensive to run casually.
• Use EXPLAIN ANALYZE to see where the estimates went wrong.