DBLE分库分表实战

环境： dble 2.19.03.0

os版本： centos linux release 7.6.1810 (core)

ip： 192.168.20.10/24

mysql版本： mysql-社区版-5.7.26

添加2个账号授权：

create user 'rw'@'%' identified by 'rw123456';

create user 'rd'@'%' identified by 'rd123456';

grant select, insert, update, delete, create,references,create temporary tables,index on *.* to rw@'%' ;

grant select on *.* to 'rd'@'%' ;

连接方式：

读写：

mysql -urw -prw123456 --port 8066 -h 192.168.20.10 testdb

只读：

mysql -urd -prd123456 --port 8066 -h 192.168.20.10 testdb

ddl专用：

mysql -uop -p123456 --port 8066 -h 192.168.20.10 testdb

管理账号：

mysql -uman1 -p654321 --port 9066 -h 192.168.20.10

解压dble：

tar xf dble-2.19.03.tar.gz /usr/local/

cd /usr/local

ln -s dble-2.19.03 dble

cd conf/

vim schema.xml 修改后的如下：

vim rule.xml 修改后的内容如下：

[root@centos7 /usr/local/dble/conf ]# vim autopartition-long_t.txt # 增加一个路由规则文件

vim server.xml 内容如下：

然后， reload 下 dble ，进行测试

mysql -uop -p123456 --port 8066 -h 192.168.20.10 testdb

去创建符合上面的要求的几个表,并写入数据测试：

############################################################################

上面就是几种常用的分区了，另外还有种 date类型按时间分区的可能在日志表的场景下也常用些。

date类型分区的实验：

先去后端的db上创建物理的库：

修改后的 schema.xml 类似如下：

然后，到 rule.xml中添加规则：

date类型的可用分区的监控（脚本的原理同样适用于其他类型的分区）：

简单的做法就是定期执行一个explain的insert插入测试，如果有error关键字就告警出来

date类型加新的分片的方法：

######################################################################################################

er 表（互联网场景下用多表join的不多，因此er分片规则不太常用到，但是需要大致的了解）：

下面的内容大篇幅参考： https://blog.csdn.net/zhanglei_16/article/details/50779929

1：er分片关系简介

有一类业务，例如订单（order）跟订单明细表（order_detail）,明细表会依赖订单单，就是该会存在表的主从关系，

这类似业务的切分可以抽象出合适的切分规则，比如根据用户id切分,其它相关的表都依赖于用户id，再或者根据订单id进行切分，

总之部分业务总会可以抽象出父子关系的表。这类表适用于er分片表，子表的记录与所关联的父表记录存放在同一个数据分片上，

避免数据join跨库操作，以order与order_detail例子为例，schema.xml中定义合适的分片配置,order,order_detail 根据order_id

迕行数据切分，保证相同order_id的数据分到同一个分片上，在进行数据插入操作时，mycat会获取order所在的分片，

然后将order_detail也插入到order所在的分片

2：父表按照主键id分片，字表的分片字段与主表id关联，配置为ＥＲ分片

2.1：在schema.xml添加如下配置配置文件修改

</table>

在rule.xml里面设定分片规则：

<rule>

<algorithm>hashmod3</algorithm>

</rule>

</tablerule>

</function>

然后， reload 下 dble

2.2 先建表， order 和 order_detail 表，有主外键关系

mysql> explain create table order1 (id int not null auto_increment primary key,sn varchar(64),create_time datetime) engine=innodb default charset=utf8;

+-----------+-----------------------------------------------------------------------------------------------------+

| data_node | sql |

| dn1 | create table order1(id int unsigned not null auto_increment primary key,sn varchar(64),create_time datetime) |

| dn2 | create table order1(id int unsigned not null auto_increment primary key,sn varchar(64),create_time datetime) |

| dn3 | create table order1(id int unsigned not null auto_increment primary key,sn varchar(64),create_time datetime) |

3 rows in set (0.02 sec)

mysql> create table order1(id int not null auto_increment primary key,sn varchar(64),create_time datetime) engine=innodb default charset=utf8;

