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:

CREATE TABLE userslogs (username VARCHAR(20) NOT NULL,logdata BLOB NOT NULL,created DATETIME NOT NULL,PRIMARY KEY(username, created))PARTITION BY RANGE( YEAR(created) )(PARTITION from_2013_or_less VALUES LESS THAN (2014),PARTITION from_2014 VALUES LESS THAN (2015),PARTITION from_2015 VALUES LESS THAN (2016),PARTITION from_2016_and_up VALUES LESS THAN MAXVALUE

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…

INSERT INTO rc1 (a,b) VALUES (4,11);INSERT INTO rc1 (a,b) VALUES (5,11);INSERT INTO rc1 (a,b) VALUES (6,11);INSERT INTO rc1 (a,b) VALUES (4,12);INSERT INTO rc1 (a,b) VALUES (5,12);INSERT INTO rc1 (a,b) VALUES (6,12);INSERT INTO rc1 (a,b) VALUES (4,13);INSERT INTO rc1 (a,b) VALUES (5,13);INSERT INTO rc1 (a,b) VALUES (6,13);

… would be placed in the following way:

a	b	partition	comparison
4	11	p0	(4,11) < (5,12) = true
4	12	p0	(4,12) < (5,12) = true
4	13	p0	(4,13) < (5,12) = true
5	11	p0	(5,11) < (5,12) = true
5	12	p3	(5,12) < (5,12) = false
5	13	p3	(5,13) < (5,12) = false
6	11	p3	(6,11) < (5,12) = false
6	12	p3	(6,12) < (5,12) = false
6	13	p3	(6,13) < (5,12) = false

You can verify this for yourself using this code snippet:

SELECT *,'p0' FROM rc1 PARTITION (p0) UNION ALL SELECT *,'p3' FROM rc1 PARTITION (p3) ORDER BY a,b ASC;

LIST Partitioning

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:

CREATE TABLE serverlogs (serverid INT NOT NULL, logdata BLOB NOT NULL,created DATETIME NOT NULL)PARTITION BY LIST (serverid)(PARTITION server_east VALUES IN(1,43,65,12,56,73),PARTITION server_west VALUES IN(534,6422,196,956,22));

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 INSERTerror.

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:

CREATE TABLE lc (a INT NULL,b INT NULL)PARTITION BY LIST COLUMNS(a,b) (PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ));

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:

CREATE TABLE serverlogs (servername VARCHAR(20) NOT NULL, logdata BLOB NOT NULL,created DATETIME NOT NULL)PARTITION BY LIST COLUMNS (servername)(PARTITION server_east VALUES IN('northern_east','east_ny'),PARTITION server_west VALUES IN('west_city','southern_ca'));

HASH Partitioning

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; HASHdoes this automatically, based on the expression or INT value of the selected column. Let’s see how it works:

CREATE TABLE serverlogs2 (serverid INT NOT NULL, logdata BLOB NOT NULL,created DATETIME NOT NULL)PARTITION BY HASH (serverid)PARTITIONS 10;

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:

CREATE TABLE serverlogs2 (serverid INT NOT NULL, logdata BLOB NOT NULL,created DATETIME NOT NULL)PARTITION BY LINEAR HASH (serverid)PARTITIONS 10;

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:

CREATE TABLE serverlogs4 (serverid INT NOT NULL, logdata BLOB NOT NULL,created DATETIME NOT NULL,UNIQUE KEY (serverid))PARTITION BY KEY()PARTITIONS 10;

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:

CREATE TABLE serverlogs5 (serverid INT NOT NULL, logdata BLOB NOT NULL,created DATETIME NOT NULL,label VARCHAR(10) NOT NULL)PARTITION BY KEY(serverid, label, created)PARTITIONS 10;

KEY comes with a LINEAR keyword option that has the exact same impact as the LINEARkeyword in HASH: the partitions are derived using a power-of-two algorithm. See how it works in the following example:

CREATE TABLE serverlogs6 (serverid INT NOT NULL, logdata BLOB NOT NULL,created DATETIME NOT NULL)PARTITION BY LINEAR KEY(serverid)PARTITIONS 10;

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:

CREATE TABLE samplelogs (logdata BLOB NOT NULL,created DATETIME NOT NULL)PARTITION BY RANGE COLUMNS (created)(PARTITION oldlogs VALUES LESS THAN ('2015-01-01'),PARTITION currentlogs VALUES LESS THAN (MAXVALUE));

