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

前言

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

1、新建表effect_new(以创建时间按月分区)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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、插入部分数据数据，

?

1 2 3 4 5 6 7 8 9 10

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、分析语句

?

1 2 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、结论：

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

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

总结

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

原文链接：https://segmentfault.com/a/1190000011593127