query ok, 0 rows affected (0.35 sec)

mysql> create table order_detail(id int auto_increment primary key, order_id int,ord_status char(1),address varchar(128),create_time datetime,constraint fk_ordid foreign key (order_id) references order1 (id)) engine=innodb default charset=utf8;

query ok, 0 rows affected (0.44 sec)

3.3 录入数据：

mysql> explain insert into order1(id,sn,create_time) values(1,'bj0001',now());

+-----------+----------------------------------------------------------------+

| data_node | sql |

| dn2 | insert into order1(id,sn,create_time) values(1,'bj0001',now()) |

1 row in set (0.03 sec)

录入数据，一组组录入，涉及到外键关系：

第一组北京的订单

mysql> insert into order1(id,sn,create_time) values(1,'bj0001',now());

query ok, 1 row affected (0.05 sec)

mysql> insert into order_detail(id,order_id,ord_status,address,create_time) values (1,1,'1','test data of order1(id=1,bj001) ',now());

第二组上海的订单：

mysql> explain insert into order1(id,sn,create_time) values(3,'shh001',now());

| dn1 | insert into order1(id,sn,create_time) values(3,'shh001',now()) |

1 row in set (0.02 sec)

mysql> insert into order1(id,sn,create_time) values(3,'shh001',now());

query ok, 1 row affected (0.04 sec)

mysql> insert into order_detail(id,order_id,ord_status,address,create_time) values (3,3,'1','test data of order1(id=3,shh001)',now());

query ok, 1 row affected (0.06 sec)

第三组广州的订单：

mysql> explain insert into order1(id,sn,create_time) values(4,'gzh004',now());

| dn2 | insert into order1(id,sn,create_time) values(4,'gzh004',now()) |

1 row in set (0.00 sec)

mysql> insert into order1(id,sn,create_time) values(4,'gzh004',now());

mysql> insert into order_detail(id,order_id,ord_status,address,create_time) values (4,4,'1','test data of order1(id=4,gzh004) ',now());

第四组武汉的订单，这里故意将order_id设置成4，看看效果，是否随id为4的广州的那组分片：

mysql> explain insert into order1(id,sn,create_time) values(5,'wuhan005',now());

+-----------+------------------------------------------------------------------+

| data_node | sql |

| dn3 | insert into order1(id,sn,create_time) values(5,'wuhan005',now()) |

1 row in set (0.01 sec)

mysql> explain insert into order1(id,sn,create_time) values(6,'wuhan006',now());

query ok, 1 row affected (0.03 sec)

mysql> insert into order_detail(id,order_id,ord_status,address,create_time) values (6,4,'1','test data of order1(id=6,wuhan006) ',now());

通过dble，查看下数据写入的情况：

(testdb) > select * from order1;

+----+--------+---------------------+

| id | sn | create_time |

| 1 | bj0001 | 2019-08-31 23:05:36 |

| 4 | gzh004 | 2019-08-31 23:06:57 |

| 3 | shh001 | 2019-08-31 23:06:43 |

3 rows in set (0.01 sec)

(testdb) > select * from order_detail ;

+----+----------+------------+--------------------------------------+---------------------+

| 1 | 1 | 1 | test data of order1(id=1,bj001) | 2019-08-31 23:06:17 |

| 4 | 4 | 1 | test data of order1(id=4,gzh004) | 2019-08-31 23:07:01 |

| 6 | 4 | 1 | test data of order1(id=6,wuhan006) | 2019-08-31 23:07:23 |

| 3 | 3 | 1 | test data of order1(id=3,shh001) | 2019-08-31 23:06:47 |

4 rows in set (0.01 sec)

