java数据库左查询右查询,如何在大型数据库中优化具有多个左连接的选择查询...

我的疑问是

select cp_emp_personal_detail.id

,cp_emp_personal_detail.emp_code

,cp_emp_personal_detail.emp_name

,cp_department.department

,GROUP_CONCAT(TIME(devicelogs_3_2018.LogDate) ORDER BY devicelogs_3_2018.LogDate) present

from cp_emp_personal_detail

left join cp_emp_official_detail on cp_emp_official_detail.emp_id = cp_emp_personal_detail.id

left join devicelogs_3_2018 on devicelogs_3_2018.DeviceId = cp_emp_personal_detail.org_id and cp_emp_personal_detail.id AND date(devicelogs_3_2018.LogDate) = CURRENT_DATE()

left join cp_department on cp_emp_official_detail.department = cp_department.id

where cp_emp_personal_detail.org_id = 1

group by cp_emp_personal_detail.id

和数据库设计是

CREATE TABLE `cp_emp_personal_detail` (

`id` int(11) NOT NULL,

`org_id` int(11) NOT NULL,

`emp_code` varchar(100) DEFAULT NULL,

`emp_name` varchar(100) NOT NULL,

`emp_guardian_name` varchar(100) DEFAULT NULL,

`emp_dob` date NOT NULL,

`emp_email` varchar(100) DEFAULT NULL,

`emp_password` varchar(100) NOT NULL,

`emp_phone` bigint(15) DEFAULT NULL,

`emp_mobile` bigint(15) NOT NULL,

`emp_emergency_no` int(11) DEFAULT NULL,

`emp_address1` longtext NOT NULL,

`emp_address2` longtext,

`emp_city` int(11) NOT NULL,

`emp_pan` varchar(30) NOT NULL,

`emp_country` int(11) NOT NULL,

`emp_state` int(11) NOT NULL,

`emp_aadhar` varchar(30) DEFAULT NULL,

`emp_tax_status` varchar(100) NOT NULL,

`emp_pic` varchar(255) DEFAULT NULL,

`emp_gender` varchar(50) NOT NULL,

`emp_status` varchar(100) NOT NULL DEFAULT 'activate',

`crm_status` varchar(11) DEFAULT NULL,

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`last_updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY LIST (org_id)

(

PARTITION P1 VALUES IN (1) ENGINE=InnoDB,

PARTITION P2 VALUES IN (2) ENGINE=InnoDB

);

CREATE TABLE `devicelogs_3_2018` (

`DeviceLogId` bigint(20) NOT NULL,

`DownloadDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`DeviceId` bigint(20) NOT NULL,

`UserId` varchar(50) NOT NULL,

`LogDate` timestamp NOT NULL DEFAULT '1970-12-31 18:30:01',

`Direction` varchar(255) DEFAULT NULL,

`AttDirection` varchar(255) DEFAULT NULL,

`C1` varchar(255) DEFAULT NULL,

`C2` varchar(255) DEFAULT NULL,

`C3` varchar(255) DEFAULT NULL,

`C4` varchar(255) DEFAULT NULL,

`C5` varchar(255) DEFAULT NULL,

`C6` varchar(255) DEFAULT NULL,

`C7` varchar(255) DEFAULT NULL,

`WorkCode` varchar(255) DEFAULT NULL,

`hrapp_syncstatus` tinyint(4) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `cp_department` (

`id` int(11) NOT NULL,

`org_id` int(11) NOT NULL,

`department` varchar(20) NOT NULL,

`depart_abb` varchar(10) DEFAULT NULL,

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`last_updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY LIST (org_id)

(

PARTITION P1 VALUES IN (1) ENGINE=InnoDB,

PARTITION P2 VALUES IN (2) ENGINE=InnoDB

);

CREATE TABLE `cp_emp_official_detail` (

`id` int(11) NOT NULL,

`emp_id` int(11) NOT NULL,

`org_id` int(11) NOT NULL,

`joining_date` date DEFAULT NULL,

`confirmation_periode` int(11) DEFAULT NULL,

`job_type` varchar(100) DEFAULT NULL,

`payment_mode` varchar(100) DEFAULT NULL,

`department` int(11) DEFAULT NULL,

`acc_no` int(11) DEFAULT NULL,

`designation` int(11) DEFAULT NULL,

`ess_status` int(11) DEFAULT NULL,

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`last_updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY LIST (org_id)

(

PARTITION P1 VALUES IN (1) ENGINE=InnoDB,

PARTITION P2 VALUES IN (2) ENGINE=InnoDB

);

谁能解释我如何优化查询目前需要3到4分钟才能执行超过8k的记录