mysql分区方案的研究

时间:2024-03-25 21:08:14

笔者觉得,分库分表确实好的。但是,动不动搞分库分表,太麻烦了。分库分表虽然是提高数据库性能的常规办法,但是太麻烦了。所以,尝试研究mysql的分区到底如何。

之前写过一篇文章,http://www.cnblogs.com/wangtao_20/p/7115962.html 讨论过订单表的分库分表,折腾起来工作量挺大的,需要多少技术去折腾。做过的人才知道有多麻烦

要按照什么字段切分,切分数据后,要迁移数据;分库分表后,会涉及到跨库、跨表查询,为了解决查询问题,又得用其他方案来弥补(比如为了应对查询得做用户订单关系索引表)。工作量确实不小。

从网上也可以看到,大部分实施过的人(成功的)的经验总结:水平分表,不是必须的,能不做,尽量不做。

  像阿里这些系统,数据库单表数量十多亿,达到瓶颈了,不得不做分库分表,扩容也方便。没有选择。
  那么,针对起步阶段的业务,技术人员不够,产品还处在试错阶段。是不是可以考虑一下分区方案。
   笔者几年前,也犯了思维错误,在小公司做系统,产品还在开发,有待推向市场验证。那个时候,笔者就去考虑什么评论表数据量大的情况下要怎么做,其实伤脑,又费时间,业务没有做起来,其实没多少用处。
  架构是演变出来的,不是设计出来的。企图一开始就设计大炮,结果只有蚊子。笔者做试验看看mysql的分区到底是什么个原理。研究发现,其实跟分表差不多,比如按hash打散数据、按值范围分散数据。

、探讨分区的原理

了解分区到底在做什么,存储的数据文件有什么变化,这样知道分区是怎么提高性能的。

实际上:每个分区都有自己独立的数据、索引文件的存放目录。本质上,一个分区,实际上对应的是一个磁盘文件。所以分区越多,文件数越多。

现在使用innodb存储较多,mysql默认的存储引擎从mysiam变为了innodb了。

以innodb来讨论:

innodb存储引擎一张表,对应两个文件:表名.ibd、表名.frm。

如果分区后,一个分区就单独一个ibd文件,如下图:

mysql分区方案的研究

将fs_punch_in_log表拆分成4个分区,上图中看到,每个分区就变成一个单独的数据文件了。mysql会使用"#p#p1"来命名数据文件,1是分区的编号。总共4个分区,最大值是4。

分表的原理,实际上类似,一个表对应一个数据文件。分表后,数据分散到多个文件去了。性能就提高了。

分区后的查询语句

语句还是按照原来的使用。但为了提高性能。还是尽量避免跨越多个分区匹配数据。

如下图,由于表是按照id字段分区的。数据分散在多个分区。现在使用user_id作为条件去查询。mysql不知道到底分配在哪个分区。所以要去全部分区扫描,如果每个分区的数据量大,这样就耗时很长了。

mysql分区方案的研究

分区思路和分区语句

id字段的值范围来分区:在1-2千万分到p0分区,4千万到-6千万p1分区。6千万到8千万p2分区。依此推算下去。这样可以分成很多的分区了。

为了保持线性扩容方便。那么只能使用range范围来算了。

sql如下