直连后端的db1，看下数据情况 (db2 和 db3 上面的数据查看，使用同样的方法）；

((none)) > select * from db1.order1;

((none)) > select * from db1.order_detail;

+----+----------+------------+----------------------------------+---------------------+

| 3 | 3 | 1 | test data of order1(id=3,shh001) | 2019-08-31 23:06:47 |

2.6 走dble，模拟下业务的查询：

(testdb) > explain select t1.*,t2.* from order1 t1,order_detail t2 where t2.ord_status='1' and t2.id=1 and t1.id=t2.order_id;

+-----------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| data_node | type | sql/ref |

| dn1_0 | base sql | select `t2`.`id`,`t2`.`order_id`,`t2`.`ord_status`,`t2`.`address`,`t2`.`create_time`,`t1`.`id`,`t1`.`sn`,`t1`.`create_time` from `order1` `t1` join `order_detail` `t2` on `t1`.`id` = `t2`.`order_id` where (`t2`.`ord_status` = '1') and (`t2`.`id` = 1) |

| dn2_0 | base sql | select `t2`.`id`,`t2`.`order_id`,`t2`.`ord_status`,`t2`.`address`,`t2`.`create_time`,`t1`.`id`,`t1`.`sn`,`t1`.`create_time` from `order1` `t1` join `order_detail` `t2` on `t1`.`id` = `t2`.`order_id` where (`t2`.`ord_status` = '1') and (`t2`.`id` = 1) |

| dn3_0 | base sql | select `t2`.`id`,`t2`.`order_id`,`t2`.`ord_status`,`t2`.`address`,`t2`.`create_time`,`t1`.`id`,`t1`.`sn`,`t1`.`create_time` from `order1` `t1` join `order_detail` `t2` on `t1`.`id` = `t2`.`order_id` where (`t2`.`ord_status` = '1') and (`t2`.`id` = 1) |

| merge_1 | merge | dn1_0; dn2_0; dn3_0 |

| shuffle_field_1 | shuffle_field | merge_1 |

5 rows in set (0.00 sec)

(testdb) > select

t1.*,

t2.*

from

order1 t1,

order_detail t2

where t2.ord_status = '1'

and t2.id = 1

and t1.id = t2.order_id ;

+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+

| 1 | bj0001 | 2019-08-31 23:05:36 | 1 | 1 | 1 | test data of order1(id=1,bj001) | 2019-08-31 23:06:17 |

2.7 总结：当子表与父表的关联字段正好是父表的分片字段时，子表直接根据父表规则进行分片，在数据录入的时候子表直接放在父表的分片上面，在进行关联查询join的时候，走的是父表的路由。

【重要】其它的总结：

当子表与父表的关联字段不是父表的分片字段时，必须通过查找对应的父表记录来确认子表所在分片，如果找不到则会抛出错误，在join查询的时候，路由走的是所有分片节点！！！！

DBLE分库分表实战

继续阅读

Mycat（三）——几种分片规则分库一致性hash的原理及使用

【分库分表】ShardingSphere-JDBC：水平分表操作

Sharding-JDBC实战(水平分表、水平分库、垂直分表、公共表、读写分离)Sharding-JDBC实战

Sharding-Proxy实战(水平分表、水平分库、垂直分表、读写分离)Sharding-Proxy实战

Mycat简单入门--安装、部署、配置、应用

从MySQL到HBase：分库分表方案转型的演进Why Not NoSQL/NewSQL?Why Not 分区?Why 分库分表?

docker部署mycat实现mysql读写分离拉取镜像mysql主从配置mysql master创建4个文件夹（不要操纵从）创建文件夹

这些资深架构师平时需要解决的问题，看看你离资深架构师还有多少距离？

分库分表分库分表如何权衡当前公司的存储需要优化Mysql的主从MyCat数据库分库分表centos7安装mysql5.7操作步骤

sharding-jdbc执行原理

springboot整合sharding-jdbc分库分表介绍springboot整合sharding-jdbc分库分表分析库表数据，不难发现所配置的存储/查询规律结论：limit 分页操作

sharding-jdbc核心概念记录

【数据库】分库分表的难点

00.数据库分库分表-概述摘要

数据库分库分表思想

MySQL 性能调优——数据库的分库分表 1. 数据库分片2、数据库分片前的准备：