mysql的表分区

什么是表的分区:

通俗地讲表分区是将一大表,根据条件分割成若干个小表。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 ;

 

已标记关键词 清除标记
【为什么还需要学习C++?】 你是否接触很多语言,但从来没有了解过编程语言的本质? 你是否想成为一名资深开发人员,想开发别人做不了的高性能程序? 你是否经常想要窥探大型企业级开发工程的思路,但苦于没有基础只能望洋兴叹?   那么C++就是你个人能力提升,职业之路进阶的不二之选。 【课程特色】 1.课程共19大章节,239课时内容,涵盖数据结构、函数、类、指针、标准库全部知识体系。 2.带你从知识与思想的层面从0构建C++知识框架,分析大型项目实践思路,为你打下坚实的基础。 3.李宁老师结合4大国外顶级C++著作的精华为大家推出的《征服C++11》课程。 【学完后我将达到什么水平?】 1.对C++的各个知识能够熟练配置、开发、部署; 2.吊打一切关于C++的笔试面试题; 3.面向物联网的“嵌入式”和面向大型化的“分布式”开发,掌握职业钥匙,把握行业先机。 【面向人群】 1.希望一站式快速入门的C++初学者; 2.希望快速学习 C++、掌握编程要义、修炼内功的开发者; 3.有志于挑战更高级的开发项目,成为资深开发的工程师。 【课程设计】 本课程包含3大模块 基础篇 本篇主要讲解c++的基础概念,包含数据类型、运算符等基本语法,数组、指针、字符串等基本词法,循环、函数、类等基本句法等。 进阶篇 本篇主要讲解编程中常用的一些技能,包含类的高级技术、类的继承、编译链接和命名空间等。 提升篇: 本篇可以帮助学员更加高效的进行c++开发,其中包含类型转换、文件操作、异常处理、代码重用等内容。
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:设计师小姐姐 返回首页