Mysql_Mycat分表

根据customer_id取模切分orders表的数据

清除dn1上的orders、dict_order_type、orders_detail表

drop table if exists orders,dict_order_type,orders_detail;

vim schema.xml

<?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> <!-- orders在两台主机上都要有，进行数据切片存储 -->
        </schema>
        <dataNode name="dn1" dataHost="host1" database="order190401" />
        <dataNode name="dn2" dataHost="host2" database="order190401" />
        <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="192.168.78.120:3306" user="root"
                                   password="root">
                        <!-- 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="hostM2" url="192.168.78.121:3306" user="root"
                                   password="root">
                        <!-- can have multi read hosts -->
                </writeHost>
        </dataHost> 
</mycat:schema>

vim rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
	- you may not use this file except in compliance with the License. - You 
	may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
	- - Unless required by applicable law or agreed to in writing, software - 
	distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
	WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
	License for the specific language governing permissions and - limitations 
	under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
	<tableRule name="mod_rule">
		<rule>
			<columns>customer_id</columns> <!-- 要和数据库表的字段名一样 -->
			<algorithm>mod-long</algorithm> <!-- 指定数据切片的算法，这里mod-long" -->
		</rule>
	</tableRule>
	<tableRule name="sharding-by-murmur">
		<rule>
			<columns>id</columns>
			<algorithm>murmur</algorithm>
		</rule>
	</tableRule>
	<tableRule name="crc32slot">
		<rule>
			<columns>id</columns>
			<algorithm>crc32slot</algorithm>
		</rule>
	</tableRule>
	<tableRule name="sharding-by-month">
		<rule>
			<columns>create_time</columns>
			<algorithm>partbymonth</algorithm>
		</rule>
	</tableRule>
	<tableRule name="latest-month-calldate">
		<rule>
			<columns>calldate</columns>
			<algorithm>latestMonth</algorithm>
		</rule>
	</tableRule>
	
	<tableRule name="auto-sharding-rang-mod">
		<rule>
			<columns>id</columns>
			<algorithm>rang-mod</algorithm>
		</rule>
	</tableRule>
	
	<tableRule name="jch">
		<rule>
			<columns>id</columns>
			<algorithm>jump-consistent-hash</algorithm>
		</rule>
	</tableRule>

	<function name="murmur"
		class="io.mycat.route.function.PartitionByMurmurHash">
		<property name="seed">0</property><!-- 默认是0 -->
		<property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
		<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
		<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
		<!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property> 
			用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
	</function>

	<function name="crc32slot"
			  class="io.mycat.route.function.PartitionByCRC32PreSlot">
		<property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
	</function>
	<function name="hash-int"
		class="io.mycat.route.function.PartitionByFileMap">
		<property name="mapFile">partition-hash-int.txt</property>
	</function>
	<function name="rang-long"
		class="io.mycat.route.function.AutoPartitionByLong">
		<property name="mapFile">autopartition-long.txt</property>
	</function>
	<function name="mod-long" class="io.mycat.route.function.PartitionByMod"><!-- 将数值按主机数量取模，然后将数据根据模值分布到对应的主机上 -->
		<!-- how many data nodes -->
		<property name="count">2</property> <!-- 这里修改成咱们实际的主机数量 -->
	</function>

	<function name="func1" class="io.mycat.route.function.PartitionByLong">
		<property name="partitionCount">8</property>
		<property name="partitionLength">128</property>
	</function>
	<function name="latestMonth"
		class="io.mycat.route.function.LatestMonthPartion">
		<property name="splitOneDay">24</property>
	</function>
	<function name="partbymonth"
		class="io.mycat.route.function.PartitionByMonth">
		<property name="dateFormat">yyyy-MM-dd</property>
		<property name="sBeginDate">2015-01-01</property>
	</function>
	
	<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
        	<property name="mapFile">partition-range-mod.txt</property>
	</function>
	
	<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
		<property name="totalBuckets">3</property>
	</function>
</mycat:rule>

启动mycat

./mycat console

创建orders表

# 连接mycat
mysql -umycat -p'123456' -P 8066 -h 192.168.78.120

