Important

You are viewing the archived documentation of TiDB, which no longer receives updates. It is recommended that you use the latest LTS version of the TiDB database.

Partitioning

This document introduces TiDB's implementation of partitioning.

Partitioning types

This section introduces the types of partitioning in TiDB. Currently, TiDB supports Range partitioning, List partitioning, List COLUMNS partitioning, and Hash partitioning.

Range partitioning, List partitioning and List COLUMNS partitioning are used to resolve the performance issues caused by a large amount of deletions in the application, and support fast drop partition operations. Hash partitioning is used to scatter the data when there are a large amount of writes.

Range partitioning

When a table is partitioned by Range, each partition contains rows for which the partitioning expression value lies within a given Range. Ranges have to be contiguous but not overlapping. You can define it by using VALUES LESS THAN.

Assume you need to create a table that contains personnel records as follows:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
);

You can partition a table by Range in various ways as needed. For example, you can partition it by using the store_id column:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

In this partition scheme, all rows corresponding to employees whose store_id is 1 through 5 are stored in the p0 partition while all employees whose store_id is 6 through 10 are stored in p1. Range partitioning requires the partitions to be ordered, from lowest to highest.

If you insert a row of data (72, 'Tom', 'John', '2015-06-25', NULL, NULL, 15), it falls in the p2 partition. But if you insert a record whose store_id is larger than 20, an error is reported because TiDB can not know which partition this record should be inserted into. In this case, you can use MAXVALUE when creating a table:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUE represents an integer value that is larger than all other integer values. Now, all records whose store_id is equal to or larger than 16 (the highest value defined) are stored in the p3 partition.

You can also partition a table by employees' job codes, which are the values of the job_code column. Assume that two-digit job codes stand for regular employees, three-digit codes stand for office and customer support personnel, and four-digit codes stand for managerial personnel. Then you can create a partitioned table like this:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT NOT NULL
)

PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),
    PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (10000)
);

In this example, all rows relating to regular employees are stored in the p0 partition, all office and customer support personnel in the p1 partition, and all managerial personnel in the p2 partition.

Besides splitting up the table by store_id, you can also partition a table by dates. For example, you can partition by employees' separation year:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY RANGE ( YEAR(separated) ) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

In Range partitioning, you can partition based on the values of the timestamp column and use the unix_timestamp() function, for example:

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

It is not allowed to use any other partitioning expression that contains the timestamp column.

Range partitioning is particularly useful when one or more of the following conditions are satisfied:

You want to delete the old data. If you use the employees table in the previous example, you can delete all records of employees who left this company before the year 1991 by simply using ALTER TABLE employees DROP PARTITION p0;. It is faster than executing the DELETE FROM employees WHERE YEAR(separated) <= 1990; operation.
You want to use a column that contains time or date values, or containing values arising from some other series.
You need to frequently run queries on the columns used for partitioning. For example, when executing a query like EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;, TiDB can quickly know that only the data in the p2 partition needs to be scanned, because the other partitions do not match the WHERE condition.

List partitioning

Warning

List partitioning is an experimental feature. It is not recommended that you use it in the production environment.

Before creating a List partitioned table, you need to set the value of the session variable tidb_enable_list_partition to ON.

set @@session.tidb_enable_list_partition = ON

Also, make sure that tidb_enable_table_partition is set to ON, which is the default setting.

List partitioning is similar to Range partitioning. Unlike Range partitioning, in List partitioning, the partitioning expression values for all rows in each partition are in a given value set. This value set defined for each partition can have any number of values but cannot have duplicate values. You can use the PARTITION ... VALUES IN (...) clause to define a value set.

Suppose that you want to create a personnel record table. You can create a table as follows:

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    store_id INT
);

Suppose that there are 20 stores distributed in 4 districts, as shown in the table below:

| Region  | Store ID Numbers     |
| ------- | -------------------- |
| North   | 1, 2, 3, 4, 5        |
| East    | 6, 7, 8, 9, 10       |
| West    | 11, 12, 13, 14, 15   |
| Central | 16, 17, 18, 19, 20   |

If you want to store the personnel data of employees of the same region in the same partition, you can create a List partitioned table based on store_id:

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    store_id INT
)
PARTITION BY LIST (store_id) (
    PARTITION pNorth VALUES IN (1, 2, 3, 4, 5),
    PARTITION pEast VALUES IN (6, 7, 8, 9, 10),
    PARTITION pWest VALUES IN (11, 12, 13, 14, 15),
    PARTITION pCentral VALUES IN (16, 17, 18, 19, 20)
);

