Mycat实战之离散分片

1 枚举分片（customer表）

1.1 修改配置信息加载配置文件

datanode
hash-int

vi partition-hash-int.txt
db1=0
db2=1
[root@mysqldb conf]#

1.2 insert演示路由信息以及mycat日志

mysql> insert into customer(id,customer_id,datanode) values(2,2,'db2');
Query OK, 1 row affected (0.00 sec)

mysql> explain insert into customer(id,customer_id,datanode) values(1,1,'db1');
+-----------+-----------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------+
| dn1 | insert into customer(id,customer_id,datanode) values(1,1,'db1') |
+-----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

查看mycat日志

1.3 查询路由信息以及mycat日志

mysql> explain select * from customer where datanode='db1';
+-----------+---------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------+
| dn1 | SELECT * FROM customer WHERE datanode = 'db1' LIMIT 100 |
+-----------+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from customer where datanode='db1';
+----+-------------+----------+
| id | customer_id | datanode |
+----+-------------+----------+
| 1 | 1 | db1 |
+----+-------------+----------+
1 row in set (0.01 sec)

mycat日志路由信息

2 程序指定分区的分片

此规则是在运行阶段有应用自主决定路由到那个分片。
此方法为直接根据字符子串（必须是数字）计算分区号（由应用传递参数，显式指定分区号）。

2.1 修改配置文件

vi rule.xml

0
1
8
0

id
sharding-by-substring

vi schema.xml
#增加tablerule

加载配置文件

reload @@config

2.2 创建表插入数据

create table mycatbysubstring (id varchar(64) not null primary key,create_time datetime,datanode varchar(10))

insert into mycatbysubstring (id,create_time,datanode) values('0-10000000','2017-06-25',database());
insert into mycatbysubstring (id,create_time,datanode) values('1-10000000','2017-06-25',database());

针对插入语句，分析mycat.log日志

2.3 查询语句分片字段是等值运算,分析mycat.log

mysql> select * from mycatbysubstring where id='1-10000000';
+------------+---------------------+----------+
| id | create_time | datanode |
+------------+---------------------+----------+
| 1-10000000 | 2017-06-25 00:00:00 | db2 |
+------------+---------------------+----------+
1 row in set (0.06 sec)

mysql> explain select * from mycatbysubstring where id='1-10000000';
+-----------+------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------+
| dn2 | select * from mycatbysubstring where id='1-10000000' |
+-----------+------------------------------------------------------+
1 row in set (0.00 sec)

分析mycat.log日志

2.4 查询语句:like查询，查询也是所有dbn上,分析mycat.log

mysql> explain select * from mycatbysubstring where id like '1%';
+-----------+-------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------+
| dn1 | SELECT * FROM mycatbysubstring WHERE id LIKE '1%' LIMIT 100 |
| dn2 | SELECT * FROM mycatbysubstring WHERE id LIKE '1%' LIMIT 100 |
| dn3 | SELECT * FROM mycatbysubstring WHERE id LIKE '1%' LIMIT 100 |
+-----------+-------------------------------------------------------------+
3 rows in set (0.02 sec)