sql 优化

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) ;