What is MySQL partitioning? What kind of partition types are there? How do you know if this is something your database engine supports? In this article, we tell you what you need to know about partitioning in MySQL.
MySQL partitioning is about altering – ideally, optimizing – the way the database engine physically stores data. It allows you to distribute portions of table data (a.k.a. partitions) across the file system based on a set of user-defined rules (a.k.a. the “partitioning function”). In this way, if the queries you perform access only a fraction of table data and the partitioning function is properly set, there will be less to scan and queries will be faster.
It is important to note that partitioning makes the most sense when dealing with large data sets. If you have fewer than a million rows or only thousands of records, partitioning will not make a difference.
Horizontal vs. Vertical Partitioning
Horizontal partitioning means that all rows matching the partitioning function will be assigned to different physical partitions. Vertical partitioning allows different table columns to be split into different physical partitions. Currently, MySQL supports horizontal partitioning but not vertical. The engine’s documentation clearly states it won’t support vertical partitions any time soon: ”There are no plans at this time to introduce vertical partitioning into MySQL.”
Vertical partitioning is about splitting up columns
Horizontal partitioning is about splitting up rows
Key Benefits of Partitioning
Some of the advantages of using partitions are:
Storage: It is possible to store more data in one table than can be held on a single disk or file system partition.
Deletion: Dropping a useless partition is almost instantaneous, but a classical DELETE query run in a very large table could take minutes.
Partition Pruning: This is the ability to exclude non-matching partitions and their data from a search; it makes querying faster. Also, MySQL 5.7 supports explicit partition selection in queries, which greatly increases the search speed. (Obviously, this only works if you know in advance which partitions you want to use.) This also applies for DELETE, INSERT, REPLACE, and UPDATE statements as well as LOAD DATA and LOAD XML.
How to Check If Your Engine Supports Partitioning
By default, community binaries include partitioning support. You can check if it’s being supported in your current instance by running the SHOW PLUGINS statement. The output should display something like the following row:
| Name | Status | Type | Library | License |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
Another alternative is to check INFORMATION_SCHEMA.PLUGINS:
| Name | Version | Status |
| partition | 1.0 | ACTIVE |
Both outputs note that the plugin you’re looking for is named "partition" and that it is set to ACTIVE. If you don’t see the partition listed by either of these methods, your MySQL version likely was installed without partitioning support. In this case, another MySQL compilation must be performed with the –DWITH_PARTITION_STORAGE_ENGINE option. The “INSTALLING MYSQL FROM SOURCE” documentation will show you how to do this.
Partitioning Basics
Before going further, it’s important to note some general considerations related to partitioning:
Optimized Datetime Functions: The TO_DAYS(), YEAR(), and TO_SECONDS() functions are optimized for use in partitions. You can also use date and time functions that return integer or NULL values, such as WEEKDAY(), DAYOFYEAR(), or MONTH().
Partition Identification: Partitions are always numbered sequentially, automatically starting from 0 when created. Rows are inserted using the partition numbers to identify where each row goes. For instance, if you partition a table into four, then MySQL will use the partition numbers 0, 1, 2, and 3 to identify each partition.
Naming Conventions: Partition names should follow the same MySQL naming conventions used for tables and databases. It’s worth noting that partition names are not case sensitive. If you try to name two partitions for the same table – i.e. “myVertabeloPart” and “myvertabelopart” – you would get this error message:
ERROR 1488 (HY000): Duplicate partition name myvertabelopart
Directory: Partitions can be assigned to a specific directory during creation. You can do this using the DATA DIRECTORY and INDEX DIRECTORY options for the PARTITION clause of the CREATE TABLE statement.
Engine: Partitions of the same table must use the same storage engine. If you partition a MyISAM table, all partitions will be MyISAM; the same applies to InnoDB.
Index Partitioning: Partitioning applies to all the data and indexes in a table; you cannot partition only the data and not the indexes, or vice versa. You also cannot partition only a portion of the table.
Foreign Keys: Partitioned InnoDB tables do not support foreign keys. The data integrity implications of this are very important. You cannot add a foreign key (pointing to another table) in the partitioned table. Conversely, if the table has a foreign key you will not be able to partition it. Also, a nonpartitioned table cannot have a foreign key column pointing to a partitioned table column.
Partition Columns: The rule of thumb here is that all columns used in the partitioning expression must be part of every unique key in the partitioned table. This apparently simple statement imposes certain important limitations, which we will discuss next.
Note: MySQL documentation explains partition restrictions and limitations HERE.
Partition Columns Explained
As stated earlier, all columns used in the partition expression must be present in every unique key in the table, including the primary key (which is UNIQUE by definition). In other words, all unique keys in the table must use all the columns in the partitioning expression.
… can’t be partitioned, as “created” is not part of every unique key in the table. In this case, the primary key does not include “created”, so the statement fails:
Error Code: 1503. A PRIMARY KEY must include all columns in the table's
partitioning function
We could rewrite the CREATE like this (depending on your use case):
If you create a table without unique keys (including primary keys), any column can be used in the partitioning expression that is compatible with the partitioning type.
Next, I’ll explain what kinds of partitions are available in MySQL as of version 5.7.
EDIT MODEL IN YOUR BROWSER
userslogsuserslogsidusernamelogdatacreatedintvarchar(20)blobdatetimePKNNNuserslogsusernamelogdatacreatedvarchar(20)blobdatetimePKNPKCan’t be partitionedas “created” is not partof the every unique keyin tableIn this way, the tablecan be partitioned
Partition Types
There are basically four partition types available: RANGE, LIST, HASH and KEY. I’ll explain each of these now. Later on we will see some practical examples of using RANGE, the partition type you will likely end up using most.
RANGE Partitioning
This type of partition assigns rows to partitions based on column values that fall within a stated range. The values should be contiguous, but they should not overlap each other. The VALUES LESS THAN operator will be used to define such ranges in order from lowest to highest (a requirement for this partition type). Also, the partition expression – in the following example, it is YEAR(created) – must yield an integer or NULL value.
As with the example above, we could partition the table like this:
Note the MAXVALUE expression I’ve used in this definition. This is not mandatory, but it works as a catch-all for logs equal to or greater than the max value (year 2016). If I didn’t do this, consider what would happen when there is an INSERT with a YEAR value of 2020. It would fail, as there wouldn’t be any partition definition for that value.
As time goes by and 2017 kicks in, you would probably (depending on your use case) find it useful to generate a new partition for this table. You do this by altering the partition definition to generate a new partition for rows from 2016 (PARTITION from_2015 VALUES LESS THAN (**2017**)) as well as records from 2017 and later (PARTITION from_**2017**_and_up VALUES LESS THAN MAXVALUE). You could use the same logic for other years.
In relation to NULL values, it’s important to note that such rows will fall in the lower partition. In the former example, a YEAR with a NULL would evaluate as LESS THAN (2014), therefore it would end up inside the partition from_2013_or_less.
RANGE partitioning works best for:
Deleting Old Data: In the above example, if logs from 2013 need to be deleted, you can simply use ALTER TABLE userslogs DROP PARTITION from_2013_or_less; to delete all rows. This process will take almost no time, whereas running DELETE FROM userslogs WHERE YEAR(created) <= 2013; could take minutes if there are lots of rows to delete.
Series Data: Working with a range of data expressions comes naturally when you’re dealing with date or time data (as in the example) or other types of “series” data.
Frequent Queries on the Partition Expression Column: If you frequently perform queries directly involving the column used in the partition expression (where the engine can determine which partition(s) it needs to scan based directly on the WHERE clause), RANGE is quite efficient. We will discuss this in greater detail in the Partition Pruning section.
An alternative to RANGE is RANGE COLUMNS, which allows the expression to include more than one column involving STRING, INT, DATE, and TIME type columns (but not functions). In such a case, the VALUES LESS THAN operator must include as many values as there are columns listed in the partition expression. For example:
CREATE` `TABLE` `rc1 (`` ``a ``INT``,`` ``b ``INT``)``PARTITION ``BY` `RANGE COLUMNS(a, b) (`` ``PARTITION p0 ``VALUES` `LESS THAN (5, 12),`` ``PARTITION p3 ``VALUES` `LESS THAN (MAXVALUE, MAXVALUE)``);
Values will be compared in tuples; for instance, these INSERTs...
LIST partitioning is similar to RANGE, except that the partition is selected based on columns matching one of a set of discrete values. In this case, the VALUES IN statement will be used to define matching criteria. Let’s see an example:
Note that in LIST partitioning, there is no catch-all (like the MAXVALUE expression in RANGE). You must cover all possible elements in the criteria list to prevent an INSERT error.
It’s worth noting that here NULL values are treated like any other value. In the above example, if serverid was NULLable and the expression evaluated to NULL for a given record, then that row would end up in the partition that accepts NULL as a possible value. Otherwise it would be rejected, as VALUES IN decides what value to put into which record.
LIST comes with an alternative – LIST COLUMNS. Like RANGE COLUMNS, this statement can include multiple columns and non-INT data types, such as STRING, DATE, and TIME. The general syntax would look like this:
Maintaining many columns with many values will usually get cumbersome, so you’ll most likely use LIST COLUMNS when you need to use many non-INT columns. This is shown in the following example:
In HASH partitioning, a partition is selected based on the value returned by a user-defined expression. This expression operates on column values in rows that will be inserted into the table. A HASH partition expression can consist of any valid MySQL expression that yields a nonnegative integer value. HASH is used mainly to evenly distribute data among the number of partitions the user has chosen.
For RANGE and LIST, one must define the partitions where the data will be stored; HASH does this automatically, based on the expression or INT value of the selected column. Let’s see how it works:
Note: If you don’t include the PARTITIONS keyword, the default number of partitions is “1”, and that’s how many you’ll get.
Here are some recommendations about using HASH partitions:
PARTITION BY HASH(expression) must return a non-constant, non-random integer value. (In other words, the returned value should be varying but deterministic.) The expressions TO_DAYS or YEAR are good to use with functions – e.g. based on the “created” column in our example.
The use of hashing expressions involving multiple columns is not particularly recommended; determining which of such expressions is suitable can be quite difficult and time-consuming.
A user-supplied expression is evaluated each time a record is inserted or updated. It may also be evaluated when records are deleted (depending on the circumstances).
MySQL determines which partition stores values by using the formula N = MOD(expr, num) where N is the resulting partition number, expr is the expression and num is the number of partitions defined in the PARTITIONS keyword. An alternative to this is LINEAR HASH.
LINEAR HASH
Instead of using the modulo described above, when MySQL uses LINEAR HASH a powers-of-two algorithm is employed to calculate the partition where the data is to be stored. Syntactically, LINEAR HASH is exactly the same as HASH, except for the addition of the word LINEAR:
It is advisable to use linear hashing when dealing with tables containing extremely large amounts (i.e. terabytes) of data. In such cases, adding, dropping, merging, and splitting partitions is much faster. However, this comes at a price: the distribution of data is not as even as with regular hashing. It is best to choose a number of partitions that is a power of 2 (2,4,8,16, etc.), otherwise the middle partitions will tend to be twice the size of the outer ones. This is due to the statistical distribution of the linear hash.
KEY Partitioning
This is very similar to HASH partitioning, but the hashing function is supplied by MySQL. A KEY partition can specify zero or many columns, which can contain non-integer values. An integer result will be returned regardless of the column data type. The following example will clarify this:
Here KEY() has been used without explicitly stating the partitioning column. MySQL will automatically use the primary key or a unique key as the partitioning column. If no unique keys are available, the statement will fail.
As stated before, we can define several columns as part of the KEY keyword. This is demonstrated in the following example:
KEY comes with a LINEAR keyword option that has the exact same impact as the LINEAR keyword in HASH: the partitions are derived using a power-of-two algorithm. See how it works in the following example:
... the optimizer will kick in and leave unnecessary partitions out of the search. Thus, the query is more efficient. You can see this by running an EXPLAIN statement:
When partition names are generated by MySQL (as in HASH and KEY partitions) the name pattern is p0, p1,..., pN-1 (where N is the number of partitions). Partition selection is supported for most operations like DELETE, UPDATE, INSERT, JOIN, etc.
A Typical Use Case: Time Series Data
Partitions are commonly used when dealing with a series of time data that contains a lot of records. These could be logs or records of phone calls, invoices, samples, etc. Most of the time, you’ll be reading fresh data, so there will be lots of old rows that need deleting once they get “stale”.
To illustrate how to deal with this problem, I’ll present a user case scenario using 1.8 million records from a weather station network. I’ll perform some common operations on the “measures” table as-is; I will also partition a copy of this table by RANGE and see if there is a difference.
You can download the sample data from HERE if you want to try it yourself.
EDIT MODEL IN YOUR BROWSER
1.8M rows examplemeasuresmeasure_timestampstation_namewind_mtsperhourwindgust_mtsperhourwindanglerain_mmtemperature_dht11humidity_dht11barometric_pressurebarometric_temperatureluxis_pluggedbattery_leveldatetimevarchar(255)intintint(3)decimal(5,2)int(5)int(5)decimal(10,2)decimal(10,0)decimal(7,2)tinyintint(3)NNNNNpartitioned_measuresmeasure_timestampstation_namewind_mtsperhourwindgust_mtsperhourwindanglerain_mmtemperature_dht11humidity_dht11barometric_pressurebarometric_temperatureluxis_pluggedbattery_leveldatetimevarchar(255)intintint(3)decimal(5,2)int(5)int(5)decimal(10,2)decimal(10,0)decimal(7,2)tinyintint(3)NNNNNALTER TABLE partitioned_measuresPARTITION BY RANGE (YEAR(measure_timestamp)) (PARTITION to_delete_logs VALUES LESS THAN (2015),PARTITION prev_year_logs VALUES LESS THAN (2016),PARTITION current_logs VALUES LESS THAN (MAXVALUE))
The SELECT Queries Benchmark
Let’s run a pair of common queries on both tables and see how they compare. (I will use the SQL- NO_CACHE keyword so we can see the impact of the query without engine caching):
It takes around half a second on my local machine to return the count of 112,153 rows for both queries (partitioned and nonpartitioned). Notice that both tables have an index on measure_timestamp; we’d expect this index to have a huge impact on the queries’ efficiency.
If I remove the indexes in both tables, I can see any impact the partitioning has on the SELECT:
As you can see the process of deleting data is way faster when you have partitions.
Conclusion
From personal experience, partitioning is the last part of any optimization process I’d perform. I’d do it only after exhausting other alternatives, like reworking slow queries. In general, partitioning makes the most sense when you’re dealing with millions of records. In this case, I have found RANGE to be the most useful. The best-fitting use case for RANGE is massively deleting old time-series data.
Before you set up any partitions, remember that they impose several limitations – the way that unique and primary keys are generated; the fact that foreign keys are not allowed; the lack of support for full-text indexes, etc.
What have you found from working with partitions in MySQL? Please share your thoughts, comments, and questions below.
What is MySQL partitioning? What kind of partition types are there? How do you know if this is something your database engine supports? In this article, we tell you what you need to know about partitioning in MySQL.
MySQL partitioning is about altering – ideally, optimizing – the way the database engine physically stores data. It allows you to distribute portions of table data (a.k.a. partitions) across the file system based on a set of user-defined rules (a.k.a. the “partitioning function”). In this way, if the queries you perform access only a fraction of table data and the partitioning function is properly set, there will be less to scan and queries will be faster.
It is important to note that partitioning makes the most sense when dealing with large data sets. If you have fewer than a million rows or only thousands of records, partitioning will not make a difference.
Horizontal vs. Vertical Partitioning
Horizontal partitioning means that all rows matching the partitioning function will be assigned to different physical partitions. Vertical partitioning allows different table columns to be split into different physical partitions. Currently, MySQL supports horizontal partitioning but not vertical. The engine’s documentation clearly states it won’t support vertical partitions any time soon: ”There are no plans at this time to introduce vertical partitioning into MySQL.”
Vertical partitioning is about splitting up columns
Horizontal partitioning is about splitting up rows
Key Benefits of Partitioning
Some of the advantages of using partitions are:
DELETEquery run in a very large table could take minutes.DELETE,INSERT,REPLACE, andUPDATEstatements as well asLOAD DATAandLOAD XML.How to Check If Your Engine Supports Partitioning
By default, community binaries include partitioning support. You can check if it’s being supported in your current instance by running the
SHOW PLUGINSstatement. The output should display something like the following row:Another alternative is to check
INFORMATION_SCHEMA.PLUGINS:You should get something like this:
Both outputs note that the plugin you’re looking for is named "partition" and that it is set to ACTIVE. If you don’t see the partition listed by either of these methods, your MySQL version likely was installed without partitioning support. In this case, another MySQL compilation must be performed with the –
DWITH_PARTITION_STORAGE_ENGINEoption. The “INSTALLING MYSQL FROM SOURCE” documentation will show you how to do this.Partitioning Basics
Before going further, it’s important to note some general considerations related to partitioning:
Optimized Datetime Functions: The TO_DAYS(), YEAR(), and TO_SECONDS() functions are optimized for use in partitions. You can also use date and time functions that return integer or NULL values, such as WEEKDAY(), DAYOFYEAR(), or MONTH().
Partition Identification: Partitions are always numbered sequentially, automatically starting from 0 when created. Rows are inserted using the partition numbers to identify where each row goes. For instance, if you partition a table into four, then MySQL will use the partition numbers 0, 1, 2, and 3 to identify each partition.
Naming Conventions: Partition names should follow the same MySQL naming conventions used for tables and databases. It’s worth noting that partition names are not case sensitive. If you try to name two partitions for the same table – i.e. “myVertabeloPart” and “myvertabelopart” – you would get this error message:
Directory: Partitions can be assigned to a specific directory during creation. You can do this using the
DATA DIRECTORYandINDEX DIRECTORYoptions for thePARTITIONclause of the CREATE TABLE statement.Engine: Partitions of the same table must use the same storage engine. If you partition a MyISAM table, all partitions will be MyISAM; the same applies to InnoDB.
Index Partitioning: Partitioning applies to all the data and indexes in a table; you cannot partition only the data and not the indexes, or vice versa. You also cannot partition only a portion of the table.
Foreign Keys: Partitioned InnoDB tables do not support foreign keys. The data integrity implications of this are very important. You cannot add a foreign key (pointing to another table) in the partitioned table. Conversely, if the table has a foreign key you will not be able to partition it. Also, a nonpartitioned table cannot have a foreign key column pointing to a partitioned table column.
Partition Columns: The rule of thumb here is that all columns used in the partitioning expression must be part of every unique key in the partitioned table. This apparently simple statement imposes certain important limitations, which we will discuss next.
Note: MySQL documentation explains partition restrictions and limitations HERE.
Partition Columns Explained
As stated earlier, all columns used in the partition expression must be present in every unique key in the table, including the primary key (which is UNIQUE by definition). In other words, all unique keys in the table must use all the columns in the partitioning expression.
For instance, this …
… can’t be partitioned, as “created” is not part of every unique key in the table. In this case, the primary key does not include “created”, so the statement fails:
We could rewrite the
CREATElike this (depending on your use case):If you create a table without unique keys (including primary keys), any column can be used in the partitioning expression that is compatible with the partitioning type.
Next, I’ll explain what kinds of partitions are available in MySQL as of version 5.7.
EDIT MODEL IN YOUR BROWSER
userslogsuserslogsidusernamelogdatacreatedintvarchar(20)blobdatetimePKNNNuserslogsusernamelogdatacreatedvarchar(20)blobdatetimePKNPKCan’t be partitionedas “created” is not partof the every unique keyin tableIn this way, the tablecan be partitioned
Partition Types
There are basically four partition types available:
RANGE,LIST,HASHandKEY. I’ll explain each of these now. Later on we will see some practical examples of using RANGE, the partition type you will likely end up using most.RANGE Partitioning
This type of partition assigns rows to partitions based on column values that fall within a stated range. The values should be contiguous, but they should not overlap each other. The
VALUES LESS THANoperator will be used to define such ranges in order from lowest to highest (a requirement for this partition type). Also, the partition expression – in the following example, it isYEAR(created)– must yield an integer or NULL value.As with the example above, we could partition the table like this:
Note the
MAXVALUEexpression I’ve used in this definition. This is not mandatory, but it works as a catch-all for logs equal to or greater than the max value (year 2016). If I didn’t do this, consider what would happen when there is anINSERTwith aYEARvalue of 2020. It would fail, as there wouldn’t be any partition definition for that value.As time goes by and 2017 kicks in, you would probably (depending on your use case) find it useful to generate a new partition for this table. You do this by altering the partition definition to generate a new partition for rows from 2016 (
PARTITION from_2015 VALUES LESS THAN (**2017**)) as well as records from 2017 and later (PARTITION from_**2017**_and_up VALUES LESS THAN MAXVALUE). You could use the same logic for other years.In relation to NULL values, it’s important to note that such rows will fall in the lower partition. In the former example, a YEAR with a NULL would evaluate as
LESS THAN (2014), therefore it would end up inside thepartition from_2013_or_less.RANGEpartitioning works best for:ALTER TABLE userslogs DROP PARTITION from_2013_or_less;to delete all rows. This process will take almost no time, whereas runningDELETE FROM userslogs WHERE YEAR(created) <= 2013;could take minutes if there are lots of rows to delete.WHEREclause),RANGEis quite efficient. We will discuss this in greater detail in the Partition Pruning section.An alternative to
RANGEisRANGE COLUMNS, which allows the expression to include more than one column involving STRING, INT, DATE, and TIME type columns (but not functions). In such a case, theVALUES LESS THANoperator must include as many values as there are columns listed in the partition expression. For example:Values will be compared in tuples; for instance, these
INSERTs...… would be placed in the following way:
You can verify this for yourself using this code snippet:
LIST Partitioning
LISTpartitioning is similar toRANGE, except that the partition is selected based on columns matching one of a set of discrete values. In this case, theVALUES INstatement will be used to define matching criteria. Let’s see an example:Note that in
LISTpartitioning, there is no catch-all (like theMAXVALUEexpression inRANGE). You must cover all possible elements in the criteria list to prevent anINSERTerror.It’s worth noting that here NULL values are treated like any other value. In the above example, if
serveridwas NULLable and the expression evaluated to NULL for a given record, then that row would end up in the partition that accepts NULL as a possible value. Otherwise it would be rejected, asVALUES INdecides what value to put into which record.LISTcomes with an alternative –LIST COLUMNS. LikeRANGE COLUMNS, this statement can include multiple columns and non-INT data types, such as STRING, DATE, and TIME. The general syntax would look like this:Maintaining many columns with many values will usually get cumbersome, so you’ll most likely use
LIST COLUMNSwhen you need to use many non-INT columns. This is shown in the following example:HASH Partitioning
In
HASHpartitioning, a partition is selected based on the value returned by a user-defined expression. This expression operates on column values in rows that will be inserted into the table. AHASHpartition expression can consist of any valid MySQL expression that yields a nonnegative integer value.HASHis used mainly to evenly distribute data among the number of partitions the user has chosen.For
RANGEandLIST, one must define the partitions where the data will be stored;HASHdoes this automatically, based on the expression or INT value of the selected column. Let’s see how it works:Note: If you don’t include the PARTITIONS keyword, the default number of partitions is “1”, and that’s how many you’ll get.
Here are some recommendations about using
HASHpartitions:PARTITION BY HASH(expression) must return a non-constant, non-random integer value. (In other words, the returned value should be varying but deterministic.) The expressionsTO_DAYSorYEARare good to use with functions – e.g. based on the “created” column in our example.PARTITIONSkeyword. An alternative to this isLINEAR HASH.LINEAR HASH
Instead of using the modulo described above, when MySQL uses
LINEAR HASHa powers-of-two algorithm is employed to calculate the partition where the data is to be stored. Syntactically,LINEAR HASHis exactly the same asHASH, except for the addition of the wordLINEAR:It is advisable to use linear hashing when dealing with tables containing extremely large amounts (i.e. terabytes) of data. In such cases, adding, dropping, merging, and splitting partitions is much faster. However, this comes at a price: the distribution of data is not as even as with regular hashing. It is best to choose a number of partitions that is a power of 2 (2,4,8,16, etc.), otherwise the middle partitions will tend to be twice the size of the outer ones. This is due to the statistical distribution of the linear hash.
KEY Partitioning
This is very similar to HASH partitioning, but the hashing function is supplied by MySQL. A
KEYpartition can specify zero or many columns, which can contain non-integer values. An integer result will be returned regardless of the column data type. The following example will clarify this:Here
KEY()has been used without explicitly stating the partitioning column. MySQL will automatically use the primary key or a unique key as the partitioning column. If no unique keys are available, the statement will fail.As stated before, we can define several columns as part of the KEY keyword. This is demonstrated in the following example:
KEYcomes with aLINEARkeyword option that has the exact same impact as theLINEARkeyword inHASH: the partitions are derived using a power-of-two algorithm. See how it works in the following example:Partition Pruning
The concept behind partition pruning is very simple: don’t search partitions where there is nothing to be found. Take the following example:
If we perform this statement…
... the optimizer will kick in and leave unnecessary partitions out of the search. Thus, the query is more efficient. You can see this by running an
EXPLAINstatement:The output shows that only the
“currentlogs”partition is being used.The optimizer automatically “prunes” only when the
WHEREcondition can be simplified as one of the following two cases:partition_column = constantpartition_column IN (constant1, constant2, ..., constantN)You can also explicitly select the partition like this:
When partition names are generated by MySQL (as in
HASHandKEYpartitions) the name pattern is p0, p1,..., pN-1 (where N is the number of partitions). Partition selection is supported for most operations likeDELETE,UPDATE,INSERT,JOIN, etc.A Typical Use Case: Time Series Data
Partitions are commonly used when dealing with a series of time data that contains a lot of records. These could be logs or records of phone calls, invoices, samples, etc. Most of the time, you’ll be reading fresh data, so there will be lots of old rows that need deleting once they get “stale”.
To illustrate how to deal with this problem, I’ll present a user case scenario using 1.8 million records from a weather station network. I’ll perform some common operations on the “measures” table as-is; I will also partition a copy of this table by RANGE and see if there is a difference.
Millions of Records
We will use the following table:
Next, we’ll make a copy of the former, adding
RANGEpartitions:You can download the sample data from HERE if you want to try it yourself.
EDIT MODEL IN YOUR BROWSER
1.8M rows examplemeasuresmeasure_timestampstation_namewind_mtsperhourwindgust_mtsperhourwindanglerain_mmtemperature_dht11humidity_dht11barometric_pressurebarometric_temperatureluxis_pluggedbattery_leveldatetimevarchar(255)intintint(3)decimal(5,2)int(5)int(5)decimal(10,2)decimal(10,0)decimal(7,2)tinyintint(3)NNNNNpartitioned_measuresmeasure_timestampstation_namewind_mtsperhourwindgust_mtsperhourwindanglerain_mmtemperature_dht11humidity_dht11barometric_pressurebarometric_temperatureluxis_pluggedbattery_leveldatetimevarchar(255)intintint(3)decimal(5,2)int(5)int(5)decimal(10,2)decimal(10,0)decimal(7,2)tinyintint(3)NNNNNALTER TABLE
partitioned_measuresPARTITION BY RANGE (YEAR(measure_timestamp)) (PARTITION to_delete_logs VALUES LESS THAN (2015),PARTITION prev_year_logs VALUES LESS THAN (2016),PARTITION current_logs VALUES LESS THAN (MAXVALUE))The SELECT Queries Benchmark
Let’s run a pair of common queries on both tables and see how they compare. (I will use the
SQL- NO_CACHEkeyword so we can see the impact of the query without engine caching):It takes around half a second on my local machine to return the count of 112,153 rows for both queries (partitioned and nonpartitioned). Notice that both tables have an index on
measure_timestamp; we’d expect this index to have a huge impact on the queries’ efficiency.If I remove the indexes in both tables, I can see any impact the partitioning has on the
SELECT:Conclusion: Running the SELECT on the nonpartitioned table takes about twice the time as the same query on the partitioned table.
The Big Delete Benchmark
I will try a massive deletion of old data and see how long it takes. Before I do that, let’s add an index back on the
measure_timestampcolumn:We have it as it originally was, with indexed data.
Now let’s do a big delete on the nonpartitioned table:
It takes about 0.7 seconds to delete 85,314 rows. What if I run a
DROP PARTITIONto achieve the same result in the partitioned table?It drops it in less than 0.06 seconds. Let’s try deleting all data older than 2016...
… and then dropping the equivalent data from the partitioned table:
Look at the output for each:
DELETEFROMvertabelo.measures ``WHERE``measure_timestamp < ``'2016-01-01'ALTERTABLEvertabelo.partitioned_measures``DROPPARTITION prev_year_logsAs you can see the process of deleting data is way faster when you have partitions.
Conclusion
From personal experience, partitioning is the last part of any optimization process I’d perform. I’d do it only after exhausting other alternatives, like reworking slow queries. In general, partitioning makes the most sense when you’re dealing with millions of records. In this case, I have found
RANGEto be the most useful. The best-fitting use case forRANGEis massively deleting old time-series data.Before you set up any partitions, remember that they impose several limitations – the way that unique and primary keys are generated; the fact that foreign keys are not allowed; the lack of support for full-text indexes, etc.
What have you found from working with partitions in MySQL? Please share your thoughts, comments, and questions below.