MySQL优化之表分区

IT美男子

已于 2022-10-10 10:49:14 修改

阅读量1.3k

点赞数

分类专栏： JAVA之数据库文章标签： mysql 数据库

于 2022-10-10 10:47:52 首次发布

个人原创文章，如若转载请标明出处哟！若帮助到您，请一键三连，谢谢咯哟

本文链接：https://blog.csdn.net/lingerlan510/article/details/127227043

版权

JAVA之数据库专栏收录该内容

3 篇文章 1 订阅

订阅专栏

一、前言

MySQL数据库作为一个程序员，我想大家肯定是非常的熟悉的,并且我们在面试中也经常被问到MySQL相关的知识点，其中MySQL的优化这个问题肯定是中高级面试跑不掉的问题。大部分同学都知道进行SQL语句优化、分库、分表等等操作，但是进行表分区这个操作可能还有些陌生。说实话我之前也是不知道这个东西的，直到项目中同事使用了，我才知道有这个东西可以进行MySQL的优化。在这里我不讲解SQL语句的优化及如何进行分库分表，只讲解如何对MySQL进行表分区及表分区相关的知识，看完本文后你可以了解到如下内容：

MySQL数据库表分区相关的知识点
如何快速的构建一张大数据量的MySQL表
项目中如何对已存在的大数据表进行表分区

提示：此处我使用的MySQL版本号为5.7.33-log

二、MySQL表分区

1.什么是表分区以及为什么要分区

我们首先找到mysql的数据存储目录，可以通过语句show variables like ‘%datadir%’;查看，我本机的是"C:\ProgramData\MySQL\MySQL Server 8.0\Data"，在该目录下，可以看到每个数据库对应着一个文件夹，对于没有分区的表，库中的每个表就对应着文件夹下的一个ibd文件
在这里插入图片描述
当一个表中的数据量太大时，会面临两个问题，一是对数据的操作会变慢，比如select、join、update、delete时，会对全表操作；二是不便于存储，可能会出现剩余磁盘空间存储不下这张表的情况。而分区就可以在一定程度上解决这两个问题。

简要的说，分区就是将表物理截断，但在逻辑上依然是一个整体，开发人员在数据操作时仍然是对这个整体大表进行操作，之后由数据库底层自己去寻找对应的分区进行操作，数据库底层寻找分区这个过程对开发人员来说是透明的，这样在数据操作时可以只对特定分区操作以提高效率，存储时也可以将不同分区的物理文件分开存放，下面是一个有3个分区(p1、p2、p3)的表(p_table)的实际存储
在这里插入图片描述

提示：只有当过滤条件为分区的字段时才会自动寻找分区，否则还是全表扫描

2.表分区与分表的区别

分区是指将数据库底层存储数据的文件进行切割，切割成多个小文件以此来提高速度
分表是指通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。
分表与分区的区别
分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

3.表分区的优缺点

优点

与单个磁盘或文件系统分区相比，可以存储更多的数据。
对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

缺点

一个表最多只能有1024个分区
在MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式，但是在MySQL5.5中提供了非整数表达式分区的支持。
如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
分区表中无法使用外键约束
MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

4.如何判断当前MySQL是否支持分区？

MySQL中5.1版本开始支持分区，但是5.6版本之前和5.6版本之后的查看是否支持分区命令存在差异，首先我们先查看我们的数据库版本号。

// 先查询MySQL版本号
SELECT VERSION();

MySQL版本号小于5.6使用如下命令

show variables like '%partiotion%';

MySQL版本号大于5.6使用如下命令

show plugins;

查看查询结果中是否包含如下内容，若包含则说明支持分区，否则不支持分区
在这里插入图片描述

5.MySQL水分分区的类型

之所以特别说明一下是水平分区，是因为还有一种垂直分区的分区方式，二者一个横向切割一个纵向切割，(对比之下感觉水平分区类似于HBase中的segment，垂直分区类似于HBase中的region~)，关于垂直分区先跳过，一是没找到多少相关的资料，二是感觉业务中用到的也不多，大多用的都是水平分区，有时间日后再补。

