This topic describes the partitioning functions that are supported by PolarDB-X databases in automatic partitioning mode.
Terms
partition key: A partition key contains one or more columns based on which PolarDB-X horizontally partitions a table.
partition key column: A partition key column is a column based on which PolarDB-X splits data in a table into partitions. A partition key consists of one or more partition key columns.
vector partition key: A vector partition key consists of one or more partition key columns, such as (c1,c2,c3) in
Partition by Hash(c1,c2,c3).single-column partition key: A single-column partition key contains only one partition key column, such as c1 in
Partition by Hash(c1).partitioning function: A partitioning function uses partition key columns as input parameters and returns results based on which PolarDB-X routes data to the partitions of a table. Example:
Partition by Hash(To_Days(c1)).monotonicity: The monotonicity of a partitioning function describes the change trend of the value that is returned by the func(x) function when the value of the independent variable x increases or decreases. A partitioning function may have monotonicity of one of the following types. Only partitioning functions with strict monotonicity or non-strict monotonicity support partition pruning for range queries.
Strict monotonicity: If the value of the independent variable x is greater than that of the independent variable y, the value returned by the func(x) function is greater than that by the func(y) function. If the value of the independent variable x is smaller than that of the independent variable y, the value returned by the func(x) function is smaller than that by the func(y) function.
Non-strict monotonicity: If the value of the independent variable x is greater than that of the independent variable y, the value returned by the func(x) function is greater than or equal to that by the func(y) function. If the value of the independent variable x is smaller than that of the independent variable y, the value returned by the func(x) function is smaller than or equal to that by the func(y) function.
No monotonicity: If the value of the independent variable x is greater than that of the independent variable y, the value returned by the func(x) function may not be greater than or equal to that by the func(y) function.
Limits
The following table describes the partitioning functions that are supported by PolarDB-X, versions of PolarDB-X that support the partitioning functions, monotonicity of the partitioning functions, and data types of partition key columns that are supported by the partitioning functions.
Partitioning function | Description | Supported version | Monotonicity | Supported data types of partition key columns | ||
Integer types1 | Date and time types2 | String types3 | ||||
| Returns the year of the input date. For more information, see YEAR(date) in MySQL documentation. | 5.4.13 and later | Non-strict monotonicity | ❌ | ✅ | ❌ |
| Returns the month of the input date. For more information, see MONTH(date) in MySQL documentation. | 5.4.13 and later | No monotonicity | ❌ | ✅ | ❌ |
| Returns the number of days from January 1, 0000 to the input date. For more information, see TO_DAYS(date) in MySQL documentation. | 5.4.13 and later | Non-strict monotonicity | ❌ | ✅ | ❌ |
| Returns the number of months from January 1, 0000 to the input date. This function is a PolarDB-X-specific function. | 5.4.16-16717637 and later | Non-strict monotonicity | ❌ | ✅ | ❌ |
| Returns the number of weeks from January 1, 0000 to the input date. This function is a PolarDB-X-specific function. | 5.4.16-16717637 and later | Non-strict monotonicity | ❌ | ✅ | ❌ |
| Returns the number of seconds from January 1, 0000, 00:00:00 to the input time. For more information, see TO_SECONDS(expr) in MySQL documentation. | 5.4.13 and later | Non-strict monotonicity | ❌ | ✅ | ❌ |
| Returns the number of seconds from January 1, 1970, 00:00:00 UTC to the input time. For more information, see UNIX_TIMESTAMP([date]) in MySQL documentation. | 5.4.13 and later | Non-strict monotonicity | ❌ | ✅ | ❌ |
| Returns the week number of the input date. For example, 1 is returned for Sunday, 2 is returned for Monday, and 7 is returned for Saturday. For more information, see DAYOFWEEK(date) in MySQL documentation. | 5.4.16-16717637 and later | No monotonicity | ❌ | ✅ | ❌ |
| Returns the day of the input date in the current month. Valid values: 1 to 31. For more information, see DAYOFMONTH(date) in MySQL documentation. | 5.4.16-16717637 and later | No monotonicity | ❌ | ✅ | ❌ |
| Returns the day of the input date in the current year. Valid values: 1 to 366. For more information, see DAYOFYEAR(date) in MySQL documentation. | 5.4.16-16717637 and later | No monotonicity | ❌ | ✅ | ❌ |
SUBSTR/SUBSTRING | Returns a substring of the input string. For more information, see SUBSTR() and SUBSTRING() in MySQL documentation. | 5.4.16-16717637 and later | No monotonicity | ❌ | ❌ | ✅ |
| Returns the rightmost substring of n characters in length from the input string. For more information, see RIGHT(str,len) in MySQL documentation. | 5.4.18-17034692 and later | No monotonicity | ❌ | ❌ | ✅ |
| Returns the leftmost substring of n characters in length from the input string. For more information, see LEFT(str,len) in MySQL documentation. | 5.4.18-17034692 and later | No monotonicity | ❌ | ❌ | ✅ |
1. Integer types: BIGINT, BIGINT UNSINGEDINT, INT, INT UNSINGED, MEDIUMINT, MEDIUMINT UNSINGED, SMALLINT, SMALLINT UNSINGED, TINYINT, and TINYINT UNSINGED
2. Date and time types: DATETIME, DATE, and TIMESTAMP
3. String types: CHAR and VARCHR
The following table describes the partitioning policies to which the partitioning functions supported by PolarDB-X can apply.
Partitioning function | KEY partitioning | HASH partitioning | RANGE partitioning | RANGE COLUMNS partitioning | LIST partitioning | LIST COLUMNS partitioning | CO_HASH partitioning | ||
Single-column partition key | Vector partition key | Single-column partition key | Vector partition key | ||||||
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
TO_SECOND | ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ❌ |
SUBSTR/SUBSTRING | ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ |
| ❌ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ | ❌ | ✅ |