After creating the partitions as above, you can easily add or delete records related to a specific region in the table. For example, suppose that all stores in the East region (East) are sold to another company. Then all the row data related to the store employees of this region can be deleted by executing ALTER TABLE employees TRUNCATE PARTITION pEast, which is much more efficient than the equivalent statement DELETE FROM employees WHERE store_id IN (6, 7, 8, 9, 10).

You can also execute ALTER TABLE employees DROP PARTITION pEast to delete all related rows, but this statement also deletes the pEast partition from the table definition. In this situation, you must execute the ALTER TABLE ... ADD PARTITION statement to recover the original partitioning scheme of the table.

Unlike Range partitioning, List partitioning does not have a similar MAXVALUE partition to store all values that do not belong to other partitions. Instead, all expected values of the partition expression must be included in the PARTITION ... VALUES IN (...) clause. If the value to be inserted in an INSERT statement does not match the column value set of any partition, the statement fails to execute and an error is reported. See the following example:

test> CREATE TABLE t (
    ->   a INT,
    ->   b INT
    -> )
    -> PARTITION BY LIST (a) (
    ->   PARTITION p0 VALUES IN (1, 2, 3),
    ->   PARTITION p1 VALUES IN (4, 5, 6)
    -> );
Query OK, 0 rows affected (0.11 sec)

test> INSERT INTO t VALUES (7, 7);
ERROR 1525 (HY000): Table has no partition for value 7

To ignore the error type above, you can use the IGNORE keyword. After using this keyword, if a row contains values that do not match the column value set of any partition, this row will not be inserted. Instead, any row with matched values is inserted, and no error is reported:

test> TRUNCATE t;
Query OK, 1 row affected (0.00 sec)

test> INSERT IGNORE INTO t VALUES (1, 1), (7, 7), (8, 8), (3, 3), (5, 5);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 5  Duplicates: 2  Warnings: 2

test> select * from t;
+------+------+
| a    | b    |
+------+------+
|    5 |    5 |
|    1 |    1 |
|    3 |    3 |
+------+------+
3 rows in set (0.01 sec)

List COLUMNS partitioning

Warning

List COLUMNS partitioning is an experimental feature. It is not recommended that you use it in the production environment.

List COLUMNS partitioning is a variant of List partitioning. You can use multiple columns as partition keys. Besides the integer data type, you can also use the columns in the string, DATE, and DATETIME data types as partition columns.

Suppose that you want to divide the store employees from the following 12 cities into 4 regions, as shown in the following table:

| Region | Cities                         |
| :----- | ------------------------------ |
| 1      | LosAngeles,Seattle, Houston    |
| 2      | Chicago, Columbus, Boston      |
| 3      | NewYork, LongIsland, Baltimore |
| 4      | Atlanta, Raleigh, Cincinnati   |

You can use List COLUMNS partitioning to create a table and store each row in the partition that corresponds to the employee's city, as shown below:

CREATE TABLE employees_1 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT,
    city VARCHAR(15)
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('LosAngeles', 'Seattle', 'Houston'),
    PARTITION pRegion_2 VALUES IN('Chicago', 'Columbus', 'Boston'),
    PARTITION pRegion_3 VALUES IN('NewYork', 'LongIsland', 'Baltimore'),
    PARTITION pRegion_4 VALUES IN('Atlanta', 'Raleigh', 'Cincinnati')
);

Unlike List partitioning, in List COLUMNS partitioning, you do not need to use the expression in the COLUMNS() clause to convert column values to integers.

List COLUMNS partitioning can also be implemented using columns of the DATE and DATETIME types, as shown in the following example. This example uses the same names and columns as the previous employees_1 table, but uses List COLUMNS partitioning based on the hired column:

CREATE TABLE employees_2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT,
    city VARCHAR(15)
)
PARTITION BY LIST COLUMNS(hired) (
    PARTITION pWeek_1 VALUES IN('2020-02-01', '2020-02-02', '2020-02-03',
        '2020-02-04', '2020-02-05', '2020-02-06', '2020-02-07'),
    PARTITION pWeek_2 VALUES IN('2020-02-08', '2020-02-09', '2020-02-10',
        '2020-02-11', '2020-02-12', '2020-02-13', '2020-02-14'),
    PARTITION pWeek_3 VALUES IN('2020-02-15', '2020-02-16', '2020-02-17',
        '2020-02-18', '2020-02-19', '2020-02-20', '2020-02-21'),
    PARTITION pWeek_4 VALUES IN('2020-02-22', '2020-02-23', '2020-02-24',
        '2020-02-25', '2020-02-26', '2020-02-27', '2020-02-28')
);

