mysql分库分表分页查询语句_MySQL分库分表的实现过程详解介绍

MySQL分库分表对于许多的开发者来讲这个只是听说地自己从来没做过MySQL分库分表的安案了其实小编也只做过mysql分表的例子没做过分库的案例了，今天我们来看一篇关于MySQL分库分表的例子吧，具体如下所示。

MySQL分库分表基础表介绍

表基本模型结构

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

结构图：

 

构造数据脚本

MySQL分库分表(1)-脚本

对业务场景进行模拟

场景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,

o.user_guide_id,

o.user_id,

o.price,

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：店主为店铺添加商品

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

-- 添加商品伪SQL

INSERT INTO goods VALUES(NULL, 商品名, 商品价格, 店铺名);

MySQL分库分表创建新表结构

分表介绍

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

静态分表：事先估算出表能达到的量，然后根据每一个表需要存多少数据直接算出需要创建表的数量。如：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生成器

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

&#