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

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单库单表的优化基础上使用的，所以单库单表优化的能力和还是十分重要的。

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