垂直分庫
根據業務劃分
配置
步驟:
- 在兩台機器都建立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);