垂直分表、水平分表详解

冲上云霄的Jayden

于 2023-06-06 12:14:23 发布

阅读量2.2k

点赞数 4

分类专栏： MySQL技术文章标签：垂直分表水平分表分库分表

本文链接：https://blog.csdn.net/NF_ALONG/article/details/131064379

版权

MySQL技术专栏收录该内容

14 篇文章 3 订阅

订阅专栏

垂直分表

什么是垂直分表

垂直分表就是把一张表按列分为多张表，多张表通过主键进行关联，从而组成完整的数据。

分表之后，每张表的结构都不相同。

垂直分表不需要额外引用其他组件，需要到Repository层面建立好表映射即可。

根据什么分表呢？

根据数据是否是热点数据划分。

热点数据即经常查询、更新频繁的列。

例如一个订单状态信息会频繁进行更新、订单金额在列表会频繁被查询到作为热点数据，而下单地址、手机号码等信息基本不会改变或者改变次数很少作为非热点数据。

垂直分表有哪些好处呢？

把热点数据分离，更新的性能更加高；
减少行数据，数据库IO效率高；
业务所有数据存在一个或者多个表，管理后台聚合查询（走从库）比较方便。

垂直分表实战

例如我们有一张外卖的表，全表结构如下：

CREATE TABLE `meal_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `trade_no` varchar(128) NOT NULL DEFAULT '' COMMENT '交易流水号',
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_type` tinyint(4) NOT NULL COMMENT '外卖类型:4:外卖;1:买单;3:团购',
  `order_status` tinyint(4) NOT NULL COMMENT '订单状态：1:未付款;2:已付款;4:部分退款;5:已退款;6:已完成;-1:已取消',
  `delivery_status` int(11) DEFAULT NULL COMMENT '配送状态：0:默认;0:已推送给配送方;10:已抢单;20:已取餐;40:已送达;100:已取消',
  `consumption_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '类型，1=个人，2=商务',
  `user_sn` bigint(20) NOT NULL COMMENT 'userSn',
  `employee_sn` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '员工SN',
  `enterprise_sn` varchar(20) NOT NULL DEFAULT '' COMMENT '企业编号',
  `order_amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '订单总价',
  `order_pay_amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '订单支付价格',
  `shipping_fee` decimal(12,2) DEFAULT NULL COMMENT '订单配送费（原始配送费）',
  `service_fee` decimal(12,2) DEFAULT NULL COMMENT '服务费',
  `ent_service_fee` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '企业服务费',
  `ent_service_fee_ratio` decimal(15,2) DEFAULT NULL COMMENT '企业服务费比例',
  `order_time` datetime DEFAULT NULL COMMENT '下单时间',
  `latest_refund_time` datetime DEFAULT NULL COMMENT '最后退款时间',
  `pay_expire_at` datetime DEFAULT NULL COMMENT '支付之后有效时间',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `pay_type` tinyint(4) DEFAULT NULL COMMENT '支付方式 1-微信支付,2-支付宝支付,3-骑士币支付,31-骑士币+微信支付,32-骑士币+支付宝支付',
  `pay_status` tinyint(4) NOT NULL COMMENT '支付状态',
  `shop_name` varchar(128) DEFAULT NULL COMMENT '商家名',
  `shop_phone` varchar(100) DEFAULT NULL COMMENT '商家电话，多个,隔开',
  `shop_address` varchar(300) DEFAULT NULL COMMENT '商家地址',
  `estimate_arrive_time` datetime DEFAULT NULL COMMENT '预计送达时间',
  `actual_arrive_time` datetime DEFAULT NULL COMMENT '实际送达时间',
  `outer_order_id` varchar(100) NOT NULL COMMENT '外部订单ID',
  `outer_order_status` int(11) DEFAULT NULL COMMENT '外部订单状态',
  `recipient_name` varchar(64) DEFAULT NULL COMMENT '收货人姓名',
  `recipient_phone` varchar(64) DEFAULT NULL COMMENT '收货人手机号',
  `recipient_address` varchar(128) DEFAULT NULL COMMENT '收货地址',
  `recipient_address_longitude` varchar(50) DEFAULT NULL COMMENT '收货地址经度  实际值需要除以 10^6 进行换算',
  `recipient_address_latitude` varchar(50) DEFAULT NULL COMMENT '收货地址维度	实际值需要除以 10^6 进行换算',
  `notify_url` varchar(500) DEFAULT NULL COMMENT '渠道服务器主动通知外部服务器里指定的页面路径',
  `return_url` varchar(500) DEFAULT NULL COMMENT '操作成功跳转地址',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `version` bigint(20) DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order_id` (`order_id`),
  KEY `idx_employee_sn` (`employee_sn`),
  KEY `idx_order_id` (`outer_order_id`),
  KEY `idx_trade_no` (`trade_no`),
  KEY `idx_user_sn` (`user_sn`),
  KEY `idx_create_time_status` (`create_time`,`order_status`)
) ENGINE=InnoDB AUTO_INCREMENT=406 DEFAULT CHARSET=utf8mb4 COMMENT='外卖订单表'

