数据库中的分库分表

前言

首先声明,我自己做过的所有项目,最多的一个数据库不过也就刚刚破万的数据量,根本就用不到这个分库分表,但是架不住面试官他要问啊,所以还是自己实践一下,然后记录下吧。

为什么要分库分表

首先分库和分表是两回事,可能我只分库,也可能我只分表(当然只分表可能意义没那么大);当然实际中遇到的大部分都是既分库又分表。

单表一般能支持差不多刚破千万左右,所以当你的数据量超过千万的时候,性能就会下降,为了提高性能,我们就不得不进行数据库分库和分表。

分表

一张表里面几千万条数据,你会发现你的sql执行时间大大增加,此时我就需要多弄几张表,然后把数据分到这些表里。比如我可以按照这种规则进行分表:根据用户id的最后一位来分成10个表,如果最后一位是0,那么就它的数据就在数据库1里面,以此类推。这样数据量就从千万级别掉回到了百万级别,又可以愉快的运行了。也叫做水平拆分。

分库

就是把一个数据库里的一些表格给拆分出来,放到一个新的数据库中,也叫做垂直拆分。最好是把有join关系的表分在一起,当然我实际中是尽量避免表与表之间进行join关联的。

分库分表

首先把表进行水平拆分,然后把拆出来的东西放到各个数据库中,这也是日常用到最多的一种了吧。

Mycat

平时我们的java程序都是直接访问数据库,mycat则是作为一个数据库中间件,我们不再直接访问数据库,而是访问mycat,由它来帮我们去访问数据库。之后的读写分离、分库分表都是由它来解决。

原理

原理其实很简单,mycat就是把你的sql语句进行了拦截,然后进行分析,分析完成之后把这条语句发到真正的数据库中,就这么简单,也就是它其实就是你的java程序的一个proxy而已。

安装

我是去官网直接下载,直接解压后即可使用,毕竟是java写的嘛。

配置

对于mycat的配置文件主要有三个,分别是:

  • server.xml文件,这个文件其实就是mycat对java程序所暴露的一个逻辑数据库,你的java程序都访问它就可以了。
1
2
3
4
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>

可以看到就是配置了一个用户叫mycat,并且为这个用户设置了密码,创建了基本的数据库TESTDB。之后你就可以通过mysql -umycat -p123456 -P 8066 -h mycat的主机ip 来访问这个中间件,再也不用去访问背后真正的数据库了。

  • schema.xml文件,这个文件就是配置主从复制关键一步。把上面配置的TESTDB对应到了一个叫dn1的节点,而dn1这个节点又对应了真正的数据库testdb,同时这个数据库背后又是一台读主机和一台写主机。
1
2
3
4
5
6
7
8
9
10
11
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="testdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="127.0.0.1:3306" user="root"
password="123456">
<readHost host="hostS1" url="另外一台主机ip:3306" user="root" password="123456" />
</writeHost>
</dataHost>
  • rule.xml文件,这个文件就是用来存放水平分表的时候所用到的各种规则的,这里先不展示了。

MySQL的主从复制

虽然说主从复制其实和这个分库分表完全没有关系,但是既然都用到了mycat了,那就顺手学了吧。

一般都是一主多从的模式,而且主一般是用来写入数据,而从机则是用来读取数据的。

主机配置(阿里云主机)

在MySQL中配置,注意!!是MySQL的配置文件中,和mycat毫无关系。

  • 配置唯一的ID
  • 启用二进制的日志
  • 设置好不需要复制的数据库(那类似mysql这种系统用的数据库肯定是不需要复制的)
  • 设置好需要复制的数据库
  • 设置binlog的格式。binlog有以下的格式:
    • statement,就是记录下你的更新删除和插入的语句,然后记录下来,接下来只需要去从库中照着执行一遍就行,缺点就是如果执行了类似time = now()这种语句的话,主从之间会出现不一致。
    • row,记录每一行的数据,效率不够好。
    • mixed,自动选择。但是对于@@host name支持不好。

总体配置好的长这样:

