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

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录 前言🚀 1.分区表🌈 1.1 什么是表分区🌈 1.2 为什么需要表分区🌈 1.3 分区表的缺点 🚀 2.分区表的类型🌈 2.1 RANGE分区🌈 2.2 LIST分区🌈 2.3 HASH分区 🌈 2.4 KEY分区🌈 2.5 多字段分区🚀 3.常见分区管理命令🚀 4.表分区实战🌈 4.1 分区管理🌈 4.2 普通表与分区表的互转

前言 表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分，本文做了详细的说明

---

下面由邦德老师，给大家详细的介绍下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;