加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 创业 > C语言 > 正文

MySQL分库分表

发布时间：2020-12-15 00:44:40 所属栏目：C语言 来源：网络整理
导读：本文源于原作者分享 MySQL分库分表基础表介绍(1st) 表基本模型结构 这里我们模拟一个商城的基本的表结。此结构由（用户、门店、导购、门店商品、订单、订单对应的商品）。其中，导购也是一个用户，门店是只属于一个店主的，同时店主本身也是一个导购也是一个
这边只展示主程序：完整的程序在附件中都有

if __name__=='__main__':
  # 设置默认的数据库链接参数
  db_config = {
    'user'    : 'root','password': 'root','host'    : '127.0.0.1','port'    : 3306,'database': 'test'
  }
  # 设置snowflake链接默认参数
  snowflake_config = {
    'host': '192.168.137.11','port': 30001
  }

rebuild = Rebuild()


设置数据库配置


rebuild.set_db_config(db_config)


设置snowflak配置


rebuild.set_snowflake_config(snowflake_config)


链接配置snowflak


rebuild.setup_snowflake()


生成数据库链接和


rebuild.get_conn_cursor()


##########################################################################


修改商品ID


##########################################################################


获得商品的游标


goods_sql = '''

SELECT goods_id FROM goods

'''

goods_iter = rebuild.execute_select_sql([goods_sql])


根据获得的商品ID更新商品表(goods)和订单商品表(order_goods)的商品ID


for goods in goods_iter:

for (goods_id,) in goods:

rebuild.update_table_id('goods','goods_id',goods_id)

rebuild.update_table_id('order_goods',goods_id,rebuild.get_current_guid())

rebuild.commit()


##########################################################################


修改订单ID,这边我们规定出售订单ID和购买订单ID相等


##########################################################################


获得订单的游标


orders_sql = '''

SELECT sell_order_id FROM sell_order_1

'''

sell_order_iter = rebuild.execute_select_sql([orders_sql])


根据出售订单修改 出售订单(sell_order_1)、购买订单(buy_order_1)、订单商品(order_goods)的出售订单ID


for sell_order_1 in sell_order_iter:

for (sell_order_id,) in sell_order_1:

rebuild.update_table_id('sell_order_1','sell_order_id',sell_order_id)

rebuild.update_table_id('buy_order_1','buy_order_id',sell_order_id,rebuild.get_current_guid())

rebuild.update_table_id('order_goods',rebuild.get_current_guid())

rebuild.commit()


##########################################################################


修改订单商品表ID


##########################################################################


获得订单商品的游标


order_goods_sql = '''

SELECT order_goods_id FROM order_goods

'''

order_goods_iter = rebuild.execute_select_sql([order_goods_sql])

for order_goods in order_goods_iter:

for (order_goods_id,) in order_goods:

rebuild.update_table_id('order_goods','order_goods_id',order_goods_id)

rebuild.commit()


关闭游标


rebuild.close_cursor('select')

rebuild.close_cursor('dml')


关闭连接


rebuild.close_conn()

完整的python程序:

执行程序

python rebuild_id.py

最后查看表的结果

SELECT * FROM goods LIMIT 0,1;
+---------------------+------------+---------+----------+
| goods_id            | goods_name | price   | store_id |
+---------------------+------------+---------+----------+
| 3791337987775664129 | goods1     | 9369.00 |        1 |
+---------------------+------------+---------+----------+
SELECT * FROM sell_order_1 LIMIT 0,1;
+---------------------+---------------+---------+---------+--------+
| sell_order_id       | user_guide_id | user_id | price   | status |
+---------------------+---------------+---------+---------+--------+
| 3791337998693437441 |             1 |      10 | 5320.00 |      1 |
+---------------------+---------------+---------+---------+--------+
SELECT * FROM buy_order_1 LIMIT 0,1;
+---------------------+---------+---------------+
| buy_order_id        | user_id | user_guide_id |
+---------------------+---------+---------------+
| 3791337998693437441 |      10 |             1 |
+---------------------+---------+---------------+
SELECT * FROM order_goods LIMIT 0,1;
+---------------------+---------------------+---------------------+---------------+---------+------+
| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price   | num  |
+---------------------+---------------------+---------------------+---------------+---------+------+
| 3792076554839789569 | 3792076377064214529 | 3792076372429508609 |             1 | 9744.00 |    2 |
+---------------------+---------------------+---------------------+---------------+---------+------+

