关于什么是分区和分表，可以先参考下面前两篇文章。
MySQL 分库分表与分区的区别和思考
搞懂MySQL分区
MySQL分区表的真正作用
分区原理和优缺点
分区是同一表中不同行的记录分配到不同的物理文件中
我们具体是否要使用分区，需要考虑几点
- 什么场景使用分区
- 分区是基于mysql底层的实现，不影响业务。分表可以支持数据量大的表进行分拆，但是需要我们在业务端进行支持
- 我们现在有个表，目前的数据量在2亿+，随着业务的发展，每天新增的数据至少在500W+
- 首先单机的性能和容量足够，完全基于内网的访问，没有太大的读并发，但是需要保证单语句查询的速度
- 每天的新增数据太大，我么先考虑按天来进行分区，分区后的所有区隔离，我们场景也不需要经常的跨天访问
- 分区后性能效果如何，能否解决问题
- 我们首先使用部分数据测试，测试表1900W+数据，加入分区
select *
from table
where s.start_time BETWEEN "2019-10-18 00:00:01" and "2019-10-18 23:59:59"
我们按照天来分区，一天的数据25W左右
-
分区前
image.png
-
分区后
image.png
初步查看，效果明显。
问题？
- 在插入数据的时候，缺少对应的分区会插入失败
- 可以设置maxValue进行容错（也就是可以接收所有数据的分区），避免异常的数据操作
- 分区后如何恢复？
- 分区后的数据分到了不同的物理文件，不同分区数据和索引都是独立的，删除分区就会删除对应的数据。
- 想恢复不分区，可以考虑创建结构相同的表迁移数据
- 分区后的跨区查询速度如何？
接下来 - 新插入分区的值上限，需要比目前的大，所以得考虑后续数据量的增长问题，合理的设置分区值
分区操作语句
查看分区情况
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='stb_traffic_info_lane';
添加分区
ALTER table stb_traffic_info_lane
add partition (partition p20200726 VALUES LESS THAN (737995) ENGINE = InnoDB);
删除分区
alter table stb_traffic_info_lane drop partition p20200726;
创建表，增加分区。数据从现有的表拷贝过来
CREATE TABLE `stb_traffic_info_lane_2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_time` datetime DEFAULT NULL,
`remarks` varchar(12800) DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`,`start_time`),
KEY `index_1` (`index_id`,`time_type`,`start_time`,`end_time`,`cross_id`,`branch_id`,`lane_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(start_time))
PARTITIONS 2(
PARTITION p20191018 VALUES LESS THAN (TO_DAYS('20191018')) ENGINE = INNODB,
PARTITION p20191019 VALUES LESS THAN (TO_DAYS('20191019')) ENGINE = INNODB
)
从其他表迁移数据
insert into `stb_traffic_info_lane_2`
select * from `stb_traffic_info_lane_1`
网友评论