MySQL社区

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 7170|回复: 5

[思路] 用户信息表水平拆表方案

[复制链接]
发表于 2013-4-18 16:25:02 | 显示全部楼层 |阅读模式
作者:贺春旸
1.jpg

2.jpg





方法就是利用用户ID进行取模,MOD(new.id,3),3就代表拆分到3张表里。


以下是步骤:

第一步、在主库上(M)建立拆分的三张表,t0,t1,t2
  1.     create table t0 like t;
  2.     create table t1 like t;
  3.     create table t2 like t;
复制代码
第二步、在主库上(M)建立三个触发器(插入、更新、删除)
  1.     DELIMITER $
  2.      
  3.     USE `test`$
  4.      
  5.     DROP TRIGGER /*!50032 IF EXISTS */ `t_insert`$
  6.      
  7.     CREATE
  8.         /*!50017 DEFINER = 'admin'@'%' */
  9.         TRIGGER `t_insert` AFTER INSERT ON `t`  
  10.         FOR EACH ROW BEGIN
  11.         DECLARE v_result INT;
  12.         SET v_result=MOD(new.id,3);
  13.         IF v_result = 0 THEN
  14.           INSERT INTO t0(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  15.         ELSEIF v_result = 1 THEN
  16.           INSERT INTO t1(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  17.         ELSE
  18.           INSERT INTO t2(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  19.         END IF;
  20.         END;
  21.     $
  22.      
  23.     DELIMITER ;
  24.      
  25.      
  26.     /*============================================*/
  27.      
  28.     DELIMITER $
  29.      
  30.     USE `test`$
  31.      
  32.     DROP TRIGGER /*!50032 IF EXISTS */ `t_update`$
  33.      
  34.     CREATE
  35.         /*!50017 DEFINER = 'admin'@'%' */
  36.         TRIGGER `t_update` AFTER UPDATE ON `t`  
  37.         FOR EACH ROW BEGIN
  38.         DECLARE v_result INT;
  39.         SET v_result=MOD(new.id,3);
  40.         IF v_result = 0 THEN
  41.           REPLACE INTO t0(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  42.         ELSEIF v_result = 1 THEN
  43.           REPLACE INTO t1(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  44.         ELSE
  45.           REPLACE INTO t2(id,NAME,age,address) VALUES(new.id,new.name,new.age,new.address);
  46.         END IF;
  47.         END;
  48.     $
  49.      
  50.     DELIMITER ;
  51.      
  52.      
  53.     /*============================================*/
  54.      
  55.     DELIMITER $
  56.      
  57.     USE `test`$
  58.      
  59.     DROP TRIGGER /*!50032 IF EXISTS */ `t_delete`$
  60.      
  61.     CREATE
  62.         /*!50017 DEFINER = 'admin'@'%' */
  63.         TRIGGER `t_delete` AFTER DELETE ON `t`  
  64.         FOR EACH ROW BEGIN
  65.         DECLARE v_result INT;
  66.         SET v_result=MOD(old.id,3);
  67.         IF v_result = 0 THEN
  68.           DELETE FROM t0 WHERE id = OLD.id;
  69.         ELSEIF v_result = 1 THEN
  70.           DELETE FROM t1 WHERE id = OLD.id;
  71.         ELSE
  72.           DELETE FROM t2 WHERE id = OLD.id;
  73.         END IF;
  74.         END;
  75.     $
  76.      
  77.     DELIMITER ;
复制代码
这样t表数据变更时,就会按照取模的结果更新到t0,t1,t2这三张表里


第三步、确保同步复制延时为0时,在从库上(S)stop slave关闭同步复制,并show slave status\G记录到文本里,以便后面时用到。


第四步、在从库上新建一个实例(S2),并导出t,t0,t1,t2四张表(导出时只要数据,不要表结构)
  1. /usr/local/mysql/bin/mysqldump -uroot -p123456 --dump-slave=2 -nt --skip-triggers -q --single-transaction test t t0 t1 t2 > t_all.sql
复制代码
--dump-slave是MySQL5.5的一个特性,具体你可参考我这一篇文章
http://hcymysql.blog.51cto.com/blog/5223301/889971
如果你是5.1的,就要用到第三步我们记录的show slave status\G到文本,找到change master to的那个点,这样就保证了拆分后的增量数据。


第五步,在从库上S2实例上,创建表t,t0,t1,t2,也同样建立那三个触发器,这样做的目的是,当我们导入的时候,就会通过触发器取模,把数据分散到t0,t1,t2这三张表里,然后开启同步复制主库M。


第六步,上面五步做完以后,请检查下数据的增长情况,并观察同步是否正常,都没问题的话,就可以把数据分散到新的3台服务器里。


在S2实例上,把t0表dump出来并导入到M_new1机器上,然后在M_new1机器上,在my.cnf里增加
  1.     replicate-ignore-table=test.t1
  2.     replicate-ignore-table=test.t2
复制代码
忽略t1,t2表的同步复制。


把t1表dump出来并导入到M_new2机器上,然后在M_new2机器上,在my.cnf里增加
  1.     replicate-ignore-table=test.t0  
  2.     replicate-ignore-table=test.t2  
复制代码
忽略t0,t2表的同步复制。


把t2表dump出来并导入到M_new3机器上,然后在M_new3机器上,在my.cnf里增加
  1.     replicate-ignore-table=test.t0   
  2.     replicate-ignore-table=test.t1  
复制代码
第七步,到目前为止,基本上我们已经做完了,剩下的就要开发改他们的代码,告诉他们拆分的规则,按用户ID取模,拆分几个表,拆分到哪台机器上,IP是什么。

等凌晨0:00点,开发让运维重启前端应用,这样就顺利实现了用户信息表的拆分。

过程可能有些复杂,有兴趣的朋友,可以按照我上面的操作,做下测试。





发表于 2013-4-18 16:51:55 | 显示全部楼层
高端又详细!{:soso_e179:}
发表于 2013-4-19 06:10:28 | 显示全部楼层
{:soso_e128:}
发表于 2013-8-19 08:56:09 | 显示全部楼层
牛,

大家都好像用中间件来做这个事,不过我非常推崇楼主的原理性研究,知其然,知其所以然。
nycle2012 该用户已被删除
发表于 2014-2-27 09:43:07 | 显示全部楼层
提示: 作者被禁止或删除 内容自动屏蔽
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周先生; 联系电话:17896003451

GMT+8, 2017-9-21 07:36 , Processed in 0.280606 second(s), 31 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表