主页 / dba / 问题 / 115165
Accepted
automatem
Asked: 2015-09-16 18:28:44 +0800 CST

复合索引适用于较大的数据集，但不适用于较小的数据集

  • 772

这是针对关键查询优化 my.ini的后续问题

数据模型：

DROP TABLE IF EXISTS `contract`;
CREATE TABLE IF NOT EXISTS `contract` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `organisation_id` int(11) NOT NULL,
  `code` varchar(3) NOT NULL,
  `moh_code` varchar(20) NOT NULL,
  `moh_number` varchar(20) NOT NULL,
  `moh_variation` varchar(20) NOT NULL,
  `description` mediumtext NOT NULL,
  `start` datetime NOT NULL,
  `finish` datetime NOT NULL,
  `cities` text,
  `is_support_contract` tinyint(1) DEFAULT NULL,
  `is_intensive` tinyint(1) DEFAULT NULL,
  `moh_team_type` varchar(4) DEFAULT NULL,
  `moh_team_setting` varchar(1) DEFAULT NULL,
  `moh_service_type` varchar(2) DEFAULT NULL,
  `moh_target_population` int(4) DEFAULT NULL,
  `moh_facility_id` varchar(10) DEFAULT NULL,
  `moh_open_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `organisation_id_idx` (`organisation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_engagement`
--

DROP TABLE IF EXISTS `peer_engagement`;
CREATE TABLE IF NOT EXISTS `peer_engagement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `peer_id` int(11) NOT NULL,
  `ps_number_id` int(11) NOT NULL,
  `service_organisation_id` int(11) DEFAULT NULL,
  `past_service_disengaged` datetime DEFAULT NULL,
  `key_worker_id` int(11) DEFAULT NULL,
  `iss_clinical_service_id` int(11) DEFAULT NULL,
  `iss_psychiatrist_id` int(11) DEFAULT NULL,
  `iss_service_coordinator_id` int(11) DEFAULT NULL,
  `iss_declined_courier_back_address` varchar(255) DEFAULT NULL,
  `waitinglist_support_suggestions_made` mediumtext,
  `waiting_psw_id` int(11) DEFAULT NULL,
  `declined_waiting_what_support_avail` mediumtext,
  `referral_source_id` int(11) DEFAULT NULL,
  `iss_referred_by` int(11) DEFAULT NULL,
  `record_entered_by` int(11) NOT NULL,
  `record_entered` datetime NOT NULL,
  `waiting_list_priority_id` int(11) DEFAULT NULL,
  `assigned_psw_id` int(11) DEFAULT NULL,
  `assigned` datetime DEFAULT NULL,
  `assigned_by` int(11) DEFAULT NULL,
  `assigned_effective` datetime DEFAULT NULL,
  `last_reengaged` datetime DEFAULT NULL,
  `last_reengaged_id` int(11) DEFAULT NULL,
  `disengagement_started` datetime DEFAULT NULL,
  `disengagement_target` datetime DEFAULT NULL,
  `disengagement_type_id` int(11) DEFAULT NULL,
  `disengagement_comments` mediumtext,
  `status_id` int(11) DEFAULT NULL,
  `closed` datetime DEFAULT NULL,
  `closed_by` int(11) DEFAULT NULL,
  `is_intensive` varchar(5) DEFAULT NULL,
  `heard_about_us_id` int(11) DEFAULT NULL,
  `primhd_referral_no` varchar(50) DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `is_gp_only` tinyint(4) NOT NULL,
  `referral_to_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `peer_id_idx` (`peer_id`),
  KEY `ps_number_id_idx` (`ps_number_id`),
  KEY `service_organisation_id_idx` (`service_organisation_id`),
  KEY `key_worker_id_idx` (`key_worker_id`),
  KEY `iss_clinical_service_id_idx` (`iss_clinical_service_id`),
  KEY `iss_psychiatrist_id_idx` (`iss_psychiatrist_id`),
  KEY `iss_service_coordinator_id_idx` (`iss_service_coordinator_id`),
  KEY `referral_source_id_idx` (`referral_source_id`),
  KEY `iss_referred_by_idx` (`iss_referred_by`),
  KEY `record_entered_by_idx` (`record_entered_by`),
  KEY `waiting_psw_id_idx` (`waiting_psw_id`),
  KEY `waiting_list_priority_id_idx` (`waiting_list_priority_id`),
  KEY `assigned_psw_id_idx` (`assigned_psw_id`),
  KEY `assigned_by_idx` (`assigned_by`),
  KEY `last_reengaged_id_idx` (`last_reengaged_id`),
  KEY `disengagement_type_id_idx` (`disengagement_type_id`),
  KEY `status_id_idx` (`status_id`),
  KEY `closed_by_idx` (`closed_by`),
  KEY `peer_engagement_heard_about_us_id_fk` (`heard_about_us_id`),
  KEY `moved_to_and_delete_id_foreign_key` (`moved_to_and_delete_id`),
  KEY `deleted_date` (`deleted_date`),
  KEY `disengagement_target` (`disengagement_target`),
  KEY `peer_engagement_referral_to_id_fk` (`referral_to_id`),
  KEY `is_intensive` (`is_intensive`),
  KEY `is_intensive_2` (`is_intensive`,`status_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1502 ;

