发布时间:2023-07-16 19:30
哈喽!大家好,我是【IT邦德】,江湖人称jeames007,10年DBA工作经验
一位上进心十足的【大数据领域博主】!
中国DBA联盟(ACDU)成员,目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发,备份恢复,安装迁移,性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】,欢迎关注【IT邦德】
❤️❤️❤️感谢各位大可爱小可爱!❤️❤️❤️
我们可以通过 show variables like ‘%datadir%’;
命令来查看数据文件存放的默认路径,一个数据库就是一个文件夹,一个库中。
只要一张表的数据量过大,就会导致 *.ibd 文件过大,数据的查找就会变的很慢。
一般生产上建议单天超过10万行,建议分区,1亿条数据大约1G
MySQL 从 5.1 开始添加了对分区的支持,
分区的过程是将一个表或索引分解为多个更小、更可管理的部分。
对于开发者而言,分区后的表使用方式和不分区基本上还是一模一样,只不过在物理存储上,
原本该表只有一个数据文件,现在变成了多个,每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。
常见的存储引擎如 InnoDB、MyISAM、NDB 等都支持分区。
但并不是所有的存储引擎都支持,如 CSV、FEDORATED、MERGE 等就不支持分区,
因此在使用此分区功能前,应该对选择的存储引擎对分区的支持有所了解。
1.可以让单表存储更多的数据。
2.分区表的数据更容易维护,可以通过清除整个分区批量删除大量数据,
也可以增加新的分区来支持新插入的数据。另外,还可以对一个独立分区进行优化、检查、修复等操作。
3.部分查询能够从查询条件确定只落在少数分区上,查询速度会很快。
4.分区表的数据还可以分布在不同的物理设备上,从而高效利用多个硬件设备。
5.可以使用分区表来避免某些特殊瓶颈,
例如 InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争。
可以备份和恢复单个分区。
表分区的主要缺点
1.一个表最多只能有 1024 个分区。
2.如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来。
3.分区表无法使用外键约束。
4.NULL 值会使分区过滤无效。
5.所有分区必须使用相同的存储引擎。
表分区的主要优点
1、可以允许在一个表里存储更多的数据,突破磁盘限制和文件系统限制。
2、对于从表里删除过期的历史数据比较容易,只需要移除对应的分区。
3、对于某些查询或修改语句,可以自动将数据范围缩小到一个至几个分区上,优化语句执行效率。
范围表分区,按照一定的范围值来确定每个分区包含的数据,如上使用的就是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));
语法: 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)
);
哈希表分区,按照一个自定义的函数返回值来确定每个分区包含的数据
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;
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;
可以指定多个字段作为分区字段
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\')
);
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
–创建分区表
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);
新增几条数据后查询可以看到数据已经分散在不同的分区中
普通表转分区表语句:
ALTER TABLE students PARTITION BY KEY(sid) PARTITIONS 2;
移除分区信息
ALTER TABLE fg_pm_nbiot_cel_h_cel remove partitioning;