mysql 分区表使用介绍

2019-12-02

加入收藏

一、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()函数的转换，其他的并不支持，

也就是说，在按日期字段分区的时候，如果不是使用上述几个函数转换的，查询优化器将无法对相关查询进行优化。

List分区，分区字段必须是整型或者转换为整型

按照某个字段上的规则，不同的数据离散地分布在不同的区中。

create table test_list_partiotion

(

id int auto_increment,

data_type tinyint,

primary key(id,data_type)

)partition by list(data_type)

(

partition p0 values in (0,1,2,3,4,5,6),

partition p1 values in (7,8,9,10,11,12),

partition p2 values in (13,14,15,16,17)

);

对于List分区，分区字段必须是已知的，如果插入的字段不在分区时枚举值中，将无法插入

Hash分区，分区字段必须是整型或者转换为整型

Hash分区可以将数据均匀地分不到预先定义的分区中，使得各个分区的数据量分布基本上一致。同样，分区字段必须是整型或者转换为整型

drop table test_hash_partiotion;

create table test_hash_partiotion

(

id int auto_increment,

create_date datetime,

primary key(id,create_date)

)partition by hash(year(create_date)) partitions 10;

一个很明显的问题就是，如果分区字段本身的分布不匀均，那么hash分区之后存储的分区也是不均匀的，hash分区时对于hash的字段，需要慎重。

对于单个值的查询hash分区可以定位到某一个分区

hash分区在查询优化方面，无法优化范围查询，因为无法确定一个某个字段经过hash计算之后究竟分布了在哪个分区之中。

Key分区，分区字段必须是整型或者转换为整型

与hash分区不用的是，key分区使用MySQL自定义的库函数进行分区，不需要hash分区那样对字段整型进行转换，同样，分区字段必须是整型或者转换为整型

create table test_key_partiotion

(

id int auto_increment,

create_date datetime,

primary key(id,create_date)

)partition by key(create_date) partitions 10;

对于查询优化，Key分区的特点与Hash分区一致，对于单个字段可以

column 分区

解决了分区字段必须是整型或者必须转换为整型的限制，可以对整型，date或者datetime进行支持。

create table test_column_partiotion

(

id int auto_increment,

data_type datetime,

primary key(id,data_type)

)partition by range columns(data_type) (

partition p0 values less than ('20180101'),

partition p1 values less than ('20180201'),

partition p2 values less than ('20180301'),

partition p3 values less than ('20180401'),

partition p4 values less than ('20180501'),

partition p5 values less than ('20180601'),

partition p6 values less than ('20180701'),

partition p7 values less than ('20180801')

);

四、其他例子

1.基于字段进行分区

CREATE TABLE e (

id INT NOT NULL,

fname VARCHAR(30),

lname VARCHAR(30)

)

PARTITION BY RANGE (id) (

PARTITION p0 VALUES LESS THAN (50),

PARTITION p1 VALUES LESS THAN (100),

PARTITION p2 VALUES LESS THAN (150),

PARTITION p3 VALUES LESS THAN (MAXVALUE)

);

INSERT INTO e VALUES

(1669, "Jim", "Smith"),

(337, "Mary", "Jones"),

(16, "Frank", "White"),

(2005, "Linda", "Black");

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

ALTER TABLE tr DROP PARTITION p2;

2.基于hash进行分区

CREATE TABLE clients (

id INT,

fname VARCHAR(30),

lname VARCHAR(30),

signed DATE

)

PARTITION BY HASH( MONTH(signed) )

PARTITIONS 12;

3.每天增加分区

首先，先多增加几个分区

ALTER TABLE tb_3a_huandan_detail PARTITION BY RANGE (TO_DAYS(ServiceStartTime))

(

PARTITION p20160523 VALUES LESS THAN (TO_DAYS('2016-05-23')),

PARTITION p20160524 VALUES LESS THAN (TO_DAYS('2016-05-24')),

PARTITION p20160525 VALUES LESS THAN (TO_DAYS('2016-05-25')),

PARTITION p20160526 VALUES LESS THAN (TO_DAYS('2016-05-26')),

PARTITION p20160527 VALUES LESS THAN (TO_DAYS('2016-05-27'))

)

其次,分区存储过程

DELIMITER $$

USE `sdk`$$

ROP PROCEDURE IF EXISTS `create_Partition_3Ahuadan`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_Partition_3Ahuadan`()

EGIN

/* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

START TRANSACTION;

/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */

SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS

WHERE table_name='tb_3a_huandan_detail' ORDER BY partition_ordinal_position DESC LIMIT 1;

SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;

/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */

SET @s1=CONCAT('ALTER TABLE tb_3a_huandan_detail ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');

/* 输出查看增加分区语句*/

SELECT @s1;

PREPARE stmt2 FROM @s1;

EXECUTE stmt2;

DEALLOCATE PREPARE stmt2;

/* 取出最小的分区的名称，并删除掉。

注意：删除分区会同时删除分区内的数据，慎重 */

/*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS

where table_name='tb_3a_huandan_detail' order by partition_ordinal_position limit 1;

SET @s=concat('ALTER TABLE tb_3a_huandan_detail DROP PARTITION ',@P0_Name);

PREPARE stmt1 FROM @s;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1; */

/* 提交 */

COMMIT ;

END$$

DELIMITER ;

最后，增加定时事件

DELIMITER ||

CREATE EVENT Partition_3Ahuadan_event

ON SCHEDULE

EVERY 1 day STARTS '2016-05-27 23:59:59'

BEGIN

CALL nres.`create_Partition_3Ahuadan`;

END ||

DELIMITER ;