mysql 5.6 表分区_mysql 5.6 版本，对在使用的表进行分区

一、分区前准备

1、先分析看看哪些字段查询比较多，怎样分区有利于提高查询效率。

2、分区是有限制的，分区字段必须是主键之一，而且如果对时间分区，时间字段尽量 date 或datetime类型。

二、删除以前没有用的分区，只删除分区不删除表记录

alter table t_user_video remove partitioning   -- 删除分区

对时间进行分区

ALTER TABLE `t_user_video`

PARTITION by RANGE  (to_days(create_time))

(

PARTITION p1 VALUES LESS THAN (to_days('2015-10-01')),

PARTITION p2 VALUES LESS THAN (to_days('2015-11-01')),

PARTITION p3 VALUES LESS THAN (to_days('2015-12-01')),

PARTITION p4 VALUES LESS THAN (to_days('2016-01-01')),

PARTITION p5 VALUES LESS THAN (to_days('2016-02-01')),

PARTITION p6 VALUES LESS THAN (to_days('2016-03-01')),

PARTITION p7 VALUES LESS THAN (to_days('2016-04-01')),

PARTITION p8 VALUES LESS THAN (to_days('2016-05-01')),

PARTITION p9 VALUES LESS THAN (to_days('2016-06-01')),

PARTITION p10 VALUES LESS THAN (to_days('2016-07-01')),

PARTITION p11 VALUES LESS THAN (to_days('2016-08-01')),

PARTITION p12 VALUES LESS THAN (to_days('2016-09-01')),

PARTITION p13 VALUES LESS THAN (to_days('2016-10-01')),

PARTITION p14 VALUES LESS THAN (to_days('2016-11-01')),

PARTITION p15 VALUES LESS THAN (to_days('2016-12-01')),

PARTITION p16 VALUES LESS THAN (to_days('2017-01-01')),

PARTITION p17 VALUES LESS THAN (to_days('2017-02-01')),

PARTITION p18 VALUES LESS THAN (to_days('2017-03-01')),

PARTITION p19 VALUES LESS THAN (to_days('2017-04-01')),

PARTITION p20 VALUES LESS THAN (to_days('2017-05-01')),

PARTITION p21 VALUES LESS THAN (to_days('2017-06-01')),

PARTITION p22 VALUES LESS THAN (to_days('2017-07-01')),

PARTITION p23 VALUES LESS THAN (to_days('2017-08-01')),

PARTITION p24 VALUES LESS THAN (to_days('2017-09-01')),

PARTITION p25 VALUES LESS THAN (to_days('2017-10-01')),

PARTITION p26 VALUES LESS THAN (to_days('2017-11-01')),

PARTITION p27 VALUES LESS THAN (to_days('2017-12-01')),

PARTITION p28 VALUES LESS THAN (to_days('2018-01-01')),

PARTITION p29 VALUES LESS THAN (to_days('2018-02-01')),

PARTITION p30 VALUES LESS THAN (to_days('2018-03-01')),

PARTITION p31 VALUES LESS THAN (to_days('2018-04-01')),

PARTITION p32 VALUES LESS THAN (to_days('2018-05-01')),

PARTITION p33 VALUES LESS THAN (to_days('2018-06-01')),

PARTITION p34 VALUES LESS THAN (to_days('2018-07-01')),

PARTITION p35 VALUES LESS THAN (to_days('2018-08-01')),

PARTITION p36 VALUES LESS THAN (to_days('2018-09-01')),

PARTITION p37 VALUES LESS THAN (to_days('2018-10-01')),

PARTITION p38 VALUES LESS THAN (to_days('2018-11-01')),

PARTITION p39 VALUES LESS THAN (to_days('2018-12-01')),

PARTITION p40 VALUES LESS THAN MAXVALUE

);

对id进行分区

ALTER TABLE `t_user_plan_item`  PARTITION BY RANGE (userplan_id) ( PARTITION p0 VALUES LESS THAN (500000),  PARTITION p1 VALUES LESS THAN (1000000),  PARTITION p2 VALUES LESS THAN (1500000),  PARTITION p3 VALUES LESS THAN (2000000),  PARTITION p4 VALUES LESS THAN (2500000),  PARTITION p5 VALUES LESS THAN (3000000),  PARTITION p6 VALUES LESS THAN (3500000), PARTITION p7 VALUES LESS THAN (4000000), PARTITION p8 VALUES LESS THAN (4500000), PARTITION p9 VALUES LESS THAN (5000000), PARTITION p10 VALUES LESS THAN (5500000), PARTITION p11 VALUES LESS THAN (6000000), PARTITION p12 VALUES LESS THAN (6500000), PARTITION p13 VALUES LESS THAN (7000000), PARTITION p14 VALUES LESS THAN (7500000), PARTITION p15 VALUES LESS THAN (8000000), PARTITION p16 VALUES LESS THAN (8500000), PARTITION p17 VALUES LESS THAN (9000000), PARTITION p18 VALUES LESS THAN (9500000), PARTITION p19 VALUES LESS THAN (10000000), PARTITION p20 VALUES LESS THAN (10500000), PARTITION p21 VALUES LESS THAN (11000000), PARTITION p22 VALUES LESS THAN (11500000), PARTITION p23 VALUES LESS THAN (12000000), PARTITION p24 VALUES LESS THAN (12500000), PARTITION p25 VALUES LESS THAN (13000000), PARTITION p26 VALUES LESS THAN (13500000), PARTITION p27 VALUES LESS THAN (14000000), PARTITION p28 VALUES LESS THAN (14500000), PARTITION p29 VALUES LESS THAN (15000000), PARTITION p30 VALUES LESS THAN MAXVALUE ); 三、对现网分区一定要知道用户量什么时候多，什么时候少，选用户量少的时候分区，避免带来不必要的麻烦。