数据优化——分库分表（一）概念及运用场景-详解

数据优化——分库分表（二）策略讲解
数据优化——分库分表（三）中间件讲解
数据优化——分库分表（四）高级策略

1 数据优化的前提是数据预估

数据的量永远是在增长的，不会是死的， 所以你优化的前提是对数据的预估，确保自己的方案某时间段内某数据量内有用（没有对的方案，只有适合的方案）。
要学会跟业务交流协商拟定数据量，比如2年内，用户数量500个W

1. 一个用户数据是10/年，一年的重量就是5000W条

2 优化思路（面试题）

按上面的的数据规格出题，提高数据检索性能，有的朋友可能就很粗暴，“假设机器以1000W性能为优，直接分5张表”。也不能说是错，只是这样粗暴的分析不一定能得到最好的效果和ROI（产出比）。

1. 不分库分表

• 软优化
  • 数据库参数的调整，例如连接数，内存量。
  • 慢SQL的检索，分析执行计划，进行SQL和程序的优化
  • 优化数据库的索引结构
  • 优化数据库的表结构（该冗余的还是要冗余的）
  • 引⼊NOSQL和程序架构调整（比如ES宽表，比如读写分离）
• 硬优化
  • 提升系统硬件（更快的IO、更多的内存）：带宽、CPU、硬盘

2. 分库分表

• 根据业务情况而定，选择适合的分库分表策略（策略因业务而变），比如外卖，电商，物流，甚至不同领域里面的不同业务都会不同。
• 先看只分表能不能满足业务现有问题和持续增长的量级问题
• 分表⽆法给数据库的并发操作带来效率上的提⾼，分表的实质还是在⼀个数据库上进⾏的操作，受数据库IO性能的限制。如果单分表满⾜不了需求，再分库分表⼀起

3. 结论
   在数据量及访问压⼒不是特别⼤的情况，⾸先考虑缓存、读写
   分离、索引技术等⽅案
   如果数据量极⼤，且业务持续增⻓快，再考虑分库分表⽅案（因为分库分表有利，也有弊）

3 分库分表的优点

3.1分库分表解决的现状问题

1. 解决数据库本身瓶颈

2. 连接数： 连接数过多时，就会出现‘too many connections’的错误，访问量太⼤或者数据库设置的最⼤连接数太⼩的原因，Mysql默认的最⼤连接数为100.可以修改，⽽mysql服务允许的最⼤连接数为16384

3. 数据库分表可以解决单表海量数据的查询性能问题

4. 数据库分库可以解决单台数据库的并发访问压⼒问题

5. 解决系统本身IO、CPU瓶颈
   例如：
   磁盘读写IO瓶颈，热点数据太多，尽管使⽤了数据库本身缓存，但是依旧有⼤量IO,导致sql执⾏速度慢。
   ⽹络IO瓶颈，请求的数据太多，数据传输⼤，⽹络带宽不够，链路响应时间变⻓ 。
   CPU瓶颈，尤其在基础数据ᰁ⼤单机复杂SQL计算，SQL语句执⾏占⽤CPU使⽤率⾼，也有扫描⾏数⼤、锁冲突、锁等待等原因

4 分库分表的缺点

1. 跨节点数据库Join关联查询和多维度查询

• 数据库切分前，多表关联查询，可以通过sql join进⾏实现，分库分表后，数据可能分布在不同的节点上，sql join带来的问题就⽐较麻烦。
• 不同维度查看数据，利⽤的partitionKey是不⼀样的，订单表 的partionKey是user_id，⽤户查看⾃⼰的订
  单列表⽅便，但商家查看⾃⼰店铺的订单列表就麻烦，分布在不同数据节点，查询结果需要聚合。

2. 分库操作带来的分布式事务问题

• 操作内容同时分布在不同库中，不可避免会带来跨库事务问题，即分布式事务

3. 执⾏的SQL排序、翻⻚、函数计算问题

• 分库后，数据分布再不同的节点上， 跨节点多库进⾏查询时，会出现limit分⻚、order by排序等问题 。
• ⽽且当排序字段⾮分⽚字段时，更加复杂了，要在不同的分⽚节点中将数据进⾏排序并返回，然后将不同分⽚返回的结果集进⾏汇总和再次排序。（也会带来更多的CPU/IO资源损耗）

4. 数据库全局主键重复问题

