MySQL分库分表分库准备(6th)

前言

随着业务的发展单库中的分表的数量越来越多, 使用在单库上存放过多的表这样是不合理的。因此，我们就需要考虑将数据根据数据库进行拆分。

一般mysql不建议表的数量超过1000个。当然，这不能一概而论，还需要根据你的数据量，和硬件来确定然后根据自己的服务器调整几个mysql ‘%open%’ 参数，从而来确定你的库应该不超过几张表性能能在可接受范围内。

分库思路

在分库前我们需要确定一下我们应该如何去分库：

1、我们是根据用户ID来进行分库，和分表的思路一样。

2、我们需要在用户表中标记一下用户的数据是在哪个库。

3、在系统设置表中应该记录下当前最大分库数量。

4、在系统设置表中应该记录现在所有分库的库名。

5、在系统设置表中应该记录每个分库的数据库连接描述符信息。

分库规则

我们以 ‘数字’ 为分库标识最终分库的名称如：test_1、test_2、test_3 …

在新增加库的时候，我们在新库中创建的表的数量是在系统设置表中的最大分表数。如在系统设置表中 name=’max_sharding_table_num’ 的 value=’10’，这时我们会初始化每个分表的个数为10个。

数据迁移

和分表一样我们应该很清楚哪些表是需要进行分库，我们需要分库的表有 buy_order_n、goods_n、sell_order_n、order_goods_n。

我们应该将之前的数据的库名进行统一。如之前test库的数据要先迁移到 test_1 上

提醒：数据迁移慎重，不是说迁移就迁移的。其实也可以不用迁移的，如果不迁移之后的自动分库的代码就需要做多一点的判断。这为了统一我就做了迁移。

数据迁移SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121

