mysql表分区

分区表是指将表按照创建时所指定的规则（如按时间、按范围等）来进行分区，即将一个大表拆分成多个小表，每个小表称为一个分区，并且每个分区可以独立存储于不同的物理介质上，从而在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。

表分区的创建

按照 Partitioning Types 分类，大致分为如下几类：

1. RANGE 范围分区

int 数字类型

按 int 数字 range 进行分区

#.按int范围进行分区
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (20),
    PARTITION p3 VALUES LESS THAN (30),
    PARTITION p4 VALUES LESS THAN MAXVALUE
  );

#.插入测试数据
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
SELECT * FROM employees order by id;

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p2 分区只有 aaa 这条记录
SELECT * FROM employees PARTITION (p2) WHERE id < 100 LIMIT 10;

date 日期类型

按 date 日期 range 进行分区，需将其转为数字类型

#.按时间范围进行分区：时间类型 timestamp 本身就是数字
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime timestamp DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY RANGE (UNIX_TIMESTAMP(inserttime)) (
    PARTITION p20180101 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-02')),
    PARTITION p20190101 VALUES LESS THAN (UNIX_TIMESTAMP('2019-01-02')),
    PARTITION p20200101 VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-02')),
    PARTITION p99991231 VALUES LESS THAN MAXVALUE
);

#.按时间范围进行分区：时间类型 datetime 用 TO_DAYS() 转换为数字
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(inserttime)) (
    PARTITION p20180101 VALUES LESS THAN (TO_DAYS('2018-01-02')),
    PARTITION p20190101 VALUES LESS THAN (TO_DAYS('2019-01-02')),
    PARTITION p20200101 VALUES LESS THAN (TO_DAYS('2020-01-02')),
    PARTITION p99991231 VALUES LESS THAN MAXVALUE
);

#.按时间范围进行分区：时间类型 datetime 用 YEAR() 转换为数字
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY RANGE (YEAR(inserttime)) (
    PARTITION p20180101 VALUES LESS THAN (2018),
    PARTITION p20190101 VALUES LESS THAN (2019),
    PARTITION p20200101 VALUES LESS THAN (2020),
    PARTITION p99991231 VALUES LESS THAN MAXVALUE
);

通过测试数据进行分区验证

#.插入测试数据
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
SELECT * FROM employees order by id;

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p20190101 分区只有 aaa 这条记录
SELECT * FROM employees PARTITION (p20190101) WHERE id < 100 LIMIT 10;

2. LIST 列表分区

#.按list散点值进行分区
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY LIST (id) (
    PARTITION p1 VALUES IN (11,12,13),
    PARTITION p2 VALUES IN (21,22,23),
    PARTITION p3 VALUES IN (31,32,33)
  );

#.只能插入分区指定的的值, 示例不存在 id=41 则插入失败
INSERT INTO employees(id,name,inserttime) VALUES(41,'ccc','2025-04-17 00:00:000');

#.以下插入成功, 因为 id=11 满足 p1 分区
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p1 分区只有 aaa 这条记录
SELECT * FROM employees PARTITION (p1) WHERE id < 100 LIMIT 10;

3. COLUMNS 列分区

RANGE COLUMNS 分区

以下是针对单个字段进行 RANGE COLUMNS 分区：

#.按range范围进行分区：时间类型 datetime 用 COLUMNS ()
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY RANGE COLUMNS (inserttime) (
    PARTITION p20180101 VALUES LESS THAN ('2018-01-02'),
    PARTITION p20190101 VALUES LESS THAN ('2019-01-02'),
    PARTITION p20200101 VALUES LESS THAN ('2020-01-02'),
    PARTITION p99991231 VALUES LESS THAN MAXVALUE
);

#.插入测试数据
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
SELECT * FROM employees order by id;

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p20190101 分区只有 aaa 这条记录
SELECT * FROM employees PARTITION (p20190101) WHERE id < 100 LIMIT 10;

以下是针对多个字段进行 RANGE COLUMNS 分区：

#.创建测试表
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (a int, b int, c varchar(4))
  PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p1 VALUES LESS THAN (5, 12),
    PARTITION p2 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