1
2
3
4
5
6
7
8
9
10
11
# id
server-id=1
# 启用binlog
log-bin=mysql-bin
# 哪些数据库不想复制
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 哪些数据库要复制
binlog-do-db=testdb
# binlog的格式
binlog_format=STATEMENT

从机配置(腾讯云主机)

同样也是配置MySQL,和mycat一毛钱关系都没有!!!

  • 配置唯一的ID
  • 启用中继日志

配置好之后长这样:

1
2
server-id=2
relay-log=mysql-relay

就这两行,收工。

建立关系

  1. 主机设置好slave的权限和密码:GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
  2. 主机查看对应的文件和position:show master status;记录下文件名和位置。比如下图就是文件名是mysql-bin.000002,位置是2245.

image-20200818002320420

  1. 去从机执行:CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;具体值看上图就知道了。

  2. 从机开始从主机获取消息,开始复制它:start slave;

此时顺利的话,你在主机上对testdb这个数据库进行的各种操作(建表啊,对表进行插入之类的)都会同步到从库中。

使用mycat进行读写分离

再次强调,上面的MySQL主从复制,跟mycat可是一点点关系都没有!!!而真正发生关系的,是从现在开始。

mycat可以根据你的设置,然后根据不同的sql语句,自己去操作对应的数据库。我们首先故意通过@@hostname来为数据库插入不同的数据:insert into mytbl values (1,@@hostname),然后通过mycat进行数据的访问,看看是否会进行读写分离。

但是!!!你会发现不论你重复多少次,访问到的都是主库的内容。哎?配置文件里明明主库是writehost,而从库才是readhost呀?其实你仔细看看,还有一个叫balance的选项,默认配置了0,而它的配置有:

  • balance=”0”, 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。这就解释了为什么都是发给writeHost。

  • balance=”1”,全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。工作中常用。

  • balance=”2”,所有读操作都随机的在 writeHost、readhost 上分发。 基本不会用。

  • balance=”3”,所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力,工作中常用。

我修改了balance为3,此时读到的就全部是从库的内容了,但是由于这个实验里只有一台从库,所以效果可能没那么震撼吧。然后修改成了2,确实是两台主机的内容都可能被读取到。

MySQL的双主双从

简单来说就是两台Master,它们互相都为对方的备份,同时这两台Master都有自己的slave。好处就是上面的一主多从模式,如果主机挂了就全完蛋了,而这个双主双从允许你一台Master挂掉。

##垂直分库

兜兜转转讲了这么一大圈,其实现在才开始分库。从现在开始,一切内容和主从复制无关,你可以手动把主从复制关掉了(当然不关也没关系,反正我们之后用的数据库也不是testdb了),这里是为了提醒你,接下来的各个主机它们之间的关系是平等的,而不再是master-slave了。

分库是把同一个数据库的表,分到不同的数据库(主机)上,分库的原则也很简单:有紧密关联关系的表应该在一个库里,相互没有关联关系的表可以分到不同的库里。注意在不同的数据库上的表就不能进行join连接了。

具体配置

现在需要修改schema配置文件,改成这样子的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!-- 表customer放到dn2中,其余的表放到dn1中 -->
<table name="customer" dataNode="dn2" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="orders" />
<dataNode name="dn2" dataHost="host2" database="orders" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="127.0.0.1:3306" user="root"
password="123456">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="dn2主机的ip:3306" user="root"
password="123456">
</writeHost>
</dataHost>

非常简单容易理解,就是把符合条件的表分到另外一个dataHost(主机)中即可。

然后我们首先需要到这两个数据库中,把orders这个数据库创建好,接下来连接到mycat,在mycat中切换到TESTDB,然后创建好四张表。创建表的语句:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#客户表 rows:20万
CREATE TABLE customer(
id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id)
);

#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT, order_type INT,
customer_id INT,
amount DECIMAL(10,2), PRIMARY KEY(id)
);

#订单详细表 rows:600万
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT,
PRIMARY KEY(id)
);

#订单状态字典表 rows:20
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id)
);

然后你去真正的两个数据库查看,会发现一个里面有一张有customer表,另外一个数据库里面有剩余的三张表。

解释

