Database Sharding: Table Partitioning, Horizontal and Vertical Splitting, and Implementation with Sharding-JDBC and Mycat

Overview – In high‑concurrency big‑data scenarios, continuous system upgrades are required and database sharding (splitting tables and databases) is a common solution.

Why sharding is needed – As user volume grows, a single table can quickly reach tens of millions of rows and QPS can exceed 5,000‑8,000, exhausting storage and performance of a single DB instance.

Table partitioning – When a table reaches several million rows, performance degrades. Two common schemes are presented:

1. Modulus partitioning : Use the ID modulo N to decide which table stores a row. An example creates four tables customer0 , customer1 , customer2 , customer3 and a uuid table for auto‑increment IDs. The Java service CustomerService generates a UUID, calculates tableName = "customer" + (cusno % 3) , and inserts the record into the appropriate table.

create table customer0(
    id int unsigned primary key,
    name varchar(32) not null default '',
    pwd varchar(32) not null default ''
)engine=myisam charset utf8;

create table customer1(
    id int unsigned primary key,
    name varchar(32) not null default '',
    pwd varchar(32) not null default ''
)engine=myisam charset utf8;

create table customer2(
    id int unsigned primary key,
    name varchar(32) not null default '',
    pwd varchar(32) not null default ''
)engine=myisam charset utf8;

create table customer3(
    id int unsigned primary key,
    name varchar(32) not null default '',
    pwd varchar(32) not null default ''
)engine=myisam charset utf8;

create table uuid(
    id int unsigned primary key auto_increment
)engine=myisam charset utf8;

@Service
public class CustomerService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public String register(String name, String pwd) {
        String insertUUidSql = "insert into uuid values(null)";
        jdbcTemplate.update(insertUUidSql);
        Long cusno = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
        String tableName = "customer" + cusno % 3;
        String insertUserSql = "INSERT INTO " + tableName + " VALUES ('" + cusno + "','" + name + "','" + pwd + "');";
        System.out.println("insertUserSql:" + insertUserSql);
        jdbcTemplate.update(insertUserSql);
        return "success";
    }

    public String get(Long cusno) {
        String tableName = "customer" + cusno % 3;
        String sql = "select name from " + tableName + " where id=" + cusno;
        System.out.println("SQL:" + sql);
        return jdbcTemplate.queryForObject(sql, String.class);
    }
}

Pros and cons of modulus sharding – Advantages: data is evenly distributed, reducing pressure on a single DB. Disadvantages: data migration is required when scaling, and queries without the sharding key become complex because they may need to hit all tables.

Range‑based sharding – Data can be split by date or ID ranges (e.g., IDs 1‑9,999 in one table, 10,000‑19,999 in another). This eases future migrations and avoids hot‑spot issues, but may still create uneven load if certain ranges become hot.

Database splitting (vertical & horizontal) – Vertical splitting separates tables with low business coupling into different databases (e.g., user DB vs product DB). Horizontal splitting distributes rows of a large table across multiple databases using the same strategies as table sharding (modulus or range). Scaling options include:

Scale‑up: upgrade hardware (expensive, short‑term).

Scale‑out: add more database instances (supports long‑term growth).

Read‑write separation can also be applied to off‑load read traffic.

Solution landscape – Open‑source middleware such as Atlas, Mycat, TDDL, DRDS, Sharding‑JDBC, and Mycat can manage sharding. Sharding‑JDBC (client‑side) has low operational cost and high performance but requires every application to upgrade its dependency. Mycat (proxy‑side) adds deployment and maintenance overhead but is transparent to applications.

For small‑to‑medium companies, Sharding‑JDBC is recommended; for large enterprises with many services, Mycat is preferable.

Additional notes – Load balancing can be achieved with Nginx using weighted round‑robin. Primary‑key generation strategies and other operational details are illustrated with images in the original article.