概念描述

  在MySQL8.0的分区表中，范围分区是按照指定的范围值来划分数据，将数据分布在不同分区表中的一种分区方式。
  范围分区的分区列值的范围必须是连续的，且不能有重叠部分，通过 VALUES LESS THAN 来定义分区范围。

测试举例

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
)
PARTITION BY RANGE (partition_column)
(
    PARTITION partition_name VALUES LESS THAN (value1),
    PARTITION partition_name VALUES LESS THAN (value2),
    ...
);

例如，以下是按照 store_id划分范围的分区表

-- 示例1：
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    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
);

-- 示例2：
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    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)
);

-- 示例3：
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL 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
);

运维操作

-- 1. 查询数据：通过分区裁剪来查询，按分区键的值自动定位到相应分区中进行查询
root@localhost:testonly 05:14:02 >select * from tndate where dt > '1990-01-01' and dt < '2000-12-01';
+------+------------+
| id   | dt         |
+------+------------+
|    5 | 1998-01-01 |
|    6 | 1999-01-01 |
+------+------------+
2 rows in set (0.00 sec)

-- 2. 查询数据：通过指定分区名来查询分区中的数据
root@localhost:testonly 05:14:42 >select * from tndate partition(p1);
+------+------------+
| id   | dt         |
+------+------------+
|    5 | 1998-01-01 |
|    6 | 1999-01-01 |
+------+------------+

root@localhost:testonly 05:17:28 >select * from tndate partition(p0,p1);
+------+------------+
| id   | dt         |
+------+------------+
|    3 | NULL       |
|    4 | 1989-01-24 |
|    5 | 1998-01-01 |
|    6 | 1999-01-01 |
+------+------------+
4 rows in set (0.00 sec)

--查询分区信息：information_schema.partitions表
mysql >select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,PARTITION_METHOD,SUBPARTITION_METHOD,PARTITION_EXPRESSION,TABLE_ROWS,DATA_LENGTH,CREATE_TIME from information_schema.partitions where table_schema='testonly' and partition_name is not null;
+--------------+------------+----------------+-------------------+------------------+---------------------+----------------------+------------+-------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | TABLE_ROWS | DATA_LENGTH | CREATE_TIME         |
+--------------+------------+----------------+-------------------+------------------+---------------------+----------------------+------------+-------------+---------------------+
| testonly     | tndate     | p0             | NULL              | RANGE            | NULL                | year(`dt`)           |          2 |       16384 | 2025-01-24 17:36:39 |
| testonly     | tndate     | p1             | NULL              | RANGE            | NULL                | year(`dt`)           |          2 |       16384 | 2025-01-24 17:36:39 |
| testonly     | tndate     | p10            | NULL              | RANGE            | NULL                | year(`dt`)           |          0 |       16384 | 2025-01-24 17:36:39 |
| testonly     | tndate     | p20            | NULL              | RANGE            | NULL                | year(`dt`)           |          0 |       16384 | 2025-01-24 17:36:39 |
| testonly     | tndate     | pmax           | NULL              | RANGE            | NULL                | year(`dt`)           |          0 |       16384 | 2025-01-24 17:36:39 |
+--------------+------------+----------------+-------------------+------------------+---------------------+----------------------+------------+-------------+---------------------+
5 rows in set (0.00 sec)

-- 删除分区
ALTER TABLE tndate DROP PARTITION p0;
说明：drop分区会删除对应分区中的数据，生产中谨慎使用！

-- 添加分区
mysql> alter table tndate reorganize partition p2 into(
    -> partition p10  values less than (2020),
    -> partition p20  values less than (2030),
    -> partition pmax values less than maxvalue );
       Query OK, 0 rows affected (0.04 sec)
       Records: 0  Duplicates: 0  Warnings: 0

-- 重建分区
mysql > ALTER TABLE tndate REBUILD PARTITION p10, p20;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 优化分区
ALTER TABLE tndate OPTIMIZE PARTITION p10, p20;

--分析分区表
ALTER TABLE tndate ANALYZE PARTITION p10;

Range Columns分区

  在MySQL8.0中 Range Columns分区是一种特殊的范围分区，它允许根据多个列值的范围将数据分配到不同分区中，它比普通范围分区更加灵活，支持非整数类型的列如字符串，date,datetime等。

Range Columns分区示例：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE COLUMNS(year(order_date), month(order_date)) (
    PARTITION p201901 VALUES LESS THAN (2019, 2),
    PARTITION p201902 VALUES LESS THAN (2019, 3),
    PARTITION p201903 VALUES LESS THAN (2019, 4),
    PARTITION pFuture VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

管理分区：

-- 添加分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p202001 VALUES LESS THAN (2020, 2)
);

-- 删除分区：
ALTER TABLE orders DROP PARTITION p201901;

-- 查询分区表：
SELECT * FROM orders WHERE year(order_date) = 2019 AND month(order_date) = 5;

知识总结

1. VALUE LESS THAN： VALUE LESS THAN是在定义范围分区时使用的核心语法也是关键字，用于指定每个分区的范围上限。
2. MAXVALUE： MAXVALUE 是一个特殊的值，用于表示可能的最大值。当分区范围中包含 MAXVALUE 时，所有大于或等于前一个分区值的数据都会被放入该分区。
3. NULL： 在 MySQL中，NULL 是一个特殊的值，表示“未知”或“不存在”。它既不是数值，也不参与普通的比较运算。然而，在范围分区中，MySQL 需要为 NULL 值分配一个明确的存储位置。为了保持分区逻辑的一致性和简单性，MySQL 选择将 NULL 值视为“小于任何值”，并将其存储到最小的分区中。
4. 分区数量： MySQL8.0中分区的数量最大为8192个分区，这个数字包含子分区的数量。
5. 分区键的限制：
  MySQL分区表中，分区键必须是主键或者唯一键的一部分
  MySQL分区表中，主键或者唯一键必须包含分区键的所有列
  MySQL分区表中，如表没有唯一键或主键，分区键的选择更加灵活，只要列类型与分区类型兼容即可
  MySQL分区表中，分区表不支持 FULLTEXT 索引
  MySQL分区表中，分区表中不能使用空间数据类型（如 POINT 或 GEOMETRY）
  MySQL分区表中，临时表不能分区
  MySQL分区表中，MySQL8.0分区表不支持外键

参考文档

https://dev.mysql.com/doc/refman/8.0/en/partitioning-range.html