建议：如果在生产上有使用到snowflake请务必要弄一个高可用防止单点故障，具体策略看你们自己定啦。

MySQL分库分表单库分表和迁移数据(4th)

前奏

因为在分表的时候我们需要知道我们分的是第几个表，所以我们先需要初始化我们的分表号

-- 创建一个系统信息表为了记录下当前最大的分表号
DROP TABLE system_setting;
CREATE TABLE system_setting(
  system_setting_id INT unsigned NOT NULL AUTO_INCREMENT COMMENT '系统设置表ID',name VARCHAR(45) NOT NULL COMMENT '系统设置项目名',value VARCHAR(45) NOT NULL COMMENT '系统设置值',PRIMARY KEY(system_setting_id)  
);

-- 初始化当前最大分表号

INSERT INTO system_setting VALUES(NULL,'max_sharding_table_num',1);


-- 指定需要有哪些表需要分，为了下面分表时进行锁表

INSERT INTO system_setting VALUES(NULL,'sharding_table','sell_order');

INSERT INTO system_setting VALUES(NULL,'buy_order');

INSERT INTO system_setting VALUES(NULL,'goods');

INSERT INTO system_setting VALUES(NULL,'order_goods');


-- 需要分表的表是通过什么字段来分表的

INSERT INTO system_setting VALUES(NULL,'sharding_sell_order_by','user_guide_id');

INSERT INTO system_setting VALUES(NULL,'sharding_buy_order_by','user_id');

INSERT INTO system_setting VALUES(NULL,'sharding_goods_by','store_id');

INSERT INTO system_setting VALUES(NULL,'sharding_order_goods_by','user_guide_id');


-- 普通用户需要分那张表

INSERT INTO system_setting VALUES(NULL,'normal_user_sharding','buy_order');

-- 导购需要分的表

INSERT INTO system_setting VALUES(NULL,'user_guide_sharding','order_goods');

-- 店主需要分哪些表

INSERT INTO system_setting VALUES(NULL,'store_owner_sharding','order_goods');

INSERT INTO system_setting VALUES(NULL,'goods');

我们的目标

  1. 我们的目标是实现能手动指定创建多少张表，并且能手动指定哪些用户到哪些表中。

  2. 最终能通过一定的算法，自动化这些分表迁移数据的工作。

在这里我们来实现 '1' 手动指定,以下可能比较枯燥都是代码了,一般只要看主逻辑有一个思路，代码自己玩转它 ^_^

程序流程图

单库分表程序流程

代码主逻辑展示

在附件中有完整的代码

if __name__=='__main__':
  # 设置默认的数据库链接参数
  db_config = {
    'user'    : 'root','database': 'test'
  }

sharding = ShardingTable()


设置数据库配置


sharding.set_db_config(db_config)


初始化游标


sharding.get_conn_cursor()


提供需要分表的个数，创建分表


sharding.create_tables(9)


指定用户迁移数据到指定表


sharding.move_data('username1',2)

sharding.move_data('username6',6)

sharding.move_data('username66',9)

上面我们指定新分9个表，并且迁移 'username1-店主'、'username6-导购'、'username66-普通用户' 的数据到指定分表

完整的python代码：

查看迁移后的数据

