mysql 普通表转分区表_分区表mysql 普通表转分区表

1.按照现有表结构,创建一个临时表,用于分区表转换
mysql> desc ClientActionTrack;
+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| sn            | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| clientSn      | int(11)       | YES  |     | NULL    |                |
| ip            | varchar(32)   | YES  |     | NULL    |                |
| url           | varchar(1000) | YES  |     | NULL    |                |
| httpMethod    | varchar(100)  | YES  |     | NULL    |                |
| requestParams | text          | YES  |     | NULL    |                |
| requestHeader | varchar(2000) | YES  |     | NULL    |                |
| pageUrl       | varchar(500)  | YES  |     | NULL    |                |
| sessionId     | varchar(100)  | YES  |     | NULL    |                |
| startTime     | datetime      | YES  |     | NULL    |                |
| completeTime  | datetime      | YES  |     | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)
 
mysql>  select clientSn,httpMethod,startTime from ClientActionTrack limit 5;
+----------+------------+---------------------+
| clientSn | httpMethod | startTime           |
+----------+------------+---------------------+
|     NULL | POST       | 2015-09-14 10:54:57 |
|     NULL | POST       | 2015-09-14 10:54:57 |
|     NULL | POST       | 2015-09-14 10:54:57 |
|     NULL | POST       | 2015-09-14 10:54:57 |
|     NULL | POST       | 2015-09-14 10:54:57 |
+----------+------------+---------------------+
5 rows in set (0.00 sec)
 
 
mysql> CREATE TABLE `tmp01` (
    ->   `sn` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `clientSn` int(11) DEFAULT NULL,
    ->   `ip` varchar(32) DEFAULT NULL,
    ->   `url` varchar(1000) DEFAULT NULL COMMENT 'request url',
    ->   `httpMethod` varchar(100) DEFAULT NULL COMMENT 'http method',
    ->   `requestParams` text COMMENT '请求参数',
    ->   `requestHeader` varchar(2000) DEFAULT NULL COMMENT '请求头信息',
    ->   `pageUrl` varchar(500) DEFAULT NULL COMMENT '页面Url',
    ->   `sessionId` varchar(100) DEFAULT NULL COMMENT 'session的Id',
    ->   `startTime` datetime DEFAULT NULL COMMENT '访问开始时间',
    ->   `completeTime` datetime DEFAULT NULL COMMENT '访问完成时间',
    ->   PRIMARY KEY (`sn`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=302753 DEFAULT CHARSET=utf8 COMMENT='用户访问记录表'
    -> partition by RANGE (startTime)  (PARTITION  p20150101 values less than (20150102));  
	
ERROR 1659 (HY000): Field 'startTime' is of a not allowed type for this type of partitioning
 
mysql> select min(startTime),max(startTime) from ClientActionTrack;
+---------------------+---------------------+
| min(startTime)      | max(startTime)      |
+---------------------+---------------------+
| 2015-09-14 10:54:57 | 2015-10-27 15:12:00 |
+---------------------+---------------------+
1 row in set (0.97 sec)
 
CREATE TABLE `tmp01` (
  `sn` bigint(20) NOT NULL AUTO_INCREMENT,
  `clientSn` int(11) DEFAULT NULL,
  `ip` varchar(32) DEFAULT NULL,
  `url` varchar(1000) DEFAULT NULL COMMENT 'request url',
  `httpMethod` varchar(100) DEFAULT NULL COMMENT 'http method',
  `requestParams` text COMMENT '请求参数',
  `requestHeader` varchar(2000) DEFAULT NULL COMMENT '请求头信息',
  `pageUrl` varchar(500) DEFAULT NULL COMMENT '页面Url',
  `sessionId` varchar(100) DEFAULT NULL COMMENT 'session的Id',
  `startTime` datetime DEFAULT NULL COMMENT '访问开始时间',
  `completeTime` datetime DEFAULT NULL COMMENT '访问完成时间',
  PRIMARY KEY (`sn`,`startTime`)
) ENGINE=InnoDB AUTO_INCREMENT=302753 DEFAULT CHARSET=utf8 COMMENT='用户访问记录表'
partition by RANGE (TO_DAYS(startTime))  (PARTITION  p20150914 values less than (736221)); 
 
主键列增加 数据被放大
 
2.批量生成分区表
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
date=`echo $1 | tr -d '-'`
date1=`echo $2 | tr -d '-'`
date2=`echo $1`
date_end=`get_date $2 +1 | tr -d '-'`
while :
    do
      #echo $date
      date3=`get_date $date +1`
      var=`perl ./get_date.pl $date3`
     echo "alter table tmp01 add partition (partition p$date values less than ($var));"
      date2=`get_date $date +1`
      date=`echo $date2 | tr -d '-'`
      #echo $date3
if [ "$date" = "$date_end" ]
       then
        exit
fi
done
You have mail in /var/spool/mail/oracle
oadb01:/home/oracle/mysql> sh ./p1.sh 20150914 20151231 >p1.sql
 
 
3.mysql> insert into tmp01 select * from ClientActionTrack;  ---插入数据到临时表
 
 
4.删除旧表,rename tmp01为ClientActionTrack;
mysql> drop table ClientActionTrack;
Query OK, 0 rows affected (0.24 sec)
 
mysql> rename table  tmp01 to ClientActionTrack;
 
5.访问分区:
 
 
以前的查询方式:
select min(startTime),max(startTime) from ClientActionTrack where starttime >='2015-10-02 00:00:00' and starttime<='2015-10-02 23:59:59'
 
改造成分区后:
 
SELECT min(startTime),max(startTime) FROM ClientActionTrack PARTITION (p20151002);