In addition, you can also add multiple columns in the COLUMNS() clause. For example:

CREATE TABLE t (
    id int,
    name varchar(10)
)
PARTITION BY LIST COLUMNS(id,name) (
     partition p0 values IN ((1,'a'),(2,'b')),
     partition p1 values IN ((3,'c'),(4,'d')),
     partition p3 values IN ((5,'e'),(null,null))
);

Hash partitioning

Hash partitioning is used to make sure that data is evenly scattered into a certain number of partitions. With Range partitioning, you must specify the range of the column values for each partition when you use Range partitioning, while you just need to specify the number of partitions when you use Hash partitioning.

Partitioning by Hash requires you to append a PARTITION BY HASH (expr) clause to the CREATE TABLE statement. expr is an expression that returns an integer. It can be a column name if the type of this column is integer. In addition, you might also need to append PARTITIONS num, where num is a positive integer indicating how many partitions a table is divided into.

The following operation creates a Hash partitioned table, which is divided into 4 partitions by store_id:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY HASH(store_id)
PARTITIONS 4;

If PARTITIONS num is not specified, the default number of partitions is 1.

You can also use an SQL expression that returns an integer for expr. For example, you can partition a table by the hire year:

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

The most efficient Hash function is one which operates upon a single table column, and whose value increases or decreases consistently with the column value.

For example, date_col is a column whose type is DATE, and the value of the TO_DAYS(date_col) expression varies with the value of date_col. YEAR(date_col) is different from TO_DAYS(date_col), because not every possible change in date_col produces an equivalent change in YEAR(date_col).

In contrast, assume that you have an int_col column whose type is INT. Now consider about the expression POW(5-int_col,3) + 6. It is not a good Hash function though, because as the value of int_col changes, the result of the expression does not change proportionally. A value change in int_col might result in a huge change in the expression result. For example, when int_col changes from 5 to 6, the change of the expression result is -1. But the result change might be -7 when int_col changes from 6 to 7.

In conclusion, when the expression has a form that is closer to y = cx, it is more suitable to be a Hash function. Because the more non-linear an expression is, the more unevenly scattered the data among the partitions tends to be.

In theory, pruning is also possible for expressions involving more than one column value, but determining which of such expressions are suitable can be quite difficult and time-consuming. For this reason, the use of hashing expressions involving multiple columns is not particularly recommended.

When using PARTITION BY HASH, TiDB decides which partition the data should fall into based on the modulus of the result of the expression. In other words, if a partitioning expression is expr and the number of partitions is num, MOD(expr, num) decides the partition in which the data is stored. Assume that t1 is defined as follows:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY HASH( YEAR(col3) )
    PARTITIONS 4;

When you insert a row of data into t1 and the value of col3 is '2005-09-15', then this row is inserted into partition 1:

MOD(YEAR('2005-09-01'),4)
=  MOD(2005,4)
=  1

How TiDB partitioning handles NULL

It is allowed in TiDB to use NULL as the calculation result of a partitioning expression.

Note

NULL is not an integer. TiDB's partitioning implementation treats NULL as being less than any other integer values, just as ORDER BY does.

Handling of NULL with Range partitioning

When you insert a row into a table partitioned by Range, and the column value used to determine the partition is NULL, then this row is inserted into the lowest partition.

CREATE TABLE t1 (
    c1 INT,
    c2 VARCHAR(20)
)

