Mysql分库分表方案

Mysql分库分表方案

1.为什么要分表：

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

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

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

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

2. mysql proxy：amoeba

做mysql集群,利用amoeba。

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

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

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

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

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

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

举例子：

数据库架构

1、简单的MySQL主从复制:

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

其主从复制的过程如下图所示：

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

1. 写入无法扩展

2. 写入无法缓存

3. 复制延时

4. 锁表率上升

5. 表变大，缓存率下降

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

2、MySQL垂直分区

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

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

3、MySQL水平分片（Sharding）

这是一个非常好的思路，将用户按一定规则（按id哈希）分组，并把该组用户的数据存储到一个数据库分片中，即一个sharding，这样随着用户数量的增加，只要简单地配置一台服务器即可，原理图如下：

如何来确定某个用户所在的shard呢，可以建一张用户和shard对应的数据表，每次请求先从这张表找用户的shard id，再从对应shard中查询相关数据，如下图所示：

拆分策略选择

其实拆分很灵活，有的是

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

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

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

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

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

MERGE引擎分表

简介

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

mysql 5.1 手册里的说的

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

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

创建使用

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

让我们先创建一个

之后查询

你创建了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存储引擎表，专门用来存储主键，数据只有一行，用的话通过

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

2.动态选择表名

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

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

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

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

分区表

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

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

分区表优缺点

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

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

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