mysql的表分区

怪只怪满眼尽是人间烟火 2020-07-16 20:59:45

189

分类专栏：后端大数据数据库

最后发布:2020-07-16 20:59:45首次发布:2020-07-16 20:59:45

本文链接：https://blog.csdn.net/weixin_38959210/article/details/107384271

版权

什么是表的分区：

通俗地讲表分区是将一大表，根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。

ANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

1.增加分区

1.1RANGE

范围分区 range (id) 中的id为分区键，less than是小于的意思。

alter table `cdb_test` partition by range (id) 
(
	partition `p0` values less than (2),
        partition `p1` values less than (4),
        partition `p2` values less than (maxvalue)
)

时间分区 range(TO_DAYS(ts))中的ts为分区键，less than是小于的意思。

alter table jyz_jyzqgdwa partition by range(TO_DAYS(rq))
(
    partition p0 values less than(TO_DAYS('2019-12-01')),
    partition p1 values less than(TO_DAYS('2020-02-01')),
    partition p2 values less than(TO_DAYS('2020-04-01')),
    partition p3 values less than(TO_DAYS('2020-06-01')),
    partition p4 values less than(TO_DAYS('2020-08-01')),
    partition p5 values less than(TO_DAYS('2020-10-01')),
    partition p6 values less than(TO_DAYS('2020-12-01')),
    partition p7 values less than(TO_DAYS('2021-02-01')),
    partition p8 values less than(TO_DAYS('2021-04-01')),
    partition p9 values less than(TO_DAYS('2021-06-01')),
    partition p10 values less than(TO_DAYS('2021-08-01')),
    partition p11 values less than(TO_DAYS('2021-10-01')),
    partition p12 values less than(TO_DAYS('2021-12-01')),
    partition p13 values less than(TO_DAYS('2022-02-01')),
    partition p14 values less than maxvalue
	)

 partition by range(TO_DAYS(rq))
(

    partition p0 values less than(TO_DAYS('20190101')),
    partition p1 values less than(TO_DAYS('20190201')),
    partition p2 values less than(TO_DAYS('20190301')),
    partition p3 values less than(TO_DAYS('20190401')),
    partition p4 values less than(TO_DAYS('20190501')),
    partition p5 values less than(TO_DAYS('20190601')),
    partition p6 values less than(TO_DAYS('20190701')),
    partition p7 values less than(TO_DAYS('20190801')),
    partition p8 values less than(TO_DAYS('20190901')),
    partition p9 values less than(TO_DAYS('20191001')),
    partition p10 values less than(TO_DAYS('20191101')),
    partition p11 values less than(TO_DAYS('20191201')),

    partition p12 values less than(TO_DAYS('20200101')),
    partition p13 values less than(TO_DAYS('20200201')),
    partition p14 values less than(TO_DAYS('20200301')),
    partition p15 values less than(TO_DAYS('20200401')),
    partition p16 values less than(TO_DAYS('20200501')),
    partition p17 values less than(TO_DAYS('20200601')),
    partition p18 values less than(TO_DAYS('20200701')),
    partition p19 values less than(TO_DAYS('20200801')),
    partition p20 values less than(TO_DAYS('20200901')),
    partition p21 values less than(TO_DAYS('20201001')),
    partition p22 values less than(TO_DAYS('20201101')),
    partition p23 values less than(TO_DAYS('20201201')),

    partition p24 values less than(TO_DAYS('20220101')),
    partition p25 values less than(TO_DAYS('20220201')),
    partition p26 values less than(TO_DAYS('20220301')),
    partition p27 values less than(TO_DAYS('20220401')),
    partition p28 values less than(TO_DAYS('20220501')),
    partition p29 values less than(TO_DAYS('20220601')),
    partition p30 values less than(TO_DAYS('20220701')),
    partition p31 values less than(TO_DAYS('20220801')),
    partition p32 values less than(TO_DAYS('20220901')),
    partition p33 values less than(TO_DAYS('20221001')),
    partition p34 values less than(TO_DAYS('20221101')),
    partition p35 values less than(TO_DAYS('20221201')),
	
	partition p36 values less than(TO_DAYS('20230101')),
    partition p37 values less than(TO_DAYS('20230201')),
    partition p38 values less than(TO_DAYS('20230301')),
    partition p39 values less than(TO_DAYS('20230401')),
    partition p40 values less than(TO_DAYS('20230501')),
    partition p41 values less than(TO_DAYS('20230601')),
    partition p42 values less than(TO_DAYS('20230701')),
    partition p43 values less than(TO_DAYS('20230801')),
    partition p44 values less than(TO_DAYS('20230901')),
    partition p45 values less than(TO_DAYS('20231001')),
    partition p46 values less than(TO_DAYS('20231101')),
    partition p47 values less than(TO_DAYS('20231201')),
	
	partition p48 values less than(TO_DAYS('20240101')),
    partition p49 values less than(TO_DAYS('20240201')),
    partition p50 values less than(TO_DAYS('20240301')),
    partition p51 values less than(TO_DAYS('20240401')),
    partition p52 values less than(TO_DAYS('20240501')),
    partition p53 values less than(TO_DAYS('20240601')),
    partition p54 values less than(TO_DAYS('20240701')),
    partition p55 values less than(TO_DAYS('20240801')),
    partition p56 values less than(TO_DAYS('20240901')),
    partition p57 values less than(TO_DAYS('20241001')),
    partition p58 values less than(TO_DAYS('20241101')),
    partition p59 values less than(TO_DAYS('20241201')),
    partition p60 values less than maxvalue
	)