PARTITION BY RANGE(c1) (
    PARTITION p0 VALUES LESS THAN (0),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

Query OK, 0 rows affected (0.09 sec)

select * from t1 partition(p0);

+------|--------+
| c1   | c2     |
+------|--------+
| NULL | mothra |
+------|--------+
1 row in set (0.00 sec)

select * from t1 partition(p1);

Empty set (0.00 sec)

select * from t1 partition(p2);

Empty set (0.00 sec)

Drop the p0 partition and verify the result:

alter table t1 drop partition p0;

Query OK, 0 rows affected (0.08 sec)

select * from t1;

Empty set (0.00 sec)

Handling of NULL with Hash partitioning

When partitioning tables by Hash, there is a different way of handling NULL value - if the calculation result of the partitioning expression is NULL, it is considered as 0.

CREATE TABLE th (
    c1 INT,
    c2 VARCHAR(20)
)

PARTITION BY HASH(c1)
PARTITIONS 2;

Query OK, 0 rows affected (0.00 sec)

INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');

Query OK, 2 rows affected (0.04 sec)

select * from th partition (p0);

+------|--------+
| c1   | c2     |
+------|--------+
| NULL | mothra |
|    0 | gigan  |
+------|--------+
2 rows in set (0.00 sec)

select * from th partition (p1);

Empty set (0.00 sec)

You can see that the inserted record (NULL, 'mothra') falls into the same partition as (0, 'gigan').

Note

In this case, the actual behavior of TiDB is in line with the description of this document.

Partition management

For LIST and RANGE partitioned tables, you can add and drop partitions using the ALTER TABLE <table name> ADD PARTITION (<partition specification>) or ALTER TABLE <table name> DROP PARTITION <list of partitions> statement.

For LIST and RANGE partitioned tables, REORGANIZE PARTITION is not yet supported.

For HASH partitioned tables, COALESCE PARTITION and ADD PARTITION are not yet supported.

EXCHANGE PARTITION works by swapping a partition and a non-partitioned table, similar to how renaming a table like RENAME TABLE t1 TO t1_tmp, t2 TO t1, t1_tmp TO t2 works.

For example, ALTER TABLE partitioned_table EXCHANGE PARTITION p1 WITH TABLE non_partitioned_table swaps the non_partitioned_table table in the p1 partition with the partitioned_table table.

Ensure that all rows that you are exchanging into the partition match the partition definition; otherwise, these rows will not be found and cause unexpected issues.

Warning

EXCHANGE PARTITION is an experimental feature. It is not recommended to use it in a production environment. To enable it, set the tidb_enable_exchange_partition system variable to ON.

Range partition management

Create a partitioned table:

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)

PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

Drop a partition:

ALTER TABLE members DROP PARTITION p2;

Query OK, 0 rows affected (0.03 sec)

Empty a partition:

ALTER TABLE members TRUNCATE PARTITION p1;

Query OK, 0 rows affected (0.03 sec)

Note

ALTER TABLE ... REORGANIZE PARTITION is currently unsupported in TiDB.

Add a partition:

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

When partitioning tables by Range, ADD PARTITION can be only appended to the very end of a partition list. If it is appended to an existing Range partition, an error is reported:

ALTER TABLE members
    ADD PARTITION (
    PARTITION n VALUES LESS THAN (1970));

ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

Hash partition management

Unlike Range partitioning, DROP PARTITION is not supported in Hash partitioning.

Currently, ALTER TABLE ... COALESCE PARTITION is not supported in TiDB as well. For partition management statements that are not currently supported, TiDB returns an error.

alter table members optimize partition p0;

ERROR 8200 (HY000): Unsupported optimize partition

Partition pruning

Partition pruning is an optimization which is based on a very simple idea - do not scan the partitions that do not match.

Assume that you create a partitioned table t1:

CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)

PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

If you want to get the result of this SELECT statement:

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

It is evident that the result falls in either the p1 or the p2 partition, that is, you just need to search for the matching rows in p1 and p2. Excluding the unneeded partitions is so-called "pruning". If the optimizer is able to prune a part of partitions, the execution of the query in the partitioned table will be much faster than that in a non-partitioned table.

The optimizer can prune partitions through WHERE conditions in the following two scenarios:

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

Some cases for partition pruning to take effect

