A Guide to Database Sharding for MySQL with Apache ShardingSphere: A Horizontal Scaling Solution - ITFROMZERO - Share tobe shared! Is MySQL hitting 45M rows, saturating disk I/O, and freezing production? Learn how to deploy Apache ShardingSphere-Proxy to shard horizontally, optimize routing, and avoid common performance traps.

2:15 AM: PagerDuty alarms go off relentlessly. Production MySQL cluster CPU usage hits 99%, connection pools are fully exhausted, and the order queue is backed up with more than 12,000 requests. The orders table has just crossed 45 million records, weighing in at over 85GB.

Even with properly tuned indexes, a simple SELECT filtered by user_id takes up to 3.8 seconds due to severe disk I/O bottlenecks. Upgrading RAM from 64GB to 128GB (Vertical Scaling) at this stage is not only expensive, but it also only buys you a few weeks. To ensure your system survives long term, you must split your data across multiple servers: Database Sharding with Apache ShardingSphere.

Table of Contents

1. Quick Start: Set Up a ShardingSphere-Proxy Cluster in 5 Minutes

ShardingSphere-Proxy is the most convenient choice when you need to put out fires fast. Positioned between your application and the MySQL cluster, it functions as a proxy layer compliant with the standard MySQL protocol. As a result, backend teams writing Spring Boot, Go, or Node.js do not need to modify a single line of connection code.

Prepare two physical MySQL instances running on port 3306:

Node 1: 192.168.1.10:3306 (contains database ds_0)
Node 2: 192.168.1.11:3306 (contains database ds_1)

Create the database and table on both nodes:

CREATE DATABASE IF NOT EXISTS ds_0;
CREATE DATABASE IF NOT EXISTS ds_1;

-- Run this DDL on both ds_0 and ds_1 nodes:
CREATE TABLE t_order (
    order_id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2),
    status VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Configure routing rules in config-sharding.yaml:

databaseName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.10:3306/ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: SecretPassword123
  ds_1:
    url: jdbc:mysql://192.168.1.11:3306/ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: SecretPassword123

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: inline_db
  shardingAlgorithms:
    inline_db:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}

Launch the Proxy using a Docker container:

docker run -d --name shardingsphere-proxy \
  -p 3307:3307 \
  -v $(pwd)/config-sharding.yaml:/opt/shardingsphere-proxy/conf/config-sharding.yaml \
  apache/shardingsphere-proxy:latest

Once this step is complete, point your application to 127.0.0.1:3307, targeting the sharding_db database. When writing a record with user_id = 101, data is automatically routed to ds_1 (101 % 2 = 1). If user_id = 100, the data routes to ds_0. The application continues to see just a single logical database.

2. Technical Architecture of Sharding and ShardingSphere

Why Do Indexes Lose Effectiveness as Data Grows?

Tables under 5 million rows run smoothly. Once reaching 50 million rows, the B-Tree index structure expands beyond the capacity of innodb_buffer_pool_size. MySQL can no longer retain the entire index in RAM. Every query is forced to perform random disk I/O, driving I/O wait times up to 40–60%.

A MySQL Read Replica setup only offloads read queries. Write throughput and disk capacity limits remain bottlenecked on the primary master node. Horizontal sharding fundamentally addresses this issue by distributing read load, write load, and storage evenly across multiple independent servers.

Two Architectural Flavors of Apache ShardingSphere

The ShardingSphere ecosystem offers two deployment modes:

ShardingSphere-JDBC: A lightweight library embedded directly into your application as a custom JDBC driver. Latency is virtually zero because there is no extra network hop. Drawback: tightly coupled with JVM languages (Java, Kotlin).
ShardingSphere-Proxy: An independent database proxy sitting in front of the MySQL cluster. Any language—from Go, Python, and PHP to C#—connects seamlessly via standard MySQL ports. This is ideal for microservices environments with diverse tech stacks.

How the Data Routing Algorithm Works

In the sample configuration, the INLINE rule uses the expression ds_${user_id % 2}. The user_id column serves as the Sharding Key. Upon receiving a query like:

SELECT * FROM t_order WHERE user_id = 100;

The Proxy parses the AST (Abstract Syntax Tree), evaluates 100 % 2 = 0, and forwards the query straight to ds_0. Node ds_1 is entirely bypassed, consuming zero CPU cycles or I/O operations.

3. Handling Distributed IDs and Multi-Tier Sharding

Preventing ID Collisions with the Snowflake Algorithm

Never rely on MySQL’s default AUTO_INCREMENT when sharding. Doing so causes both ds_0 and ds_1 to generate id = 1, breaking data integrity. ShardingSphere includes built-in support for the Snowflake algorithm, automatically generating unique 64-bit integer IDs across the cluster without collision risks.

Declare the generator within your rule configuration:

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake_gen
  keyGenerators:
    snowflake_gen:
      type: SNOWFLAKE

Two-Tier Sharding Strategy (Database + Table)

When scale reaches hundreds of millions of records, combining both database-level and physical table-level sharding is recommended. Suppose we have 2 databases (ds_0, ds_1), and each contains 2 sub-tables (t_order_0, t_order_1), totaling 4 physical tables:

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: inline_db
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: inline_tbl
  shardingAlgorithms:
    inline_db:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    inline_tbl:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}

4. Production Battle-Tested Lessons

The Heavy Cost of Queries Missing Sharding Keys

Consider the following query:

SELECT * FROM t_order WHERE status = 'COMPLETED';

Because the statement lacks a user_id filter, the Proxy cannot determine where the data resides. It is forced to broadcast the query across every underlying shard (a Scatter-Gather pattern), and subsequently pull millions of rows into RAM for sorting and merging. This often triggers immediate Proxy Out-Of-Memory (OOM) errors. Mitigate this risk by strictly requiring developers to pass the Sharding Key in every order lookup API.

Handling Distributed Transactions Correctly

Writing data across two physical databases invalidates MySQL’s local ACID transaction guarantees. ShardingSphere supports XA standards (2-Phase Commit) and the Saga pattern via Apache Seata. However, XA can degrade throughput by 4x to 5x due to extended resource locking. In practice, the best solution is smart schema design: ensure all operations within a single business transaction share the same Sharding Key.

Use Broadcast Tables for Reference Data

Low-churn tables that are frequently joined—such as provinces or product_categories—should be configured as Broadcast Tables. ShardingSphere replicates these tables to all shard nodes. Any JOIN operation between orders and reference data will execute entirely within the local node, eliminating cross-network data transfer costs.