也就是其实mycat会通过你的sql语句看到,如果是访问customer表,或者是修改删除插入等操作,只要是customer表,它就会自动给你发往dn1,而剩下的三个就会给你发往dn2,这样垂直分库就完成了,是不是非常简单呢?

水平分表

虽然分表只要把一张表水平分成多个部分就算分表了,但是如果不分到各个机器上,那么意义不大,所以分表一般肯定是跨越数据库的。

怎么分?

就上面那个orders来进行水平分表,它有四个属性,那么我们根据什么来进行区分呢?这个相比起垂直分库,就没那么标准的答案了,因为它其实是根据实际经验来的。

  • 比如我可以根据id来区分,这样相当于根据时间来分,那么肯定是最近的会被频繁查询,而越古老的数据访问次数越少,所以我们可以让老旧的机器来负责老旧的数据,而高性能的数据负责最新的数据。
  • 比如我也可以根据用户的id来区分,这样一个人的所有数据都会放到一台机器上,这样访问可能会更快。
  • 当然你还可以根据实际情况,根据表的属性来自己区分。

具体配置

水平扩展开始,rule.xml这个文件就开始发挥作用了。

首先我们需要去修改一下之前的schema.xml文件:

1
2
3
4
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!-- 表orders进行水平分表,所以两个节点都会发送,且遵守规则 mod_rule -->
<table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
</schema>

上面的还是非常好理解的,重点其实是rule的设置:

1
2
3
4
5
6
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>

name就是对应上面schema中的rule,然后是对表中的customer_id列,使用mod-long算法,而这个算法是默认提供的:

1
2
3
4
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>

你节点有几个,就设置几,这样就会分布到这些节点中。

接下来首先去dn2中创建orders表格(因为刚刚没这张表),然后重启mycat,并且连接到mycat中,进行下面的语句:

1
2
3
4
5
6
INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100); 
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);

注意!! 虽然insert语句在数据全的时候是可以省略列名的,但是这里不可以,因为要根据你的语句来进行数据库的定位。

如果查询一下表格,结果如下图:

image-20200818011840052

你会发现id不是顺序排列的,反而是根据customer_id来进行排序的。接着再到各个数据库查看,你会发现126这三条数据是一个数据库中的,而345是在一个数据库中的。

更多的水平分表规则

除了上面的那个根据指定的列进行mod来判断一个字段应该属于哪张表,还有下面的规则:

  • 如果两张表是有join关系的,那么我们肯定希望表A中和表B中对应的数据要分到同一个数据库中去,不然就无法执行join了。此时我们可以用相关的规则,来让相应的行都分配到同一个数据库中。
  • 如果有一张表,它数据量不大,而且所有的表都和它有关系,那么mycat提供了一个全局表的概念,会在所有的节点上存在这个表,这样大家都可以访问它,而且操作更新它都会在所有的节点中执行,这样所有的表都可以和全局表进行join操作。
  • 分片枚举。比如可以根据订单的地址,我们可以枚举出中国的所有省,然后根据订单的地址来分配到固定的数据库。
  • 范围约定,可以理解为大范围的枚举,指定一个范围,然后该范围的列,会被分到同一个数据库。
  • 日期约定。见名知意。

全局序列

如果实现了分库分表,那么数据库的自增主键就会有问题,因为在一个数据库里不重复,不代表着你在全局不重复,针对这点,mycat也提出了解决方案:

  • mycat在本地存放一个文件,然后会读取这个文件中的值作为主键,这样就不会重复了。优点是速度快,但是缺点是如果mycat宕机就完蛋了。
  • 通过数据库的方式。因为mycat背后有许许多多的数据库,mycat可以挑选其中的一个,然后问它要一段序列,比如是(1001-2000),然后每次来一个就发放一个,发完了再去问数据库要。此时就算宕机,也不怕,因为接下来再问数据库去要,数据库会把这个数字累加,并不会重复。
  • 时间戳或者UUID,缺点是主键太长了,不太好。
  • 利用redis的单线程特点,来生成序列。
  • 利用开源的实现:snowflake算法和美团的leaf。

Mycat高可用

来日再填坑。