垂直分库
根据业务划分
配置
步骤:
- 在两台机器都创建orders库
- 修改schema.xml如下图
- 启动mycat
- 依次创建customer,orders等表
- 发现customer创建在154机器中,而其它表在155中
说明:下面的创建的好处就是设置好配置文件,就可以不要来回的切换数据库来创建表,但我们也可以用数据库连接工具(如navicate)来进行操作
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="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="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="106.55.146.154:3306" user="root"
password="xxx">
<!-- can have multi read hosts -->
</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="hostM1" url="39.106.142.155:3306" user="root"
password="xxx">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
</mycat:schema>
水平分表
步骤
1.在schema.xm中增加:<table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<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>
<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="106.55.146.154:3306" user="root"
password="xxx">
<!-- can have multi read hosts -->
</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="hostM1" url="39.106.142.155:3306" user="root"
password="xxx">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
</mycat:schema>
2.在rule.xml增加mod_rule
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
修改mod-long,将数据改为2
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
3.重启mycat,添加orders记录,会发现按customer_id分片
join补充(ER表)
mycat借鉴了newsql领域的新秀Foundation DB的设计思路,它创新性的提出了Table Group的概念,将其字表的存储位置依赖于主表,并且物理上紧邻存放,因此彻底解决了join的效率和性能问题,提出了E-R关系的数据分配策略,字表的记录与所关联的父表存放在同一个数据分片上。
步骤
1.在schemal.xml中增加<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
2.创建orders_detail表
3.重启mycat,增加orders_detail记录,发现还是会按主表orders的customer_id分片
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>
全局表
特点:
1.变动不频繁
2.数据量不大
步骤:
1.修改schema.xml如下:
<table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
2.补充dict_order_type表
3.重启mycat,插入dict_order_type记录,发现在两个数据节点有相同的记录
分片规则
- 取模(算法使用mod-long)
- 分片枚举(算法使用hash-int, 算法属性type,0:数字,1:字符串 defaultNode, 0:不使用默认,1.使用默认)
- 范围约定(算法使用range-long, 没有属性type,defaultNode,文件使用autopartition-long.txt,文件内容为0-120=0)
- 按日期(算法使用自定义shardingByDate)
分片枚举
步骤
1.修改schema.xml中
<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile"></table>
2.修改rule.xml
<tableRule name="sharding_by_intfile">
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
<property name="defaultNode">0</property>
</function>
3.修改partition-hash-int.txt
110=0
120=1
4.重启mycat,在mycat中创建orders_ware_info,可以发现两个数据节点都有数据
5.插入数据,areacode按110,120枚举进行插入,再从两个数据节点都能看到有数据
全局序列(保持id唯一)
- 本地文件方式(简单,但主机宕机就不能正常运行)
- 时间戳方式(简单,但id太长)
- 数据库方式(麻烦,但企业经常使用)
- 自主方式(如redis的incr方法)
数据库方式
步骤
1.创建全局序列表
DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_SEQUENCE (name VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(name)) ENGINE=InnoDB;
2.创建全局序列函数
DROP FUNCTION IF EXISTS mycat_seq_currval;
DELIMITER $
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval;
END $
DELIMITER ;
DROP FUNCTION IF EXISTS mycat_seq_setval;
DELIMITER $
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END $
DELIMITER ;
DROP FUNCTION IF EXISTS mycat_seq_nextval;
DELIMITER $
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END $
DELIMITER ;
3.插入一条数据
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('ORDERS', 4000000, 100);
4.修改Mycat 配置文件 sequence_db_conf.properties,添加ORDERS=dn1(发现已经有了)
5.修改server.xml,1代表数据库,从0开始
<property name="sequnceHandlerType">1</property>
6.重启mycat,并插入数据,并发现有有效了
insert into orders(id, amount, customer_id,order_type) values(next value for MYCATSEQ_ORDERS, 1000,101,102);