PARTITION BY RANGE COLUMNS (rq)
(PARTITION p0 VALUES LESS THAN ('2019-01-01'),
 PARTITION p1 VALUES LESS THAN ('2019-02-01'),
 PARTITION p2 VALUES LESS THAN ('2019-03-01'),
 PARTITION p3 VALUES LESS THAN ('2019-04-01'),
 PARTITION p4 VALUES LESS THAN ('2019-05-01'),
 PARTITION p5 VALUES LESS THAN ('2019-06-01'),
 PARTITION p6 VALUES LESS THAN ('2019-07-01'),
 PARTITION p7 VALUES LESS THAN ('2019-08-01'),
 PARTITION p8 VALUES LESS THAN ('2019-09-01'),
 PARTITION p9 VALUES LESS THAN ('2019-10-01'),
 PARTITION p10 VALUES LESS THAN ('2019-11-01'),
 PARTITION p11 VALUES LESS THAN ('2019-12-01'),
 PARTITION p12 VALUES LESS THAN ('2020-01-01'),
 PARTITION p13 VALUES LESS THAN ('2020-02-01'),
 PARTITION p14 VALUES LESS THAN ('2020-03-01'),
 PARTITION p15 VALUES LESS THAN ('2020-04-01'),
 PARTITION p16 VALUES LESS THAN ('2020-05-01'),
 PARTITION p17 VALUES LESS THAN ('2020-06-01'),
 PARTITION p18 VALUES LESS THAN ('2020-07-01'),
 PARTITION p19 VALUES LESS THAN ('2020-08-01'),
 PARTITION p20 VALUES LESS THAN ('2020-09-01'),
 PARTITION p21 VALUES LESS THAN ('2020-10-01'),
 PARTITION p22 VALUES LESS THAN ('2020-11-01'),
 PARTITION p23 VALUES LESS THAN ('2020-12-01'),
 PARTITION p24 VALUES LESS THAN ('2021-01-01'),
 PARTITION p25 VALUES LESS THAN ('2021-02-01'),
 PARTITION p26 VALUES LESS THAN ('2021-03-01'),
 PARTITION p27 VALUES LESS THAN ('2021-04-01'),
 PARTITION p28 VALUES LESS THAN ('2021-05-01'),
 PARTITION p29 VALUES LESS THAN ('2021-06-01'),
 PARTITION p30 VALUES LESS THAN ('2021-07-01'),
 PARTITION p31 VALUES LESS THAN ('2021-08-01'),
 PARTITION p32 VALUES LESS THAN ('2021-09-01'),
 PARTITION p33 VALUES LESS THAN ('2021-10-01'),
 PARTITION p34 VALUES LESS THAN ('2021-11-01'),
 PARTITION p35 VALUES LESS THAN ('2021-12-01'),
 PARTITION p36 VALUES LESS THAN ('2022-01-01'),
 PARTITION p37 VALUES LESS THAN ('2022-02-01'),
 PARTITION p38 VALUES LESS THAN ('2022-03-01'),
 PARTITION p39 VALUES LESS THAN ('2022-04-01'),
 PARTITION p40 VALUES LESS THAN ('2022-05-01'),
 PARTITION p41 VALUES LESS THAN ('2022-06-01'),
 PARTITION p42 VALUES LESS THAN ('2022-07-01'),
 PARTITION p43 VALUES LESS THAN ('2022-08-01'),
 PARTITION p44 VALUES LESS THAN ('2022-09-01'),
 PARTITION p45 VALUES LESS THAN ('2022-10-01'),
 PARTITION p46 VALUES LESS THAN ('2022-11-01'),
 PARTITION p47 VALUES LESS THAN ('2022-12-01'),
 PARTITION p48 VALUES LESS THAN ('2023-01-01'),
 PARTITION p49 VALUES LESS THAN ('2023-02-01'),
 PARTITION p50 VALUES LESS THAN ('2023-03-01'),
 PARTITION p51 VALUES LESS THAN ('2023-04-01'),
 PARTITION p52 VALUES LESS THAN ('2023-05-01'),
 PARTITION p53 VALUES LESS THAN ('2023-06-01'),
 PARTITION p54 VALUES LESS THAN ('2023-07-01'),
 PARTITION p55 VALUES LESS THAN ('2023-08-01'),
 PARTITION p56 VALUES LESS THAN ('2023-09-01'),
 PARTITION p57 VALUES LESS THAN ('2023-10-01'),
 PARTITION p58 VALUES LESS THAN ('2023-11-01'),
 PARTITION p59 VALUES LESS THAN ('2023-12-01'),
 PARTITION p60 VALUES LESS THAN MAXVALUE);

1.2List

list(id)中的id为分区键，p0为id为3和5的精确分区。

alter table `cdb_test` partition by list(id) 
(
	     partition `p0` values in (3,5),
            partition `p1` values in (6,7,8,10,15),
      
)

1.3hash

hash(id)中的id为分区键，partitions为分区的数量；HASH 分区只支持数字分区,如果分区键为varchar可使用key分区

alter table `cdb_test` partition by hash(id)
(

    partitions 10; 
	)

1.4key

同hash分区，但还是有所区别。区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

alter table `cdb_test` partition by LINEAR KEY (id)
(

    partitions 10; 
	)

2.查看分区

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='record1';  --record1查询的表名

3.删除分区

ALTER TABLE  `cdb_test` DROP PARTITION s20100406 ;

点赞 1
评论
分享
x
海报分享

扫一扫，分享海报
收藏
打赏

打赏

怪只怪满眼尽是人间烟火

你的鼓励将是我创作的最大动力

C币余额

2C币 4C币 6C币 10C币 20C币 50C币

确定
举报
关注关注
一键三连
点赞Mark关注该博主, 随时了解TA的最新博文

已标记关键词清除标记

插入表情

还能输入1000个字符 “速评一下”