Why Sharding Is No Longer Recommended: Introducing TiDB Distributed Database and Its Comparison with MySQL

When MySQL reaches a certain scale, performance degrades. Historically, developers introduced database middleware such as Mycat , ShardingSphere and tddl to handle sharding, but this approach is now considered outdated. Modern distributed databases can avoid sharding altogether.

Why Sharding Is Not Recommended

Sharding introduces several challenges:

Pagination performance drops sharply with large page numbers.

Distributed transaction complexities.

Data migration effort required to redistribute existing data across shards.

Scaling limits of each shard eventually require re‑sharding.

Development model changes: queries must include shard keys.

Cross‑shard and cross‑table query difficulties.

Business logic often needs to compromise due to sharding limitations.

To avoid these problems, the article recommends using the distributed database TiDB.

TiDB Introduction

TiDB is an open‑source distributed relational database developed by PingCAP, launched in September 2015. It supports both OLTP (online transaction processing) and OLAP (online analytical processing) workloads, offers horizontal scaling, financial‑grade high availability, real‑time HTAP (Hybrid Transactional and Analytical Processing), and is cloud‑native. TiDB is compatible with the MySQL 5.7 protocol and ecosystem, making it suitable for high‑availability, strong‑consistency, and large‑scale data scenarios.

Core Features

Financial‑grade high availability.

Online horizontal scaling with compute‑storage separation.

Cloud‑native deployment on public, private, or hybrid clouds.

Real‑time HTAP via TiKV row‑store and TiFlash column‑store engines.

MySQL protocol and ecosystem compatibility.

Strongly consistent distributed transactions.

Seamless migration from MySQL with minimal code changes.

PD provides CP consistency in the CAP theorem.

Application Scenarios

Financial systems requiring strong consistency, high reliability, and disaster recovery.

High‑concurrency OLTP workloads with massive data and scalability needs.

Data aggregation and secondary processing pipelines.

Case Studies

TiDB is used in production by more than 1,500 companies across various industries. Representative cases can be found on the TiDB official website.

System Architecture

TiDB Server

The stateless TiDB Server exposes a MySQL‑compatible endpoint, parses SQL, locates the required TiKV data via PD, and returns results. It can be load‑balanced using LVS, HAProxy, or F5, allowing unlimited horizontal scaling.

PD Server

PD (Placement Driver) manages the cluster metadata, performs scheduling and load balancing for TiKV, conducts leader election, and allocates globally unique, monotonically increasing transaction IDs. A PD cluster typically runs an odd number of nodes (minimum three) to maintain availability.

TiKV Server

TiKV provides the distributed transactional key‑value store for OLTP data. Data is stored in Regions, each covering a contiguous key range. Multiple Regions are assigned to each TiKV node.

How TiKV Prevents Data Loss

TiKV replicates data across multiple machines using the Raft consensus algorithm. Writes are committed on the leader and must be replicated to a majority of replicas (default three) before the client receives success.

Distributed Transaction Support

TiKV implements distributed transactions based on Google’s Percolator model (originally built on BigTable). This allows multi‑key writes without requiring the keys to reside in the same Region.

Comparison with MySQL

Supported Features

Distributed transactions based on Percolator/BigTable concepts.

Optimistic lock + MVCC (MySQL uses pessimistic lock + MVCC). TiDB checks write‑write conflicts only at commit time.

Unsupported Features

No stored procedures, functions, or triggers.

Auto‑increment IDs work only within a single TiDB Server.

No foreign‑key constraints.

No temporary tables.

No MySQL optimizer trace.

XA syntax is not exposed via SQL (TiDB uses two‑phase commit internally).

Resource Usage

TiDB achieves a compression ratio of about 3.4:1 compared with MySQL (10.8 TB → 3.2 TB). In a comparable workload, TiDB required 14 nodes versus MySQL’s 32, 224 CPU cores versus MySQL’s 512, and 16 TB storage versus MySQL’s 48 TB.

Performance Tests

Test Report 1

Five ECS instances with varying CPU counts (2, 4, 16, 64, 96) were used. The MySQL database size was 70 GB, while TiDB compressed it to 30 GB. Several queries were executed, including simple count, group‑by, filtered scans, and complex aggregations. Results (shown in the accompanying charts) demonstrate TiDB’s superior response times as the data volume grows.

System Benchmark

On an m4.16xlarge instance, Sysbench point‑select tests (1‑128 threads) showed TiDB achieving higher transactions per second than MySQL.

Test Report 2

Using the hardware shown, a two‑stage JMeter test (1 M and 13 M orders) followed by a 100 k request load indicated that for small datasets MySQL outperforms TiDB due to network and coordination overhead, but TiDB’s advantage becomes evident with larger data volumes.

Conclusion

TiDB’s architecture and features make it a compelling alternative to traditional MySQL sharding for large‑scale, high‑availability workloads. Organizations with modest data sizes may not need the added complexity, but enterprises facing rapid growth should consider TiDB as the ultimate solution beyond sharding.