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

HH MySQL213,6594字数 2026阅读6分45秒阅读模式

前言

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

因此,在数据量达到一定程度就算是建了索引,查询使用了索引,查询、修改速度也是会降下来的。为了额能较好的克服这样的问题,我们不得不重新整理并对大数据的表进行表切分。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

分表介绍

当下有静态分表和动态分表两种:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

  • 静态分表:事先估算出表能达到的量,然后根据每一个表需要存多少数据直接算出需要创建表的数量。如:1亿数据每一个表 100W 条数据那就要建100张表,然后通过一定的hash算法计算每一条数据存放在那张表。其实就有点像是使用partition table 一样。静态分表有一个毙命就是当分的那么多表还不满足时,需要再扩展难度和成本就会很高。
  • 动态分表:同样也是对大数据量的表进行拆分,他可以避免静态分表带来的后遗症。当然也需要在设计上多一些东西(这往往是我们能接受的)。

如果使用了分表的设计的数据库在一些查询上面会变的复杂一些。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

我的选择

本着要让之后让表能更好的扩展,并能达到手工指定数据到自己想要的表,为了以后能自动化分表操作,我选择了动态分表。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

业务分解

由于在我们的业务中每一个导购除了能卖东西,还能买东西,因此在逻辑上就分为两张表:出售订单表、购买订单表。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

业务分解后表结构图如下:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

mysql文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

我们潜规则

我们是按user表中的每一个用户去指定他的订单数据要在哪个表。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

由于按用户分表后会涉及到是然购买者方便查询,还是让销售者方便查询的问题。我们这里选择的是让销售者查询方便来分表,因为销售者的查询和对订单的修改会频繁一些。因此,我们将出售订单表中存放着比较完整的订单信息,而在购买订单表中存放的是出售订单表的ID作为关联。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

我们出购买订单表ID和售订单表ID保持一致。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

小提示:你也可以在购买订单表中添加一些冗余字段为了更好的查询,但是建议冗余字段不要是业务上是可变的。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

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

-- 创建出售订单表-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 '分表标识';

如有疑问跟帖说明。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

感觉本文内容不错,读后有收获?文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

逛逛衣服店,鼓励作者写出更好文章。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
HH
  • 本文由 发表于 18/02/2016 01:38:50
  • 转载请务必保留本文链接:https://www.ttlsa.com/mysql/mysql-distributed-database-and-table-create-new-table/
评论  2  访客  2
    • 阿龙弟弟
      阿龙弟弟 1

      “查询使用了索引,查询、修改速度也是会降下来的”
      是不是有错?查询是用了索引,为什么查询的速度会降下来,不是应该提高?

        • 匿名
          匿名 9

          @ 阿龙弟弟 人家说这话前提是表过大,此时索引过多,查询速度当然不如以前

      评论已关闭!