前言：在生产库下对一个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
阅读(4650) | 评论(0) | 转发(0) |
