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