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

数据库 2024/11/9 佚名

3 2 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; #自增起始

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。

MySQl数据备份,MySQl,分区表,MySQl数据备份转移

标签：

MySQl数据备份,MySQl,分区表,MySQl数据备份转移

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

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

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

暂无“通过实例学习MySQL分区表原理及常用操作”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

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

MySQl数据备份,MySQl,分区表,MySQl数据备份转移

在OneProxy的基础上实行MySQL读写分离与负载均衡

mysql installer community 8.0.16.0安装配置图文教程

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

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

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

MySQl数据备份,MySQl,分区表,MySQl数据备份转移

在OneProxy的基础上实行MySQL读写分离与负载均衡

mysql installer community 8.0.16.0安装配置图文教程

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

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存