垂直拆分(分库)
一个书库由很多表构成,每个表对应不同的业务,垂直切分是指按照业务将表进行分类,分布到不同的数据库上面,这样也就将数据或者说压力分摊到不同的库上面,如下图:
如何划分表:
分库的原则:有紧密关联的表应该在一个库里,互相没有关联关系的表可以分到不同的库里。
实现分库:
客户表customer一个库;
订单表orders、订单详细表orders_detail、订单转态表dict_order_type3个表一个库;
schema配置文件:
<!-- 3张表在dn1 -->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!-- 客户表在dn2 -->
<table name="customer" dataNode="dn2"></table>
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="host1" database="orders" />
<dataNode name="dn2" dataHost="host2" database="orders" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="127.0.0.1:3339" user="root"
password="123456">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="127.0.0.1:3340" user="root"
password="123456">
</writeHost>
</dataHost>
新增两个空白库,在数节点dn1、dn2上分别创建数据库orders:
启动mycat:
./mycat start
创建客户表:
create table customer(
id int auto_increment,
name varchar(200),
primary key(id)
);
创建订单表:
create table orders(
id int auto_increment,
order_type int,
customer_id int,
amount decimal(10,2),
primary key(id)
);
创建订单详细表:
create table orders_detail(
id int auto_increment,
detail varchar(2000),
order_id int,
primary key(id)
);
创建订单状态表:
create table dict_order_type(
id int auto_increment,
order_type varchar(200),
primary key(id)
);
查看dn1和dn2中orders库的表情况:
dn1:
dn2:
成功分库。
水平拆分(分表)
相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中 包含一部分数据。
简单来说,可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分 到一个数据库,而另外的某些行又切分到其他的数据库中,如图:
选择要拆分的表:
MySQL单表存储数据条数是有瓶颈的,单表达到1000万条数据就会达到瓶颈,会影响查询效率,需要进行水平拆分进行优化。
假如,上面例子中的orders、orders_detail都达到600万条数据,则需要进行分表优化。
分表字段:
以orders表为例,可以根据不同字段进行分表:
编号|分表字段|效果|
😐:-|
1|id(主键、或创建时间)|查询订单注重实效,历史订单被查询的次数少,如此分片会造成一个结点访问多,一个访问少,不平均|
2|customer_id(客户id)|根据客户id去分,两个结点访问平均,一个客户的所有订单都在同一个节点|
配置schema.xml:
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
</schema>
配置rule.xml:
在rule配置文件里新增分片规则mod_rule,并制定规则使用字段为customer_id,还有选择分片算法mod-long(对字段求模运算),customer_id对两个节点求模,根据结果分片。
配置算法mod-long参数count为2,两个结点。
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
...
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
测试:
首先保证dn1和dn2都要有orders表,然后启动Mycat。
访问mycat,向orders表插入数据,INSERT字段不能省略:
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);
查看两个数据节点:
dn1:
dn2:
一半一半,分表成功。
Mycat的分片“join”
Orders订单表已经分表操作了,和它关联的orders_detail订单详情表如何畸形join查询。
Join的原理图:
ER表:
Mycat借鉴了NewSQL领域新秀Foundation DB的设计思路,Foundation DB创建性的提出了Table Group的概念,其将字表的存储位置依赖于主表,并且物理上紧邻存放,因此彻底解决了JOIN的效率和性能问题,根据这一思路,提出了基于E-R关系的数据分片策略,子表的记录与所关联的父表记录存放在同一个数据分片上。
修改schema.xm配置:
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>
</schema>
测试:
在dn2创建orders_detail表,重启Mycat,并插入数据:
insert into orders_detail(id, detail, order_id) values(1, 'detail', 1);
insert into orders_detail(id, detail, order_id) values(2, 'detail', 2);
insert into orders_detail(id, detail, order_id) values(3, 'detail', 3);
insert into orders_detail(id, detail, order_id) values(4, 'detail', 4);
insert into orders_detail(id, detail, order_id) values(5, 'detail', 5);
insert into orders_detail(id, detail, order_id) values(6, 'detail', 6);
在dn1中join查询:
在dn2中join查询:
在mycat中查询:
全局表
在分片的情况下,当业务表因为规模而进行分片以后,业务表与这些附属的字典表之间的关联,就成了比较棘手的问题,考虑到字典表具有以下几个特性:
- 变动不频繁
- 数据量总体变化不大
- 数据规模不大,很少有超过数十万条记录
所以,Mycat定义了一种特殊的表,称之为全局表,全局表具有以下特性:
- 全局表的插入、更新操作会实时在所有节点上执行,保持各个分片的数据一致性
- 全局表的查询操作,只从一个节点获取
- 全局表可以跟任何一个表进行join操作
将字典表或者符合字典表特性的一些表定义为全局表,则从另外一个方面,很好的解决了数据JOIN的难题。通过全局表+基于E-R关系的分片策略,Mycat可以满足80%以上的企业应用开发
配置sechma.xml:
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>
<!-- 全局表 -->
<table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
</schema>
测试:
在dn2中创建dict_order_type表,重启Mycat插入数据:
insert into dict_order_type(id, order_type) values(101, 'type1');
insert into dict_order_type(id, order_type) values(102, 'type2');
dn1中查询:
dn2中查询: