Skip to content
Why TiDB Is Replacing Manual Sharding: Architecture, HTAP & Migration Guide
Databases 16 min read

Why TiDB Is Replacing Manual Sharding: Architecture, HTAP & Migration Guide

This article explains why TiDB is gaining adoption over manual sharding by detailing its native distributed architecture, automatic Region-based sharding, Raft-based strong consistency, HTAP capabilities with TiFlash, MySQL migration steps including AUTO_RANDOM for hotspot avoidance, AI-assisted slow query analysis, and a balanced pros/cons assessment with scenario-based recommendations.

Su San Talks Tech
Su San Talks Tech
Su San Talks Tech

Introduction: The Sharding Pain Point

When business data grows to hundreds of millions of rows, single-table queries slow down. The traditional response is sharding — e.g., using ShardingSphere to split an order table into 64 shards. But sharding introduces new problems: maintaining shard rules, manually stitching cross-shard queries, migrating data during scale-out, and worrying about primary-replica lag during large DDL operations.

TiDB offers an alternative: a native distributed database that moves "distributed" concerns from the application layer back into the database layer. You face a single logical database; TiDB handles partitioning, replication, and scheduling internally.

What Is TiDB?

Unlike MySQL sharding — which adds a routing layer atop independent MySQL instances — TiDB is built as a distributed system from the ground up. Its compute and storage layers are separated; the storage layer (TiKV) is multi-replica and strongly consistent by default. You write one SQL statement; TiDB decides how to split, execute, and merge the work without any shard-key configuration from you.

Architecture Overview

图片
图片

TiDB comprises three layers:

Compute Layer (TiDB Server) : Stateless SQL engine that parses SQL, generates execution plans, pushes requests to storage, and merges results. Adding nodes scales compute without downtime.

Storage Layer (TiKV + TiFlash) : TiKV is a row-store engine for OLTP; TiFlash is a column-store engine for OLAP. They stay in sync via Raft Learner replication, maintaining strong consistency.

Scheduling Layer (PD) : Manages cluster metadata, load balancing, and timestamp allocation. It controls Region scheduling and replica placement.

Core Principles

3.1 Automatic Sharding: Regions

MySQL sharding requires you to define shard rules. TiDB does it automatically: data is split by key range into Regions (~96 MB each). When a Region grows or becomes hot, PD splits it and rebalances across TiKV nodes. You never pre-judge table size or shard count; scaling is simply adding TiKV nodes while PD migrates Regions automatically.

图片
图片

3.2 Raft Consensus: Strong Consistency

Each Region has multiple replicas (typically 3). Replicas synchronize via the Raft protocol : the leader writes, then replicates to followers; a write succeeds only after a majority (2 of 3) acknowledge. If the leader fails, Raft elects a new one automatically — no data loss, failover in seconds. This eliminates the need for external tools like MHA or custom failover scripts.

3.3 HTAP: One System, Two Workloads

After sharding, real-time analytics usually means syncing data to Hive or ClickHouse (T+1 latency, consistency challenges). TiDB runs row-store TiKV and column-store TiFlash side by side; TiFlash replicates from TiKV in real time via Raft Learner. The optimizer routes OLTP queries to TiKV and OLAP queries to TiFlash automatically.

Case study — 擎文集团 : Previously on sharded MySQL with offline Hive analysis (T+1). After migrating to TiDB, a single database serves both online transactions and real-time analytics, removing the sync pipeline entirely.

Migrating from MySQL: What Changes?

4.1 Connection String

// Original MySQL
String url = "jdbc:mysql://mysql-host:3306/mydb";

// TiDB
String url = "jdbc:mysql://tidb-host:4000/mydb";

Port changes from 3306 to 4000. TiDB speaks the MySQL protocol — JDBC driver, connection pool, and most SQL syntax work unchanged.

4.2 Schema Design: AUTO_INCREMENT Pitfall & AUTO_RANDOM

TiDB supports AUTO_INCREMENT, but if a table lacks an explicit primary key, TiDB uses _tidb_rowid as the row identifier, which shares an allocator with the auto-increment column and can cause ID gaps. The recommended pattern is to declare an explicit primary key with AUTO_RANDOM (a TiDB-specific feature) to avoid write hotspots on monotonically increasing keys:

-- Recommended: explicit PK with AUTO_RANDOM
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_RANDOM,
  user_id BIGINT,
  amount DECIMAL(10,2),
  created_at DATETIME
);

For high-write tables, prefer AUTO_RANDOM over AUTO_INCREMENT.

4.3 Cross-Shard Joins Become Simple Joins

In a sharded setup, cross-shard joins require application-level stitching or broadcast tables. In TiDB you write standard SQL:

SELECT o.id, u.name, o.amount
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2026-01-01';

TiDB handles partitioning, execution, and merging transparently.

Using AI to Analyze Slow Queries

TiDB's slow-query logs are structured; you can feed the log and EXPLAIN output to an AI to diagnose "why is this SQL slow, what index should I add?"

Step 1 — Provide EXPLAIN output: "This query scans 2M rows but returns 10. Help me analyze why it didn't use an index and what index to add." The AI spots a filter on WHERE status = 'PENDING' AND created_at > '2026-01-01' with only a single-column index on created_at, and suggests a composite index (status, created_at).

Step 2 — Validate with AI: "What should I watch for when adding this composite index in TiDB? Will it hurt write performance?" The AI notes TiDB's online DDL (no table lock), but index creation consumes resources — run during low traffic. It also suggests a partial index (e.g., only index rows where status = 'PENDING' ) if the column has low cardinality, reducing index size.

Limitation: AI cannot decide the business trade-off — if write volume is huge, the index cost may outweigh read gains. AI explains the plan; you make the call.

Pros and Cons

Pros

No manual sharding — Regions auto-split, auto-schedule, auto-rebalance. Scale-out = add nodes.

MySQL-compatible, low migration cost — Change connection string; most SQL works.

HTAP — TiKV for OLTP, TiFlash for OLAP; no separate sync pipeline to Hive/ClickHouse.

Financial-grade HA — Raft multi-replica, majority commit, auto-failover, sub-second RTO, zero data loss.

Online DDL — Even billion-row DDL runs without blocking reads/writes.

Proven at scale — Kimi.ai uses TiDB Cloud for AI Agent infra (1-second isolated DB creation); Flipkart runs TiDB on Kubernetes at 1M QPS.

Cons

Not 100% MySQL-compatible — No stored procedures, triggers, events, UDFs, full-text indexes, or spatial types. Heavy users of these need refactoring.

Different ops model — Requires understanding PD, Regions, Raft. TiUP and TiDB Operator help, but learning curve exists.

Write hotspots on auto-increment PKs — Must use AUTO_RANDOM or application-generated IDs to distribute writes.

Higher resource footprint — Minimum production deployment needs multiple nodes; overkill for small data/low concurrency.

When to Use TiDB (Scenario Matrix)

Large data, sharding ops pain — ✅✅✅ Strongly recommended. Auto-sharding removes rule maintenance.

Need HTAP (transactions + real-time analytics) — ✅✅✅ Strongly recommended. Single system, dual engines, no sync pipeline.

Financial-grade HA required — ✅✅✅ Strongly recommended. Raft multi-replica, auto-failover.

AI Agent database infrastructure — ✅✅✅ Strongly recommended. Kimi.ai validated: 1-second isolated DB provisioning.

Depend on stored procedures/triggers/full-text — ⚠️ Evaluate. TiDB lacks these features.

Small data, low concurrency — ❌ Not recommended. Resource overhead not justified; MySQL suffices.

Conclusion

TiDB shifts the "data outgrows single node" problem from "how does the application shard?" to "how does the database distribute itself?" Sharding solves the capacity ceiling but introduces shard-rule management, cross-shard queries, distributed transactions, and scale-out migrations. TiDB internalizes all of that.

HTAP is the other differentiator: sharding forces a separate analytics pipeline (sync lag, dual maintenance); TiFlash lets transactions and analytics share the same endpoint with real-time consistency.

Trade-offs exist: incomplete MySQL feature parity, new operational concepts, hotspot avoidance discipline, and higher baseline resource use. But for teams facing "MySQL can't keep up, and we don't want to maintain sharding," TiDB is a battle-tested, continuously evolving, MySQL-compatible choice.

Reference: https://github.com/pingcap/tidb

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

distributed databaseTiDBHTAPRaft consensusslow query optimizationMySQL migrationAI-assisted debuggingauto-sharding
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.