常见的水平分区类型有 RANGE、LIST、HASH、KEY 、复合等五种。
如果一个分区表定义了主键或唯一索引，则分区键必须被包含在主键或唯一索引的字段中，否则无法建表成功。这很好理解，使用主键或唯一索引是最常见、最高效检索数据的方式，如果使用主键或唯一索引做分区键，根据分区定义，可以直接定位数据在哪个分区，不需要查询其余分区，最大程度发挥分区的优点。

1.RANGE 分区（常用）

range分区，顾名思义，就是按照范围进行分区，下面是创建一个range分区表：

drop table if exists `range_table`;
create table `range_table`(
	`id` int,
	`name` varchar(10)
) 
partition by range(id)(
	partition p1 values less than (10),
	partition p2 values less than (20),
	partition p3 values less than maxvalue
);

上面以id为分区字段，根据id大小划分为[-∞, 10)，[10, 20)，[20, +∞]三个区间，注意包前不包后，在数据插入时会自动根据id插入到各自分区

# 插入数据
insert into range_table (id, name) values (1,"梁静茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷贝");
# 查看各个分区数据条数
select partition_name,table_rows from information_schema.partitions where table_name = 'range_table';

在这里插入图片描述
分别指定分区查看各个分区里面的数据，可以看到id为1的保存到了p1，id为10和15的保存到了p2，id为50的保存到了p3

select * from range_table partition (p1);
select * from range_table partition (p2);
select * from range_table partition (p3);

在进行select/update/delete时如果where后面的限制条件包含分区字段id时会自动去对应分区中查找，否则还是全表扫描，如下所示。

explain select * from range_table where id = "1" and name = '梁静茹';

在这里插入图片描述

explain select * from range_table where name = '梁静茹';

在这里插入图片描述
提示：range分区字段只支持整型，如果需要对时间日期这样的字段进行range分区，可以通过相关函数将类型转为整型再分区。

2.LIST分区

list就是枚举的意思，list分区就是在创建各分区时具体指定哪些值属于这些分区，下面是创建list分区表的代码：

drop table if exists `list_table`;
create table `list_table`(
	`id` int,
	`name` varchar(10)
) 
partition by list(id)(
	partition p1 values in (1),
	partition p2 values in (10,15,50)
);

插入数据

insert into list_table (id, name) values (1,"梁静茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷贝");

id为1的保存到了p1分区，id为10，15，20的保存到了p2分区，需要注意如果插入数据的 id 在各个分区所对应着的列表里面都没找到，则会报错。
查询分区数据结果

select partition_name,table_rows from information_schema.partitions where table_name = 'list_table';

在这里插入图片描述
提示：list分区分区字段同样只能是int型，该分区使用较少

3.HASH分区

hash分区分为常规hash和线性hash，常规hash是在分区字段上基于分区个数的取模运算，根据余数分区。线性hash是对分区字段进行二次方运算，根据运算结果分区，所以hash分区同样要求分区字段为整型或者是可以返回整型结果的表达式。二者在建表时候的区别只是线性hash比常规hash多了个linear(线性的)限定。

3.1.常规hash

常规hash分区建表：

drop table if exists `hash_table`;
create table `hash_table`(
	`id` int,
	`name` varchar(10)
) 
partition by hash(id)
partitions 3;

hash分区不能指定分区名，会默认创建名为pn的分区，n从0开始自增。上面这段代码会创建p0，p1，p2三个分区，分区名可以通过下面的sql查看，

select partition_name from information_schema.PARTITIONS where table_schema = schema() and table_name = "hash_table";

在这里插入图片描述
上面说的常规hash就是基于分区数对分区字段进行取模求余操作，按照这种计算，插入下面的数据，

insert into hash_table (id, name) values (1,"梁静茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷贝");

1 10 15 50 分别对3求余对应的结果 1 1 0 2，也就是上面4条数据应该分别被保存到p1, p1, p0, p2分区，对此进行验证：

select 'p0' as part, t.* from hash_table partition (p0) t
union
select 'p1' as part, t.* from hash_table partition (p1) t
union
select 'p2' as part, t.* from hash_table partition (p2) t;

