MySQL - Horizontal Partitioning

MySQL - Horizontal Partitioning

Partitioning is an advanced database technique used to divide a very large table into smaller, more manageable pieces called partitions. While logically the table remains a single entity, physically its data is stored across these separate partitions. This can lead to significant improvements in performance and manageability.

MySQL supports two main types of partitioning: horizontal and vertical. This tutorial focuses on Horizontal Partitioning, where rows of a table are divided into different partitions based on a partitioning key.

Why Use Horizontal Partitioning?

*   **Performance**: Queries that access only a subset of data can run much faster. If the `WHERE` clause of a query matches the partitioning scheme, the database engine can scan only the relevant partition(s) instead of the entire table. This is called **partition pruning**.
*   **Manageability**: Administrative tasks like backups, restores, or deleting old data can be performed on individual partitions. For example, to delete all data from a specific year in a time-series table, you can simply drop the corresponding partition, which is much faster than running a `DELETE` query.
*   **Storage**: You can assign different partitions to different physical storage devices, optimizing for I/O.

RANGE Partitioning

RANGE partitioning assigns rows to partitions based on whether a column’s value falls within a given continuous range. It is ideal for data with a clear range, such as dates or prices.

Example: Partitioning by Year

Let’s create a Sales table and partition it by the year of the order_date. This is a very common use case for large, time-series tables.

CREATE TABLE Sales (
    order_id INT AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, order_date) -- Partitioning key must be part of the primary key
)
PARTITION BY RANGE(YEAR(order_date)) (
    PARTITION p_2021 VALUES LESS THAN (2022),
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE -- Best practice: a catch-all partition
);

Now, let’s insert some data. MySQL will automatically place each row in the correct partition.

INSERT INTO Sales (product_name, order_date, amount) VALUES
    ('Laptop', '2021-11-15', 1200.00),
    ('Mouse', '2022-01-20', 25.00),
    ('Keyboard', '2022-07-30', 75.00),
    ('Monitor', '2023-03-10', 300.00),
    ('Docking Station', '2024-02-05', 150.00);

To see how a query benefits from partitioning, we can use EXPLAIN.

EXPLAIN SELECT * FROM Sales WHERE order_date >= '2022-01-01' AND order_date < '2023-01-01';

The output of the EXPLAIN statement will show a partitions column, indicating that MySQL only scanned the p_2022 partition, not the entire table.

LIST Partitioning

LIST partitioning assigns rows to partitions based on whether a column’s value matches one of a discrete set of values. It is perfect for categorical data, like country codes, department IDs, or status codes.

Example: Partitioning by Region

Let’s create a Users table partitioned by region.

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    region ENUM('North America', 'Europe', 'Asia', 'Other') NOT NULL
)
PARTITION BY LIST(region) (
    PARTITION p_na VALUES IN ('North America'),
    PARTITION p_eu VALUES IN ('Europe'),
    PARTITION p_asia VALUES IN ('Asia'),
    PARTITION p_other VALUES IN ('Other')
);

Inserting data will route rows to the correct partition based on the region.

INSERT INTO Users (username, region) VALUES
    ('john_doe', 'North America'),
    ('jane_smith', 'Europe'),
    ('akira', 'Asia');

A query for users in Europe will only scan the p_eu partition.

SELECT * FROM Users WHERE region = 'Europe';

HASH and KEY Partitioning

HASH and KEY partitioning are used to ensure an even distribution of data across a specified number of partitions. You don’t define explicit value ranges; instead, MySQL uses a hashing function on a column value to determine the partition.

• HASH Partitioning: The partitioning expression must return an integer. The partitioning is done using MOD(expression, num_partitions).
• KEY Partitioning: Similar to HASH, but MySQL supplies its own internal hashing function. It allows partitioning on non-integer columns (like strings or dates) and can use a table’s primary key by default if no column is specified.

Example: KEY Partitioning by User ID

This is useful when there’s no obvious business logic for RANGE or LIST, but you still want to break up a large table for better performance.

CREATE TABLE Sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id INT NOT NULL,
    login_time DATETIME NOT NULL
)
PARTITION BY KEY(user_id)
PARTITIONS 8; -- Distribute data into 8 partitions

In this case, all session data for a given user_id will be located in the same partition, which can be efficient for queries that filter by user_id.

Sub-partitioning

Sub-partitioning (also known as composite partitioning) involves dividing each partition further by a second partitioning method. For example, you could partition a table by a RANGE of dates and then sub-partition each of those by a HASH of the customer ID.

Example: RANGE-HASH Sub-partitioning

CREATE TABLE Orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    PRIMARY KEY (order_id, order_date, customer_id)
)
PARTITION BY RANGE(YEAR(order_date))
SUBPARTITION BY HASH(customer_id)
SUBPARTITIONS 4 (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

This creates a total of 3 * 4 = 12 partitions. A query like SELECT * FROM Orders WHERE order_date = '2023-05-20' AND customer_id = 123; will be extremely fast, as MySQL can first prune to the p2023 partition and then prune again to the specific sub-partition where customer_id = 123 would be located.

Managing Partitions

You can manage partitions using the ALTER TABLE statement.

*   **Dropping a partition**: `ALTER TABLE Sales DROP PARTITION p_2021;` (Instantly deletes all data from 2021).
*   **Adding a partition**: `ALTER TABLE Sales ADD PARTITION (PARTITION p_2024 VALUES LESS THAN (2025));` (Only for RANGE and LIST).
*   **Truncating a partition**: `ALTER TABLE Users TRUNCATE PARTITION p_asia;` (Deletes all data from the partition but keeps the partition itself).
*   **Checking partition status**: `SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'Sales';`