-- --------------------------------------------------------

--
-- Table structure for table `peer_number`
--

DROP TABLE IF EXISTS `peer_number`;
CREATE TABLE IF NOT EXISTS `peer_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ps_number` varchar(5) NOT NULL,
  `contract_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `contract_id_idx` (`contract_id`),
  KEY `ps_number` (`ps_number`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=75981 ;

-- --------------------------------------------------------

--
-- Table structure for table `person`
--

DROP TABLE IF EXISTS `person`;
CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `suite` varchar(100) DEFAULT NULL,
  `hnr_street` varchar(200) NOT NULL,
  `suburb` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `postcode` varchar(5) NOT NULL,
  `manual_address_entry` tinyint(1) DEFAULT NULL,
  `current_living_situation_id` int(11) DEFAULT NULL,
  `dhb_area_id` int(11) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `phone_daytime` varchar(25) DEFAULT NULL,
  `phone_evening` varchar(25) DEFAULT NULL,
  `mobile` varchar(25) DEFAULT NULL,
  `fax` varchar(25) DEFAULT NULL,
  `emergency_contact_details` text,
  `notes` mediumtext,
  `dob` date DEFAULT NULL,
  `nhi` varchar(7) DEFAULT NULL,
  `gender_id` int(11) DEFAULT NULL,
  `ethnicity_id` int(11) DEFAULT NULL,
  `team_leader_id` int(11) DEFAULT NULL,
  `organisation_id` int(11) DEFAULT NULL,
  `role` varchar(100) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `postal_address` varchar(200) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `deleted_date` datetime DEFAULT NULL,
  `moved_to_and_delete_id` int(4) DEFAULT NULL,
  `employment_situation_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id_idx` (`user_id`),
  KEY `current_living_situation_id_idx` (`current_living_situation_id`),
  KEY `dhb_area_id_idx` (`dhb_area_id`),
  KEY `gender_id_idx` (`gender_id`),
  KEY `team_leader_id_idx` (`team_leader_id`),
  KEY `organisation_id_idx` (`organisation_id`),
  KEY `moved_to_and_delete_id_foreign_key_person` (`moved_to_and_delete_id`),
  KEY `person_employment_situation_id_fk` (`employment_situation_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2377 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `contract`
--
ALTER TABLE `contract`
  ADD CONSTRAINT `contract_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`);

