Mastering High‑Concurrency Big Data: Sharding, Partitioning, and Index Strategies

Massive data processing remains a hot focus for many companies; this article shares practical high‑concurrency big‑data handling experiences.

Recent layoffs have increased competition for technical talent, making strong practical skills essential, especially when interviewers ask about large‑scale data challenges.

Common relational databases such as MySQL and SQL Server store data row‑wise and rely on B‑tree indexes. As table size grows, index trees expand, leading to more disk reads and reduced performance; typically, tables beyond millions to tens of millions of rows require alternative solutions.

One approach is read/write separation, using a primary database for writes and multiple replica databases for reads, which improves read capacity but does not scale write throughput.

Another solution is sharding (分库分表). By distributing data across multiple databases, overall service capacity increases.

Vertical Sharding (垂直拆分)

Data is partitioned by columns, storing different fields in separate databases with differing schemas.

Horizontal Sharding (水平拆分)

Data is partitioned by rows, keeping the same schema across databases but storing different record subsets.

Horizontal sharding solves storage capacity issues but introduces challenges such as uneven load distribution, key selection, and query limitations.

To achieve balanced distribution, choose a sharding key (e.g., userID) and apply a hash‑mod algorithm. However, using userID can overload a shard if a “big seller” has many records.

Common mitigation includes archiving older data (e.g., records older than three months) to a non‑relational store like HBase.

Queries after sharding must include the sharding key; otherwise, full‑table scans occur. A practical remedy is building a heterogeneous index table that asynchronously maintains a complete copy of data indexed by alternative dimensions, trading storage for query speed.

By storing a second copy keyed by userID, the system can retrieve all orders for a user without full scans.

These strategies illustrate how engineers can address performance bottlenecks in massive data processing, emphasizing both high‑level design and detailed implementation.