前言
大家都知道对于分区字段必须是主键的一部分,那么建了复合主键之后,是否需要对分许字段再单独添加一个索引呢?有没有效果?来验证一下,下面话不多说了,来一起看看详细的介绍吧。
1、新建表effect_new(以创建时间按月分区)
CREATE TABLE `effect_new` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`type` tinyint(4) NOT NULL DEFAULT '0',
`timezone` varchar(10) DEFAULT NULL,
`date` varchar(10) NOT NULL,
`hour` varchar(2) DEFAULT NULL,
`position` varchar(200) DEFAULT NULL,
`country` varchar(32) NOT NULL,
`create_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
PRIMARY KEY (`id`,`create_time`),
KEY `index_date_hour_coun` (`date`,`hour`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=983041 DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS (`create_time`))
(PARTITION p0 VALUES LESS THAN (736754) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (736785) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (736815) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (736846) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (736876) ENGINE =