优缺点分析
优点:
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 |
优点:保证数据较均匀的分散落在不同的库、表中,可以有效的避免热点数据集中问题;
缺点:扩容不是很方便,需要数据迁移 ;