Mastering Database Sharding with MyCat: Vertical & Horizontal Partitioning Explained

1. Why Sharding

Database architecture evolves from a single-instance database to read‑write separation using master‑slave replication. As traffic grows, read requests can be scaled with multiple slaves, but write traffic becomes a bottleneck; adding another master does not solve consistency issues.

Sharding (splitting databases and tables across different MySQL servers) distributes write load by placing data on multiple servers.

2. Problems Caused by Large Databases and Tables

Single database too large: limited processing capacity, insufficient disk space, I/O bottlenecks; requires splitting into smaller databases.

Single table too large: low CRUD efficiency; huge index files cause I/O delays and query timeouts. MyCat’s rule.xml defines splitting algorithms (time‑based, consistent hash, modulo, etc.).

Splitting Strategy

When a database is too large, first determine whether the issue is many tables or large data volume:

If many tables cause excess data, use vertical splitting—separate business domains into different databases.

If a single table’s data volume is huge, use horizontal splitting—divide the table according to a rule defined in mycat/conf/rule.xml.

The principle is to consider vertical splitting before horizontal splitting.

3. Vertical Splitting

Vertical splitting can be combined with read‑write separation.

1. Vertical Database Splitting

Configuration example (server.xml):

<user name="root">
  <property name="password">123456</property>
  <property name="schemas">USERDB1,USERDB2</property>
</user>

This defines two logical databases USERDB1 and USERDB2.

Corresponding schema.xml defines two logical schemas and their data nodes:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="USERDB1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" />
  <schema name="USERDB2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2" />
  <dataNode name="dn1" dataHost="node1" database="mytest1" />
  <dataNode name="dn2" dataHost="node2" database="mytest2" />
  <dataHost name="node1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <writeHost host="192.168.131.129" url="192.168.131.129:3306" user="root" password="123456" />
  </dataHost>
  <dataHost name="node2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
    <heartbeat>select user()</heartbeat>
    <writeHost host="192.168.0.6" url="192.168.0.6:3306" user="root" password="123456" />
  </dataHost>
</mycat:schema>

The two logical databases map to two different physical machines, each holding a subset of tables.

2. Vertical Table Splitting

Vertical table splitting separates columns; large or rarely used columns are moved to an extension table, while frequently accessed columns stay in the main table.

4. Horizontal Splitting

Horizontal splitting targets massive single tables (e.g., order tables) by dividing rows across multiple tables using RANGE, HASH, or modulo rules. The tables remain in the same database, but the approach alleviates I/O and connection bottlenecks.

Sharding can coexist with master‑slave replication, but read‑write separation relies on replication.

1. Configure Horizontal Sharding

Example server.xml snippet:

<user name="root">
  <property name="password">123456</property>
  <property name="schemas">USERDB</property>
</user>

Corresponding schema.xml defines a logical schema with tables and data nodes:

<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100">
    <table name="user" dataNode="dn1" />
    <table name="student" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long" />
  </schema>
  <dataNode name="dn1" dataHost="node1" database="mytest1" />
  <dataNode name="dn2" dataHost="node2" database="mytest2" />
  ... (dataHost definitions as above) ...
</mycat:schema>

In this configuration, the logical table student is split across two physical tables on dn1 and dn2 using the modulo‑long rule (id % 2).

Testing steps include connecting to MyCat’s 8066 port, inserting data into user and student tables, and observing that inserts are routed to the appropriate physical tables on the Linux and Windows hosts as shown in the screenshots.

Queries issued to MyCat are automatically merged from the two data nodes, providing a seamless view of the sharded data.