MYSQL查询优化

目前手头有个查询：

SELECT LPP.learning_project_pupilID, SL.serviceID, MAX(LPPO.start_date), SUM(LPPOT.license_mode_value) totalAssignedLicenses
          FROM t_services_licenses SL 
          INNER JOIN t_pupils_offers_services POS ON POS.service_licenseID = SL.service_licenseID 
          INNER JOIN j_learning_projects_pupils_offers LPPO ON LPPO.learning_project_pupil_offerID = POS.learning_project_pupil_offerID
          INNER JOIN j_learning_projects_pupils LPP ON LPPO.learning_project_pupilID = LPP.learning_project_pupilID
          INNER JOIN j_learning_projects_pupils_offers_tracking LPPOT ON LPPOT.pupil_offer_serviceID = POS.pupil_offer_serviceID
          INNER JOIN t_filters_items FI ON FI.itemID = LPP.learning_project_pupilID_for_filter_join  
          WHERE FI.filterID = '4dce2235-aafd-4ba2-b248-c137ad6ce8ca' 
          AND SL.serviceID IN ('OnlineConversationClasses', 'TwentyFourSeven')
          GROUP BY LPP.learning_project_pupilID, SL.serviceID

查询非常慢，需要耗时半个多小时之多。

下面是表的一些详细信息：

t_filters_items表：

j_learning_projects_pupils_offers_tracking表：

j_learning_projects_pupils表：

j_learning_projects_pupils_offers表：

t_pupils_offers_services表：

 

t_services_licenses表：

执行计划如下：

 sql脚本如下：