Partition pruning uses the query conditions on the partitioned table, so if the query conditions can not be pushed down to the partitioned table according to the planner's optimization rules, partition pruning does not apply for this query.
For example:
```
create table t1 (x int) partition by range (x) (
        partition p0 values less than (5),
        partition p1 values less than (10));
create table t2 (x int);
```
```
explain select * from t1 left join t2 on t1.x = t2.x where t2.x > 5;
```
In this query, the left out join is converted to the inner join, and then t1.x > 5 is derived from t1.x = t2.x and t2.x > 5, so it could be used in partition pruning and only the partition p1 remains.
```
explain select * from t1 left join t2 on t1.x = t2.x and t2.x > 5;
```
In this query, t2.x > 5 can not be pushed down to the t1 partitioned table, so partition pruning would not take effect for this query.
Since partition pruning is done during the plan optimizing phase, it does not apply for those cases that filter conditions are unknown until the execution phase.
For example:
```
create table t1 (x int) partition by range (x) (
        partition p0 values less than (5),
        partition p1 values less than (10));
```
```
explain select * from t2 where x < (select * from t1 where t2.x < t1.x and t2.x < 2);
```
This query reads a row from t2 and uses the result for the subquery on t1. Theoretically, partition pruning could benefit from t1.x > val expression in the subquery, but it does not take effect there as that happens in the execution phase.
As a result of a limitation from current implementation, if a query condition can not be pushed down to TiKV, it can not be used by the partition pruning.
Take the fn(col) expression as an example. If the TiKV coprocessor supports this fn function, fn(col) may be pushed down to the the leaf node (that is, partitioned table) according to the predicate push-down rule during the plan optimizing phase, and partition pruning can use it.
If the TiKV coprocessor does not support this fn function, fn(col) would not be pushed down to the leaf node. Instead, it becomes a Selection node above the leaf node. The current partition pruning implementation does not support this kind of plan tree.
For Hash partition, the only query supported by partition pruning is the equal condition.
For Range partition, for partition pruning to take effect, the partition expression must be in those forms: col or fn(col), and the query condition must be one of >, <, =, >=, and <=. If the partition expression is in the form of fn(col), the fn function must be monotonous.
If the fn function is monotonous, for any x and y, if x > y, then fn(x) > fn(y). Then this fn function can be called strictly monotonous. For any x and y, if x > y, then fn(x) >= fn(y). In this case, fn could also be called "monotonous". In theory, all monotonous functions are supported by partition pruning.
Currently, partition pruning in TiDB only support those monotonous functions:
```
unix_timestamp
to_days
```
For example, the partition expression is a simple column:
```
create table t (id int) partition by range (id) (
        partition p0 values less than (5),
        partition p1 values less than (10));
select * from t where id > 6;
```
Or the partition expression is in the form of fn(col) where fn is to_days:
```
create table t (dt datetime) partition by range (to_days(id)) (
        partition p0 values less than (to_days('2020-04-01')),
        partition p1 values less than (to_days('2020-05-01')));
select * from t where dt > '2020-04-18';
```
An exception is floor(unix_timestamp()) as the partition expression. TiDB does some optimization for that case by case, so it is supported by partition pruning.
```
create table t (ts timestamp(3) not null default current_timestamp(3))
partition by range (floor(unix_timestamp(ts))) (
        partition p0 values less than (unix_timestamp('2020-04-01 00:00:00')),
        partition p1 values less than (unix_timestamp('2020-05-01 00:00:00')));
select * from t where ts > '2020-04-18 02:00:42.123';
```

Partition selection

SELECT statements support partition selection, which is implemented by using a PARTITION option.

SET @@sql_mode = '';

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)

PARTITION BY RANGE(id)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (15),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

You can view the rows stored in the p1 partition:

SELECT * FROM employees PARTITION (p1);

+----|-------|--------|----------|---------------+
| id | fname | lname  | store_id | department_id |
+----|-------|--------|----------|---------------+
|  5 | Mary  | Jones  |        1 |             1 |
|  6 | Linda | Black  |        2 |             3 |
|  7 | Ed    | Jones  |        2 |             1 |
|  8 | June  | Wilson |        3 |             1 |
|  9 | Andy  | Smith  |        1 |             3 |
+----|-------|--------|----------|---------------+
5 rows in set (0.00 sec)

If you want to get the rows in multiple partitions, you can use a list of partition names which are separated by commas. For example, SELECT * FROM employees PARTITION (p1, p2) returns all rows in the p1 and p2 partitions.

When you use partition selection, you can still use WHERE conditions and options such as ORDER BY and LIMIT. It is also supported to use aggregation options such as HAVING and GROUP BY.

SELECT * FROM employees PARTITION (p0, p2)
    WHERE lname LIKE 'S%';

+----|-------|-------|----------|---------------+
| id | fname | lname | store_id | department_id |
+----|-------|-------|----------|---------------+
|  4 | Jim   | Smith |        2 |             4 |
| 11 | Jill  | Stone |        1 |             4 |
+----|-------|-------|----------|---------------+
2 rows in set (0.00 sec)

SELECT id, CONCAT(fname, ' ', lname) AS name
    FROM employees PARTITION (p0) ORDER BY lname;

+----|----------------+
| id | name           |
+----|----------------+
|  3 | Ellen Johnson  |
|  4 | Jim Smith      |
|  1 | Bob Taylor     |
|  2 | Frank Williams |
+----|----------------+
4 rows in set (0.06 sec)

