mysql数据库分区

最新推荐文章于 2024-06-18 22:00:59 发布

五月天的尾巴

最新推荐文章于 2024-06-18 22:00:59 发布

阅读量2.6k

点赞数 1

分类专栏：数据库文章标签：数据库分区 mysql数据库分区

本文链接：https://blog.csdn.net/weixin_49114503/article/details/132050910

版权

数据库专栏收录该内容

35 篇文章 2 订阅

订阅专栏

文章目录

一、什么是分区
二、分区类型
三、分区字段和主键
四、分区使用
五、分区使用可能遇到的问题
- 5.1、[HY000][1493] VALUES LESS THAN value must be strictly increasing for each partition
- 5.2、ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions

本人安装的mysql版本为8.0.34社区版，以下若有涉及到sql执行，大多数都是基于此版本进行讲解。

一、什么是分区

所谓分区，就是将一个表分成多个区块进行操作和保存，从而降低每次操作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是独立的对象，可以进行独立处理。简而言之：未分区的表所有数据物理层面存储在同一个文件中，分区的表会按分区数量存储到不同的文件中，提高查询效率

以下为分区和不分区物理层面的区别。
在这里插入图片描述

分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。

MySQL数据库在5.1版本及以上时添加了对分区的支持，分区功能并不是在存储引擎层完成的，常见的存储引擎InnoDB、MyISAM、NDB等都支持分区。但是并不是所有的存储引擎都支持，如CSV、FEDORATED、MERGE等就不支持分区。mysql8及以上只有存储引擎InnoDB、NDB支持分区。

更多分区内容详见官网Partitioning

1.1、分区类型（水平分区、垂直分区）

水平分区：一个表的行可以分配给不同的物理分区。
垂直分区：将表的不同列分配给不同的物理分区。(MySQL 8.0 不支持垂直分区，目前没有计划在 MySQL 中引入垂直分区)

此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。

1.2、表分区的优缺点

分区的好处：

可以让单表存储更多的数据。
分区表的数据更容易维护，可以通过清除整个分区来批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。
部分查询能够从查询条件确定只落在少数分区上，速度会很快（查询条件尽量扫描少的分区）。
分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备。
可以使用分区表来避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争。
可以备份和恢复单个分区。
对于查询分区表的SELECT语句，建议包含分区列，这样就只会在这个分区内查询，不用全表搜索，效率更快

分区的限制和缺点：

在mysql5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。
如果一个表有主键，那么MYSQL的分区字段必须包含在主键内，也就是分区字段必须是主键的一部分或者全部，不能以非主键的字段作为分区字段。当然，也可以为没有主键的表建立分区。
分区表无法使用外键约束。
NULL值会使分区过滤无效。
所有分区必须使用相同的存储引擎。

1.3、分表和表分区的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如银行交易流水记录根据时间每月一个表分成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表（虽然在物理层面上是有多个表文件），而分表则是将一张表分解成多张表。

如下：创建分区表后物里层面会有多个表文件

create table if not exists product
(
	id int not null ,
	name varchar(20) null comment '商品名称',
	type varchar(20) not null comment '商品类型',
    primary key(id,type)
) partition by key(type) partitions 4;  -- 根据商品类型进行分区

在这里插入图片描述

二、分区类型

分区方式可分为四种：range分区、list分区、hash分区和key分区。
（1）range分区：按照连续的范围划分分区，例如按月份划分、比如 id 1-100的分配到一起，101-200的分配到一起，201-300的分配到一起等等
（2）list分区：按照离散的值列表划分分区，例如按照国家、省份、城市等信息划分。比如根据字段把值为1、3、5的放到一起，2、4、6的另外放到一起等等
（3）hash分区：根据用户自定义规则对数据进行散列划分。
（4）key分区：和hash分区类似，但是根据MySQL引擎自身的规则处理数据。
（5）columns 分区：5.5版本前的RANGE、LIST、HASH分区要求分区键必须是int；MySQL5.5及以上，支持非整形的RANGE和LIST分区，即：range columns 和 list columns (可以用字符串来进行分区)。

