柒伍伍捌壹陆 2019-11-11 13:47 采纳率: 0%
浏览 955
已采纳

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等会导致索引失效的方式。

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 nrf52810-c三个a 程序
  • ¥15 lego-loam跑出来的roll误差很大
  • ¥50 求一个半透明没有锯齿的圆角窗体的实现例子
  • ¥15 STM32cubeMX里的FreeRTOS无法释放内存
  • ¥15 CATIA有些零件打开直接单机确定终止
  • ¥15 请问有会的吗,用MATLAB做
  • ¥15 phython如何实现以下功能?查找同一用户名的消费金额合并—
  • ¥15 ARIMA模型时间序列预测用pathon解决
  • ¥15 孟德尔随机化怎样画共定位分析图
  • ¥18 模拟电路问题解答有偿速度