Partitioning is a mechanism that allows you to split up individual logical tables into multiple separate locations on disk or even spread across multiple storage devices. Knowing what you are doing with partitioning and intelligently splitting your large table into smaller parts can lead to significant performance gains.
In MySQL, you specify partitions by a partition key. For example, you could partition your data based on the year the records were created. If you have 100 million rows covering ten years' worth of data, you could partition by year and end up with ten separate partitions, with approximately 10 million rows each.
Partitioning can lead to improved query performance in some cases. It also allows for improved data management and gives you the ability to spread data from a single table across multiple physical disks, potentially leading to performance improvements and cost savings.
Partitions can be defined when the table is created or afterward. You can tell MySQL how many partitions you need for a table and what information/column to use to decide which data should go into which partition. As a warning, when you partition a table, it will lock it. Thus, you should only partition out an existing table if you can afford the downtime incurred. To avoid this downtime, you can use special online schema change tools such as PlanetScale's no-downtime deploy requests, GitHub Online Schema Change (gh-ost), or Percona's pt-online-schema-change.
A RANGE-based partition function allows you to separate a given column's data-based ranges of numeric values.
A LIST-based partition function allows you to separate data based on predefined values you tell MySQL when partitioning a table.
A HASH-based partition function allows you to define a function/expression that will "hash" the input data. Then, MySQL will use this hashed value to determine how to distribute your data evenly into a set number of partitions.
A KEY-based partitioning function uses a built-in MySQL algorithm to distribute data evenly across several partitions.
Different schemes handle nulls in different ways. With RANGE partitioning, a null value gets mapped to the lowest ranged partition. For LIST partitioning, MySQL will only insert if null is defined in one of the listed partition values. KEY and HASH do the same thing in that they both treat a null as a zero.
Partitioning applies to the table data as well as the indexes for that table. That means the indexes don't span the entire table, just individual ones for the partition. Remember this if you have some queries that want to use indexes and span multiple partitions.
Some functions and operators cannot be used in the partition function expressions. Stored procedures, functions, loadable functions, or plugins cannot be used, as well as a few other MySQL features.
There are a few other miscellaneous limitations, such as not supporting full-text indexes, spatial data types not being used for the partition key, and temp and log tables needing support.