SELECT store_id, COUNT(department_id) AS c
    FROM employees PARTITION (p1,p2,p3)
    GROUP BY store_id HAVING c > 4;

+---|----------+
| c | store_id |
+---|----------+
| 5 |        2 |
| 5 |        3 |
+---|----------+
2 rows in set (0.00 sec)

Partition selection is supported for all types of table partitioning, including Range partitioning and Hash partitioning. For Hash partitions, if partition names are not specified, p0, p1, p2,..., or pN-1 is automatically used as the partition name.

SELECT in INSERT ... SELECT can also use partition selection.

Restrictions and limitations on partitions

This section introduces some restrictions and limitations on partitioned tables in TiDB.

Partitioning keys, primary keys and unique keys

This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: Every unique key on the table must use every column in the table's partitioning expression. This also includes the table's primary key, because it is by definition a unique key.

For example, the following table creation statements are invalid:

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)

PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)

PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

In each case, the proposed table has at least one unique key that does not include all columns used in the partitioning expression.

The valid statements are as follows:

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)

PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)

PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

The following example displays an error:

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2),
    UNIQUE KEY (col3)
)

PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

The CREATE TABLE statement fails because both col1 and col3 are included in the proposed partitioning key, but neither of these columns is part of both of unique keys on the table. After the following modifications, the CREATE TABLE statement becomes valid:

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3),
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
    PARTITIONS 4;

The following table cannot be partitioned at all, because there is no way to include in a partitioning key any columns that belong to both unique keys:

CREATE TABLE t4 (
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3),
    UNIQUE KEY (col2, col4)
);

Because every primary key is by definition a unique key, so the next two statements are invalid:

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)

PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col3),
    UNIQUE KEY(col2)
)

PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

In the above examples, the primary key does not include all columns referenced in the partitioning expression. After adding the missing column in the primary key, the CREATE TABLE statement becomes valid:

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2, col3),
    UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

If a table has neither unique keys nor primary keys, then this restriction does not apply.

When you change tables using DDL statements, you also need to consider this restriction when adding a unique index. For example, when you create a partitioned table as shown below:

CREATE TABLE t_no_pk (c1 INT, c2 INT)
    PARTITION BY RANGE(c1) (
        PARTITION p0 VALUES LESS THAN (10),
        PARTITION p1 VALUES LESS THAN (20),
        PARTITION p2 VALUES LESS THAN (30),
        PARTITION p3 VALUES LESS THAN (40)
    );

Query OK, 0 rows affected (0.12 sec)

You can add a non-unique index by using ALTER TABLE statements. But if you want to add a unique index, the c1 column must be included in the unique index.

When using a partitioned table, you cannot specify the prefix index as a unique attribute:

CREATE TABLE t (a varchar(20), b blob,
    UNIQUE INDEX (a(5)))
    PARTITION by range columns (a) (
    PARTITION p0 values less than ('aaaaa'),
    PARTITION p1 values less than ('bbbbb'),
    PARTITION p2 values less than ('ccccc'));

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

Partitioning limitations relating to functions

Only the functions shown in the following list are allowed in partitioning expressions:

ABS()
CEILING()
DATEDIFF()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT() (see EXTRACT() function with WEEK specifier)
FLOOR()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP() (with TIMESTAMP columns)
WEEKDAY()
YEAR()
YEARWEEK()

Compatibility with MySQL

Currently, TiDB supports Range partitioning, List partitioning, List COLUMNS partitioning, and Hash partitioning. Other partitioning types that are available in MySQL such as key partitioning are not supported yet in TiDB.

For a table partitioned by RANGE COLUMNS, currently TiDB only supports using a single partitioning column.

With regard to partition management, any operation that requires moving data in the bottom implementation is not supported currently, including but not limited to: adjust the number of partitions in a Hash partitioned table, modify the Range of a Range partitioned table, merge partitions and exchange partitions.

For the unsupported partitioning types, when you create a table in TiDB, the partitioning information is ignored and the table is created in the regular form with a warning reported.

The LOAD DATA syntax does not support partition selection currently in TiDB.

create table t (id int, val int) partition by hash(id) partitions 4;

The regular LOAD DATA operation is supported:

load local data infile "xxx" into t ...

But Load Data does not support partition selection:

load local data infile "xxx" into t partition (p1)...

For a partitioned table, the result returned by select * from t is unordered between the partitions. This is different from the result in MySQL, which is ordered between the partitions but unordered inside the partitions.

