
MySQL全面瓦解29:分库分表之Partition功能详解
1 回顾
上一节我们详细讲解了如何对数据库进行分区操作,包括了 垂直拆分(Scale Up 纵向扩展)和 水平拆分(Scale Out 横向扩展) ,同时简要整理了水平分区的几种策略,现在来回顾一下。
2 水平分区的5种策略
2.1 Hash(哈希)
这种策略是通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如我们可以建立一个对表的日期的年份进行分区的策略,这样每个年份都会被聚集在一个区间。
2.2 Range(范围)
这种策略是将数据划分不同范围。例如我们可以将一个千万级别的表通过id划分成4个分区,每个分区大约500W的数据,超过750W后的数据统一放在第4个分区。
2.3 Key(键值)
Hash策略的一种延伸,这里的Hash Key是MySQL系统产生的。
2.4、List(预定义列表)
这种策略允许系统通过定义列表的值所对应的行数据进行分割。例如,我们根据岗位编码进行分区,不同岗位类型的编码对应到不同的分区去,达到分治的目的。
上述的SQL脚本,使用了列表匹配LIST函数对员工岗位编号进行分区,共分为4个分区,行政岗位 编号为46,77,89的对应在分区P0中,技术岗位 106,125,177类别在分区P1中,依次类推即可。
2.5、Composite(复合模式)
复合模式其实就是对上面几种模式的组合使用,比如你在Range的基础上,再进行Hash 哈希分区。
3 测试Range策略
3.1 建立总表与分表
我们建立一个普通的用户表 users,再建立一个分区表users_part,将80年代出生的用户按照年份进行了分区,如下:
3.1.1 总表语句
3.1.2 分表语句
最后一行注意一下,是将89年之后出生的都归属到第10个分区上,我们这边模拟的都是80年代出生的用户,实际业务中跟据具体情况进行拆分。
3.2 初始化表数据
我们可以使用函数或者存储过程批量进行数据初始化,这边插入1000W条数据。
3.3 同步数据至完整表中
3.4 测试执行SQL的效率
结果比较清晰,分区表的执行效率确实比较高,执行时间是未分区表 1/10 都不到。
3.5 使用Explain执行计划分析
这边关注两个关键参数:一个 是partitions,users_part中是p7,说明数据检索在第七分区中,users表是null的,说明是全区域扫描,无分区。
另外一个参数是rows,是预测扫描的行数,users表明显是全表扫描。
3.6 建索引提效
因为我们使用birth字段进行分区和条件查询,所以这边尝试在birth字段上简历索引进行效率优化。
创建索引后的数据库文件大小列表:
再次测试SQL性能
这边可以看到,在关键的字段添加索引并重启(net stop mysql,net start mysql)之后,分区的表性能有略微提升。而未分区的全表性能提升最明显,几乎接近分区的效率。
3.7 跨区执行效率分析
通过上面的分析可以看出,在单个区内执行,比不分区效率又很明显的差距,这是因为分区之后扫描非范围缩小了。
那如果我们上面条件增加出生年份的范围,让他产生跨区域的情况,效果会怎么样呢,我们测试一下。
可见,跨区之后性能会差一些。这边应该这样理解,跨区的越多,性能越差,所以做分区设计的时候应该意识到,避免那种频繁的跨区情况发生,谨慎判断分区边界条件。
3.8 总结
1、分区和未分区占用文件空间大致相同 (数据和索引文件)
2、查询语句中关键字段未建立索引字段时,分区时间远远优于未分区时间
3、如果查询语句中字段建立了索引,分区和未分区的差别缩小,但是仍然优于未分区情况,而且随着数据量增加,这个优势会更明显。
4、对于大数据量,还是建议使用分区功能,无论他有没有建立索引。
5、根据MySQL手册, 增加myisam_max_sort_file_size 会增加分区性能( mysql重建索引时允许使用的临时文件最大大小)
6、对分区进行设计时,谨慎判断分区边界条件,避免有过度频繁的跨区操作,否则性能不会理想。
4 分区策略详解
4.1 HASH(哈希)
HASH分区主要用来确保数据在预先确定数目的分区中平均分布,而在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中,
而在HASH分区中,MySQL自动完成这些工作,
你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。示例如下:
上面的例子,使用HASH函数对createtime日期进行HASH运算,并根据这个日期来分区数据,这里共分为10个分区。
建表语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回整数的表达式,它可以是字段类型为MySQL 整型的一列的名字,也可以是返回非负数的表达式。
另外,可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。
每个分区都有自己独立的数据、索引文件的存放目录,并且这些目录所在的物理磁盘分区可能也都是完全独立的,可以提高磁盘IO吞吐量。
4.2 RANGE(范围)
基于属于一个给定连续区间的列值,把多行分配给同一个分区,这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。示例如下:
上面的示例,使用了范围RANGE函数对岗位编号进行分区,共分为4个分区,
岗位编号为1~100 的对应在分区P0中,101~200的编号在分区P1中,依次类推即可。那么类别编号大于300,可以使用MAXVALUE来将大于300的数据统一存放在分区P3中即可。
每个分区都有自己独立的数据、索引文件的存放目录,并且这些目录所在的物理磁盘分区可能也都是完全独立的,可以提高磁盘IO吞吐量。
4.3 LIST(预定义列表)
类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择分区的。LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,
然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。示例如下:
上面的例子,使用了列表匹配LIST函数对员工岗位编号进行分区,共分为4个分区,编号为46,77,89的对应在分区P0中,106,125,177类别在分区P1中,依次类推即可。
不同于RANGE的是,LIST分区的数据必须匹配列表中的岗位编号才能进行分区,所以这种方式只是适合比较区间值确定并少量的情况。
每个分区都有自己独立的数据、索引文件的存放目录,并且这些目录所在的物理磁盘分区可能也都是完全独立的,可以提高磁盘IO吞吐量。
4.4 KEY(键值)
类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。示例如下:
注意:此种分区算法目前使用的比较少,使用服务器提供的哈希函数有不确定性,对于后期数据统计、整理存在会更复杂,所以我们更倾向于使用由我们定义表达式的Hash,大家知道其存在和怎么使用即可。
4.5 嵌套分区(子分区)
嵌套分区(子分区)是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。
如上,对RANGE 分区再次进行子分区划分,子分区采用 HASH 类型。
5 分区管理
5.1 删除分区
5.2 重建分区
5.2.1 RANGE 分区重建
用于因空间过于浪费而产生的合并情况。
5.2.2 LIST 分区重建
5.2.3 HASH/KEY 分区重建
5.3 新增分区
5.3.1 新增 RANGE 分区
5.3.2 新增 HASH/KEY 分区
5.3.3 给已有的表加上分区
6 去除分区主键限制
默认分区限制分区字段必须是主键(PRIMARY KEY)的一部分,需要去除此限制。
如果表中设立主键,会报出如下提示:A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
一种解决方式就是使用主键来做为分区条件:
另外一种方式就是把分区条件字段加入主键中,变成联合主键。如下,id和gwcode 组成了联合主键:
本文转载自公众号架构与思维
