Mysql分库分表方案

  1. 云栖社区>
  2. 博客>
  3. 正文

Mysql分库分表方案

余二五 2017-11-07 15:34:00 浏览1706
展开阅读全文

Mysql分库分表方案

1.为什么要分表:

当一张表的数据达到几千万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

mysql中有一种机制是表锁定和行锁定,是为了保证数据的完整性


表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。


行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。


  1. mysql proxy:amoeba

做mysql集群,利用amoeba。

从上层的java程序来讲,不需要知道主服务器和从服务器的来源,即主从数据库服务器对于上层来讲是透明的。可以通过amoeba来配置。

3.大数据量并且访问频繁的表,将其分为若干个表

比如对于某网站平台的数据库表-公司表,数据量很大,这种能预估出来的大数据量表,我们就事先分出个N个表,这个N是多少,根据实际情况而定。

某网站现在的数据量至多是5000万条,可以设计每张表容纳的数据量是500万条,也就是拆分成10张表,那么如何判断某张表的数据是否容量已满呢?可以在程序段对于要新增数据的表,在插入前先做统计表记录数量的操作,当<500万条数据,就直接插入,当已经到达阀值,可以在程序段新创建数据库表(或者已经事先创建好),再执行插入操作。

  1. 利用merge存储引擎来实现分表

如果要把已有的大数据量表分开比较痛苦,最痛苦的事就是改代码,因为程序里面的sql语句已经写好了。用merge存储引擎来实现分表, 这种方法比较适合.

举例子:

640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy

数据库架构

1、简单的MySQL主从复制:

MySQL的主从复制解决了数据库的读写分离,并很好的提升了读的性能,其图如下:

其主从复制的过程如下图所示:
640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy

但是,主从复制也带来其他一系列性能瓶颈问题:

  1. 写入无法扩展

  2. 写入无法缓存

  3. 复制延时

  4. 锁表率上升

  5. 表变大,缓存率下降

那问题产生总得解决的,这就产生下面的优化方案,一起来看看。

2、MySQL垂直分区

如果把业务切割得足够独立,那把不同业务的数据放到不同的数据库服务器将是一个不错的方案,而且万一其中一个业务崩溃了也不会影响其他业务的正常进行,并且也起到了负载分流的作用,大大提升了数据库的吞吐能力。经过垂直分区后的数据库架构图如下:
640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy

然而,尽管业务之间已经足够独立了,但是有些业务之间或多或少总会有点联系,如用户,基本上都会和每个业务相关联,况且这种分区方式,也不能解决单张表数据量暴涨的问题,因此为何不试试水平分割呢?

3、MySQL水平分片(Sharding)

这是一个非常好的思路,将用户按一定规则(按id哈希)分组,并把该组用户的数据存储到一个数据库分片中,即一个sharding,这样随着用户数量的增加,只要简单地配置一台服务器即可,原理图如下:
640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy

如何来确定某个用户所在的shard呢,可以建一张用户和shard对应的数据表,每次请求先从这张表找用户的shard id,再从对应shard中查询相关数据,如下图所示:
640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy





拆分策略选择


其实拆分很灵活,有的是

垂直切分,将一个库拆成两个或多个,将有相关联的表放在一个库里。有的是


水平切分将数据量大的表按照一定逻辑进行拆分。


个人感觉垂直切分的相对来说缓解了IO的瓶颈,而水平切分,目的是减轻了单个表或某些表读写的压力。

我们项目根据个人需求,采用的水平切分,没有去分库。之后要看看需要采用何种的切分了。

了解到的有: 分表、分区、MERGE引擎分表。


MERGE引擎分表


简介


先介绍merge表,此方法只适用于MyISAM。我数据库的表都是采用InnoDB引擎的,所以首先就被pass了,但是还是在这里简单介绍下吧。

mysql 5.1 手册里的说的

640?tp=webp&wxfrom=5&wx_lazy=1

改变到MERGE引擎表,意味着成为一个被分区的表,这样将单一的表各分区存储在分离的文件中。分区可以使一些操作效率更显著,并且不受MyISAM存储引擎的限制。(蹩脚的英语,各位看官多担待吧。)


以上应该是使用merge表的主要原因吧。


创建使用


能够创建MERGE表的要求,首先是一组数据结构完全相同的表,并且存储引擎为MyISAM。


让我们先创建一个

640?tp=webp&wxfrom=5&wx_lazy=1

之后查询