#.插入测试数据并验证分区
mysql> INSERT INTO employees VALUES (5,10,'a'), (5,11,'b'), (5,12,'c'),(6,13,'d');
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='employees';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p1             |          2 |
| p2             |          2 |
+----------------+------------+

LIST COLUMNS 分区

#.准备测试表
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY LIST COLUMNS (name) (
    PARTITION p1 VALUES IN ('aaa'),
    PARTITION p2 VALUES IN ('bbb'),
    PARTITION p3 VALUES IN ('ccc'),
    PARTITION p4 VALUES IN ('ddd')
);

#.插入测试数据
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
SELECT * FROM employees order by id;

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p1 分区只有 aaa 这条记录
SELECT * FROM employees PARTITION (p1) WHERE id < 100 LIMIT 10;

4. HASH 哈希分区

根据 inserttime的年份进行均匀分布的分区

#.按 hash 进行分区
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY HASH (YEAR(inserttime)) PARTITIONS 4;

#.插入测试数据
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(41,'ddd','2021-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(51,'eee','2022-01-31 00:00:000');
SELECT * FROM employees order by id;

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p2 分区有 aaa,eee 这2条记录
SELECT * FROM employees PARTITION (p2) WHERE id < 100 LIMIT 10;

LINEAR HASH 分区

#.按 linear hash 进行分区
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY LINEAR HASH (YEAR(inserttime)) PARTITIONS 4;

#.插入测试数据
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(41,'ddd','2021-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(51,'eee','2022-01-31 00:00:000');
SELECT * FROM employees order by id;

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p2 分区有 aaa,eee 这2条记录
SELECT * FROM employees PARTITION (p2) WHERE id < 100 LIMIT 10;

5. KEY 键分区

使用 KEY Partitioning 的前提是该表有 PRIMARY KEY 或 UNIQUE KEY

#.按key进行分区：
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10) NOT NULL PRIMARY KEY, name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY KEY () PARTITIONS 4;

#.插入测试数据
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
SELECT * FROM employees order by id;

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p2 分区有 aaa,ccc 这2条记录
SELECT * FROM employees PARTITION (p2) WHERE id < 100 LIMIT 10;

6. SUBPARTITION 子分区

#.按子分区进行分区
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) 
  ENGINE=InnoDB PARTITION BY RANGE( YEAR(inserttime) )
    SUBPARTITION BY HASH( TO_DAYS(inserttime) )
    SUBPARTITIONS 2 (
        PARTITION p1 VALUES LESS THAN (2018),
        PARTITION p2 VALUES LESS THAN (2019),
        PARTITION p3 VALUES LESS THAN MAXVALUE
    );

#.插入测试数据
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
SELECT * FROM employees order by id;

#.查看分区规则及数据分布
SELECT table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name='employees';

#.查看特定分区满足条件的数据,示例 p3 分区有 bbb,ccc 这2条记录
SELECT * FROM employees PARTITION (p3) WHERE id < 100 LIMIT 10;

表分区的维护

1. 查看分区信息

#.指定分区查询
SELECT * FROM employees PARTITION (p2) WHERE id < 100 LIMIT 10; 

#.查看分区信息
EXPLAIN PARTITIONS SELECT * FROM employees;

#.查看分区规则及数据分布
SELECT table_schema,table_name,partition_name,table_rows FROM information_schema.partitions WHERE table_name = 'employees';

#.查看分区规则及数据分布，按时间类型分区 from_unixtime(partition_description) 才有效
SELECT table_schema,table_name,partition_name,partition_expression,from_unixtime(partition_description)
,table_rows,avg_row_length,data_length/1024/1024/1024 as data_size,index_length/1024/1024/1024 as index_size 
FROM information_schema.partitions WHERE table_name = 'employees';

2. 表分区的增删改

