Mysql分区分析

关于什么是分区和分表，可以先参考下面前两篇文章。

MySQL 分库分表与分区的区别和思考
搞懂MySQL分区
MySQL分区表的真正作用
分区原理和优缺点

分区是同一表中不同行的记录分配到不同的物理文件中

---

我们具体是否要使用分区，需要考虑几点

1. 什么场景使用分区

• 分区是基于mysql底层的实现，不影响业务。分表可以支持数据量大的表进行分拆，但是需要我们在业务端进行支持
• 我们现在有个表，目前的数据量在2亿+，随着业务的发展，每天新增的数据至少在500W+
• 首先单机的性能和容量足够，完全基于内网的访问，没有太大的读并发，但是需要保证单语句查询的速度
• 每天的新增数据太大，我么先考虑按天来进行分区，分区后的所有区隔离，我们场景也不需要经常的跨天访问

2. 分区后性能效果如何，能否解决问题

• 我们首先使用部分数据测试，测试表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

初步查看，效果明显。

---

问题？

1. 在插入数据的时候，缺少对应的分区会插入失败

• 可以设置maxValue进行容错（也就是可以接收所有数据的分区），避免异常的数据操作

2. 分区后如何恢复？

• 分区后的数据分到了不同的物理文件，不同分区数据和索引都是独立的，删除分区就会删除对应的数据。
• 想恢复不分区，可以考虑创建结构相同的表迁移数据

3. 分区后的跨区查询速度如何？
   接下来
4. 新插入分区的值上限，需要比目前的大，所以得考虑后续数据量的增长问题，合理的设置分区值

---

分区操作语句

查看分区情况

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`