-- 查看迁移后的数据-购买订单表
SELECT * FROM buy_order_2 LIMIT 0,1;
SELECT * FROM buy_order_6 LIMIT 0,1;
SELECT * FROM buy_order_9 LIMIT 0,1;
-- 查看迁移后的数据-商品表
SELECT * FROM goods_2 LIMIT 0,1;
SELECT * FROM goods_6 LIMIT 0,1;
SELECT * FROM goods_9 LIMIT 0,1;
-- 查看迁移后的数据-出售订单表
SELECT * FROM sell_order_2 LIMIT 0,1;
SELECT * FROM sell_order_6 LIMIT 0,1;
SELECT * FROM sell_order_9 LIMIT 0,1;
-- 查看迁移后的数据-订单商品表
SELECT * FROM order_goods_2 LIMIT 0,1;
SELECT * FROM order_goods_6 LIMIT 0,1;
SELECT * FROM order_goods_9 LIMIT 0,1;
-- 查看用户数据分布在哪个分表 table_flag
SELECT * FROM user WHERE user_id IN(1,6,66);

MySQL分库分表分表后数据的查询(5th)

前言

在分表完之后显然对于数据的查询会变的比较的复杂，特别是在表的关联方面，在有些情况下根本就不能使用JOIN。

其实个人是比较鼓励将那些大的JOIN SQL拆分成几个小的SQL来查询数据。这样虽然总体的效率可能会稍稍下降（如果使用了连接池完全可以忽略），但是查询的语句变简单了，使得后续的维护带来的方便。同时也能带来比较便利的扩展。你可以感受一下有一个100行的SQL语句给你维护，和给你10个10行并且每一块都有很好的注释的SQL去维护，去帮助调优。你愿意选哪个。不管你们信不信，反正我是选第二种，而且第二种可以很好的理解业务。

上面说到要拆分JOIN，我的意思不是将每个语句都拆分。我的准则是 O(n) 次的查询。忌讳那种查出数据后通过程序循环查出结果再去数据库中查询，也就是需要 O(n*M)这种。 瞬间感觉方法论很重要有木有 ^_^。

模拟场景

场景1：购买者下订单

  1. 在浏览商品的时候能获得商品的 门店ID 和 商品ID，至于导购ID这里我们能以随机的形式得到（需要根据业务来确定如何获取导购ID）

  2. 通过导购ID获得导购的用户信息从而得到导购的数据应该放在那张分表。

  3. 将下单数据存入出售者的分表，和购买者的分表。

下面展示的是伪代码(因为只用SQL不好展示具体业务逻辑)，其实是自己比较懒不想写Python了。^_^

-- 获得导购分表信息，和所在门店
SELECT u.table_flag AS guide_flag,ug.store_id AS store_id
FROM user AS u,user_guide AS ug
WHERE u.user_id = ug.user_id
  AND user_guide_id = 导购ID;

SET autocommit=0;

START TRANSACTION;

-- 创建销售订单 sell_order_2 通过程序拼凑出来的

INSERT INTO sell_order_2

VALUES(order_SnowflakeID,购买者ID,订单总额,订单状态);

-- 记录此订单有哪些商品

INSERT INTO order_goods_2

VALUES(order_goods_SnowflakeID,order_SnowflakeID,商品个数);

-- 记录购买订单表  buy_order_6 购买者所在的分表，上面的是出售者所在的分表别弄混了

-- 购买者订单ID 和 出售者订单ID是一样的

INSERT INTO buy_order_6

VALUES(order_SnowflakeID,用户ID,导购ID)


COMMIT;

SET autocommit=1;

情况2：购买者浏览订单

浏览购买者订单就是比较麻烦的，因为购买者订单信息和商品信息不是在同一分表中。

  1. 分页查找出购买者的订单列表。

  2. 将订单信息返回给浏览器后，使用ajax获取每个订单的商品。

-- 获得用户的分表信息 user_id = 66
SELECT table_flag FROM user WHERE user_id=66;
+------------+
| table_flag |
+------------+
|          9 |
+------------+
-- 获取用户订单,这些信息值直接先返回给浏览器的
SELECT * FROM buy_order_9 WHERE user_id=66 LIMIT 0,1;
+---------------------+---------+---------------+
| buy_order_id        | user_id | user_guide_id |
+---------------------+---------+---------------+
| 3792111966815784961 |      66 |             1 |
+---------------------+---------+---------------+
-- 获取 user_guide_id=1 用户的分表信息
SELECT u.table_flag AS guide_flag
FROM user AS u,user_guide AS ug
WHERE u.user_id = ug.user_id
  AND user_guide_id = 1;
