Amoeba：水平切分

1、在master2上创建users表

1. [root@localhost:mydb][09:32:49am] MySQL-> CREATE TABLE `users` (
   
2.     -> `user_id` int(11) NOT NULL,
   
3.     -> `group_id` int(11) NOT NULL,
   
4.     -> `name` varchar(20) DEFAULT NULL,
   
5.     -> `address` varchar(50) DEFAULT NULL,
   
6.     -> `email` varchar(50) DEFAULT NULL,
   
7.     -> PRIMARY KEY (`user_id`)
   
8.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
9. Query OK, 0 rows affected (0.02 sec)

2、配置rule.xml

1.          <tableRule name="users" schema="mydb" defaultPools="master1,master2">
   
2. 
   
3.                 <rule name="rule1">
   
4.                         <parameters>user_id</parameters>
   
5.                         <expression><![CDATA[ user_id %2 == 0 ]]></expression>
   
6.                         <defaultPools>master1</defaultPools>
   
7.                         <readPools>slave1</readPools>
   
8.                         <writePools>master1</writePools>
   
9.                 </rule>
   
10. 
    
11.                 <rule name="rule1">
    
12.                         <parameters>user_id</parameters>
    
13.                         <expression><![CDATA[ user_id %2 == 1 ]]></expression>
    
14.                         <defaultPools>master2</defaultPools>
    
15.                         <readPools>slave2</readPools>
    
16.                         <writePools>master2</writePools>
    
17.                 </rule>
    
18.                 
    
19.         </tableRule>
    
20.         
    
21.         <tableRule name="groups" schema="mydb" defaultPools="master2" />

3、插入测试数据

1. [root@10.10.10.39:mydb][07:59:03pm] MySQL-> insert into users values (1,1,'john','zhejiang','a@163.com');
   
2. Query OK, 2 rows affected (0.05 sec)
   
3. ullnull
   
4. 
   
5. [root@10.10.10.39:mydb][07:59:30pm] MySQL-> select * from users;
   
6. +---------+----------+------+----------+-----------+
   
7. | user_id | group_id | name | address | email |
   
8. +---------+----------+------+----------+-----------+
   
9. | 1 | 1 | john | zhejiang | a@163.com |
   
10. | 1 | 1 | john | zhejiang | a@163.com |
    
11. +---------+----------+------+----------+-----------+
    
12. 2 rows in set (0.01 sec)
    
13. 
    
14. [root@10.10.10.39:mydb][08:01:27pm] MySQL-> truncate table users;
    
15. Query OK, 0 rows affected (0.06 sec)

在amoeba中插入1条记录，却查到了2条一样的记录，在master1和master2上分别都插入记录，且记录相同
然后truncate这条记录，发现master1上的记录删除了，而master2的记录然后存在。
继续插入测试数据

1. insert into users(user_id,group_id,name,address,email) values (1,1,'john','zhejiang','a@163.com');
   
2. insert into users(user_id,group_id,name,address,email) values (2,1,'james','bejing','b@163.com');
   
3. insert into users(user_id,group_id,name,address,email) values (3,2,'kitty','shanghai','c@163.com');
   
4. insert into users(user_id,group_id,name,address,email) values (4,4,'dave','hubei','d@163.com');
   
5. insert into users(user_id,group_id,name,address,email) values (5,4,'mica','jiangsu','e@163.com');
   
6. insert into users(user_id,group_id,name,address,email) values (6,3,'chole','zhejiang','f@163.com');
   
7. insert into users(user_id,group_id,name,address,email) values (7,4,'ice','shanghai','g@163.com')

查看master1和master2的数据分布情况
master1：

1. [root@localhost:mydb][12:28:08pm] MySQL-> select * from users;
   
2. +---------+----------+-------+----------+-----------+
   
3. | user_id | group_id | name | address | email |
   
4. +---------+----------+-------+----------+-----------+
   
5. | 2 | 1 | james | bejing | b@163.com |
   
6. | 4 | 4 | dave | hubei | d@163.com |
   
7. | 6 | 3 | chole | zhejiang | f@163.com |
   
8. +---------+----------+-------+----------+-----------+
   
9. 3 rows in set (0.00 sec):

master2：

1. [root@localhost:mydb][12:31:10pm] MySQL-> select * from users;
   
2. +---------+----------+-------+----------+-----------+
   
3. | user_id | group_id | name | address | email |
   
4. +---------+----------+-------+----------+-----------+
   
5. | 1 | 1 | john | zhejiang | a@163.com |
   
6. | 3 | 2 | kitty | shanghai | c@163.com |
   
7. | 5 | 4 | mica | jiangsu | e@163.com |
   
8. | 7 | 4 | ice | shanghai | g@163.com |
   
9. +---------+----------+-------+----------+-----------+
   
10. 4 rows in set (0.00 sec)

4、测试
4.1 排序

1. [root@10.10.10.39:mydb][08:14:19pm] MySQL-> select * from users order by user_id;
   
2. +---------+----------+-------+----------+-----------+
   
3. | user_id | group_id | name | address | email |
   
4. +---------+----------+-------+----------+-----------+
   
5. | 1 | 1 | john | zhejiang | a@163.com |
   
6. | 3 | 2 | kitty | shanghai | c@163.com |
   
7. | 5 | 4 | mica | jiangsu | e@163.com |
   
8. | 7 | 4 | ice | shanghai | g@163.com |
   
9. | 2 | 1 | james | bejing | b@163.com |
   
10. | 4 | 4 | dave | hubei | d@163.com |
    
11. | 6 | 3 | chole | zhejiang | f@163.com |
    
12. +---------+----------+-------+----------+-----------+
    
13. 7 rows in set (0.01 sec)

很乱哦，仔细查看，其实是先读取master2，然后读取master1的数据，而且是分别排序的
4.2 分页

1. [root@10.10.10.39:mydb][08:17:32pm] MySQL-> select * from users limit 0,3;
   
2. +---------+----------+-------+----------+-----------+
   
3. | user_id | group_id | name | address | email |
   
4. +---------+----------+-------+----------+-----------+
   
5. | 1 | 1 | john | zhejiang | a@163.com |
   
6. | 3 | 2 | kitty | shanghai | c@163.com |
   
7. | 5 | 4 | mica | jiangsu | e@163.com |
   
8. | 2 | 1 | james | bejing | b@163.com |
   
9. | 4 | 4 | dave | hubei | d@163.com |
   
10. | 6 | 3 | chole | zhejiang | f@163.com |
    
11. +---------+----------+-------+----------+-----------+
    
12. 6 rows in set (0.01 sec)

分页与排序的情况差不多，但是不同的是分页需要在2个分库中同时读取相同的分页数
4.3 join

1. [root@10.10.10.39:mydb][08:20:10pm] MySQL-> select user_id,a.group_id,name,address,email,group_name,privilege_id
   
2.     -> from mydb.users a,mydb.groups b
   
3.     -> where a.group_id=b.group_id;
   
4. ERROR 1146 (42S02): Table 'mydb.groups' doesn

当表groups在master2上的时候，报错表不存在。
在master1上创建groups表，并插入与master1一样的数据

1. [root@localhost:mydb][12:32:42pm] MySQL-> CREATE TABLE `groups` (
   
2.     -> `group_id` int(11) NOT NULL,
   
3.     -> `group_name` varchar(20) DEFAULT NULL,
   
4.     -> `privilege_id` int(11) NOT NULL,
   
5.     -> PRIMARY KEY (`group_id`)
   
6.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
7. Query OK, 0 rows affected (0.04 sec)
   
8. 
   
9. [root@localhost:mydb][12:43:52pm] MySQL-> insert into groups values (1,'admin',1);
   
10. Query OK, 1 row affected (0.01 sec)
    
11. 
    
12. [root@localhost:mydb][12:44:08pm] MySQL-> insert into groups values (2,'leder',2);
    
13. Query OK, 1 row affected (0.01 sec)
    
14. 
    
15. [root@localhost:mydb][12:44:08pm] MySQL-> insert into groups values (3,'engineer',4);
    
16. Query OK, 1 row affected (0.00 sec)
    
17. 
    
18. [root@localhost:mydb][12:44:08pm] MySQL-> insert into groups values (4,'sale',3)
    
19.     ->
    
20.     -> ;
    
21. Query OK, 1 row affected (0.02 sec)

删除master2上的groups表，在次进行join查询

1. [root@10.10.10.39:mydb][08:27:37pm] MySQL-> select user_id,a.group_id,name,address,email,group_name,privilege_id
   
2.     -> from mydb.users a,mydb.groups b
   
3.     -> where a.group_id=b.group_id;
   
4. ERROR 1146 (42S02): Table 'mydb.groups' doesn

仍然报错表不存在，最后在master1和master2上同时创建groups表，并数据相同

1. [root@10.10.10.39:mydb][08:21:36pm] MySQL-> select user_id,a.group_id,name,address,email,group_name,privilege_id
   
2.     -> from mydb.users a,mydb.groups b
   
3.     -> where a.group_id=b.group_id;
   
4. +---------+----------+-------+----------+-----------+------------+--------------+
   
5. | user_id | group_id | name | address | email | group_name | privilege_id |
   
6. +---------+----------+-------+----------+-----------+------------+--------------+
   
7. | 1 | 1 | john | zhejiang | a@163.com | admin | 1 |
   
8. | 3 | 2 | kitty | shanghai | c@163.com | leder | 2 |
   
9. | 5 | 4 | mica | jiangsu | e@163.com | sale | 3 |
   
10. | 7 | 4 | ice | shanghai | g@163.com | sale | 3 |
    
11. | 2 | 1 | james | bejing | b@163.com | admin | 1 |
    
12. | 6 | 3 | chole | zhejiang | f@163.com | engineer | 4 |
    
13. | 4 | 4 | dave | hubei | d@163.com | sale | 3 |
    
14. +---------+----------+-------+----------+-----------+------------+--------------+
    
15. 7 rows in set (0.01 sec)

也就是说没有分片的join表，必须在分片表的每个分库中都存在，也就是说amoeba不支持分库的join查询。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12219480/viewspace-1788852/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12219480/viewspace-1788852/