炸锅了，MySQL表500W行，居然有人不做分区？

下面由邦德老师，给大家详细的介绍下MySQL的分区吧~

🚀 1.分区表

🌈 1.1 什么是表分区

我们可以通过 show variables like '%datadir%';
命令来查看数据文件存放的默认路径，一个数据库就是一个文件夹，一个库中。
只要一张表的数据量过大，就会导致 *.ibd 文件过大，数据的查找就会变的很慢。
一般生产上建议单天超过10万行,建议分区，1亿条数据大约1G

MySQL 从 5.1 开始添加了对分区的支持，
分区的过程是将一个表或索引分解为多个更小、更可管理的部分。
对于开发者而言，分区后的表使用方式和不分区基本上还是一模一样，只不过在物理存储上，
原本该表只有一个数据文件，现在变成了多个，每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。

常见的存储引擎如 InnoDB、MyISAM、NDB 等都支持分区。
但并不是所有的存储引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分区，
因此在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。

🌈 1.2 为什么需要表分区

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

🌈 1.3 分区表的缺点

🚩表分区的主要缺点

1.一个表最多只能有 1024 个分区。
2.如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
3.分区表无法使用外键约束。
4.NULL 值会使分区过滤无效。
5.所有分区必须使用相同的存储引擎。

🚩表分区的主要优点

1、可以允许在一个表里存储更多的数据，突破磁盘限制和文件系统限制。
2、对于从表里删除过期的历史数据比较容易，只需要移除对应的分区。
3、对于某些查询或修改语句，可以自动将数据范围缩小到一个至几个分区上，优化语句执行效率。

🚀 2.分区表的类型

🌈 2.1 RANGE分区

范围表分区，按照一定的范围值来确定每个分区包含的数据，如上使用的就是range表分区；
语法：partition by range(id) partition p0 values less than()
分区的定义范围必须是连续的，且不能重叠，使用values less than()来定义分区范围，从小到大定义范围。
给分区字段赋值的时候分区字段取值范围不能超过values less than()的取值范围。
使用values less than maxvalue来将未来不确定的值放到这个表分区中。
按时间类型(datetime)来做表分区可以在RANGE()中使用函数来做转换，
例如：partition by range(year(create_time)),timestamp可以使用unix_timestamp('2019-11-20 00:00:00')转化

create table user_range(
  id int,
  username varchar(255),
  password varchar(255),
  createDate date,
  primary key (id,createDate)
) engine=innodb
  partition by range(year(createDate))(
     partition  p2022  values  less  than(2023),
     partition  p2023  values  less  than(2024),
     partition  p2024  values  less  than(2025)  
);

🚩 注意：
createDate 是联合主键的一员。**如果 createDate 不是主键，
只是一个普通字段，那么创建时就会抛出如下错误：

🚩 删除分区
alter table user_range drop partition p2022;
🚩 新增分区
alter table user_range add partition(partition p2025 values less than(2026));

🌈 2.2 LIST分区

语法： partition by list(id) partition p0 values in(1,2,3)
分区字段必须是整数类型或者分区函数返回整数，取值范围通过values in()来定义，不能使用maxvalue。
假设我有一个用户表，用户有性别，现在想按照性别将用户分开存储，
男性存储在一个分区中，女性存储在一个分区中，SQL 如下：

create  table user_list(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  primary key(id, gender)
)engine=innodb
  partition by list(gender)(
     partition  man  values  in  (1),
     partition  woman  values  in  (0)
  );

🌈 2.3 HASH分区

哈希表分区，按照一个自定义的函数返回值来确定每个分区包含的数据
HASH 分区的目的是将数据均匀地分布到预先定义的各个分区中，
保证各分区的数据量大致都是一样的。在 RANGE 和 LIST 分区中，
必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；
而在 HASH 分区中，MySQL 自动完成这些工作，
用户所要做的只是基于将要进行哈希分区的列指定一个表达式，并且分区的数量。
使用 HASH 分区来分割一个表，要在 CREATE TABLE 语句上添加 PARTITION BY HASH (expr)，
其中 expr 是一个字段或者是一个返回整数的表达式；另外通过 PARTITIONS 属性指定分区的数量，
如果没有指定，那么分区的数量默认为 1，另外，HASH 分区不能删除分区，
所以不能使用 DROP PARTITION 操作进行分区删除操作。
语法：partition by hash(id) partitions 4
根据hash算法来分配到分区中，以上设置四个分区，并根据id%4进行取模运算，根据余数插入到指定的分区中。