+------------+
| guide_flag |
+------------+
|          2 |
+------------+
-- 浏览器通过ajax获取商品信息进行展现
SELECT * 
FROM order_goods_2 
WHERE sell_order_id = 3792111966815784961
  AND user_guide_id = 1;
+---------------------+---------------------+---------------------+---------------+---------+------+
| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price   | num  |
+---------------------+---------------------+---------------------+---------------+---------+------+
| 3792112143781859329 | 3792111966815784961 | 3792111950445416449 |             1 | 3100.00 |    2 |
| 3792112160789762049 | 3792111966815784961 | 3792111951305248769 |             1 | 5810.00 |    1 |
+---------------------+---------------------+---------------------+---------------+---------+------+

从上面的试验我们可以看到原本在 '分库分表(1)--基础表介绍' 中的关联查询就能获得出订单的数据现在需要被拆为多个部分来查询(是不可避免的,这样做也未必不是好事)。

这里说一下我们为什么要使用ajax来获取并展现 '订单商品' 的数据：

  1. 我们不知道 '购买订单' 的导购的分表是哪一个，因此我们需要便利查询出的每一条 '购买订单'，如果有10个订单就需要便利10次去获取对应导购是哪个分表。

  2. 获得分表完之后还需要通过每个分表去关联 '订单商品' 获得商品信息。

  3. 获得到以上信息或需要整合成一个列表返回给浏览器。

通过上面一次性把说有数据返回给浏览器的方法，会影响到用户体验，让用户觉得很慢的感觉。并且需要写复杂的逻辑，难以维护。

我们将查询时间放大，一个查是 1s 如果有10个订单 一次性完成就可能需要 11s 以上的时间才返回给浏览器。如果先将查询的订单返回给浏览器。看上去就只需要 1s就吧数据返回给浏览器了。

情况3：导购查看订单

导购也是一个普通用户,因此一登陆系统就知道 导购ID 和 用户ID

-- 获得导购的分表信息 user_id = 6,user_guide_id = 5
SELECT table_flag FROM user WHERE user_id=6;
+------------+
| table_flag |
+------------+
|          6 |
+------------+
-- 查询订单信息
SELECT * FROM sell_order_6 WHERE user_guide_id = 5 LIMIT 0,3;
+---------------------+---------------+---------+---------+--------+
| sell_order_id       | user_guide_id | user_id | price   | status |
+---------------------+---------------+---------+---------+--------+
| 3792112033412943873 |             5 |      10 | 5197.00 |      1 |
| 3792112033429721089 |             5 |      10 | 6826.00 |      1 |
| 3792112033446498305 |             5 |      10 | 5765.00 |      1 |
+---------------------+---------------+---------+---------+--------+
-- 查询订单商品信息
SELECT * FROM order_goods_6
WHERE sell_order_id IN(
  3792112033412943873,3792112033429721089,3792112033446498305
);
+---------------------+---------------------+---------------------+---------------+---------+------+
| order_goods_id      | sell_order_id       | goods_id            | user_guide_id | price   | num  |
+---------------------+---------------------+---------------------+---------------+---------+------+
| 3792112273532653569 | 3792112033412943873 | 3792111951800176641 |             5 | 7826.00 |    1 |
| 3792112292964864001 | 3792112033412943873 | 3792111952559345665 |             5 | 3057.00 |    2 |
| 3792112273545236481 | 3792112033429721089 | 3792111952660008961 |             5 | 8540.00 |    1 |
| 3792112292981641217 | 3792112033429721089 | 3792111951863091201 |             5 | 8545.00 |    1 |
| 3792112273566208001 | 3792112033446498305 | 3792111952110555137 |             5 | 8383.00 |    2 |
| 3792112292998418433 | 3792112033446498305 | 3792111952966193153 |             5 | 3282.00 |    2 |
+---------------------+---------------------+---------------------+---------------+---------+------+

