Mysql分区表Partition

一、背景 话说风和日丽的一天，为提高随着业务增长的大表（3510449行吧）的访问效率，于是决定对表分区，记录如下。

二、实操 结合业务，若干条记录会集中在一个日期，查询时也往往只查询一个日期内的数据，于是选取分区字段为时间。

创建分区比如 CREATE TABLE message_all ( id int(10) NOT NULL AUTO_INCREMENT, …… createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’ PRIMARY KEY (id,createtime) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (YEAR(createtime)) (PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB, PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB, PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB, PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

不过我们表已经有了当然不能这么建，除非你想导一次数据。 如下操作： 1、 ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime)) ( PARTITION p2015 VALUES LESS THAN (to_days(‘2016-01-01’)), PARTITION p2016 VALUES LESS THAN (to_days(‘2017-01-01’)), PARTITION p2017 VALUES LESS THAN (to_days(‘2018-01-01’)), PARTITION p2018 VALUES LESS THAN MAXVALUE ); 或者 2、ALTER TABLE message_all PARTITION BY RANGE (YEAR(createtime)) ( PARTITION p2015 VALUES LESS THAN (YEAR(‘2016-01-01’)) ); 然后追加。 ALTER TABLE message_all ADD PARTITION ( PARTITION p2016 VALUES LESS THAN (YEAR(‘2017-01-01’)), PARTITION p2017 VALUES LESS THAN (YEAR(‘2018-01-01’)), PARTITION p2018 VALUES LESS THAN MAXVALUE );

这里会有几种错误情况： 1、ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime)) ; [Err] 1492 - For RANGE partitions each partition must be defined 解释：必须指定至少一个分区。

2、[Err] 1492 - A PRIMARY KEY must include all columns in the table’s partitioning function 解释：分区字段必须是主键之一。

3、[Err] 1492 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed 解释：分区字段为timestamp，换成datetime。

4、[Err] 1526 - Table has no partition for value xxxx 解释：用追加方式第一次必须覆盖目前所有数据。

总结： 1、创建时必须指定至少一个分区。 2、key必须为主键之一。 3、RANGE处必须为INT型，时间字段用函数转——YEAR()、YEARWEEK()、TO_DAYS()。 4、THAN处必须为INT型，时间字段用函数转——TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()。 5、它就是以两个INT比大小划分的文件。 6、所有ENGINE必须一样。 7、范围分区添加只能在最大值后面追加。 8、分区是有上限的貌似1024个。

用到的其他操作 1、删除分区（直接扔掉分区文件，数据也没了） ALTER TABLE message_all DROP PARTITION p2016; 2、清空分区数据 ALTER TABLE message_all TRUNCATE PARTITION p2017; 3、重定义（可实现：分区拆分、合并、重命名） ALTER TABLE message_all REORGANIZE PARTITION p201601,p201602,p201603,p201604 INTO ( PARTITION p2016012 VALUES less than(TO_DAYS(‘2016-03-01’)), PARTITION p2016034 VALUES less than(TO_DAYS(‘2016-05-01’)) );

检查/查看你的分区

1、SHOW TABLE STATUS LIKE ‘message_all’;

2、SELECT * FROM information_schema.partitions WHERE table_name=’message_all’;

3、SHOW CREATE TABLE message_all;

4、EXPLAIN SELECT COUNT(1) FROM message_all WHERE createtime>= ‘2016-01-01’ AND createtime < ‘2016-12-30’;如果用到了分区partitions里会有显示。

5、指定分区查 SELECT COUNT(1) FROM message_all PARTITION (p2016) 表别名 WHERE ……;

到这里就结束啦，土豆白。

一些概念

水平分区Partition有以下几种模式

Range（范围） – 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980’s）的数据，90年代（1990’s）的数据以及任何在2000年（包括2000年）后的数据。

Hash（哈希） – 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。

Key（键值） – 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

List（预定义列表） – 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

Composite（复合模式） - 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。