将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)