通过实例学习MySQL分区表原理及常用操作
程序员文章站
2023-12-02 09:43:04
1、分区表含义
分区表定义指根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规...
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分区的时候，可以使用其他类型的列作为分区键(blob or text 列除外)
7)如果表中有主键和唯一索引，按主键字段进行分区时，唯一索引列应该包含分区键。
8)目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引。
9)对象限制(分区表达式不能出现stored functions, stored procedures, udfs, orplugins,declared variables or user variables.)
10)运算限制(支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个int或者null。支持div,不支持/,|, &, ^, <<, >>, and ~ 不允许出现在分区表达式中)
11)sql_mode限制(官方强烈建议你在创建分区表后，永远别改变mysql的sql_mode。因为在不同的模式下，某些函数或者运算返回的结果可能会不一样)
12)不支持query_cache和insert delayed
13)分区键不能是一个子查询(即使是子查询返回的是int值或者null.)
14)子分区限制(只有rang和list分区能进行子分区。hash和key分区不能进行子分区并且子分区必须是hash 或 key类型)
4、分区类型
1)水平分区(根据列属性按行分)
如:一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。
水平分区的几种模式：
* range（范围）：这种模式允许dba将数据划分不同范围。
如:可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。
* hash（哈希）：这中模式允许dba通过对表的一个或多个列的hash key进行计算，最后通过这个hash码不同数值对应的数据区域进行分区。
如：可以建立一个对表主键进行分区的表。
* key（键值）：上面hash模式的一种延伸，这里的hash key是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> show plugins ; +----------------------------+----------+--------------------+---------+---------+ | name | status | type | library | license | +----------------------------+----------+--------------------+---------+---------+ | partition | active | storage engine | null | gpl | +----------------------------+----------+--------------------+---------+---------+ 或使用 mysql> select plugin_name as name, plugin_version as version, plugin_status as status -> from information_schema.plugins -> where plugin_type='storage engine';
注意:mysql 5.6.1 之前的版本，可以下命令查看 have_partitioning 参数，新的版本已移除该参数。
mysql> show variables like '%partition%';
5、实战常用分区表几种模式
1)使用range分区模式
####创建测试表t1，并插入接近400万行数据，再没有分区的情况下，对查询某一条件耗时
mysql> create table `t1` ( `id` int(10) unsigned not null auto_increment comment '表主键', `pid` int(10) unsigned not null comment '产品id', `price` decimal(15,2) not null comment '单价', `num` int(11) not null comment '购买数量', `uid` int(10) unsigned not null comment '客户id', `atime` datetime not null comment '下单时间', `utime` int(10) unsigned not null default 0 comment '修改时间', `isdel` tinyint(4) not null default '0' comment '软删除标识', primary key (`id`,`atime`) ) insert into t1(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,current_timestamp()); insert into t1(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,'2016-05-01 00:00:00'); insert into t1(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,'2017-05-01 00:00:00'); insert into t1(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,'2018-05-01 00:00:00'); insert into t1(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89756,'2015-05-01 00:00:00'); insert into t1(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89756,'2016-05-01 00:00:00'); insert into t1(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89756,'2017-05-01 00:00:00'); insert into t1(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89756,'2018-05-01 00:00:00'); /**********************************主从复制大量数据******************************/ mysql> insert into `t1`(`pid`,`price`,`num`,`uid`,`atime`) select `pid`,`price`,`num`,`uid`,`atime` from `t1`; mysql> select * from `t1` where `uid`=89757 and `atime`< current_timestamp(); 1048576 rows in set (5.62 sec) #没有分区表情况耗时5.62s
如果是针对已有的表进行表分区，可以使用alter table来进行更改表为分区表，这个操作会创建一个分区表，然后自动进行数据copy然后删除原表。
注: 这种会使服务器资源消耗比较大(400多万数据要1分多钟)
mysql> alter table t1 partition by range (year(atime)) -> ( -> partition p0 values less than (2016), -> partition p1 values less than (2017), -> partition p2 values less than (2018), -> partition p3 values less than maxvalue ); query ok, 4194304 rows affected (1 min 8.32 sec) mysql> explain partitions select * 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 | +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)
同样用上面的查询测试结果
mysql> select * from `t1` where `uid`=89757 and `atime`< current_timestamp(); 1048576 rows in set (4.46 sec) #与上面没有分区查询执行的时间相比少了接近1s mysql> explain partitions select * from `t1` where `uid`=89757 and `atime`< current_timestamp(); #查看查询使用的分区情况 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | simple | t1 | p0,p1,p2 | all | null | null | null | null | 3135804 | 3.33 | using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)同时也要注意，进行表分区以后，mysql存放的数据文件夹中该表的存放文件也被拆分为多个
-rw-r----- 1 mysql mysql 8.7k 2月 14 14:49 t1.frm -rw-r----- 1 mysql mysql 36m 2月 14 14:50 t1#p#p0.ibd -rw-r----- 1 mysql mysql 64m 2月 14 14:50 t1#p#p1.ibd -rw-r----- 1 mysql mysql 92m 2月 14 14:50 t1#p#p2.ibd -rw-r----- 1 mysql mysql 64m 2月 14 14:50 t1#p#p3.ibd实际生产环境中，大多是采用另外一种方式:新建一个和原来表一样的分区表，然后把数据从原表导出，接着导入新表，最后建立普通索引。
mysql> create table `t2` (
`id` int(10) unsigned not null auto_increment comment '表主键',
`pid` int(10) unsigned not null comment '产品id',
`price` decimal(15,2) not null comment '单价',
`num` int(11) not null comment '购买数量',
`uid` int(10) unsigned not null comment '客户id',
`atime` datetime not null comment '下单时间',
`utime` int(10) unsigned not null default 0 comment '修改时间',
`isdel` tinyint(4) not null default '0' comment '软删除标识',
primary key (`id`,`atime`)
)
partition by range columns(atime) (
partition p0 values less than ('2016-01-01'),
partition p1 values less than ('2016-02-01'),
partition p2 values less than ('2016-03-01'),
partition p3 values less than ('2016-04-01'),
partition p4 values less than ('2016-05-01'),
partition p5 values less than ('2016-06-01'),
partition p6 values less than ('2016-07-01'),
partition p7 values less than ('2016-08-01'),
partition p8 values less than ('2016-09-01'),
partition p9 values less than ('2016-10-01'),
partition p10 values less than ('2016-11-01'),
partition p11 values less than ('2016-12-01'),
partition p12 values less than ('2017-01-01'),
partition p13 values less than ('2017-02-01'),
partition p14 values less than ('2017-03-01'),
partition p15 values less than ('2017-04-01'),
partition p16 values less than ('2017-05-01'),
partition p17 values less than ('2017-06-01'),
partition p18 values less than ('2017-07-01'),
partition p19 values less than ('2017-08-01'),
partition p20 values less than ('2017-09-01'),
partition p21 values less than ('2017-10-01'),
partition p22 values less than ('2017-11-01'),
partition p23 values less than ('2017-12-01'),
partition p24 values less than ('2018-01-01'),
partition p25 values less than ('2018-02-01'),
partition p26 values less than ('2018-03-01'),
partition p27 values less than ('2018-04-01'),
partition p28 values less than ('2018-05-01'),
partition p29 values less than ('2018-06-01'),
partition p30 values less than ('2018-07-01'),
partition p31 values less than ('2018-08-01'),
partition p32 values less than ('2018-09-01'),
partition p33 values less than ('2018-10-01'),
partition p34 values less than ('2018-11-01'),
partition p35 values less than ('2018-12-01'),
partition p36 values less than maxvalue
);
注:表主键只有id，而分区字段是atime, 这里主键要修改为 id,stsdate 联合主键，分区表要求分区字段要是主键或者是主键的一部分!!!
mysql> explain partitions select * 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 1 row in set, 2 warnings (0.00 sec) *******************************************插入数据************************************************* insert into `t2`(`pid`,`price`,`num`,`uid`,`atime`) select `pid`,`price`,`num`,`uid`,`atime` from `t1`; query ok, 4194304 rows affected (1 min 18.54 sec) records: 4194304 duplicates: 0 warnings: 0或采用导出数据再导入数据，可再添加索引
mysqldump -u dbname -p --no-create-info dbname t2 > t2.sq
修改表名，导入数据，测试下ok，删除原来的表。
2)使用list分区模式(如果原表存在主键强烈创建新表时，把原主键和要分区字段作为联合主键一并创建)
mysql> create table `tb01` ( `id` int(10) unsigned not null auto_increment comment '表主键', `pid` int(10) unsigned not null comment '产品id', `price` decimal(15,2) not null comment '单价', `num` int(11) not null comment '购买数量', `uid` int(10) unsigned not null comment '客户id', `atime` datetime not null comment '下单时间', `utime` int(10) unsigned not null default 0 comment '修改时间', `isdel` tinyint(4) not null default '0' comment '软删除标识', primary key (`id`,`num`) ); *****************************插入测试数据****************************************************** insert into `tb01`(`pid`,`price`,`num`,`uid`,`atime`) select `pid`,`price`,`num`,`uid`,`atime` from `tb`; query ok, 3145728 rows affected (46.26 sec) records: 3145728 duplicates: 0 warnings: 0 mysql> alter table tb01 partition by list(num) ( partition pl01 values in (1,3), partition pl02 values in (2,4), partition pl03 values in (5,7), partition pl04 values in (6,8), partition pl05 values in (9,10) ); query ok, 3145728 rows affected (48.86 sec) records: 3145728 duplicates: 0 warnings: 0 存放mysql数据文件中生成，以下文件 -rw-r----- 1 mysql mysql 8.7k 2月 15 11:35 tb01.frm -rw-r----- 1 mysql mysql 56m 2月 15 11:36 tb01#p#pl01.ibd -rw-r----- 1 mysql mysql 32m 2月 15 11:36 tb01#p#pl02.ibd -rw-r----- 1 mysql mysql 36m 2月 15 11:36 tb01#p#pl03.ibd -rw-r----- 1 mysql mysql 36m 2月 15 11:36 tb01#p#pl04.ibd -rw-r----- 1 mysql mysql 52m 2月 15 11:36 tb01#p#pl05.ibd
mysql> explain partitions select * 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 | +----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)
3)columns分区
创建多列分区表tb02，这里两列都不是联合主键
mysql> create table tb02( -> a int not null, -> b int not null -> ) -> partition by range columns(a,b)( -> partition p0 values less than(0,10), -> partition p1 values less than(10,20), -> partition p2 values less than(10,30), -> partition p3 values less than(maxvalue,maxvalue) -> ); mysql> explain partitions select * 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 | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> insert into tb02 values (11,13); #手工插入测试数据 query ok, 1 row affected (0.01 sec) mysql> select partition_name,partition_expression,table_rows from information_schema.partitions where table_schema=schema() and table_name='tb02'; +----------------+----------------------+------------+ | partition_name | partition_expression | table_rows | +----------------+----------------------+------------+ | p0 | `a`,`b` | 0 | | p1 | `a`,`b` | 0 | | p2 | `a`,`b` | 0 | | p3 | `a`,`b` | 1 | +----------------+----------------------+------------+ 4 rows in set (0.03 sec)4)hase分区
hash主要是为了让数据在设定个数的分区中尽可能分布平均，执行哈希分区时，mysql会对分区键执行哈希函数，以确定数据放在哪个分区中。hash分区分为常规hash分区和线性hash分区，前者使用取模算法，后者使用线性2的幂的运算规则。
create table `tb03` ( `id` int(10) unsigned not null auto_increment comment '表主键', `pid` int(10) unsigned not null comment '产品id', `price` decimal(15,2) not null comment '单价', `num` int(11) not null comment '购买数量', `uid` int(10) unsigned not null comment '客户id', `atime` datetime not null comment '下单时间', `utime` int(10) unsigned not null default 0 comment '修改时间', `isdel` tinyint(4) not null default '0' comment '软删除标识', primary key (`id`) ) partition by hash(id) partitions 4; 插入2行数据： insert into tb03(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,current_timestamp()); insert into tb03(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,current_timestamp()); mysql> explain partitions select * from tb03 where id=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 | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain partitions select * from tb03 where id=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 | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)注意:hash分区虽然尽可能让数据平均地分布在每个分区上，提高了查询效率，但增加了分区管理的代价，比如以前有5个分区，现在要加上一个分区，算法有mod(expr,5)变成(expr,6)，原5个分区的数据大部分要重新计算重新分区。虽然使用线性hash分区会降低分区管理的代价，但是数据却没有常规hash分布得那么均匀。
5)key分区
key分区类似与hash分区，但是不能自定义表达式，不过支持分区键的类型很多，除text,blob等文本类型。
create table `tb04` ( `id` int(10) unsigned not null auto_increment comment '表主键', `pid` int(10) unsigned not null comment '产品id', `price` decimal(15,2) not null comment '单价', `num` int(11) not null comment '购买数量', `uid` int(10) unsigned not null comment '客户id', `atime` datetime not null comment '下单时间', `utime` int(10) unsigned not null default 0 comment '修改时间', `isdel` tinyint(4) not null default '0' comment '软删除标识', primary key (`id`) ) partition by key(id) partitions 4; 插入2行数据： insert into tb04(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,current_timestamp()); insert into tb04(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,current_timestamp()); #用执行任务查看记录落在分区情况 mysql> explain partitions select * from tb04 where id=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 | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain partitions select * from tb04 where id=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 | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)6)分区表管理
建议在生产环境中尽量不要修改分区，alter会读出存在旧表中的数据，再存入新定义的表中，过程io将很大，而且全表都会锁住。
*1*删除分区:示例以上面tb01表
--未删除p05分区查询数据，主要验证当删除分区数据是否被删除
mysql> select count(1) from tb01 where num=10; +----------+ | count(1) | +----------+ | 524288 | +----------+ 1 row in set (0.37 sec) mysql> alter table tb01 drop partition pl05; #删除pl05分区,如：一次性删除多个分区，alter table tb01 drop partition pl04,pl05; query ok, 0 rows affected (0.06 sec) records: 0 duplicates: 0 warnings: 0 mysql> select count(1) from tb01 where num=10; #结果数据也被删除，慎重操作 +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec）注意：删除分区会删除数据，谨慎操作；不可以删除hash或者key分区。
*2*增加分区
注：新分区的值不能包含任意一个现有分区中值列表中的值，否则报错;新增分区会重新整理数据，原有数据不会丢失。有maxvalue值后，直接不能直接加分区，如示例以上面的t1表为例子。
mysql> alter table t1 add partition (partition p4 values less than (2018) ) ; error 1481 (hy000): maxvalue can only be used in last partition definition 示例：把tb01上面删除的pl05分区添加 mysql> alter table tb01 add partition(partition pl05 values in (9,10)); query ok, 0 rows affected (0.05 sec) records: 0 duplicates: 0 warnings: 0*3*分解分区
注：reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。分解前后分区的整体范围应该一致。
示例：
mysql> create table tb05 -> (dep int, -> birthdate date, -> salary int -> ) -> partition by range(salary) -> ( -> partition p1 values less than (1000), -> partition p2 values less than (2000), -> partition p3 values less than maxvalue -> ); query ok, 0 rows affected (0.08 sec) ****插入一条测试数据 mysql> insert tb05 values(1,'2016-03-06',80); query ok, 1 row affected (0.01 sec) mysql>alter table tb05 reorganize partition p1 into( partition p01 values less than (100), partition p02 values less than (1000) ); ----不会丢失数据 mysql> explain partitions select * from tb05 where salary=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 | using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)*4*合并分区
注:把2个分区合并为一个。
示例：把上面的tb05表中分解的p01和p02合并至p1上
mysql> alter table tb05 reorganize partition p01,p02 into(partition p1 values less than (1000)); --不会丢失数据 query ok, 0 rows affected (0.05 sec) records: 0 duplicates: 0 warnings: 0 mysql> explain partitions select * from tb05 where salary=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 | using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)*5*重新定义hash分区表:
range和list分区在重新定义时，只能重新定义相邻的分区，不可以跳过分区，并且重新定义的分区区间必须和原分区区间一致，也不可以改变分区的类型。
示例:
mysql> explain partitions select * 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 | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> alter table tb03 partition by hash(id)partitions 8; #不会丢失数据 query ok, 4 rows affected (0.13 sec) records: 4 duplicates: 0 warnings: 0 mysql> explain partitions select * 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 | +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.02 sec)*6*删除表的所有分区:
示例:删除tb03表所有分区
mysql> alter table tb03 remove partitioning; #不会丢失数据 query ok, 4 rows affected (0.07 sec) records: 4 duplicates: 0 warnings: 0 mysql> explain partitions select * 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 | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec)
*7*整理分区碎片
注:如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有varchar，blob，或text类型的列）作了许多修改，可以使用“alter table ... optimize partition”来收回没有使用的空间，并整理分区数据文件的碎片。
alter table tb03 optimize partition p1,p2;
*8*分析分区：
读取并保存分区的键分布。
mysql> alter table tb04 check partition p1,p2; +--------------+-------+----------+----------+ | table | op | msg_type | msg_text | +--------------+-------+----------+----------+ | testsms.tb04 | check | status | ok | +--------------+-------+----------+----------+ 1 row in set (0.01 sec)
*9*检查分区:
可以使用几乎与对非分区表使用check table 相同的方式检查分区。这个命令可以告诉tb04表分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况，使用“alter table ... repair partition”来修补该分区。
mysql> alter table tb04 check partition p1,p2; +--------------+-------+----------+----------+ | table | op | msg_type | msg_text | +--------------+-------+----------+----------+ | testsms.tb04 | check | status | ok | +--------------+-------+----------+----------+ 1 row in set (0.01 sec)
6、实际生产简单应用
场景:之前有个没有分区的大数据量表smssend(例表，大概2800万行记录)，统计过程非常的耗时，考虑用年分区，并且对历史数据库进行备份，把过去2014年的数据转移至新的备份表smssendbak。如在线重定义比较耗时间，可采用exchange处理！
1)查看当前smssend表
mysql> show create table smssend; #查看创建信息，未进行分区 | smssend | create table `smssend` ( `guid` char(36) not null comment '唯一标识', `sid` varbinary(85) default null comment '商家唯一编号', `mobile` longtext not null comment '接收手机号(以","分割)', `smscontent` varchar(500) not null comment '短信内容', `smscount` int(11) not null default '1' comment '条数', `status` int(11) not null comment '当前状态(0,等待发送;1,发送成功;-1,发送失败)', `sendchanelkeyname` varchar(20) default null comment '发送通道标识', `sendtime` datetime not null comment '发送成功时间', `sendtype` int(11) not null default '1' comment '短信发送类型(1,单发;2,群发)', `receivetime` datetime default null comment '接收到回复报告的时间', `priority` int(11) not null default '0' comment '优先级', `useraccount` varchar(50) default null comment '操作员', `chainstoreguid` char(36) default null comment '操作店面唯一标识', `relationkey` longtext comment '回复报告关联标识', `meno` text comment '备注', `isfree` bit(1) not null default b'0' comment '是否免费' ) engine=innodb default charset=utf8mb4 | mysql> select count(*) from smssend; #行记录 +----------+ | count(*) | +----------+ | 28259803 | +----------+ 1 row in set (1 min 52.60 sec) #可得知大数据表下在线分区比较慢并且耗性能 mysql> alter table smssend partition by range (year(sendtime)) -> ( -> partition py01 values less than (2015), -> partition py02 values less than (2016), -> partition py03 values less than (2017) ); query ok, 28259803 rows affected (20 min 36.05 sec) records: 28259803 duplicates: 0 warnings: 0 #查看分区记录数 mysql> select count(1) from smssend partition(py01); +----------+ | count(1) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> explain partitions select * from smssend where sendtime < '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 | using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> select count(1) from smssend partition(py02); +----------+ | count(1) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec）
2)快速创建一个smssendbak备份表与原smssend表结构一致，并删除备份表所有分区
mysql> create table smssendbak like smssend; query ok, 0 rows affected (0.14 sec) mysql> alter table smssendbak remove partitioning; query ok, 0 rows affected (0.19 sec) records: 0 duplicates: 0 warnings: 0
3)使用exchange partition转移分区数据至备份表，并查看原来表分区记录以及新备份表
smssendbak记录
mysql> alter table smssend exchange partition py01 with table smssendbak; query ok, 0 rows affected (0.13 sec) mysql> select count(1) from smssend partition(py01); #对比上面原smssend表分区的记录 +----------+ | count(1) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> select count(1) from smssendbak; #查看新smssendbak备份表转移记录 +----------+ | count(1) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) *****************测试使用的表*********************************************************************** 创建一个基础测试表: create table `tb` ( `id` int(10) unsigned not null auto_increment comment '表主键', `pid` int(10) unsigned not null comment '产品id', `price` decimal(15,2) not null comment '单价', `num` int(11) not null comment '购买数量', `uid` int(10) unsigned not null comment '客户id', `atime` datetime not null comment '下单时间', `utime` int(10) unsigned not null default 0 comment '修改时间', `isdel` tinyint(4) not null default '0' comment '软删除标识', ) ; 插入数据： insert into tb(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,current_timestamp()); insert into tb(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,'2016-05-01 00:00:00'); insert into tb(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,'2017-05-01 00:00:00'); insert into tb(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89757,'2018-05-01 00:00:00'); insert into tb(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89756,'2015-05-01 00:00:00'); insert into tb(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89756,'2016-05-01 00:00:00'); insert into tb(`pid`,`price`,`num`,`uid`,`atime`) values(1,12.23,1,89756,'2017-05-01 00:00:00'); ************************************插入大量的数据(建议百万以上)************************************* insert into `tb`(`pid`,`price`,`num`,`uid`,`atime`) select `pid`,`price`,`num`,`uid`,`atime` from `tb`; ****注意，如果要删除自增长的主键id(修改过程中，建议该库改为只读)，如下操作： alter table tb change id id int(10)； #先删除自增长 alter table tb drop primary key；#删除主建 alter table tb change id id int not null auto_increment; #如果想重新设置为自增字段 alter table tb auto_increment=1; #自增起始
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。