Mysql多表联查使用union all 代替or查询

多表联查注意点：

 

ACT_RU_TASK  表数据量：19767

ACT_HI_PROCINST  表数据量：21607

BPM_HI_ROLETASKINST  表数据量：1911

BPM_HI_ROLEIDENTITYLINK  表数据量：1229

 

所有的查询sql、所有的表结构及数据详情见附件。

所有的查询sql、所有的表结构及数据详情见附件（百度云网盘：链接: https://pan.baidu.com/s/1Jte1GykJBK9TJ1NyQTMzug  密码: vvja

）。

1.Sql多表联查如果只使用and的话，可以直接使用join系列。

   比如：整个查询速度还是可以的，大约0.041s

      

SELECT         RES.*         FROM             ACT_RU_TASK RES             INNER JOIN ACT_HI_PROCINST HPI ON RES.PROC_INST_ID_ = HPI.ID_             LEFT JOIN BPM_HI_ROLETASKINST as BHR  on  BHR.TASKID = RES.ID_             INNER JOIN BPM_HI_ROLEIDENTITYLINK I ON I.TASK_ID = RES.ID_         WHERE             HPI.STATUS_ !=0             AND RES.ASSIGNEE_ IS NULL             AND I.USER_TYPE = 'candidate'             AND (I.USER_LDAP ="xiaoyuhan" and I.USER_INDEX != "xiaoyuhan")         ORDER BY             RES.CREATE_TIME_ DESC             LIMIT 200 OFFSET 0

 

 

2.Sql多表联查如果只会使用到or过滤的话，不要直接使用join系列

   比如：使用join并且使用or来过滤join表中的的某个字段如下，整个查询时间大约3.833s，因此不推荐这样用。

       

SELECT          DISTINCT   RES.*         FROM             ACT_RU_TASK RES             INNER JOIN ACT_HI_PROCINST HPI ON RES.PROC_INST_ID_ = HPI.ID_             INNER JOIN ACT_RU_IDENTITYLINK I ON I.TASK_ID_ = RES.ID_         --  left join BPM_HI_ROLETASKINST as bhr  on  bhr.taskId = RES.ID_             LEFT JOIN BPM_HI_ROLEIDENTITYLINK as BHRL on  BHRL.TASK_ID =RES.ID_         WHERE             HPI.STATUS_ !=0             AND RES.ASSIGNEE_ IS NULL             AND I.TYPE_ = 'candidate'             AND (I.USER_ID_ ="xiaoyuhan"  or BHRL.user_ldap ="xiaoyuhan")         ORDER BY             RES.CREATE_TIME_ DESC             LIMIT 200 OFFSET 0

 

 

  1）可以使用union代替or查询，整个查询时间大约0.193s，就快了很多。

      

SELECT         DISTINCT RES.*         FROM             ACT_RU_TASK RES             INNER JOIN ACT_HI_PROCINST HPI ON RES.PROC_INST_ID_ = HPI.ID_             INNER JOIN ACT_RU_IDENTITYLINK I ON I.TASK_ID_ = RES.ID_         --  LEFT JOIN (SELECT ASSIGNEE_,ID_ FROM ACT_RU_TASK UNION  SELECT USER_LDAP,TASKID FROM BPM_HI_ROLETASKINST) AS BHR ON BHR.ID_=RES.ID_             LEFT JOIN (SELECT USER_ID_,TASK_ID_ FROM ACT_RU_IDENTITYLINK UNION   SELECT USER_LDAP,TASK_ID FROM BPM_HI_ROLEIDENTITYLINK) as BHRL on  BHRL.TASK_ID_ =RES.ID_         WHERE             HPI.STATUS_ !=0             AND RES.ASSIGNEE_ IS NULL             AND I.TYPE_ = 'candidate'             AND (BHRL.USER_ID_ ="xiaoyuhan")         ORDER BY             RES.CREATE_TIME_ DESC             LIMIT 200 OFFSET 0

 

 

  2）union all  大部分是比union更快的，但是不会去重，此处由于查询不符和查询目的，因此查询时间可能会更长一些，整个查询时间大约0.340s

       

SELECT             DISTINCT RES.*         FROM             ACT_RU_TASK RES             INNER JOIN ACT_HI_PROCINST HPI ON RES.PROC_INST_ID_ = HPI.ID_             INNER JOIN ACT_RU_IDENTITYLINK I ON I.TASK_ID_ = RES.ID_         --  LEFT JOIN (SELECT ASSIGNEE_,ID_ FROM ACT_RU_TASK UNION  SELECT USER_LDAP,TASKID FROM BPM_HI_ROLETASKINST) AS BHR ON BHR.ID_=RES.ID_             LEFT JOIN (SELECT USER_ID_,TASK_ID_ FROM ACT_RU_IDENTITYLINK UNION all  SELECT USER_LDAP,TASK_ID FROM BPM_HI_ROLEIDENTITYLINK) as BHRL on  BHRL.TASK_ID_ =RES.ID_         WHERE             HPI.STATUS_ !=0             AND RES.ASSIGNEE_ IS NULL             AND I.TYPE_ = 'candidate'             AND (BHRL.USER_ID_ ="xiaoyuhan")         ORDER BY             RES.CREATE_TIME_ DESC             LIMIT 200 OFFSET 0

 

 

   3）整体的union all两个子select，比left 单个表最后where还要快，这是速度最快的，整个查询时间大约0.044s（非常推荐）。

            

(SELECT             RES.*         FROM             ACT_RU_TASK RES             INNER JOIN ACT_HI_PROCINST HPI ON RES.PROC_INST_ID_ = HPI.ID_             INNER JOIN ACT_RU_IDENTITYLINK I ON I.TASK_ID_ = RES.ID_         WHERE             HPI.STATUS_ != 0             AND RES.ASSIGNEE_ IS NULL             AND I.TYPE_ = 'candidate'             AND ( I.USER_ID_ = "xiaoyuhan" )         )         UNION ALL             (SELECT             RES.*         FROM             ACT_RU_TASK RES             INNER JOIN ACT_HI_PROCINST HPI ON RES.PROC_INST_ID_ = HPI.ID_             LEFT JOIN BPM_HI_ROLETASKINST as BHR  on  BHR.TASKID = RES.ID_             INNER JOIN BPM_HI_ROLEIDENTITYLINK as BHRL on  BHRL.TASK_ID =RES.ID_         WHERE             HPI.STATUS_ !=0             AND RES.ASSIGNEE_ IS NULL             AND BHRL.USER_TYPE = 'candidate'             AND (BHRL.USER_LDAP ="xiaoyuhan" and BHRL.USER_INDEX != "xiaoyuhan")         )             ORDER BY             CREATE_TIME_  DESC             LIMIT 200 OFFSET 0