mysql添加分区健_MySQL添加分区添加索引

添加新用户    今天在为mysql添加新用户时，找到下面这条指令：    insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));       然后退出    mysql>\q    重新登录    $mysql -u test -p    Enter password:

1、创建表表user(id，username，password)，在name 上建立 唯一约束 Alter table user add unique(username);

CREATE TABLE `rpt_all_order_list_d` ( `add_time` datetime DEFAULT NULL COMMENT '添加时间', `account_no` varchar(32) DEFAULT NULL COMMENT '会员ID', `order_id` varchar(32) DEFAULT NULL COMMENT '订单ID', `fin_product_id` varchar(32) DEFAULT NULL COMMENT '金融产品ID', `trade_type` bigint(20) DEFAULT NULL COMMENT '1:申购;', `status_code` varchar(1) DEFAULT NULL COMMENT 'Y:成功，N:失败', `trade_money` decimal(16,2) DEFAULT NULL COMMENT '交易金额', KEY `index_add_time` (`add_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、往现有的表中添加分区

ALTER TABLE `rpt_all_order_list_d` PARTITION BY RANGE (TO_DAYS(add_time)) (PARTITION p0 VALUES LESS THAN (TO_DAYS('2015-01-01')), PARTITION p1 VALUES LESS THAN (TO_DAYS('2015-02-01')) , PARTITION p2 VALUES LESS THAN (TO_DAYS('2015-03-01')) , PARTITION p3 VALUES LESS THAN (TO_DAYS('2015-04-01')) , PARTITION p4 VALUES LESS THAN (TO_DAYS('2015-05-01')) , PARTITION p5 VALUES LESS THAN (TO_DAYS('2015-06-01')) , PARTITION p6 VALUES LESS THAN (TO_DAYS('2015-07-01')) , PARTITION p7 VALUES LESS THAN (TO_DAYS('2015-08-01')) , PARTITION p8 VALUES LESS THAN (TO_DAYS('2015-09-01')) , PARTITION p9 VALUES LESS THAN (TO_DAYS('2015-10-01')) , PARTITION p10 VALUES LESS THAN (TO_DAYS('2015-11-01')), PARTITION p11 VALUES LESS THAN (TO_DAYS('2015-12-01')), PARTITION p12 VALUES LESS THAN (TO_DAYS('2016-01-01')), PARTITION p13 VALUES LESS THAN (TO_DAYS('2016-02-01')) , PARTITION p14 VALUES LESS THAN (TO_DAYS('2016-03-01')) , PARTITION p15 VALUES LESS THAN (TO_DAYS('2016-04-01')) , PARTITION p16 VALUES LESS THAN (TO_DAYS('2016-05-01')) , PARTITION p17 VALUES LESS THAN (TO_DAYS('2016-06-01')) , PARTITION p18 VALUES LESS THAN (TO_DAYS('2016-07-01')) , PARTITION p19 VALUES LESS THAN (TO_DAYS('2016-08-01')) , PARTITION p20 VALUES LESS THAN (TO_DAYS('2016-09-01')) , PARTITION p21 VALUES LESS THAN (TO_DAYS('2016-10-01')) , PARTITION p22 VALUES LESS THAN (TO_DAYS('2016-11-01')), PARTITION p23 VALUES LESS THAN (TO_DAYS('2016-12-01')), PARTITION p24 VALUES LESS THAN (TO_DAYS('2017-01-01')), PARTITION p25 VALUES LESS THAN (TO_DAYS('2017-02-01')) , PARTITION p26 VALUES LESS THAN (TO_DAYS('2017-03-01')) , PARTITION p27 VALUES LESS THAN (TO_DAYS('2017-04-01')) , PARTITION p28 VALUES LESS THAN (TO_DAYS('2017-05-01')) , PARTITION p29 VALUES LESS THAN (TO_DAYS('2017-06-01')) , PARTITION p30 VALUES LESS THAN (TO_DAYS('2017-07-01')) , PARTITION p31 VALUES LESS THAN (TO_DAYS('2017-08-01')) , PARTITION p32 VALUES LESS THAN (TO_DAYS('2017-09-01')) , PARTITION p33 VALUES LESS THAN (TO_DAYS('2017-10-01')) , PARTITION p34 VALUES LESS THAN (TO_DAYS('2017-11-01')), PARTITION p35 VALUES LESS THAN (TO_DAYS('2017-12-01')), PARTITION p36 VALUES LESS THAN MAXVALUE );

3、添加索引

ALTER TABLE rpt_all_order_list_d ADD INDEX index_add_time (add_time) ALTER TABLE rpt_fin_reg_auth_all ADD INDEX index_auth_time (auth_time)