通过实例学习MySQL分区表原理及常用操作

2023-08-23 05:34:06 91

1、分区表含义

分区表定义指根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表

2、分区表优点

1)分区表更容易维护。对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

2)一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

3)优化查询。涉及到例如SUM()和COUNT(),可以在多个分区上并行处理，最终结果只需通过总计所有分区得到的结果。

4)通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

3、分区表限制

1)一个表最多只能有1024个分区；

2)MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持；

3)如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列；

4)分区表中无法使用外键约束；

5)MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

6)分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键(BLOBorTEXT列除外)

7)如果表中有主键和唯一索引，按主键字段进行分区时，唯一索引列应该包含分区键。

8)目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引。

9)对象限制(分区表达式不能出现Storedfunctions,storedprocedures,UDFs,orplugins,Declaredvariablesoruservariables.)

10)运算限制(支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个INT或者NULL。支持DIV,不支持/,|,&,^,<<,>>,and~不允许出现在分区表达式中)

11)sql_mode限制(官方强烈建议你在创建分区表后，永远别改变mysql的sql_mode。因为在不同的模式下，某些函数或者运算返回的结果可能会不一样)

12)不支持query_cache和INSERTDELAYED

13)分区键不能是一个子查询(即使是子查询返回的是int值或者null.)

14)子分区限制(只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区并且子分区必须是HASH或KEY类型)

4、分区类型

1)水平分区(根据列属性按行分)

如:一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

水平分区的几种模式：

*Range（范围）：这种模式允许DBA将数据划分不同范围。

如:可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。

*Hash（哈希）：这中模式允许DBA通过对表的一个或多个列的HashKey进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。

如：可以建立一个对表主键进行分区的表。

*Key（键值）：上面Hash模式的一种延伸，这里的HashKey是MySQL系统产生的。

*List（预定义列表）：这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

*Columns分区是对range，list分区的补充，弥补了后两者只支持整型数分区（或者通过转换为整型数），使得支持数据类型增加很多（所有整数类型，日期时间类型，字符类型），还支持多列分区。

注:在多列分区表上插入数据时，采用元组的比较，即多列排序，先根据field1排序，再根据field2排序，根据排序结果来来分区存储数据。

*Composite（复合模式）：以上模式的组合使用。

如：在初始化已经进行了Range范围分区的表上，可以对其中一个分区再进行hash哈希分区。

垂直分区（按列分）：

如：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，可以把这些不经常使用的text和BLOB划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。

注意：子分区（关键字subparttition）：使用RANGE或LIST分区可以再次分割形成子分区，子分区可以是HASH分区或者KEY分区。建议在多磁盘上使用。

查看是否有支持Partition分区表

mysql>SHOWPLUGINS;
+----------------------------+----------+--------------------+---------+---------+
|Name|Status|Type|Library|License|
+----------------------------+----------+--------------------+---------+---------+
|partition|ACTIVE|STORAGEENGINE|NULL|GPL|
+----------------------------+----------+--------------------+---------+---------+
或使用
mysql>SELECTPLUGIN_NAMEasName,PLUGIN_VERSIONasVersion,PLUGIN_STATUSasStatus
->FROMINFORMATION_SCHEMA.PLUGINS
->WHEREPLUGIN_TYPE='STORAGEENGINE';

注意:MySQL5.6.1之前的版本，可以下命令查看have_partitioning参数，新的版本已移除该参数。

mysql>SHOWVARIABLESLIKE'%partition%';

5、实战常用分区表几种模式

1)使用RANGE分区模式

####创建测试表t1，并插入接近400万行数据，再没有分区的情况下，对查询某一条件耗时

