MyCAT测试

一、测试环境

属性	值
Ubuntu版本	Ubuntu 14.04.2 LTS
服务器IP	10.1.1.167
MySQL版本	10.0.21-MariaDB-log
Mycat版本	Mycat-server-1.5-GA-20160215160037-linux.tar.gz
jdk版本	jdk-7u71-linux-x64.tar.gz
Mycat-MySQL版本	Server version: 5.5.8-mycat-1.5-GA-20160215160037 MyCat Server (OpenCloundDB)
MySQL账号密码	gaizai/123
Mycat账号密码	test/test

二、测试

连接到Mycat进行测试

/usr/local/mariadb/bin/mysql -h10.1.1.167 -P8066 -utest -p

1、测试 `travelrecord` 表

<!-- auto sharding by id (long) -->
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

travelrecord表，是根据ID主键的范围进行分片，分布在dn1,dn2,dn3三个节点，对应着conf/autopartition-long.txt文件

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

(product)test@10.1.1.167 [(none)]> use TESTDB;
Database changed

(product)test@10.1.1.167 [TESTDB]> create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.04 sec)

(product)test@10.1.1.167 [TESTDB]> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(1000000,'abc','2016-01-02',100.01,3);
+-----------+-------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                   |
+-----------+-------------------------------------------------------------------------------------------------------+
| dn1       | insert into travelrecord (id,user_id,traveldate,fee,days) values(1000000,'abc','2016-01-02',100.01,3) |
+-----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

(product)test@10.1.1.167 [TESTDB]> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(7000000,'abc','2016-01-02',100.01,3);
+-----------+-------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                   |
+-----------+-------------------------------------------------------------------------------------------------------+
| dn2       | insert into travelrecord (id,user_id,traveldate,fee,days) values(7000000,'abc','2016-01-02',100.01,3) |
+-----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(product)test@10.1.1.167 [TESTDB]> explain select * from travelrecord;
+-----------+--------------------------------------+
| DATA_NODE | SQL                                  |
+-----------+--------------------------------------+
| dn1       | SELECT * FROM travelrecord LIMIT 100 |
| dn2       | SELECT * FROM travelrecord LIMIT 100 |
| dn3       | SELECT * FROM travelrecord LIMIT 100 |
+-----------+--------------------------------------+
3 rows in set (0.03 sec)

insert into travelrecord (id,user_id,traveldate,fee,days) values(1000000,'abc','2016-01-02',100.01,3);
insert into travelrecord (id,user_id,traveldate,fee,days) values(7000000,'abc','2016-01-02',100.01,3);

(product)test@10.1.1.167 [TESTDB]> select * from travelrecord;
+---------+---------+------------+------+------+
| id      | user_id | traveldate | fee  | days |
+---------+---------+------------+------+------+
| 7000000 | abc     | 2016-01-02 |  100 |    3 |
| 1000000 | abc     | 2016-01-02 |  100 |    3 |
+---------+---------+------------+------+------+
2 rows in set (0.01 sec)

2、测试 `company` 表

<!-- global table is auto cloned to all defined data nodes ,so can join
     with any table whose sharding node is in the same data node -->
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />

company表，定义为全局表，在dn1,dn2,dn3 三个节点都有相同的记录

create table company(id int not null primary key,name varchar(100));

(product)test@10.1.1.167 [TESTDB]> explain insert into company(id,name) values(1,'hp');
+-----------+---------------------------------------------+
| DATA_NODE | SQL                                         |
+-----------+---------------------------------------------+
| dn1       | insert into company(id,name) values(1,'hp') |
| dn2       | insert into company(id,name) values(1,'hp') |
| dn3       | insert into company(id,name) values(1,'hp') |
+-----------+---------------------------------------------+
3 rows in set (0.00 sec)

insert into company(id,name) values(1,'hp');
insert into company(id,name) values(2,'ibm');
insert into company(id,name) values(3,'oracle');
(product)test@10.1.1.167 [TESTDB]> select * from company;
+----+--------+
| id | name   |
+----+--------+
|  1 | hp     |
|  2 | ibm    |
|  3 | oracle |
+----+--------+
3 rows in set (0.00 sec)

3、测试 `goods` 表测试测试测试测试

<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />

goods表，定义为全局表，在dn1,dn2两个节点都有相同的记录

create table goods(id int not null primary key,name varchar(200),good_type tinyint,good_img_url  varchar(200),good_created date,good_desc varchar(500), price double);

