尧图网页设计 
流量包业务模型与数据量预估简介梳理账号服务里流量包的业务模型预估数据量引出分库分表的需求业务模型创建短链要消耗流量包流量包是对外售卖的商品可以叠加购买一个用户会有多条流量包记录类似订单记录流量包商品商品每天可创建有效期流量包一5 次1 个月流量包二10 次6 个月流量包三50 次12 个月用户行为与流量包记录用户行为生成的流量包记录刚注册免费每天 2 次不过期购买商品一付费每天 5 次1 个月过期购买商品二 × 3 份付费每天 30 次10 × 36 个月过期表结构CREATETABLEtraffic (id bigint unsignedNOTNULLAUTO_INCREMENT,day_limitintDEFAULTNULLCOMMENT每天限制多少条短链,day_usedintDEFAULTNULLCOMMENT当天用了多少条短链,total_limitintDEFAULTNULLCOMMENT总次数活码才用,account_no bigintDEFAULTNULLCOMMENT账号,out_trade_novarchar(64)CHARACTERSETutf8mb4 COLLATE utf8mb4_binDEFAULTNULLCOMMENT订单号,levelvarchar(64)CHARACTERSETutf8mb4 COLLATE utf8mb4_binDEFAULTNULLCOMMENT产品层级FIRST青铜、SECOND黄金、THIRD钻石,expired_datedateDEFAULTNULLCOMMENT过期日期,plugin_typevarchar(64)CHARACTERSETutf8mb4 COLLATE utf8mb4_binDEFAULTNULLCOMMENT插件类型,product_id bigintDEFAULTNULLCOMMENT商品主键,gmt_create datetimeDEFAULTCURRENT_TIMESTAMP,gmt_modified datetimeDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id),UNIQUEKEYuk_trade_no (out_trade_no,account_no)USINGBTREE,KEYidx_account_no (account_no)USINGBTREE) ENGINEInnoDBDEFAULTCHARSETutf8mb4 COLLATEutf8mb4_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热点数据太多即使用了数据库自身的缓存仍有大量 IOSQL 执行慢网络 IO请求的数据多、传输量大带宽不够链路响应时间变长CPU单机做复杂 SQL 计算多表关联时 CPU 使用率高还有扫描行数大、锁冲突、锁等待分表还能缩小锁的影响范围单表 1000 万数据时一次锁表影响所有请求分成 4 张表后只影响落到被锁那张表的请求注意点分库后每个库放到不同服务器才能获得更多的 CPU、内存、带宽前期为了节省服务器可以先放在同一台分库分表带来的新问题简介分库分表不是万能方案拆分后会多出 6 类问题问题说明跨节点 Join 和多维度查询拆分前多表关联用 SQLjoin就能实现拆分后数据分布在不同节点join很麻烦分布式事务一次操作的内容分布在不同库不可避免出现跨库事务例商品库扣库存成功、订单库生成订单失败怎么回滚排序、翻页、函数计算跨节点多库查询时limit分页、order by排序都会出问题全局主键重复自增 id 在不同库表中各自增长多张表都会有 id 1 的记录二次扩容首次预估很难覆盖未来 3 ~ 5 年业务发展快就满足不了存储需要多次扩容技术选型分库分表中间件较多各有优势和短板多维度查询的例子图中绿色的查询只落到一个库橙色的查询要把所有库查一遍不同维度查数据用到的分片键partition key不一样订单表的分片键是user_id用户下单、查自己的订单列表都固定落到同一个库商家查自己店铺的订单列表很麻烦订单分布在不同的数据节点下单用户的user_id各不相同只能把所有库都查一遍库越多、商家越多越撑不住排序分页为什么更复杂排序字段不是分片字段时要先在各个分片节点排序并返回再把结果集汇总后二次排序例查前 10 条每个分片都要先取 10 条汇总后再排序取前 10 条带来更多的 CPU、IO 消耗垂直分表与垂直分库简介垂直拆分按“列”和“业务”拆解决字段过多和单库资源瓶颈垂直分表需求商品表字段太多每个字段访问频次不一样浪费 IO 资源大表拆小表基于列字段进行访问频次低、字段大的商品描述信息放一张表访问频次高的商品基本信息放一张表拆分原则不常用的字段单独放一张表text、blob等大字段拆出来放在附表业务上经常组合查询的列放在同一张表例子商品列表页只展示标题、封面、价格等基本信息进入详情页才加载课前须知、富文本详情所以商品拆成主表和附表拆分前这些字段都在product一张表里-- 拆分后主表访问频次高的基本信息CREATETABLEproduct (idint(11) unsignedNOTNULLAUTO_INCREMENT,titlevarchar(524)DEFAULTNULLCOMMENT视频标题,cover_imgvarchar(524)DEFAULTNULLCOMMENT封面图,priceint(11)DEFAULTNULLCOMMENT价格,分,totalint(10)DEFAULT0COMMENT总库存,left_numint(10)DEFAULT0COMMENT剩余,PRIMARYKEY(id)) ENGINEInnoDB AUTO_INCREMENT1DEFAULTCHARSETutf8;-- 拆分后附表访问频次低的大字段CREATETABLEproduct_detail (idint(11) unsignedNOTNULLAUTO_INCREMENT,product_idint(11)DEFAULTNULLCOMMENT产品主键,learn_base textCOMMENT课前须知学习基础,learn_result textCOMMENT达到水平,summaryvarchar(1026)DEFAULTNULLCOMMENT概述,detail textCOMMENT视频商品详情,PRIMARYKEY(id)) ENGINEInnoDB AUTO_INCREMENT1DEFAULTCHARSETutf8;垂直分库需求C 端项目里单个数据库的 CPU、内存长期处于 90% 以上数据库连接经常不够按业务拆分一个系统中的不同业务各用各的库拆分前全部落在单一的库上单库处理能力是瓶颈还受磁盘空间、内存、TPS 等限制拆分后不同库不再竞争同一台物理机的 CPU、内存、网络 IO、磁盘高并发场景下一定程度上能突破 IO、连接数和单机硬件资源的瓶颈解决业务层面的耦合业务清晰方便管理和维护单体项目升级改造为微服务项目就是垂直分库图中红色是各个微服务独立的库注意点垂直分库分表可以提高并发但没有解决单表数据量过大的问题水平分表与水平分库简介水平拆分按“行”拆表结构不变解决单表数据量过大和单库资源瓶颈垂直与水平的区别都是大表拆小表垂直分表拆表结构水平分表拆数据垂直是竖着切切列水平是横着切切行图中绿色是拆分后的表和库结构相同、数据不同水平分表需求一张表的数据达到几千万查询一次耗时长把一张表的数据分到同一个库的多张表中每张表只有部分数据每张表结构一样、数据不一样所有表的数据合起来就是全部数据针对数据量巨大的单表如订单表按某种规则RANGE、HASH 取模等切分到多张表减少锁表时间没分表前执行 DDL如添加一列会锁表期间所有读写只能等待分表后改其中一张表其他表不受影响局限这些表仍在同一个库单库操作还是有 IO 瓶颈主要解决单表数据量过大的问题水平分库需求高并发项目中水平分表后仍在单个库上一个库的 CPU、内存、带宽限制导致响应慢把同一个表的数据按一定规则分到不同的数据库数据库在不同的服务器上是对数据行的拆分不影响表结构每个库的结构都一样数据都不一样没有交集所有库的并集就是全量数据水平分库的粒度比水平分表更大可以和水平分表一起用每个库里再分表同时解决单机瓶颈和单表数据量过大的问题四种拆分方式对比方式拆分依据解决的问题局限垂直分表按列字段冷热、大小字段多、大字段浪费 IO单表行数没变垂直分库按业务单库连接数、CPU、内存、IO 瓶颈和业务耦合单表数据量过大没解决水平分表按行RANGE、HASH 取模单表数据量过大仍在同一个库有单库 IO 瓶颈水平分库按行分到不同服务器的库单库 CPU、内存、带宽瓶颈引入跨库查询、分布式事务等问题面试/考试记忆点数据库优化不要一上来就分库分表先软优化、硬优化再分表最后才分库分表数据量和访问压力不大时优先考虑缓存、读写分离、索引分表解决单表数据量大的查询性能问题分库解决单库的并发访问压力问题分库分表没有通用策略分片策略选错会造成数据热点分库分表带来 6 类问题跨节点 Join 和多维度查询、分布式事务、排序分页、全局主键、二次扩容、技术选型垂直分表按列拆冷热字段分离垂直分库按业务拆单体改微服务水平拆分按行拆结构相同、数据不同、合起来是全量垂直拆分不解决单表数据量过大水平分表不解决单库资源瓶颈容量一次预估到位单表数据量不超过 1000 万 海量数据下的分库分表：优化思路、利弊与四种拆分方式
流量包业务模型与数据量预估简介梳理账号服务里流量包的业务模型预估数据量引出分库分表的需求业务模型创建短链要消耗流量包流量包是对外售卖的商品可以叠加购买一个用户会有多条流量包记录类似订单记录流量包商品商品每天可创建有效期流量包一5 次1 个月流量包二10 次6 个月流量包三50 次12 个月用户行为与流量包记录用户行为生成的流量包记录刚注册免费每天 2 次不过期购买商品一付费每天 5 次1 个月过期购买商品二 × 3 份付费每天 30 次10 × 36 个月过期表结构CREATETABLEtraffic (id bigint unsignedNOTNULLAUTO_INCREMENT,day_limitintDEFAULTNULLCOMMENT每天限制多少条短链,day_usedintDEFAULTNULLCOMMENT当天用了多少条短链,total_limitintDEFAULTNULLCOMMENT总次数活码才用,account_no bigintDEFAULTNULLCOMMENT账号,out_trade_novarchar(64)CHARACTERSETutf8mb4 COLLATE utf8mb4_binDEFAULTNULLCOMMENT订单号,levelvarchar(64)CHARACTERSETutf8mb4 COLLATE utf8mb4_binDEFAULTNULLCOMMENT产品层级FIRST青铜、SECOND黄金、THIRD钻石,expired_datedateDEFAULTNULLCOMMENT过期日期,plugin_typevarchar(64)CHARACTERSETutf8mb4 COLLATE utf8mb4_binDEFAULTNULLCOMMENT插件类型,product_id bigintDEFAULTNULLCOMMENT商品主键,gmt_create datetimeDEFAULTCURRENT_TIMESTAMP,gmt_modified datetimeDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(id),UNIQUEKEYuk_trade_no (out_trade_no,account_no)USINGBTREE,KEYidx_account_no (account_no)USINGBTREE) ENGINEInnoDBDEFAULTCHARSETutf8mb4 COLLATEutf8mb4_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热点数据太多即使用了数据库自身的缓存仍有大量 IOSQL 执行慢网络 IO请求的数据多、传输量大带宽不够链路响应时间变长CPU单机做复杂 SQL 计算多表关联时 CPU 使用率高还有扫描行数大、锁冲突、锁等待分表还能缩小锁的影响范围单表 1000 万数据时一次锁表影响所有请求分成 4 张表后只影响落到被锁那张表的请求注意点分库后每个库放到不同服务器才能获得更多的 CPU、内存、带宽前期为了节省服务器可以先放在同一台分库分表带来的新问题简介分库分表不是万能方案拆分后会多出 6 类问题问题说明跨节点 Join 和多维度查询拆分前多表关联用 SQLjoin就能实现拆分后数据分布在不同节点join很麻烦分布式事务一次操作的内容分布在不同库不可避免出现跨库事务例商品库扣库存成功、订单库生成订单失败怎么回滚排序、翻页、函数计算跨节点多库查询时limit分页、order by排序都会出问题全局主键重复自增 id 在不同库表中各自增长多张表都会有 id 1 的记录二次扩容首次预估很难覆盖未来 3 ~ 5 年业务发展快就满足不了存储需要多次扩容技术选型分库分表中间件较多各有优势和短板多维度查询的例子图中绿色的查询只落到一个库橙色的查询要把所有库查一遍不同维度查数据用到的分片键partition key不一样订单表的分片键是user_id用户下单、查自己的订单列表都固定落到同一个库商家查自己店铺的订单列表很麻烦订单分布在不同的数据节点下单用户的user_id各不相同只能把所有库都查一遍库越多、商家越多越撑不住排序分页为什么更复杂排序字段不是分片字段时要先在各个分片节点排序并返回再把结果集汇总后二次排序例查前 10 条每个分片都要先取 10 条汇总后再排序取前 10 条带来更多的 CPU、IO 消耗垂直分表与垂直分库简介垂直拆分按“列”和“业务”拆解决字段过多和单库资源瓶颈垂直分表需求商品表字段太多每个字段访问频次不一样浪费 IO 资源大表拆小表基于列字段进行访问频次低、字段大的商品描述信息放一张表访问频次高的商品基本信息放一张表拆分原则不常用的字段单独放一张表text、blob等大字段拆出来放在附表业务上经常组合查询的列放在同一张表例子商品列表页只展示标题、封面、价格等基本信息进入详情页才加载课前须知、富文本详情所以商品拆成主表和附表拆分前这些字段都在product一张表里-- 拆分后主表访问频次高的基本信息CREATETABLEproduct (idint(11) unsignedNOTNULLAUTO_INCREMENT,titlevarchar(524)DEFAULTNULLCOMMENT视频标题,cover_imgvarchar(524)DEFAULTNULLCOMMENT封面图,priceint(11)DEFAULTNULLCOMMENT价格,分,totalint(10)DEFAULT0COMMENT总库存,left_numint(10)DEFAULT0COMMENT剩余,PRIMARYKEY(id)) ENGINEInnoDB AUTO_INCREMENT1DEFAULTCHARSETutf8;-- 拆分后附表访问频次低的大字段CREATETABLEproduct_detail (idint(11) unsignedNOTNULLAUTO_INCREMENT,product_idint(11)DEFAULTNULLCOMMENT产品主键,learn_base textCOMMENT课前须知学习基础,learn_result textCOMMENT达到水平,summaryvarchar(1026)DEFAULTNULLCOMMENT概述,detail textCOMMENT视频商品详情,PRIMARYKEY(id)) ENGINEInnoDB AUTO_INCREMENT1DEFAULTCHARSETutf8;垂直分库需求C 端项目里单个数据库的 CPU、内存长期处于 90% 以上数据库连接经常不够按业务拆分一个系统中的不同业务各用各的库拆分前全部落在单一的库上单库处理能力是瓶颈还受磁盘空间、内存、TPS 等限制拆分后不同库不再竞争同一台物理机的 CPU、内存、网络 IO、磁盘高并发场景下一定程度上能突破 IO、连接数和单机硬件资源的瓶颈解决业务层面的耦合业务清晰方便管理和维护单体项目升级改造为微服务项目就是垂直分库图中红色是各个微服务独立的库注意点垂直分库分表可以提高并发但没有解决单表数据量过大的问题水平分表与水平分库简介水平拆分按“行”拆表结构不变解决单表数据量过大和单库资源瓶颈垂直与水平的区别都是大表拆小表垂直分表拆表结构水平分表拆数据垂直是竖着切切列水平是横着切切行图中绿色是拆分后的表和库结构相同、数据不同水平分表需求一张表的数据达到几千万查询一次耗时长把一张表的数据分到同一个库的多张表中每张表只有部分数据每张表结构一样、数据不一样所有表的数据合起来就是全部数据针对数据量巨大的单表如订单表按某种规则RANGE、HASH 取模等切分到多张表减少锁表时间没分表前执行 DDL如添加一列会锁表期间所有读写只能等待分表后改其中一张表其他表不受影响局限这些表仍在同一个库单库操作还是有 IO 瓶颈主要解决单表数据量过大的问题水平分库需求高并发项目中水平分表后仍在单个库上一个库的 CPU、内存、带宽限制导致响应慢把同一个表的数据按一定规则分到不同的数据库数据库在不同的服务器上是对数据行的拆分不影响表结构每个库的结构都一样数据都不一样没有交集所有库的并集就是全量数据水平分库的粒度比水平分表更大可以和水平分表一起用每个库里再分表同时解决单机瓶颈和单表数据量过大的问题四种拆分方式对比方式拆分依据解决的问题局限垂直分表按列字段冷热、大小字段多、大字段浪费 IO单表行数没变垂直分库按业务单库连接数、CPU、内存、IO 瓶颈和业务耦合单表数据量过大没解决水平分表按行RANGE、HASH 取模单表数据量过大仍在同一个库有单库 IO 瓶颈水平分库按行分到不同服务器的库单库 CPU、内存、带宽瓶颈引入跨库查询、分布式事务等问题面试/考试记忆点数据库优化不要一上来就分库分表先软优化、硬优化再分表最后才分库分表数据量和访问压力不大时优先考虑缓存、读写分离、索引分表解决单表数据量大的查询性能问题分库解决单库的并发访问压力问题分库分表没有通用策略分片策略选错会造成数据热点分库分表带来 6 类问题跨节点 Join 和多维度查询、分布式事务、排序分页、全局主键、二次扩容、技术选型垂直分表按列拆冷热字段分离垂直分库按业务拆单体改微服务水平拆分按行拆结构相同、数据不同、合起来是全量垂直拆分不解决单表数据量过大水平分表不解决单库资源瓶颈容量一次预估到位单表数据量不超过 1000 万