表中的字段很多，如果只是获取其中某些列，数据库也需要把整行加载到内存再截取某些列，这样自然增加了IO成本。

在外卖订单里面，订单ID、订单状态、配送状态、用户Sn、支付状态等经常更新和在订单列表查询到，作为热点数据。

企业信息、服务费、商家信息、收货人信息等更新频率极低，并且只在订单详情查询，作为非热点数据。

外卖订单表垂直拆分如下：

CREATE TABLE `meal_order` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `order_type` tinyint(4) NOT NULL COMMENT '外卖类型:4:外卖;1:买单;3:团购',
  `order_status` tinyint(4) NOT NULL COMMENT '订单状态：1:未付款;2:已付款;4:部分退款;5:已退款;6:已完成;-1:已取消',
  `delivery_status` int(11) DEFAULT NULL COMMENT '配送状态：0:默认;0:已推送给配送方;10:已抢单;20:已取餐;40:已送达;100:已取消',
  `user_sn` bigint(20) NOT NULL COMMENT 'userSn',
  `order_amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '订单总价',
  `order_pay_amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '订单支付价格',
  `order_time` datetime DEFAULT NULL COMMENT '下单时间',
  `pay_expire_at` datetime DEFAULT NULL COMMENT '支付之后有效时间',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  `pay_type` tinyint(4) DEFAULT NULL COMMENT '支付方式 1-微信支付,2-支付宝支付,3-骑士币支付,31-骑士币+微信支付,32-骑士币+支付宝支付',
  `pay_status` tinyint(4) NOT NULL COMMENT '支付状态',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `version` bigint(20) DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order_id` (`order_id`),
  KEY `idx_user_sn` (`user_sn`),
  KEY `idx_create_time_status` (`create_time`,`order_status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='外卖订单表';

