When to Use MySQL Partition Tables vs Sharding: Key Differences and Best Practices

1. Main Differences

1.1 Partition Table

A partition table appears as a single logical table to users, but underneath it consists of multiple physical sub‑tables, each stored in its own file. Queries on a partitioned table are translated into storage‑engine calls that locate the appropriate physical table.

From the InnoDB perspective, a partitioned table behaves like a regular table; the engine does not need to know whether a table is partitioned.

Example: creating an order table partitioned by month.

CREATE TABLE tb_order (
    order_id INT AUTO_INCREMENT,
    order_amount DECIMAL(19,4),
    order_date DATE,
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (TO_DAYS(order_date)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p2 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p3 VALUES LESS THAN (TO_DAYS('2023-05-01')),
    PARTITION pMAX VALUES LESS THAN MAXVALUE
);

Inserting four rows creates four physical files, one for each partition.

INSERT INTO tb_order(order_amount, order_date) VALUE (20.3, '2023-02-02');
INSERT INTO tb_order(order_amount, order_date) VALUE (30.3, '2023-03-02');
INSERT INTO tb_order(order_amount, order_date) VALUE (40.3, '2023-04-02');
INSERT INTO tb_order(order_amount, order_date) VALUE (50.3, '2023-05-02');

Limitations of MySQL partition tables:

A table can have at most 1024 partitions.

Foreign‑key constraints cannot be defined on partitions.

All sub‑tables must use the same storage engine.

Partition functions and expressions have restrictions.

When operating on a partitioned table, MySQL often opens and locks all underlying sub‑tables, which can cause lock contention. However, if the WHERE clause matches the partition expression, MySQL can prune partitions that do not contain matching rows.

1.2 Sharding (Manual Table Splitting)

Sharding involves manually creating multiple tables with identical structures (e.g., tb_order_1, tb_order_2, tb_order_3, tb_order_4) and letting the application decide which table to read from or write to. For MySQL, each shard is an independent table.

Using sharding typically requires an application‑level proxy or a middleware that routes queries to the correct physical table.

Characteristics compared with partition tables:

If there is no proxy or middleware, the application must explicitly specify the target table for every operation.

Backup, restore, and DDL must be performed on each shard separately, increasing management overhead.

Because shards are independent, MySQL does not need to open all underlying tables at once, resulting in higher concurrency and lower lock‑conflict probability.

Cross‑shard queries are less efficient because the application or middleware must aggregate data.

Each shard maintains its own indexes, reducing index‑maintenance cost per shard.

2. Use Cases

2.1 Partition Table

Typical scenarios for partition tables include:

Very large tables that cannot fit entirely in memory, or where only recent data is hot while older data is cold.

Range‑based query patterns (e.g., date ranges).

High cost of full‑table scans and expensive index maintenance due to massive data volume.

Need to delete large amounts of data quickly (e.g., logs older than six months).

Requirement to backup, restore, or archive data by range independently.

Desire to reduce lock contention.

MySQL most commonly uses range partitioning, but also supports key, hash, list, and sub‑partitioning. When using partition tables, keep the following in mind:

The partition key must not be NULL; otherwise many NULL values may end up in the same partition.

The partition key should be an indexed column.

Limit the number of partitions to avoid excessive partition‑lookup overhead.

If partition pruning cannot be applied, MySQL may need to open and lock all sub‑tables, which is costly.

Maintenance operations such as repartitioning or other DDL can be expensive.

2.2 Sharding

Sharding is suitable for the following situations:

Single tables have become so large that query performance degrades, and data needs to be spread across multiple databases or tables.

Query conditions are not concentrated on a single range.

Fine‑grained data management requirements (e.g., per‑tenant isolation).

High‑frequency queries with strict performance requirements.

The additional maintenance cost of sharding is acceptable.

3. Summary

Both MySQL partition tables and sharding aim to solve performance problems caused by oversized single tables. Partition tables achieve this by filtering partitions during query execution, while sharding improves performance by operating on separate tables.

If the architecture already uses database‑level sharding, adding more shards is the preferred solution. If sharding is not in place and low maintenance cost is desired, a partitioned table is a viable alternative.

When using partition tables, choose a partition key that distributes data evenly and avoid excessive partition counts. When using sharding, pay attention to data consistency across shards.