在使用分区表时，我们需要注意以下几个细节：
（1）如果表中存在primary key 或者 unique key 时，分区的列必须是paimary key或者unique key的一个组成部分，也就是说，分区函数的列只能从pk或者uk这些key中取子集
（2）如果表中不存在任何的paimary key或者unique key，则可以指定任何一个列作为分区列
（3）使用分区表时，必须指定一个分区列。分区列的数据类型必须与分区方式对应。
（4）如果需要创建多级分区，只需要在创建一级分区时，将第二级分区的信息一起写在括号中即可。
（5）对于查询分区表的SELECT语句，必须包含分区列，并且只查询特定分区的数据时，需要使用类似“SELECT * FROM orders PARTITION (p2010)”这样的语法。
（6）分区的命名默认是p0,p1,p2.........并且分区名称不区分大小写
（6）全表查询时，分区表默认排序是按分区排序，而不是按主键排序

2.1、RANGE分区（官方文档）

RANGE分区是最常用的一种分区类型，基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

RANG分区特点：

根据分区键的值的范围把不同数据存储到表的不同分区中。
多个分区的分区键的值的范围要连续，但是不能重叠。
默认情况下使用VALUES LESS THAN属性，但每个分区并不包括指定的那个值。

2.1.1、创建RANGE分区

下面创建一个以id列为区间分区表，当id小于10时，数据插入p0分区；当id大于等于10小于20时，数据插入p1分区。代码如下：

create  table  t(
  id int
)engine=innodb
partition by range(id)(
     partition  p0  values  less  than(10),-- 存放<10的数据
     partition  p1  values  less  than(20)-- 存放>=10  <20的数据
);

建立分区后，我们在插入数据时，mysql 会自动根据数据的值来将数据插插入到对应分区内。

insert into t values (9);
insert into t values (11);

插入上面数据后，查看分区：

-- 查看p0分区的数据
SELECT * FROM t PARTITION (p0);

结果如下：
在这里插入图片描述

在对表建立分区后，插入的数据的值应该严格遵守分区的定义，当插入一个不属于任何分区的值时，MySQL数据库会直接报错。如下：
在这里插入图片描述
[HY000][1526] Table has no partition for value 40

为了避免这种情况发生，我们可以对分区添加一个MAXVALUE值的分区，MAXVALUE可以理解为正无穷，因此所有大于等于20且小于MAXVALUE的值被放入p2分区。

alter table t add partition(partition p2 values less than maxvalue);

此时，所有 >=20 的值都会被插入到 p2 分区中。

2.1.2、对字段类型date, timestamp进行range分区

我就直接从官网上拿用例了，更多信息见官网
RANGE Partitioning

 CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

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)
);

2.2、LIST分区（官方文档）

LIST分区和RANGE分区类似，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择，而非连续的。

LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

LIST分区的特点：

按分区键取值的列表进行分区
同范围分区一样，各分区的列表值不能重复
每一行数据必须能找到对应的分区列表，否则数据插入失败

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 LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

2.3、HASH分区（官方文档）

HASH分区的特点：

根据MOD(分区键，分区数)的值把数据行存储到表的不同分区中
数据可以平均的分布在各个分区中
HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型

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 HASH( YEAR(hired) )
PARTITIONS 4;

如果你在employees 中插入一条记录，它的hired值是’2005-09- 15’，那么它所存储的分区如下所示：

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

2.4、key分区

按键分区类似于按散列分区，不同之处在于散列分区使用用户定义的表达式，用于键分区的散列函数由MySQL服务器提供。NDB集群使用MD5（）来实现此目的;对于使用其它存储引擎的表，服务器采用其自身的内部散列函数。

key分区类似于创建按哈希分区的表的方法。主要区别如下：

使用KEY而不是HASH。
KEY只接受零个或多个列名的列表。任何用作分区键的列都必须包含表的部分或全部主键（如果表有主键的话）。如果没有将列名指定为分区键，则使用表的主键（如果有）。例如，下面的CREATE TABLE语句在MySQL 8.0中是有效的：

示例1：

CREATE TABLE k1 (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20)
)
PARTITION BY KEY() -- 注意这里的key()没有指定列，则默认是按主键分区
PARTITIONS 2;

示例2：

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

2.5、COLUMNS分区

官网地址：RANGE COLUMNS partitioning / LIST COLUMNS partitioning
COLUMNS分区是在MYSQL5.5中引入的分区类型，引入COLUMNS分区的目的是为了解决MYSQL5.5之前版本的RANGE分区和LIST分区只支持整数分区，需要通过额外函数计算得到整数或者额外的转换表转换成整数在分区，COLUMNS分区可以细分为RANGE COLUMNS分区和LIST COLUMNS分区，它们都支持整数、日期、字符串三大数据类型：