在这里插入图片描述
这样当在查询的时候会采用相同的取模运算到对应分区下查找，比如查id为5的数据，就会去p2分区查找。

3.2.线性hash

线性hash在建表时只是比常规hash多了个linear字段：

drop table if exists `hash_linear_table`;
create table `hash_linear_table`(
	`id` int,
	`name` varchar(10)
) 
partition by linear hash(id)
partitions 3;

插入数据

insert into hash_linear_table (id, name) values (1,"梁静茹"),(10,"金泰妍"),(15,"王菲"),(50,"邵夷贝");

关于线性分区的具体计算规则可以参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/partitioning-linear-hash.html，这里假设num是分区个数，value是某条记录的分区字段对应的值，N是最终经过计算得到的某个分区编号，则N的计算过程如下：

step1：V = power(2, ceil(log(2, num)))
step2：N = value & (V-1) step3：if
N>=num: N=N & (ceil(V/2) - 1)

按照上面步骤，将id为50的这条数据代入计算：

step1：V = power(2, ceil(log(2, num))) = power(2, ceil(log(2, 3))) = power(2, 2) = 4
step2：N = value & (V-1) = 50 & 3 = 110010 & 000011 = 000010 = 2
step3：N>=num? <=> 2>=3? False：N=2

即id为50的这条数据保存到p2分区，同理可以计算出id为10时N=2，id为1时N=1，id为15是N=1，验证一下计算结果：

select 'p0' as part, t.* from hash_linear_table partition (p0) t
union
select 'p1' as part, t.* from hash_linear_table partition (p1) t
union
select 'p2' as part, t.* from hash_linear_table partition (p2) t;

在这里插入图片描述
结果计算正确。

4.KEY 分区

主要还是参考官方文档吧，https://dev.mysql.com/doc/refman/8.0/en/partitioning-key.html，里面主要说的是，key分区类似于hash分区，只不过分区列不再强制为整型，可以为除text和BLOB两种类型外的其它类型。key分区也有两种，常规key和线性key，常规key对分区字段采用的是MD5算法，线性key对分区字段采用的是二次方算法，参考hash分区中的线性hash，分区列选取的具体规则为：

当表中只有主键primary key或只有唯一键unique
key时，分区列必须包含主键或唯一键中的部分或全部字段，不允许出现主键或唯一键中字段以外的其它字段
当表中主键和唯一键同时存在时，分区列为主键和唯一键公共字段的部分或全部
当表中主键唯一键都没有时：任意指定除text和BLOB类型外的其它字段，可以为1个或多个

分区列也可以缺省不指定，但必须要求表中存在主键或唯一键，优先以主键作为分区字段，没有主键时以唯一键作为分区字段，此时唯一键必须显示指定not null。

下面是常规key分区建表的一个demo，name为分区字段：

drop table if exists `key_table`;
create table `key_table`(
	`id` int,
	`name` varchar(10) not null,
	unique `uk_name` (name)
) 
partition by key()
partitions 3;

线性key分区的建表也只是多了一个linear字段：

drop table if exists `key_table`;
create table `key_table`(
	`id` int,
	`name` varchar(10) not null,
	unique `uk_name` (name)
) 
partition by linear key()
partitions 3;

5.复合分区

文档地址：https://dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.html，里面有这么一段话，
说的是我们可以对采用range分区或者list分区的表，进行二次分区，二次分区只能为hash分区或者key分区。这种分区方式有两种建表写法，一种是指定子分区名，一种是不指定子分区名由系统默认。
不指定子分区名创建：

drop table if exists `subpart_table`;
create table `subpart_table`(
	dt date
) 
partition by range(year(dt))
subpartition by hash(month(dt))
subpartitions 2 (
	partition p1 values less than (1990),
	partition p2 values less than (2000),
	partition p3 values less than maxvalue
);

通过如下命令查看各个分区情况：

select partition_name, subpartition_name from information_schema.partitions where table_schema = schema() and table_name = 'subpart_table';

查询结果如下：
在这里插入图片描述
物理上也被分成了单独的6个文件：