mysql>CREATETABLE`t1`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'表主键',
`pid`int(10)unsignedNOTNULLCOMMENT'产品ID',
`price`decimal(15,2)NOTNULLCOMMENT'单价',
`num`int(11)NOTNULLCOMMENT'购买数量',
`uid`int(10)unsignedNOTNULLCOMMENT'客户ID',
`atime`datetimeNOTNULLCOMMENT'下单时间',
`utime`int(10)unsignedNOTNULLDEFAULT0COMMENT'修改时间',
`isdel`tinyint(4)NOTNULLDEFAULT'0'COMMENT'软删除标识',
PRIMARYKEY(`id`,`atime`)
)
INSERTINTOt1(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERTINTOt1(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,'2016-05-0100:00:00');
INSERTINTOt1(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,'2017-05-0100:00:00');
INSERTINTOt1(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,'2018-05-0100:00:00');
INSERTINTOt1(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89756,'2015-05-0100:00:00');
INSERTINTOt1(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89756,'2016-05-0100:00:00');
INSERTINTOt1(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89756,'2017-05-0100:00:00');
INSERTINTOt1(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89756,'2018-05-0100:00:00');
/**********************************主从复制大量数据******************************/
mysql>INSERTINTO`t1`(`pid`,`price`,`num`,`uid`,`atime`)SELECT`pid`,`price`,`num`,`uid`,`atime`FROM`t1`;
mysql>SELECT*FROM`t1`WHERE`uid`=89757AND`atime`
如果是针对已有的表进行表分区，可以使用ALTERTABLE来进行更改表为分区表，这个操作会创建一个分区表，然后自动进行数据copy然后删除原表。
 注:这种会使服务器资源消耗比较大(400多万数据要1分多钟)
mysql>ALTERTABLEt1PARTITIONBYRANGE(YEAR(atime))
->(
->PARTITIONp0VALUESLESSTHAN(2016),
->PARTITIONp1VALUESLESSTHAN(2017),
->PARTITIONp2VALUESLESSTHAN(2018),
->PARTITIONp3VALUESLESSTHANMAXVALUE);
QueryOK,4194304rowsaffected(1min8.32sec)
mysql>EXPLAINPARTITIONSSELECT*FROM`t1`;#查看分区情况
+----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+
|1|SIMPLE|t1|p0,p1,p2,p3|ALL|NULL|NULL|NULL|NULL|4180974|100.00|NULL|
+----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+
1rowinset,2warnings(0.00sec)

同样用上面的查询测试结果
mysql>SELECT*FROM`t1`WHERE`uid`=89757AND`atime`EXPLAINPARTITIONSSELECT*FROM`t1`WHERE`uid`=89757AND`atime`
同时也要注意，进行表分区以后，mysql存放的数据文件夹中该表的存放文件也被拆分为多个

-rw-r-----1mysqlmysql8.7K2月1414:49t1.frm
-rw-r-----1mysqlmysql36M2月1414:50t1#P#p0.ibd
-rw-r-----1mysqlmysql64M2月1414:50t1#P#p1.ibd
-rw-r-----1mysqlmysql92M2月1414:50t1#P#p2.ibd
-rw-r-----1mysqlmysql64M2月1414:50t1#P#p3.ibd
实际生产环境中，大多是采用另外一种方式:新建一个和原来表一样的分区表，然后把数据从原表导出，接着导入新表，最后建立普通索引。

mysql>CREATETABLE`t2`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'表主键',
`pid`int(10)unsignedNOTNULLCOMMENT'产品ID',
`price`decimal(15,2)NOTNULLCOMMENT'单价',
`num`int(11)NOTNULLCOMMENT'购买数量',
`uid`int(10)unsignedNOTNULLCOMMENT'客户ID',
`atime`datetimeNOTNULLCOMMENT'下单时间',
`utime`int(10)unsignedNOTNULLDEFAULT0COMMENT'修改时间',
`isdel`tinyint(4)NOTNULLDEFAULT'0'COMMENT'软删除标识',
PRIMARYKEY(`id`,`atime`)
)

