柒伍伍捌壹陆
2019-11-11 13:47
采纳率: 33.3%
浏览 756

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左右

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • 到底有多少个小谢 2019-11-11 14:18
    已采纳

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

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • 葫芦胡 2019-11-11 16:02

    你的 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
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题