【mysql 详解】mysql分区详解

已于 2024-05-16 14:58:19 修改
阅读量484 收藏 4
点赞数 5
文章标签： mysql 数据库
于 2024-05-16 14:05:48 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/snakelis/article/details/138959302
版权

分区简介

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中，让一个大对象碧昂城一些小对象。

MySQL分区即可以对数据进行分区也可以对索引进行分区。

分区类型

  • range 分区

基于一个给定的连续区间范围(区间要求连续并且不能重叠)，把数据分配到不同的分区

  • list 分区

类似于range分区，区别在于list分区是居于枚举出的值列表分区，range是基于给定的连续区间范围分区

  • hash分区

基于给定的分区个数，把数据分配到不同的分区

  • key分区

类似于hash分区

分区优点

  • 和单个磁盘或者文件系统分区相比，可以存储更多数据
  • 优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率；同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果
  • 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据
  • 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量
  • 分区可以分在多个磁盘，存储更大一点
  • 根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了
  • 进行大数据搜索时可以进行并行处理。
  • 跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

分区和水平分表的区别

  • 水平分表需要用户预先手动显式创建出多张分表(如tbl_user0, tbl_user1, tbl_user2)，在物理上实实在在的创建多张表，通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)来实现分表逻辑。
  • 分区是MySQL的一个插件Plugin功能，将一张大表的数据在数据库底层分成多个分区文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd)，和水平分表不同的是分区不需要显式的创建“分表”,数据库会自动创建分区文件的，用户看到的只是一张普通的表，其实是对应的是多个分区，这个是对用户是屏蔽的、透明的，在使用上和使用一张表完全一样，不需要借助任何功能来实现。分区是一种逻辑上的水平分表，在物理层面还是一张表。

脚本

# 查看mysql data 文件存放目录
show variables like '%datadir%'

#生成测试表

