背景:由于业务量的递增以及客户并发请求的递增,某些数据的返回需经大表(3000W以上)查询返回,返回数据时间由能接受到了完全不能接受的地步,需要对数据库进行相应优化
目前架构:A-B-C 相当于单主下边挂着单从库
方案一:
基于成本足够的情况下,数据库切分,可以考虑PXC(三节点最优)+mycat架构
优点:
缺点:
方案二:
成本不够的话,在原有的基础上进行表的分区,eg:业务基于时间段返回数据,可以通过range分区基于复合主键对时间段进行表的分区
优点:将一个大表分为多个小文件进行存储,存储位置可以指定放置于不同的磁盘上,分担磁盘IO压力
缺点:锁的粒度比较大
表分区类型:
1>range表分区:适用于查询条件中以时间为区分度的需求。比如:2019~2021年的表单数据,以时间字段作为主键复合索引分区为四个区 2019以前的,2019~2020年,2020~2021年,2021年以后的
2>list表分区:适用于查询条件包含某些确定列表中的数据。 eg: 300个公司报表数据,分为10个表存储,以公司ID做为主键复合索引
3>hash表分区:可以对某个字段hash取模后,将数据均匀分配
4>key表分区:根据mysql内部提供函数进行分区
基于时间字段以range方式对A表进行表分区:
CREATE TABLE `A_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`role` varchar(20) DEFAULT NULL COMMENT '岗位编号',
`createtime` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`,`createtime`),
KEY `index1` (`createtime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='A统计表'
PARTITION by range (YEAR(createtime))(
PARTITION p2018 VALUES less than (2019),
PARTITION p2019 VALUES less than (2020),
PARTITION p2020 VALUES less than (2021),
PARTITION p2021 VALUES less than (2022),
);
方案三:
基于现有业务可以接受的情况,引入数仓(hadoop大数据)离线计算,通过接口调用进行一个离线数据的返回
1、kylin 需要预先建模
2、presto 分布式,基于离线计算亚秒级返回
待完善
PRIMARY KEY (`id`,`createtime`),