MySQL Partitioning enables developers and DBAs to improve database performance and simplify the management of very large databases. MySQL supports horizontal partitioning, allowing the rows of a database to be divided into smaller data sets and then distributed across multiple directories and disks.
Partitioning delivers increased query performance, as smaller sets of data only need to be accessed for specific operations, rather than one large single table. It is also possible to stripe a partitioned table across different physical drives allowing physical I/O contention to be reduced when multiple partitions are accessed simultaneously.
Partitioning also simplifies data management. For example, a DBA can drop specific partitions in a partitioned table while the remaining partitions remain intact (as opposed to crafting a fragmentation-producing mass delete operation for the whole table). with no DBA intervention being necessary.
Multiple partitioning methods, enable DBAs to precisely control how the data is partitioned:
Each partition contains rows mapping to a specific set (range) of values. Range partitioning is useful when frequently running queries that depend directly on the column used for partitioning the table, or for quickly deleting old data by simply dropping a table.
List partitioning is similar to range partitioning. The main difference is that this partitioning mode allows data to be segmented based on a pre-defined list of values that are specified by the DBA, rather than a set of contiguous ranges of values.
Columns partitioning enables the use of multiple columns in partitioning keys. The multiple columns are used for both the placing of rows in partitions and for determining which partitions are checked for matching rows in partition pruning operations. Column partitioning is an extension of Range and List partitioning.
Partitioning by Hash is used primarily to ensure an even distribution of data among a pre-determined number of partitions. With range or list partitioning, you must specify explicitly into which partition a given column value is to be stored. With hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.
A variant of Hash partitioning is Linear Hash, which uses a more complex algorithm to partition data which can make adding, dropping, merging, and splitting of partitions much faster when dealing with multi-terabyte tables.
Partitioning by key is similar to partitioning by hash, where MySQL guarantees even distribution of data through a system-generated hash key rather than user-defined expressions used by hash partitioning. Key Partitioning allows a great range of column data types to be used for partitioning.
Sub-Partitioning enables the further division of each partition in a partitioned table and is designed to be used with especially large tables, enabling the distribution of data and indexes across many servers and disks.
By using the appropriate "WHERE" or "ON" clauses in a query, it is possible for the MySQL Optimizer to only access those partitions where matching values will exist. As a result, the execution of the query can be an order of magnitude faster than the same query against a non-partitioned table.