MySQL 分区表原理及数据备份转移实战

江南才子

　　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分区。建议在多磁盘上使用。
　　

　　  5、查看是否有支持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.sql

　　

　　  修改表名，导入数据，测试下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*分析分区：
　　读取并保存分区的键分布。

ALTER TABLE tb03 analyze partition p0,p1,p2,p3,p4,p5,p6,p7;

　　

　　
　　 *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; #自增起始值

[MySQL] MySQL 分区表原理及数据备份转移实战

相关帖子

MySQL让人又爱又恨的多表查询

MySQL存储过程图文实例讲解

MySQL由浅入深掌握连接查询

MySQL数据库触发器从小白到精通

一篇带给你MySQL索引知识详解

MySQL视图和索引专篇精讲

阿里面试MySQL死锁问题的处理

mysql中drop、truncate与delete的区别详析

HarmonyOS应用开发：鸿蒙Js实战，计算器功能开发！

diskgenius分区固态硬盘

江南才子 LV4