CREATE TABLE `meal_order_attach` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `trade_no` varchar(128) NOT NULL DEFAULT '' COMMENT '交易流水号',
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `consumption_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '类型，1=个人，2=商务',
  `employee_sn` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '员工SN',
  `enterprise_sn` varchar(20) NOT NULL DEFAULT '' COMMENT '企业编号',
  `shipping_fee` decimal(12,2) DEFAULT NULL COMMENT '订单配送费（原始配送费）',
  `service_fee` decimal(12,2) DEFAULT NULL COMMENT '服务费',
  `ent_service_fee` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '企业服务费',
  `ent_service_fee_ratio` decimal(15,2) DEFAULT NULL COMMENT '企业服务费比例',
  `latest_refund_time` datetime DEFAULT NULL COMMENT '最后退款时间',
  `shop_name` varchar(128) DEFAULT NULL COMMENT '商家名',
  `shop_phone` varchar(100) DEFAULT NULL COMMENT '商家电话，多个,隔开',
  `shop_address` varchar(300) DEFAULT NULL COMMENT '商家地址',
  `estimate_arrive_time` datetime DEFAULT NULL COMMENT '预计送达时间',
  `actual_arrive_time` datetime DEFAULT NULL COMMENT '实际送达时间',
  `outer_order_id` varchar(100) NOT NULL COMMENT '外部订单ID',
  `outer_order_status` int(11) DEFAULT NULL COMMENT '外部订单状态',
  `recipient_name` varchar(64) DEFAULT NULL COMMENT '收货人姓名',
  `recipient_phone` varchar(64) DEFAULT NULL COMMENT '收货人手机号',
  `recipient_address` varchar(128) DEFAULT NULL COMMENT '收货地址',
  `recipient_address_longitude` varchar(50) DEFAULT NULL COMMENT '收货地址经度  实际值需要除以 10^6 进行换算',
  `recipient_address_latitude` varchar(50) DEFAULT NULL COMMENT '收货地址维度	实际值需要除以 10^6 进行换算',
  `notify_url` varchar(500) DEFAULT NULL COMMENT '渠道服务器主动通知外部服务器里指定的页面路径',
  `return_url` varchar(500) DEFAULT NULL COMMENT '操作成功跳转地址',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `version` bigint(20) DEFAULT '0' COMMENT '版本号',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order_id` (`order_id`),
  KEY `idx_employee_sn` (`employee_sn`),
  KEY `idx_order_id` (`outer_order_id`),
  KEY `idx_trade_no` (`trade_no`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='外卖订单表拓展信息';

meal_order 表保存热点数据，meal_order_attach表保存非热点数据。

meal_order 表可以很好的支持列表查询、订单状态更新等热点数据，并且表列数比原来少了一半多。

水平分表

什么是水平分表

水平分表就是指以行为单位对数据进行拆分，一般意义上的分库分表指的就是水平分表。

分表之后，所有表的结构都是一样的。

水平分表需要额外引入中间主键完成，例如shardingSphere等。

为什么要水平分表

水平分表可以解决表中的数据量大的问题，如果一张表的数据太多，操作起来会很麻烦，所以分表存储数据。

水平分表有哪些好处呢？

表数据量小，如分表算法合适，每个表大小相似；
拓展方便，如数据量继续增大，可以在增加表；

但凡事有两面性，水平分表需要选择合适的列进行，管理后台查询是需要union 或者引入ES等组件。

水平分表实战

水平分表算法

一般可以有范围法和hash法来进行水平分表。

加入未来5年数据量增加到1亿，预算每个表1千万数据，那么需要分成10个表。

范围法很好理解，可以让第1-1千万行数据存放在表1，第1千万01-2千万行数据存放在表2，以此类推。
范围法也有缺点，冷热数据不均匀，例如订单查询和操作都是最近的，几个月或者更早一起的订单便很少有操作。

hash法也不难理解，就是选择一个合适的hash函数对指定的列计算哈希码后进行取模，比如说使用取模操作(%)，把%3结果为0的数据存放在表1，结果为1的存放在表2，结果为2的存放在表3即可。
hash法也有缺点，后续再就行分表时，需要改造hash函数，或者迁移就数据。

冲上云霄的Jayden

关注

4
点赞
踩
13

收藏

觉得还不错? 一键收藏
打赏
0
评论
垂直分表、水平分表详解

垂直分表就是把一张表按列分为多张表，多张表通过主键进行关联，从而组成完整的数据。分表之后，每张表的结构都不相同。垂直分表不需要额外引用其他组件，需要到Repository层面建立好表映射即可。水平分表就是指以行为单位对数据进行拆分，一般意义上的分库分表指的就是水平分表。分表之后，所有表的结构都是一样的。水平分表需要额外引入中间主键完成，例如shardingSphere等。
复制链接

扫一扫