PARTITIONBYRANGECOLUMNS(atime)(
PARTITIONp0VALUESLESSTHAN('2016-01-01'),
PARTITIONp1VALUESLESSTHAN('2016-02-01'),
PARTITIONp2VALUESLESSTHAN('2016-03-01'),
PARTITIONp3VALUESLESSTHAN('2016-04-01'),
PARTITIONp4VALUESLESSTHAN('2016-05-01'),
PARTITIONp5VALUESLESSTHAN('2016-06-01'),
PARTITIONp6VALUESLESSTHAN('2016-07-01'),
PARTITIONp7VALUESLESSTHAN('2016-08-01'),
PARTITIONp8VALUESLESSTHAN('2016-09-01'),
PARTITIONp9VALUESLESSTHAN('2016-10-01'),
PARTITIONp10VALUESLESSTHAN('2016-11-01'),
PARTITIONp11VALUESLESSTHAN('2016-12-01'),
PARTITIONp12VALUESLESSTHAN('2017-01-01'),
PARTITIONp13VALUESLESSTHAN('2017-02-01'),
PARTITIONp14VALUESLESSTHAN('2017-03-01'),
PARTITIONp15VALUESLESSTHAN('2017-04-01'),
PARTITIONp16VALUESLESSTHAN('2017-05-01'),
PARTITIONp17VALUESLESSTHAN('2017-06-01'),
PARTITIONp18VALUESLESSTHAN('2017-07-01'),
PARTITIONp19VALUESLESSTHAN('2017-08-01'),
PARTITIONp20VALUESLESSTHAN('2017-09-01'),
PARTITIONp21VALUESLESSTHAN('2017-10-01'),
PARTITIONp22VALUESLESSTHAN('2017-11-01'),
PARTITIONp23VALUESLESSTHAN('2017-12-01'),
PARTITIONp24VALUESLESSTHAN('2018-01-01'),
PARTITIONp25VALUESLESSTHAN('2018-02-01'),
PARTITIONp26VALUESLESSTHAN('2018-03-01'),
PARTITIONp27VALUESLESSTHAN('2018-04-01'),
PARTITIONp28VALUESLESSTHAN('2018-05-01'),
PARTITIONp29VALUESLESSTHAN('2018-06-01'),
PARTITIONp30VALUESLESSTHAN('2018-07-01'),
PARTITIONp31VALUESLESSTHAN('2018-08-01'),
PARTITIONp32VALUESLESSTHAN('2018-09-01'),
PARTITIONp33VALUESLESSTHAN('2018-10-01'),
PARTITIONp34VALUESLESSTHAN('2018-11-01'),
PARTITIONp35VALUESLESSTHAN('2018-12-01'),
PARTITIONp36VALUESLESSTHANMAXVALUE
);

注:表主键只有id，而分区字段是atime,这里主键要修改为id,stsdate联合主键，分区表要求分区字段要是主键或者是主键的一部分!!!
mysql>EXPLAINPARTITIONSSELECT*FROM`t2`\G;
***************************1.row***************************
id:1
select_type:SIMPLE
table:t2
partitions:p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36
type:ALL
possible_keys:NULL
key:NULL
key_len:NULL
ref:NULL
rows:1
filtered:100.00
Extra:NULL
1rowinset,2warnings(0.00sec)
*******************************************插入数据*************************************************
INSERTINTO`t2`(`pid`,`price`,`num`,`uid`,`atime`)SELECT`pid`,`price`,`num`,`uid`,`atime`FROM`t1`;
QueryOK,4194304rowsaffected(1min18.54sec)
Records:4194304Duplicates:0Warnings:0

或采用导出数据再导入数据，可再添加索引

mysqldump-udbname-p--no-create-infodbnamet2>t2.sq
修改表名，导入数据，测试下ok，删除原来的表。
2)使用LIST分区模式(如果原表存在主键强烈创建新表时，把原主键和要分区字段作为联合主键一并创建)

mysql>CREATETABLE`tb01`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'表主键',
`pid`int(10)unsignedNOTNULLCOMMENT'产品ID',
`price`decimal(15,2)NOTNULLCOMMENT'单价',
`num`int(11)NOTNULLCOMMENT'购买数量',
`uid`int(10)unsignedNOTNULLCOMMENT'客户ID',
`atime`datetimeNOTNULLCOMMENT'下单时间',
`utime`int(10)unsignedNOTNULLDEFAULT0COMMENT'修改时间',
`isdel`tinyint(4)NOTNULLDEFAULT'0'COMMENT'软删除标识',
PRIMARYKEY(`id`,`num`)
);
*****************************插入测试数据******************************************************
INSERTINTO`tb01`(`pid`,`price`,`num`,`uid`,`atime`)SELECT`pid`,`price`,`num`,`uid`,`atime`FROM`tb`;
QueryOK,3145728rowsaffected(46.26sec)
Records:3145728Duplicates:0Warnings:0
mysql>ALTERTABLEtb01PARTITIONBYLIST(num)
(
PARTITIONpl01VALUESIN(1,3),
PARTITIONpl02VALUESIN(2,4),
PARTITIONpl03VALUESIN(5,7),
PARTITIONpl04VALUESIN(6,8),
PARTITIONpl05VALUESIN(9,10)
);
QueryOK,3145728rowsaffected(48.86sec)
Records:3145728Duplicates:0Warnings:0
存放mysql数据文件中生成，以下文件
-rw-r-----1mysqlmysql8.7K2月1511:35tb01.frm
-rw-r-----1mysqlmysql56M2月1511:36tb01#P#pl01.ibd
-rw-r-----1mysqlmysql32M2月1511:36tb01#P#pl02.ibd
-rw-r-----1mysqlmysql36M2月1511:36tb01#P#pl03.ibd
-rw-r-----1mysqlmysql36M2月1511:36tb01#P#pl04.ibd
-rw-r-----1mysqlmysql52M2月1511:36tb01#P#pl05.ibd

