Partitioning in MySQL 8

In the previous chapter, replication in MySQL 8 was explained. This included detailed explanations of replication, configuration, and implementation. The chapter also explained group replication versus clustering, and covered the replication approach as a solution.

In this chapter, we will do partitioning in MySQL 8. Partitioning is the concept of managing and maintaining data with specific operations with several operators, and defining rules to control over partitioning. Basically, it provides a configuration hook for managing the underlying data files in a specified way.

We will cover the following topics on partitioning:

• Overview of partitioning
• Types of partitioning
• Partition management
• Partition selection
• Partition pruning
• Restriction and limitation in partitioning

Overview of partitioning

The concept of partitioning relates to the physical aspects of data storage in the database. If you look at the SQL standards, they do not give much information on the concept, and the SQL language itself intends to work independently of which media or data structure is used for storing information or data specific to different schemas, tables, rows, or columns. Advanced database management systems have added means of specifying the physical location used for data storage as hardware, the file system, or as both. In MySQL, the InnoDB storage engine provides support for these purposes with the notion of tablespace.

Partitioning enables us to distribute parts of individual tables to be stored as separate tables at different locations in the file system. Additionally, the distribution is accomplished with user specified rules provided, such as in the form...

Types of partitioning

In this section, you will understand different types of partitioning and also the purpose of using specific partitioning. The following is a list of the partitioning types that are available in MySQL 8 :

• RANGE partitioning
• LIST partitioning
• COLUMNS partitioning
• HASH partitioning
• KEY partitioning
• Subpartitioning

In addition to the above list, we will also see NULLhandling in MySQL 8 Partitioning in detailed section.

A very common use case for database partitioning is segregating data by date. MySQL 8 does not support date partitioning, which some database systems provide explicitly, but it is easy to create partitioning schemes with date, time, or datetime columns, or that are based on date/time related expressions that evaluate values from these column types.

You can use the date, time, or datetime types as column values for partition columns without any...

Partition management

There are plenty of ways to use SQL statements in order to modify partitioned tables—you can drop, add, merge, split, or redefine partitions with the ALTER TABLE statement. There are also ways to retrieve partitioned tables and partition information. We will see each of these in the following sections:

• RANGE and LIST partition management
• HASH and KEY partition management
• Partition maintenance
• Obtain partition information

RANGE and LIST partition management

Partition adding and dropping is handled in a similar way for the RANGE and LIST partition types. A table partitioned by RANGE or LIST partitioning can be dropped using the ALTER TABLE statement with the DROP PARTITION option available.

Make...

Partition selection and pruning

In this section, you will see how partitioning can optimize SQL statements clause execution with the optimizer known as partition pruning, and the use of SQL statements to effectively use partition data for selection and perform modification operations on the partitioning.

Partition pruning

Partition pruning is related to the optimization concept in partition. In partition pruning the concept described as Do not scan partitions where no possible matching values can be present is applied based on the query statements.

Suppose there is a partitioned table, tp1, created with the following statement:

CREATE TABLE tp1 (
    first_name VARCHAR (30) NOT NULL,
    last_name VARCHAR (30) NOT NULL,
    zone_code...

Restrictions and limitations in partitioning

In this section, you will see the restrictions and limitations in MySQL 8 partitioning, covering prohibited constructs, performance considerations, and limitation aspects related to storage engines and functions in detail, to gain optimum benefits from the table partitioning.

Partitioning keys, primary keys, and unique keys

The relationship between partitioning keys with primary keys and unique keys is very important for partition schema structure design. To say the rule in one line it will be that All the columns used in the partitioning in the partition table must include every unique key of the table. So every unique key, including the primary key column on the table, must be...

Summary

In this chapter, we learned about different types of partitioning and the need for partitions. We also covered detailed information on managing all types of partitions. We learned about partition pruning and selection of partitions which is used by the optimizer. We also discussed applicable limitations and restrictions to consider while using partitioning.

In the next chapter, you will learn how to do scaling in MySQL 8, and discover common challenges faced when providing scalability in MySQL 8. You will also learn how to make the MySQL server highly available and achieve high availability.