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

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

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