mysql基础（四）表分区

目录1.range(范围分区)2.list列表分区3.hash4.key分区表分区就是把一张表分成若干小表管理起来更方便。MySQL 主要的分区策略包括 RANGE、LIST、HASH、KEYLINEAR HASH / LINEAR KEY 属于线性变体另外还有 RANGE COLUMNS、LIST COLUMNS以及 RANGE/LIST 基础上的子分区。1.range(范围分区)建立表的同时按区域类型range分区以字段age做为分区键共三个分区年龄范围20以内的为年轻年龄40以内为中年最大年龄以内为老年。输入语句执行CREATETABLErg(idINT,ageINT)PARTITIONBYRANGE(age)(PARTITIONmiddleVALUESLESS THAN(40),PARTITIONyoungVALUESLESS THAN(20),PARTITIONOLDVALUESLESS THAN maxvalue)结果报错根据错误提示必须要根据取值范围递增也就是要按顺序取值。上面的语句是先取40以内再取20以内就是这两句:PARTITIONmiddleVALUESLESS THAN(40),PARTITIONyoungVALUESLESS THAN(20),顺序改过来再执行CREATETABLErg(idINT,ageINT)PARTITIONBYRANGE(age)(PARTITIONyoungVALUESLESS THAN(20),PARTITIONmiddleVALUESLESS THAN(40),PARTITIONOLDVALUESLESS THAN maxvalue)执行成功查看分区SELECT*FROMinformation_schema.PARTITIONSWHEREtable_namerg结果如下图貌似系统自动根据分区名排序了可以加上ORDER BY partition_ordinal_position按分区顺序排序SELECT*FROMinformation_schema.PARTITIONSWHEREtable_namergORDERBYpartition_ordinal_position结果如图三个分区一目了然。分别查看指定分区SELECT*FROMrgPARTITION(young)SELECT*FROMrgPARTITION(middle)SELECT*FROMrgPARTITION(OLD)结果分别如下图删除old分区ALTERTABLErgDROPPARTITIONOLD再次查看表分区SELECT*FROMinformation_schema.PARTITIONSWHEREtable_namergORDERBYpartition_ordinal_position查询结果可知删除成功添加分区ALTERTABLErgADDPARTITION(PARTITIONlaonianVALUESLESS THAN(60))再次查看表的分区信息SELECT*FROMinformation_schema.PARTITIONSWHEREtable_namergORDERBYpartition_ordinal_position添加成功。删除young分区中的数据不是删除分区ALTERTABLErgTRUNCATEPARTITIONyoung查看对young区进行拆分ALTERTABLErg REORGANIZEPARTITIONyoungINTO(PARTITIONs1VALUESLESS THAN(10),PARTITIONs2VALUESLESS THAN(20))执行结果对middle和laonian两个分区进行合并ALTERTABLErg REORGANIZEPARTITIONmiddle,laonianINTO(PARTITIONadultVALUESLESS THAN(60))输入查询分区语句SELECT*FROMinformation_schema.PARTITIONSWHEREtable_namergORDERBYpartition_ordinal_position结果range中less than(x)是取值范围所以括号中的数字x不能为空否则会报错。range还支持日期型的字段这里不做演示。2.list列表分区list和range极其类似不同的是list是从枚举列表中取值而range是从连续区间集合中取值。下面创建一张表以YEAR(days)为分区键进行list分区将日期转换成年后分为单年、双年和未知三个分区。CREATETABLElt(idINT,daysDATE)PARTITIONBYLIST(YEAR(days))(PARTITIONdannianVALUESIN(2011,2013,2015,2017,2019),PARTITIONshuangnianVALUESIN(2012,2014,2016,2018,2020),PARTITIONweizhiVALUESIN(NULL))输入查询分区语句SELECT*FROMinformation_schema.PARTITIONSWHEREtable_nameltORDERBYpartition_ordinal_position结果如图可以看出List分区是没有顺序的不像range分区从上往下按顺序递增。插入数据INSERTINTOltVALUES(1,2011-11-8),(2,NULL),(3,2018-10-9),(4,2016-01-05),(7,2015-03-15),(8,2020-01-03)查看分区数据SELECT*FROMltPARTITION(dannian)SELECT*FROMltPARTITION(shuangnian)SELECT*FROMltPARTITION(weizhi)依次执行结果如下list字段值不能在所有分区枚举值之外例如执行如下语句INSERTINTOltVALUES(9,2021-1-3)执行结果报错提示表中没有值为2021的分区因为枚举值是2011-2020,还有空值Null不包含2021所以报错。这一点要注意。分区字段的数据类型MySQL 的分区语法决定了字段类型的选择主要分为两种情况使用 RANGE 或 LIST 分区时分区表达式必须产生整数INTEGER或 NULL 值。如果要对日期分区通常需要借助 YEAR()、TO_DAYS() 等函数将日期转换为整数。使用 RANGE COLUMNS 或 LIST COLUMNS 分区时MySQL 5.5 支持可以直接使用非整数类型的列作为分区键。支持的类型主要包括所有整数类型DATE 和 DATETIME 类型部分字符串类型CHAR、VARCHAR、BINARY、VARBINARY注意DECIMAL 或 FLOAT 等浮点数类型不支持作为 COLUMNS 分区的列。3.hashHASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中必须明确指定一个给定的列值或列值集合应该保存在哪个分区中而在HASH分区中MySQL 自动完成这些工作你所要做的只是基于将要被哈希的列值指定一个列值或表达式以及指定被分区的表将要被分割成的分区数量。创建过程如下CREATETABLEhs(idINT,daysDATE)PARTITIONBYHASH(YEAR(days))PARTITIONS4;创建分区时不用指定具体取值只要指定分区数量就行如果不指定默认值分区数量为1。查询分区SELECT*FROMinformation_schema.PARTITIONSWHEREtable_namehsORDERBYpartition_ordinal_position结果是按指定分区数4分区的。插入数据INSERTINTOhsVALUES(1,2011-11-8),(2,NULL),(3,2018-10-9),(4,2016-01-05),(7,2015-03-15),(8,2020-01-03)依次查询4个分区SELECT*FROMhsPARTITION(p0)SELECT*FROMhsPARTITION(p1)SELECT*FROMhsPARTITION(p2)SELECT*FROMhsPARTITION(p3)hash的分区原理是mod函数对分区键对应的字段或表达式值与分区数量的求余运算即mod(分区键对应列值表达式值分区数量)也就是modyear(days),4。可以拿分在p0区的days2016-01-05’测试执行如下语句SELECTMOD(YEAR(2016-01-15),4)注因为YEAR(‘2016-01-15’)2016所以MOD(YEAR(‘2016-01-15’),4)语句等同于mod(2016,4)。结果再拿分在p2区的2018-10-9进行测试SELECTMOD(YEAR(2018-10-9),4)返回结果null值取余运算也是null当成0分配在p0区也是理所当然了。尝试删除分区ALTERTABLEhsDROPPARTITIONp3结果报错删除分区只能在range和list分区使用。以上是常规哈希还有线性哈希分区。这是官方文档说明MySQL还支持线性哈希功能它与常规哈希的区别在于线性哈希功能使用的一个线性的2的幂powers-of-two运算法则而常规 哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于在“PARTITION BY” 子句中添加“LINEAR”关键字如下所示CREATETABLEline(idINT,daysDATE)PARTITIONBYLINEARHASH(YEAR(days))PARTITIONS4;执行后查看SELECT*FROMinformation_schema.PARTITIONSWHEREtable_nameline插入数据INSERTINTOlineVALUES(1,2011-11-8),(2,NULL),(3,2018-10-9),(4,2016-01-05),(7,2015-03-15),(8,2020-01-03)依次查询SELECT*FROMlinePARTITION(p0)SELECT*FROMlinePARTITION(p1)SELECT*FROMlinePARTITION(p2)SELECT*FROMlinePARTITION(p3)结果分别如下线性哈希算法找到下一个大于num.的、2的幂我们把这个值称为V 它可以通过下面的公式得到V POWER(2, CEILING(LOG(2, num)))例如假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4则V POWER(2,4), 即等于16。设置 N F(column_list) (V - 1).当 N num:· 设置 V CEIL(V / 2)· 设置 N N (V - 1)注num是分区数量拿p0中的days2016-01-05进行测试执行SELECTPOWER(2,CEILING(LOG(2,4)))求得V4NF(column_list) (V - 1)year(2015-03-15) (4-1)2016 3执行SELECT20163返回结果0再拿p3区的2015进行测试直接执行SELECT20153结果为3以上几张分区表都没有主键或者唯一约束不妨建一张测试效果CREATETABLENEW(idINTPRIMARYKEY,daysDATE)PARTITIONBYLINEARHASH(YEAR(days))PARTITIONS4;结果报错分区改成常规hashCREATETABLENEW(idINTPRIMARYKEY,daysDATE)PARTITIONBYHASH(YEAR(days))PARTITIONS4;还是报同样的错再试试range分区CREATETABLENEW(idINTPRIMARYKEY,daysDATE)PARTITIONBYRANGE(YEAR(days))(PARTITIONp1VALUESLESS THAN(2015),PARTITIONp2VALUESLESS THAN(2010))依然报错A PRIMARY KEY must include all columns in the table’s partitioning function难道是主键的问题再试试List分区把主键约束改成唯一约束CREATETABLENEW(idINTUNIQUE,daysDATE)PARTITIONBYLIST(YEAR(days))(PARTITIONp1VALUESIN(2015),PARTITIONp2VALUESIN(2010))还是报错这是为什么呢根据报错信息A PRIMARY KEY must include all columns in the table’s partitioning主键必须包括表的分区函数中的所有列。A UNIQUE INDEX must include all columns in the table’s partitioning function惟一的索引必须包括表的分区函数中的所有列。接下来分别以主键和唯一约束字段做为分区键CREATETABLENEW(idINTPRIMARYKEY,daysDATE)PARTITIONBYLIST(id)(PARTITIONp1VALUESIN(2,4),PARTITIONp2VALUESIN(1,3))CREATETABLEnew1(idINTUNIQUE,daysDATE)PARTITIONBYLIST(id)(PARTITIONp1VALUESIN(2,4),PARTITIONp2VALUESIN(1,3))两张表都创建成功原来在表中有主键约束的时候必须以主键字段为分区键有唯一约束的时候同样以唯一约束字段做为分区键。那么假设一张表中主键约束和唯一约束同时存在如何分区呢继续测试先以主键约束字段为分区键CREATETABLEnew2(idINTPRIMARYKEY,cidINTUNIQUE,daysDATE)PARTITIONBYLIST(id)(PARTITIONp1VALUESIN(2,4),PARTITIONp2VALUESIN(1,3))执行结果那么试下用唯一约束字段做为分区键CREATETABLEnew2(idINTPRIMARYKEY,cidINTUNIQUE,daysDATE)PARTITIONBYLIST(cid)(PARTITIONp1VALUESIN(2,4),PARTITIONp2VALUESIN(1,3))执行结果可以这样讲分区表达式中使用到的所有列必须包含在表的每一个 UNIQUE KEY 中PRIMARY KEY 本身也是一种 UNIQUE KEY所以同样必须包含这些分区列。如下例CREATETABLEnew2(idINT,cidINT,daysDATE,PRIMARYKEY(id),UNIQUE(id,cid))PARTITIONBYLIST(id)(PARTITIONp1VALUESIN(2,4),PARTITIONp2VALUESIN(1,3))分区键id既是主键又属于唯一约束中的一个字段可以说它能代表两者执行成功4.key分区与hash类似区别在于key可以不用指定分区键在表中主键和唯一键同时存在的情况下会自动选用兼具两种约束的字段也就是在前面所说的代表做为分区键如下图id是主键也是唯一键CREATETABLEnew4(idINT,cidINTNOTNULL,daysDATE,PRIMARYKEY(id),UNIQUE(cid,id))PARTITIONBYKEY()PARTITIONS4;上述情况只有唯一键是复合键如果主键和唯一键都是复合键并且里面字段多的情况下不手动指定分区键容易报错。表中只存在主键情况下会自动选择主键做为分区键CREATETABLEnew5(idINT,cidINTNOTNULL,daysDATE,PRIMARYKEY(id))PARTITIONBYKEY()PARTITIONS4;没有主键情况下选择唯一键做为分区键CREATETABLEnew6(idINT,cidINTNOTNULL,daysDATE,UNIQUE(cid))PARTITIONBYKEY()PARTITIONS4;但是唯一键必须是非空不然报错如下所示CREATETABLEnew7(idINT,cidINT,daysDATE,UNIQUE(cid))PARTITIONBYKEY()PARTITIONS4;只是少了个not null就建表失败这种情况下要么在唯一键字段加上not null要么手动指定分区键如下CREATETABLEnew7(idINT,cidINT,daysDATE,UNIQUE(cid))PARTITIONBYKEY(cid)PARTITIONS4;手动指定了分区键cid执行成功。5.子分区子分区是分区表中每个分区的再次分割可以用于特别大的表在多个磁盘间分配数据和索引。创建过程如下CREATETABLEnew9(idINT,daysDATE)PARTITIONBYRANGE(YEAR(days))SUBPARTITIONBYHASH(TO_DAYS(days))SUBPARTITIONS2(PARTITIONp1VALUESLESS THAN(2010),PARTITIONp2VALUESLESS THAN(2015),PARTITIONp3VALUESLESS THAN(2020))查看分区查看结果显示共有三个大分区p1、p2、p36个小分区也就是建立了三个range分区而每个range分区下有2个hash小分区小分区只指定了数量自动生成的所以名字默认就像一个二维数组int[3][2].也可以指定具体子分区表名如下CREATETABLEnew10(idINT,daysDATE)PARTITIONBYRANGE(YEAR(days))SUBPARTITIONBYHASH(TO_DAYS(days))(PARTITIONp1VALUESLESS THAN(2010)(SUBPARTITION s1,SUBPARTITION s2),PARTITIONp2VALUESLESS THAN(2015)(SUBPARTITION s3,SUBPARTITION s4),PARTITIONp3VALUESLESS THAN(2020)(SUBPARTITION s5,SUBPARTITION s6))查询分区注意每个大分区里的小分区数量必须是相同的所以指定具体的小分区时必须要写完整像下面这样是会报错的CREATETABLEnew11(idINT,daysDATE)PARTITIONBYRANGE(YEAR(days))SUBPARTITIONBYHASH(TO_DAYS(days))(PARTITIONp1VALUESLESS THAN(2010)(SUBPARTITION s1,SUBPARTITION s2),PARTITIONp2VALUESLESS THAN(2015)(SUBPARTITION s3,SUBPARTITION s4),PARTITIONp3VALUESLESS THAN(2020))还有用来分小区的是subpartition,指定小区数量的是subpartitions不要拼错。