CREATE TABLE `fs_punch_in_log` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键自增' ,
`user_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '签到的用户id' ,
`punch_in_time` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '打卡签到时间戳' ,
PRIMARY KEY (`id`)
) partition BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (40000000),
PARTITION p2  VALUES LESS THAN (80000000), 
PARTITION p3  VALUES LESS THAN (120000000),
PARTITION p4  VALUES LESS THAN MAXVALUE

);

  以上语句经过笔者测验,注意点:

  • 按照hash均匀分散。传递给分区的hash()函数的值,必须是一个整数(hash计算整数计算,实现均匀分布)。上面的id字段就是表的主键,满足整数要求。
  • partition BY RANGE 中的partition BY表示按什么方式分区。RANGE告诉mysql,我使用范围分区。

情况:如果表结构已经定义好了,里面有数据了,怎么进行分区呢?使用alter语句修改即可,经过笔者测验了。

ALTER TABLE `fs_punch_in_log`
PARTITION BY RANGE (id)
( PARTITION p1 VALUES LESS THAN (40000000),
PARTITION p2 VALUES LESS THAN (80000000),
PARTITION p3 VALUES LESS THAN (120000000),
PARTITION p4 VALUES LESS THAN MAXVALUE )

  

注:由于表里面已经存在数据了,进行重新分区,mysql会把数据按照分区规则重新移动一次,生成新的文件。如果数据量比较大,耗时间比较长。

二、四种分区类型

mysql分区包括四种分区方式:hash分区、按range分区、按key分区、list分区。

四种有点多,实际上,为了好记,把类再缩小点,就两大类方式进行分区:一种是计算hash值、一种是按照范围值。

其实分库分表的时候,也会用到两大类,hash运算分、按值范围分。

1、HASH分区

有常规hash和线性hash两种方式。

  • 常规hash是基于分区个数取模(%)运算。根据余数插入到指定的分区。打算分4个分区,根据id字段来分区。

怎么算出新插入一行数据,需要放到分区1,还是分区4呢?  id的值除以4,余下1,这一行数据就分到1分区。

            常规hash,可以让数据非常平均的分布每一个分区。比如分为4个取,取余数,余数总是0-3之间的值(总到这几个分区去)。分配打散比较均匀。

            但是也是有缺点的:由于分区的规则在创建表的时候已经固定了,数据就已经打散到各个分区。现在如果需要新增分区、减少分区,运算规则变化了,原来已经入库的数据,就需要适应新的运算规则来做迁移。

            实际上在分库分表的时候,使用hash方式,也是数据量迁移的问题。不过还好。

针对这个情况,增加了线性hash的方式。

  • 线性HASH(LINEAR HASH)稍微不同点。

实际上线性hash算法,就是我们memcache接触到的那种一致性hash算法。使用虚拟节点的方式,解决了上面hash方式分区时,当新增加分区后,涉及到的数据需要大量迁移的问题。也不是不需要迁移,而是需要迁移的数据量小。

在技术实现上:线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规哈希使用的是求哈希函数值的模数。

线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY”子句中添加“LINEAR”关键字。

两者也有有相同的地方:

  • 都是均匀分布的,预先指定n个分区,然后均匀网几个分区上面分布数据。根据一个字段值取hash值,这样得到的结果是一个均匀分布的值。后面添加新的分区多少需要考虑数据迁移。
  • 常规HASH和线性HASH,因为都是计算整数取余的方式,那么增加和收缩分区后,原来的数据会根据现有的分区数量重新分布。
  • HASH分区不能删除分区,所以不能使用DROP PARTITION操作进行分区删除操作;

考虑以后迁移数据量少,使用线性hash。

2、按照range范围分区

范围分区,可以*指定范围。比如指定1-2000是一个分区,2000到5000千又是一个分区。范围完全可以自己定。后面我要添加新的分区,很容易吗?

3、按key分区

   类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

4、按list方式分区

可以把list看成是在range方式的基础上的改进版。list和range本质都是基于范围,自己控制范围。

range是列出范围,比如1-2000范围算一个分区,这样是一个连续的值。

而list分区方式是枚举方式。可以指定在1,5,8,9,20这些值都分在第一个分区。从list单词的字面意思命名暗示就是列表,指定列表中出现的值都分配在第几个分区。

三、如何根据业务选择分区类型

1、何时选择分区,何时选择分表

分表还是比分区更加灵活。在代码中可以自己控制。一般分表会与分库结合起来使用的。在进行分表的时候,顺带连分库方案也一起搞定了。

分表分库,性能和并发能力要比分区要强。分表后,有个麻烦点:自己需要修改代码去不同的表操作数据。

比如用户表分表后,计划分4个表,每个表4千万用户。按照用户编号取模为4。

代码很多处要做专门的匹配如下:

     每次操作用户资料,先要根据uid算出是哪个表名称。然后再去写sql查询。
    当然,是可以使用数据库中间件来做完成分库、分表。应用代码不用修改。大部分中间件是根据他们自己的业务特点定制的,拿来使用,不见得适合自己的业务。所以目前缺少通用的。
     如果使用分区的方式。代码不用修改。sql还是按照原来的方式写。mysql内部自动做了匹配了。

非常适合业务刚刚起步的时候,能不能做起来,存活期是多久不知。不用把太多精力花费在分库分表的适应上。

    考虑到现在业务才起步,使用分区不失为一种既省事又能提高数据库并发能力的办法。等以后业务发展起来了,数据量过亿了,那个时候经济实力已增强,再做改进方案不迟。
    架构是演变出来的,不是设计出来的。适应当前业务的方案,就是好的方案。
    过度设计也是一种负担:很多技术,企图一开始就设计出一个多大量的系统,实际上没有那种量,为了显示自己技术牛逼。
    总结:访问量不大,但是数据表行数很多。可以使用分区的方式。访问量大,数据量大,可以重构成分表的方式。
    这是因为虽然数据量多,但是访问量不大,如果使用分表的话,要修改代码很多地方,弄起来太麻烦了。投入多,产出少就没必要了。
2、如何选择适合自己的分区类型
使用分区和分表一样的思想:尽量让数据均匀分散,这样达到分流、压力减小的效果。如果不能均匀分布,某个分区的操作量特别大,出现单点瓶颈。
虽然4种类型的分区方式。其实总共两大类,按范围分区和按hash运算分区。
range范围分区,适合按照范围来切分数据。比如按时间范围分区。
hash,适合均匀分散数据。使用hash分区,麻烦点是后续增加分区,数据要迁移。有了线性hash分区法,这个迁移量减低了很多。
以用户表为例子,如果要使用分区方案。改使用哪种分区类型呢?
    考虑到user_id一般不会设计成自增数字。有人会奇怪,怎么不是自增的,我见过好多用户编号都是自增的!
    的确,有自增数字做uid的,不过一般是开源系统为了省事,比如discuz、ecshop等。人家没那么多工作量给你设计用户编号方案。
    自增的用户编号,由于是每次加1进行递增的。这规律太明显了,很容易被别有用途的人猜测user_id。再说,别人一看就知道你有多少用户! 
    有个原则,设计编号的时候,尽量不要让外部知道你的生成规律。比如订单号,如果是逐个加1的订单号,外界可以猜测出你的业务订单总数出来。
    说一个自增用户编号的例子。笔者曾经在一家上市互联网公司,有几千万的用户,uid过去是discuz那一套自增的方式。后来不得不改掉user_id的生成方式。笔者当时负责了这个改造方案。
    不是自增的数字,会是这种:注册一个是1897996,再注册一个是9689898,外界完全找不到数字的规律。
    不是自增的编号,如果使用范围来分区,各个分区的数据做不到均匀分布的。原因如下:
    比如说用户编号为1-200000000的用户分配到p1分区,20000000-40000000分配到p2分区,40000000-60000000分配到p3区,这样类推下去。
    由于用户编号不是自增,注册分配到的用户编号,可能是1到2千万之间的数字,也可能是4千万到6千万之间的一个数字。如果被分配到4千万到6千万的数字会更多,那么各个分区给到的数据是不均匀的。
    故不好使用范围来分区。
    比较好的办法是,使用hash取模,user_id%分区数。数据就可以分散均匀到4个分区去了。