所有整数类型：tinyint、smallint、mediumint、int（Integer）、bigint，其他数值类型都不支持，如Decimal和Float
日期时间类型：date、datetime
字符类型：char、varchar、binary、varbinary，不支持text和blob类型作为分区键
注意：COLUMNS分区仅支持一个或者多个字段名作为分区键，不支持表达式作为分区键。(和RANGE分区和LIST分区不同)，如下使用多列组合的RANGE COLUMNS分区：

2.5.1、range columns

RANGE COLUMNS分区在以下方面与RANGE分区有显著不同：

RANGE COLUMNS不接受表达式，只接受列的名称。
RANGE COLUMNS接受一个或多个列的列表。

语法：

CREATE TABLE table_name
PARTITION BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]

示例1：

CREATE TABLE rcx (
    a INT,
    b INT,
    c CHAR(3),
    d INT
)
PARTITION BY RANGE COLUMNS(a,d,c) (
    PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
    PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);

示例2：

-- 对日期类型的分区，不再需要year()函数了，而直接使用columns
create table t_columns(
a int,
b datetime
)engine=innodb
partition by range columns (b) (
partition p0 values less than ('2009-01-01'),
partition p1 values less than ('2010-01-01')
);

-- 可以直接使用字符串的分区
create table t_columns2(
a int,
b datetime,
city varchar(15)
)engine=innodb
partition by list columns(city) (
partition p1 values in ('a','b','c'),
partition p2 values in ('d','e','f'),
partition p3 values in ('g','h','k'),
);

-- 可以使用多个列进行分区：
create table rcx (
a int,
b int,
c char(3)
)engine=innodb
partition by range columns(a,b,c) (
partition p0 values 
);

三、分区字段和主键

MYSQL的分区字段，必须包含在主键字段或者唯一索引列中。如果一个表有主键或者唯一索引列，那么分区字段必须包含在主键或者唯一索引列内，也就是分区的字段必须全部都属于主键或者唯一索引列的一部分或者全部，不能以非主键且非唯一索引列的字段作为分区字段。当然，也可以为没有主键的表建立分区。

在对表进行分区时，如果分区字段没有包含在主键字段内那就会直接报错。

假设某个表主键为 id，我们想用 date 日期字段作为分区字段，此时必须得把 date 日期字段作为主键一部分才行，可以创建 (id, date) 的组合主键，否则创建分区将直接报错。

四、分区使用

4.1、查询数据库是否支持分区

从MySQL5.1开始引入分区功能，可以如下方式查看是否支持：

老版本（5.6之前）：

SHOW VARIABLES LIKE '%partition%';

如果输出：have_partitioning YES 则表示支持分区并且默认是开启状态。
在这里插入图片描述

新版本（5.6之后）：

show plugins;

显示所有插件，如果有partition - ACTIVE - STORAGE ENGINE - GPL 插件则表明支持分区
在这里插入图片描述

mysql8.0 SHOW PLUGINS 命令输出以及 INFORMATION_SCHEMA.PLUGINS 表不再显示 partition。

4.2、创建分区

create table if not exists product
(
	id int not null ,
	name varchar(20) null comment '商品名称',
	type varchar(20) not null comment '商品类型',
    primary key(id,type)
) partition by key(type) partitions 4;  -- 根据商品类型进行分区，创建4个分区


insert into product values
(1,'格力空调','空调'),
(2,'美的空调','空调'),
(3,'九阳电饭煲','电饭煲'),
(4,'苏泊尔电饭煲','电饭煲'),
(5,'小米手机','手机'),
(6,'华为手机','手机');

创建分区时，若只指定了分区数没有指定分区名称，则分区名称默认从0开始，p0,p1,p2…
在这里插入图片描述

4.3、创建分区并指定分区名称

create table if not exists tt
(
	id int not null primary key,
   name varchar(20) null comment '商品名称'
)engine=InnoDB partition by hash(id) partitions 3 (partition t1,partition t2,partition t3);

在这里插入图片描述

4.4、查看schema中哪些表是分区表/每个表有几个分区/已使用了几个分区/分区名称/每个分区内的数据量

select * 
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA=SCHEMA();

或者

select * 
FROM information_schema.PARTITIONS 
WHERE TABLE_SCHEMA='your schema name';

执行sql截取部门内容如下：
在这里插入图片描述
以上图片中我们能看到schema为test的库中所有表的信息：是否分区、分区名称、分区数量、分区类型(list,range,key,hash)、分区字段、每个分区内的数据量等等。

