海量数据下的分库分表：优化思路、利弊与四种拆分方式

guslegend
0 阅读12分钟

流量包业务模型与数据量预估

简介：梳理账号服务里流量包的业务模型，预估数据量，引出分库分表的需求

  • 业务模型

    • 创建短链要消耗流量包，流量包是对外售卖的商品，可以叠加购买
    • 一个用户会有多条流量包记录，类似订单记录

  • 流量包商品

商品每天可创建有效期
流量包一5 次1 个月
流量包二10 次6 个月
流量包三50 次12 个月
  • 用户行为与流量包记录
用户行为生成的流量包记录
刚注册免费：每天 2 次，不过期
购买商品一付费：每天 5 次，1 个月过期
购买商品二 × 3 份付费：每天 30 次（10 × 3），6 个月过期
  • 表结构
CREATE TABLE `traffic` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `day_limit` int DEFAULT NULL COMMENT '每天限制多少条，短链',
  `day_used` int DEFAULT NULL COMMENT '当天用了多少条，短链',
  `total_limit` int DEFAULT NULL COMMENT '总次数，活码才用',
  `account_no` bigint DEFAULT NULL COMMENT '账号',
  `out_trade_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单号',
  `level` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '产品层级：FIRST青铜、SECOND黄金、THIRD钻石',
  `expired_date` date DEFAULT NULL COMMENT '过期日期',
  `plugin_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '插件类型',
  `product_id` bigint DEFAULT NULL COMMENT '商品主键',
  `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_trade_no` (`out_trade_no`,`account_no`) USING BTREE,
  KEY `idx_account_no` (`account_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

  • 数据量预估

    • 用户量由产品 / 运营预估：按免费流量（软文、内容平台推广）和付费流量（广告平台投放）估算每月新增，再乘以月数
    • 未来 2 年累计 500 万用户，每个用户每年约 10 条记录，总量约 5000 万条
    • 单表数据量最好不超过 1000 万，所以至少分 5 张表
    • 进一步按水平分表的思路，表数量取 2、4、8、16 张
    • 实战里业务逻辑复杂，先分 2 张表
      • 哈希取模方式下，分 2 张和 4 张的编码复杂度差别不大，但表越多调试越麻烦

  • 注意点

    • 容量尽量一次性预估好，数据入库后再扩容要做数据迁移，成本大
    • 估不准时可以多分几张表，多几张表影响不大

数据库性能优化思路（面试题）

简介：单表 1000 万数据，未来 1 年再增长 500 万，查询变慢，说出优化思路

  • 回答顺序
    • 千万不要一上来就说分库分表
      • 1000 万 ~ 2000 万的数据量并不大，靠软硬优化足够解决，涨到 3000 万也能撑住
    • 先反问业务场景，再从“不分库分表”和“分库分表”两个角度回答

数据库优化思路

  • 图中绿色是优先做的不分库分表方案，红色是最后才考虑的分库分表

  • 不分库分表

类型做法
软优化数据库参数调优（缓存、连接池等）
软优化分析慢查询 SQL 和执行计划，改写 SQL 和程序
软优化优化索引结构、优化表结构
软优化引入 NoSQL、调整程序架构
硬优化提升硬件：带宽、CPU 核数、内存、机械硬盘换固态硬盘

  • 软优化里最关键的一项：引入 NoSQL 和程序架构调整

    • 读写分离
      • 多数业务读多写少，一主多从，读请求走从库，写请求走主库
      • 问题：主从复制有延迟，要看业务能否接受
    • 引入 NoSQL
      • 把数据同步到 Elasticsearch 做宽表，复杂的关联查询直接查宽表
      • 同样有同步延迟

  • 分库分表

    • 没有通用策略，根据业务场景选择（外卖、物流、电商都不一样）
    • 先看只分表能否满足业务需求和未来增长
      • 分表解决单表数据量大时的查询效率问题
      • 分表仍在同一个库上操作，CPU、内存、IO 没变，提升不了并发
    • 只分表满足不了，再分库和分表一起做

  • 注意点

    • 分片策略选错会产生数据热点
      • 外卖按城市分库：一线城市的库数据量和访问量远大于小城市，瓶颈集中在少数库，其余库浪费资源
      • 按时间范围分库：产品爆发期注册的活跃用户都集中在同一个库
    • 要问清楚“慢”是 SQL 响应慢还是并发上不去，只是单表数据量大就先分表

  • 结论

    • 数据量和访问压力不是特别大：优先考虑缓存、读写分离、索引等方案
    • 数据量极大且业务持续快速增长：再考虑分库分表

分库分表解决的问题

简介：分库分表能突破数据库自身的瓶颈，以及服务器 IO、CPU 的瓶颈

单库拆成多库多表

  • 图中红色是分库后的库，绿色是每个库内再分出的表

  • 解决数据库本身的瓶颈

    • 连接数不够：连接过多时报 too many connections
      • 原因是访问量太大，或数据库设置的最大连接数太小
      • 多个库共用一个 MySQL 实例时共享连接数，每个服务节点的连接池又各占几十个连接，多启动几个节点就占满
      • 最大连接数可以调大，但调得过大同样有瓶颈
    • 分表解决单表海量数据的查询性能问题，分库解决单台数据库的并发访问压力问题
      • 例：用户表 1000 万数据分成 4 张表，每张 250 万，单次查询面对的数据量大幅下降

  • 解决系统本身的 IO、CPU 瓶颈

瓶颈表现
磁盘读写 IO热点数据太多，即使用了数据库自身的缓存，仍有大量 IO，SQL 执行慢
网络 IO请求的数据多、传输量大，带宽不够，链路响应时间变长
CPU单机做复杂 SQL 计算（多表关联）时 CPU 使用率高，还有扫描行数大、锁冲突、锁等待

  • 分表还能缩小锁的影响范围

    • 单表 1000 万数据时，一次锁表影响所有请求
    • 分成 4 张表后，只影响落到被锁那张表的请求

  • 注意点

    • 分库后每个库放到不同服务器，才能获得更多的 CPU、内存、带宽；前期为了节省服务器可以先放在同一台

分库分表带来的新问题

简介：分库分表不是万能方案，拆分后会多出 6 类问题

问题说明
跨节点 Join 和多维度查询拆分前多表关联用 SQL join 就能实现，拆分后数据分布在不同节点，join 很麻烦
分布式事务一次操作的内容分布在不同库，不可避免出现跨库事务；例：商品库扣库存成功、订单库生成订单失败，怎么回滚
排序、翻页、函数计算跨节点多库查询时，limit 分页、order by 排序都会出问题
全局主键重复自增 id 在不同库表中各自增长，多张表都会有 id = 1 的记录
二次扩容首次预估很难覆盖未来 3 ~ 5 年，业务发展快就满足不了存储，需要多次扩容
技术选型分库分表中间件较多，各有优势和短板
  • 多维度查询的例子

多维度查询问题

  • 图中绿色的查询只落到一个库，橙色的查询要把所有库查一遍

  • 不同维度查数据，用到的分片键（partition key）不一样

    • 订单表的分片键是 user_id：用户下单、查自己的订单列表，都固定落到同一个库
    • 商家查自己店铺的订单列表很麻烦：订单分布在不同的数据节点
      • 下单用户的 user_id 各不相同，只能把所有库都查一遍，库越多、商家越多越撑不住

  • 排序分页为什么更复杂

    • 排序字段不是分片字段时，要先在各个分片节点排序并返回，再把结果集汇总后二次排序
      • 例：查前 10 条，每个分片都要先取 10 条，汇总后再排序取前 10 条
    • 带来更多的 CPU、IO 消耗

垂直分表与垂直分库

简介：垂直拆分按“列”和“业务”拆，解决字段过多和单库资源瓶颈

  • 垂直分表
    • 需求：商品表字段太多，每个字段访问频次不一样，浪费 IO 资源
    • 大表拆小表，基于列字段进行
      • 访问频次低、字段大的商品描述信息放一张表
      • 访问频次高的商品基本信息放一张表
    • 拆分原则
      • 不常用的字段单独放一张表
      • text、blob 等大字段拆出来放在附表
      • 业务上经常组合查询的列放在同一张表
    • 例子：商品列表页只展示标题、封面、价格等基本信息，进入详情页才加载课前须知、富文本详情，所以商品拆成主表和附表
      • 拆分前这些字段都在 product 一张表里
-- 拆分后：主表，访问频次高的基本信息
CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(524) DEFAULT NULL COMMENT '视频标题',
  `cover_img` varchar(524) DEFAULT NULL COMMENT '封面图',
  `price` int(11) DEFAULT NULL COMMENT '价格,分',
  `total` int(10) DEFAULT '0' COMMENT '总库存',
  `left_num` int(10) DEFAULT '0' COMMENT '剩余',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- 拆分后：附表，访问频次低的大字段
CREATE TABLE `product_detail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL COMMENT '产品主键',
  `learn_base` text COMMENT '课前须知，学习基础',
  `learn_result` text COMMENT '达到水平',
  `summary` varchar(1026) DEFAULT NULL COMMENT '概述',
  `detail` text COMMENT '视频商品详情',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  • 垂直分库
    • 需求：C 端项目里，单个数据库的 CPU、内存长期处于 90% 以上，数据库连接经常不够
    • 按业务拆分：一个系统中的不同业务各用各的库
      • 拆分前全部落在单一的库上，单库处理能力是瓶颈，还受磁盘空间、内存、TPS 等限制
      • 拆分后不同库不再竞争同一台物理机的 CPU、内存、网络 IO、磁盘
    • 高并发场景下，一定程度上能突破 IO、连接数和单机硬件资源的瓶颈
    • 解决业务层面的耦合，业务清晰，方便管理和维护
    • 单体项目升级改造为微服务项目，就是垂直分库

垂直分库

  • 图中红色是各个微服务独立的库

  • 注意点

    • 垂直分库分表可以提高并发，但没有解决单表数据量过大的问题

水平分表与水平分库

简介：水平拆分按“行”拆，表结构不变，解决单表数据量过大和单库资源瓶颈

  • 垂直与水平的区别
    • 都是大表拆小表
    • 垂直分表：拆表结构
    • 水平分表：拆数据
    • 垂直是竖着切（切列），水平是横着切（切行）

水平分表与水平分库

  • 图中绿色是拆分后的表和库，结构相同、数据不同

  • 水平分表

    • 需求：一张表的数据达到几千万，查询一次耗时长
    • 把一张表的数据分到同一个库的多张表中，每张表只有部分数据
      • 每张表结构一样、数据不一样，所有表的数据合起来就是全部数据
    • 针对数据量巨大的单表（如订单表），按某种规则（RANGE、HASH 取模等）切分到多张表
    • 减少锁表时间：没分表前执行 DDL（如添加一列）会锁表，期间所有读写只能等待
      • 分表后改其中一张表，其他表不受影响
    • 局限：这些表仍在同一个库，单库操作还是有 IO 瓶颈，主要解决单表数据量过大的问题

  • 水平分库

    • 需求：高并发项目中，水平分表后仍在单个库上，一个库的 CPU、内存、带宽限制导致响应慢
    • 把同一个表的数据按一定规则分到不同的数据库，数据库在不同的服务器上
    • 是对数据行的拆分，不影响表结构
    • 每个库的结构都一样，数据都不一样，没有交集，所有库的并集就是全量数据
    • 水平分库的粒度比水平分表更大
    • 可以和水平分表一起用（每个库里再分表），同时解决单机瓶颈和单表数据量过大的问题

  • 四种拆分方式对比

方式拆分依据解决的问题局限
垂直分表按列（字段冷热、大小）字段多、大字段浪费 IO单表行数没变
垂直分库按业务单库连接数、CPU、内存、IO 瓶颈和业务耦合单表数据量过大没解决
水平分表按行（RANGE、HASH 取模）单表数据量过大仍在同一个库，有单库 IO 瓶颈
水平分库按行，分到不同服务器的库单库 CPU、内存、带宽瓶颈引入跨库查询、分布式事务等问题

面试/考试记忆点

  • 数据库优化不要一上来就分库分表：先软优化、硬优化，再分表，最后才分库分表
  • 数据量和访问压力不大时，优先考虑缓存、读写分离、索引
  • 分表解决单表数据量大的查询性能问题，分库解决单库的并发访问压力问题
  • 分库分表没有通用策略，分片策略选错会造成数据热点
  • 分库分表带来 6 类问题：跨节点 Join 和多维度查询、分布式事务、排序分页、全局主键、二次扩容、技术选型
  • 垂直分表按列拆（冷热字段分离），垂直分库按业务拆（单体改微服务）
  • 水平拆分按行拆：结构相同、数据不同、合起来是全量
  • 垂直拆分不解决单表数据量过大，水平分表不解决单库资源瓶颈
  • 容量一次预估到位，单表数据量不超过 1000 万
avatar
文章
阅读
粉丝