前言
随着业务的发展单库中的分表的数量越来越多, 使用在单库上存放过多的表这样是不合理的。因此，我们就需要考虑将数据根据数据库进行拆分。
一般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
'用户数据所在数据库名'
;
|
文章出自:http://www.ttlsa.com/mysql/mysql-distributed-database-and-table-prepare/
本文转自027ryan 51CTO博客，原文链接：http://blog.51cto.com/ucode/1746032，如需转载请自行联系原作者