上面的sql是查询是schema为test的库中所有表的信息，并不仅仅是分区表的信息，当然我们可以修改一下sql语句，使之只查询分区表信息，如下：
在这里插入图片描述
注意：information_schema.partitions对于InnoDB表，table_rows行计数仅是大概估计值，不准确。所以有时候可能会有误差仅作为参考
更多内容可以参考官网The INFORMATION_SCHEMA PARTITIONS Table

4.5、查询表是否是分区表

方式一：通过show create table命令查看ddl语句
如下图，通过显示出来的ddl语句，发现该表是分区表

mysql> show create table product;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    58
Current database: test

+---------+-----------------------------------------------------------------------------------------+
| Table   | Create Table

                                                                    |
+---------+-----------------------------------------------------------------------------------------+
| product | CREATE TABLE `product` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL COMMENT '商品名称',
  `type` varchar(20) NOT NULL COMMENT '商品类型',
  PRIMARY KEY (`id`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY KEY (`type`)
PARTITIONS 4 */ |
+---------+-----------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

方式二：通过查询information_schema.PARTITIONS获得分区详细信息

select PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='product';

在这里插入图片描述

4.6、查询指定分区内的数据

从下图中我们可以看到products表中的数据都在p2,p3分区
在这里插入图片描述
查询单个分区内的数据

select * from product partition (p2) ;

在这里插入图片描述
注意，mysql5.5.41不支持对指定分区的查询，在5.6增强了分区表的分区的相关操作，其中包括支持了对指定分区的查询。

查询多个分区内的数据

select * from product partition (p2,p3) ;

在这里插入图片描述
分区与where组合查询

select * from product partition (p2,p3)  where id in (1,6);

在这里插入图片描述
有分区的表，查询数据默认按分区排序

CREATE TABLE `person` (
     `id` INT,
     `name` VARCHAR(50),
     `birthday` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(birthday) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000)
);

INSERT INTO person VALUES
(1, 'lava', '1998-12-25'),
(2, 'admin', '1993-11-05'),
(3, '张三', '1996-03-10'),
(4, '李四', '1982-01-10'),
(5, '王五', '1984-09-16'),
(6, '赵六', '1987-06-05'),
(7, 'tony', '1992-08-04');

如下图：id=4,5,6是p0分区；id=2,7是p1分区，id=1,3的p2分区
在这里插入图片描述

4.7、增加分区

-- range/list参考
alter table person add partition(PARTITION p6 VALUES LESS THAN (2020));

-- key/hash参考
alter table product add partition(PARTITION p6);
或者
alter table product add partition partitions 2;

注意：

只支持删除range、 list分区，不支持删除key、 hash分区。若添加key/hash分区时需注意。
对于key/hash分区，新增分区会导致之前表中的数据重新分区
对于RANGE分区的表，只可以添加新的分区到分区列表的最高端。
对于LIST分区的表，不能添加已经包含在现有分区值列表中的任意值。

案例一：rang类型添加分区

CREATE TABLE `person` (
     `id` INT,
     `name` VARCHAR(50),
     `birthday` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(birthday) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000)
);

INSERT INTO person VALUES
(1, 'lava', '1998-12-25'),
(2, 'admin', '1993-11-05'),
(3, '张三', '1996-03-10'),
(4, '李四', '1982-01-10'),
(5, '王五', '1984-09-16'),
(6, '赵六', '1987-06-05'),
(7, 'tony', '1992-08-04');

-- 新增分区   新增的分区数值只能比之前的更大
alter table person add partition (PARTITION p3 VALUES LESS THAN (2005));

4.8、删除分区

注意：
1.只支持删除range、 list分区，不支持删除key、 hash分区
2.删除分区后，分区内的数据也会被删除

# list/range删除分区
alter table person drop partition p3;

# key/hash不能通过drop删除分区，可以通过coalesce减少分区
语法：ALTER TABLE table_name COALESCE PARTITION num
示例：alter table product coalesce partition 2; -- 将原来的分区减少两个

4.9、清空分区数据

# 清空某分区的数据
alter table person truncate partition p0;

4.10、删除所有分区但是保留数据

如果想把分区表变成不分区的表，可以移除所有的分区

# 格式
ALTER TABLE table_name remove partitioning;

# 样例
alter table person remove partitioning;

4.11、重定义分区（拆分分区、合并分区、重命名）

如果希望能不丢失数据的条件下重新定义分区，可以使用如下语句：

# 格式  range/list
alter table tbl_name reorganize partition partition_list into(partition_definitions)

