Partitioned Tables

Partitioning is a feature in MySQL that allows you to split large tables into smaller, more manageable pieces called partitions. Each partition is stored separately, but they can be accessed and manipulated as if they were a single table. This can help improve query performance and manageability of large datasets. In this article, we will discuss the benefits of partitioning, the different types of partitioning methods available in MySQL, and how to create partitioned tables in MySQL.

Range partitioning

Range partitioning is a type of table partitioning in MySQL where the data in the table is partitioned based on a specific range of values. The partitioning is done based on a specific column that contains values in a continuous range. Each partition can be defined with a minimum and maximum value, and any value within that range will be stored in that partition.

Suppose we have a table named sales that stores daily sales data for a store. We want to partition the table based on the date of the sale so that each partition contains sales data for a specific date range. We can do this using range partitioning as follows:

CREATE TABLE sales (
    id INT NOT NULL AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    item_name VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date))
(
    PARTITION p0 VALUES LESS THAN (2018),
    PARTITION p1 VALUES LESS THAN (2019),
    PARTITION p2 VALUES LESS THAN (2020),
    PARTITION p3 VALUES LESS THAN (2021),
    PARTITION p4 VALUES LESS THAN (2022),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);

In this example, we partition the sales table by the year of the sale date using the RANGE keyword. We define six partitions, each with a specific year range using the VALUES LESS THAN clause. The first five partitions correspond to the years 2018 to 2022, and the last partition includes all rows with a sale_date greater than or equal to 2023.

Each partition has a name starting with p, followed by a number. The PRIMARY KEY of the table includes both the id and sale_date columns, which ensures that each row has a unique combination of id and sale_date.

Now, when we insert rows into the sales table, MySQL automatically determines in which partition to place the row based on the year of the sale_date. For example, if we insert a row with a sale_date of '2021-06-01', it will be placed in partition p3. This can improve query performance by allowing MySQL to quickly identify which partition(s) to scan when executing queries that filter by sale_date.

List partitioning

List partitioning is a type of partitioning that divides a table into partitions based on discrete values specified in a column, known as the partitioning key. Each partition is associated with a set of values of the partitioning key. List partitioning is useful when the values of the partitioning key are not continuous, and the data needs to be split into non-overlapping partitions based on specific values.

Suppose we have a table named Products that stores information about various products. We want to partition the table based on the product category so that each partition contains product information for a specific category. We can do this using list partitioning as follows:

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  category VARCHAR(50) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (id, category)
)
PARTITION BY LIST (category) (
  PARTITION p0 VALUES IN ('Electronics'),
  PARTITION p1 VALUES IN ('Books', 'Magazines'),
  PARTITION p2 VALUES IN ('Clothing', 'Shoes'),
  PARTITION p3 VALUES IN ('Furniture'),
  PARTITION p4 VALUES IN ('Sports', 'Fitness'),
  PARTITION p5 VALUES IN ('Toys'),
  PARTITION p6 VALUES IN ('Other')
);

In this example, we create a table named products with columns id, name, category, and price. We partition the table using list partitioning based on the category column.

We define 7 partitions using the PARTITION keyword followed by the partition name and the VALUES IN clause specifying the category values that should belong to that partition.

For example, the first partition p0 is defined to hold all rows with a category value of 'Electronics'. The second partition p1 is defined to hold all rows with a category value of 'Books' or 'Magazines'. Similarly, the remaining partitions are defined based on the category values.

Hash partitioning

Hash partitioning is a type of table partitioning in MySQL that distributes data evenly among partitions based on a hash function. This method is useful when there is no clear partitioning key and the data needs to be evenly distributed across partitions.

Here's an example of creating a hash-partitioned table in MySQL:

CREATE TABLE users (
   id INT NOT NULL,
   name VARCHAR(50),
   email VARCHAR(50),
   PRIMARY KEY (id, email)
)
PARTITION BY HASH(id)
PARTITIONS 4;

In this example, we create a table named users that has three columns: id, name and email. The table is partitioned by the id column using the HASH function, which will evenly distribute data among the specified number of partitions (in this case, 4 partitions). The primary key for this table is a composite key consisting of both the id and email columns.

When a query is executed on this table, MySQL uses the hash function to determine which partition the data belongs to and retrieves the data from that partition. This can improve query performance as the data is distributed evenly across partitions, reducing the amount of data that needs to be scanned for a given query.

Key partitioning

Key partitioning is a type of table partitioning in MySQL that uses the values of one or more columns as the partition key. This means that rows with the same partition key values are stored in the same partition. Key partitioning is useful when you have a column with a high degree of data skew, which means that a few values occur much more frequently than others. By partitioning the table based on this column, you can distribute the data more evenly across partitions, improving query performance.

Here's an example of creating a key-partitioned table in MySQL:

CREATE TABLE user_data (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    data VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    KEY (user_id)
)
PARTITION BY KEY (user_id)
PARTITIONS 4;

In this example, we're creating a table called user_data that has an id column, a user_id column, and a data column. We're defining the user_id column as the partition key using the PARTITION BY KEY clause. Finally, we're specifying that we want to create 4 partitions using the PARTITIONS 4 clause. This will evenly distribute rows across the partitions based on their user_id values.

Benefits of partitioning

Partitioning can provide several benefits when dealing with large datasets. Some of the main advantages of partitioning include:

• Improved query performance: Partitioning can help improve query performance by reducing the amount of data that needs to be scanned. Queries that only require a subset of the data can be executed more quickly by scanning only the relevant partitions.

• Faster data loading and maintenance: Partitioning can also speed up data loading and maintenance tasks. Instead of loading or updating a large table, you can focus on smaller partitions, which can be more efficient.

• Better manageability: Partitioning can also improve the manageability of large datasets. For example, you can archive old data by moving it to a separate partition, making it easier to manage the remaining data.

Conclusion

In conclusion, partitioning is a useful feature in MySQL that allows users to manage large datasets efficiently. It splits large tables into smaller, more manageable partitions that can be accessed and manipulated as if they were a single table. In the topic, we studied several types of partitioning available in MySQL, including range, list, and hash partitioning:

• Range partitioning divides the table based on a specific range of values in a continuous column;

• List partitioning divides the table based on discrete values specified in a column;

• Hash partitioning distributes data evenly among partitions based on a hash function.

By understanding these partitioning methods and how to implement them in MySQL, we can significantly improve query performance and manageability of large datasets. So let's have some practice on what we've learnt so far!