SQL语句中，尽量避免使用or,in关键字，因为执行效率低。

 

join > exists > in

union > or

 

select * from PIXPatient where PIXPatientTID

in (select distinct PIXPatientTID from DomainPatient where DomainPatientTID

in ( select DomainPatientTID from DomainPatient

  where PatientBirthday = '1994-01-09' or PatientBirthday = '1994-01-01'

  union select  DomainPatientTID from PersonName where FamilyName = '倪' or GivenName = '界'));

 

两种方式：

1）加索引

alter table PersonName add index Index_FamilyName (FamilyName), add index Index_GivenName (GivenName);

alter table DomainPatient add index Index_PatientBirthday (PatientBirthday);

 

2）替换or ， in（效果明显）

select * from PIXPatient inner join (

    select distinct PIXPatientTID from DomainPatient inner join (

     select DomainPatientTID from DomainPatient  where PatientBirthday = '1994-01-09'

     union select DomainPatientTID from DomainPatient  where PatientBirthday = '1994-01-01'

     union select  DomainPatientTID from PersonName where FamilyName = '倪'

     union select  DomainPatientTID from PersonName where GivenName = '界' ) a using(DomainPatientTID) ) b using(PIXPatientTID) ;