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

发布时间:2023-07-16 19:30


哈喽!大家好,我是【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表500W行,居然有人不做分区?_第1张图片\"
\"炸锅了,MySQL表500W行,居然有人不做分区?_第2张图片\"
\"炸锅了,MySQL表500W行,居然有人不做分区?_第3张图片\"
下面由邦德老师,给大家详细的介绍下MySQL的分区吧~

1.分区表

1.1 什么是表分区

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

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

\"炸锅了,MySQL表500W行,居然有人不做分区?_第4张图片\"

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

\"炸锅了,MySQL表500W行,居然有人不做分区?_第5张图片\"

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
)

数据存储文件将根据分区被拆分成多份
\"炸锅了,MySQL表500W行,居然有人不做分区?_第6张图片\"

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);
新增几条数据后查询可以看到数据已经分散在不同的分区中

\"炸锅了,MySQL表500W行,居然有人不做分区?_第7张图片\"

4.2 普通表与分区表的互转

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

\"炸锅了,MySQL表500W行,居然有人不做分区?_第8张图片\"

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

\"炸锅了,MySQL表500W行,居然有人不做分区?_第9张图片\"
\"炸锅了,MySQL表500W行,居然有人不做分区?_第10张图片\"

\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网,如有侵权请联系:561261067@qq.com

桂ICP备16001015号