mysql数据库 大表分区瘦身实践

DROP table IF EXISTS yys_shangpin_t;
CREATE TABLE `yys_shangpin_t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商品id',
`sid` int(10) unsigned NOT NULL COMMENT '同一个商品',
`cateid` smallint(6) unsigned DEFAULT NULL COMMENT '所属栏目ID',
`brandid` smallint(6) unsigned DEFAULT NULL COMMENT '所属品牌ID',
`title` varchar(100) DEFAULT NULL COMMENT '商品标题',
`title_style` varchar(100) DEFAULT NULL,  
PRIMARY KEY (`id`), 
KEY `sid` (`sid`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='商品表';
PARTITION BY RANGE COLUMNS(id) (
  PARTITION p0 VALUES LESS THAN (500),
  PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (3000),
  PARTITION p4 VALUES LESS THAN (4000),   
  PARTITION p15 VALUES LESS THAN MAXVALUE
);

2.备份整库，在原表上操作分区。如果有问题就恢复整库。
保险上也还可以。用这个了。
备份原库，直接远程用secure CRT登录数据库，
每个表进行一下，分区命令。

destoon_buy_data

ALTER TABLE destoon_buy_data
partition by range(itemid)
(
partition p0 values less than (100000),
partition p1 values less than (200000),
partition p2 values less than (300000),
partition p3 values less than maxvalue
);


destoon_sell_data

ALTER TABLE destoon_sell_data
partition by range(itemid)
(
partition p0 values less than (100000),
partition p1 values less than (200000),
partition p2 values less than (300000),
partition p3 values less than (400000),
partition p4 values less than (500000),
partition p5 values less than (600000),
partition p6 values less than (700000),
partition p7 values less than (800000),
partition p8 values less than (900000),
partition p9 values less than (1000000),
partition p10 values less than maxvalue
);


destoon_quote_data


ALTER TABLE destoon_quote_data
partition by range(itemid)
(
partition p0 values less than (100000),
partition p1 values less than (200000),
partition p2 values less than (300000),
partition p3 values less than maxvalue
);

destoon_news_data

ALTER TABLE destoon_news_data
partition by range(itemid)
(
partition p0 values less than (100000),
partition p1 values less than (200000),
partition p2 values less than maxvalue
);