PYTHON+MYSQL 分库分表实战
本文档由Linux公社 www.linuxidc.com 整理
目录
0. 前言 .......................................................................................................................................................................................................3
0.1 关于我 .............................................................................................................................................................................................3
0.2 感谢 .................................................................................................................................................................................................3
0.3 关于本站 .........................................................................................................................................................................................3
0.4 发展历程 .........................................................................................................................................................................................3
0.5 本站原创系列 .................................................................................................................................................................................3
0.6 业务合作 .........................................................................................................................................................................................4
0.7 联系我们 .........................................................................................................................................................................................4
0.8 捐助 .................................................................................................................................................................................................4
1. MySQL 分库分表基础表介绍 ...............................................................................................................................................................5
1.1. 表基本模型结构 .......................................................................................................................................................................5
1.2. 对业务场景进行模拟 ...............................................................................................................................................................5
2. MySQL 分库分表创建新表结构 ...........................................................................................................................................................7
2.1. 前言 ...........................................................................................................................................................................................7
2.2. 分表介绍 ...................................................................................................................................................................................7
2.3. 我的选择 ...................................................................................................................................................................................7
2.4. 业务分解 ...................................................................................................................................................................................7
2.5. 我们潜规则 ...............................................................................................................................................................................8
2.6. 业务分解后数据迁移到新表 ...................................................................................................................................................8
3. MySQL 分库分表使用 Snowflake 全局 ID 生成器 ............................................................................................................................ 10
3.1. 前言 ........................................................................................................................................................................................ 10
3.2. Snowflake 的使用................................................................................................................................................................... 10
3.3. 数据整理重建 ID .................................................................................................................................................................... 11
3.4. 使用 python 重建 ID .............................................................................................................................................................. 11
3.5. 执行程序 ................................................................................................................................................................................ 13
3.6. 最后查看表的结果 ................................................................................................................................................................ 13
4. MySQL 分库分表单库分表和迁移数据 ............................................................................................................................................ 15
4.1. 前奏 ........................................................................................................................................................................................ 15
4.2. 我们的目标 ............................................................................................................................................................................ 15
4.3. 程序流程图 ............................................................................................................................................................................ 16
4.4. 代码主逻辑展示 .................................................................................................................................................................... 16
4.5. 执行程序 .................................................................................................................................................................................17
4.6. 查看迁移后的数据 .................................................................................................................................................................17
5. MySQL 分库分表分表后数据的查询 ................................................................................................................................................ 18
5.1. 前言 ........................................................................................................................................................................................ 18
5.2. 模拟场景 ................................................................................................................................................................................ 18
6. MySQL 分库分表分库准备 ................................................................................................................................................................ 21
6.1. 前言 ........................................................................................................................................................................................ 21
6.2. 分库思路 ................................................................................................................................................................................ 21
6.3. 分库规则 ................................................................................................................................................................................ 21
6.4. 数据迁移 ................................................................................................................................................................................ 21
6.5. 数据迁移 SQL ......................................................................................................................................................................... 21
7. MySQL 分库分表 python 实现分库 ...................................................................................................................................................25
7.1. 理清思路 .................................................................................................................................................................................25
7.2. 分库流程图 .............................................................................................................................................................................25
7.3. 主执行过程 .............................................................................................................................................................................25
7.4. 执行分库程序 .........................................................................................................................................................................26
7.5. 执行后结果 .............................................................................................................................................................................26
7.6. python 迁移用户数据到指定的分库分表 ............................................................................................................................. 27
7.7. 主程序 .................................................................................................................................................................................... 28
本文档由Linux公社 www.linuxidc.com 整理
7.8. 迁移后结果展示 .................................................................................................................................................................... 28
8. MySQL 分库分表分库后的查询 ........................................................................................................................................................ 30
8.1. 前言 ........................................................................................................................................................................................ 30
8.2. python 操作数据库 ................................................................................................................................................................ 30
9. MySQL 分库分表-多实例 INSERT 的困扰 ......................................................................................................................................... 39
9.1. 存在问题 ................................................................................................................................................................................ 39
9.2. 解决办法 ................................................................................................................................................................................ 39
9.3. 使用 MySQL 的 XA ................................................................................................................................................................. 39
9.4. 从业务上去解决 .................................................................................................................................................................... 40
10. MySQL 分库分表-弃强妥最提高性能 ....................................................................................................................................... 42
10.1. 回顾 .................................................................................................................................................................................... 42
10.2. 我们想要的 ........................................................................................................................................................................ 42
10.3. 业务最终一致性 ................................................................................................................................................................ 42
10.4. 为什么要最终一致性 ........................................................................................................................................................ 42
10.5. kafka 配合完成最终一致性 ............................................................................................................................................... 42
11. MySQL 分库分表-缩容............................................................................................................................................................... 50
11.1. 此缩容非彼缩容 ................................................................................................................................................................ 50
11.2. 缩容 .................................................................................................................................................................................... 50
11.3. 总结 .....................................................................................................................................................................................51
12. MySQL 分库分表(番外篇 1)-使用 kafka 记入日志 ....................................................................................................................52
12.1. 前言 .....................................................................................................................................................................................52
12.2. 实际情况 .............................................................................................................................................................................52
12.3. 解决方案 .............................................................................................................................................................................52
13. MySQL 分库分表(番外篇 2)-使用 redis .....................................................................................................................................53
13.1. 前言 .....................................................................................................................................................................................53
13.2. 我的推荐 .............................................................................................................................................................................53
13.3. 在那里使用 redis ................................................................................................................................................................53
14. MySQL 分库分表(番外篇 3)-小表冗余 ..................................................................................................................................... 54
版本记录 .....................................................................................................................................................................................................55
本文档由Linux公社 www.linuxidc.com 整理
0. 前言
0.1 关于我
从事 JAVA、PHP、Python 开发工作。Oracle 数据库认证专家,且与 MySQL 数据库结缘至今。
。
本文档由Linux公社 www.linuxidc.com 整理
1. MySQL 分库分表基础表介绍
1.1. 表基本模型结构
这里我们模拟一个商城的基本的表结。此结构由(用户、门店、导购、门店商品、订单、订单对应的商品)。其中,导购
也是一个用户,门店是只属于一个店主的,同时店主本身也是一个导购也是一个普通用户。
结构图:
构造数据脚本:MySQL 分库分表(1)-脚本
1.2. 对业务场景进行模拟
场景 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;
本文档由Linux公社 www.linuxidc.com 整理