mysql 分区表使用介绍 _mysql

文章插图

一、MySQL分区表
分区的作用是将一个表的数据按照某种方式，比如按照时间上的月份，分成多个较小的，更容易管理的部分，但是逻辑上仍是一个表。

二、MySQL分区表对分区字段的限制
分区的字段，必须是表上所有的唯一索引（或者主键索引）包含的字段的子集

MySQL是局部分区，意思是一个分区中，包含分区的数据和其对应的索引，而不是索引是一个索引统一存放在一个地方，仅分区数据这种方式。
想一下，为什么MySQL的分区表会有这个么一个奇怪的要求：一个表上有一个或者多个唯一索引的情况下，分区的字段必须被包含在所有的主键或者唯一索引字段中？

三、分区类型
range分区,分区字段必须是整型或者转换为整型
按照字段的区间划分数据的归属，典型的就是按照时间维度的月份分区
CREATE TABLE test_range_partition(
id INT auto_increment,
createdate DATETIME,
primary key (id,createdate)
)
PARTITION BY RANGE (TO_DAYS(createdate) ) (
PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
);

insert into test_range_partition (createdate) values ('20180105');
insert into test_range_partition (createdate) values ('20180205');
insert into test_range_partition (createdate) values ('20180206');
insert into test_range_partition (createdate) values ('20180305');
insert into test_range_partition (createdate) values ('20180405');
insert into test_range_partition (createdate) values ('20180505');
insert into test_range_partition (createdate) values ('20180605');
insert into test_range_partition (createdate) values ('20180705');
insert into test_range_partition (createdate) values ('20180805');
insert into test_range_partition (createdate) values ('20180905');
insert into test_range_partition (createdate) values ('20181005');
insert into test_range_partition (createdate) values ('20181105');

select
table_schema,
table_name,
partition_name,
partition_ordinal_position,
partition_method,
partition_expression,
table_rows
from information_schema.`PARTITIONS` where table_schema = 'db01' and table_name = 'test_range_partition';

对应的物理文件

文章插图

查看每个分区的信息

文章插图

分区在查询中的优化体现
并不是说一个表只要分区了，对于任何查询都会实现查询优化，只有查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表
在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可

文章插图

增加与删除分区
ALTER TABLE test ADD PARTITION (PARTITION p201902 VALUES LESS THAN ( TO_DAYS('20190301') ));
ALTER TABLE test DROP PARTITION p20180201;

对于range分区，分区字段必须是整型或者转换为整型，如果分区字段是日期类型的字段，那么就必须将日期类型的字段转换成整型类型
对于日期类型的转换，优化器只支持year() ， to_days ， to_seconds ， unix_timestamp()函数的转换，其他的并不支持，