(product)test@10.1.1.167 [TESTDB]> explain insert into goods(id,name,good_type,good_img_url,good_created,good_desc,price) values(1,'hp',1,'http://a.jgp',now(),'惠普',100.00);
+-----------+--------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                                                  |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------+
| dn1       | insert into goods(id,name,good_type,good_img_url,good_created,good_desc,price) values(1,'hp',1,'http://a.jgp',now(),'惠普',100.00)   |
| dn2       | insert into goods(id,name,good_type,good_img_url,good_created,good_desc,price) values(1,'hp',1,'http://a.jgp',now(),'惠普',100.00)   |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.02 sec)

insert into goods(id,name,good_type,good_img_url,good_created,good_desc,price) values(1,'hp',1,'http://a.jgp',now(),'惠普',100.00);

(product)test@10.1.1.167 [TESTDB]> select * from goods;
+----+------+-----------+--------------+--------------+-----------+-------+
| id | name | good_type | good_img_url | good_created | good_desc | price |
+----+------+-----------+--------------+--------------+-----------+-------+
|  1 | hp   |         1 | http://a.jgp | 2016-02-23   | 我      |   100 |
+----+------+-----------+--------------+--------------+-----------+-------+
1 row in set (0.00 sec)

4、测试 `hotnews` 表

<!-- random sharding using mod sharind rule -->
<table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="mod-long" />

hotnews表，基于主键取摸的方式分配到dn1,dn2,dn3上

create table hotnews(id int  not null primary key ,title varchar(400) ,created_time datetime);

(product)test@10.1.1.167 [TESTDB]> explain insert into hotnews(id,title,created_time) values(1,'first',now());
+-----------+--------------------------------------------------------------------+
| DATA_NODE | SQL                                                                |
+-----------+--------------------------------------------------------------------+
| dn2       | insert into hotnews(id,title,created_time) values(1,'first',now()) |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

(product)test@10.1.1.167 [TESTDB]> explain insert into hotnews(id,title,created_time) values(2,'two',now());
+-----------+------------------------------------------------------------------+
| DATA_NODE | SQL                                                              |
+-----------+------------------------------------------------------------------+
| dn3       | insert into hotnews(id,title,created_time) values(2,'two',now()) |
+-----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

(product)test@10.1.1.167 [TESTDB]> explain insert into hotnews(id,title,created_time) values(3,'three',now());
+-----------+--------------------------------------------------------------------+
| DATA_NODE | SQL                                                                |
+-----------+--------------------------------------------------------------------+
| dn1       | insert into hotnews(id,title,created_time) values(3,'three',now()) |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

insert into hotnews(id,title,created_time) values(1,'first',now());
insert into hotnews(id,title,created_time) values(2,'two',now());
insert into hotnews(id,title,created_time) values(3,'three',now());
insert into hotnews(id,title,created_time) values(5,'five',now());

(product)test@10.1.1.167 [TESTDB]> select * from hotnews;
+----+-------+---------------------+
| id | title | created_time        |
+----+-------+---------------------+
|  3 | three | 2016-02-23 14:34:19 |
|  2 | two   | 2016-02-23 14:34:15 |
|  5 | five  | 2016-02-23 14:34:22 |
|  1 | first | 2016-02-23 14:34:11 |
+----+-------+---------------------+
4 rows in set (0.00 sec)

id为1，1%3=1，则到dn2上；
id为2，2%3=2，则到dn3上；
id为3，3%3=0，则到dn1上；
id为5，5%3=2，则到dn3上，即对应dn3的index；

5、测试 `employee` 表

<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />

employee表，根据sharding-by-intfile （分片字段为sharding_id）规则进行分片，只在dn1,dn2存储数据，对应着conf/partition-hash-int.txt文件

10000=0
10010=1

create table employee (id int not null primary key,name varchar(100),sharding_id int not null);

insert into employee(id,name,sharding_id) values(1,'leader us',10000);
insert into employee(id,name,sharding_id) values(2,'me',10010);
insert into employee(id,name,sharding_id) values(3,'mycat',10000);
insert into employee(id,name,sharding_id) values(4,'mydog',10010);

(product)test@10.1.1.167 [TESTDB]> select * from employee;
+----+-----------+-------------+
| id | name      | sharding_id |
+----+-----------+-------------+
|  1 | leader us |       10000 |
|  3 | mycat     |       10000 |
|  2 | me        |       10010 |
|  4 | mydog     |       10010 |
+----+-----------+-------------+
4 rows in set (0.00 sec)

6、测试 `orders` 表

<table name="customer" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile">
    <childTable name="orders" primaryKey="ID" joinKey="customer_id" parentKey="id">
    <childTable name="order_items" joinKey="order_id" parentKey="id" />
    </childTable>
    <childTable name="customer_addr" primaryKey="ID" joinKey="customer_id" parentKey="id" />
</table>

