SoFunction
Updated on 2025-04-06

MySQL data table partition technology PARTITION detailed explanation

What is a data table partition?

Data table partitioning refers to physically dividing a large table into multiple smaller and more manageable parts, but logically still a complete table. Each part is called a partition. By rationally designing partitioning strategies, the amount of data in queries can be effectively reduced and the query speed can be accelerated, and it can also help improve the efficiency of data management and maintenance.

Benefits of partitioning

  1. Improve query performance: By reducing the amount of data that needs to be scanned, especially for operations that often perform range queries.
  2. Simplify maintenance operations: Operations such as backup, recovery, deletion, etc. can be performed on a single or several partitions without affecting the entire table.
  3. Balancing I/O load: By distributing different partitions on different physical storage devices, I/O load can be effectively dispersed and the overall performance of the system can be improved.
  4. Improve usability: When a partition is corrupted, only the data from that partition is unavailable, and the data from other partitions can still be accessed normally.

MySQL supports partition types

MySQL supports multiple types of partitions, each type suitable for different scenarios:

  • RANGE partition: Allocate data based on a range of column values. Suitable for timestamps or date fields.
  • LIST partition: Allocate data based on a value whose column value belongs to a predefined list.
  • HASH partition: Partitioned according to the return value of user-defined expressions, usually used to evenly distribute data.
  • KEY Partition: Similar to HASH partition, but the MySQL system automatically calculates partition key values, suitable for primary or unique keys.
  • COLUMNS Partition: Allows multiple columns as partition keys, and supports RANGE and LIST partitions.

How to create a partition table

RANGE partition example

Suppose we have a log table that records user activity and want to partition by year:

