欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-03 20:25:05
前言 大家都知道对于分区字段必须是主键的一部分,那么建了复合主键之后,是否需要对分许字段再单独添加一个索引呢?有没有效果?来验证一下,下面话不多说了,来一起看看详细的介绍...

前言

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

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 = innodb,
 partition p5 values less than (736907) engine = innodb,
 partition p6 values less than (736938) engine = innodb,
 partition p7 values less than (736968) engine = innodb,
 partition p8 values less than (736999) engine = innodb,
 partition p9 values less than (737029) engine = innodb,
 partition p10 values less than (737060) engine = innodb);

2、插入部分数据数据,

insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('1', '0', 'gmt+8', '2017-07-01', '', 'm-noticleanfull-familyrecom-0026', '', '2017-07-02 00:07:02');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('2', '1', 'gmt+8', '2017-09-30', '23', 'ma5dtjub', 'eg', '2017-10-01 00:00:00');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('3', '1', 'gmt+8', '2017-09-10', '10', '28', 'dz', '2017-09-11 00:08:20');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('4', '1', 'gmt+8', '2017-02-03', '20', '32', 'ad', '2017-02-04 00:00:00');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('5', '0', 'gmt+8', '2017-03-05', '2', null, 'ai', '2017-03-06 02:10:00');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('6', '0', 'gmt+8', '2017-09-23', '13', 'm-brandsplash-s-0038', 'ag', '2017-09-23 13:00:00');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('7', '1', null, '2017-10-13', '12', 'bb-main-appad-0018', 'af', '2017-10-14 12:00:00');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('8', '0', 'gmt+8', '2017-10-28', '2', 'm-chargereminder-s-0040', 'ae', '2017-10-29 00:00:00');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('9', '1', 'gmt+8', '2017-10-09', null, '30', 'ai', '2017-10-10 00:09:00');
insert into `effect_new` (`id`, `type`, `timezone`, `date`, `hour`, `position`, `country`, `create_time`) values ('10', '0', 'gmt+8', '2017-10-05', '5', ' m-brandsplash', 'la', '2017-10-06 05:10:00');

3、分析语句

explain partitions
select * from effect_new_index
where create_time = '2017-10-14 12:00:00' 

结果为:

id select_type table partitions tpye possible_keys key key_len ref rows filtered extra
1 simple effect_new p8 all null null null null 391515 10 using where

4、给表effect_new添加索引idx_ctime

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

结果为:

id select_type table partitions tpye possible_keys key key_len ref rows filtered extra
1 simple effect_new p8 ref idx_ctime idx_ctime 5 const 60760 100 null

6、结论:

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

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

总结

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