create table t (id int, val int) partition by range (id) (
    partition p0 values less than (3),
    partition p1 values less than (7),
    partition p2 values less than (11));

Query OK, 0 rows affected (0.10 sec)

insert into t values (1, 2), (3, 4),(5, 6),(7,8),(9,10);

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

TiDB returns a different result every time, for example:

select * from t;

+------|------+
| id   | val  |
+------|------+
|    7 |    8 |
|    9 |   10 |
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------|------+
5 rows in set (0.00 sec)

The result returned in MySQL:

select * from t;

+------|------+
| id   | val  |
+------|------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
|    9 |   10 |
+------|------+
5 rows in set (0.00 sec)

The tidb_enable_list_partition environment variable controls whether to enable the partitioned table feature. If this variable is set to OFF, the partition information will be ignored when a table is created, and this table will be created as a normal table.

This variable is only used in table creation. After the table is created, modify this variable value takes no effect. For details, see system variables.

Dynamic pruning mode

Warning

This is still an experimental feature. It is NOT recommended that you use it in the production environment.

TiDB accesses partitioned tables in one of the two modes: dynamic mode and static mode. Currently, static mode is used by default. If you want to enable dynamic mode, you need to manually set the tidb_partition_prune_mode variable to dynamic.

set @@session.tidb_partition_prune_mode = 'dynamic'

In static mode, TiDB accesses each partition separately using multiple operators, and then merges the results using Union. The following example is a simple read operation where TiDB merges the results of two corresponding partitions using Union:

mysql> create table t1(id int, age int, key(id)) partition by range(id) (
    ->     partition p0 values less than (100),
    ->     partition p1 values less than (200),
    ->     partition p2 values less than (300),
    ->     partition p3 values less than (400));
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from t1 where id < 150;
+------------------------------+----------+-----------+------------------------+--------------------------------+
| id                           | estRows  | task      | access object          | operator info                  |
+------------------------------+----------+-----------+------------------------+--------------------------------+
| PartitionUnion_9             | 6646.67  | root      |                        |                                |
| ├─TableReader_12             | 3323.33  | root      |                        | data:Selection_11              |
| │ └─Selection_11             | 3323.33  | cop[tikv] |                        | lt(test.t1.id, 150)            |
| │   └─TableFullScan_10       | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_18             | 3323.33  | root      |                        | data:Selection_17              |
|   └─Selection_17             | 3323.33  | cop[tikv] |                        | lt(test.t1.id, 150)            |
|     └─TableFullScan_16       | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+------------------------+--------------------------------+
7 rows in set (0.00 sec)

In dynamic mode, each operator supports direct access to multiple partitions, so TiDB no longer uses Union.

mysql> set @@session.tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t1 where id < 150;
+-------------------------+----------+-----------+-----------------+--------------------------------+
| id                      | estRows  | task      | access object   | operator info                  |
+-------------------------+----------+-----------+-----------------+--------------------------------+
| TableReader_7           | 3323.33  | root      | partition:p0,p1 | data:Selection_6               |
| └─Selection_6           | 3323.33  | cop[tikv] |                 | lt(test.t1.id, 150)            |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t1        | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+-----------------+--------------------------------+
3 rows in set (0.00 sec)

From the above query results, you can see that the Union operator in the execution plan disappears while the partition pruning still takes effect and the execution plan only accesses p0 and p1.

dynamic mode makes execution plans simpler and clearer. Omitting the Union operation can improve the execution efficiency and avoid the problem of Union concurrent execution. In addition, dynamic mode also solves two problems that cannot be solved in static mode:

Plan Cache cannot be used. (See example 1 and 2)
Execution plans with IndexJoin cannot be used. (See example 3 and 4)

Example 1：In the following example, the Plan Cache feature is enabled in the configuration file and the same query is executed twice in static mode:

mysql> set @a=150;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@tidb_partition_prune_mode = 'static';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from 'select * from t1 where id < ?';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
Empty set (0.00 sec)

mysql> execute stmt using @a;
Empty set (0.00 sec)

-- In static mode, when the same query is executed twice, the cache cannot be hit at the second time.
mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

The last_plan_from_cache variable can show whether the last query hits the Plan Cache or not. From example 1, you can see that in static mode, even if the same query is executed multiple times on the partitioned table, the Plan Cache is not hit.

Example 2: In the following example, the same operations are performed in dynamic mode as done in example 1:

mysql> set @@tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from 'select * from t1 where id < ?';
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a;
Empty set (0.00 sec)

