本文主要向大家介绍了MySQL数据库之MySQL 分区表原理及数据备份转移实战 ，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。
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`
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`; #查看分区情况 +----+-------------+-------+-----------"本文由职坐标整理并发布，希望对同学们学习MySQL有所帮助
