Amoeba：水平切分

  • javenzhen

    2015-09-01 20:31:56
  • MySQL
  • 原创
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">

  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>

  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

  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)

  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)

  9. [root@localhost:mydb][12:43:52pm] MySQL-> insert into groups values (1,'admin',1);
  10. Query OK, 1 row affected (0.01 sec)

  12. [root@localhost:mydb][12:44:08pm] MySQL-> insert into groups values (2,'leder',2);
  13. Query OK, 1 row affected (0.01 sec)

  15. [root@localhost:mydb][12:44:08pm] MySQL-> insert into groups values (3,'engineer',4);
  16. Query OK, 1 row affected (0.00 sec)

  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查询。

请使用浏览器的分享功能分享到微信等