MySQL分区字段列有必要再单独建索引吗？

前言

大家都知道对于分区字段必须是主键的一部分，那么建了复合主键之后，是否需要对分许字段再单独添加一个索引呢？有没有效果？来验证一下，下面话不多说了，来一起看看详细的介绍吧。


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 LЕSS THAN (736754) ENGINE = InnoDB,

PARTITION p1 VALUES LЕSS THAN (736785) ENGINE = InnoDB,

PARTITION p2 VALUES LЕSS THAN (736815) ENGINE = InnoDB,

PARTITION p3 VALUES LЕSS THAN (736846) ENGINE = InnoDB,

PARTITION p4 VALUES LЕSS THAN (736876) ENGINE = InnoDB,

PARTITION p5 VALUES LЕSS THAN (736907) ENGINE = InnoDB,

PARTITION p6 VALUES LЕSS THAN (736938) ENGINE = InnoDB,

PARTITION p7 VALUES LЕSS THAN (736968) ENGINE = InnoDB,

PARTITION p8 VALUES LЕSS THAN (736999) ENGINE = InnoDB,

PARTITION p9 VALUES LЕSS THAN (737029) ENGINE = InnoDB,

PARTITION p10 VALUES LЕSS THAN (737060) ENGINE = InnoDB);
2、插入部分数据数据，

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘1’, ‘0’, ‘GMT+8’, ‘2022-07-01’, ”, ‘M-NotiCleanFull-FamilyRecom-0026’, ”, ‘2022-07-02 00:07:02’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘2’, ‘1’, ‘GMT+8’, ‘2022-09-30′, ’23’, ‘Ma5dtJub’, ‘EG’, ‘2022-10-01 00:00:00’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘3’, ‘1’, ‘GMT+8’, ‘2022-09-10′, ’10’, ’28’, ‘DZ’, ‘2022-09-11 00:08:20’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘4’, ‘1’, ‘GMT+8’, ‘2022-02-03′, ’20’, ’32’, ‘AD’, ‘2022-02-04 00:00:00’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘5’, ‘0’, ‘GMT+8’, ‘2022-03-05’, ‘2’, NULL, ‘AI’, ‘2022-03-06 02:10:00’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘6’, ‘0’, ‘GMT+8’, ‘2022-09-23′, ’13’, ‘M-BrandSplash-S-0038’, ‘AG’, ‘2022-09-23 13:00:00’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘7’, ‘1’, NULL, ‘2022-10-13′, ’12’, ‘BB-Main-APPAd-0018’, ‘AF’, ‘2022-10-14 12:00:00’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘8’, ‘0’, ‘GMT+8’, ‘2022-10-28’, ‘2’, ‘M-ChargeReminder-S-0040’, ‘AE’, ‘2022-10-29 00:00:00’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (‘9’, ‘1’, ‘GMT+8’, ‘2022-10-09′, NULL, ’30’, ‘AI’, ‘2022-10-10 00:09:00’);

INSERT INTO `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) VALUES (’10’, ‘0’, ‘GMT+8’, ‘2022-10-05’, ‘5’, ‘ M-BrandSplash’, ‘LA’, ‘2022-10-06 05:10:00’);
3、分析语句

EXPLAIN PARTITIONS

select * from effect_new_index

where create_time = ‘2022-10-14 12:00:00’ 结果为：

4、给表effect_new添加索引idx_ctime

5、分析添加索引后的执行计划

结果为：

6、结论：

虽然表已经根据此字段分区，但这不能等同于索引。分了区，只能说该字段为某个值的记录会在某个分区里面，但不是索引，还要一顿好找。

有时候，主键不等于分区依据列，这时候主键又想建聚集索引的话，那么必须包含分区依据列，搞成复合主键。那么，这种情况下，分区依据列不就有索引了吗？是的，可是它不够快，如果在这个复合索引里面，分区依据列不排在第一位，就不够快，如果查找语句里常常用分区依据列作为过滤条件，就有必要为分区依据列额外单独建立一个索引。


总结

以上就是这篇文章的全部内容了，本文还有许多不足，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对共生网络的支持。