Database Bottlenecks and Sharding Strategies

Database performance problems often stem from I/O or CPU bottlenecks, which increase active connections and can exhaust the pool of usable connections, causing service degradation or crashes.

I/O bottlenecks include disk read overload caused by hot data that cannot fit into cache (solved by horizontal database sharding or vertical sharding) and network bandwidth limits when request volume is high (addressed by horizontal database sharding).

CPU bottlenecks arise from inefficient SQL statements—joins, GROUP BY, ORDER BY, or non‑indexed filters—that increase computation, as well as large tables that require full scans; solutions involve SQL optimization, proper indexing, and horizontal table partitioning.

Horizontal sharding (horizontal database partitioning) splits a single database into multiple databases based on a chosen key (hash, range, etc.). All databases share the same schema, store disjoint data, and together represent the full dataset; it is useful when overall concurrency rises but table‑level partitioning cannot fully solve the problem.

Horizontal table partitioning divides a large table into multiple tables using the same key strategy, keeping schemas identical while data sets are mutually exclusive; it helps when a single table’s size hurts SQL efficiency and CPU usage.

Vertical database partitioning moves groups of tables into separate databases according to business domains, resulting in different schemas per database and non‑overlapping data; it is appropriate when concurrency spikes and distinct business modules emerge, enabling service‑oriented architectures.

Vertical table partitioning separates frequently accessed fields (hot data) into a main table and less‑used fields into extension tables, reducing row size, cache pressure, and random read I/O; queries should retrieve data from both tables in the service layer rather than using joins.

Common sharding tools include Sharding‑Sphere (formerly Sharding‑JDBC), TDDL (Taobao Distributed Data Layer), and Mycat; users should evaluate their advantages and drawbacks independently.

The typical sharding workflow involves assessing current and projected capacity, selecting a uniformly distributed key, defining a partitioning rule (hash or range), executing the migration (usually with dual‑write), and planning for future scaling to minimize data movement.

Key challenges include queries that do not contain the partition key (addressed by mapping, gene‑based, or NoSQL approaches), cross‑shard pagination (often solved with Elasticsearch or similar), and scaling (horizontal database expansion via replica promotion and horizontal table expansion via dual‑write migration).

In summary, identify the true bottleneck before deciding on sharding, choose keys that balance even distribution and query requirements, and keep partitioning rules as simple as possible.

For a concrete example, see the GitHub repository: https://github.com/littlecharacter4s/study-sharding.