情况4：导购修改订单

-- 修改订单价格
UPDATE sell_order_6 SET price = 1000.00 WHERE sell_order_id = 3792112033412943873;

情况5：店主为店铺添加商品

添加商品只有店铺的店主有权限。然而店主也是一个普通用户。

-- 获得店主的分表信息 user_id = 1
SELECT table_flag FROM user WHERE user_id=1;
+------------+
| table_flag |
+------------+
|          2 |
+------------+
-- 店主添加商品
INSERT INTO goods_2 VALUES(SnowflakeID,商品名称,门店ID);

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

-- 创建新库
CREATE DATABASE test_1;
use test;
-- 拼出需要创建的表
SELECT CONCAT('CREATE TABLE test_1.',TABLE_NAME,' LIKE ',TABLE_SCHEMA,'.',';'
)              
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 ',' SELECT * FROM test',';'
)              
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 '用户数据所在数据库名';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

本文源于原作者分享

MySQL分库分表基础表介绍(1st)

表基本模型结构

这里我们模拟一个商城的基本的表结。此结构由（用户、门店、导购、门店商品、订单、订单对应的商品）。其中，导购也是一个用户，门店是只属于一个店主的，同时店主本身也是一个导购也是一个普通用户。

结构图：

结构图

构造数据脚本

对业务场景进行模拟

场景1:购买者下订单

  1. 从session中获得客户ID。

  2. 可以通过时间戳等拼凑一个订单ID（在创建表的时候为了方便我用自增的，在以下我们一直就吧订单ID看成不是自增的，是用程序生成的）。

  3. 从商品的店铺能获得到导购ID（获取规则我们这边认为是随机）。

  4. 可以从商品中计算出订单价格。

最终就能拼凑出下单的INSERT SQL语句(这边我就不真正写插入语句了)

SET autocommit=0;
START TRANSACTION;
-- 创建订单语句
INSERT INTO orders VALUES(订单ID,导购ID,购买用户ID,订单价格,订单状态);
-- 创建商品订单语句
INSERT INTO order_goods VALUES(NULL,订单ID,商品ID,商品价格,商品数量);
-- 可以给添加多个商品到订单中
......
COMMIT;
set autocommit=1;

以上就是一个客户下单时要操作的，订单ID（订单号）是程序生成的，订单ID（订单号）是程序生成的，重要的事要说三遍。

场景2:购买者浏览订单

当用户查看订单列表的时候可以通过分页一次性获得自己的订单列表。

-- 每一页10行(这边顺便展示一下单数据量大时优化后的sql语句)
-- 查找用户ID为100的订单
SELECT l_o.orders_id,o.user_guide_id,o.user_id,o.price,og.price
FROM (
  SELECT orders_id
  FROM orders
  WHERE user_id = 100
  LIMIT 0,10
) AS l_o
  LEFT JOIN orders AS o ON l_o.orders_id = o.orders_id
  LEFT JOIN order_goods AS og ON l_o.orders_id = og.orders_id;

场景3:导购查看订单

-- 每个导购也可以查看他销售了多少的订单
-- 查找导购ID为1的销售情况
SELECT o.orders_id,og.price
FROM orders AS o
  LEFT JOIN order_goods AS og ON o.orders_id = og.orders_id
WHERE o.orders_id IN(
  SELECT orders_id
  FROM (
    SELECT orders_id
    FROM orders
    WHERE user_guide_id=1
    LIMIT 0,10
  ) AS tmp
);

场景4:导购修改订单

-- 这边我们修改订单金额就好，修改ID为1000的订单
UPDATE orders SET price = '10000' WHERE orders_id=1000;

场景5:店主为店铺添加商品

我们可以根据操作的用户获得店铺名

-- 添加商品伪SQL
INSERT INTO goods VALUES(NULL,商品名,店铺名);