指定分区名创建，这种方式要求每个一级分区下的子分区数量必须一致，所有子分区的分区名不能重复：

drop table if exists `subpart_table`;
create table `subpart_table`(
	dt date
) 
partition by range(year(dt))
subpartition by hash(month(dt))
(
	partition p1 values less than (1990)(
		subpartition s1,
		subpartition s2
	),
	partition p2 values less than (2000)(
		subpartition s3,
		subpartition s4
	),
	partition p3 values less than maxvalue(
		subpartition s5,
		subpartition s6
	)
);

上表根据日期的年份进行一级分区，根据日期的月份二级分区，s1、s3、s5存偶数月，s2、s4、s6存奇数月，插入数据验证一下：

insert into subpart_table values('1989-01-01'), ('1989-02-01'), 
								('1995-01-01'), ('1989-02-01'), 
								('2022-01-01'), ('2022-02-01');

select 's1' as part, t.* from subpart_table partition (s1) t
union
select 's2' as part, t.* from subpart_table partition (s2) t
union
select 's3' as part, t.* from subpart_table partition (s3) t
union
select 's4' as part, t.* from subpart_table partition (s4) t
union
select 's5' as part, t.* from subpart_table partition (s5) t
union
select 's6' as part, t.* from subpart_table partition (s6) t;

查询结果如下：
在这里插入图片描述

三、MySQL中如何快速创建大数据表

1.创建表

drop table if exists user;
create table user(
	id int not null primary key auto_increment,
    number int not null,
    name varchar(20) not null
)engine=innodb default charset=utf8;

2.创建生产编号和名称的函数

DELIMITER $
CREATE FUNCTION rand_number() RETURNS INT
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i= FLOOR(1+RAND()*100);
    RETURN i;
END $
DELIMITER ;

DELIMITER $
CREATE FUNCTION rand_name(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 $
DELIMITER ;

3.创建生成数据的存储过程

DELIMITER $
CREATE PROCEDURE insert_user(IN max_num INT(10))
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; 
  START TRANSACTION;
  while i < max_num do
  insert into user(id,number,name) values(null,rand_number(),rand_name(5));
  set i = i + 1;
END WHILE;
COMMIT;
END $
DELIMITER ;

4.插入数据

我此处插入的是100W条，如果电脑配置差的话可少插入一点，100W条插入需要耗费些时间，请耐心等待。我插入100W条耗时217秒

call insert_user(1000*10000);

四、项目中如何对已存在的大数据表进行表分区

直接使用ALTER TABLE来进行更改表为分区表，这个操作会创建一个分区表，然后自动进行数据copy然后删除原表，猜测该操作服务器资源消耗比较大，请谨慎操作。
警告：生产环境中操作时，千万要记得备份之后再进行操作，以防出现问题导致数据丢失。

本案例使用RANG分区以上面创建的user表为例：
总共100W条数据，分成5个区，每个区20W条数据，操作如下
创建分区语句：

ALTER TABLE user PARTITION BY RANGE (id)
(
PARTITION p_0 VALUES LESS THAN (200000),
PARTITION p_1 VALUES LESS THAN (400000),
PARTITION p_2 VALUES LESS THAN (600000),
PARTITION p_3 VALUES LESS THAN (800000),
PARTITION p_4 VALUES LESS THAN MAXVALUE );

执行后，回发现ibd文件发生了变化，如下图所示：
在这里插入图片描述
查看表的分区状态：

select partition_name, subpartition_name,table_rows from information_schema.partitions where table_schema = schema() and table_name = 'user';

验证分区是否成功

explain select *  from  user  where id=333;

在这里插入图片描述
如图所示，数据已经分区成功！

五、小结

表分区的相关知识大家可以多了解一些，这部分内容虽然工作中可能回很少用到，但是面试的过程中被问到的可能性还是很大的，因此我们还是需要进行掌握的。
本文参考链接如下：
https://www.cnblogs.com/liuqiyun/p/15787045.html
https://blog.csdn.net/qq_41487004/article/details/125303464
https://blog.51cto.com/u_13675040/2114580
https://blog.csdn.net/atwdy/article/details/125181469