#.1.创建基础表.
DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees(id int(10), name nvarchar(20), inserttime datetime DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
INSERT INTO employees(id,name,inserttime) VALUES(11,'aaa','2018-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(21,'bbb','2019-01-31 00:00:000');
INSERT INTO employees(id,name,inserttime) VALUES(31,'ccc','2020-01-31 00:00:000');
SELECT * FROM employees order by id;

#.2.向基础表创建分区（分区必须覆盖所有已存在的数值）
#ALTER TABLE employees PARTITION BY RANGE (id) (PARTITION p10 VALUES LESS THAN (10), PARTITION p20 VALUES LESS THAN (20));
ALTER TABLE employees PARTITION BY RANGE (id) (PARTITION p20 VALUES LESS THAN (20), PARTITION p100 VALUES LESS THAN (100));

#.3.已分区表添加分区（新的分区必须大于已有分区最大值）
ALTER TABLE employees ADD PARTITION (PARTITION p110 VALUES LESS THAN (110));

#.4.插入数据测试（满足分区条件的成功，超出分区范围的失败）
mysql> INSERT INTO employees(id,name,inserttime) VALUES(101,'ddd','2021-01-31 00:00:000');
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO employees(id,name,inserttime) VALUES(201,'eee','2022-01-31 00:00:000');    
ERROR 1526 (HY000): Table has no partition for value 201

#.5.合并多个分区为一个（将 p100,p110 合并为 p200）
ALTER TABLE employees REORGANIZE PARTITION p100,p110 INTO (PARTITION p200 VALUES LESS THAN (200));

#.6.分解一个已有分区（将 p200 分解为 p100, p200）
ALTER TABLE employees REORGANIZE PARTITION p200 INTO (PARTITION p100 VALUES LESS THAN (100), PARTITION p200 VALUES LESS THAN (200));

#.7.特定分区的重建与优化
ALTER TABLE employees REBUILD PARTITION p100, p200;
ALTER TABLE employees OPTIMIZE PARTITION p100, p200;
ALTER TABLE employees ANALYZE PARTITION p100;
ALTER TABLE employees REPAIR PARTITION p100, p200;
ALTER TABLE employees CHECK PARTITION p200;

#.8.删除某个分区,同时删数据
#.对于有大量行的表，TRUNCATE PARTITION 比运行 DELETE FROM employees WHERE inserttime <= '2018-08-01'; 这样的DELETE查询要有效得多
#.但它不会返回删除了多少行，可以通过select count()语句查看
ALTER TABLE employees TRUNCATE PARTITION p200;

#.9.移除所有分区,不删数据
ALTER TABLE employees REMOVE PARTITIONING;

附录

MySQL 使用表分区，需要注意如下几点：

创建分区的时候，必须指定至少一个分区
RANGE 处必须为INT型，时间字段用函数转 YEAR()、YEARWEEK()、TO_DAYS()
THAN 处必须为INT型，时间字段用函数转 TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()
范围分区添加只能在最大值后面追加
mysql5.6.7之前，单表最多1024个分区；5.6.7开始，单表最多8192个分区
使用单个分区查询时，查询语句必须指定具体的分区名
当查询语句涉及到多个分区时，MySQL会自动选择需要进行查询的分区
分区表无法使用外键约束
NULL值会使分区过滤无效
所有分区必须使用相同的存储引擎

MySQL支持多种类型的分区，每种类型适用于不同的场景：

RANGE分区：基于一个列值的范围来分配数据。适用于自增id、时间戳或日期字段
LIST分区：基于列值属于预定义列表中的一个值来分配数据
HASH分区：根据用户定义的表达式的返回值进行分区，通常用于均匀分布数据
KEY分区：类似于HASH分区，但是MySQL系统会自动计算分区键值，适用于主键或唯一键
COLUMNS分区：允许使用多列作为分区键，支持RANGE和LIST分区

参考文章：

MySQL 5.7 Reference Manual / RANGE Partitioning

mysql表分区

mysql表分区

表分区的创建

1. RANGE 范围分区

int 数字类型

date 日期类型

2. LIST 列表分区

3. COLUMNS 列分区

RANGE COLUMNS 分区

LIST COLUMNS 分区

4. HASH 哈希分区

LINEAR HASH 分区

5. KEY 键分区

6. SUBPARTITION 子分区

表分区的维护

1. 查看分区信息

2. 表分区的增删改

附录

results matching ""

No results matching ""