Range partitioning is a convenient method for historical data. In range partitioning, boundary values are used to define the range and the order of partitions in a table or an index.
Range partitioning is usually used to organize data at time intervals on columns of the DATE type. Therefore, most SQL statements that access range partitions focus on time ranges. The following example is a scenario similar to the SQL statement to select data within a specific time period. If each partition represents the data of one month, the query of 21-12 data must access the partition of December 2021. Only a fraction of the total available data is scanned. This optimization method is called partition pruning.
Range partitioning is also ideal for scenarios where new data is loaded and old data is cleared on a regular basis. For example, a tumbling window is usually retained to query the data of the past 36 months. Range partitioning simplifies this process. To add the data of a new month, you must load it into a separate table, clear it, index it, and then execute the EXCHANGE PARTITION statement to add it to the range partitioned table while the original table still can be queried. After you add a new partition, you can execute the DROP PARTITION statement to delete the partition of the last month.
You can use range partitioning in the following cases:
Some columns are often scanned by range in very large tables, such as order table ORDER or purchase schedule LINEITEM. Partition pruning can be implemented by partitioning tables on these columns.
A tumbling window is maintained.
Management operations such as backup and restoration on large tables cannot be completed within the specified time range, but large tables can be divided into smaller logical blocks by range.
Example: Create the orders table spanning more than nine years and partition it by range based on the o_orderdate column. Divide the data into eight years, with one partition for each year. Partition pruning can be used to analyze sales data at short intervals. The tumbling window method is supported.
CREATE TABLE `orders` (
`o_orderkey` int(11) NOT NULL,
`o_custkey` int(11) NOT NULL,
`o_orderstatus` char(1) DEFAULT NULL,
`o_totalprice` decimal(10,2) DEFAULT NULL,
`o_orderDATE` date NOT NULL,
`o_orderpriority` char(15) DEFAULT NULL,
`o_clerk` char(15) DEFAULT NULL,
`o_shippriority` int(11) DEFAULT NULL,
`o_comment` varchar(79) DEFAULT NULL,
PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
KEY `o_orderkey` (`o_orderkey`),
KEY `i_o_custkey` (`o_custkey`),
KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(o_orderdate))
(PARTITION item1 VALUES LESS THAN (TO_DAYS('1992-01-01')),
PARTITION item2 VALUES LESS THAN (TO_DAYS('1993-01-01')),
PARTITION item3 VALUES LESS THAN (TO_DAYS('1994-01-01')),
PARTITION item4 VALUES LESS THAN (TO_DAYS('1995-01-01')),
PARTITION item5 VALUES LESS THAN (TO_DAYS('1996-01-01')),
PARTITION item6 VALUES LESS THAN (TO_DAYS('1997-01-01')),
PARTITION item7 VALUES LESS THAN (TO_DAYS('1998-01-01')),
PARTITION item8 VALUES LESS THAN (TO_DAYS('1999-01-01')),
PARTITION item9 VALUES LESS THAN (MAXVALUE));
EXPLAIn select * from orders where o_orderDATE = '1992-03-01';
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | item2 | ref | i_o_orderdate | i_o_orderdate | 3 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------------+---------+-------+------+----------+-------+Due to the limits of the RANGE type, the original DDL statement may not be displayed after you partition the table by using TO_DAYS() and execute the SHOW CREATE TABLE statement. Example:
show create table orders;
| orders | CREATE TABLE `orders` (
`o_orderkey` int(11) NOT NULL,
`o_custkey` int(11) NOT NULL,
`o_orderstatus` char(1) DEFAULT NULL,
`o_totalprice` decimal(10,2) DEFAULT NULL,
`o_orderDATE` date NOT NULL,
`o_orderpriority` char(15) DEFAULT NULL,
`o_clerk` char(15) DEFAULT NULL,
`o_shippriority` int(11) DEFAULT NULL,
`o_comment` varchar(79) DEFAULT NULL,
PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
KEY `o_orderkey` (`o_orderkey`),
KEY `i_o_custkey` (`o_custkey`),
KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`o_orderDATE`))
(PARTITION item1 VALUES LESS THAN (727563),
PARTITION item2 VALUES LESS THAN (727929),
PARTITION item3 VALUES LESS THAN (728294),
PARTITION item4 VALUES LESS THAN (728659),
PARTITION item5 VALUES LESS THAN (729024),
PARTITION item6 VALUES LESS THAN (729390),
PARTITION item7 VALUES LESS THAN (729755),
PARTITION item8 VALUES LESS THAN (730120),
PARTITION item9 VALUES LESS THAN MAXVALUE) */ |Range columns partitioning is an extension of range partitioning. Multiple column values are defined for range partitioning. Non-integer columns can be selected as partition key columns.
Range columns partitioning differs from range partitioning in the following ways:
In RANGE COLUMNS(), you can specify only column names, rather than expressions.
RANGE COLUMNS() can contain one or more columns. Range columns partitioning compares tuples instead of scalar values. The row position in range columns partitioning is also based on the comparison between tuples.
In range columns partitioning, partition key columns can be of the INTEGER, STRING, DATE, and DATETIME types.
Create a range columns partitioned table:
CREATE TABLE `orders` (
`o_orderkey` int(11) NOT NULL,
`o_custkey` int(11) NOT NULL,
`o_orderstatus` char(1) DEFAULT NULL,
`o_totalprice` decimal(10,2) DEFAULT NULL,
`o_orderDATE` date NOT NULL,
`o_orderpriority` char(15) DEFAULT NULL,
`o_clerk` char(15) DEFAULT NULL,
`o_shippriority` int(11) DEFAULT NULL,
`o_comment` varchar(79) DEFAULT NULL,
PRIMARY KEY (`o_orderkey`,`o_orderDATE`,`o_custkey`),
KEY `o_orderkey` (`o_orderkey`),
KEY `i_o_custkey` (`o_custkey`),
KEY `i_o_orderdate` (`o_orderDATE`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
PARTITION item2 VALUES LESS THAN ('1993-01-01'),
PARTITION item3 VALUES LESS THAN ('1994-01-01'),
PARTITION item4 VALUES LESS THAN ('1995-01-01'),
PARTITION item5 VALUES LESS THAN ('1996-01-01'),
PARTITION item6 VALUES LESS THAN ('1997-01-01'),
PARTITION item7 VALUES LESS THAN ('1998-01-01'),
PARTITION item8 VALUES LESS THAN ('1999-01-01'),
PARTITION item9 VALUES LESS THAN (MAXVALUE));