🚩 create table user7(id int) partition by hash(id) partitions 3;

🌈 2.4 KEY分区

KEY 分区和 HASH 分区相似，但是 KEY 分区支持除 text 和 BLOB 之外的所有数据类型的分区，
而 HASH 分区只支持数字分区。KEY 分区不允许使用用户自定义的表达式进行分区，
KEY 分区使用系统提供的 HASH 函数进行分区。当表中存在主键或者唯一索引时，
如果创建 KEY 分区时没有指定字段系统默认会首选主键列作为分区字段,
如果不存在主键列会选择非空唯一索引列作为分区字段。
key()括号里面可以包含0个或多个字段（不必是整数类型，可以是普通字段）

create table user_key(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  primary key(id, gender)
)engine=innodb partition by key() partitions 4;

🌈 2.5 多字段分区

可以指定多个字段作为分区字段
COLUMN 分区是 5.5 开始引入的分区功能，只有 RANGE COLUMN 和 LIST COLUMN 这两种分区；
支持整形、日期、字符串；这种分区方式和 RANGE、LIST 的分区方式非常的相似。
COLUMNS Vs RANGE Vs LIST 分区：
针对日期字段的分区不需要再使用函数进行转换了。
COLUMN 分区支持多个字段作为分区键但是不支持表达式作为分区键。
COLUMNS 支持的类型
整形支持：tinyint、smallint、mediumint、int、bigint；不支持 decimal 和 float。
时间类型支持：date、datetime。
字符类型支持：char、varchar、binary、varbinary；不支持text、blob。

create table user1(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  createDate date,
  primary key(id, createDate)
)engine=innodb PARTITION BY RANGE COLUMNS(createDate) (
    PARTITION p0 VALUES LESS THAN ('1990-01-01'),
    PARTITION p1 VALUES LESS THAN ('2000-01-01'),
    PARTITION p2 VALUES LESS THAN ('2010-01-01'),
    PARTITION p3 VALUES LESS THAN ('2020-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);


create table user2(
  id int,
  username varchar(255),
  password varchar(255),
  gender int,
  createDate date,
  primary key(id, createDate)
)engine=innodb PARTITION BY LIST COLUMNS(createDate) (
    PARTITION p0 VALUES IN ('1990-01-01'),
    PARTITION p1 VALUES IN ('2000-01-01'),
    PARTITION p2 VALUES IN ('2010-01-01'),
    PARTITION p3 VALUES IN ('2020-01-01')
);

🚀 3.常见分区管理命令

1.添加分区：
alter table user add partition (partition p3 values less than (4000)); -- range 分区
alter table user add partition (partition p3 values in (40)); -- lists分区
2.删除表分区（会删除数据）：
alter table user drop partition p30;
3.删除表的所有分区（不会丢失数据）：
alter table user_list remove partitioning;
4.重新定义 list分区表（不会丢失数据）：
alter table user_list partition by list(gender)(
partition man values in (1),
partition woman values in (0)
);
5.重新定义 hash 分区表（不会丢失数据）：
alter table user partition by hash(salary) partitions 7;
6.合并分区：把 2 个分区合并为一个，不会丢失数据：
alter table user reorganize partition p1,p2 into (partition p1 values less than (30));
注意：合并之后范围取最大
6.数据字典查询
select * from information_schema.partitions
where table_schema='jeames' and table_name='user'\G

🚀 4.表分区实战

🌈 4.1 分区管理

--创建分区表
create table user(id int(11) not null,name varchar(32) not null)
partition by range(id)
(
partition p0 values less than(10),
partition p1 values less than(20),
partition p2 values less than(30),
partition p3 values less than maxvalue
)

🚩 数据存储文件将根据分区被拆分成多份

insert into user values(1,'IT');
insert into user values(12,'007');
insert into user values(22,'jeames');
insert into user values(50,'TenKE');
select * from user partition(p0);
select * from user partition(p1);
select * from user partition(p2);
select * from user partition(p3);
新增几条数据后查询可以看到数据已经分散在不同的分区中

🌈 4.2 普通表与分区表的互转

🚩 普通表转分区表语句:
ALTER TABLE students PARTITION BY KEY(sid) PARTITIONS 2;

🚩移除分区信息
ALTER TABLE fg_pm_nbiot_cel_h_cel remove partitioning;