Is it necessary to create a separate index for the MySQL partition field column ?, Mysql Field

Is it necessary to create a separate index for the MySQL partition field column ?, Mysql Field

Preface

As we all know, partition fields must be part of the primary key. After a composite primary key is created, do you need to add an independent index to the allowed fields? Is there any effect? Let's take a look at the details below.

1. Create a table named partition t_new (created on a monthly basis)

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=utf8PARTITION 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 some data,

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. Analysis Statement

EXPLAIN PARTITIONSselect * from effect_new_indexwhere create_time = '2017-10-14 12:00:00' 

Result:

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. Add the index idx_ctime to the effect_new table.

5. Analyze the execution plan after the index is added

Result:

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. Conclusion:

Although the table is already partitioned based on this field, this cannot be equivalent to an index. If the field is partitioned, it can only be said that the record with a value of this field will be in a partition, but it is not an index, and it will be easy to find.

Sometimes, the primary key is not equal to the partition based column. If you want to create a clustered index for the primary key, you must include the partition based column to form a composite primary key. In this case, isn't the partition column indexed? Yes, but it is not fast enough. In this composite index, the partition is not ranked first by column. If the query statement uses the partition as the filter condition, it is necessary to create an additional index for the partition based on the column.

Summary

The above is all the content of this article. There are still many shortcomings in this article. I hope this article will have some reference and learning value for everyone's learning or work. If you have any questions, please leave a message, thank you for your support.