640?tp=webp&wxfrom=5&wx_lazy=1

你创建了total表,只是相当于在t1,t2的表的基础上创建的,需要注意的是在单个表中的主键或唯一索引,放在MERGE后的total表中就不能再当唯一索引用了,这点应该比较好理解但还是要说一下的。


同时你可以drop或者ALTER TABLE tbl_name UNION=(...)改变表的数据集,这样可以让其动态变化,剔除不需要的。


使用场景


如果你的数据记录呈现一定时间规律,比如每天产生的一些需要记录的日志,可能你只需要最近一个月的或者最近几个月的,这样你可以每天或者一定时间创建一个数据表,当需要查询一段时间的数据,你只要将这段时间的数据表创建一张总计的MERGE表。这样数据集可以控制在可控的范围呢,不错吧。so easy。


分表


分表其实想法上很简单,顾名思义就是将现有的一张数据量大的表去拆分。如果数据库的性能瓶颈在几个关键表上,这时你可以将分表列入你考虑的范围。


遇到的问题


我说说我在实验分表时遇到的问题和相关解决方式


1.如何去分表


根据什么策略把现有表中的数据分到多个表中,并且还有考虑到以后的扩展性上。



是建立一张索引表,用户id与数据库id对应,(这里他将相同结构的表分在了不同的数据库中进一步减少压力,但同时对于数据的同步也需要通过其他手段来解决),其本质也是分表了同时分库了。这么做的好处是便于以后的扩展,但损耗一点性能,因为会多一次查询嘛。


个人想法,这样索引表可能会成为新的瓶颈,除非用户不会一直增长哈。


我的做法属于另一种,写了个算法通过计算某列值,按照一定规律将数据大致均分在每个分表中。至于扩展性,写算法时候考虑进去了以后增加分表数的问题了。


选择哪种策略,是要看自己的表的业务特点了,方法没有绝对的优缺,还是要根据自己的需求选取。


2.分表之后主键的维护


分表之前,主键就是自动递增的bigint型。所以主键的格式已经提早被确定了,像什么uuid之类的就被直接pass掉了。


还有想过自己写一个主键生成程序,利用Java 的Atomic原子量特性,但是考虑还需要增加工作量并且高并发下,这里很可能是个隐患。


还有就是通过应用层上管理主键,如redis中有原子性的递增。


网上较有名的策略是《Ticket Servers: Distributed Unique Primary Keys on the Cheap》, 大致意思是使用一张名Tickets64的MyISAM存储引擎表,专门用来存储主键,数据只有一行,用的话通过

640?tp=webp&wxfrom=5&wx_lazy=1

来取。并且设置了两个库,相同的方法,只是每次增长的步长不同,防止一个宕掉,还可以稳定运行。


2.动态选择表名


表分好之后,问题又来了,数据库层我们的项目使用的是Mybatis框架。SQL语句都写在了xml文件中,现在我需要动态的设置表名。


其实设置mybatis本身,就可以解决这个问题

640?tp=webp&wxfrom=5&wx_lazy=1

只要把属性statementType设置为STATEMENT,表名就可以以参数形式传入。传入参数时要以美元符${columnName}这样传入参数,至于Statement,PreparedStatement 的区别我想大家应该都能知道的。


另一种解决方式,是使用《shardbatis插件》,它是开源的,可以实现数据水平切分功能,有兴趣的朋友可以了解下。


分区表


从mysql5.1之后,提供了一种partition引擎的表,看这句

640?tp=webp&wxfrom=5&wx_lazy=1

在我的理解,如果把一张表分区之后,不同分区放在不同磁盘位置上,对整体的读取是否更有益?


分区表优缺点


这里主要是看的mysql手册,我也就起到了个翻译的作用。

640?tp=webp&wxfrom=5&wx_lazy=1

在选择mysql 分区方案时,还有一个需要考虑的,在mysql的bug中有一个关于mysql分区表查询缓存的bug: 《Partitioning + Query Cache》,因为这个问题,mysql已经将分区表的查询缓存disable了,无论你是否开启查询缓存,都不会启用查询缓存。如果你在意这点,请慎重选择方案。


以上是关于,mysql三个拆分方案的总结,资料方面都是自己查找的所以不免有些会不准确,如有发现请务必告知,希望与各位共成长~~~。











本文转自 chengxuyonghu 51CTO博客,原文链接:http://blog.51cto.com/6226001001/1726092,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注