mysql - sql 在“查询中”重复查询但比查询中的一次要快？

Question

mysql版本是5.7，

SELECT distinct(t.id),
               t.sys_user_id,
               t.update_time
FROM institute_student t
         inner join institute_student_campus student_campus on student_campus.student_id = t.id
WHERE t.deleted = 0
  AND t.birth_country_id = 9
  AND t.type = 'FINISHED'
  AND t.gender = 6

  and t.id in (SELECT distinct s_alert.student_id FROM institute_student_alert_welfare s_alert inner join institute_alert_welfare alert on s_alert.alert_welfare_id=alert.id and alert.type='Alert')

  and student_campus.campus_id in (1, 2, 17, 18, 19, 20)
ORDER by t.update_time desc
LIMIT 15;

花 4.25 秒，

SELECT distinct(t.id),
               t.sys_user_id,
               t.update_time
FROM institute_student t
         inner join institute_student_campus student_campus on student_campus.student_id = t.id
WHERE t.deleted = 0
  AND t.birth_country_id = 9
  AND t.type = 'FINISHED'
  AND t.gender = 6

  and t.id in (SELECT distinct s_alert.student_id FROM institute_student_alert_welfare s_alert inner join institute_alert_welfare alert on s_alert.alert_welfare_id=alert.id and alert.type='Alert')
  and t.id in (SELECT distinct s_alert.student_id FROM institute_student_alert_welfare s_alert inner join institute_alert_welfare alert on s_alert.alert_welfare_id=alert.id and alert.type='Alert')

  and student_campus.campus_id in (1, 2, 17, 18, 19, 20)
ORDER by t.update_time desc
LIMIT 15;

花0.02秒。

唯一的区别是下面的双重“查询中”sql。

and t.id in (SELECT distinct s_alert.student_id FROM institute_student_alert_welfare s_alert inner join institute_alert_welfare alert on s_alert.alert_welfare_id=alert.id and alert.type='Alert')

谁能告诉我为什么？谢谢 ：）

标签： mysql