mysql> execute stmt using @a;
Empty set (0.00 sec)

-- In dynamic mode, the cache can be hit at the second time.
mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

From example 2, you can see that in dynamic mode, querying the partitioned table hits the Plan Cache.

Example 3: In the following example, a query is performed in static mode using the execution plan with IndexJoin:

mysql> create table t2(id int, code int);
Query OK, 0 rows affected (0.01 sec)

mysql> set @@tidb_partition_prune_mode = 'static';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select /*+ TIDB_INLJ(t1, t2) */ t1.* from t1, t2 where t2.code = 0 and t2.id = t1.id;
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
| id                             | estRows  | task      | access object          | operator info                                  |
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
| HashJoin_13                    | 12.49    | root      |                        | inner join, equal:[eq(test.t1.id, test.t2.id)] |
| ├─TableReader_42(Build)        | 9.99     | root      |                        | data:Selection_41                              |
| │ └─Selection_41               | 9.99     | cop[tikv] |                        | eq(test.t2.code, 0), not(isnull(test.t2.id))   |
| │   └─TableFullScan_40         | 10000.00 | cop[tikv] | table:t2               | keep order:false, stats:pseudo                 |
| └─PartitionUnion_15(Probe)     | 39960.00 | root      |                        |                                                |
|   ├─TableReader_18             | 9990.00  | root      |                        | data:Selection_17                              |
|   │ └─Selection_17             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_16       | 10000.00 | cop[tikv] | table:t1, partition:p0 | keep order:false, stats:pseudo                 |
|   ├─TableReader_24             | 9990.00  | root      |                        | data:Selection_23                              |
|   │ └─Selection_23             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_22       | 10000.00 | cop[tikv] | table:t1, partition:p1 | keep order:false, stats:pseudo                 |
|   ├─TableReader_30             | 9990.00  | root      |                        | data:Selection_29                              |
|   │ └─Selection_29             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|   │   └─TableFullScan_28       | 10000.00 | cop[tikv] | table:t1, partition:p2 | keep order:false, stats:pseudo                 |
|   └─TableReader_36             | 9990.00  | root      |                        | data:Selection_35                              |
|     └─Selection_35             | 9990.00  | cop[tikv] |                        | not(isnull(test.t1.id))                        |
|       └─TableFullScan_34       | 10000.00 | cop[tikv] | table:t1, partition:p3 | keep order:false, stats:pseudo                 |
+--------------------------------+----------+-----------+------------------------+------------------------------------------------+
17 rows in set, 1 warning (0.00 sec)

From example 3, you can see that even if the TIDB_INLJ hint is used, the query on the partitioned table cannot select the execution plan with IndexJoin.

Example 4: In the following example, the query is performed in dynamic mode using the execution plan with IndexJoin:

mysql> set @@tidb_partition_prune_mode = 'dynamic';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select /*+ TIDB_INLJ(t1, t2) */ t1.* from t1, t2 where t2.code = 0 and t2.id = t1.id;
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
| id                              | estRows  | task      | access object          | operator info                                                                                                       |
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
| IndexJoin_11                    | 12.49    | root      |                        | inner join, inner:IndexLookUp_10, outer key:test.t2.id, inner key:test.t1.id, equal cond:eq(test.t2.id, test.t1.id) |
| ├─TableReader_16(Build)         | 9.99     | root      |                        | data:Selection_15                                                                                                   |
| │ └─Selection_15                | 9.99     | cop[tikv] |                        | eq(test.t2.code, 0), not(isnull(test.t2.id))                                                                        |
| │   └─TableFullScan_14          | 10000.00 | cop[tikv] | table:t2               | keep order:false, stats:pseudo                                                                                      |
| └─IndexLookUp_10(Probe)         | 1.25     | root      | partition:all          |                                                                                                                     |
|   ├─Selection_9(Build)          | 1.25     | cop[tikv] |                        | not(isnull(test.t1.id))                                                                                             |
|   │ └─IndexRangeScan_7          | 1.25     | cop[tikv] | table:t1, index:id(id) | range: decided by [eq(test.t1.id, test.t2.id)], keep order:false, stats:pseudo                                      |
|   └─TableRowIDScan_8(Probe)     | 1.25     | cop[tikv] | table:t1               | keep order:false, stats:pseudo                                                                                      |
+---------------------------------+----------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

From example 4, you can see that in dynamic mode, the execution plan with IndexJoin is selected when you execute the query.