将mysql非分区表转换为分区表

查看表的分布状况 mysql> select count(*) from justin; +----------+ | count(*) | +----------+ | 5845246 | +----------+ 1 row in set (0.00 sec) mysql> select month(create_time),count(*) from justin group by month(create_time); +-----------------------+----------+ | month(create_time) | count(*) | +-----------------------+----------+ | 1 | 1128520 | | 11 | 1574965 | | 12 | 3141750 | +-----------------------+----------+ 3 rows in set (6.93 sec) 考虑以create_time为分区键建立分区表 第一步 创建中间表，以主键id和分区列为联合主键 CREATE TABLE `temp_justin` ( `id` bigint(1) NOT NULL AUTO_INCREMENT COMMENT '流水号,自增', `create_time` datetime DEFAULT NULL COMMENT '订单日志创建时间(建立索引)', PRIMARY KEY (`id`,`create_time`), ) ENGINE=MyISAM AUTO_INCREMENT=6000000 DEFAULT CHARSET=utf8; 表已经存在580多万记录并且不断在增长，因此中间表初始的id值设置成6000000 增加分区，以月为单位 alter table temp_justin partition by range(to_days(create_time)) ( partition p1012 values less than (to_days('2011-01-01')), partition p1101 values less than (to_days('2011-02-01')), partition p1102 values less than (to_days('2011-03-01')), partition p1103 values less than (to_days('2011-04-01')), partition p1104 values less than (to_days('2011-05-01')), partition p1105 values less than (to_days('2011-06-01')), partition p1106 values less than (to_days('2011-07-01')), partition p1107 values less than (to_days('2011-08-01')), partition p1108 values less than (to_days('2011-09-01')), partition p1109 values less than (to_days('2011-10-01')), partition p11010 values less than (to_days('2011-11-01')), partition p11011 values less than (to_days('2011-12-01')), partition p11012 values less than (to_days('2012-01-01')) ); 第二步 重命名表 Alter table justin rename to justin_bak_110113; Alter table temp_justin rename to justin; 第三步 同步数据 Insert into justin select * from temp_justin; 表里已经存在将近600万条记录，如此批量导入数据会对数据库性能影响很大。 每一万条提交一次，sleep 2s ，53万数据总耗时2 min 39.67 sec。 mysql> create procedure cp_data() -> begin -> declare i int; -> set i=0; -> while i<60 do -> insert into justin -> select * from justin_bak_110113 -> where id >= i*10000 and id <(i+1)*10000; -> set i=i+1; -> select sleep(2); -> end while; -> end|| Query OK, 0 rows affected (0.04 sec) mysql> mysql> delete from justin; -> || Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call cp_data(); +----------+ | sleep(2) | +----------+ | 0 | +----------+ 1 row in set (2 min 39.67 sec) Query OK, 0 rows affected (2 min 39.67 sec) mysql> select count(*) from justin; +----------+ | count(*) | +----------+ | 525031 | +----------+ 1 row in set (0.00 sec) 查看执行计划，使用了分区扫描 mysql> explain -> select count(*) from justin where create_time -> <='2011-01-13' and create_time>'2011-01-04'; +----+-------------+--------------------+-------+---------------+---------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+-------+---------------+---------+---------+------+--------+--------------------------+ | 1 | SIMPLE | justin | index | NULL | PRIMARY | 16 | NULL | 525031 | Using where; Using index | +----+-------------+--------------------+-------+---------------+---------+---------+------+--------+--------------------------+ 1 row in set (0.00 sec) mysql> explain -> partitions -> select count(*) from justin where create_time -> <='2011-01-13' and create_time>'2011-01-04'; +----+-------------+--------------------+-------------+-------+---------------+---------+---------+------+--------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+-------------+-------+---------------+---------+---------+------+--------+--------------------------+ | 1 | SIMPLE | justin | p1012,p1101 | index | NULL | PRIMARY | 16 | NULL | 525031 | Using where; Using index | +----+-------------+--------------------+-------------+-------+---------------+---------+---------+------+--------+--------------------------+ 1 row in set (0.00 sec)