linux mysql 分区表_MySQL分区表操作

又到了要做表分区的时候了，写个博客记录一下。

1. 背景

MySQL版本是5.5，5.1以后就开始支持分区了，所以软件上没有问题。

1. show variables like "%version%"

'version', '5.5.11-log'

'innodb_version', '1.1.6'

------------

2. show variables like "%partition%"

'have_partitioning':'YES'

要操作的是一张log表。我知道有人在问为啥log表要放在mysql中，其实我就是为了方便，一方面做统计so easy,另一方面如果这个不放MySQL中，那我真的就没法学习MySQL了，因为貌似别的数据更没有理由放在这里，要么cassandra，要么redis，要么文件。制造一个需求也不容易呀兄弟，所以这样也可以说是为了个人兴趣吧。

看一下这张表的状况:

1. 数据

SHOW TABLE STATUS FROM dictCourse like "liveLog20160401"

-》4G数据大小

-》4G索引大小

-》2800万条记录

2. 看一下是否之前有分区

select partition_name from liveLog20160401.partitions

不过没有权限~_~

3. 查看一下执行计划中是否有分区信息

explain partitions select * from liveLog20160401 where sendTime < '2016-05-13' and sendTime>'2016-05-14';

-》partitions：null

所以看上去是没有的

2. 执行

目前看来直接在原表上进行修改是不可能的。所以我想的是写入另一张新表中，然后把名字改为旧表的名。

以下是建表语句，另外的一些字段我已经删除了，因为它们与要分析的几个问题不太相关。

CREATE TABLE `liveLog_temp` (

`id` bigint unsigned NOT NULL AUTO_INCREMENT,

`userId` varchar(100) NOT NULL,

`sendTime` datetime NOT NULL DEFAULT '1000-01-01',

`uniqMd5` varchar(33) NOT NULL,

PRIMARY KEY (`id`, `sendTime`),

UNIQUE KEY `uniqNum_UNIQUE` (`uniqMd5`,`sendTime`),

KEY `i_uid` (`userId`),

)

ENGINE=InnoDB

AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

partition by list (month(sendTime))

(

partition p1 values in (1),

partition p2 values in (2),

partition p3 values in (3),

partition p4 values in (4),

partition p5 values in (5),

partition p6 values in (6),

partition p7 values in (7),

partition p8 values in (8),

partition p9 values in (9),

partition p10 values in (10),

partition p11 values in (11),

partition p12 values in (12)

)

;

执行失败有以下一些情况：

我一开始使用的是mac上的MySQLWorkbench,使用的端模式是5.5(我切成了5.6也会有问题)。报的异常是：

partition by list (month(sendTime))这句话前面一直有一个叉，提示

Syntax error: 'partition' (identifier) is not valid input at this position.因为有异常所以压根不让执行了。

搜过了一下其实是MySQLWorkbench这个软件自身的编辑器问题。我改用了linux下的shell客户端以及windows上的 MySQL Query Browser，执行都没有问题。

另一个写这个语句时遇到的问题是

PRIMARY KEY (`id`, `sendTime`),

UNIQUE KEY `uniqNum_UNIQUE` (`uniqMd5`,`sendTime`),

这两个语句，都需要带上分区的字段，否则会报

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

之前我的sendTime是TIMESTAMP类型的，一直报失败。后来查了一下，分区只能支持所有的整数类型日期只有date和dateTime,以及字符非text和blob类型。所以把sendTime改成了datetime类型。所以我还得去试一下数据库是否能写

3. 效果

直接插表：

insert into newTableName select * from oldTableName;

执行了还是很久的。

看一下执行计划

explain partitions select * FROM dictCourse.liveLog_temp;

partitions:'p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12'

已经有了这样的分区计划了.

再看一下数据情况

show TABLE status FROM dictCourse like 'liveLog_temp';

几千万条记录10分钟左右迁移完了。