原始SQL（执行时间80s+）
SELECT count(id) as id FROM task WHERE cid=100015 AND sid IN(112310,112316,106959,110780,112324,112331,112317) AND flag='9'
优化后SQL（平均执行时间100ms）
select sum(id) as id from (
SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112310 AND flag=9
union all
SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112316 AND flag=9
union all
SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=106959 AND flag=9
union all
SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=110780 AND flag=9
union all
SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112324 AND flag=9
union all
SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112331 AND flag=9
union all
SELECT count(*) as id FROM task WHERE AND cid=123456 AND sid=112317 AND flag=9
) t
说明：
mysql版本5.6
表总的数据量60万+
cid和flag为int类型，参数去掉引号，避免隐式转换
(cid sid flag)是组合索引，使用uinon all拆分后，遍历的表多了，使用组全但更高效
點(diǎn)擊查看更多內(nèi)容
為 TA 點(diǎn)贊
評(píng)論
評(píng)論
共同學(xué)習(xí)，寫下你的評(píng)論
評(píng)論加載中...
作者其他優(yōu)質(zhì)文章
正在加載中
感謝您的支持，我會(huì)繼續(xù)努力的～
掃碼打賞，你說多少就多少
贊賞金額會(huì)直接到老師賬戶
支付方式
打開微信掃一掃，即可進(jìn)行掃碼打賞哦