--
-- Constraints for table `peer_engagement`
--
ALTER TABLE `peer_engagement`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `peer_engagement` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_by_person_id` FOREIGN KEY (`assigned_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_assigned_psw_id_person_id` FOREIGN KEY (`assigned_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_closed_by_person_id` FOREIGN KEY (`closed_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_disengagement_type_id_disengagement_type_id` FOREIGN KEY (`disengagement_type_id`) REFERENCES `disengagement_type` (`id`),
  ADD CONSTRAINT `peer_engagement_heard_about_us_id_fk` FOREIGN KEY (`heard_about_us_id`) REFERENCES `heard_about_us` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_clinical_service_id_organisation_id` FOREIGN KEY (`iss_clinical_service_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_psychiatrist_id_person_id` FOREIGN KEY (`iss_psychiatrist_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_referred_by_person_id` FOREIGN KEY (`iss_referred_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_iss_service_coordinator_id_person_id` FOREIGN KEY (`iss_service_coordinator_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_key_worker_id_person_id` FOREIGN KEY (`key_worker_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_last_reengaged_id_peer_number_id` FOREIGN KEY (`last_reengaged_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_peer_id_person_id` FOREIGN KEY (`peer_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_ps_number_id_peer_number_id` FOREIGN KEY (`ps_number_id`) REFERENCES `peer_number` (`id`),
  ADD CONSTRAINT `peer_engagement_record_entered_by_person_id` FOREIGN KEY (`record_entered_by`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_source_id_referral_source_id` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_source` (`id`),
  ADD CONSTRAINT `peer_engagement_referral_to_id_fk` FOREIGN KEY (`referral_to_id`) REFERENCES `referral_to` (`id`),
  ADD CONSTRAINT `peer_engagement_service_organisation_id_organisation_id` FOREIGN KEY (`service_organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `peer_engagement_status_id_status_id` FOREIGN KEY (`status_id`) REFERENCES `status` (`id`),
  ADD CONSTRAINT `peer_engagement_waiting_psw_id_person_id` FOREIGN KEY (`waiting_psw_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `pwwi` FOREIGN KEY (`waiting_list_priority_id`) REFERENCES `waiting_list_priority` (`id`);

--
-- Constraints for table `peer_number`
--
ALTER TABLE `peer_number`
  ADD CONSTRAINT `peer_number_contract_id_contract_id` FOREIGN KEY (`contract_id`) REFERENCES `contract` (`id`);

--
-- Constraints for table `person`
--
ALTER TABLE `person`
  ADD CONSTRAINT `moved_to_and_delete_id_foreign_key_person` FOREIGN KEY (`moved_to_and_delete_id`) REFERENCES `person` (`id`),
  ADD CONSTRAINT `person_current_living_situation_id_current_living_situation_id` FOREIGN KEY (`current_living_situation_id`) REFERENCES `current_living_situation` (`id`),
  ADD CONSTRAINT `person_dhb_area_id_contract_id` FOREIGN KEY (`dhb_area_id`) REFERENCES `contract` (`id`),
  ADD CONSTRAINT `person_employment_situation_id_fk` FOREIGN KEY (`employment_situation_id`) REFERENCES `employment_situation` (`id`),
  ADD CONSTRAINT `person_gender_id_gender_id` FOREIGN KEY (`gender_id`) REFERENCES `gender` (`id`),
  ADD CONSTRAINT `person_organisation_id_organisation_id` FOREIGN KEY (`organisation_id`) REFERENCES `organisation` (`id`),
  ADD CONSTRAINT `person_team_leader_id_person_id` FOREIGN KEY (`team_leader_id`) REFERENCES `person` (`id`);

查询 1： 

1) [EXPLAIN] 
  SELECT p.id AS p__id, p.status_id AS p__status_id,     p.disengagement_target AS p__disengagement_target, p2.id AS p2__id, p2.name AS p2__name, p3.id AS p3__id, p3.name AS p3__name, p4.id AS p4__id, p4.ps_number AS p4__ps_number, c.id AS c__id, c.code AS c__code, p5.id AS p5__id, p5.nhi AS p5__nhi, COALESCE(p2.name, p3.name) AS p2__0 
  FROM peer_engagement p 
    LEFT JOIN person p2 ON p.assigned_psw_id = p2.id 
    LEFT JOIN person p3 ON p.waiting_psw_id = p3.id 
    INNER JOIN peer_number p4 ON p.ps_number_id = p4.id 
    INNER JOIN contract c ON p4.contract_id = c.id 
    INNER JOIN person p5 ON p.peer_id = p5.id 
  WHERE (p.is_intensive = 0 
     AND p.status_id in (0, 1, 2, 3, 4, 5, 7)) 
  LIMIT 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   range   peer_id_idx,ps_number_id_idx,status_id_idx,is_inte...   is_intensive_2  10  NULL    611 Using where
1   SIMPLE  p2  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.assigned_psw_id   1   
1   SIMPLE  p3  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.waiting_psw_id    1   
1   SIMPLE  p5  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.peer_id   1   
1   SIMPLE  p4  eq_ref  PRIMARY,contract_id_idx PRIMARY 4   mabel_mindandbody_co_nz.p.ps_number_id  1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p4.contract_id  1

查询 2： 

2) [EXPLAIN] 
  SELECT p.id AS p__id, p.status_id AS p__status_id,     p.disengagement_target AS p__disengagement_target, p2.id AS p2__id, p2.name AS p2__name, p3.id AS p3__id, p3.name AS p3__name, p4.id AS p4__id, p4.ps_number AS p4__ps_number, c.id AS c__id, c.code AS c__code, p5.id AS p5__id, p5.nhi AS p5__nhi, COALESCE(p2.name, p3.name) AS p2__0 
  FROM peer_engagement p 
    LEFT JOIN person p2 ON p.assigned_psw_id = p2.id 
    LEFT JOIN person p3 ON p.waiting_psw_id = p3.id 
    INNER JOIN peer_number p4 ON p.ps_number_id = p4.id 
    INNER JOIN contract c ON p4.contract_id = c.id 
    INNER JOIN person p5 ON p.peer_id = p5.id 
  WHERE (p.is_intensive = 1 
     AND p.status_id in (0, 1, 2, 3, 4, 5, 7)) 
  LIMIT 20

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  p   range   peer_id_idx,ps_number_id_idx,status_id_idx,is_inte...   is_intensive_2  10  NULL    128 Using where
1   SIMPLE  p2  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.assigned_psw_id   1   
1   SIMPLE  p3  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.waiting_psw_id    1   
1   SIMPLE  p5  eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p.peer_id   1   
1   SIMPLE  p4  eq_ref  PRIMARY,contract_id_idx PRIMARY 4   mabel_mindandbody_co_nz.p.ps_number_id  1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   mabel_mindandbody_co_nz.p4.contract_id  1

区别仅在于is_intensive列。

这是数据细分：

SELECT is_intensive, status_id, COUNT( * ) 
FROM  `peer_engagement` 
GROUP BY is_intensive, status_id
ORDER BY is_intensive, status_id

is_intensive    status_id   count(*)
0   1   18
0   3   229
0   4   13
0   5   101
0   6   653
0   7   252
1   1   2
1   3   26
1   5   73
1   6   14
1   7   24

令我惊讶的是第一个查询被 index 显着加快了is_intensive_2，但是第二个查询却没有。我的预期恰恰相反：第一个查询覆盖了表的 50% 以上，因此执行表扫描，但是对于第二个查询，将使用索引。

我正在寻找一种方法来加快这两个查询的速度，即第一个查询现在执行的程度。

mysql performance

1 个回答

  1. Best Answer

    (is_intensive, status_id)是最优指标。（PS：你应该删除KEY(is_intensive)它，因为它是多余的。）但是，优化器会查看统计信息来决定使用哪个索引，并大致说......

    • 如果使用索引会命中不到 20% 的行，那么就使用它吧。
    • 否则，何必在索引和数据之间来回跳动呢？让我们避开索引并简单地扫描表。

    显然0vs 1foris_intensive使这种区别变得简单。

    您可能会发现优化器做出了错误的决定。发生这种情况是因为统计数据不准确，算法不完善，并且当您运行它时，不同的索引和/或数据块可能恰好在缓存中。

    • 1

相关问题