有如下一条SQL 执行时间1.477s

 

SELECT 

  `c`.*,

  `p`.`patient_name`,

  `p`.`age_first_val` AS `age`,

  `p`.`patient_phone`,

  `p`.`department_name`,

  `p`.`case_type`,

  `p`.`check_time`,

  `p`.`card_no`,

  `p`.`doctor`,

  `p`.`outpatient`,

  `p`.`coll_time`,

  `r`.`json_data`,

  `r`.`report_form`,

  `p`.`created_at` AS `receive_time`,

  `r`.`created_at` AS `report_time`,

  `r`.`state` 

FROM

  `ts_report_card` AS `c` 

  LEFT JOIN `kd_report_spec` AS `r` 

    ON `r`.`ts_id` = `c`.`uuid`

  LEFT JOIN `sp_case_info` AS `p` 

    ON `p`.`uuid` = `r`.`case_id` 

where `r`.`state` = 1 

ORDER BY `c`.`created_at` DESC 

LIMIT 10 OFFSET 0;

优化后这条SQL执行时间0s

SELECT 

  `c`.*,

  `p`.`patient_name`,

  `p`.`age_first_val` AS `age`,

  `p`.`patient_phone`,

  `p`.`department_name`,

  `p`.`case_type`,

  `p`.`check_time`,

  `p`.`card_no`,

  `p`.`doctor`,

  `p`.`outpatient`,

  `p`.`coll_time`,

  `r`.`json_data`,

  `r`.`report_form`,

  `p`.`created_at` AS `receive_time`,

  `r`.`created_at` AS `report_time`,

  `r`.`state` 

FROM

  `ts_report_card` AS `c` 

  LEFT JOIN `kd_report_spec` AS `r` 

    ON `r`.`ts_id` = `c`.`uuid` AND r.state = 1

  LEFT JOIN `sp_case_info` AS `p` 

    ON `p`.`uuid` = `r`.`case_id` 

ORDER BY `c`.`created_at` DESC 

LIMIT 10 OFFSET 0;