Background: Table partitioning is a technique used in databases to split a large table into smaller, more manageable pieces. This approach can significantly improve query performance, ease maintenance tasks, and facilitate more efficient data management.
In MySQL 8.0, partitioning support is provided by the InnoDB and NDB storage engines.
The architecture shown below.
To quickly set up and run a local MySQL Cluster with NDB storage engine, you can use the preconfigured Docker Compose setup available in the following GitHub repository:
Simply follow the instructions in the README file to get started.
docker-compose up
Now, let’s dive into the different types of MySQL table partitions and their examples
1. RANGE Partitioning
RANGE partitioning is used to divide a table into partitions based on a specified range of values for a given column. This type is particularly useful when dealing with data that has a natural range, such as dates or ages.
Example:
Consider a table ‘sales’ with a ‘sale_date’ column. We can create a range partition based on the year of the sale:
CREATE TABLE sales (
id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p0 VALUES LESS THAN (2019),
PARTITION p1 VALUES LESS THAN (2020),
PARTITION p2 VALUES LESS THAN (2021),
PARTITION p3 VALUES LESS THAN MAXVALUE
);Script to populate the sales table with sample data:
INSERT INTO sales (id, sale_date, amount) VALUES
(1, '2018-05-15', 100.00),
(2, '2018-12-20', 150.00),
(3, '2019-03-25', 200.00),
(4, '2019-11-05', 250.00),
(5, '2020-02-14', 120.00),
(6, '2020-06-30', 180.00),
(7, '2021-01-10', 300.00),
(8, '2021-07-15', 400.00);Script to query partition information for the sales table:
SELECT
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
PARTITION_ORDINAL_POSITION,
SUBPARTITION_ORDINAL_POSITION,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_NAME = 'sales'
AND TABLE_SCHEMA = 'test_db'; 
2. LIST Partitioning
LIST partitioning is similar to RANGE partitioning, but instead of using a range of values, it uses a list of discrete values for partitioning. This type is useful for columns with a limited set of known values.
Example:
Consider a table ‘employees’ with a ‘country’ column. We can create a list partition based on the country:
CREATE TABLE employees (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
country VARCHAR(2) NOT NULL
)
PARTITION BY LIST (country) (
PARTITION pAmericas VALUES IN ('US', 'CA', 'MX'),
PARTITION pEurope VALUES IN ('FR', 'DE', 'UK'),
PARTITION pAsia VALUES IN ('CN', 'JP', 'IN'),
PARTITION pOthers VALUES IN (DEFAULT)
);3. COLUMNS Partitioning
COLUMNS partitioning allows partitioning based on multiple columns, which can be of various data types. This type is especially useful when dealing with composite keys or multi-column ranges.
Example:
Consider a table ‘orders’ with ‘customer_id’ and ‘order_date’ columns. We can create a columns partition based on both columns:
CREATE TABLE orders (
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE COLUMNS(customer_id, order_date) (
PARTITION p0 VALUES LESS THAN (1000, '2019-01-01'),
PARTITION p1 VALUES LESS THAN (1000, '2020-01-01'),
PARTITION p2 VALUES LESS THAN (2000, '2019-01-01'),
PARTITION p3 VALUES LESS THAN (2000, '2020-01-01')
);4. HASH Partitioning
HASH partitioning uses a hashing function to distribute rows evenly across a predefined number of partitions. This type is useful for ensuring a balanced distribution of data when no obvious partitioning key is available.
Example:
Consider a table ‘products’ with an ‘id’ column. We can create a hash partition based on the ‘id’ column:
CREATE TABLE products (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL
)
PARTITION BY HASH (id) PARTITIONS 4;5. KEY Partitioning
KEY partitioning is similar to HASH partitioning, but it uses MySQL’s internal hashing function on one or more columns to distribute rows evenly across a predefined number of partitions. This type is useful for ensuring a balanced distribution of data when the columns used for partitioning are unique or indexed.
Example:
Consider a table ‘products’ with an ‘id’ column. We can create a key partition based on the ‘id’ column:
CREATE TABLE products (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (id)
)
PARTITION BY KEY(id) PARTITIONS 4;6. Subpartitioning
Subpartitioning is the process of partitioning each partition created by a primary partitioning method (such as RANGE or LIST) further using a secondary partitioning method (such as HASH or KEY). This type is useful for optimizing the distribution of data and query performance in specific use cases.
Example:
Consider a table ‘sales’ with ‘store_id’ and ‘sale_date’ columns. We can create a range partition based on the ‘sale_date’ column and subpartition each range partition using a hash partition on the ‘store_id’ column:
CREATE TABLE sales (
store_id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY HASH (store_id)
SUBPARTITIONS 4 (
PARTITION p0 VALUES LESS THAN (2019),
PARTITION p1 VALUES LESS THAN (2020),
PARTITION p2 VALUES LESS THAN (2021),
PARTITION p3 VALUES LESS THAN MAXVALUE
);References:
