分库分表的利弊分析与探究

优缺点分析

优点：

1）解决数据库本身瓶颈

         连接数过多时，就会出现‘too many connections’的错误，访问量太大或者数据库设置的最大连接数太小的原

        Mysql默认的最大连接数为100.可以修改，而mysql服务允许的最大连接数为16384

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

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

2）解决系统本身IO、CPU瓶颈

        磁盘读写IO瓶颈，热点数据太多，尽管使用了数据库本身缓存，但是依旧有大量IO,导致sql执行速度慢

        网络IO瓶颈，请求的数据太多，数据传输大，网络带宽不够，链路响应时间变长

        CPU瓶颈，尤其在基础数据量大单机复杂SQL计算，SQL语句执行占用CPU使用率高，也有扫描行数大、锁冲突、锁等待等原因

 缺点：

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

        数据库切分前，多表关联查询，可以通过sql join进行实现，但分库分表后，数据可能分布在不同的节点上，sql join带来的问题就比较麻烦

2）不同维度查看数据，利用的partitionKey是不一样的

        订单表的partionKey是user_id，用户查看自己的订单列表方便，但商家查看自己店铺的订单列表就麻烦，分布在不同数据节点

3）执行的SQL排序、翻页、函数计算问题

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

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

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

5）容量规划,分库分表后二次扩容问题

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

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

        市场分库分表中间件相对较多，框架各有各的优势与短板，应该如何选择

分库分表模式

垂直分表

含义：也就是“大表拆小表”，基于列字段进行的，拆分原则一般是表中的字段较多，将不常用的或者数据较大，长度较长的拆分到“扩展表 如text类型字段，访问频次低、字段大的商品描述信息单独存放在一张表中;  访问频次较高的商品基本信息单独放在一张表中。例如商品详情一般是拆分主表和附表；

垂直拆分原则：1）把不常用的字段单独放在一张表;2）把text，blob等大字段拆分出来放在附表中;3）业务经常组合查询的列放在一张表中

//拆分前
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 '视频商品详情',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


//拆分后
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;

垂直分库

        垂直分库针对的是一个系统中的不同业务进行拆分， 数据库的连接资源比较宝贵且单机处理能力也有限；没拆分之前全部都是落到单一的库上的，单库处理能力成为瓶颈，还有磁盘空间，内存，tps等限制，拆分之后，避免不同库竞争同一个物理机的CPU、内存、网络IO、磁盘，所以在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。垂直分库可以更好解决业务层面的耦合，业务清晰，且方便管理和维护，一般从单体项目升级改造为微服务项目，就是垂直分库。

 C端项目里面，单个数据库的CPU、内存长期处于90%+的利用率，数据库连接经常不够该场景下可采用该垂直分库方式；

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

 水平分表

        把一个表的数据分到一个数据库的多张表中，每个表只有这个表的部分数据，核心是把一个大表，分割N个小表，每个表的结构是一样的，数据不一样，全部表的数据合起来就是全部数据，针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去，但是这些表还是在同一个库中，所以单数据库操作还是有IO瓶颈，主要是解决单表数据量过大的问题。减少锁表时间，没分表前，如果是DDL(create/alter/add等)语句，当需要添加一列的时候mysql会锁表，期间所有的读写操作只能等待。

当一张表的数据达到几千万时，查询一次所花的时间长，需要进行优化，缩短查询时间

水平分库

        把同个表的数据按照一定规则分到不同的数据库中，数据库在不同的服务器上，水平分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构，每个库的结构都一样,但每个库的数据都不一样，没有交集，所有库的并集就是全量数据，水平分库的粒度，比水平分表更大。

水平分库分表策略

自增id

        根据ID范围进行分表（左闭右开）

      例如：规则案例

            1~1,000,000 是 table_1
            1,000,000 ~2,000,000  是 table_2
            2,000,000~3,000,000  是 table_3
            ...更多

        优点：1）id是自增长，可以无限增长 ；2）扩容不用迁移数据，容易理解和维护；

        缺点：1）大部分读和写都访会问新的数据，有IO瓶颈，整体资源利用率低；2）数据倾斜严重，热点数据过于集中，部分节点有瓶颈；

      根据自增id延申解决方案：

        1）数字：自增id范围，应用场景：微博发送记录、微信消息记录、日志记录，id增长/时间分区都行。

        2）时间：年、月、日范围。比如按照月份生成 库或表 pay_log_2022_01、pay_log_2022_02，应用场景：网站签到等活动流水数据时间分区最好。

        3）空间：地理位置：省份、区域（华东、华北、华南），比如按照 省份 生成 库或表，应用场景：大区划分（一二线城市和五六线城市活跃度不一样，如果能避免热点问题，即可选择）。

Hash取模

        例如：用户ID是整数型的，要分2库，每个库表数量4表，一共8张表，用户ID取模后，值是0到7的要平均分配到每张表

        库ID = userId % 库数量 2 
        表ID = userId / 库数量 2 % 表数量4

userId	id % 2 (库-取余)	id /2 % 4 (表)
1	1	0
2	0	1
3	1	1
4	0	2
5	1	2
6	0	3
7	1	3
8	0	0
9	1	0

        优点：保证数据较均匀的分散落在不同的库、表中，可以有效的避免热点数据集中问题；
        缺点：扩容不是很方便，需要数据迁移 ；