CREATE TABLE `tbl_user_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_user_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=myisam AUTO_INCREMENT=5100002 DEFAULT CHARSET=utf8;

#插入数据500W

-- 修改mysql默认的结束符号，默认是分号；但是在函数和存储过程中会使用到分号导致解析不正确
delimiter $$

-- 随机生成一个指定长度的字符串
create function rand_string(n int) returns varchar(255) 
begin 
 # 定义三个变量
 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;

 while i < n do 
   set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
   set i = i + 1;
 end while;
 return return_str;
end $$

-- 创建插入的存储过程
create procedure insert_user(in start int(10), in max_num int(10))
begin
    declare i int default 0; 
    set autocommit = 0;  
    repeat
        set i = i + 1;
        insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
        until i = max_num
    end repeat;
   commit;
end $$

-- 将命令结束符修改回来
delimiter ;

-- 调用存储过程，插入500万数据，需要等待一会时间，等待执行完成
call insert_user(100001,5000000);
-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G内存用了8分钟才执行完

select count(*) from tbl_user_no_part;

查看mysql版本

 select version();

查看mysql 是否激活分区插件

show variables like '%partition%';

have_partitioning YES

show plugins;

partition ACTIVE STORAGE ENGINE GPL

未激活修改my.conf

innodb_file_per_table=1

range 分区

给定一个连续区间的范围值进行分区，某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段，如 日期范围, 连续的数字

  • 使用range分区时表结构要么没有主键，要么分区字段必须是主键。

  • range 对应的分区键值必须是数字值，可以使用range columns(分区字段) 对非int型做分区，如字符串，对于日期类型的可以使用year()、to_days()、to_seconds()等函数

    create table range_table (
    id int not null auto_increment,
    age int not null,
    PRIMARY KEY (id,age)
    )
    engine = innodb default charset=utf8
    partition by range (age)(
    partition p1 values less than (20),
    partition p2 values less than (40)
    )

list 分区

设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列。list分区使用in表示一些固定的值的列表

   create table list_table(
    id int not null auto_increment,
    age int not null,
    primary key (id,age)
)
engine=innodb default charset=utf8
partition by list (age)(
partition p0 values in (1,2,3),
partition p1 values in (4,5,6)
)

columns分区

在mysql5.5之前range分区和list分区只支持整数分区，可以通过额外的函数运算或者额外的转换从而得到一个整数。columns分区分为 range columns 和 list columns 两种，支持整数(tinyint到bigint， 不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三大数据类型

columns分区支持一个或者多个字段作为分区键，不支持表达式作为分区键，这点区别于range 和 list 分区。需要注意的是range columns 分区键的比较是基于元组的比较，也就是基于字段组的比较，这和range分区有差异。

create table range_column_table (
	id int not null auto_increment,
	age int not null,
	primary key (id,age)
)
engine=innodb default charset=utf8
partition by range columns (id,age)(
	partition p1 values less than (0,10),
	partition p2 values less than (10,20),
	partition p3 values less than (10, maxvalue),
	partition p4 values less than (maxvalue, maxvalue)
)

# 查看数据存储的分区位置
select  * from information_schema.partitions
where table_schema=schema()

hash分区

hash分区只需要指定要分区的字段和要分成几个分区

Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中可能的平均分布。对一个表执行Hash分区时，mysql会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪个分区

hash分区类型
  • 常规hash分区

常规hash分区使用的是取模算法，对应一个表达式expr是可以计算出它被保存到哪个分区中，N = MOD(expr, num)
create table hash_table (
id int not null auto_increment,
age int not null,
primary key (id,age)
)
engine=innodb default charset=utf8
partition by hash(MOD(id,5))
PARTITIONS 5

  • 线性hash分区

线性hash分区使用的是一个线性的2的幂运算法则

常规hash分区在管理上带来了的代价太大，不适合需要灵活变动分区的需求。为了降低分区管理上的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速，缺点是：对比常规hash分区，线性hash各个分区之间数据的分布不太均衡。
create table linear_hash_table (
id int not null auto_increment,
age int not null,
primary key (id,age)
)
engine=innodb default charset=utf8
partition by linear hash(MOD(id,5))
PARTITIONS 5

key分区

按照key进行分区非常类似于按照hash进行分区，只不过hash分区允许使用用户自定义的表达式，而key分区不允许使用用于自定义的表达式，需要使用mysql服务器提供的hash函数，同时hash分区只支持整数分区，而key分区支持使用出blob or text类型外的其他类型的列作为分区键。

create table key_table (
    id int not null auto_increment,
    age int not null,
    primary key (id,age)
)
engine=innodb default charset=utf8
partition by key(id) 
partitions 5;

-- 不指定默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键
create table key_default_table (
    id int not null auto_increment,
    age int not null,
    primary key (id,age)
)
engine=innodb default charset=utf8
partition by key()
partitions 5;

# 线性key分区
create table linear_key_table (
    id int not null auto_increment,
    age int not null,
    primary key (id,age)
)
engine=innodb default charset=utf8
partition by linear key(id)
partitions 5;

子分区

子分区(subpartition)：是分区表中对每个分区的再次分割，又被称为复合分区，支持对range和list进行子分区，子分区即可以使用hash分区也可以使用key分区。复合分区适用于保存非常大量的数据记录。

create table sub_hash_table (
id int not null auto_increment,
age int not null,
primary key (id,age)
)
engine=innodb default charset=utf8
partition by range (id)
subpartition by hash(id) subpartitions 2
(
    partition p1 values less than (10),
    partition p2 values less than (20)
);

管理分区

# 删除那会list、range 分区（同时删除对应的分区数据
alter table list_table drop PARTITION  p1

# 新增分区
# 添加range分区
alter table range_table add partition
(partition p3 values less than maxvalue )

# 添加list分区
alter table list_table add partition
(partition p1 values in (4,5,6))

# 添加hash分区
alter table hash_table add partition 
partitions 4

# 添加key分区
alter table key_table add partition
partitions 4

# 添加子分区
alter table sub_hash_table add partition (
partition p3 values less than (30)
)

# range 分区 p3 分成 p3、p4
alter table range_table reorganize partition p3 into (        			
    partition p3 values less than (30),
    partition p4 values less than MAXVALUE
);

# list 分区 p1分成p1、p2
alter table list_table reorganize partition p1 into (
partition p1 values in (4,5),
partition p2 values in (6,7)
)

参考

MySQL性能优化(五)：分区

snakelis
关注
  • 5
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值