MENU


Partitioning

A table can be divided into multiple partitions, which may be stored in different directories, letting data span multiple disks and letting related data be moved easily. Some queries benefit from built-in partition pruning, which excludes non-matching partitions when retrieving rows for a WHERE clause; specific partitions can also be named explicitly in DML.

MySQL supports only horizontal partitioning, where different rows are assigned to different partitions. The MERGE, CSV, and FEDERATED storage engines do not support partitioning – see Storage Engines.

Creation

partition_options:
    PARTITION BY
      { [LINEAR] HASH(expr)
       | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
       | RANGE{(expr) | COLUMNS(column_list)}
       | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
      { [LINEAR] HASH(expr)
       | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
       [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}]
       [[STORAGE] ENGINE [=] engine_name]
       [COMMENT [=] 'string']
       [DATA DIRECTORY [=] 'data_dir']
       [INDEX DIRECTORY [=] 'index_dir']
       [MAX_ROWS [=] max_number_of_rows]
       [MIN_ROWS [=] min_number_of_rows]
       [TABLESPACE [=] tablespace_name]
       [(subpartition_definition [, subpartition_definition] ...)]

The full partition_options clause, as used after a CREATE TABLE definition. For InnoDB, DATA DIRECTORY='directory' stores a partition outside the data directory (requires innodb_file_per_table), which can place partitions on different disks or hosts across a LAN via a shared path such as \\Host2\SharedFolder (Windows) or /mnt/shared_folder_host2 (Linux).

CREATE TABLE tbl(a INT)
PARTITION BY RANGE (a) (
   PARTITION p0 VALUES LESS THAN (10),
   PARTITION p1 VALUES LESS THAN (20),
   PARTITION p2 VALUES LESS THAN (30),
   PARTITION p3 VALUES LESS THAN MAXVALUE
);

RANGE partitioning – partitions on an expression or column value, in ascending, non-overlapping bands.

CREATE TABLE tbl (d DATE, s VARCHAR(10))
PARTITION BY RANGE COLUMNS (s,d) ( -- no expression allowed
   PARTITION p0 VALUES LESS THAN ('d','2015-01-01'),
   PARTITION p1 VALUES LESS THAN ('f','2015-01-01'),
   PARTITION p2 VALUES LESS THAN ('n','2015-01-01'),
   PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

RANGE COLUMNS partitioning – like RANGE, but compares tuples of column values directly instead of a single expression.

CREATE TABLE tbl(a INT)
PARTITION BY LIST (a) ( -- integer columns only
   PARTITION odd VALUES IN(1,3,5,7,9),
   PARTITION even VALUES IN(0,2,4,6,8)
);

LIST partitioning – each partition matches an explicit set of discrete values.

CREATE TABLE tbl(a VARCHAR(3), b VARCHAR(3))
PARTITION BY LIST COLUMNS (a,b) (
   PARTITION pt1 VALUES IN(('a','x'),('c','x')),
   PARTITION pt2 VALUES IN(('b','y'))
);

LIST COLUMNS partitioning – LIST over tuples of column values instead of a single expression.

CREATE TABLE tbl(d1 DATE, d2 DATE, UNIQUE(c,d))
PARTITION BY LINEAR HASH (YEAR(d1)+YEAR(d2)) -- integer expression
   PARTITIONS 4;
HASH partitioning uses a simple modulo function to map rows to partitions; LINEAR HASH uses a power-of-two algorithm that makes adding, dropping, merging, and splitting partitions much faster, at the cost of a less even data distribution. To find a row's partition under LINEAR HASH: compute v = POWER(2, CEILING(LOG(2, pTotal))), set N = F(columns) & (v-1), then while N >= pTotal, halve v (rounding up) and recompute N = N & (v-1).
CREATE TABLE tbl(a DATE, b VARCHAR(10), PRIMARY KEY (a,b))
PARTITION BY KEY(a,b) -- non-integer columns allowed
   PARTITIONS 6;
KEY partitioning is similar to HASH, but MySQL supplies its own internal hashing function (the same one used for PASSWORD()), and non-integer columns are allowed. Omitting the column list uses the table's primary key. LINEAR KEY is also supported, analogous to LINEAR HASH.
CREATE TABLE tbl (a INT, b DATE)
PARTITION BY RANGE(YEAR(b)) -- RANGE or LIST
SUBPARTITION BY HASH(TO_DAYS(b)) -- HASH or KEY
SUBPARTITIONS 2 (
   PARTITION p0 VALUES LESS THAN (2016),
   PARTITION p1 VALUES LESS THAN (2018),
   PARTITION p2 VALUES LESS THAN MAXVALUE
);

Subpartitioning – each RANGE/LIST partition is further divided by HASH/KEY, here into 2 subpartitions apiece. Subpartitions can also be defined explicitly, each with its own DATA DIRECTORY/INDEX DIRECTORY.

A unique index must include every column used in the partitioning function.

Management

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

partition_option:
    ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING

Partition-management clauses of ALTER TABLE.

ALTER TABLE tbl TRUNCATE PARTITION p3;
ALTER TABLE tbl DROP PARTITION p4;
ALTER TABLE tbl ADD PARTITION (
   PARTITION p5 VALUES LESS THAN (2010)); -- appended at the end only

ALTER TABLE members REORGANIZE PARTITION p0 INTO (
   PARTITION n0 VALUES LESS THAN (1960),
   PARTITION n1 VALUES LESS THAN (1970)
); -- splitting

ALTER TABLE members REORGANIZE PARTITION s2,p3 INTO (
   PARTITION p0 VALUES LESS THAN (1980)
); -- merging

ALTER TABLE tbl PARTITION BY HASH(YEAR(dob)) PARTITIONS 8; -- change partitioning type entirely

Altering RANGE/LIST partitions: truncating, dropping, adding (new RANGE partitions can only be appended at the end), splitting and merging via REORGANIZE, and switching partitioning schemes altogether.

ALTER TABLE tbl COALESCE PARTITION 4; -- reduce by 4
ALTER TABLE tbl ADD PARTITION PARTITIONS 6; -- add 6

Altering HASH/KEY partitions, which are added or removed by count rather than by name.

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE tbl; -- pt is partitioned, tbl is not
ALTER TABLE e2 REMOVE PARTITIONING; -- removes partitioning
ALTER TABLE tbl REBUILD PARTITION p0, p1; -- removes and reinserts all rows
ALTER TABLE tbl OPTIMIZE PARTITION p0, p1; -- same as CHECK + ANALYZE + REPAIR PARTITION
ALTER TABLE tbl ANALYZE PARTITION p3; -- reads and stores key distributions
ALTER TABLE t1 REPAIR PARTITION p0,p1; -- repairs corrupted partitions
ALTER TABLE trb3 CHECK PARTITION p1; -- like CHECK TABLE
ALTER TABLE tbl TRUNCATE PARTITION ALL; -- empties all partitions

Other partition maintenance clauses, several of which mirror the Table Maintenance Commands available for whole tables.

SHOW CREATE TABLE tbl;
EXPLAIN PARTITIONS SELECT * FROM tbl;

Inspecting a partitioned table's definition and which partitions a query plan touches. See also Information Commands.

Selection

Specific partitions can be targeted for SELECT, DELETE, INSERT, REPLACE, UPDATE, LOAD DATA, and LOAD XML operations. The partition clause usually follows the table name.

SELECT a, b FROM tbl PARTITION (p1,p2);
DELETE FROM tbl PARTITION (p0, p1) WHERE a=10;
UPDATE tbl PARTITION (p0) SET a=2 WHERE b=3;

Restricting a query, delete, and update to specific named partitions.


CREATE TABLE tbl (
    ts TIMESTAMP
) PARTITION BY RANGE (UNIX_TIMESTAMP(ts)) (
    PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01 00:00:00')),
    PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')),
    PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

-- Partition pruning: only p0 would be scanned (table is freshly created, so far empty)
SELECT * FROM tbl WHERE ts < '2013-06-01';

-- Restricting DML to a named partition
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'tbl';

Empty set (0 rows)
PARTITION_NAMETABLE_ROWS
p00
p10
p20
p30

CREATE TABLE members (
    id INT PRIMARY KEY,
    birth_year INT
) PARTITION BY RANGE (birth_year) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1980),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- Split p0 into two narrower ranges
ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION n0 VALUES LESS THAN (1950),
    PARTITION n1 VALUES LESS THAN (1960)
);

-- Perform routine maintenance on selected partitions
ALTER TABLE members ANALYZE PARTITION p1, p2;
ALTER TABLE members OPTIMIZE PARTITION p1, p2;

-- Query only the partitions that matter
SELECT * FROM members PARTITION (p1, p2) WHERE birth_year > 1975;