-- 创建orders表
create table orders(
id int auto_increment,
order_type int,
customer_id int,
amount decimal(10,2),
primary key(id)
);

此时，dn1 、dn2主机上都已经创建好了orders表。

测试表的水平切分

此处请注意： 但凡使用mycat进行数据的插入，需要按照标准的sql写法（将表字段写明），否则mycat无法识别内容所对应的字段，既：必须是 insert into orders(id,order_type ,customer_id ,amount ) values(1,101,100,100100); 不能写成 insert into orders values(1,101,100,100100); 否则会报错，无法插入数据。

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); 

mysql> select * from orders;
+----+------------+-------------+-----------+
| id | order_type | customer_id | amount    |
+----+------------+-------------+-----------+
|  3 |        101 |         101 | 120000.00 |
|  4 |        101 |         101 | 103000.00 |
|  5 |        102 |         101 | 100400.00 |
|  1 |        101 |         100 | 100100.00 |
|  2 |        101 |         100 | 100300.00 |
|  6 |        102 |         100 | 100020.00 |
+----+------------+-------------+-----------+
6 rows in set (0.00 sec)

mysql> 

分别在dn1、dn2上查看：


可以看到customer_id为101的数据分布在dn2上，customer_id为100的数据分布在了dn1上，与预期的一致。

配置E-R表

由于不能跨库join，为了能够使orders和子表orders_detail进行关联查询，orders_detail也需要按照相应的规则（mycat会根据配置自动按照父表的分布规则）进行数据分布，mycat提供了E-R表配置的方式进行配置。

vim 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'>
                        <childTable name='orders_detail' primaryKey='id' joinKey='order_id' parentKey='id' /> <!-- 配置E-R表，子表与父表的关系 -->
                </table>
        </schema>

重启mycat

./mycat console

连接mycat,并创建orders_detail表

create table orders_detail(
id int auto_increment,
detail varchar(2000),
order_id int,
primary key(id)
);

插入数据

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);

mysql> select * from orders_detail;
+----+--------+----------+
| id | detail | order_id |
+----+--------+----------+
|  1 | detail |        1 |
|  2 | detail |        2 |
|  6 | detail |        6 |
|  3 | detail |        3 |
|  4 | detail |        4 |
|  5 | detail |        5 |
+----+--------+----------+
6 rows in set (0.06 sec)

mysql> 

-- 关联查询
mysql> select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount    | detail |
+----+------------+-------------+-----------+--------+
|  1 |        101 |         100 | 100100.00 | detail |
|  2 |        101 |         100 | 100300.00 | detail |
|  6 |        102 |         100 | 100020.00 | detail |
|  3 |        101 |         101 | 120000.00 | detail |
|  4 |        101 |         101 | 103000.00 | detail |
|  5 |        102 |         101 | 100400.00 | detail |
+----+------------+-------------+-----------+--------+
6 rows in set (0.04 sec)

mysql> 

配置全局表（global / replication）

orders_detail表使用了字典表dict_order_type，所以，每台主机上都需要有dict_order_type所有的数据。

• 全局表一般不能是大数据表或者更新频繁的表，一般是字典表或系统表为宜。
• 全局表数据一般不大，就算有冗余，也不会对数据库性能产生大的影响。

vim 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'>
                        <childTable name='orders_detail' primaryKey='id' joinKey='order_id' parentKey='id' />
                </table>
                <table name='dict_order_type' dataNode='dn1,dn2' type='global' /> <!-- 配置全局表 -->
        </schema>

重启mycat： ./mycat console
连接mycat，并创建表 dict_order_type ，插入数据

create table dict_order_type(
id int auto_increment,
order_type varchar(200),
primary key(id)
);

insert into dict_order_type(id,order_type) values(101,'type1');
insert into dict_order_type(id,order_type) values(102,'type2');

mysql> select * from dict_order_type;  -- 全局表在每台主机上的数据都是一致的
+-----+------------+
| id  | order_type |
+-----+------------+
| 101 | type1      |
| 102 | type2      |
+-----+------------+
2 rows in set (0.04 sec)

参考：https://www.bilibili.com/video/BV12b411K7Zu?p=349&spm_id_from=pageDriver