欢迎来到爱书不爱输的程序猿的博客, 本博客致力于知识分享，与更多的人进行学习交流
本文收录于SQL应知应会专栏,本专栏主要用于记录对于数据库的一些学习，有基础也有进阶，有MySQL也有Oracle
分区表 • MySQL版
- 前言
- 一、分区表
- 1.非分区表
- 2.分区表
- 2.1 概念
- 2.2 MySQL数据库表分区
- 2.2.1 InnoDB 逻辑存储结构
- 2.3 MySQL数据库分区的由来
- 2.4 为什么对表进行分区？
- 2.5 MySQL的分区形式
- 2.6 MySQL分区的类型
- 2.7 MySQL分区代码
- 2.7.5.3 range-key 复合分区
- 2.7.5.4 list - key 复合分区
- 2.8 常见分区操作
- 2.8.1 删除分区
- 2.8.2 增加分区
- 2.8.3 分解分区
- 2.8.4 合并分区
- 2.8.5 重新定义分区
- 2.8.6 重建分区
- 2.8.7 检查分区
- 2.8.8 修补分区
- 2.9 MySQL分区表的局限性
- 2.9.1 错误示例
- 2.9.2 错误修正
- 小结
前言
在前面的内容中，【SQL应知应会】表分区（一）• MySQL版、【SQL应知应会】表分区（二）• MySQL版、【SQL应知应会】表分区（三）• MySQL版中，已经完成了MySQL的表分区方面的大部分知识的学习，如为什么对表进行分区，分区有哪些形式，分区有哪些类型以及每一种类型的语句，分区的注意事项以及适用场景，并且用例子代码演示了MySQL的各种分区
今天这篇内容，将继续进行讲述MySQL的表分区的后续内容，主要包括常见的分区操作，如删除分区、增加分区、分解分区、合并分区、重新定义分区、重建分区、 检查分区、修补分区，不但使用代码进行演示，并且补充了一些需要注意的内容；今天还讲到了MySQL分区表的局限性，其中直接使用错误示例帮助大家更直接明了的看到错误的原因，并且展示了错误修正后的代码
希望文章的内容对大家有所帮助，如果有什么不足的地方，大家可以在评论区或者私信我，感谢大家的支持
那么，快拿出你的电脑，跟着文章一起学习起来吧
一、分区表
1.非分区表
👉:传送门💖非分区表构💖
2.分区表
2.1 概念
👉:传送门💖概念💖
2.2 MySQL数据库表分区
2.2.1 InnoDB 逻辑存储结构
👉:传送门💖InnoDB 逻辑存储结构💖
2.2.2 段(segment)
2.2.3 区(extent)
2.2.4 页(page)
2.3 MySQL数据库分区的由来
👉:传送门💖MySQL数据库分区的由来💖
2.4 为什么对表进行分区？
👉:传送门💖为什么对表进行分区💖
2.4.1 表分区要解决的问题
2.4.2 表分区有如下优点
2.5 MySQL的分区形式
👉:传送门💖MySQL的分区形式💖
2.5.1 水平分区(HorizontalPartitioning)
2.5.2 垂直分区(VerticalPartitioning)
2.6 MySQL分区的类型
2.6.1 range分区 👉:传送门💖range分区💖
2.6.2 list分区(列表分区)
2.6.3 hash分区
2.6.4 KEY表分区
2.6.5 多字段分区(range、list)
2.6.6 分区注意事项及适用场景
👉:传送门💖2.6.2 ~ 2.6.6💖
2.7 MySQL分区代码
2.7.1range分区
2.7.2list分区
👉:传送门💖2.7.1~ 2.7.2💖
2.7.3 hash表分区
2.7.4 key表分区
2.7.5复合分区
2.7.5.1 range-hash(范围哈希)复合分区
2.7.5.2 list-hash(列表哈希)复合分区
👉:传送门💖2.7.3 ~ 2.7.5💖
2.7.5.3 range-key 复合分区
## range-key 复合分区
create table foo_emp2
(
empno varchar(20) not null,
empname varchar(20),
deptno int,
salary int
)
partition by range(salary)
subpartition by key(deptno)
subpartitions 3
(
partition p1 values less than (2000),
partition p2 values less than (3000)
)
insert into foo_emp2 select 1,1,20,1000 from dual
2.7.5.4 list - key 复合分区
## list - key 复合分区
create table empk(
empno varchar(20) not null,
empname varchar(20),
deptno int,
birthdate date not null,
salary int
)
partition by list(deptno)
subpartition by key(birthdate)
subpartitions 3
(
partition p1 values in (10),
partition p2 values in (20)
)
2.8 常见分区操作
2.8.1 删除分区
alter table emp drop partition p1
## 不能删除hash或者key分区
- 一次性删除多个分区
alter table emp drop partition p1,p2
- 删除表的所有分区
alter table emp remove partitioning; -- 不会丢失数据
2.8.2 增加分区
- 增加范围分区
- 范围分区一般只能往后增加，往前增加一般得reorganize重新组织分区或者Oracle的split分区
### 范围分区一般只能往后增加，往前增加一般得reorganize重新组织分区或者Oracle的split分区
alter table emp add partition(partition 3 values less than (4000))
-- 增加完4000的,是否可以增加一个3500？
-- 不可以，因为4000之前的已经划分完了
- 增加列表分区
alter table emp1 add partition(partiton 3 value in (40))
-- 如果前面的list分区中,主分区有3个子分区,那么新增加的这个也会自动给配3个子分区
2.8.3 分解分区
Reorganize partition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据- 分解前后分区的整体范围应该一致
alter table te
reorignize partition p1 into
(
partition p1 values less than (100),
partition p3 values less than (1000)
); -- 不会丢失数据
2.8.4 合并分区
- Merge分区:把2个分区合并为1个
alter table te
reorganize partition p1,p3 into
(
partition p1 values less than (1000)
) -- 不会丢失数据
2.8.5 重新定义分区
- 重新定义hash分区表
alter table emp partition by hash(salary) partitions 7;
-- 不会丢失数据
- 重新定义range分区表
alter table emp partition by range(salary)
(
partition p1 values less than (2000),
partition p2 values less than (4000)
) -- 不会丢失数据
2.8.6 重建分区
- 这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果
- 可用于整理分区碎片
alter table emp rebuild partition p1,p2;
2.8.7 检查分区
- 可以使用几乎与对非分区表使用check table相同的方式检查分区
- 这个命令可以告知表emp的分区p1,p2中的数据或索引是否已经被破坏,若发生了这种情况,使用修补命令
alter table emp check partition p1,p2;
2.8.8 修补分区
# 修补被破坏的分区
alter table emp repairpartition p1,p2
2.9 MySQL分区表的局限性
- 在5.1版本中分区表对唯一约束有明确的规定,每一个唯一约束必须包含中分区表的分区键(也包括主键约束)
2.9.1 错误示例
报错：MySQL Database Error:A PRIMARY KEY must include allcolums in the tables partitioning function
create table emptt(
empno varchar(20) not null,
empname varchar(20),
deptno int,
birthdate date not null,
salary int,
primary key(empno)
)
partition by range(salary) -- 这样的语句会出错：MySQL Database Error:A PRIMARY KEY must include allcolums in the tables partitioning function
(
partition p1 values less than (100),
partition p2 values less than (200)
)
2.9.2 错误修正
create table emptt(
empno varchar(20) not null,
empname varchar(20),
deptno int,
birthdate date not null,
salary int,
primary key(empno,salary) -- 在主键中加入salary列就正常
)
partition by range(salary)
(
partition p1 values less than (100),
partition p2 values less than (200)
)
小结
感谢大家耐心的看完这篇文章，对于SQL在表分区的知识点，我们在MySQL方面已经有四篇内容了，如果大家觉着还算可以，那么就给个三连支持一下吧，如果想要继续关注和学习后续更多的内容，就关注一下爱书不爱输的程序猿吧，当然，如果大家还有什么其他方面的知识点想要看，可以在评论区或者私信我