mysql>EXPLAINPARTITIONSSELECT*FROM`tb01`;
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+
|1|SIMPLE|tb01|pl01,pl02,pl03,pl04,pl05|ALL|NULL|NULL|NULL|NULL|3136392|100.00|NULL|
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+
1rowinset,2warnings(0.00sec)

3)COLUMNS分区
 创建多列分区表tb02，这里两列都不是联合主键
mysql>CREATETABLEtb02(
->aintnotnull,
->bintnotnull
->)
->PARTITIONBYRANGECOLUMNS(a,b)(
->partitionp0valueslessthan(0,10),
->partitionp1valueslessthan(10,20),
->partitionp2valueslessthan(10,30),
->partitionp3valueslessthan(maxvalue,maxvalue)
->);
mysql>EXPLAINPARTITIONSSELECT*FROM`tb02`;#查看
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
|1|SIMPLE|tb02|p0,p1,p2,p3|ALL|NULL|NULL|NULL|NULL|1|100.00|NULL|
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
1rowinset,2warnings(0.00sec)
mysql>insertintotb02values(11,13);#手工插入测试数据
QueryOK,1rowaffected(0.01sec)
mysql>selectPARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWSfrominformation_schema.partitionswheretable_schema=schema()andtable_name='tb02';
+----------------+----------------------+------------+
|PARTITION_NAME|PARTITION_EXPRESSION|TABLE_ROWS|
+----------------+----------------------+------------+
|p0|`a`,`b`|0|
|p1|`a`,`b`|0|
|p2|`a`,`b`|0|
|p3|`a`,`b`|1|
+----------------+----------------------+------------+
4rowsinset(0.03sec)

 4)Hase分区
 HASH主要是为了让数据在设定个数的分区中尽可能分布平均，执行哈希分区时，mysql会对分区键执行哈希函数，以确定数据放在哪个分区中。HASH分区分为常规HASH分区和线性HASH分区，前者使用取模算法，后者使用线性2的幂的运算规则。
