Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Understand how the optimizer automatically prunes irrelevant partitions and how to explicitly select partitions in your queries for efficiency.
When a WHERE clause is related to the partitioning expression, the optimizer knows which partitions are relevant for the query. Other partitions will not be read. This optimization is called partition pruning.
EXPLAIN PARTITIONS can be used to know which partitions are read for a given query. A column called partitions will contain a comma-separated list of the accessed partitions. For example:
EXPLAIN PARTITIONS SELECT * FROM orders WHERE id < 15000000;
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | orders | p0,p1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+------+-------------+--------+------------+-------+---------------+---------+---------+------+------+-------------+Sometimes the WHERE clause does not contain the necessary information to use partition pruning, or the optimizer cannot infer this information. However, we may know which partitions are relevant for the query. We can force MariaDB to only access the specified partitions by adding a PARTITION clause. This feature is called partition selection. For example:
The PARTITION clause is supported for all DML statements:
In general, partition pruning is applied to statements contained in .
However, note that if a BEFORE INSERT or BEFORE UPDATE trigger is defined on a table, MariaDB doesn't know in advance if the columns used in the partitioning expression are changed. For this reason, it is forced to lock all partitions.
This page is licensed: CC BY-SA / Gnu FDL
SELECT * FROM orders PARTITION (p3) WHERE user_id = 50;
SELECT * FROM orders PARTITION (p2,p3) WHERE user_id >= 40;Optimize large tables in MariaDB Server with partitioning. Learn how to divide tables into smaller, manageable parts for improved performance, easier maintenance, and scalability.
Learn about LINEAR KEY partitioning, which combines the internal key hashing with a linear algorithm for efficient partition handling.
LINEAR PARTITION BY KEY ([column_names])
[PARTITIONS (number_of_partitions)]LINEAR KEY partitioning is a form of , similar to .
LINEAR KEY partitioning makes use of a powers-of-two algorithm, while KEY partitioning uses modulo arithmetic to determine the partition number.
Adding, dropping, merging and splitting partitions is much faster than with the ; however, data is less likely to be evenly distributed over the partitions.
This page is licensed: CC BY-SA / Gnu FDL
Understand how to retrieve metadata about partitions using the INFORMATION_SCHEMA.PARTITIONS table to monitor row counts and storage usage.
The PARTITIONS table in the INFORMATION_SCHEMA database contains information about partitions.
The SHOW TABLE STATUS statement contains a Create_options column, that contains the string 'partitioned' for partitioned tables.
The SHOW CREATE TABLE statement returns the CREATE TABLE statement that can be used to re-create a table, including the partitions definition.
This page is licensed: CC BY-SA / Gnu FDL
CREATE OR REPLACE TABLE t1 (v1 INT)
PARTITION BY LINEAR KEY (v1)
PARTITIONS 2;Understand LIST partitioning, where rows are assigned to partitions based on whether a column value matches one in a defined list of values.
LIST partitioning is conceptually similar to RANGE partitioning. In both cases you decide a partitioning expression (a column, or a slightly more complex calculation) and use it to determine which partitions will contain each row. However, with the RANGE type, partitioning is done by assigning a range of values to each partition. With the LIST type, we assign a set of values to each partition. This is usually preferred if the partitioning expression can return a limited set of values.
A variant of this partitioning method, LIST COLUMNS, allows us to use multiple columns and more datatypes.
The last part of a statement can be the definition of the new table's partitions. In the case of LIST partitioning, the syntax is as follows:
PARTITION BY LIST indicates that the partitioning type is LIST.
The partitioning_expression is an SQL expression that returns a value from each row. In the simplest cases, it is a column name. This value is used to determine which partition should contain a row.
partition_name is the name of a partition.
value_list is a list of values. If partitioning_expression returns one of these values, the row are stored in this partition. If we try to insert something that does not belong to any of these value lists, the row are rejected with an error.
The DEFAULT partition catches all records which do not fit into other partitions.
LIST partitioning can be useful when we have a column that can only contain a limited set of values. Even in that case, RANGE partitioning could be used instead; but LIST partitioning allows us to equally distribute the rows by assigning a proper set of values to each partition.
This page is licensed: CC BY-SA / Gnu FDL
This page outlines constraints when using partitioning, such as the maximum number of partitions and restrictions on foreign keys and query cache usage.
The following limitations apply to partitioning in MariaDB:
Each table can contain a maximum of 8192 partitions.
Queries are never parallelized, even when they involve multiple partitions.
A table can only be partitioned if the storage engine supports partitioning.
Explore different partitioning types for MariaDB Server tables. Understand range, list, hash, and key partitioning to optimize data management and improve query performance.
It requires a column value, or an expression based on a column value, which is hashed, as well as the number of partitions into which to divide the table.
partitioning_expression needs to return a non-constant, deterministic integer. It is evaluated for each insert and update, so overly complex expressions can lead to performance issues. A hashing function operating on a single column, and where the value changes consistently with the column value, allows for easy pruning on ranges of partitions, and is usually a better choice. For this reason, using multiple columns in a hashing expression is not usually recommended.
number_of_partitions is a positive integer specifying the number of partitions into which to divide the table. If the PARTITIONS clause is omitted, the default number of partitions is one.
To determine which partition to use, perform the following calculation:
For example, if the expression is TO_DAYS(datetime_column) and the number of partitions is 5, inserting a datetime value of '2023-11-15' would determine the partition as follows:
TO_DAYS('2023-11-15') gives a value of 739204.
MOD(739204,5) returns 4, so the 4th partition is used.
HASH partitioning makes use of the modulus of the hashing function's value. The LINEAR HASH partitioning type is similar, using a powers-of-two algorithm. Data is more likely to be evenly distributed over the partitions than with the LINEAR HASH partitioning type; however, adding, dropping, merging and splitting partitions is much slower.
Using the Information Schema PARTITIONS Table for more information:
Partition Maintenance for suggestions on using partitions
This page is licensed: CC BY-SA / Gnu FDL
LINEAR HASH partitioning makes use of a powers-of-two algorithm, while HASH partitioning uses the modulus of the hashing function's value. Adding, dropping, merging and splitting partitions is much faster than with the HASH partitioning type, however, data is less likely to be evenly distributed over the partitions.
This page is licensed: CC BY-SA / Gnu FDL
PARTITION BY LINEAR HASH (partitioning_expression)
[PARTITIONS(number_of_partitions)]PARTITION BY LIST (partitioning_expression)
(
PARTITION partition_name VALUES IN (value_list),
[ PARTITION partition_name VALUES IN (value_list), ... ]
[ PARTITION partition_name DEFAULT ]
)CREATE OR REPLACE TABLE t1 (
num TINYINT(1) NOT NULL
)
ENGINE = InnoDB
PARTITION BY LIST (num) (
PARTITION p0 VALUES IN (0,1),
PARTITION p1 VALUES IN (2,3),
PARTITION p2 DEFAULT
);PARTITION BY HASH (partitioning_expression)
[PARTITIONS(number_of_partitions)]MOD(partitioning_expression, number_of_partitions)CREATE OR REPLACE TABLE t1 (c1 INT, c2 DATETIME)
PARTITION BY HASH(TO_DAYS(c2))
PARTITIONS 5;INSERT INTO t1 VALUES (1,'2023-11-15');
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 0 |
| p4 | 1 |
+----------------+------------+CREATE OR REPLACE TABLE t1 (c1 INT, c2 DATETIME)
PARTITION BY LINEAR HASH(TO_DAYS(c2))
PARTITIONS 5;All partitions must use the same storage engine. For a workaround, see Using CONNECT - Partitioning and Sharding.
A partitioned table cannot contain, or be referenced by, foreign keys.
The query cache is not aware of partitioning and partition pruning. Modifying a partition will invalidate the entries related to the whole table.
Updates can run more slowly when binlog_format=ROW and a partitioned table is updated than an equivalent update of a non-partitioned table.
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
In versions prior to , it is not possible to create partitions on tables that contain GEOMETRY types.
INFORMATION_SCHEMA.PARTITIONS contains information about existing partitions.
Partition Maintenance for suggestions on using partitions
This page is licensed: CC BY-SA / Gnu FDL
The RANGE partitioning type assigns rows to partitions based on whether column values fall within contiguous, non-overlapping ranges.
The RANGE partitioning type is used to assign each partition a range of values generated by the partitioning expression. Ranges must be ordered, contiguous and non-overlapping. The minimum value is always included in the first range. The highest value may or may not be included in the last range.
A variant of this partitioning method, RANGE COLUMNS, allows us to use multiple columns and more datatypes.
The last part of a CREATE TABLE statement can be definition of the new table's partitions. In the case of RANGE partitioning, the syntax is the following:
PARTITION BY RANGE indicates that the partitioning type is RANGE.
partitioning_expression is an SQL expression that returns a value from each row. In the simplest cases, it is a column name. This value is used to determine which partition should contain a row.
partition_name is the name of a partition.
value indicates the upper bound for that partition. The values must be ascending. For the first partition, the lower limit is NULL
As a catchall, MAXVALUE can be specified as a value for the last partition. Note, however, that in order to append a new partition, it is not possible to use ; instead, must be used.
A typical use case is when we want to partition a table whose rows refer to a moment or period in time; for example commercial transactions, blog posts, or events of some kind. We can partition the table by year, to keep all recent data in one partition and distribute historical data in big partitions that are stored on slower disks. Or, if our queries always read rows which refer to the same month or week, we can partition the table by month or year week (in this case, historical data and recent data are stored together).
values also represent a chronological order. So, these values can be used to store old data in separate partitions. However, partitioning by id is not the best choice if we usually query a table by date.
Partitioning a log table by year:
Partitioning the table by both year and month:
In the last example, the function is used to accomplish the purpose. Also, the first two partitions cover longer periods of time (probably because the logged activities were less intensive).
In both cases, when our tables become huge and we don't need to store all historical data any more, we can drop the oldest partitions in this way:
We will still be able to drop a partition that does not contain the oldest data, but all rows stored in it will disappear.
Example of an error when inserting outside a defined partition range:
To avoid the error, use the IGNORE keyword:
An alternative definition with MAXVALUE as a catchall:
This page is licensed: CC BY-SA / Gnu FDL
An introduction to the various partitioning strategies available in MariaDB, helping you choose the right method for your data distribution needs.
A partitioning type determines how a partitioned table's rows are distributed across partitions. Some partition types require the user to specify a partitioning expression that determines in which partition a row are stored.
The size of individual partitions depends on the partitioning type. Read and write performance are affected by the partitioning expression. Therefore, these choices should be made carefully.
MariaDB supports the following partitioning types:
This page is licensed: CC BY-SA / Gnu FDL
Learn how MariaDB stores partitioned tables on the filesystem, typically creating separate .ibd files for each partition when using InnoDB.
A partitioned table is stored in multiple files. By default, these files are stored in the MariaDB (or InnoDB) data directory. It is possible to keep them in different paths by specifying table options. This is useful to store different partitions on different devices.
Note that, if the server system variable is set to 0 at the time of the table creation, all partitions are stored in the system tablespace.
The following files exist for each partitioned tables:
KEY takes an optional list of column_names, and the hashing function is given by the server.Just like HASH partitioning, in KEY partitioning the server takes care of the partition and ensures an even distribution among the partitions. However, the largest difference is that KEY partitioning makes use of column_names, and cannot accept a partitioning_expression which is based on column_names, in contrast to HASH partitioning, which can.
If no column_names are specified, the table's primary key is used if present, or not null unique key if no primary key is present. If neither of these keys are present, not specifying any column_names will result in an error:
Unlike other partitioning types, columns used for partitioning by KEY are not limited to integer or NULL values.
KEY partitions do not support column index prefixes. Any columns in the partitioning key that make use of column prefixes are not used.
The unique key must be NOT NULL:
KEY requires column_values if no primary key or not null unique key is present:
Primary key columns with index prefixes are silently ignored, so the following two queries are equivalent:
a(5) and c(5) are silently ignored in the former.
If all columns use index prefixes, the statement fails with a slightly misleading error:
This page is licensed: CC BY-SA / Gnu FDL
table_name#P#partition_name.ext
Normal files created by the storage engine use this pattern for names. The extension depends on the storage engine.
For example, an InnoDB table with 4 partitions will have the following files:
If we convert the table to MyISAM, we will have these files:
This page is licensed: CC BY-SA / Gnu FDL
table_name.frm
Contains the table definition. Non-partitioned tables have this file, too.
table_name.par
Contains the partitions definitions.
orders.frm
orders.par
orders#P#p0.ibd
orders#P#p1.ibd
orders#P#p2.ibd
orders#P#p3.ibdorders.frm
orders.par
orders#P#p0.MYD
orders#P#p0.MYI
orders#P#p1.MYD
orders#P#p1.MYI
orders#P#p2.MYD
orders#P#p2.MYI
orders#P#p3.MYD
orders#P#p3.MYIPARTITION BY RANGE (partitioning_expression)
(
PARTITION partition_name VALUES LESS THAN (value),
[ PARTITION partition_name VALUES LESS THAN (value), ... ]
[ PARTITION partition_name VALUES LESS THAN MAXVALUE ]
)CREATE TABLE log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
dt DATETIME NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, dt)
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);CREATE TABLE log2
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ts TIMESTAMP NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, ts)
)
ENGINE = InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(ts))
(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2014-08-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2014-11-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2015-02-01 00:00:00'))
);ALTER TABLE log DROP PARTITION p0;INSERT INTO log(id,dt) VALUES
(1, '2016-01-01 01:01:01'),
(2, '2015-01-01 01:01:01');
ERROR 1526 (HY000): Table has no partition for value 2016INSERT IGNORE INTO log(id,dt) VALUES
(1, '2016-01-01 01:01:01'),
(2, '2015-01-01 01:01:01');
SELECT * FROM log;
+----+---------------------+------+
| id | timestamp | user |
+----+---------------------+------+
| 2 | 2015-01-01 01:01:01 | NULL |
+----+---------------------+------+CREATE TABLE log
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
dt DATETIME NOT NULL,
user INT UNSIGNED,
PRIMARY KEY (id, dt)
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016),
PARTITION p4 VALUES LESS THAN MAXVALUE
);PARTITION BY KEY ([column_names])
[PARTITIONS (number_of_partitions)] ERROR 1488 (HY000): Field in list of fields for partition function not found in tableCREATE OR REPLACE TABLE t1 (v1 INT)
PARTITION BY KEY (v1)
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (v1 INT, v2 INT)
PARTITION BY KEY (v1,v2)
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
id INT NOT NULL UNIQUE KEY,
name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
id INT NULL UNIQUE KEY,
name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;
ERROR 1488 (HY000): Field in list of fields for partition function not found in tableCREATE OR REPLACE TABLE t1 (
id INT NULL UNIQUE KEY,
name VARCHAR(5)
)
PARTITION BY KEY()
PARTITIONS 2;
ERROR 1488 (HY000): Field in list of fields for partition function not found in tableCREATE OR REPLACE TABLE t1 (
id INT NULL UNIQUE KEY,
name VARCHAR(5)
)
PARTITION BY KEY(name)
PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
a VARCHAR(10),
b VARCHAR(10),
c VARCHAR(10),
PRIMARY KEY (a(5), b, c(5))
) PARTITION BY KEY() PARTITIONS 2;
CREATE OR REPLACE TABLE t1 (
a VARCHAR(10),
b VARCHAR(10),
c VARCHAR(10),
PRIMARY KEY (b)
) PARTITION BY KEY() PARTITIONS 2;CREATE OR REPLACE TABLE t1 (
a VARCHAR(10),
b VARCHAR(10),
c VARCHAR(10),
PRIMARY KEY (a(5), b(5), c(5))
) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning functionDiscover these variants that allow partitioning based on multiple columns and non-integer types, offering greater flexibility than standard RANGE/LIST.
RANGE COLUMNS and LIST COLUMNS are variants of, respectively, RANGE and LIST. With these partitioning types, there is not a single partitioning expression; instead, a list of one or more columns is accepted. The following rules apply:
The list can contain one or more columns.
Only bare columns are permitted; no expressions.
All the specified columns are compared to the specified values to determine which partition should contain a specific row. See below for details.
The last part of a statement can be definition of the new table's partitions. In the case of RANGE COLUMNS partitioning, the syntax is as follows:
The syntax for LIST COLUMNS is as follows:
partition_name is the name of a partition.
To determine which partition should contain a row, all specified columns are compared to each partition definition.
With LIST COLUMNS, a row matches a partition if all row values are identical to the specified values. At most one partition can match the row.
With RANGE COLUMNS, a row matches a partition if it is less than the specified value tuple in lexicographic order. The first partition that matches the row values are used.
The DEFAULT partition catches all records which do not fit in other partitions. Only one DEFAULT partition is allowed.
RANGE COLUMNS partition:
LIST COLUMNS partition:
This page is licensed: CC BY-SA / Gnu FDL
PARTITION BY RANGE COLUMNS (col1, col2, ...)
(
PARTITION partition_name VALUES LESS THAN (value1, value2, ...),
[ PARTITION partition_name VALUES LESS THAN (value1, value2, ...), ... ]
)PARTITION BY LIST COLUMNS (partitioning_expression)
(
PARTITION partition_name VALUES IN (value1, value2, ...),
[ PARTITION partition_name VALUES IN (value1, value2, ...), ... ]
[ PARTITION partititon_name DEFAULT ]
)CREATE OR REPLACE TABLE t1 (
date1 DATE NOT NULL,
date2 DATE NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (date1,date2) (
PARTITION p0 VALUES LESS THAN ('2013-01-01', '1994-12-01'),
PARTITION p1 VALUES LESS THAN ('2014-01-01', '1995-12-01'),
PARTITION p2 VALUES LESS THAN ('2015-01-01', '1996-12-01')
);CREATE OR REPLACE TABLE t1 (
num TINYINT(1) NOT NULL
)
ENGINE = InnoDB
PARTITION BY LIST COLUMNS (num) (
PARTITION p0 VALUES IN (0,1),
PARTITION p1 VALUES IN (2,3),
PARTITION p2 DEFAULT
);AUTO_INCREMENT secrets.
General partitioning advice, taken from Rick's RoTs - Rules of Thumb:
Don't use PARTITIONing until you know how and why it will help.
Don't use PARTITION unless you will have more than a million rows to handle.
No more than 50 PARTITIONs on a table (open, show table status, etc, are impacted).
PARTITION BY RANGE is the only useful method.
Subpartitions are not useful.
The partition field should not be the first field in any key.
It is okay to have an AUTO_INCREMENT as the first part of a compound key, or in a nonunique index.
It is so tempting to believe that PARTITIONing will solve performance problems. But it is so often wrong.
PARTITIONing splits up one table into several smaller tables. But table size is rarely a performance issue. Instead, I/O time and indexes are the issues.
A common fallacy: "Partitioning will make my queries run faster". It won't. Ponder what it takes for a 'point query'. Without partitioning, but with an appropriate index, there is a BTree (the index) to drill down to find the desired row. For a billion rows, this might be 5 levels deep. With partitioning, first the partition is chosen and "opened", then a smaller BTree (of say 4 levels) is drilled down. Well, the savings of the shallower BTree is consumed by having to open the partition. Similarly, if you look at the disk blocks that need to be touched, and which of those are likely to be cached, you come to the conclusion that about the same number of disk hits is likely. Since disk hits are the main cost in a query, Partitioning does not gain any performance (at least for this typical case). The 2D case (below) gives the main contradiction to this discussion.
Use case #1 -- time series. Perhaps the most common use case where PARTITIONing shines is in a dataset where "old" data is periodically deleted from the table. RANGE PARTITIONing by day (or other unit of time) lets you do a nearly instantaneous DROP PARTITION plus REORGANIZE PARTITION instead of a much slower DELETE. Much of this blog is focused on this use case. This use case is also discussed in Big DELETEs
The big win for Case #1: DROP PARTITION is a lot faster than DELETEing a lot of rows.
Use case #2 -- 2-D index. INDEXes are inherently one-dimensional. If you need two "ranges" in the WHERE clause, try to migrate one of them to PARTITIONing.
Finding the nearest 10 pizza parlors on a map needs a 2D index. Partition pruning sort of gives a second dimension. See Latitude/Longitude Indexing That uses PARTITION BY RANGE(latitude) together with PRIMARY KEY(longitude, ...)
The big win for Case #2: Scanning fewer rows.
Use case #3 -- hot spot. This is a bit complicated to explain. Given this combination:
A table's index is too big to be cached, but the index for one partition is cacheable, and
The index is randomly accessed, and
Data ingestion would normally be I/O bound due to updating the index Partitioning can keep all the index "hot" in RAM, thereby avoiding a lot of I/O.
The big win for Case #3: Improving caching to decrease I/O to speed up operations.
For AUTO_INCREMENT to work (in any table), it must be the first field in some index. Period. There are no other requirements on indexing it.
Being the first field in some index lets the engine find the 'next' value when opening the table.
AUTO_INCREMENT need not be UNIQUE. What you lose: prevention of explicitly inserting a duplicate id. (This is rarely needed, anyway.)
Examples (where id is AUTO_INCREMENT):
PRIMARY KEY (...), INDEX(id)
PRIMARY KEY (...), UNIQUE(id, partition_key) -- not useful
INDEX(id), INDEX(...) (but no UNIQUE keys)
PRIMARY KEY(id), ... -- works only if id is the partition key (not very useful)
Let's focus on the maintenance task involved in Case #1, as described above.
You have a large table that is growing on one end and being pruned on the other. Examples include news, logs, and other transient information. PARTITION BY RANGE is an excellent vehicle for such a table.
DROP PARTITION is much faster than DELETE. (This is the big reason for doing this flavor of partitioning.)
Queries often limit themselves to 'recent' data, thereby taking advantage of "partition pruning".
Depending on the type of data, and how long before it expires, you might have daily or weekly or hourly (etc) partitions.
There is no simple SQL statement to "drop partitions older than 30 days" or "add a new partition for tomorrow". It would be tedious to do this by hand every day.
After which you have...
Perhaps you noticed some odd things in the example. Let me explain them.
Partition naming: Make them useful.
from20120415 ... 04-16: Note that the LESS THAN is the next day's date
The "start" partition: See paragraph below.
The "future" partition: This is normally empty, but it can catch overflows; more later.
The range key (dt) must be included in any PRIMARY or UNIQUE key.
The range key (dt) should be last in any keys it is in -- You have already "pruned" with it; it is almost useless in the index, especially at the beginning.
DATETIME, etc -- I picked this datatype because it is typical for a time series. Newer MySQL versions allow TIMESTAMP. INT could be used; etc.
There is an extra day (03-16 thru 04-16): The latest day is only partially full.
Why the bogus "start" partition? If an invalid datetime (Feb 31) were to be used, the datetime would turn into NULL. NULLs are put into the first partition. Since any SELECT could have an invalid date (yeah, this stretching things), the partition pruner always includes the first partition in the resulting set of partitions to search. So, if the SELECT must scan the first partition, it would be slightly more efficient if that partition were empty. Hence the bogus "start" partition. Longer discussion, by The Data Charmer 5.5 eliminates the bogus check, but only if you switch to a new syntax:
More on the "future" partition. Sooner or later the cron/EVENT to add tomorrow's partition will fail to run. The worst that could happen is for tomorrow's data to be lost. The easiest way to prevent that is to have a partition ready to catch it, even if this partition is normally always empty.
Having the "future" partition makes the ADD PARTITION script a little more complex. Instead, it needs to take tomorrow's data from "future" and put it into a new partition. This is done with the REORGANIZE command shown. Normally nothing need be moved, and the ALTER takes virtually zero time.
DROP if the oldest partition is "too old".
Add 'tomorrow' near the end of today, but don't try to add it twice.
Do not count partitions -- there are two extra ones. Use the partition names or information_schema.PARTITIONS.PARTITION_DESCRIPTION.
DROP/Add only once in the script. Rerun the script if you need more.
Run the script more often than necessary. For daily partitions, run the script twice a day, or even hourly. Why? Automatic repair.
As I have said many times, in many places, BY RANGE is perhaps the only useful variant. And a time series is the most common use for PARTITIONing.
(as discussed here) DATETIME/DATE with TO_DAYS()
DATETIME/DATE with TO_DAYS(), but with 7-day intervals
TIMESTAMP with TO_DAYS(). (version 5.1.43 or later)
PARTITION BY RANGE COLUMNS(DATETIME) (5.5.0)
PARTITION BY RANGE(TIMESTAMP) (version 5.5.15 / 5.6.3)
PARTITION BY RANGE(TO_SECONDS()) (5.6.0)
INT UNSIGNED with constants computed as unix timestamps.
INT UNSIGNED with constants for some non-time-based series.
MEDIUMINT UNSIGNED containing an "hour id": FLOOR(FROM_UNIXTIME(timestamp) / 3600)
Months, Quarters, etc: Concoct a notation that works.
How many partitions?
Under, say, 5 partitions -- you get very little of the benefits.
Over, say, 50 partitions, and you hit inefficiencies elsewhere.
Certain operations (SHOW TABLE STATUS, opening the table, etc) open every partition.
MyISAM, before version 5.6.6, would lock all partitions before pruning!
Partition pruning does not happen on INSERTs (until Version 5.6.7), so INSERT needs to open all the partitions.
A possible 2-partition use case:
8192 partitions is a hard limit (1024 before ).
Before "native partitions" (5.7.6), each partition consumed a chunk of memory.
The complexity of the code is in the discovery of the PARTITION names, especially of the oldest and the 'next'.
To run the demo,
Install Perl and DBIx::DWIW (from CPAN).
copy the txt file (link above) to demo_part_maint.pl
execute perl demo_part_maint.pl to get the rest of the instructions
The program will generate and execute (when needed) either of these:
The tips in this document apply to MySQL, MariaDB, and Percona.
Future (as envisioned in 2016):
MySQL 5.7.6 has "native partitioning for InnoDB".
FOREIGN KEY support, perhaps in a later 8.0.xx.
"GLOBAL INDEX" -- this would avoid the need for putting the partition key in every unique index, but make DROP PARTITION costly. This is farther into the future.
MySQL 8.0, released Sep, 2016, not yet GA)
Only InnoDB tables can be partitioned -- MariaDB is likely to continue maintaining Partitioning on non-InnoDB tables, but Oracle is clearly not.
Some of the problems having lots of partitions are lessened by the Data-Dictionary-in-a-table.
Native partitioning will give:
This will improve performance slightly by combining two "handlers" into one.
Decreased memory usage, especially when using a large number of partitions.
Rick James graciously allowed us to use this article in the documentation. Rick James' site has other useful tips, how-tos, optimizations, and debugging tips. Original source: partitionmaint
This page is licensed: CC BY-SA / Gnu FDL
ALTER TABLE tbl
DROP PARTITION from20120314;
ALTER TABLE tbl
REORGANIZE PARTITION future INTO (
PARTITION from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),
PARTITION future VALUES LESS THAN MAXVALUE);CREATE TABLE tbl (
dt DATETIME NOT NULL, -- or DATE
...
PRIMARY KEY (..., dt),
UNIQUE KEY (..., dt),
...
)
PARTITION BY RANGE (TO_DAYS(dt)) (
PARTITION START VALUES LESS THAN (0),
PARTITION from20120315 VALUES LESS THAN (TO_DAYS('2012-03-16')),
PARTITION from20120316 VALUES LESS THAN (TO_DAYS('2012-03-17')),
...
PARTITION from20120414 VALUES LESS THAN (TO_DAYS('2012-04-15')),
PARTITION from20120415 VALUES LESS THAN (TO_DAYS('2012-04-16')),
PARTITION future VALUES LESS THAN MAXVALUE
);PARTITION BY RANGE COLUMNS(dt) (
PARTITION day_20100226 VALUES LESS THAN ('2010-02-27'), ...ALTER TABLE tbl REORGANIZE PARTITION
future
INTO (
PARTITION from20150606 VALUES LESS THAN (736121),
PARTITION future VALUES LESS THAN MAXVALUE
)
ALTER TABLE tbl
DROP PARTITION from20150603Learn the fundamentals of table partitioning in MariaDB, including its benefits for performance, maintenance, and managing large datasets.
In MariaDB, a table can be split in smaller subsets. Both data and indexes are partitioned.
There can be several reasons to use this feature:
If you often need to delete a large set of rows, such as all rows for a given year, using partitions can help, as dropping a partition with many rows is very fast, while deleting a lot of rows can be very slow.
Very large tables and indexes can be slow even with optimized queries. But if the target table is partitioned, queries that read a small number of partitions can be much faster. However, this means that the queries have to be written carefully in order to only access a given set of partitions.
Partitioning allows one to distribute files over multiple storage devices. For example, we can have historical data on slower, larger disks (historical data are not supposed to be frequently read); and current data can be on faster disks, or SSD devices.
In case we separate historical data from recent data, we will probably need to take regular backups of one partition, not the whole table.
When partitioning a table, the use should decide:
a partitioning type;
a partitioning expression.
A partitioning type is the method used by MariaDB to decide how rows are distributed over existing partitions. Choosing the proper partitioning type is important to distribute rows over partitions in an efficient way.
With some partitioning types, a partitioning expression is also required. A partitioning function is an SQL expression returning an integer or temporal value, used to determine which partition will contain a given row. The partitioning expression is used for all reads and writes on involving the partitioned table, thus it should be fast.
MariaDB supports the following partitioning types:
By default, MariaDB permits partitioning. You can determine this by using the statement, for example:
If partition is listed as DISABLED:
MariaDB has either been built without partitioning support, or has been started with the option, or one of its variants:
and you will not be able to create partitions.
It is possible to create a new partitioned table using .
allows one to:
Partition an existing table;
Remove partitions from a partitioned table (with all data in the partition);
Add/remove partitions, or reorganize them, as long as the partitioning function allows these operations (see below);
Exchange a partition with a table;
[ALTER TABLE](../../reference/sql-statements-and-structure/sql-statements/data-definition/alter/alter-table.md) ... ADD PARTITION can be used to add partitions to an existing table:
With partitions, it is only possible to add a partition to the high end of the range, not the low end. For example, the following results in an error:
You can work around this by using REORGANIZE PARTITION to split the partition instead. See .
is used to reduce the number of HASH or KEY partitions by the specified number. For example, given the following table with 5 partitions:
The following statement reduces the number of partitions by 2, leaving the table with 3 partitions:
can be used to convert partitions in an existing table to a standalone table:
CONVERT TABLE does the reverse, converting a table into a partition:
When converting tables to a partition, validation is performed on each row to ensure it meets the partition requirements. This can be very slow in the case of larger tables. It is possible to disable this validation by specifying the WITHOUT VALIDATION option.
WITH VALIDATION will result in the validation being performed, and is the default behaviour.
An alternative to convert partitions to tables is to use . This requires having to manually do the following steps:
Create an empty table with the same structure as the partition.
Exchange the table with the partition.
Drop the empty partition.
For example:
Similarly, to do the reverse and convert a table into a partition [ALTER TABLE](../../reference/sql-statements-and-structure/sql-statements/data-definition/alter/alter-table.md) ... EXCHANGE PARTITION can also be used, with the following manual steps required:
create the partition
exchange the partition with the table
drop the old table:
For example:
can be used to drop specific partitions (and discard all data within the specified partitions) for and partitions. It cannot be used on or partitions. To rather remove all partitioning, while leaving the data unaffected, see .
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2 allows to exchange a partition or subpartition with another table.
The following requirements must be met:
Table t1 must be partitioned, and table t2 cannot be partitioned.
Table t2 cannot be a temporary table.
Table t1 and t2 must otherwise be identical.
Any existing row in t2 must match the conditions for storage in the exchanged partition p1 unless, from , the WITHOUT VALIDATION option is specified.
By default, MariaDB performs the validation to see that each row meets the partition requirements, and the statement fails if a row does not fit.
This attempted exchange fails, as the value is already in t2, and 2015-05-05 is outside of the partition conditions:
This validation is performed for each row, and can be very slow in the case of larger tables. It is possible to disable this validation by specifying the WITHOUT VALIDATION option:
WITH VALIDATION results in the validation being performed, and is the default behavior.
removes all partitioning from the table, while leaving the data unaffected. To rather drop a particular partition (and discard all of its data), see .
Reorganizing partitions allows one to adjust existing partitions, without losing data. Specifically, the statement can be used for:
Splitting an existing partition into multiple partitions.
Merging a number of existing partitions into a new, single, partition.
Changing the ranges for a subset of existing partitions defined using VALUES LESS THAN.
Changing the value lists for a subset of partitions defined using VALUES I
An existing partition can be split into multiple partitions. This can also be used to add a new partition at the low end of a partition (which is not possible by ).
Similarly, if MAXVALUE binds the high end:
A number of existing partitions can be merged into a new partition, for example:
The statement can also be used for renaming partitions. Note that this creates a copy of the partition:
[ALTER TABLE](../../reference/sql-statements-and-structure/sql-statements/data-definition/alter/alter-table.md) ... TRUNCATE PARTITION
removes all data from the specified partition/s, leaving the table and partition structure unchanged. Partitions don't need to be contiguous:
Similar to , key distributions for specific partitions can also be analyzed and stored, for example:
Similar to , specific partitions can be checked for errors, for example:
The ALL keyword can be used in place of the list of partition names, and the check operation are performed on all partitions.
Similar to , specific partitions can be repaired:
As with , the QUICK and EXTENDED options are available. However, the USE_FRM option cannot be used with this statement on a partitioned table.
REPAIR PARTITION fails if there are duplicate key errors. ALTER IGNORE TABLE ... REPAIR PARTITION can be used in this case.
The ALL keyword can be used in place of the list of partition names, and the repair operation are performed on all partitions.
Similar to , specific partitions can be checked for errors:
OPTIMIZE PARTITION does not support per-partition optimization on InnoDB tables, and will issue a warning and cause the entire table to rebuilt and analyzed. ALTER TABLE ... REBUILD PARTITION and ALTER TABLE ... ANALYZE PARTITION can be used instead.
The ALL keyword can be used in place of the list of partition names, and the optimize operation are performed on all partitions.
Some MariaDB allow more interesting uses for partitioning.
The storage engine allows one to:
Treat a set of identical defined tables as one.
A MyISAM table can be in many different MERGE sets and also used separately.
allows one to:
Move partitions of the same table on different servers. In this way, the workload can be distributed on more physical or virtual machines (data sharding).
All partitions of a SPIDER table can also live on the same machine. In this case there are a small overhead (SPIDER uses connections to localhost), but queries that read multiple partitions will use parallel threads.
allows one to:
Build a table whose partitions are tables using different storage engines (like InnoDB, MyISAM, or even engines that do not support partitioning).
Build an indexable, writeable table on several data files. These files can be in different formats.
See also:
contains information about existing partitions.
for suggestions on using partitions
This page is licensed: CC BY-SA / Gnu FDL
Perform administrative operations on some or all partitions (analyze, optimize, check, repair).
Renaming partitions.
SHOW PLUGINS;
...
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+| partition | DISABLED | STORAGE ENGINE | NULL | GPL |
+-------------------------------+----------+--------------------+---------+---------+--skip-partition
--disable-partition
--partition=OFFADD PARTITION [IF NOT EXISTS] (partition_definition)CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 ADD PARTITION (
PARTITION p4 VALUES LESS THAN (2017),
PARTITION p5 VALUES LESS THAN (2018)
);ALTER TABLE t1 ADD PARTITION (
PARTITION p0a VALUES LESS THAN (2012)
);
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partitionCOALESCE PARTITION numberCREATE OR REPLACE TABLE t1 (v1 INT)
PARTITION BY KEY (v1)
PARTITIONS 5;ALTER TABLE t1 COALESCE PARTITION 2;CONVERT PARTITION partition_name TO TABLE tbl_name
CONVERT TABLE normal_table TO partition_definitionCREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
INSERT INTO t1 VALUES ('2013-11-11'),('2014-11-11'),('2015-11-11');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
| 2015-11-11 00:00:00 |
+---------------------+
ALTER TABLE t1 CONVERT PARTITION p3 TO TABLE t2;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
+---------------------+
SELECT * FROM t2;
+--------------+
| dt |
+--------------+
| 2015-11-11 00:00:00 |
+---------------------+
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (year(`dt`))
(PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB)
SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
TABLE: t2
CREATE TABLE: CREATE TABLE `t2` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ciALTER TABLE t1 CONVERT TABLE t2 TO PARTITION p3 VALUES LESS THAN (2016);
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
| 2015-11-11 00:00:00 |
+---------------------+
3 rows in set (0.001 sec)
SELECT * FROM t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (year(`dt`))
(PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN (2016) ENGINE = InnoDB)CONVERT TABLE normal_table TO partition_definition [{WITH | WITHOUT} VALIDATION]CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
INSERT INTO t1 VALUES ('2013-11-11'),('2014-11-11'),('2015-11-11');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
| 2015-11-11 00:00:00 |
+---------------------+
CREATE OR REPLACE TABLE t2 LIKE t1;
ALTER TABLE t2 REMOVE PARTITIONING;
ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE t2;
ALTER TABLE t1 DROP PARTITION p3;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
+---------------------+
SELECT * FROM t2;
+--------------+
| dt |
+--------------+
| 2015-11-11 00:00:00 |
+---------------------+
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (year(`dt`))
(PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB)
SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
TABLE: t2
CREATE TABLE: CREATE TABLE `t2` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ciALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2016));
ALTER TABLE t1 EXCHANGE PARTITION p3 WITH TABLE t2;
DROP TABLE t2;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-11 00:00:00 |
| 2014-11-11 00:00:00 |
| 2015-11-11 00:00:00 |
+---------------------+
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
TABLE: t1
CREATE TABLE: CREATE TABLE `t1` (
`dt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE (year(`dt`))
(PARTITION `p0` VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN (2016) ENGINE = InnoDB)DROP PARTITION [IF EXISTS] partition_namesCREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
INSERT INTO t1 VALUES ('2012-11-15');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2012-11-15 00:00:00 |
+---------------------+
ALTER TABLE t1 DROP PARTITION p0;
SELECT * FROM t1;
Empty set (0.002 sec)EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]EXCHANGE PARTITION partition_name WITH TABLE tbl_nameCREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014)
);
CREATE OR REPLACE TABLE t2 (
dt DATETIME NOT NULL
) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('2012-01-01'),('2013-01-01');
INSERT INTO t2 VALUES ('2013-02-02');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2012-01-01 00:00:00 |
| 2013-01-01 00:00:00 |
+---------------------+
SELECT * FROM t2;
+--------------+
| dt |
+--------------+
| 2013-02-02 00:00:00 |
+---------------------+
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2012-01-01 00:00:00 |
| 2013-02-02 00:00:00 |
+---------------------+
SELECT * FROM t2;
+--------------+
| dt |
+--------------+
| 2013-01-01 00:00:00 |
+---------------------+CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014)
);
CREATE OR REPLACE TABLE t2 (
dt DATETIME NOT NULL
) ENGINE = InnoDB;
INSERT INTO t1 VALUES ('2012-02-02'),('2013-03-03');
INSERT INTO t2 VALUES ('2015-05-05');
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2;ERROR 1526 (HY000): Table has no partition for value 0ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2 WITHOUT VALIDATION;Query OK, 0 rows affected (0.048 sec)REMOVE PARTITIONINGALTER TABLE t1 REMOVE PARTITIONING;REORGANIZE PARTITION [partition_names INTO (partition_definitions)]CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 REORGANIZE PARTITION p0 INTO (
PARTITION p0a VALUES LESS THAN (2012),
PARTITION p0b VALUES LESS THAN (2013)
);CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
ALTER TABLE t1 REORGANIZE PARTITION p4 INTO (
PARTITION p4 VALUES LESS THAN (2017),
PARTITION p5 VALUES LESS THAN MAXVALUE
);CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 REORGANIZE PARTITION p2,p3 INTO (
PARTITION p2 VALUES LESS THAN (2016)
);CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (
PARTITION p3 VALUES LESS THAN (2017)
);CREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (
PARTITION p3_new VALUES LESS THAN (2016)
);TRUNCATE PARTITION partition_namesCREATE OR REPLACE TABLE t1 (
dt DATETIME NOT NULL
)
ENGINE = InnoDB
PARTITION BY RANGE (YEAR(dt))
(
PARTITION p0 VALUES LESS THAN (2013),
PARTITION p1 VALUES LESS THAN (2014),
PARTITION p2 VALUES LESS THAN (2015),
PARTITION p3 VALUES LESS THAN (2016)
);
INSERT INTO t1 VALUES ('2012-11-01'),('2013-11-02'),('2014-11-03'),('2015-11-04');
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2012-11-01 00:00:00 |
| 2013-11-02 00:00:00 |
| 2014-11-03 00:00:00 |
| 2015-11-04 00:00:00 |
+---------------------+
ALTER TABLE t1 TRUNCATE PARTITION p0,p2;
SELECT * FROM t1;
+--------------+
| dt |
+--------------+
| 2013-11-02 00:00:00 |
| 2015-11-04 00:00:00 |
+---------------------+ALTER TABLE t1 ANALYZE PARTITION p0,p1,p3;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status | OK |
+---------+---------+----------+----------+CHECK PARTITION {ALL | PARTITION [,partition2 ...]}ALTER TABLE t1 CHECK PARTITION p1,p3;
+---------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status | OK |
+---------+-------+----------+----------+REPAIR PARTITION {ALL | partition [,partition2 ...]} [QUICK] [EXTENDED]ALTER TABLE t1 REPAIR PARTITION p0,p3;
+---------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------+----------+----------+
| test.t1 | repair | status | OK |
+---------+--------+----------+----------+OPTIMIZE PARTITION {ALL | PARTITION [,partition2 ...]}ALTER TABLE t1 OPTIMIZE PARTITION p0,p3;
+---------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t1 | optimize | status | OK |
+---------+----------+----------+----------+