生产库mysql大表分区方案

前言：在生产库下对一个1000万行的表进行分区，经过分析，根据ctime(datetime)字段RANGE型按月进行分区．该表特点：
１．闲时每分钟产生约100行数据
２．只有insert修改，select查询，没有delete,update,replace等修改
３．有一列自增列
4 .   数据库版本Percona-Server-5.1.57-rel12.8-232-Linux-x86_64

从未分区到分区的迁移方案．如下：

db_name="production"

＃　找出当前位置，导出当前的表
mark_id=`mysql  $db_name -e "select max(id) from production_tb;"`
mysqldump  --default-character-set=utf8  $db_name  production_tb > production_tb.sql

#   建立分区表 , 禁止索引
mysql --default-character-set=utf8  $db_name <CREATE TABLE production_tb_partition (
  id int(8) NOT NULL AUTO_INCREMENT,
  ctime datetime NOT NULL,
  ip bigint(20) DEFAULT NULL,
  country varchar(255) DEFAULT NULL,
  http_host varchar(100) DEFAULT NULL,
  http_referer varchar(255) NOT NULL,
  server_id smallint(4) NOT NULL,
  source varchar(20) DEFAULT NULL,
  medium varchar(20) DEFAULT NULL,
  term varchar(20) DEFAULT NULL,
  content varchar(20) DEFAULT NULL,
  campaign varchar(40) DEFAULT NULL,
  code varchar(10) DEFAULT NULL,
  sub_id varchar(10) DEFAULT NULL,
  session_id varchar(50) NOT NULL,
  keyword varchar(255) NOT NULL,
  PRIMARY KEY (id,ctime),　　　＃分区列必须是主键
  KEY idx_source_subid (source,sub_id),
  KEY idx_reg_time (ctime),
  KEY idx_source (source),
  KEY idx_click_ip (ip)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(ctime))
(
PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),
PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')) ,
PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')) ,
PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')) ,
PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')) ,
PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')) ,
PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')) ,
PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')) ,
PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')) ,
PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')) ,
PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),
PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),
PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE );
ALTER TABLE production_tb_partition DISABLE KEYS;
EOF

＃　dump出来的文件, 只保留insert语句，更改表名
grep -i insert production_tb.sql > production_tb_partition.sql
sed -i -e "s%production_tb%production_tb_partition%g"  production_tb_partition.sql
time mysql --default-character-set=utf8  $db_name <  production_tb_partition.sql

＃　分区表重命名为正式表
rename table production_tb to  production_tb_nopart;
rename table production_tb_partition to production_tb;

#  在会话中锁定正式表只读,将新数据库导入正式表
LOCK TABLES　production_tb  READ;
insert into  production_tb
  select  * from production_tb_nopart where id > $mark_id;
unlock tables;

＃ enable正式表的key
ALTER TABLE production_tb_partition ENABLE KEYS;

备注 :
1.效率问题
  a.直接alter为分区表 (未分区前ctime不是主键，无法测试)
  b.新表 insert前 未 disable key   72min
  c.新表insert前 disable key    10min (本方案)
2. 之前效率问题测试引发的数据存储问题
  explain同一个查询，a方案，b方案，c方案 ,扫描到的行数不同．
　　a方案 424964 rows
      b方案  322415 rows
      c方案  231766 rows