CREATETABLE`tb03`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'表主键',
`pid`int(10)unsignedNOTNULLCOMMENT'产品ID',
`price`decimal(15,2)NOTNULLCOMMENT'单价',
`num`int(11)NOTNULLCOMMENT'购买数量',
`uid`int(10)unsignedNOTNULLCOMMENT'客户ID',
`atime`datetimeNOTNULLCOMMENT'下单时间',
`utime`int(10)unsignedNOTNULLDEFAULT0COMMENT'修改时间',
`isdel`tinyint(4)NOTNULLDEFAULT'0'COMMENT'软删除标识',
PRIMARYKEY(`id`)
)
PARTITIONBYHASH(id)partitions4;
插入2行数据：
INSERTINTOtb03(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERTINTOtb03(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
mysql>explainpartitionsselect*fromtb03whereid=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|1|SIMPLE|tb03|p1|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1rowinset,2warnings(0.00sec)
mysql>explainpartitionsselect*fromtb03whereid=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|1|SIMPLE|tb03|p2|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1rowinset,2warnings(0.00sec)
注意:HASH分区虽然尽可能让数据平均地分布在每个分区上，提高了查询效率，但增加了分区管理的代价，比如以前有5个分区，现在要加上一个分区，算法有mod(expr,5)变成(expr,6)，原5个分区的数据大部分要重新计算重新分区。虽然使用线性HASH分区会降低分区管理的代价，但是数据却没有常规HASH分布得那么均匀。
5)KEY分区
KEY分区类似与HASH分区，但是不能自定义表达式，不过支持分区键的类型很多，除Text,Blob等文本类型。
CREATETABLE`tb04`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'表主键',
`pid`int(10)unsignedNOTNULLCOMMENT'产品ID',
`price`decimal(15,2)NOTNULLCOMMENT'单价',
`num`int(11)NOTNULLCOMMENT'购买数量',
`uid`int(10)unsignedNOTNULLCOMMENT'客户ID',
`atime`datetimeNOTNULLCOMMENT'下单时间',
`utime`int(10)unsignedNOTNULLDEFAULT0COMMENT'修改时间',
`isdel`tinyint(4)NOTNULLDEFAULT'0'COMMENT'软删除标识',
PRIMARYKEY(`id`)
)
PARTITIONBYKEY(id)partitions4;
插入2行数据：
INSERTINTOtb04(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERTINTOtb04(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
#用执行任务查看记录落在分区情况
mysql>explainpartitionsselect*fromtb04whereid=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|1|SIMPLE|tb04|p0|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1rowinset,2warnings(0.00sec)
mysql>explainpartitionsselect*fromtb04whereid=2;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|1|SIMPLE|tb04|p3|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1rowinset,2warnings(0.00sec)
6)分区表管理
 建议在生产环境中尽量不要修改分区，alter会读出存在旧表中的数据，再存入新定义的表中，过程IO将很大，而且全表都会锁住。
 *1*删除分区:示例以上面tb01表
 --未删除p05分区查询数据，主要验证当删除分区数据是否被删除
mysql>selectcount(1)fromtb01wherenum=10;
+----------+
|count(1)|
+----------+
|524288|
+----------+
1rowinset(0.37sec)
mysql>altertabletb01droppartitionpl05;#删除pl05分区,如：一次性删除多个分区，altertabletb01droppartitionpl04,pl05;
QueryOK,0rowsaffected(0.06sec)
Records:0Duplicates:0Warnings:0
mysql>selectcount(1)fromtb01wherenum=10;#结果数据也被删除，慎重操作
+----------+
|count(1)|
+----------+
|0|
+----------+
1rowinset(0.01sec）

注意：删除分区会删除数据，谨慎操作；不可以删除hash或者key分区。
 *2*增加分区
 注：新分区的值不能包含任意一个现有分区中值列表中的值，否则报错;新增分区会重新整理数据，原有数据不会丢失。有MAXVALUE值后，直接不能直接加分区，如示例以上面的t1表为例子。
mysql>ALTERTABLEt1ADDPARTITION(PARTITIONP4VALUESLESSTHAN(2018));
ERROR1481(HY000):MAXVALUEcanonlybeusedinlastpartitiondefinition
示例：把tb01上面删除的pl05分区添加
mysql>ALTERTABLEtb01ADDPARTITION(PARTITIONpl05VALUESIN(9,10));
QueryOK,0rowsaffected(0.05sec)
Records:0Duplicates:0Warnings:0

 *3*分解分区
 注：Reorganizepartition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。
 示例：
mysql>createtabletb05
->(depint,
->birthdatedate,
->salaryint
->)
->partitionbyrange(salary)
->(
->partitionp1valueslessthan(1000),
->partitionp2valueslessthan(2000),
->partitionp3valueslessthanmaxvalue
->);
QueryOK,0rowsaffected(0.08sec)
****插入一条测试数据
mysql>inserttb05values(1,'2016-03-06',80);
QueryOK,1rowaffected(0.01sec)
mysql>altertabletb05reorganizepartitionp1into(
partitionp01valueslessthan(100),
partitionp02valueslessthan(1000)
);----不会丢失数据
mysql>explainpartitionsselect*fromtb05wheresalary=80;#查看已经落在新的分区p01上
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1|SIMPLE|tb05|p01|ALL|NULL|NULL|NULL|NULL|1|100.00|Usingwhere|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1rowinset,2warnings(0.00sec)

*4*合并分区
 注:把2个分区合并为一个。
 示例：把上面的tb05表中分解的p01和p02合并至p1上
mysql>altertabletb05reorganizepartitionp01,p02into(partitionp1valueslessthan(1000));--不会丢失数据
QueryOK,0rowsaffected(0.05sec)
Records:0Duplicates:0Warnings:0
mysql>explainpartitionsselect*fromtb05wheresalary=80;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1|SIMPLE|tb05|p1|ALL|NULL|NULL|NULL|NULL|1|100.00|Usingwhere|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1rowinset,2warnings(0.00sec)
*5*重新定义hash分区表:
 RANGE和LIST分区在重新定义时，只能重新定义相邻的分区，不可以跳过分区，并且重新定义的分区区间必须和原分区区间一致，也不可以改变分区的类型。
 示例:
mysql>EXPLAINPARTITIONSSELECT*FROM`tb03`;
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
|1|SIMPLE|tb03|p0,p1,p2,p3|ALL|NULL|NULL|NULL|NULL|4|100.00|NULL|
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
1rowinset,2warnings(0.00sec)
mysql>Altertabletb03partitionbyhash(id)partitions8;#不会丢失数据
QueryOK,4rowsaffected(0.13sec)
Records:4Duplicates:0Warnings:0
mysql>EXPLAINPARTITIONSSELECT*FROM`tb03`;
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+
|1|SIMPLE|tb03|p0,p1,p2,p3,p4,p5,p6,p7|ALL|NULL|NULL|NULL|NULL|1|100.00|NULL|
+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+
1rowinset,2warnings(0.02sec)

*6*删除表的所有分区:
 示例:删除tb03表所有分区 
mysql>Altertabletb03removepartitioning;#不会丢失数据
QueryOK,4rowsaffected(0.07sec)
Records:4Duplicates:0Warnings:0
mysql>EXPLAINPARTITIONSSELECT*FROM`tb03`;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|1|SIMPLE|tb03|NULL|ALL|NULL|NULL|NULL|NULL|4|100.00|NULL|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1rowinset,2warnings(0.00sec)


 *7*整理分区碎片
 注:如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTERTABLE...OPTIMIZEPARTITION”来收回没有使用的空间，并整理分区数据文件的碎片。

ALTERTABLEtb03optimizepartitionp1,p2;

*8*分析分区：
读取并保存分区的键分布。



mysql>ALTERTABLEtb04CHECKpartitionp1,p2;
+--------------+-------+----------+----------+
|Table|Op|Msg_type|Msg_text|
+--------------+-------+----------+----------+
|testsms.tb04|check|status|OK|
+--------------+-------+----------+----------+
1rowinset(0.01sec)


 *9*检查分区:
 可以使用几乎与对非分区表使用CHECKTABLE相同的方式检查分区。这个命令可以告诉tb04表分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTERTABLE...REPAIRPARTITION”来修补该分区。
mysql>ALTERTABLEtb04CHECKpartitionp1,p2;
+--------------+-------+----------+----------+
|Table|Op|Msg_type|Msg_text|
+--------------+-------+----------+----------+
|testsms.tb04|check|status|OK|
+--------------+-------+----------+----------+
1rowinset(0.01sec)
6、实际生产简单应用
场景:之前有个没有分区的大数据量表SmsSend(例表，大概2800万行记录)，统计过程非常的耗时，考虑用年分区，并且对历史数据库进行备份，把过去2014年的数据转移至新的备份表smssendbak。如在线重定义比较耗时间，可采用exchange处理！
1)查看当前SmsSend表
mysql>SHOWCREATETABLESmsSend;#查看创建信息，未进行分区
|SmsSend|CREATETABLE`SmsSend`(
`Guid`char(36)NOTNULLCOMMENT'唯一标识',
`SID`varbinary(85)DEFAULTNULLCOMMENT'商家唯一编号',
`Mobile`longtextNOTNULLCOMMENT'接收手机号(以","分割)',
`SmsContent`varchar(500)NOTNULLCOMMENT'短信内容',
`SmsCount`int(11)NOTNULLDEFAULT'1'COMMENT'条数',
`Status`int(11)NOTNULLCOMMENT'当前状态(0,等待发送;1,发送成功;-1,发送失败)',
`SendChanelKeyName`varchar(20)DEFAULTNULLCOMMENT'发送通道标识',
`SendTime`datetimeNOTNULLCOMMENT'发送成功时间',
`SendType`int(11)NOTNULLDEFAULT'1'COMMENT'短信发送类型(1,单发;2,群发)',
`ReceiveTime`datetimeDEFAULTNULLCOMMENT'接收到回复报告的时间',
`Priority`int(11)NOTNULLDEFAULT'0'COMMENT'优先级',
`UserAccount`varchar(50)DEFAULTNULLCOMMENT'操作员',
`ChainStoreGuid`char(36)DEFAULTNULLCOMMENT'操作店面唯一标识',
`RelationKey`longtextCOMMENT'回复报告关联标识',
`Meno`textCOMMENT'备注',
`IsFree`bit(1)NOTNULLDEFAULTb'0'COMMENT'是否免费'
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4|
mysql>SELECTCOUNT(*)FROMSmsSend;#行记录
+----------+
|COUNT(*)|
+----------+
|28259803|
+----------+
1rowinset(1min52.60sec)
#可得知大数据表下在线分区比较慢并且耗性能
mysql>ALTERTABLESmsSendPARTITIONBYRANGE(YEAR(SendTime))
->(
->PARTITIONpy01VALUESLESSTHAN(2015),
->PARTITIONpy02VALUESLESSTHAN(2016),
->PARTITIONpy03VALUESLESSTHAN(2017));
QueryOK,28259803rowsaffected(20min36.05sec)
Records:28259803Duplicates:0Warnings:0
#查看分区记录数
mysql>selectcount(1)fromSmsSendpartition(py01);
+----------+
|count(1)|
+----------+
|10|
+----------+
1rowinset(0.00sec)
mysql>explainpartitionsselect*fromSmsSendwhereSendTime<'2015-01-01';#2014年的数据落在第一分区
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|1|SIMPLE|SmsSend|py01|ALL|NULL|NULL|NULL|NULL|10|33.33|Usingwhere|
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1rowinset,2warnings(0.00sec)
mysql>selectcount(1)fromSmsSendpartition(py02);
+----------+
|count(1)|
+----------+
|10|
+----------+
1rowinset(0.00sec）
2)快速创建一个smssendbak备份表与原SmsSend表结构一致，并删除备份表所有分区


mysql>CREATETABLEsmssendbakLIKESmsSend;
QueryOK,0rowsaffected(0.14sec)
mysql>ALTERTABLEsmssendbakREMOVEPARTITIONING;
QueryOK,0rowsaffected(0.19sec)
Records:0Duplicates:0Warnings:0

3)使用EXCHANGEPARTITION转移分区数据至备份表，并查看原来表分区记录以及新备份表
smssendbak记录
mysql>ALTERTABLESmsSendEXCHANGEPARTITIONpy01WITHTABLEsmssendbak;
QueryOK,0rowsaffected(0.13sec)
mysql>selectcount(1)fromSmsSendpartition(py01);#对比上面原SmsSend表分区的记录
+----------+
|count(1)|
+----------+
|0|
+----------+
1rowinset(0.00sec)
mysql>SELECTCOUNT(1)FROMsmssendbak;#查看新smssendbak备份表转移记录
+----------+
|COUNT(1)|
+----------+
|10|
+----------+
1rowinset(0.00sec)
*****************测试使用的表***********************************************************************
创建一个基础测试表:
CREATETABLE`tb`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'表主键',
`pid`int(10)unsignedNOTNULLCOMMENT'产品ID',
`price`decimal(15,2)NOTNULLCOMMENT'单价',
`num`int(11)NOTNULLCOMMENT'购买数量',
`uid`int(10)unsignedNOTNULLCOMMENT'客户ID',
`atime`datetimeNOTNULLCOMMENT'下单时间',
`utime`int(10)unsignedNOTNULLDEFAULT0COMMENT'修改时间',
`isdel`tinyint(4)NOTNULLDEFAULT'0'COMMENT'软删除标识',
);
插入数据：
INSERTINTOtb(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERTINTOtb(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,'2016-05-0100:00:00');
INSERTINTOtb(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,'2017-05-0100:00:00');
INSERTINTOtb(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89757,'2018-05-0100:00:00');
INSERTINTOtb(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89756,'2015-05-0100:00:00');
INSERTINTOtb(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89756,'2016-05-0100:00:00');
INSERTINTOtb(`pid`,`price`,`num`,`uid`,`atime`)VALUES(1,12.23,1,89756,'2017-05-0100:00:00');

************************************插入大量的数据(建议百万以上)*************************************
INSERTINTO`tb`(`pid`,`price`,`num`,`uid`,`atime`)SELECT`pid`,`price`,`num`,`uid`,`atime`FROM`tb`;
****注意，如果要删除自增长的主键id(修改过程中，建议该库改为只读)，如下操作：
Altertabletbchangeididint(10)；#先删除自增长
Altertabletbdropprimarykey；#删除主建
Altertabletbchangeididintnotnullauto_increment;#如果想重新设置为自增字段
Altertabletbauto_increment=1;#自增起始
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。

通过实例学习MySQL分区表原理及常用操作

热门推荐

随机推荐