— 创建新库 CREATE  DATABASE test_1; use test; — 拼出需要创建的表 SELECT  CONCAT( ‘CREATE TABLE test_1.’,   TABLE_NAME,    ‘ LIKE ‘,   TABLE_SCHEMA,  ‘.’, TABLE_NAME,  ‘;’ ) FROM information_schema. tables WHERE TABLE_SCHEMA =  ‘test’; — 创建表这边我们不迁移公用的表: user、store、user_guide、system_setting CREATE  TABLE test_1.buy_order_1  LIKE test.buy_order_1; CREATE  TABLE test_1.buy_order_10  LIKE test.buy_order_10; CREATE  TABLE test_1.buy_order_2  LIKE test.buy_order_2; CREATE  TABLE test_1.buy_order_3  LIKE test.buy_order_3; CREATE  TABLE test_1.buy_order_4  LIKE test.buy_order_4; CREATE  TABLE test_1.buy_order_5  LIKE test.buy_order_5; CREATE  TABLE test_1.buy_order_6  LIKE test.buy_order_6; CREATE  TABLE test_1.buy_order_7  LIKE test.buy_order_7; CREATE  TABLE test_1.buy_order_8  LIKE test.buy_order_8; CREATE  TABLE test_1.buy_order_9  LIKE test.buy_order_9; CREATE  TABLE test_1.goods_1  LIKE test.goods_1; CREATE  TABLE test_1.goods_10  LIKE test.goods_10; CREATE  TABLE test_1.goods_2  LIKE test.goods_2; CREATE  TABLE test_1.goods_3  LIKE test.goods_3; CREATE  TABLE test_1.goods_4  LIKE test.goods_4; CREATE  TABLE test_1.goods_5  LIKE test.goods_5; CREATE  TABLE test_1.goods_6  LIKE test.goods_6; CREATE  TABLE test_1.goods_7  LIKE test.goods_7; CREATE  TABLE test_1.goods_8  LIKE test.goods_8; CREATE  TABLE test_1.goods_9  LIKE test.goods_9; CREATE  TABLE test_1.order_goods_1  LIKE test.order_goods_1; CREATE  TABLE test_1.order_goods_10  LIKE test.order_goods_10; CREATE  TABLE test_1.order_goods_2  LIKE test.order_goods_2; CREATE  TABLE test_1.order_goods_3  LIKE test.order_goods_3; CREATE  TABLE test_1.order_goods_4  LIKE test.order_goods_4; CREATE  TABLE test_1.order_goods_5  LIKE test.order_goods_5; CREATE  TABLE test_1.order_goods_6  LIKE test.order_goods_6; CREATE  TABLE test_1.order_goods_7  LIKE test.order_goods_7; CREATE  TABLE test_1.order_goods_8  LIKE test.order_goods_8; CREATE  TABLE test_1.order_goods_9  LIKE test.order_goods_9; CREATE  TABLE test_1.sell_order_1  LIKE test.sell_order_1; CREATE  TABLE test_1.sell_order_10  LIKE test.sell_order_10; CREATE  TABLE test_1.sell_order_2  LIKE test.sell_order_2; CREATE  TABLE test_1.sell_order_3  LIKE test.sell_order_3; CREATE  TABLE test_1.sell_order_4  LIKE test.sell_order_4; CREATE  TABLE test_1.sell_order_5  LIKE test.sell_order_5; CREATE  TABLE test_1.sell_order_6  LIKE test.sell_order_6; CREATE  TABLE test_1.sell_order_7  LIKE test.sell_order_7; CREATE  TABLE test_1.sell_order_8  LIKE test.sell_order_8; CREATE  TABLE test_1.sell_order_9  LIKE test.sell_order_9; — 生成插入表的数据 SELECT  CONCAT( ‘INSERT INTO ‘,   TABLE_SCHEMA,  ‘.’, TABLE_NAME,    ‘ SELECT * FROM test’,  ‘.’, TABLE_NAME,  ‘;’ ) FROM information_schema. tables WHERE TABLE_SCHEMA =  ‘test_1’; — 插入数据 INSERT  INTO test_1.buy_order_1  SELECT *  FROM test.buy_order_1; INSERT  INTO test_1.buy_order_10  SELECT *  FROM test.buy_order_10; INSERT  INTO test_1.buy_order_2  SELECT *  FROM test.buy_order_2; INSERT  INTO test_1.buy_order_3  SELECT *  FROM test.buy_order_3; INSERT  INTO test_1.buy_order_4  SELECT *  FROM test.buy_order_4; INSERT  INTO test_1.buy_order_5  SELECT *  FROM test.buy_order_5; INSERT  INTO test_1.buy_order_6  SELECT *  FROM test.buy_order_6; INSERT  INTO test_1.buy_order_7  SELECT *  FROM test.buy_order_7; INSERT  INTO test_1.buy_order_8  SELECT *  FROM test.buy_order_8; INSERT  INTO test_1.buy_order_9  SELECT *  FROM test.buy_order_9; INSERT  INTO test_1.goods_1  SELECT *  FROM test.goods_1; INSERT  INTO test_1.goods_10  SELECT *  FROM test.goods_10; INSERT  INTO test_1.goods_2  SELECT *  FROM test.goods_2; INSERT  INTO test_1.goods_3  SELECT *  FROM test.goods_3; INSERT  INTO test_1.goods_4  SELECT *  FROM test.goods_4; INSERT  INTO test_1.goods_5  SELECT *  FROM test.goods_5; INSERT  INTO test_1.goods_6  SELECT *  FROM test.goods_6; INSERT  INTO test_1.goods_7  SELECT *  FROM test.goods_7; INSERT  INTO test_1.goods_8  SELECT *  FROM test.goods_8; INSERT  INTO test_1.goods_9  SELECT *  FROM test.goods_9; INSERT  INTO test_1.order_goods_1  SELECT *  FROM test.order_goods_1; INSERT  INTO test_1.order_goods_10  SELECT *  FROM test.order_goods_10; INSERT  INTO test_1.order_goods_2  SELECT *  FROM test.order_goods_2; INSERT  INTO test_1.order_goods_3  SELECT *  FROM test.order_goods_3; INSERT  INTO test_1.order_goods_4  SELECT *  FROM test.order_goods_4; INSERT  INTO test_1.order_goods_5  SELECT *  FROM test.order_goods_5; INSERT  INTO test_1.order_goods_6  SELECT *  FROM test.order_goods_6; INSERT  INTO test_1.order_goods_7  SELECT *  FROM test.order_goods_7; INSERT  INTO test_1.order_goods_8  SELECT *  FROM test.order_goods_8; INSERT  INTO test_1.order_goods_9  SELECT *  FROM test.order_goods_9; INSERT  INTO test_1.sell_order_1  SELECT *  FROM test.sell_order_1; INSERT  INTO test_1.sell_order_10  SELECT *  FROM test.sell_order_10; INSERT  INTO test_1.sell_order_2  SELECT *  FROM test.sell_order_2; INSERT  INTO test_1.sell_order_3  SELECT *  FROM test.sell_order_3; INSERT  INTO test_1.sell_order_4  SELECT *  FROM test.sell_order_4; INSERT  INTO test_1.sell_order_5  SELECT *  FROM test.sell_order_5; INSERT  INTO test_1.sell_order_6  SELECT *  FROM test.sell_order_6; INSERT  INTO test_1.sell_order_7  SELECT *  FROM test.sell_order_7; INSERT  INTO test_1.sell_order_8  SELECT *  FROM test.sell_order_8; INSERT  INTO test_1.sell_order_9  SELECT *  FROM test.sell_order_9; — 向系统表中添加当前最大分库数量 INSERT  INTO test.system_setting VALUES( NULL,  ‘max_sharding_database_num’, 1); — 向系统表中添加分库名前缀 INSERT  INTO test.system_setting VALUES( NULL,  ‘sharding_database_prefix’,  ‘test’); — 向系统表中添加当前有哪些分库 INSERT  INTO test.system_setting VALUES( NULL,  ‘sharding_database’,  ‘test_1’); — 修改系统表字段类 value型为 varchar(120) ALTER  TABLE test.system_setting MODIFY ` value`  varchar(120)  NOT  NULL  COMMENT  ‘系统设置值’; — 向系统表添加响应数据库链接描述符 INSERT  INTO test.system_setting VALUES( NULL,  ‘test_1’,  ‘{“user”:”root”,”password”:”root”,”host”:”127.0.0.1″,”port”:3306,”database”:”test_1″}’); — 初始化用户所在库为test_1 ALTER  TABLE  user ADD db_name  VARCHAR(45)  NOT  NULL  DEFAULT  ‘test_1’ COMMENT  ‘用户数据所在数据库名’;

文章转载来自：trustauth.cn