一、前言

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 BEGINDECLARE i INT DEFAULT 0;SET i= FLOOR(1+RAND()*100);RETURN i; END $ DELIMITER ; DELIMITER $ CREATE FUNCTION rand_name(n INT) RETURNS VARCHAR(255) BEGINDECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i<n DOSET 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)) BEGINDECLARE i INT DEFAULT 0;DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; START TRANSACTION;while i < max_num doinsert 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

总结

以上是生活随笔为你收集整理的MySQL优化之表分区的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。