Mycat -04 分片续集
范围分片auto_sharding_long
1.schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-intfile" /> <table name="auto_sharding" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="auto_sharding_long" /> </schema> <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="10.1.3.67:3606" user="mycat" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS1" url="10.1.3.68:3606" user="mycat" password="123456" /> <readHost host="hostS2" url="10.1.3.69:3606" user="mycat" password="123456" /> </writeHost> <!-- <writeHost host="hostS1" url="10.1.3.69:3606" user="mycat" password="123456" /> --> </dataHost>
2.rule.xml
<tableRule name="auto-sharding-long"> <rule> <columns>idcolumns> <algorithm>rang-long</algorithm> </rule> </tableRule> <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong"> <property name="mapFile">fun/autopartition-long.txt</property> <property name="defaultNode">0</property> </function>
3.autopartition-long.txt
# range start-end ,data node index # K=1000,M=10000. 0-500M=0 500M-1000M=1 1000M-1500M=2
4.登录8066 ，建表测试
create table auto_sharding (id int primary key , name varchar(10)); mysql> insert into auto_sharding(id,name) values (1,database()); mysql> insert into auto_sharding(id,name) values (2,database()); mysql> insert into auto_sharding(id,name) values (8000000,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into auto_sharding(id,name) values (10000000,database()); Query OK, 1 row affected (0.01 sec) mysql> insert into auto_sharding(id,name) values (12000000,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into auto_sharding(id,name) values (15000000,database()); Query OK, 1 row affected (0.00 sec) mysql> select * from auto_sharding; +----------+------+ | id | name | +----------+------+ | 8000000 | db2 | | 10000000 | db2 | | -100 | db1 | | 1 | db1 | | 2 | db1 | | 1200000 | db1 | | 1400000 | db1 | | 12000000 | db3 | | 15000000 | db3 | +----------+------+ mysql> explain select * from auto_sharding; +-----------+---------------------------------------+ | DATA_NODE | SQL | +-----------+---------------------------------------+ | dn1 | SELECT * FROM auto_sharding LIMIT 100 | | dn2 | SELECT * FROM auto_sharding LIMIT 100 | | dn3 | SELECT * FROM auto_sharding LIMIT 100 | +-----------+---------------------------------------+
按天数分片 sharding-by-day
schema.xml
<mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" /> <!-- random sharding using mod sharind rule --> <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" /> <table name="employee" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-intfile" /> <table name="auto_sharding" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> <table name="sharding_day" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-day" /> </schema> <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="10.1.3.67:3606" user="mycat" password="123456"> <!-- can have multi read hosts --> <readHost host="hostS1" url="10.1.3.68:3606" user="mycat" password="123456" /> <readHost host="hostS2" url="10.1.3.69:3606" user="mycat" password="123456" /> </writeHost> <!-- <writeHost host="hostS1" url="10.1.3.69:3606" user="mycat" password="123456" /> --> </dataHost> </mycat:schema>
rule.xml
<tableRule name="sharding-by-day"> <rule> <columns>create_time</columns> <algorithm>part-by-day</algorithm> </rule> </tableRule> <function name="part-by-day" class="io.mycat.route.function.PartitionByDate"> <property name="dateFormat">yyyy-MM-dd</property> <property name="sBeginDate">2018-03-18</property> <property name="sPartionDay">10</property> # 10天一个分区 </function>
开始时间为2018-03-18，如果未设置结束时间，则时间范围超出三个分片后就报错。但是插入开始时间之前的不会报错。
如果设置了结束的时间sEndDate，则代表数据达到了这个日期的分片后后循环从开始分片插入。
注意事项：
schema里的table的dataNode节点个数必须：大于rule的开始时间按照分片天数计算到现在的个数
（如开始时间：2017-10-01.分片天数为：每10天一个分片，当前时间为：2017-10-31 那么dataNode的节点必须大于等于4个）
登录8066 测试验证
CREATE TABLE `sharding_day` ( `id` int(11) NOT NULL, `create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据（第一条为开始时间之前的） mysql> insert into sharding_day (id,create_time,name ) values (1,'2018-03-11',database()); Query OK, 1 row affected (0.02 sec) mysql> insert into sharding_day (id,create_time,name ) values (2,'2018-03-18',database()); Query OK, 1 row affected (0.01 sec) mysql> insert into sharding_day (id,create_time,name ) values (3,'2018-03-28',database()); Query OK, 1 row affected (0.01 sec) mysql> insert into sharding_day (id,create_time,name ) values (4,'2018-04-08',database()); Query OK, 1 row affected (0.02 sec) mysql> insert into sharding_day (id,create_time,name ) values (5,'2018-04-10',database()); Query OK, 1 row affected (0.00 sec) mysql> insert into sharding_day (id,create_time,name ) values (5,'2018-04-18',database()); ERROR 1064 (HY000): Can't find a valid data node for specified node index :SHARDING_DAY -> CREATE_TIME -> 2018-04-18 -> Index : 3 未设置sEnddate ,超过了分片的个数就会报错
mysql> select * from sharding_day; +----+---------------------+------+ | id | create_time | name | +----+---------------------+------+ | 1 | 2018-03-11 00:00:00 | db1 | | 4 | 2018-04-08 00:00:00 | db3 | | 5 | 2018-04-10 00:00:00 | db3 | | 3 | 2018-03-28 00:00:00 | db2 | | 2 | 2018-03-18 00:00:00 | db1 | +----+---------------------+------+
按照自然月分片sharding-by-month
schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <table name="sharding_month" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-month" /> </schema>
rule.xml
<tableRule name="sharding-by-month"> <rule> <columns>create_time</columns> <algorithm>partbymonth</algorithm> </rule> </tableRule> <function name="partbymonth" class="io.mycat.route.function.PartitionByMonth"> <property name="dateFormat">yyyy-MM-dd</property> <property name="sBeginDate">2018-03-01</property> <property name="sEndDate">2018-05-31</property> </function>
schema里的table的dataNode节点个数必须：大于rule的开始时间按照分片数计算到现在的个数
登录8066 测试验证
mysql> create table sharding_month(id int primary key ,create_time timestamp null on update current_timestamp , name varchar(20)); Query OK, 0 rows affected (0.12 sec) mysql> desc sharding_month; +-------------+-------------+------+-----+---------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-----------------------------+ | id | int(11) | NO | PRI | NULL | | | create_time | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP | | name | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+-----------------------------+ 3 rows in set (0.00 sec) mysql> insert into sharding_month(id,create_time ,name ) values (1,'2018-03-01',database()); Query OK, 1 row affected (0.02 sec) mysql> insert into sharding_month(id,create_time ,name ) values (2,'2018-04-01',database()); Query OK, 1 row affected (0.00 sec) mysql> insert into sharding_month(id,create_time ,name ) values (3,'2018-05-01',database()); Query OK, 1 row affected (0.00 sec) mysql> insert into sharding_month(id,create_time ,name ) values (4,'2018-05-31',database()); Query OK, 1 row affected (0.02 sec) mysql> insert into sharding_month(id,create_time ,name ) values (5,'2018-06-1',database()); Query OK, 1 row affected (0.01 sec) mysql> insert into sharding_month(id,create_time ,name ) values (5,'2018-07-1',database()); Query OK, 1 row affected (0.01 sec) mysql> select * from sharding_month; +----+---------------------+------+ | id | create_time | name | +----+---------------------+------+ | 1 | 2018-03-01 00:00:00 | db1 | | 5 | 2018-06-01 00:00:00 | db1 | | 2 | 2018-04-01 00:00:00 | db2 | | 5 | 2018-07-01 00:00:00 | db2 | | 3 | 2018-05-01 00:00:00 | db3 | | 4 | 2018-05-31 00:00:00 | db3 | +----+---------------------+------+
如果设置了sEndDate，则超过的时间会循环插入各个datanode，如果不设置sEndDate ，则会报错如下。
mysql> insert into sharding_month(id,create_time ,name ) values (6,'2018-08-1',database());
ERROR 1064 (HY000): Can't find a valid data node for specified node index :SHARDING_MONTH -> CREATE_TIME -> 2018-08-1 -> Index : 5
一致性hash 分片
有效解决了分布式数据扩容问题，后续有数据迁移实践。
schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="murmur_hash" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="sharding-by-murmur" /> </schema>
rule.xml
<tableRule name="sharding-by-murmur"> <rule> <columns>id</columns> <algorithm>murmur</algorithm> </rule> </tableRule> <function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash"> <property name="seed">0</property><!-- 默认是0 --> <property name="count">3</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 --> <property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是 虚拟节点数是物理节点数的160倍 --> </function>
登录8066 测试验证
mysql> create table murmur_hash (id int primary key ,name varchar(20)); Query OK, 0 rows affected (0.19 sec) mysql> insert into murmur_hash(id,name) values (1,database()); Query OK, 1 row affected (0.04 sec) mysql> insert into murmur_hash(id,name) values (2,database()); Query OK, 1 row affected (0.02 sec) mysql> insert into murmur_hash(id,name) values (3,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into murmur_hash(id,name) values (4,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into murmur_hash(id,name) values (5,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into murmur_hash(id,name) values (6,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into murmur_hash(id,name) values (7,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into murmur_hash(id,name) values (8,database()); Query OK, 1 row affected (0.03 sec) mysql> insert into murmur_hash(id,name) values (9,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into murmur_hash(id,name) values (10,database()); Query OK, 1 row affected (0.02 sec) mysql> insert into murmur_hash(id,name) values (101221,database()); Query OK, 1 row affected (0.03 sec) mysql> select * from murmur_hash; +--------+------+ | id | name | +--------+------+ | 1 | db2 | | 2 | db2 | | 3 | db2 | | 5 | db1 | | 6 | db1 | | 8 | db1 | | 9 | db2 | | 10 | db2 | | 101221 | db2 | | 4 | db3 | | 7 | db3 | +--------+------+
取模分片
schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <table name="part_mod" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="mod-long" /> </schema>
rule.xml
<tableRule name="mod-long"> <rule> <columns>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">3</property> </function>
登录8066 测试验证
mysql> create table part_mod (id int primary key ,name varchar(20)); Query OK, 0 rows affected (0.08 sec) mysql> desc part_mod; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into part_mod (id,name) values (1,database()); Query OK, 1 row affected (0.04 sec) mysql> insert into part_mod (id,name) values (2,database()); Query OK, 1 row affected (0.05 sec) mysql> insert into part_mod (id,name) values (3,database()); Query OK, 1 row affected (0.00 sec) mysql> insert into part_mod (id,name) values (4,database()); Query OK, 1 row affected (0.03 sec) mysql> insert into part_mod (id,name) values (5,database()); Query OK, 1 row affected (0.01 sec) mysql> insert into part_mod (id,name) values (105,database()); Query OK, 1 row affected (0.00 sec) mysql> select * from part_mod; +-----+------+ | id | name | +-----+------+ | 1 | db2 | | 4 | db2 | | 2 | db3 | | 5 | db3 | | 3 | db1 | | 105 | db1 | +-----+------+ 6 rows in set (0.00 sec)
浙公网安备 33010602011771号