CREATE TABLE user_activity (
    id INT NOT NULL,
    user_id INT NOT NULL,
    activity_date DATE NOT NULL
) PARTITION BY RANGE (YEAR(activity_date)) (
    PARTITION p0 VALUES LESS THAN (2018),
    PARTITION p1 VALUES LESS THAN (2019),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

HASH partition example

If we want to perform evenly distributed partitions based on user ID, we can do this:

CREATE TABLE user_data (
    user_id INT NOT NULL,
    data VARCHAR(100)
) PARTITION BY HASH (user_id)
PARTITIONS 4;

Partition maintenance

As the data grows, it may be necessary to periodically adjust partitioning strategies, such as adding new partitions or merging old partitions. MySQL provides​ALTER TABLE ... REORGANIZE PARTITION​Commands to help complete these tasks.

Add a new partition

If needed for the​user_activity​The table adds a new year partition, which can be done like this:

ALTER TABLE user_activity REORGANIZE PARTITION p3 INTO (
    PARTITION p3 VALUES LESS THAN (2021),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

Delete the partition

If certain partitions are no longer needed, you can delete them in the following ways:

ALTER TABLE user_activity DROP PARTITION p0;

By rationally utilizing MySQL's partitioning function, the performance and manageability of large data tables can be significantly improved. Choosing the right partition type and policy is the key to successfully implementing partitions. I hope this article can help you better understand and apply MySQL partitioning technology.

MySQL's data table partitioning technology (PARTITION) is a method to optimize the performance of large database tables. By physically dividing a large table into smaller parts, query efficiency can be significantly improved, especially when processing large amounts of data. Partitioning can be based on different strategies, such as range partition, list partition, hash partition and key partition.

Below I will explain how to use MySQL's partitioning technology through several practical application scenarios and provide corresponding sample code.

How to use MySQL's partitioning technology

1. RANGE Partitioning

Suppose we have a table that records user login information, which contains the user's ID and login time. In order to optimize the query of recent login records, we can partition this table according to the year.

CREATE TABLE user_logins (
    user_id INT NOT NULL,
    login_time DATETIME NOT NULL
) PARTITION BY RANGE (YEAR(login_time)) (
    PARTITION p0 VALUES LESS THAN (2015),
    PARTITION p1 VALUES LESS THAN (2016),
    PARTITION p2 VALUES LESS THAN (2017),
    PARTITION p3 VALUES LESS THAN (2018),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

In this example,​user_logins​The table is divided into five partitions, each of which stores login records within a specific year range.

2. List Partitioning

Suppose we have a product table that needs to be partitioned according to the product's category to speed up query by category.

CREATE TABLE products (
    product_id INT NOT NULL,
    category_id INT NOT NULL,
    name VARCHAR(100)
) PARTITION BY LIST (category_id) (
    PARTITION p_electronics VALUES IN (1, 2, 3),
    PARTITION p_clothing VALUES IN (4, 5),
    PARTITION p_food VALUES IN (6, 7, 8)
);

Here,​products​Table based​category_id​The different values ​​of ​ are divided into three partitions.

3. Hash partitioning

For a scenario where data is evenly distributed, such as an order table, a hash partition can be used to ensure that data is evenly distributed.

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL
) PARTITION BY HASH (order_id)
PARTITIONS 4;

In this example,​orders​The table is divided into 4 partitions.​order_id​​The hash function is calculated and determined in which partition the data is stored in.

4. Key Partitioning

Key partitioning is similar to hash partitioning, but it uses hash functions inside MySQL, which are usually used for fields of non-integer types.

CREATE TABLE employees (
    emp_id INT NOT NULL,
    name VARCHAR(100),
    hire_date DATE NOT NULL
) PARTITION BY KEY (emp_id)
PARTITIONS 5;

Here, the employeees table is key partitioned according to the emp_id and divided into 5 partitions.

The above example shows how to use MySQL's partitioning technology to optimize data access performance in different business scenarios. Choosing the appropriate partitioning strategy depends on the specific application requirements and data characteristics. Correct partition design can greatly improve query efficiency, reduce system load, and thus improve the performance of the entire database system. MySQL's data table partitioning technology is a method of physically dividing large tables into multiple small parts, each part being called a partition. This technique can improve query efficiency, especially for large data tables. Partitioning not only makes data management more efficient, but also improves performance, especially when processing large amounts of data. Below are some examples of common types of MySQL partitions and their corresponding SQL statements.

Common types of MySQL partitions and their corresponding SQL statement examples

1. RANGE partition

RANGE partitioning divides data based on a range of column values. For example, you can partition according to year:

CREATE TABLE sales (
    id INT NOT NULL,
    year INT NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (year) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2005),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

In this example,​sales​Table based​year​The values ​​of the column are divided into four partitions. Each partition contains data for a specific year range.

2. LIST partition

A LIST partition is similar to a RANGE partition, but it divides the data based on an explicit list of column values. For example, you can partition according to the region:

CREATE TABLE employees (
    id INT NOT NULL,
    region VARCHAR(10)
) PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('North'),
    PARTITION p_south VALUES IN ('South'),
    PARTITION p_east VALUES IN ('East'),
    PARTITION p_west VALUES IN ('West')
);

Here,​employees​Table based​region​The values ​​of the column are divided into four partitions, each containing employee data for a specific region.

3. HASH partition

HASH partition is used to evenly distribute data, and it uses the hash value of a user-defined expression to determine which partition the row belongs to. This is often used to ensure that the data is evenly distributed across all partitions:

CREATE TABLE customers (
    id INT NOT NULL,
    name VARCHAR(50)
) PARTITION BY HASH(id) PARTITIONS 4;

In this example,​customers​Table based​id​The hash value of the column is divided into four partitions.

4. KEY partition

The KEY partition is similar to the HASH partition, but it uses the hash function provided by the MySQL server. This is very useful for ensuring that the data is evenly distributed:

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL
) PARTITION BY KEY(order_id) PARTITIONS 8;

Here,​orders​Table based​order_id​The values ​​of the column are divided into eight partitions.

5. Subpartition

Subpartitions (SUBPARTITION) allow partitioning within a partition. This can further optimize data management and query performance:

CREATE TABLE history (
    id INT NOT NULL,
    date DATE NOT NULL
) PARTITION BY RANGE (YEAR(date))
SUBPARTITION BY HASH(TO_DAYS(date))
SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

In this example,​history​The table is based on​date​​ The year of the column is RANGE partitioned, and then each partition is internally based on ​​date​​ The specific date of the column is used for HASH subpartition.

Things to note

  • The partition key must be part of the primary key of the table, or if the table does not have a primary key, the partition key must be part of the unique index.
  • Partitioning can significantly improve query performance, but can also increase table complexity and management costs.
  • When designing partitioning strategies, the access pattern and distribution of data should be considered to ensure that partitioning can effectively improve performance.

Hopefully these examples and explanations can help you better understand and use MySQL's partitioning technology.

The above is the detailed explanation of MySQL data table partition technology PARTITION. For more information about MySQL data table partition PARTITION, please pay attention to my other related articles!