Mycat--实践--09--分片--范围约定

Mycat–实践–09–分片–范围约定

1、介绍

1.1、配置说明

<tableRule name="auto-sharding-long">
	<rule>
		<columns>id</columns>
		<algorithm>rang-long</algorithm>
	</rule>
</tableRule>


<function name="rang-long"
	class="io.mycat.route.function.AutoPartitionByLong">
	<property name="mapFile">autopartition-long.txt</property>
</function>

说明

当数据达到多少时，才进行分库分表

1.2、优点

没有跨区回滚事务的风险

1.3、缺点

没有写的负载均衡效果

2、测试步骤

测试期望

db1 的表里面，写满10条后，才进db2写数据

步骤1、修改 schema.xml配置

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<!-- 逻辑库 TESTDB-->
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
	 <!-- 
	 逻辑表 sys_user，存在3个数据节点中，分别是dn1,dn2,dn3
	 sharding-by-intfile 是 枚举分片
	 -->
		<!-- 枚举 分片测试 -->
		<table name="sys_user" dataNode="dn1,dn2,dn3" rule="sharding-by-intfile" /> 
		
		<!-- 取模 分片测试 -->
		<table name="sys_dept" dataNode="dn1,dn2,dn3" rule="mod-long" /> 
		
		<!-- 范围约定 分片测试 -->
		<table name="sys_test1" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> 
	</schema>
 
  <!-- 
	 数据节点，因为dataHost都是localhost1，所有dataHost主机有3个库，分别db1,db2,db3
	 
	 -->
	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />
 
 <!-- 
 数据库最大连接是1000，最小连接是10,
 balance="0" 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
 writeType="0" :所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost
 switchType="1"：主从切换策略，自动切换
 	 -->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<!-- 心跳检测语句 -->
		<heartbeat>select user()</heartbeat>
		
		<!-- M1主节点 -->
		<writeHost host="M1" url="192.168.187.130:3307" user="root" password="root"> 
			<!-- 从节点 -->
			<readHost host="M1S1" url="192.168.187.130:3308" user="root" password="root" />
			<readHost host="M1S2" url="192.168.187.130:3309" user="root" password="root" />  
		</writeHost>
		  
		
	</dataHost>
	 
</mycat:schema>

步骤2、修改 autopartition-long.txt

cd /usr/local/mycat/conf
vim autopartition-long.txt

步骤3、重启mycat

/usr/local/mycat/bin/mycat  restart

步骤4、创建真实表：sys_test1

CREATE TABLE sys_test1(
  id INT PRIMARY KEY ,
  testname VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

步骤5、插入数据测试

INSERT INTO sys_test1(id,testname) VALUES(1,'测试1');
INSERT INTO sys_test1(id,testname) VALUES(2,'测试2');
INSERT INTO sys_test1(id,testname) VALUES(3,'测试3');
INSERT INTO sys_test1(id,testname) VALUES(4,'测试4');
INSERT INTO sys_test1(id,testname) VALUES(5,'测试5');
INSERT INTO sys_test1(id,testname) VALUES(6,'测试6');
INSERT INTO sys_test1(id,testname) VALUES(7,'测试7');
INSERT INTO sys_test1(id,testname) VALUES(8,'测试8');
INSERT INTO sys_test1(id,testname) VALUES(9,'测试9');
INSERT INTO sys_test1(id,testname) VALUES(10,'测试10');
INSERT INTO sys_test1(id,testname) VALUES(11,'测试11');
INSERT INTO sys_test1(id,testname) VALUES(12,'测试12');
INSERT INTO sys_test1(id,testname) VALUES(13,'测试13');
INSERT INTO sys_test1(id,testname) VALUES(14,'测试14');
INSERT INTO sys_test1(id,testname) VALUES(15,'测试15');
INSERT INTO sys_test1(id,testname) VALUES(16,'测试16');
INSERT INTO sys_test1(id,testname) VALUES(17,'测试17');
INSERT INTO sys_test1(id,testname) VALUES(18,'测试18');
INSERT INTO sys_test1(id,testname) VALUES(19,'测试19');
INSERT INTO sys_test1(id,testname) VALUES(20,'测试20');
INSERT INTO sys_test1(id,testname) VALUES(21,'测试21');
INSERT INTO sys_test1(id,testname) VALUES(22,'测试22');
INSERT INTO sys_test1(id,testname) VALUES(23,'测试23');
INSERT INTO sys_test1(id,testname) VALUES(24,'测试24');
INSERT INTO sys_test1(id,testname) VALUES(25,'测试25');
INSERT INTO sys_test1(id,testname) VALUES(26,'测试26');


EXPLAIN INSERT INTO sys_test1(id,testname) VALUES(4,'测试4');
EXPLAIN INSERT INTO sys_test1(id,testname) VALUES(26,'测试26');