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

互联网行业企业都倾向于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') ENGINE = InnoDB,PARTITION p2018 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,PARTITION p2019 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,PARTITION p2020 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB);

②备份

备份的方式有两类：

a、在线备份

数据一直在数据库中不离线。（最好的方式是把备库拉出来做，做分区。再做主备切换）

insert into t_send_message_send2 (select * from t_send_message_send);

b、离线备份

数据先导出到本地，再从本地导回数据库。这种需要先停机，时间较长

③删原表

drop table t_send_message_send;

注意：删原表前一定要确认数据备份完成，且完整。

后面再做增量同步，保证数据不丢失。

rename table t_send_message_send to t_send_message_send_bak;

④改名

rename table t_send_message_send2 to t_send_message_send;

附：

1、查询表分区情况

select partition_name part,partition_expression expr,partition_description descr,table_rows from information_schema.partitions where table_schema = schema() and table_name='t_send_message_send';

1

2、查询表分区数据

select * from t_send_message_send partition(p2020);

mysql分区表对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。
实现分区的代码实际上是对一组底层表的句柄对象的封装。mysql在创建表时使用PARTITIONBY子句定义每个分区存放的数据。
在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区——只需要查询包含需要数据的分区就可以了。
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中，这样做可以将相关的数据放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。

a），mysql 的分表是真正的分表，一张表分成很多表后，

每一个小表都是完正的一张表，都对应三个文件， 一个.MYD 数据文件，.MYI 索引文件，.frm 表结构文件。

在下面的场景中，分区可以起到非常大的作用：

• 1.表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。

• 2.分区表的数据更容易维护。例如想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。

• 3.分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。

• 4.可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3文件系统的inode锁竞争等。

• 5.如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

分区表本身也有一些限制，下面是其中比较重要的几点：

• 1.一个表最多只能有1024个分区。

• 2.在mysql5.1中，分区表达式必须是整数，或者是返回整数的表达式。在mysql5.5中，某些场景中可以直接使用列进行分区。

• 3.如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。

• 4.分区表中无法使用外键约束。

分区表上的操作按照下面的操作逻辑进行：

select查询

当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。

insert操作

当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。

delete操作

当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。

update操作

当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据在哪个分区，最后对底层进行写入操作，并对原数据所在的底层表进行删除操作。

虽然每个操作都有“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如innoDb，则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似。

---

1. mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，

2. 一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，

3. 在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。

4. 如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去

reate table employees (
id int not null primary key,
first_name varchar(30),
last_name varchar(30))
partition by range(id)(
partition p0 values less than (11),
partition p1 values less than (21),
partition p2 values less than (31),
partition p3 values less than (41)
);
insert into employees values(1,'Vincent','Chen');
insert into employees values(6,'Victor','Chen');
insert into employees values(11,'Grace','Li');
insert into employees values(16,'San','Zhang');

2）explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

查询到这条数据，是从p1里面找到的。

找不到，所以不是从哪个分区找到的。

查询条件不是分区建立的条件，所以走所有分区。

我们增加分区条件id

阿里不建议分区，而是分表：

因此，如果要使用分区表，就不要创建太多分区。我见过一个用户做了按天分区策略，然后预先创建了10年的分区。这种情况下，访问分区表的性能自然是不好的。这里有两个问题：

• 分区并不是越细越好 单表或单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已是小表

• 分区不要提前预留太多，在使用之前预先创建即可 比如，如果是按月分区，每年年底时再把下一年度的12个新分区创建上即可。对于没有数据的历史分区，及时drop

分区表的其他问题，比如查询需要跨多个分区取数据，查询性能就会比较慢，基本上就不是分区表本身的问题，而是数据量或说使用方式问题。 如果你的团队已经维护了成熟的分库分表中间件，用业务分表，对业务开发同学没有额外的复杂性，对DBA也更直观，自然更好。