Vertical Partitioning in MySQL: Database, Schema, and Table Level Splitting

Introduction

Generally, data sharding can be divided into two layers: vertical sharding and horizontal sharding. This article focuses on vertical sharding.

Vertical sharding splits data by database, table, or column units.

Body

MySQL vertical sharding can be subdivided into: vertical database splitting (instance level), vertical schema splitting (schema level), and vertical table splitting (column level).

1. Vertical Database Splitting

In the business layer, logic is divided into smaller independent services, each stored in a separate MySQL instance; queries target only the relevant instance, similar to micro‑service governance.

As shown in Figure 1, each sub‑business corresponds to a MySQL instance group deployed with HA architectures (master‑slave sync, group replication, MySQL Cluster, etc.).

The advantages are clear: workload is distributed across multiple small databases, improving overall performance. The drawbacks are that the application must maintain routing information and further splitting becomes difficult once a single instance reaches its limits.

2. Vertical Schema Splitting

Similar to database splitting, but the smallest unit is a schema rather than an instance. In MySQL a database equals a schema, and this method addresses performance degradation caused by too many files in a single directory.

Figure 2 illustrates a database (dbA) containing 10,000 tables, which are divided into ten separate databases (dbA1‑dbA10), each holding 1,000 tables.

Example steps for vertical schema splitting:

(debian-ytt1:3500)|(ytta)>select count(*) from information_schema.tables where table_schema='ytta';
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.04 sec)

Create ten new databases ytt1‑ytt10:

for i in `seq 1 10`; \
  do mysql -S mysqld.sock -uroot -proot -e "create database ytt$i"; done

Copy table structures and data to each new database (assuming single‑file‑per‑table):

for i in `seq 0 9`; \
  do for j in `seq 1 1000`; \
    do x=$((j + i * 1000)); \
    mysql -uroot -proot -hdebian-ytt1 -P3500 \
      -e "use ytta;create table ytt$((i+1)).t$x like ytta.t$x;alter table ytt$((i+1)).t$x discard tablespace;"; \
    done; \
  done;

Unlock tables after copying:

rs.run_sql('unlock tables')

Import tablespaces into the new databases:

for i in range(1,11):
  for j in range(1,1001):
    x = j + (i - 1) * 1000
    rs.run_sql('alter table ytt'+ str(i) +'.t' + str(x)  + ' import tablespace')

Advantages: no schema changes, logical readability improves, minimal code changes. Example query before splitting:

select * from t1 join t1001 using(id) join t2001 using(id);

After splitting:

select * from ytt1.t1 join ytt2.t1001 using(id) join ytt3.t2001 using(id);

3. Vertical Table Splitting

The basic unit is a table; a wide table is divided into multiple narrower tables based on column access frequency (hot vs. cold columns).

Figure 3 shows a table A with five columns (one primary key, four non‑key). It is split into A11 and A12, each retaining the primary key.

Example: create a table with 1,000 columns and 10,000 rows, then split it into 100 tables each with 11 columns (including the primary key).

field_list=[]
for i in range(1,1001): field_list.append('r'+str(i)+' int')
field_lists=','.join(field_list)
rs.run_sql('create table t_large(id serial primary key,'+field_lists+')')
# insert rows
v_list=[]
for i in range(1000,2000): v_list.append(str(i))
v_lists=','.join(v_list)
for i in range(1,10001): rs.run_sql('insert into t_large select null,'+v_lists)

Split into 100 tables:

for i in range(1,101):
  f_list1=[]; f_list2=[]
  for j in range(1,11):
    f_list1.append('r'+str(j+(i-1)*10)+' int')
    f_list2.append('r'+str(j+(i-1)*10))
  rs.run_sql('create table t_large'+str(i)+'( id serial primary key,'+','.join(f_list1)+')')
  rs.run_sql('insert into t_large'+str(i)+' select id,'+','.join(f_list2)+' from t_large')

Querying the hot columns now only touches the relevant small table, and updates on hot columns become dramatically faster (tens of times speed‑up).

Summary

Vertical partitioning in MySQL can be performed at three levels: vertical database splitting (instance level), vertical schema splitting, and vertical table splitting. The main advantages are clearer logical separation and improved I/O and connection handling; the main disadvantages are difficulty scaling a saturated shard and residual cross‑shard joins.

Logical business becomes clearer and easier to manage.

I/O and connection count can be improved.

When a single shard reaches its performance limit, finer‑grained splitting is hard.

Complete isolation is difficult; low‑frequency cross‑shard joins may still occur.