DROP TABLE IF EXISTS t_services_licenses;
CREATE TABLE t_services_licenses (
  service_licenseID varchar(36) NOT NULL,
  serviceID varchar(36) NOT NULL,
  disciplineID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  serial_key varchar(50) DEFAULT NULL,
  deleted tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (service_licenseID),
  KEY FK_t_services_licenses_serviceID (serviceID),
  KEY IDX_disciplineID (disciplineID),
  KEY IDX_deleted (deleted),
  CONSTRAINT FK_t_services_licenses_serviceID FOREIGN KEY (serviceID) REFERENCES p_services (serviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS t_pupils_offers_services;
CREATE TABLE t_pupils_offers_services (
  pupil_offer_serviceID varchar(36) NOT NULL,
  learning_project_pupil_offerID varchar(36) NOT NULL,
  service_licenseID varchar(36) NOT NULL,
  triggered_pupil_offer_serviceID varchar(36) DEFAULT NULL,
  triggered_right_of_use_typeID int(10) unsigned DEFAULT NULL,
  triggered_right_of_use_value bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (pupil_offer_serviceID),
  KEY FK_t_pupils_offers_services_offer_serviceID (service_licenseID),
  KEY IDX_ID_SERVICE (learning_project_pupil_offerID,service_licenseID),
  CONSTRAINT FK_t_pupils_offers_services_lppoID FOREIGN KEY (learning_project_pupil_offerID) REFERENCES j_learning_projects_pupils_offers (learning_project_pupil_offerID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_t_pupils_offers_services_slID FOREIGN KEY (service_licenseID) REFERENCES t_services_licenses (service_licenseID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS j_learning_projects_pupils_offers;
CREATE TABLE j_learning_projects_pupils_offers (
  learning_project_pupil_offerID varchar(36) NOT NULL,
  learning_project_pupilID bigint(20) unsigned NOT NULL,
  offerID varchar(36) NOT NULL,
  start_date datetime NOT NULL,
  end_date datetime NOT NULL,
  deleted tinyint(3) unsigned NOT NULL DEFAULT '0',
  interruption_count int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (learning_project_pupil_offerID),
  KEY FK_j_learning_projects_pupils_offers_projID (learning_project_pupilID),
  KEY FK_j_learning_projects_pupils_offers_offerID (offerID),
  KEY IDX_start_date (start_date) USING BTREE,
  KEY IDX_end_date (end_date) USING BTREE,
  KEY IDX_deleted (deleted),
  CONSTRAINT FK_LPPO_LP FOREIGN KEY (learning_project_pupilID) REFERENCES j_learning_projects_pupils (learning_project_pupilID) ON DELETE CASCADE,
  CONSTRAINT FK_LPPO_O FOREIGN KEY (offerID) REFERENCES t_offers (offerID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS j_learning_projects_pupils;
CREATE TABLE j_learning_projects_pupils (
  learning_project_pupilID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_actorID varchar(36) NOT NULL,
  active tinyint(3) unsigned DEFAULT '1' COMMENT '1',
  disciplineID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  deleted tinyint(3) unsigned NOT NULL DEFAULT '0',
  active_work_reminder tinyint(3) unsigned NOT NULL DEFAULT '1',
  enable_change_of_reminder_settings tinyint(3) unsigned NOT NULL DEFAULT '1',
  enable_objective_redefinition tinyint(3) unsigned NOT NULL DEFAULT '1',
  enable_sequence_removing tinyint(3) unsigned NOT NULL DEFAULT '1',
  send_detailed_report tinyint(3) unsigned NOT NULL DEFAULT '1',
  send_learning_path tinyint(3) unsigned NOT NULL DEFAULT '1',
  send_test_result tinyint(3) unsigned NOT NULL DEFAULT '1',
  weekly_availability_time bigint(20) unsigned NOT NULL DEFAULT '0',
  working_reminder_periodicityID tinyint(3) unsigned NOT NULL,
  working_reminder_periodicity_value bigint(20) unsigned NOT NULL DEFAULT '0',
  registration_date datetime NOT NULL,
  current_free_mode_learning_levelID bigint(20) unsigned DEFAULT NULL,
  enable_change_of_difficulty_settings tinyint(3) unsigned NOT NULL DEFAULT '0',
  was_activated tinyint(3) unsigned NOT NULL DEFAULT '0',
  was_connected tinyint(3) unsigned NOT NULL DEFAULT '0',
  snapshot_running tinyint(3) unsigned NOT NULL DEFAULT '0',
  visible tinyint(3) unsigned NOT NULL DEFAULT '1',
  tempID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  learning_project_pupilID_for_filter_join varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  objectives_expected_time_on_formation smallint(5) unsigned NOT NULL DEFAULT '0',
  objectives_expected_time_enabled tinyint(3) unsigned NOT NULL DEFAULT '0',
  cecr_level tinyint(3) unsigned NOT NULL DEFAULT '1',
  enable_show_popup_phone_lesson_and_live_tutoring TINYINT(3) UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (learning_project_pupilID),
  KEY user_actorID (user_actorID),
  KEY FK_j_learning_projects_pupils_level (current_free_mode_learning_levelID),
  KEY IDX_statuses (was_activated,was_connected,deleted,active),
  KEY IDX_LPP_TEMPID (tempID),
  KEY IDX_LPP_TEMPFILTERID (learning_project_pupilID_for_filter_join),
  KEY IDX_DAVA (deleted,active,visible,active_work_reminder),
  KEY IDX_discipline_v (disciplineID,visible) USING BTREE,
  CONSTRAINT FK_j_learning_projects_pupils_discipline FOREIGN KEY (disciplineID) REFERENCES p_disciplines (disciplineID),
  CONSTRAINT FK_j_learning_projects_pupils_j_users_actors FOREIGN KEY (user_actorID) REFERENCES j_users_actors (user_actorID),
  CONSTRAINT FK_j_learning_projects_pupils_level FOREIGN KEY (current_free_mode_learning_levelID) REFERENCES p_learning_levels (learning_levelID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS j_learning_projects_pupils_offers_tracking;
CREATE TABLE j_learning_projects_pupils_offers_tracking (
  learning_project_pupil_offer_trackingID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  pupil_offer_serviceID varchar(36) NOT NULL,
  license_mode_typeID int(10) unsigned NOT NULL,
  license_mode_value bigint(20) unsigned NOT NULL DEFAULT '0',
  effective_consumption_value bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (learning_project_pupil_offer_trackingID),
  KEY FK_j_learning_projects_pupils_offers_tracking_posID (pupil_offer_serviceID),
  CONSTRAINT FK_j_learning_projects_pupils_offers_tracking_posID FOREIGN KEY (pupil_offer_serviceID) REFERENCES t_pupils_offers_services (pupil_offer_serviceID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



DROP TABLE IF EXISTS t_filters_items;
CREATE TABLE t_filters_items (
  filterID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  itemID varchar(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  row_order bigint(20) unsigned NOT NULL DEFAULT '0',
  selected tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (filterID,itemID),
  CONSTRAINT FK_t_filters_items_ID FOREIGN KEY (filterID) REFERENCES t_filters (filterID) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;