【数据库系列】MQSQL历史数据分区

人工智

已于 2023-03-05 20:30:48 修改

阅读量841

点赞数 1

分类专栏：日常编程记录数据库文章标签： mysql 数据库 java Powered by 金山文档

于 2023-03-05 20:21:24 首次发布

本文链接：https://blog.csdn.net/qq_35789269/article/details/129350001

版权

文章讨论了MySQL数据库在面对亿级别数据时，如何通过分库分表和Range分区策略提升性能和效率。Range分区是根据特定字段（如时间或ID）将数据分散到不同分区，减少查询时间。文章还提到了分区表的优缺点，以及操作如INSERT、DELETE和UPDATE在分区表上的处理逻辑。此外，建议不要过度分区，并给出了阿里的分表策略。

摘要由CSDN通过智能技术生成

互联网行业企业都倾向于mysql数据库，虽说mysql单表能支持亿级别的数据量，加上索引优化下查询速度，勉强能使用，但是对于追求性能和效率的互联网企业，这是远远不够的。Mysql数据库单表数据量到达500万左右，达到性能最佳点，可是对于需要亿级别的业务来说，500万是远远不够的。既然数据放在一个位置不行，那我们就把数据拆分放到多个位置。如果寻找数据位置的时间成本忽略不计的话，那么在亿级别的数据量里面查询数据的时间成本就相当于从单张表力查询数据的时间成本一样。这就是分库分表的最初思想。

对表进行分区，是为了能最大限度的提高数据库的IO能力，分区能让数据库将同一张表中的数据放在不同的磁盘下，提高数据库IO能力，类似多核多线程的思想。因此分区能提高单标的高并发能力。

range分区

range方式创建分区语句如下：

#根据表结构中的时间字段来作为分区键，如下的year()方法，或者to_char()方法
 
create table table_range(
 
　　   id int(11),
 
　　   amt int(11) unsigned not null,
 
　　   created_on datetime
 
　　)partition by range(year(created_on))(
 
　　partition p2018 values less than (2018),
 
　　partition p2019 values less than (2019),
 
　　partition p2020 values less than (2020)
 
　　partition pdefault values less than maxvalue  #MAXVALUE 表示最大的可能的整数值
 
　　)；
 
#或者使用id作为范围分区
 
create table table_range(
 
　　   id int(11),
 
　　   amt int(11) unsigned not null,
 
　　   created_on datetime
 
　　)partition by range(id)(
 
　　partition p10000 values less than (10000),
 
　　partition p20000 values less than (20000),
 
　　partition p30000 values less than (30000)
 
　　partition pdefault values less than maxvalue  #MAXVALUE 表示最大的可能的整数值
 
　　)；

范围分区

所有范围区间不能重叠。

查询条件里包括分区键，免全表扫描，分区表查询都应该注意这个。

分区键一般是时间或是唯一的索引值，一般都会在每条数据上计算并保存分区字段。

实例

假如我们现在有一张大表需要做分区：

过程应该如下建新表–>备份–>停机-》删原表–>改名〉恢复

CREATE TABLE `t_send_message_send2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `plan_id` bigint(20) DEFAULT NULL,
  `job_uuid` varchar(36) DEFAULT NULL,
  `send_port` varchar(16) DEFAULT NULL,
  `mobile` varchar(16) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  `product_code` varchar(16) DEFAULT 'HELP',
  `fake` bit(1) DEFAULT b'0',
  `date_push` datetime NOT NULL,
  `activity_id` bigint(20) DEFAULT '0',
  PRIMARY KEY (`id`,`date_push`),
  KEY `mobile` (`mobile`),
  KEY `date_push` (`date_push`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(date_push)
(PARTITION p2016 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN ('2018-01-01'