Scaling MySQL: Evolution of Database Architecture

As application traffic grows, monolithic database architectures often become bottlenecks. To handle increased loads, engineers typically employ a combination of architectural changes: service separation, master-slave replication, database sharding (splitting databases), and table partitioning (splittting tables). This article focuses specifically on the latter two: sharding and partitioning.

1. Service Separation

In the early stages of development, a monolithic architecture with a single database containing tables like users, products, reviews, and orders is common. However, as the system scales, this becomes difficult to maintain and limits performance.

The solution is to decouple the system. For instance, in an e-commerce platform, you would migrate the user data to a user_db, product data to a catalog_db, and orders to an order_db. This distributes the load across multiple physical or logical database instances, significantly improving overall system throughput.

2. Master-Slave Replication

While this article focuses on sharding, it is worth noting that replication (Master/Slave) is often used in conjunction. Data is replicated asynchronously from a Master node to Slave nodes. Applications direct write operations to the Master and read operations to the Slaves, though developers must account for eventual consistency due to replication lag.

3. Table Partitioning Strategy

Key Concepts: Shard Key (e.g., Customer ID), Table Count

The most common strategy for partitioning involves using a unique identifier, such as a customer_id. This ensures that all records for a specific user reside in the same table, simplifying queries.

Consider an order table structure:

CREATE TABLE `orders` (
  `id` bigint(32) primary key auto_increment,
  `customer_id` bigint(32),
  `amount` decimal(10,2),
  ...
)

If we decide to split this into 100 physical tables (e.g., orders_00 to orders_99), we use a modulo operation on the shard key. Given a table count of 100, the logic is customer_id % 100.

For example, if customer_id = 101:

-- Calculation: 101 % 100 = 1
-- Target table: orders_01
SELECT * FROM orders_01 WHERE customer_id = 101;

4. Database Sharding Strategy

Table partitioning solves the issue of large table scans, but it does not solve the I/O limit of a single data base server. Sharding distributes data across multiple database instances.

Similar to partitioning, we use the customer_id and a Database Count variable. If we have 10 database instances (shards), we route the user to a specific database using customer_id % 10.

If the ID is a UUID or non-numeric string, it must first be passed through a hashing algorithm (like CRC32 or MD5) before the modulo operation is applied.

5. Combined Sharding and Partitioning

To maximize both concurrency (via sharding) and query speed (via partitioning), we often combine the two. The routing logic becomes slightly more complex. Assuming we have N shards and M tables per shard:

1. intermediate_value = customer_id % (N * M);
2. shard_number = floor(intermediate_value / M);
3. table_number = intermediate_value % M;

Example Scenario:

• Total Shards (N): 256
• Tables per Shard (M): 1024
• Customer ID: 262145

Calculation:

1. intermediate_value = 262145 % (256 * 1024) = 262145 % 262144 = 1
2. shard_number = floor(1 / 1024) = 0
3. table_number = 1 % 1024 = 1

Result: The record is stored in Shard 0, specifically in Table 1.

Considerations and Trade-offs

While sharding improves performance, it introduces complexity:

• Distributed Transactions: Transactions spanning multiple shards are difficult to manage and require mechanisms like Two-Phase Commit (2PC) or eventual consistency patterns.
• Joins: Performing SQL joins across different shards is not possible natively; this logic must be handled in the application layer.
• Re-sharding: If the cluster needs to grow (e.g., adding more shards), data migration is required, which is often complex and downtime-prone without proper tooling.

Alternative routing strategies include using numeric ranges (Range-based) or consistent hashing. Consistant hashing helps minimize data movement during cluster scaling but can lead to uneven distribution compared to modulo hashing.

Middleware solutions like Cobar (by Alibaba) or proxy tools such as MyCat and ShardingSphere can help automate these routing rules, allowing the application to interact with the database cluster as if it were a single instance.