MYSQL查询优化的问题多条件 in查询

SELECT `userid` FROM `esys_sysusersets` WHERE (  `groupid` = '102' AND `userrole` < '3' ) OR (  `groupid` = '103' AND `userrole` < '3' ) )

返回的是一个数组如果直接把语句放到另一个查询里

in的地方用语句

SELECT `id`,`createat`,`createby`,`clientid`,`contactid`,`contactway`,`contactlog` FROM esys_dbcontactlogs esys WHERE (  ( `createby` = '100'  or `createby` in (SELECT `userid` FROM `esys_sysusersets` WHERE (  `groupid` = '102' AND `userrole` < '3' ) OR (  `groupid` = '103' AND `userrole` < '3' ) )  )  ) AND (  `dstatus` = 1 ) ORDER BY  `id` desc  LIMIT 0,10

[ RunTime:0.0112s ]

SELECT COUNT(id) AS tp_count FROM esys_dbcontactlogs esys WHERE (  ( `createby` = '100'  or `createby` in (SELECT `userid` FROM `esys_sysusersets` WHERE (  `groupid` = '102' AND `userrole` < '3' ) OR (  `groupid` = '103' AND `userrole` < '3' ) )  )  ) AND (  `dstatus` = 1 ) LIMIT 1

[ RunTime:24.0147s ]

in的地方先把语句执行了返回列表再进行查询

SELECT `id`,`createat`,`createby`,`clientid`,`contactid`,`contactway`,`contactlog` FROM esys_dbcontactlogs esys WHERE (  ( `createby` = '100'  or `createby` in (659,708,720,725,728,744,757,795,803,833,835,837,847,849,851,856,858,883,884,885,886,887,888,927,932,937,945,947,953,958,963,964,965,966,967,968,1006,1013,1014,1016)  )  ) AND (  `dstatus` = 1 ) ORDER BY  `id` desc  LIMIT 0,10

[ RunTime:0.0007s ]

SELECT COUNT(id) AS tp_count FROM esys_dbcontactlogs esys WHERE (  ( `createby` = '100'  or `createby` in (659,708,720,725,728,744,757,795,803,833,835,837,847,849,851,856,858,883,884,885,886,887,888,927,932,937,945,947,953,958,963,964,965,966,967,968,1006,1013,1014,1016)  )  ) AND (  `dstatus` = 1 ) LIMIT 1

[ RunTime:0.5297s ]

两者效率为什么差这么多呢

esys_dbcontactlogs的数据量在60W左右

esys_sysusersets表的数据量大概多少？
第二次SELECT COUNT(id)的查询确认不是因为缓存才这么快？
esys_dbcontactlogs表注意是否命中索引（select前面加EXPLAIN），没有索引的话可以加上。尽量避免使用or等会导致索引失效的方式。

你的 createBy上应该建了索引了，如果没有需要建一个普通索引，这样速度更快。
然后sql中尽量不要使用or，第二个速度快是因为你条件里面都是常量。
用我的sql试下，看看速度快不快：

SELECT count(*) tp_count FROM 
(
SELECT
id
FROM
    esys_dbcontactlogs esys 
WHERE
`createby` = '100' AND ( `dstatus` = 1 ) 
 UNION 
 SELECT
id
FROM
    esys_dbcontactlogs esys 
WHERE
`createby` IN ( SELECT `userid` FROM `esys_sysusersets` WHERE  `groupid` in( '102','103' ) AND `userrole` < '3' ) AND ( `dstatus` = 1 ) 
) A

MYSQL查询优化的问题 多条件 in查询

MYSQL查询优化的问题多条件 in查询