If we perform this statement…

SELECT * FROM samplelogs WHERE created > '2016-01-01'

… 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:

EXPLAIN PARTITIONS SELECT * FROM samplelogs WHERE created > '2016-01-01'

The output shows that only the “currentlogs” partition is being used.

The optimizer automatically “prunes” only when the WHERE condition can be simplified as one of the following two cases:

• partition_column = constant
• partition_column IN (constant1, constant2, ..., constantN)

You can also explicitly select the partition like this:

SELECT * FROM samplelogs PARTITION (currentlogs) WHERE created > '2016-01-01';

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.

Millions of Records

We will use the following table:

CREATE TABLE `measures` (`measure_timestamp` datetime NOT NULL,`station_name` varchar(255) DEFAULT NULL,`wind_mtsperhour` int(11) NOT NULL,`windgust_mtsperhour` int(11) NOT NULL,`windangle` int(3) NOT NULL,`rain_mm` decimal(5,2),`temperature_dht11` int(5),`humidity_dht11` int(5),`barometric_pressure` decimal(10,2) NOT NULL,`barometric_temperature` decimal(10,0) NOT NULL,`lux` decimal(7,2),`is_plugged` tinyint(1),`battery_level` int(3),KEY `measure_timestamp` (`measure_timestamp`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Next, we’ll make a copy of the former, adding RANGE partitions:

CREATE TABLE `partitioned_measures` (`measure_timestamp` datetime NOT NULL,`station_name` varchar(255) DEFAULT NULL,`wind_mtsperhour` int(11) NOT NULL,`windgust_mtsperhour` int(11) NOT NULL,`windangle` int(3) NOT NULL,`rain_mm` decimal(5,2),`temperature_dht11` int(5),`humidity_dht11` int(5),`barometric_pressure` decimal(10,2) NOT NULL,`barometric_temperature` decimal(10,0) NOT NULL,`lux` decimal(7,2),`is_plugged` tinyint(1),`battery_level` int(3),KEY `measure_timestamp` (`measure_timestamp`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `vertabelo`.`partitioned_measures` PARTITION 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)) ;

You can download the sample data from HERE if you want to try it yourself.
EDIT MODEL IN YOUR BROWSER1.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_measures`PARTITION 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):

SELECT SQL_NO_CACHECOUNT(*)FROMvertabelo.measuresWHEREmeasure_timestamp >= '2016-01-01'AND DAYOFWEEK(measure_timestamp) = 1; SELECT SQL_NO_CACHECOUNT(*)FROMvertabelo.partitioned_measuresWHEREmeasure_timestamp >= '2016-01-01'AND DAYOFWEEK(measure_timestamp) = 1;

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:

ALTER TABLE `vertabelo`.`measures` DROP INDEX `measure_timestamp` ; ALTER TABLE `vertabelo`.`partitioned_measures` DROP INDEX `measure_timestamp` ;

Conclusion: Running the SELECT on the nonpartitioned table takes about twice the timeas 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_timestamp column:

ALTER TABLE `vertabelo`.`measures` ADD INDEX `index1` (`measure_timestamp` ASC);

ALTER TABLE `vertabelo`.`partitioned_measures` ADD INDEX `index1` (`measure_timestamp` ASC);

We have it as it originally was, with indexed data.

Now let’s do a big delete on the nonpartitioned table:

DELETEFROM vertabelo.measuresWHERE  measure_timestamp < '2015-01-01';

It takes about 0.7 seconds to delete 85,314 rows. What if I run a DROP PARTITION to achieve the same result in the partitioned table?

ALTER TABLE vertabelo.partitioned_measures DROP PARTITION to_delete_logs ;

It drops it in less than 0.06 seconds. Let’s try deleting all data older than 2016…

DELETEFROM vertabelo.measuresWHERE  measure_timestamp < '2016-01-01';

… and then dropping the equivalent data from the partitioned table:

ALTER TABLE vertabelo.partitioned_measures DROP PARTITION prev_year_logs ;

Look at the output for each:

DELETE FROM vertabelo.measures WHEREmeasure_timestamp < '2016-01-01'	988644 row(s) affected	56.125 sec
ALTER TABLE vertabelo.partitioned_measuresDROP PARTITION prev_year_logs	0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0	0.047 sec

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.

About Author