MySQL - Vertical Partitioning

MySQL - Table Partitioning

In database management, partitioning is a technique used to divide large tables into smaller, more manageable pieces called partitions. While the partitions can be stored and managed individually, they are still treated as a single logical table by the database. This can significantly improve performance for certain types of queries.

It’s important to distinguish between two main types of partitioning: Horizontal and Vertical. MySQL’s native partitioning feature implements Horizontal Partitioning, which splits a table’s rows into different partitions based on a specified key. Vertical Partitioning, on the other hand, involves splitting a table’s columns into multiple tables, which is typically achieved through database design (like normalization) rather than a built-in command. This tutorial focuses on MySQL’s built-in Horizontal Partitioning capabilities.

Understanding MySQL’s Horizontal Partitioning

MySQL provides several partitioning types, such as RANGE, LIST, HASH, and KEY. This guide will focus on COLUMNS partitioning, a variant of RANGE and LIST that allows using multiple columns and more data types for the partition key.

The primary benefits of partitioning are:

• Performance Improvement: Queries that access only a subset of data can run faster because the database only needs to scan the relevant partitions (a process called ‘partition pruning’) instead of the entire table.
• Easier Management: Administrative tasks like backing up, restoring, or rebuilding indexes can be performed on individual partitions, reducing the maintenance window for very large tables.

RANGE COLUMNS Partitioning

RANGE COLUMNS partitioning assigns rows to partitions based on whether their column values fall within specified ranges. This is particularly useful for data that has a natural range, like dates or prices.

Example: Partitioning an E-commerce Orders Table

Imagine an orders table that grows very large. We can partition it by the order_date to improve the performance of date-based reports.

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_total DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, order_date) -- Partition key must be part of the primary key
)
PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p2022 VALUES LESS THAN ('2023-01-01'),
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

Let’s insert some sample data:

INSERT INTO orders (customer_id, order_date, order_total) VALUES
(101, '2022-11-15', 150.75),
(102, '2023-03-20', 89.99),
(103, '2023-08-10', 230.00),
(101, '2024-01-05', 45.50);

To see how MySQL has distributed the rows, we can query the INFORMATION_SCHEMA.PARTITIONS table:

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'orders';

The output shows the row count for each partition:

PARTITION_NAME	TABLE_ROWS
p2022	1
p2023	2
p_future	1

Demonstrating Partition Pruning

When we run a query with a WHERE clause on the partition key, MySQL is smart enough to scan only the relevant partition(s). We can verify this with the EXPLAIN statement.

EXPLAIN SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

The output of the EXPLAIN command will show a partitions column, indicating that only the p2023 partition was accessed, demonstrating the performance benefit.

LIST COLUMNS Partitioning

LIST COLUMNS partitioning is used to assign rows to partitions based on a match from a discrete set of values. It is ideal for categorical data, such as a product category, country code, or status.

Example: Partitioning a Users Table by Region

Let’s create a users table and partition it based on the region column.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    region VARCHAR(10) NOT NULL,
    signup_date DATE,
    PRIMARY KEY (user_id, region)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north_america VALUES IN('USA', 'CAN', 'MEX'),
    PARTITION p_europe VALUES IN('GBR', 'DEU', 'FRA'),
    PARTITION p_asia VALUES IN('JPN', 'IND', 'SGP')
);

Now, let’s insert some users:

INSERT INTO users (username, region, signup_date) VALUES
('john_doe', 'USA', '2023-01-15'),
('jane_smith', 'DEU', '2023-02-20'),
('kenji_t', 'JPN', '2023-04-10'),
('maria_g', 'MEX', '2023-05-01');

Let’s check the partition status:

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

The output shows the distribution of users by regional partition:

PARTITION_NAME	TABLE_ROWS
p_north_america	2
p_europe	1
p_asia	1

Querying a Specific Partition

Similar to range partitioning, queries filtering by the region will benefit from partition pruning. You can also explicitly query a single partition, which can be useful for maintenance tasks.

SELECT * FROM users PARTITION(p_europe);

This query returns only the users from the European partition:

user_id	username	region	signup_date
2	jane_smith	DEU	2023-02-20

Best Practices & Considerations

• Partition Key Choice: The columns used for partitioning should frequently appear in the WHERE clauses of your most common and performance-critical queries.
• Primary Keys: The partitioning key must be part of the table’s primary key or any unique keys.
• Too Many Partitions: Creating an excessive number of partitions can degrade performance due to increased metadata management overhead. A few dozen to a few hundred partitions is a reasonable range for most use cases.
• Maintenance: Plan for partition maintenance. For example, in a time-based range partition, you might add new partitions for future periods and drop old ones.