上海网站建设工资多少,手工艺品网站建设,wordpress注册邮件,自动升级wordpress失败前言
上一篇介绍了 MySQL 的存储过程和触发器#xff0c;这一篇将介绍表分区和分库分表相关的内容。
表分区
原本的表文件都是以完整的形式存储在磁盘中#xff0c;而表分区则是指将一张表的数据拆分成多个磁盘文件#xff0c;然后放到磁盘中存储。
做了表分区之后…前言
上一篇介绍了 MySQL 的存储过程和触发器这一篇将介绍表分区和分库分表相关的内容。
表分区
原本的表文件都是以完整的形式存储在磁盘中而表分区则是指将一张表的数据拆分成多个磁盘文件然后放到磁盘中存储。
做了表分区之后表在逻辑上还是同一张只是磁盘中会划分为多个文件存储而已所以表分区并不会影响原有的增删改查操作。
表分区只能进行水平划分即以行为粒度进行划分一条记录只能在一个分区中。
好处
相较于使用单个文件存储表数据表分区技术可以打破单个磁盘分区的容量限制。对于一些失效数据如三年前的数据可以通过快速删除分区的方式清理效率十分高。能够在一定程度上提升磁盘 IO 检索数据的性能毕竟只需对一小片磁盘表文件做寻道。支持聚合函数的并行执行比如 sum()、count() 这类函数可以分别统计各分区的数据做汇总。带来更好的数据管理性和可用性当一个表文件受损时不会影响其他分区文件中的表数据。
限制
单张表最多只能创建 1024 个分区MySQL5.6 版本中拓展到 8192 个。MySQL5.1 及之前的版本中分区键只能选择整数型字段或支持哈希函数处理的字段。对一个表做了分区后后续使用表的过程中无法对表上的其他字段建立唯一索引。分区表中无法创建外键不过一般情况下表也不允许创建外键都是靠逻辑上维护主外关系。表中存在主键、唯一键的情况下分区键的字段必须为主键或唯一键的部分或全部字段。
在 MySQL 中总共支持六种分区类型range、list、hash、key、sub、columns。
RANGE
按照一个字段范围进行分区仅支持整数类型字段作为分区键如果想要以日期字段来做数据分区需要想将其转换为整数格式的时间戳。
partition by range(r_id)(partition p1 values less than (100000),partition p2 values less than (200000),partition p3 values less than (300000),partition p4 values less than maxvalue
);-- 查询 zz_range 表中不同分区的数据量
select partition_name,table_rows
from information_schema.partitions
where table_name zz_range;LIST
枚举分区只支持整数字段作为分区键。如果插入的数据在所有分区中找不到对应的值会直接报错。
partition by list(l_sex)(partition p1 values in (0),partition p2 values in (1)
);HASH
哈希分区中支持两种哈希分区法
常规哈希基于某个整数型字段直接做取模最后根据余数来决定数据的分区。线性哈希基于某个字段哈希之后的哈希值进行取模运算最后根据余数来决定数据的分区。
常规哈希只能基于整数型字段对数据做划分线性哈希则可以不限制字段的类型只要能够通过 MySQL 哈希函数转换出哈希值的字段类型都可以作为分区键但本质上 MySQL 中好像没有提供将字符串转换为数值类型的哈希函数。
-- 选用 h_id 作为分区键划分为三个分区
partition by hash(h_id)
partitions 3;
-- 使用线性哈希分区
partition by linear hash(lh_id)
partitions 3;KEY
在 hash 分区中想要使用一个字段作为分区键要么这个字段本身是整数类型要么这个字段经过哈希函数处理后能够得到一个整数的哈希值才行。但在 key 分区中除开不支持 text、blob 两种类型外其他类型的字段都可以作为分区键。
在 key 分区中也可以不显式指定分区键MySQL 会自动选择但不管是自己显式声明分区键亦是 MySQL 自动选取分区键都会遵循如下规则
表中只存在主键或唯一字段时分区键必须为主键/唯一键的部分或全部字段不允许选择其他字段。表中主键、唯一字段同时存在时分区键必须为主键和唯一键共有的部分或全部字段。当表中不存在主键或唯一键时分区键可以是除 text、blob 类型外的任意单个或多个字段。
partition by key(k_name)
partitions 3;SUB
又称子分区所谓的子分区是指基于表分区后的结果进一步做分区处理也就是基于一个分区再做分区好比一张表可以基于日期中的年份做分区基于年份做了分区后还可以基于年分区进一步做月分区。
这种方式要求每个一级分区下的二级分区数量都一致同时二级分区的类型只能为 hash、key 类型。
partition by range(year(register_time))
subpartition by hash(month(register_time))
(partition p1 values less than (2000)(subpartition p1_s1,subpartition p1_s2),partition p2 values less than (2020)(subpartition p2_s1,subpartition p2_s2),partition p3 values less than maxvalue(subpartition p3_s1,subpartition p3_s2)
);COLUMNS
cloumns 分区实际上是 range、list 分区的变种cloumns 分区可以使得 range、list 的分区键由多个字段来组成同时支持的字段类型也相对更丰富一些但这种分区法一般用的极少。
分表
垂直分表
当一张表由于字段过多时会导致表中每行数据的体积变大一方面会导致磁盘 IO 次数增多影响数据的读写效率同时另一方面结果集响应时还会占用大量网络带宽影响数据的传输效率再从内存维度来看单行数据越大缓冲区中能放下的热点数据页会越少当读写操作无法在内存中定位到相应的数据页从而又会产生大量的磁盘 IO。
垂直分表一半可以根据冷热字段对表进行拆分。拆分的表中需要保存外键来建立联系。
由于修改数据时会同时修改多张表所以需要使用事务来保证原子性。
水平分表
当一张表内的数据量过大一般要求控制在 500-1200w 之间查询性能就会下降从而需要对表进行水平拆分。
水平分表后多个表的表结构、索引相同数据不同每张表中会存储不同范围的数据。
水平分表和表分区十分类似一般会选用水平分表方案。
由于数据会被存储到多个表中所以进行增删改查数据前需要定位到相应的表中再进行操作。且进行聚合操作时需要从多个表中取出数据再在后端进行聚合操作或者依赖 Redis、ES 等第三方中间件来完成。
另外可能出现多个表中 ID 相同数据不同的情况所以要合理设置 ID 规则来避免。比如可以设置交叉增长的 ID可以利用特殊算法雪花算法等生成有序的分布式 ID利用第三方中间件生成 ID 等。
分库
如果数据库整体负载都很高那不管再怎么做分表也不能解决问题此时就需要考虑分库方案。
垂直分库
可以按业务特性将大库拆分为多个业务功能单一的小库每个小库只为对应的业务提供服务。
一次查询需要的数据如果被分到了多个库中就需要用额外的手段来获取数据。比如通过广播表/网络表/全局表将对应的表数据直接完全同步一份到相应库中或者在程序中组装数据再返回。
修改数据时可能会一次性修改不同库中的数据所以需要考虑数据一致性问题即分布式事务问题就需要分布式锁等额外的手段来保证数据一致性。
垂直分库无法解决部分库的性能瓶颈所以可能还需要分表或者水平分库。
水平分库
当单个数据库节点的性能达到瓶颈后除了提升该节点的配置外还可以通过水平拆分的方案将数据存储在多个节点上从而分担压力。
水平分表中存在的问题在水平分库中也会遇到。
最后
本文介绍了 MySQL 的表分区和分库分表。
下一节将介绍 MySQL 的主从架构。
