Mastering Database Sharding: Boost Performance with Horizontal Partitioning

Chapter 1 Introduction

With the widespread adoption of Internet applications, massive data storage and access have become bottlenecks in system design. A large‑scale web service handling billions of page views per day puts enormous load on databases, threatening stability and scalability. Horizontal data partitioning (sharding) is the preferred method for scaling the data layer.

Horizontal sharding reduces load on a single machine and minimizes loss from failures.

Load‑balancing strategies lower per‑machine access load and reduce outage probability.

Cluster solutions eliminate single‑point‑of‑failure issues.

Read‑write separation maximizes read speed and concurrency.

Chapter 2 Basic Principles and Concepts

What Is Data Sharding?

The term "shard" means "fragment" in English. In database terminology, sharding (often translated as "分片") refers to horizontal scaling by distributing data across multiple databases or tables. Its main goal is to overcome the I/O limits of a single database server and improve scalability.

For example, consider a blog's article table. By assigning user IDs 1‑10,000 to DB1, 10,001‑20,000 to DB2, and so on, the data is naturally partitioned across databases.

Routing to the correct database relies on the same partition key (e.g., user_id). Knowing a user's ID allows the system to locate the appropriate DB using the predefined rule.

Designing for sharding often introduces redundant fields (such as user_id) that serve as partition markers, which may violate normal normalization rules but are necessary for efficient scaling.

Why Perform Data Sharding?

Although mature databases like Oracle can handle large volumes, their licensing costs and hardware requirements are prohibitive for many companies. When a single machine reaches its physical limits, horizontal expansion by adding more machines becomes the practical solution.

Sharding distributes compute, storage, and I/O across multiple nodes, improves fault isolation, and avoids single‑point failures, thereby reducing operational costs while maintaining high performance.

How to Achieve Data Sharding

Physical sharding distributes data across different DB servers using routing rules, turning a single‑machine load into an N‑machine load.

Logical sharding within a database splits a large table into multiple sub‑tables (e.g., article_001, article_002, …). This reduces the number of rows per table, dramatically decreasing index‑building time and improving write performance.

Common partitioning strategies include:

Range partitioning : Assign a range of IDs to each DB (e.g., 1‑1000 → DB1, 1001‑2000 → DB2). Advantages: partial migration possible; disadvantages: uneven data distribution.

Hash modulo partitioning : Compute user_id % N to select one of N databases, yielding uniform distribution; however, data migration can be complex.

Mapping table : Maintain a separate configuration DB that maps each user_id to a specific DB. This offers strong flexibility but adds an extra lookup overhead.

Complex projects may combine these methods.

Chapter 3 Research Outline

The distributed data solution provides the following functions:

Provide database and routing rules (RouteRule, RR).

Introduce the concept of a Group (cluster) to ensure high availability.

Introduce a Load Balance Policy (LB).

Implement node health‑check mechanisms to keep LB decisions accurate and maintain system stability.

Support read/write separation to improve query speed.

Pure database‑sharding alone is insufficient; a single node failure would make a portion of data inaccessible. By grouping multiple machines per shard (one Master and several Slaves), load can be shared and failures tolerated.

When a Slave fails, the load balancer redirects traffic to remaining healthy nodes, preventing data loss. However, the LB must be aware of node health; otherwise it may still route requests to a failed Slave, causing errors.

Two mechanisms address this:

Health‑check probing : The client periodically attempts connections to each node to verify availability.

Push‑based status updates : DBAs or monitoring systems actively notify the application of node failures, allowing the LB to exclude unhealthy nodes.

Read/write separation further improves performance: writes go to the Master, while reads are distributed among Slaves. In typical web workloads, the read/write ratio is about 10:1, justifying multiple Slaves.

Original source: http://zhengdl126.iteye.com/blog/419850