MySQL下的表分区简述

rger

MySQL表分区就是把一张表根据设定好的条件下把表切分成若干个小表相互之间，在MySQL的5.1版本以后就开始支持表分区的功能，在使用表的分区后会使MySQL中大表在平时查询统计时性能提升。使用MySQL的表分区有以下这些优点：

1 2 3 4 5

1.与单个磁盘或文件系统分区相比，可以存储更多的数据 2.很容易就能删除不用或者过时的数据 3.一些查询可以得到极大的优化 4.涉及到 SUM()/COUNT() 等聚合函数时，可以并行进行 5.IO吞吐量更大

在早期的MySQL版本中可以查询SHOW VARIABLES LIKE 'have_partitioning';变量参数来得知系统中是否会支持表分区而在MySQL5.6的版本后就不在使用该参数变量，默认都是支持分区功能，但是并不是所有的数据引擎都支持表分区的，其中目前可以使用表发分区功能引擎有：InnoDB、MyISAM、MEMORY另外还有BLACKHOLE引擎也是支持分区，但是BLACKHOLE引擎中的数据一切为空所以在使用分区并没有实际的意义，而使用较为多的就是InnoDB和MyISAM的引擎表上做分区，在这里需要注意的是在使用的时候需要注意在原先是什么表引擎分区的时候就必须是用什么表引擎，其次在使用InnoDB引擎表时建议开启innodb_file_per_table（独立表空间）这样表空间文件也是独立的便于管理，如果是在建立表分区时没有指定分区所在的路径默认是在datedir目录下的库文件夹下把表的数据文件下切分创建出许多小文件，当然在建表或修改表的时候如果有指定表文件的数据目录那么还可以使一张表分区后把数据放在不同的磁盘中，最后在使用的做分区的字段上需要定义成主键，如果原先有一个主键那么该处就会结合之前的主键形成联合主键，同时在该字段上因为主键原因该字段应该不为NULL，建议在建立表后再ALTER表添加修改表分区，这样可以确保修改创建表分区时不容易出错，因为在MySQL中表分区不同于Oracle中可以在建表后分区，分区时就会按照所给出的条件把数据划分至各个分区文件中，当然在MySQL中分区后还可以在分区的基础上再进行子分区，但是一般情况下一般很少使用，最后需要注意的一点如果删除分区时，也会丢失数据的，所以在删除表分区时需要慎重。其中在MySQL下的分区类型有以下四种：

1 2 3 4

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。 LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。 KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

其中我们比较常用的就是分区方式就是按字段中的时间来分区那么就选用RANGE分区方式来进行分区在这里就说一说最常用的使用时间条件来进行表分区，按时间分区可以按照年月日等条件用来分区，因为是按时间条件分区所以在这里就选用RANGE分区，在这里分区的切条件判断有三种：

1 2 3

LESS THAN：如果是数值就是小于等于，时间则是小于 LESS THAN MAX:不等于 IN:包含于某某区间

而切分时的条件可以用day()、to_days()等相关时间函数都可以，需要注意的是必须是返回值是整形的。如下有一张表

其中按时间字段date按天分区，首先需要把date字段改成主键，后添加分区切分规则

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

ALTER TABLE `employee_tbl` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `date`); -- 之前id是主键，所以这里第2主键为联合主键 ALTER TABLE `employee_tbl` ADD PRIMARY KEY (`date`); -- 这里为了便于管理分区名都已“p时间”来命名 ALTER TABLE `employee_tbl` PARTITION BY RANGE (to_days(date)) (     PARTITION `p20171101` VALUES LESS THAN (to_days('20171101')),     PARTITION `p20171102` VALUES LESS THAN (to_days('20171102')),     PARTITION `p20171103` VALUES LESS THAN (to_days('20171103')),     PARTITION `p20171104` VALUES LESS THAN (to_days('20171104')),     PARTITION `p20171105` VALUES LESS THAN (to_days('20171105')),     PARTITION `p20171106` VALUES LESS THAN (to_days('20171106')),     PARTITION `p20171107` VALUES LESS THAN (to_days('20171107')),     PARTITION `p20171108` VALUES LESS THAN (to_days('20171108')),     PARTITION `p20171109` VALUES LESS THAN (to_days('20171109')),     PARTITION `p20171110` VALUES LESS THAN (to_days('20171110')) );

这样按天进行分区就创完毕，当然如果感觉不够满意还可以重新分区,后期添加分区也很简单：

1	ALTER TABLE employee_tbl ADD PARTITION (PARTITION p20171111 VALUES LESS THAN (TO_DAYS ('2017-11-11')));

删除分区：

1	ALTER TABLE employee_tbl DROP PARTITION p20171101;

最后我们可以通过查询MySQL的系统字典库得知所有的分区详情信息

1 2 3 4 5 6

SELECT     * FROM     information_schema. PARTITIONS t WHERE     t.PARTITION_NAME IS NOT NULL

在分区创建后可以通过过程和事件控制自动增加表分区