MySQL分库分表创建新表结构(2nd)

前言

在互联网时代大家都知道数据量是爆炸试的增加，从之前的表结构设计来看，我们很容易的知道商品表(goods)、订单表(orders)、订单商品表(order_goods)这几张表的数据量将会爆炸试的增加。

因此，在数据量达到一定程度就算是建了索引，查询使用了索引，查询、修改速度也是会降下来的。为了额能较好的克服这样的问题，我们不得不重新整理并对大数据的表进行表切分。

分表介绍

当下有静态分表和动态分表两种：

  • 静态分表：事先估算出表能达到的量，然后根据每一个表需要存多少数据直接算出需要创建表的数量。如：1亿数据每一个表 100W 条数据那就要建100张表，然后通过一定的hash算法计算每一条数据存放在那张表。其实就有点像是使用partition table 一样。静态分表有一个毙命就是当分的那么多表还不满足时，需要再扩展难度和成本就会很高。

  • 动态分表：同样也是对大数据量的表进行拆分，他可以避免静态分表带来的后遗症。当然也需要在设计上多一些东西（这往往是我们能接受的）。

如果使用了分表的设计的数据库在一些查询上面会变的复杂一些。

我的选择

本着要让之后让表能更好的扩展，并能达到手工指定数据到自己想要的表，为了以后能自动化分表操作，我选择了动态分表。

业务分解

由于在我们的业务中每一个导购除了能卖东西，还能买东西，因此在逻辑上就分为两张表：出售订单表、购买订单表。

业务分解后表结构图如下：

结构表

我们潜规则

我们是按user表中的每一个用户去指定他的订单数据要在哪个表。

由于按用户分表后会涉及到是然购买者方便查询，还是让销售者方便查询的问题。我们这里选择的是让销售者查询方便来分表，因为销售者的查询和对订单的修改会频繁一些。因此，我们将出售订单表中存放着比较完整的订单信息，而在购买订单表中存放的是出售订单表的ID作为关联。

我们出购买订单表ID和售订单表ID保持一致。

小提示：你也可以在购买订单表中添加一些冗余字段为了更好的查询，但是建议冗余字段不要是业务上是可变的。

业务分解后数据迁移到新表

-- 创建出售订单表-sell_order_1
CREATE TABLE sell_order_1 LIKE orders;
-- 修改出售订单表ID字段名
ALTER TABLE sell_order_1
  CHANGE orders_id sell_order_id INT unsigned NOT NULL AUTO_INCREMENT 
  COMMENT '出售订单ID';
-- 修改商品订单表的订单ID名为sell_order_id
ALTER TABLE order_goods
  CHANGE orders_id sell_order_id INT unsigned NOT NULL
  COMMENT '出售订单ID';
-- 将orders表数据转移到sell_order_1表
INSERT INTO sell_order_1
SELECT * FROM orders;

-- 迁移商品表到 goods_1

CREATE TABLE goods_1 LIKE goods;

-- 插入goods_1表数据

INSERT INTO goods_1

SELECT * FROM goods;


-- 迁移订单商品表到order_goods_1

CREATE TABLE order_goods_1 LIKE order_goods;

-- 插入order_goods_1

INSERT INTO order_goods_1

SELECT * FROM order_goods;


-- 创建购买订单表

CREATE TABLE buy_order_1(

buy_order_id BIGINT unsigned NOT NULL COMMENT '出售订单ID与出售订单相等',user_id INT unsigned DEFAULT NULL COMMENT '下单用户ID',user_guide_id INT unsigned DEFAULT NULL COMMENT '导购ID',PRIMARY KEY(buy_order_id),KEY idx$buy_order_1$user_id(user_id),KEY idx$buy_order_1user_guide_id(user_guide_id)

);

-- 买订单表导入数据

INSERT INTO buy_order_1

SELECT sell_order_id,user_id,user_guide_id

FROM sell_order_1;


-- user表增加指定表标识字段

ALTER TABLE user

