MySQL Table Partitioning: Types and Examples

MySQL Table Partitioning: Types and Examples.

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.

Understanding MySQL Partitioning:-

MySQL is a Relational Database Management System (RDBMS) which stores data in the form of rows and columns in a table.

Different DB engine stores table data in the file systems in such a way, if you run a simple filter query on a table it will scan the whole file in which table data is stored.

Partitioning a table divides the data into logical chunks based on keys(columns values) and stores the chunks inside the file system in such a way, if a simple filter query is run on the partitioned table it will only scan the file containing a chunk of data that you required.

So in a way partitioning distributes your table’s data across the file system, so when the query is run on a table only a fraction of data is processed which results in better performance.

RANGE Partitioning:-

Range partitioning involves dividing data based on a specified range of column values. For example, you can partition a table based on date ranges, such as monthly or yearly intervals. This technique is beneficial for time-series data or any data with a natural ordering. Sample query is given below:

CREATE TABLE sales (

    sale_id INT AUTO_INCREMENT PRIMARY KEY,

    sale_date DATE,

    product_name VARCHAR(255),

    sale_amount DECIMAL(10, 2)

)

PARTITION BY RANGE (YEAR(sale_date)) (

    PARTITION p0 VALUES LESS THAN (2022),

    PARTITION p1 VALUES LESS THAN (2023),

    PARTITION p2 VALUES LESS THAN (2024)

);

Now we hvae create three partition and we are going to run only p1 partition.

mysql> select * from sales partition(p1);

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)

);

 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')

);

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;

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;

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

);