customer表，根据sharding-by-intfile （分片字段为sharding_id）规则进行分片，只在dn1,dn2存储数据
orders表，ER分片，根据customer表的ID

首先创建customer表

create table customer(id int not null primary key,name varchar(100),company_id int not null,sharding_id int not null);

(product)test@10.1.1.167 [TESTDB]> explain insert into customer (id,name,company_id,sharding_id)values(1,'wang',1,10000);
+-----------+-------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                           |
+-----------+-------------------------------------------------------------------------------+
| dn1       | insert into customer (id,name,company_id,sharding_id)values(1,'wang',1,10000) |
+-----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(product)test@10.1.1.167 [TESTDB]> explain insert into customer (id,name,company_id,sharding_id)values(2,'xue',2,10010);
+-----------+------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                          |
+-----------+------------------------------------------------------------------------------+
| dn2       | insert into customer (id,name,company_id,sharding_id)values(2,'xue',2,10010) |
+-----------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(product)test@10.1.1.167 [TESTDB]> explain insert into customer (id,name,company_id,sharding_id)values(3,'feng',3,10000);
+-----------+-------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                           |
+-----------+-------------------------------------------------------------------------------+
| dn1       | insert into customer (id,name,company_id,sharding_id)values(3,'feng',3,10000) |
+-----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

insert into customer(id,name,company_id,sharding_id)values(1,'wang',1,10000);
insert into customer(id,name,company_id,sharding_id)values(2,'xue',2,10010);
insert into customer(id,name,company_id,sharding_id)values(3,'feng',3,10000);
insert into customer(id,name,company_id,sharding_id)values(4,'wo',4,10000);

(product)test@10.1.1.167 [TESTDB]> select * from customer;
+----+------+------------+-------------+
| id | name | company_id | sharding_id |
+----+------+------------+-------------+
|  2 | xue  |          2 |       10010 |
|  1 | wang |          1 |       10000 |
|  3 | feng |          3 |       10000 |
|  4 | wo   |          4 |       10000 |
+----+------+------------+-------------+
4 rows in set (0.01 sec)

测试orders表

create table orders (id int not null primary key ,customer_id int not null,sataus int ,note varchar(100) );

//stored in db1 because customer table with id=3 stored in db1
insert into orders(id,customer_id) values(1,3);

//stored in db1 because customer table with id=1 stored in db1
insert into orders(id,customer_id) values(2,1);

//stored in db2 because customer table with id=2 stored in db2
insert into orders(id,customer_id) values(3,2);

(product)test@10.1.1.167 [TESTDB]> select * from orders;
+----+-------------+--------+------+
| id | customer_id | sataus | note |
+----+-------------+--------+------+
|  1 |           3 |   NULL | NULL |
|  2 |           1 |   NULL | NULL |
|  3 |           2 |   NULL | NULL |
+----+-------------+--------+------+
3 rows in set (0.00 sec)

(product)test@10.1.1.167 [TESTDB]> select customer.name ,orders.* from customer ,orders where customer.id=orders.customer_id;
+------+----+-------------+--------+------+
| name | id | customer_id | sataus | note |
+------+----+-------------+--------+------+
| wang |  2 |           1 |   NULL | NULL |
| feng |  1 |           3 |   NULL | NULL |
| xue  |  3 |           2 |   NULL | NULL |
+------+----+-------------+--------+------+
3 rows in set (0.05 sec)

这里无法使用explain来查看insert执行分布，如果加上explain，直接被当成insert了；

三、参考文献

_MyCAT安装与部署
_Mycat 安装配置

最后编辑于：2017.12.03 03:20:40

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,064评论 5赞 466
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 83,606评论 2赞 376
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,011评论 0赞 328
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,550评论 1赞 269
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,465评论 5赞 359
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 47,919评论 1赞 275
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,428评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,075评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,208评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,185评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,191评论 1赞 328
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,914评论 3赞 316
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,482评论 3赞 302
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,585评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,825评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,194评论 2赞 344
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,703评论 2赞 339

MyCAT测试

一、测试环境

二、测试

1、测试 travelrecord 表

2、测试 company 表

3、测试 goods 表测试测试测试测试

3、测试 goods 表测试测试测试测试

3、测试 goods 表测试测试测试测试

4、测试 hotnews 表

5、测试 employee 表

6、测试 orders 表

三、参考文献

推荐阅读更多精彩内容

1、测试 `travelrecord` 表

2、测试 `company` 表

3、测试 `goods` 表测试测试测试测试

3、测试 `goods` 表测试测试测试测试

3、测试 `goods` 表测试测试测试测试

4、测试 `hotnews` 表

5、测试 `employee` 表

6、测试 `orders` 表