• 常规表的id是使⽤⾃增id进⾏实现，分库分表后，由于表中数据同时存在不同数据库中，如果⽤⾃增id，则会出现冲突问题。

5. 容规划,分库分表后⼆次扩容问题

• 业务发展快，初次分库分表后，满⾜不了数据存储，导致需要多次扩容

6. 分库分表技术选型问题

• 市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择（可能有的技术方案今年适用，明年就不适合了，又需要迭代）

5 垂直分表-垂直分库

5.1 垂直分表

适合: 表字段太多，每个字段访问频次不⼀样，浪费了IO资源，需要进⾏优化

1. 也就是“⼤表拆⼩表”，基于列字段进⾏的。
2. 拆分原则⼀般是表中的字段较多，将不常⽤的或者数据较⼤，⻓度较⻓的拆分到“扩展表 如text类型字段。
3. 访问频次低、字段⼤的商品描述信息单独存放在⼀张表中。
4. 访问频次较⾼的商品基本信息单独放在⼀张表中
   例子:

//拆分前
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 '剩余',

`learn_base` text COMMENT '课前须知，学习基础',
`learn_result` text COMMENT '达到⽔平',
`summary` varchar(1026) DEFAULT NULL COMMENT '概
述',
`detail` text COMMENT '视频商品详情',

//拆分后
//适合列表查询，多频
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;

5.2 垂直分库

适合： 项⽬⾥⾯单个数据库的CPU、内存⻓期处于90%+的利⽤率，数据库连接经常不够，需要进⾏优化

1. 垂直分库针对的是⼀个系统中的不同业务进⾏拆分， 数据库的连接资源⽐较宝贵且单机处理能⼒也有限。
2. 没拆分之前全部都是落到单⼀的库上的，单库处理能⼒成为瓶颈，还有磁盘空间，内存，tps等限制 。
3. 拆分之后，避免不同库竞争同⼀个物理机的CPU、内存、⽹络IO、磁盘，所以在⾼并发场景下，垂直分库⼀定程度上能够突破IO、连接数及单机硬件资源的瓶颈。
4. 垂直分库可以更好解决业务层⾯的耦合，业务清晰，且⽅便管理和维护。
5. ⼀般从单体项⽬升级改造为微服务项⽬，一定程度上来说就是垂直分库（因为业务区分了，每个业务也都是自己独立的库）。

问题： 垂直分库分表可以提⾼并发，但是依然没有解决单表数据量过⼤的问题

6 水平分表-水平分库

6.1 水平分表

适合： 当⼀张表的数据达到⼏千万时，查询⼀次所花的时间⻓，需要进⾏优化，缩短查询时间。

1. 都是⼤表拆⼩表（垂直分表：表结构拆分；⽔平分表：数据拆分）
2. 把⼀个表的数据分到⼀个数据库的多张表中，每个表只有这个表的部分数据。
3. 核⼼是把⼀个⼤表，分割N个⼩表，每个表的结构是⼀样的，数据不⼀样，全部表的数据合起来就是全部数据。
4. 针对数据ᰁ巨⼤的单张表（⽐如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表⾥⾯去。
5. 但是这些表还是在同⼀个库中，所以单数据库操作还是有IO瓶颈，主要是解决单表数据ᰁ过⼤的问题。
6. 减少锁表时间，没分表前，如果是DDL(create/alter/add等)语句，当需要添加⼀列的时候mysql会锁表，期间所有的读写操作只能等待。

6.1 水平分库

适合： ⾼并发的项⽬中，⽔平分表后依旧在单个库上⾯，1个数据库资源瓶颈 CPU/内存/带宽等限制导致响应慢，需要进⾏优化。

1. 把同个表的数据按照⼀定规则分到不同的数据库中，数据库在。
2. ⽔平分库是把不同表拆到不同数据库中，它是对数据⾏的拆分，不影响表结构。
3. 每个库的结构都⼀样,但每个库的数据都不⼀样，没有交集，所有库的并集就是全量数据。
4. ⽔平分库的粒度，⽐⽔平分表更⼤。

7 总结

分库分表是有利，有弊的，在可以单表单库（读写分离）优化完成的情况下一般不会选择封库分表。即时，使用了分库分表，也是在对DB单库单表的优化基础上使用的，所以单库单表优化的能力和还是十分重要的。