大家都知道對於分區字段必須是主鍵的一部分,那麼建了複合主鍵之後,是否需要對分許字段再單獨添加一個索引呢?有沒有效果?本文主要為大家介紹了關於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 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);
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');
# #
EXPLAIN PARTITIONS select * from effect_new_index where create_time = '2017-10-14 12:00:00'
結果為:
#select_type | table | partitions | tpye | possible_keys | key | ##key_len#ref | rows | filtered | extra | #1 | |
effect_new | p8 | ALL | null | null | #null | null | #391515 | #10 | Using where |
5、分析新增索引後的執行計劃
結果為:
table | partitions | tpye | possible_keys | key | key_len | ##ref# rows | filtered | extra | #1 | SIMPLE | |
#p8 | ref | idx_ctime | idx_ctime | #5 | const | 60760 | #100 | null | 6、結論: |
有時候,主鍵不等於分區依據列,這時候主鍵又想建聚集索引的話,那麼就必須包含分區依據列,搞成複合主鍵。那麼,在這種情況下,分區依據列不就有索引了嗎?是的,可是它不夠快,如果在這個複合索引裡面,分區依據列不排在第一位,就不夠快,如果查找語句裡常常用分區依據列作為過濾條件,就有必要為分區依據列額外單獨建立一個索引。
MYSQL的分區字段,必須包含在主鍵字段內_MySQL
MySQL不同儲存引擎和不同分區欄位對於查詢的影響_MySQL
MySQL分區表partition線上修改分區欄位_MySQL#
以上是MySQL分區字段列有必要再單獨建立索引嗎?的詳細內容。更多資訊請關注PHP中文網其他相關文章!