ADD table_flag TINYINT NOT NULL DEFAULT 1

COMMENT '分表标识';

MySQL分库分表使用Snowflake全局ID生成器(3rd)

前言

由于考虑到以后要动态切分数据，防止将不同表切分数据到同一个表中时出现主键相等的冲突情况，这里我们使用一个全局ID生存器。重要的是他是自增的。

这边我使用Snowflake的python实现版（pysnowflake）。当然你也可以使用java实现版.

具体详细信息：

Snowflake的使用

安装 requests

pip install requests

安装 pysnowflake

pip install pysnowflake

启动pysnowflake服务

snowflake_start_server 
  --address=192.168.137.11 
  --port=30001 
  --dc=1 
  --worker=1 
  --log_file_prefix=/tmp/pysnowflask.log

--address：本机的IP地址默认localhost这里解释一下参数意思（可以通过--help来获取）：

--dc：数据中心唯一标识符默认为0

--worker：工作者唯一标识符默认为0

--log_file_prefix：日志文件所在位置

使用示例（这边引用官网的）

 # 导入pysnowflake客户端
>>> import snowflake.client

链接服务端并初始化一个pysnowflake客户端








host = '192.168.137.11'

port = 30001

snowflake.client.setup(host,port)


生成一个全局唯一的ID（在MySQL中可以用BIGINT UNSIGNED对应）


snowflake.client.get_guid()

3631957913783762945


查看当前状态


snowflake.client.get_stats()

{

'dc': 1,'worker': 1,'timestamp': 1454126885629,# current timestamp for this worker

'last_timestamp': 1454126890928,# the last timestamp that generated ID on

'sequence': 1,# the sequence number for last timestamp

'sequence_overload': 1,# the number of times that the sequence is overflow

'errors': 1,# the number of times that clock went backward

}

数据整理重建ID

重建ID是一个很庞大的工程，首先要很了解表的结构。不然，如果少更新了某个表的一列都会导致数据的不一致。

当然，如果你的表中有很强的外键以及设置了级联那更新一个主键会更新其他相关联的外键。这里我还是不建议去依赖外键级联更新来投机取巧毕竟如果有数据库的设计在项目的里程碑中经过了n次变化，也不能肯定设置的外键一定是级联更新的。

在这边我强烈建议重建ID时候讲MySQL中的检查外键的参数设置为0。

SET FOREIGN_KEY_CHECKS=0;

小提示：其实理论上我们是没有必要重建ID的因为原来的ID已经是唯一的了而且是整型，他兼容BIGINT。但是这里我还是做了重建，主要是因为以后的数据一致。并且如果有些人的ID不是整型的，而是有一定含义的那时候也肯定需要做ID的重建。

修改相关表ID的数据类型为BIGINT

-- 修改商品表 goods_id 字段
ALTER TABLE goods_1
MODIFY COLUMN goods_id BIGINT UNSIGNED NOT NULL 
COMMENT '商品ID';




-- 修改出售订单表 goods_id 字段

ALTER TABLE sell_order_1

MODIFY COLUMN sell_order_id BIGINT UNSIGNED NOT NULL

COMMENT '出售订单ID';


-- 修改购买订单表 buy_order_id 字段

ALTER TABLE buy_order_1

MODIFY COLUMN buy_order_id BIGINT UNSIGNED NOT NULL

COMMENT '出售订单ID与出售订单相等';


-- 修改订单商品表 order_goods_id、orders_id、goods_id 字段

ALTER TABLE order_goods_1

MODIFY COLUMN order_goods_id BIGINT UNSIGNED NOT NULL

COMMENT '订单商品表ID';

ALTER TABLE order_goods_1

MODIFY COLUMN sell_order_id BIGINT UNSIGNED NOT NULL

COMMENT '订单ID';

ALTER TABLE order_goods_1

MODIFY COLUMN goods_id BIGINT UNSIGNED NOT NULL

COMMENT '商品ID';

使用python重建ID

使用的python 模块：

模块名
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同