# key/hash不能通过REORGANIZE合并分区，可以通过coalesce减少分区，达到合并分区的效果
语法：ALTER TABLE table_name COALESCE PARTITION num
示例：alter table product coalesce partition 2; -- 将原来的分区减少两个

# 样例：range/list  合并分区
ALTER TABLE tbl_name REORGANIZE PARTITION s0,s1 INTO(partition p0 values in(1,2,3,4,5));

#样例：range/list  拆分分区
alter table person reorganize partition p0,p1 into(partition s0 values less than(5),partition s1 values less than(10));
或者如：
alter table tbl_name reorganize partition p0 into(partition s0 values in(1,2,3), partition s1 values in(4,5));

注意：如果要拆分分区，则需将分区内的数据全部拆分完。
如：

CREATE TABLE `person` (
     `id` INT,
     `name` VARCHAR(50),
     `birthday` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(birthday) ) (
     PARTITION p0 VALUES LESS THAN (2005)
);

alter table person reorganize partition p0 into(
	partition s0 values less than(1995),
	partition s1 values less than(2005) --这里的数值必须跟p0分区的数值一致或者更大
);

4.12、查询的时候使用到的分区

如果查询是基于分区表的话，会显示查询将访问的分区。在5.7以前的版本中，想要显示partitions需要使用explain partitions命令；想要显示filtered需要使用explain extended命令。在5.7版本后，默认explain直接显示partitions和filtered中的信息。

explain select * from product where type ='手机';

在这里插入图片描述

4.13、其他分区管理语句

1.重建分区：类似于先删除保存在分区中的所有记录，然后重新插入它们，可用于整理分区碎片。如：ALTER table tbl_name REBUILD PARTITION p2,p3;

2.优化分区：如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB或TEXT类型的列）做了许多修改，可以使用 ALTER TABLE tbl_name OPTIMIZE PARTITION来收回没有使用的空间，并整理分区数据文件的碎片。如：ALTER TABLE tbl_name OPTIMIZE PARTITION p2,p3;

mysql8.0开始，ALTER TABLE … OPTIMIZE PARTITION 语句对于InnoDB分区表不再有效，使用如下语句代替：
ALTER TABLE … REBUILD PARTITION
ALTER TABLE … ANALYZE PARTITION

3.分析分区：读取并保存分区的键分布，如：ALTER TABLE tbl_name ANALYZE PARTITION p2,p3;

4.检查分区：检查分区中的数据或索引是否已经被破坏，如：ALTER TABLE tbl_name CHECK PARTITION p2,p3;

5.修补分区：修补被破坏的分区，如：ALTER TABLE tbl_name REPAIR PARTITION p2,p3;

五、分区使用可能遇到的问题

5.1、[HY000][1493] VALUES LESS THAN value must be strictly increasing for each partition

报错内容如下：

[HY000][1493] VALUES LESS THAN value must be strictly increasing for each partition

译文：[HY000][1493]对于每个分区，小于值的值必须严格增加

报错场景：
对range分区新增分区时，值小于之前的最小值。如下：我想在1995-2000之间再增加一个分区就会报这个错误，增加的分区必须大于2000年。

CREATE TABLE `person` (
     `id` INT,
     `name` VARCHAR(50),
     `birthday` DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(birthday) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000)
);

mysql> alter table person add partition (PARTITION p3 VALUES LESS THAN (1998));
[HY000][1493] VALUES LESS THAN value must be strictly increasing for each partition

5.2、ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions

不支持删除key, hash分区的表

create table if not exists product
(
	id int not null ,
	name varchar(20) null comment '商品名称',
	type varchar(20) not null comment '商品类型',
    primary key(id,type)
) partition by key(type) partitions 4; 

mysql> alter table product drop partition p1;
ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions

ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions

参考文章：
https://www.cnblogs.com/wenxuehai/p/15901779.html#_label2_1
官网Partitioning https://dev.mysql.com/doc/refman/8.0/en/partitioning.html
官网上关于分区讲解的很细，而且挺方便查看的，更多信息都可以从官网上找一下答案。

五月天的尾巴

关注

1
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库分区

所谓分区，就是将一个表分成多个区块进行操作和保存，从而降低每次操作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是独立的对象，可以进行独立处理。未分区的表所有数据物理层面存储在同一个文件中，分区的表会按分区数量存储到不同的文件中，提高查询效率以下为分区和不分区物理层面的区别。分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。
复制链接

扫一扫

专栏目录